Логические функции

Откройте рабочую книгу "Логические функции" и загрузите себе копию.

Результаты практической работы "Корни квадратного уравнения":

Фамилия, имя	Оценка
Кучерявенко, Познанский, Ковалев	11
Шилкова, Яценко	10
Кучеренко	8
Родион	?
Аноним	?
Искендеров	9
Андриенко, Куц	9
Куприенко	12
Савенкова	9
Ермакова, Терещенко	11
Болгарова, Капуста, Кудряшова	8

В арсенале Excel существует набор логических функций, которые вы можете найти на вкладке Формулы в группе Библиотека функций в выпадающем меню Логические. Все логические функции возвращают ИСТИНА или ЛОЖЬ в зависимости от оценки. 

Основные логические функции:

ЕСЛИ(логическое_выражение; [значение_если_истина]; [значение_если_ложь]) – Проверяет логическое_выражение и возвращает значение_если_истина в ячейку если оно равно ИСТИНА, иначе возвращает значение_если_ложь.

И(логическое_значение1; [логическое_значение2]; …) – Проверяет логические_значения аргументов и возвращает ИСТИНА, если все они имеют значение ИСТИНА. Если хотя бы одно значение ложно, возвращает ЛОЖЬ.

ИЛИ(логическое_значение1; [логическое_значение2]; …) — Проверяет логические_значения аргументов и возвращает ИСТИНА, если хотя бы одно имеет значение ИСТИНА. Если все значения равны ЛОЖЬ, функция ИЛИ возвращает ЛОЖЬ.

Как работают логические функции?

Логические выражения и логические значения, используемые в функциях, обычно используют операторы сравнения (=, <, >, <=, >= или <>), которые возвращают ИСТИНА и ЛОЖЬ. 

Предположим, вы ввели следующую формулу:

=И(A1=B1; C1>=500)

В этой формуле Excel определяет, равны ли друг другу значения ячеек A1 и B1. Если равны, возвращает ИСТИНА, если нет – ЛОЖЬ. Затем Excel вычисляет второе логическое значение и возвращает ИСТИНА, если содержимое ячейки С1 больше или равно 500, иначе возвращает ЛОЖЬ.

После оценки логических значений, функция И сравнивает их и возвращает ИСТИНА, если оба аргумента имеют значение ИСТИНА. Однако, если хотя бы один аргумент равен ЛОЖЬ, функция И возвращает ЛОЖЬ.

Как работает функция ЕСЛИ?

Функция ЕСЛИ (часто используемая в программировании) дает Excel первые задатки разумного существа, которое может выбирать на основании полученных данных. 

В функции ЕСЛИ используется, так называемое, логическое выражение, от результата которого будет зависеть выбор Excel в пользу  значение_если_истина или значение_если_ложь. Логическое выражение чаще всего использует операторы сравнения, которое возвращает либо ИСТИНА, либо ЛОЖЬ. Соответственно при логическом выражении равным ИСТИНА, Excel вернет значение, которое было установлено в аргументе значение_если_истина, в противном случае — значение_если_ложь.

Рассмотрим следующую формулу, которая в зависимости от значения в ячейке с условием, определяет по какой формуле выдавать результат:

=ЕСЛИ(E5=»Да»;D5+D5*7.5%;D5)

Если значение ячейки E5 равняется Да, функция ЕСЛИ будет использовать аргумент значение_если_истина, которое говорит Excel, чтобы он увеличил Значение на 7,5%. Если ячейка E5 будет пустой или будет содержать что-нибудь отличное от Да, функция ЕСЛИ будет использовать аргумент значение_если_ложь, которое вернет значение ячейки D5 без изменений.

Т.е. аргументы значение_если_истина и значение_если_ложь функции ЕСЛИ могут содержать константы или выражения, результаты вычислений которых будут возвращены в ячейку с формулой ЕСЛИ.

Несколько условий ЕСЛИ в Excel

Функция ЕСЛИ в Excel позволяет оценивать ситуацию с двух точек зрения, например, значение больше 0 или меньше, и, в зависимости от ответа на этот вопрос, произвести дальнейшие расчеты по той или иной формуле. Однако не редки ситуации, когда вам приходится работать более чем с двумя условиями. 

