Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Работа с датами
Дальше: Вычисления с проверкой условий. Функции СУММЕСЛИМН / SUMIFS и другие
Логические выражения и функция ЕСЛИ / IF
Файл с примерами: Логические выражения.xlsxФункция ЕСЛИ / IF делает следующее: проверяет логическое выражение (это ее первый аргумент) и возвращает в зависимости от его значения (а логическое выражение может принимать только два значения — ИСТИНА / TRUE или ЛОЖЬ / FALSE) то или иное значение (второй и третий аргумент).
Проще говоря, ЕСЛИ позволяет проверить условие и в зависимости от его выполнения выдать тот или иной результат.
В логических выражениях используются знаки сравнения: «равно» (=), «не равно» (<>), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=).
Например:
=A1="Возврат"
Такое выражение будет возвращать ИСТИНА, если в ячейке A1 находится текст «Возврат» (в любом регистре!).
Обратите внимание: первый знак «равно» всегда означает начало формулы, а второй — это знак сравнения.
Если нужно сравнивать с учетом регистра, используйте функцию EXACT / СОВПАД — она возвращает ИСТИНА только тогда, когда два ее аргумента равны друг другу, включая регистр символов.
=B1>=A1
Такое выражение будет возвращать ИСТИНА / TRUE, если число в ячейке B1 больше (либо равно) числа в ячейке A1.
=A2>10000
Это выражение будет истинным, если число в A2 строго больше десяти тысяч.
Также есть функции для проверки данных, которые возвращают только ИСТИНА или ЛОЖЬ. Например, ЕПУСТО / ISBLANK будет возвращать ИСТИНА, если ячейка (аргумент) пустая, а ЕТЕКСТ / ISTEXT — если в ней текстовое значение.
Такие выражения и функции можно использовать в первом аргументе функции ЕСЛИ / IF — функции, которая позволяет проверять условия и возвращать одно или другое значение. Они проверяются, и в зависимости от выполнения условия ЕСЛИ возвращает второй или третий аргумент:
=ЕСЛИ(условие; значение при выполнении условия; значение при невыполнении условия)
Например, мы хотим автоматически (формулой) формировать скидку в определенных случаях: если стоимость позиции выше какой-то планки, допустим 300 000. Тогда в общем виде функция ЕСЛИ будет выглядеть так:
=ЕСЛИ(Ячейка со стоимостью > 300000; Стоимость * % скидки; 0)
Если первый аргумент истинный, то есть стоимость выше заданного уровня (из ячейки или в формуле), то функция будет возвращать произведение стоимости и процент скидки (он тоже может быть указан в самой формуле или в ячейке). Иначе — ноль.
«Пограничную» стоимость для получения скидки и процент скидки можно указывать в отдельных ячейках — тогда их можно будет легко поменять и получить в таблице обновленный результат, не трогая формулы. Главное в таком случае — не забыть закрепить их, сделав ссылки абсолютными.
НЕСКОЛЬКО УСЛОВИЙ: ФУНКЦИИ И / AND, ИЛИ / OR
Если нужно проверить выполнение сразу нескольких условий, используйте функцию И / AND. Она возвращает ИСТИНА только в том случае, если сразу все ее аргументы (два или более логических выражений) истинные.
Допустим, мы даем скидку только тем, кто покупал у нас электронную библиотеку, и только если клиент обратился к нам на выставке. Оба условия в разных столбцах должны выполняться одновременно, поэтому мы отправляем их внутрь функции И:
И([@Продукт]="Электронная библиотека";[@Канал]="Выставка")
Такая функция будет возвращать ИСТИНА только когда одновременно и продукт — библиотека, и канал продаж — выставка. Если истинный только один аргумент, на выходе будет ЛОЖЬ.
Эта функция нужна нам не сама по себе — она будет внутри ЕСЛИ в качестве логического выражения. В общем виде:
=ЕСЛИ(И(…); значение, если все условия в И выполняются; значение, если хотя бы одно не выполняется)
И в нашем примере:
=ЕСЛИ(И([@Продукт]="Электронная библиотека";[@Канал]="Выставка"); % Скидки * Стоимость; 0)
А если требуется выполнение хотя бы одного из условий, подойдет функция ИЛИ / OR. Она возвращает ИСТИНА / TRUE, если хотя бы один из ее аргументов истинный.
Если мы хотим давать одинаковую скидку за разные продукты, то ИЛИ будет лучшим решением:
ИЛИ([@Продукт]="Курс";[@Продукт]="Консультация")
Она будет возвращать ИСТИНА и в тех случаях, когда продукт — курс, и когда продукт — консультация.
По аналогии с И подставим ее в качестве первого аргумента ЕСЛИ:
=ЕСЛИ(ИЛИ(…); значение, если хотя бы одно условие в ИЛИ выполняется; значение, если ни одно не выполняется)
Очевидно, что одновременно оба условия в данном примере выполняться не могут — в ячейке может быть или что-то одно, или другое. Но если бы мы, например, давали скидку во всех случаях, когда покупают курс (столбец «Продукт») ИЛИ когда канал продаж — «Рассылка», мы тоже могли бы воспользоваться функцией ИЛИ:
ИЛИ([@Продукт]="Курс";[@Продукт]="Консультация")
НЕСКОЛЬКО УСЛОВИЙ: ВЛОЖЕННЫЕ ЕСЛИ
Если у нас несколько вариантов (например, разные скидки для разных категорий товаров), мы не можем ограничиться одной функцией ЕСЛИ. В такой ситуации обычно используют вложенные функции, которые строятся по следующей логике:
=ЕСЛИ(первое условие; значение, если выполняется первое условие; ЕСЛИ(второе условие; значение, если оно выполняется; третье условие))
То есть вторая функция ЕСЛИ выступает третьим аргументом первой — она вычисляется только тогда, когда первое условие не выполняется (возвращает ЛОЖЬ), и проверяет свое, второе условие.
Если и оно не выполняется, то возвращается последний аргумент второй функции ЕСЛИ — то значение, которое нужно вернуть «для всех остальных случаев», когда оба условия не выполняются.
Например, мы хотим выдавать скидку 10% при продаже курса, но 12% — при продаже консультации.
Тогда формула будет выглядеть так:
=ЕСЛИ([@Продукт]="курс";10%*[@Сумма];ЕСЛИ([@Продукт]="консультация";12%*[@Сумма];0))
Если условий больше, то, соответственно, придется добавить еще функции ЕСЛИ.
В Excel 2003 допускалось максимум 7 уровней с вложенными функциями ЕСЛИ, а начиная с 2007 — до 64 уровней! Но лучше избегать таких массивных конструкций и при таком разветвлении рассмотреть альтернативы.Это могут быть функции ЕСЛИМН / IFS или ПЕРЕКЛЮЧ / SWITCH, если они есть в вашей версии. О них мы поговорим прямо сейчас.Либо можно воспользоваться функцией ВПР / VLOOKUP или другой, которая поможет тянуть данные из вспомогательной таблицы (где будут перечислены условия и соответствующие им проценты или значения). Про ВПР читайте в соответствующей главе ().Если вы предполагаете, что книгу могут открывать ваши коллеги или контрагенты с версиями Excel 2016, 2013 или более старыми, лучше использовать вложенные ЕСЛИ или ВПР, доступные во всех версиях.
ФУНКЦИИ ЕСЛИМН / IFS И ПЕРЕКЛЮЧ / SWITCH
Файл с примерами: Функции ЕСЛИМН и ПЕРЕКЛЮЧ.xlsxЕсли у вас Excel от версии 2019 или Google Таблицы, можно воспользоваться одной из этих функций.
Функция ЕСЛИМН / IFS позволяет проверять много условий, а не одно, как в ЕСЛИ.
Синтаксис функции:
=ЕСЛИМН(логическая_проверка1; если_значение_истина1; [логическая_проверка2];[если_значение_истина2];…)
логическая_проверка1 (logical_test1) — первое условие, может быть истинным или ложным (равенство, неравенство). Как логическое выражение в ЕСЛИ / IF.
если_значение_истина1 (value_if_true1) — что будет возвращать функция ЕСЛИМН при выполнении первого условия.
[логическая_проверка2] — второе условие.
[если_значение_истина2] — что будет возвращать функция ЕСЛИМН при выполнении второго условия.
И так далее, пар «условие — значение» может быть и больше.
Эта функция может быть удобнее, когда у вас есть много вариантов, для каждого из которых нужно возвращать разное значение. Например, разный процент скидки/комиссии по разным продуктам.
Например, следующая функция будет возвращать скидку в размере 5% от суммы, если продукт — курс, а 7% — если консультация:
=ЕСЛИМН([@Продукт]="Курс";5%*[@Сумма];[@Продукт]="Консультация";7%*[@Сумма])
Обратите внимание: если в «обычной» функции ЕСЛИ / IF есть вариант «значение_если_ложь» (что возвращает функция при невыполнении условия), то у ЕСЛИМН есть только пары «условие — что возвращать, если оно выполняется».
То есть если какой-то вариант в функции не прописан, то будет возникать ошибка.
Электронной библиотеки среди условий нет — возникает ошибка #Н/Д (#N/A).
Избежать ошибки можно с помощью функции ЕСНД / IFNA. Она позволяет в случае возникновения ошибки #Н/Д возвращать другое значение. Например, ноль или ничего.
Другой вариант: указать в качестве последнего аргумента ИСТИНА (то есть априори верное логическое выражение) и затем — то значение, которое необходимо возвращать в случае, если все предыдущие условия не выполняются. Функция сначала проверяет все условия по порядку и, если ни одно не является истинным, доходит до последнего, которое истинно в любом случае, поскольку так задано нами сознательно.
ФУНКЦИЯ ПЕРЕКЛЮЧ / SWITCH
В Excel эта функция появилась в 2017 году (то есть в Office 365 и с версии 2019).
В Google Таблицах она также есть и называется SWITCH даже при русском языке формул.
=ПЕРЕКЛЮЧ (выражение; если_значение_истина1; [логическая_проверка2];[если_значение_истина2];…)
выражение (expression) — выражение (любое значение, например текст или дата), которое будет сверяться со списком аргументов (значение1, значение2 и так далее).
значение1 (value1) — первое значение, с которым будет сравниваться выражение.
результат1 (result1) — результат, который будет возвращаться, если выражение совпадает с первым значением.
[по_умолчанию_или_значение2] (default_or_value2) — второе значение, с которым будет сравниваться выражение.
[результат2] (result2) — результат, который будет возвращаться, если выражение совпадает со вторым значением. Если результат для второго или любого последующего значения не указан, то это значение будет возвращаться для всех остальных случаев.
Первый аргумент функции ПЕРЕКЛЮЧ — выражение (значение из ячейки, которое может быть не только числом, но и текстом), а все последующие — это пары «значение — результат». Последний аргумент — результат для всех остальных случаев.
В отличие от функций ЕСЛИ, ЕСЛИМН, здесь не нужно формировать много логических выражений. Если мы хотим сравнить значение из ячейки с какими-то значениями, достаточно поставить ссылку на ячейку в первом аргументе и далее указывать значения, с которыми мы ее сравниваем.
Например, можно задать разную скидку для трех каналов продаж, а для остальных (последний аргумент, который остается непарным) — нулевую:
=ПЕРЕКЛЮЧ(ячейка с каналом продаж;"Сайт";15%;"Рассылка";12%;"Выставка";10%;0)
