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

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)

Вычисления с проверкой условий. Функции СУММЕСЛИМН / 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;"выставка")

Если мы хотим получить количество