Рассмотрим примеры создания формул в Excel с несколькими условиями ЕСЛИ.

Принцип создания формул с несколькими условиями ЕСЛИ заключается в том, что в одном из аргументов формулы (значение_если_ИСТИНА или значение_если_ЛОЖЬ) находится еще одна формула ЕСЛИ.

Например: =ЕСЛИ(A5=0;»НОЛЬ»;ЕСЛИ(A5<0;»МЕНЬШЕ НОЛЯ»;»БОЛЬШЕ НОЛЯ»)), где функция оценивает значение ячейки A5 два раза, первый, проверяет, равняется ли значение нулю, и возвращает текст – НОЛЬ, если ИСТИНА. Если результат оценки вернул значение ЛОЖЬ, происходит вторая оценка, функция проверяет, является ли значение ячейки A5 меньше ноля, и возвращает текст МЕНЬШЕ НОЛЯ, если результат ИСТИНА, в противном случае возвращает текст БОЛЬШЕ НОЛЯ.

Таким образом, в примере выше, формула вернет значение МЕНЬШЕ НОЛЯ, так как при первой оценке, результат оказался ЛОЖЬ, а при второй оценке ИСТИНА.

Давайте рассмотрим пример посложнее. Предположим, вам необходимо рассчитать размер комиссии каждого продавца в зависимости от объема его продаж.

Ваш план таков:

• Если продажи меньше или равны 500$, комиссия составляет 7%
• Если продажи больше 500$, но меньше или равны 750%, комиссия составляет 10%
• Если продажи больше 750$, но меньше или равны 1000%, комиссия составляет 12,5%
• Если продажи больше 1000$, комиссия составляет 16%

Вместо того, чтобы рассчитывать размер комиссии для каждого работника, можно создать формулу с несколькими условиями ЕСЛИ. 

Логика формулы будет следующая:

1. Продажи меньше или равны 500$. Если ИСТИНА, рассчитываем комиссию.
2. Если ЛОЖЬ, то продажи меньше или равны 750$. Если ИСТИНА, рассчитываем комиссию.
3. Если ЛОЖЬ, то продажи меньше или равны 1000$. Если ИСТИНА, рассчитываем комиссию.
4. Если ЛОЖЬ, рассчитываем комиссию, так как это будет означать, что продажи больше 1000$ и больше логических тестов проводить не нужно.

Давайте создадим формулу следуя данной логике для продавца Сергея. 

=ЕСЛИ(B4<400;B4*7%;ЕСЛИ(B4<750;B4*10%;ЕСЛИ(B4<1000;B4*12.5%;B4*16%)))

На первый взгляд может показаться, что это ужасная формула, но давайте попробуем разобраться:

Логическое выражение в первой формуле ЕСЛИ проверяет, является ли значение в ячейке B4 меньше 400, если ИСТИНА, формула умножает значение ячейки B4 на 7% и останавливает дальнейшие вычисления. Если значение ячейки B4 больше 400, мы переходим к следующей функции ЕСЛИ. Так будет продолжаться, пока мы не достигнем последнего значения, где значение ячейки умножается на 16%. Это значит, что ни одно из условий не удовлетворило требованиям, т.е. продажи составляют более 1000$.

Ниже вы видите, как будет выглядеть колонка Комиссия, когда все формулы будут введены. Также в колонке Формула отображены формулы для каждого продавца.

Можно проверить на примере Натальи правильность работы формулы. 

Продажи Натальи составили 844$, т.е. больше, чем 750$, но меньше чем 1000$. Соответственно, коэффициент комиссии будет равняться 12,5%, а сама комиссия составит 105,5$. Также важно отметить, о работе формулы с пограничными значениями. Предположим, что сумма продаж Натальи составила 750$, какой коэффициент должна применить формула? Коэффициент будет 12,5%, так как для коэффициента 10% сумма продаж должна равняться меньше 750. 

Функция ЕСЛИ очень мощный инструмент при составлении логических выражений с несколькими условиями и позволяет экономить время на просчет каждой ячейки таблицы.

