Функция ЕСЛИ в Excel
Без преувеличения можно сказать, что функция ЕСЛИ в excel является одной из наиболее часто встречаемых и часто используемых. Работа экономиста в excel немыслима без знания этой функции.
Функция ЕСЛИ относиться к логическим функциям MS Excel, и позволяет создавать весьма затейливые конструкции, основываясь на проверке условия и выборе из двух и более альтернатив.
С использованием функции ЕСЛИ в excel можно строить ветвящиеся алгоритмы, строить дерево решений и другие системы и формулы используя вложение одной функции ЕСЛИ в другую и т.д. В Excel 2010 можно использовать до 64 вложений. Это позволяет построить поистине грандиозный алгоритм вычислений.
В общем виде функция ЕСЛИ в excel имеет следующий синтаксис:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Функция возвращает одно из двух значений в звисимости от того, какое значение принимает условие (лог_выраение). Если условие принимает значение ИСТИНА, то функция ЕСЛИ возвращает значение_если_истина, а если условие принимает значение ЛОЖЬ, то возвращается значение_ели_ложь.
Рассмотрим синтаксис данной функции на примере алгоритма вычисления суммы с НДС или без НДС.
Лог_выражение – это может быть любое значение или выражение (формула) которое принимает значение ИСТИНА или ЛОЖЬ. Так, например, С5=“с НДС” – это логическое выражение. Если в ячейке С5 находиться текст «с НДС», то логическое выражение принимает значение ИСТИНА, в противном случае – ЛОЖЬ, например, если там содержится текст «без НДС».
Значение_если_истина — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Так, например, если этот аргумент равен формуле С12*1,18, то при значении логического выражения равному ИСТИНА, вычисления ведется по этой формуле.
Если аргумент «лог_выражение» имеет значение ИСТИНА, а аргумент «значение_если_истина» не задан, возвращается значение 0 (ноль).
Значение_если_ложь — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ. Так, например, если этот аргумент равен формуле С12*1 или просто С12, то при значении логического выражения равному ЛОЖЬ, вычисления ведется по этой формуле, т.е., как в нашем примере, берется просто значение суммы из ячейки С12
Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» пуст (т. е. после аргумента «значение_если_истина» стоит точка с запятой, а за ней — закрывающая скобка), то возвращается значение 0 (ноль).
Рассмотрим наиболее часто встречающиеся в работе экономиста и при финансовом моделировании ситуации, в котором нам поможет использование функции ЕСЛИ.
Пример 1. Использование текстовых значений.
Сразу хочу обратить ваше внимание, что при использовании текстовых выражений в формулах, необходимо заключать их в кавычки “текст” для корректной работы формул.
Например, нам надо добавить какой либо текстовый признак или комментарий, в зависимости от значения той или иной ячейки.
Создаем формулу с использованием функции ЕСЛИ:
=ЕСЛИ(Р20>1000;“превышение лимита”;“в рамках лимита”)
Скопировав эту формулу по всему проверяемому диапазону, мы можем теперь легко отфильтровать значения превышающие лимит, по созданному нами критерию.
Пример 2. Скрытие значений. Скрытие нулевых значений.
Практически повсеместно встречающаяся ситуация – это возникновение ошибки #ДЕЛ/0! при подготовке исходных форматов. Мы готовим исходный формат, в котором в одном из столбцов вычисляем темп роста одного показателя к другому. Как известно, для этого используется формула А/В*100%, но поскольку у нас еще нет данных, то возникает ошибка при делении значений из столбца А на нулевые значения из столбца В.
Чтобы этого избежать, используем, как один из вариантов функцию ЕСЛИ.
=ЕСЛИ(В10=0;0;А10/В10) или =ЕСЛИ(В10=0;””;А10/В10)
В первом случае будет вместо ошибки #ДЕЛ/0! будет возвращено значение 0, а во втором случае будет просто пустая ячейка. Двойные кавычки возвращают пустое значение в ячейку.
Другой пример, проверка сходимости баланса. В этом случае, нам надо сравнить сумму актива и сумму пассива баланса, и в случае если они равны, т.е. например, С85-С160=0, нам надо скрыть нулевое значение, а в случае если есть расхождение – отобразить это значение.
=ЕСЛИ(С85-С160=0;””; С85-С160)
Пример 3. Многоуровневые, вложенные вычисления.
Очень часто в финансовом моделировании встречается ситуация, когда необходимо построить алгоритм вычисления конечного значения в зависимости от нескольких различных значений исходного логического выражения.
Опять нам не обойтись без функции ЕСЛИ. Причем здесь мы уже будем вкладывать ее одну в другую, и создавать ветвящийся алгоритм.
Типичный пример, расчет скидки в зависимости от нескольких уровней объема продаж. Или заработка, в зависимости от нескольких уровней выработки.
Предположим у нас следующая матрица скидок:
До 100 000 рублей – 0%
От 100 001 до 300 000 рублей – 3%
От 300 001 до 500 000 рублей – 5%
Свыше 500 001 – 7%
Оговорюсь сразу, размеры скидок – условный пример. Их расчет зависти от соотношения переменных расходов и выручки. И основная цель – увеличить общую маржу по реализованной продукции.
Создаем формулу используя функцию ЕСЛИ:
Предположим, в ячейке D10 у нас находиться объем продаж.
=ЕСЛИ(D10>=500001;D10*0,93;ЕСЛИ(D10>=300001;D10*0,95;ЕСЛИ(D10>=100001;D10*0,97;D10)))
Эта формула в зависимости от уровня объема продаж устанавливает тот или иной уровень скидок. Количество уровней вложения и соответственно уровней скидок может быть гораздо больше.
Операторы сравнения.
В логических выражениях для сравнения значений и формул можно использовать следующие операторы сравнения:
= Равно
> Больше
< Меньше
>= Больше или равно
<= Меньше или равно
<> Не равно
Использование операторов сравнения позволяет более гибко использовать функцию ЕСЛИ в excel для решения прикладных экономических задач.
В примере №3 мы как раз использовали оператор >= больше или равно для задания диапазона ссылок.
В дополнение к функции ЕСЛИ в excel можно использовать другие логические функции, еще более расширяя возможности построения сложных алгоритмов и повышая гибкость вычислений.
Функции И, ИЛИ, НЕ
Функция И в excel имеет следующий синтаксис:
И(логическое_значение1, [логическое_значение2], …)
Функция возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ.
Функция ИЛИ в excel имеет аналогичный синтаксис:
ИЛИ(логическое_значение1, [логическое_значение2],…)
Однако возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА. Если все аргументы имеют значение ЛОЖЬ, то возвращается значение ЛОЖЬ.
Функция НЕ в excel имеет следующий синтаксис:
НЕ(логическое_значение)
Функция НЕ используется в тех случаях, когда необходимо убедиться, что значение не равно некой конкретной величине. Она возвращает значение ЛОЖЬ, если значение равно ИСТИНА и наоборот.
Пример 4. Использование нескольких условий.
Предположим, нам надо сделать отбор наших дебиторов, которые задолжали нам за 3 месяца сумму большую 100 000 рублей. Допустим, в этом случае формула должна выводить критерий «злостный задолжник».
В ячейке В10 у нас будет срок задолженности в месяцах, а в ячейке С10 сумма задолженности.
Тогда формула будет иметь следующий вид:
=ЕСЛИ(И(В10>=3;С10>100000);»злостный задолжник»;»»)
Если мы считаем критичным либо задолженность старше 3 месяцев, либо свыше 100 000 рублей, то формулу можно записать так:
=ЕСЛИ(ИЛИ(В10>=3;С10>100000);»критичная задолженность»;»»)
В общем, как вы сами понимаете, возможности применения функции ЕСЛИ в excel безграничны.
Самое главное – это продумать алгоритм вычислений, а уж инструментария в MS Excel для его реализации вполне достаточно.
Microsoft Excel содержит дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа вхождений текстовой строки или числа в диапазоне ячеек можно использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, можно использовать функции СУММАЕСЛИ и СУММЕСЛИМН.
Об этих функциях мы поговорим в следующий раз.
Comments are currently closed.