Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

В 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) — результат, который будет возвращаться, если выражение совпадает с первым значением.