Диаграммы в Excel

Результаты практической работы "Диаграммы в Excel":

Фамилия, имя	Оценка
Искендеров	9
Куприенко	9
Савенкова	9
Андриенко, Куц	9
Кудряшова, Ищенко, Зима	11
Болгарова, Капуста	7

Загрузите копию рабочей книги "Бюджет семьи". Заполните ячейки формулами, используя функцию суммы и абсолютные ссылки на ячейки. Предоставьте ячейкам в столбце Процент формат Процентный.

Зачем нужны диаграммы?

В большинстве случаев трудности при восприятии таблиц Excel возникают из-за обилия информации. К счастью, Excel располагает замечательный инструмент визуализации данных - построение диаграмм.

Диаграммы в Excel способны графически представлять данные, позволяет легче воспринимать большие объемы информации и оценивать ее. Excel предлагает самые разнообразные типы диаграмм. Среди этого многообразия можно подобрать именно ту диаграмму, которая наиболее точно подходит под ваши потребности. Чтобы использовать диаграммы максимально эффективно, необходимо знать, как ими пользоваться в Excel.

Какими могут быть диаграммы?

Excel располагает большое разнообразие типов диаграмм, каждый из которых имеет свои преимущества.

Гистограмма

Гистограмма - это один из самых распространенных типов диаграмм. Гистограммы используют вертикальные столбики для представления данных. Их можно применять в самых различных ситуациях, но чаще всего они используются для сравнения значений.

В идеале таблица данных для этих типов диаграмм занимает несколько строк и столбцов, причем все ячейки диапазона должны быть заполнены. Также все столбцы и строки таблицы данных должны быть подписаны, чтобы Excel автоматически сгенерированный подписи осей координат на диаграмме.

График

Графики, как и гистограммы, также очень популярны. Графики идеальные в отражении изменения непрерывных данных, а также для демонстрации трендов. Точки на графике соединяются линиями, позволяя увидеть динамику с течением времени. Графики легко воспринимаются и могут составлять до 25% всех диаграмм в документах.

Круговая диаграмма

Круговые диаграммы подходят для демонстрации пропорций, то есть части чего-то по целому. Каждое значение представлено в виде доли (сектора) от суммы всех значений (круга). Круговая диаграмма строится для одного ряда данных и, как правило, содержит до 5-8 секторов. Такой подход очень полезен, когда нужно сравнить данные друг с другом.

Значения, которые используются для построения круговой диаграммы, должны быть положительными. В противном случае Excel преобразует их в положительные, автоматически отбросив знак "минус".

Круговые диаграммы способны отразить только один ряд данных, поэтому макет исходной таблицы несколько отличается. Допускается наличие только одного столбца с данными, при этом заголовки этого столбика и всех строк обязательно должны присутствовать.

Из каких элементов состоит диаграмма?

Диаграммы в Excel содержат пять основных элементов:

Пример диаграммы с обозначенными элементами

• Заголовок диаграммы должен четко описывать, что представлено на ней
• Вертикальная ось (также известная как ось Y) является вертикальной частью диаграммы. На вертикальной оси отображаются значения столбцов, поэтому ее называют осью значений. В примере выше величиной измерения чистая выручка от продаж каждого продавца.
• Ряд данных состоит из связанных точек (значений) на диаграмме. В примере выше синие столбцы отражают выручку от продаж Роберта привального. Мы понимаем, что выручка относится именно к этому продавцу, благодаря легенде в правой части диаграммы. Анализируя ряды данных, можно увидеть, что Роберт был лучшим продавцом в первом и третьем квартале и вторым во втором и четвертом.
• Легенда указывает на принадлежность каждого ряда к кому-либо или чему-либо. В примере легенда содержит 3 цвета с соответствующими продавцами. По легенде достаточно легко определить, какому из продавцов принадлежит каждый из столбцов.
• Горизонтальная ось (также известная как ось X) является горизонтальной частью диаграммы. Горизонтальная ось представляет категории. В примере каждый квартал содержит свою группу.

Задание для самостоятельной работы

Восстановите таблицу данных "Соотношение городского и сельского населения Украины" по диаграмме: