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

[по_умолчанию_или_значение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;"электронная библиотека")

Общие правила записи условий в этих функциях: