«Функции для поиска и извлечения значений»).
Если вы предполагаете, что книгу могут открывать ваши коллеги или контрагенты с версиями Excel 2016, 2013 или более старыми, лучше использовать вложенные ЕСЛИ или ВПР, доступные во всех версиях.
Файл с примерами: Функции ЕСЛИМН и ПЕРЕКЛЮЧ.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. Она позволяет в случае возникновения ошибки #Н/Д возвращать другое значение. Например, ноль или ничего.
Другой вариант: указать в качестве последнего аргумента ИСТИНА (то есть априори верное логическое выражение) и затем — то значение, которое необходимо возвращать в случае, если все предыдущие условия не выполняются. Функция сначала проверяет все условия по порядку и, если ни одно не является истинным, доходит до последнего, которое истинно в любом случае, поскольку так задано нами сознательно.
В 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)
Вычисления с проверкой условий. Функции СУММЕСЛИМН / SUMIFS и другие
Файл с примерами: Расчеты с условиями.xlsx
Есть четыре базовые функции для подсчетов без условий: СУММ / SUM (сумма аргументов), СЧЁТ / COUNT (количество чисел) и СЧЁТЗ / COUNTA (количество любых значений), СРЗНАЧ / AVERAGE (среднее арифметическое аргументов).
А есть функции с теми же вычислениями, позволяющие суммировать/подсчитывать/усреднять, но не все ячейки, а только удовлетворяющие определенным условиям.
У этих функций несколько аргументов: диапазон суммирования или усреднения (что суммируем или усредняем; такие аргументы есть у СУММЕСЛИ(МН) и СРЗНАЧЕСЛИ(МН)), диапазоны условий (где ищем) и условия (что ищем).
Важно, чтобы все диапазоны условий и диапазоны суммирования/усреднения были одинаковой размерности. Это могут быть и столбцы целиком (E: E), и диапазоны (E2:E40), и столбцы таблиц (Название_таблицы[Столбец]). Например, если один аргумент — это столбец целиком (D: D), то и другой должен быть в таком же формате (такого же размера — E: E, а не E2:E120).
Функции с окончанием ЕСЛИ / IF позволяют проводить вычисления с одним условием, а функции ЕСЛИМН / IFS — как с одним, так и с несколькими. Все функции — в таблице ниже.
Например, нам нужна сумма продаж по одному каналу продаж — рассылке.
Нужна сумма — значит, мы можем использовать СУММЕСЛИ или СУММЕСЛИМН (условие одно, поэтому подойдут обе функции). Ссылаться можно на столбцы с суммами и с каналами продаж целиком:
=СУММЕСЛИМН(E: E; D: D; I2)
Либо на диапазоны. Условие может задаваться как в виде ссылки на ячейку, где оно указано, так и в кавычках (регистр значения не имеет).
=СУММЕСЛИМН(E2:E50; D2:D50; "рассылка")
Либо на столбцы таблицы:
=СУММЕСЛИМН(Сделки[Сумма];Сделки[Откуда];I2)
Главное, чтобы размеры диапазонов были одинаковыми и чтобы они были параллельны (то есть чтобы диапазон суммирования не начинался, например, с первой строки, а диапазон условия — со второй). В случае со столбцами целиком и с таблицей мы предполагаем, что в диапазоне могут появиться новые данные — все строки, добавленные внизу, в таком случае попадут в расчет. Но формулы с таблицами работают быстрее, так что это предпочтительный вариант.
Если условий несколько, мы перечисляем их попарно с их диапазонами — порядок условий значения не имеет:
=СУММЕСЛИМН(E2:E49;D2:D49;"сайт";B2:B49;"курс")
У функций СРЗНАЧЕСЛИ / AVERAGEIF и СРЗНАЧЕСЛИМН / AVERAGEIFS синтаксис идентичный — только вместо диапазона суммирования тут диапазон усреднения. В следующем примере считаем среднюю продажу через выставку (соответствующее значение в столбце D):
=СРЗНАЧЕСЛИМН(E: E;D: D;"выставка")
Если мы хотим получить количество строк, удовлетворяющих одному или нескольким условиям, то используем функцию СЧЁТЕСЛИ / COUNTIF или СЧЁТЕСЛИМН / COUNTIFS. Работает она аналогично, только диапазона суммирования/усреднения у нее нет — мы ничего не суммируем, а только подсчитываем, сколько строк удовлетворяют заданным условиям:
=СЧЁТЕСЛИМН(B2:B49;"электронная библиотека")
Общие правила записи условий в этих функциях:
• в них используются знаки сравнения: «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=), «не равно» (<>);
• если вы ищете точное совпадение с текстовым значением, а не сравниваете числа и даты, то эти знаки не нужны; просто укажите текст в кавычках или дайте ссылку на ячейку с текстом;
• условие берется в кавычки;
• можно ссылаться на ячейки с условиями (в таком случае нужно объединять знаки в условии со ссылкой на ячейку через амперсанд &) или указывать условия прямо в формуле (обратите внимание, что условия, как любые текстовые значения в формулах, указываются в кавычках).
Вот как записываются условия на разные типы данных.
Обратите внимание, что в условиях всех функций …ЕСЛИМН / …IFS регистр не учитывается, то есть вы можете ввести условие и как "МОСКВА", и как "Москва", и как "москва" — в любом случае все ячейки, в которых это слово встречается (и тоже в любом регистре), попадут в расчет.
В условиях функций можно использовать два символа подстановки — * (звездочка) и ? (знак вопроса):
* — текстовая строка любой длины, включая нулевую (то есть на месте звездочки может быть любой текст или не быть вообще ничего);
? — один любой символ (на месте знака вопроса не может быть ничего, это строго одна позиция в тексте, занятая пробелом, буквой, цифрой, символом).
Так, например, если мы хотим просуммировать продажи книг одного автора в следующей таблице, нам не подойдет стандартное условие, потому что у авторов есть разные книги, а в столбце с названием есть и название произведения, и имя автора.
И если нам нужно подсчитать продажи книг Роулинг, например, нам необходимо добавить по звездочке слева и справа от фамилии автора в условии функции:
=СУММЕСЛИМН(C: C;A: A;"*Роулинг*")
Это условие — любой текст (в том числе ничего, текст нулевой длины) + Роулинг (в любом регистре, напомним) + любой текст.