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

СТОЛБЕЦ()-1 — здесь мы просто берем номер столбца, в котором стоит формула, и уменьшаем на единицу, чтобы получить номера столбцов на листе с данными (у нас там на один столбец меньше; так как другая структура данных — нет списка листов в первом столбце; понятно, что у вас структура может быть какая-то еще).

Остается просуммировать (СУММ / SUM; если, конечно, вам нужна сумма, а не среднее или что-то еще) все полученные значения, которые ВПР / VLOOKUP нам принесет со всех листов:

=СУММ(MAP($A$2:$A20;

LAMBDA(sh;

ЕСЛИ(ЕПУСТО(sh);0;

ВПР($B2;ДВССЫЛ("'"&sh&"'!A: N");СТОЛБЕЦ()-1;0)))))

ФУНКЦИЯ BYROW: ОБРАБАТЫВАЕМ КАЖДУЮ СТРОКУ МАССИВА

Файл с примером: BYROW.xlsx

Функция BYROW позволяет последовательно обращаться к каждой строке в массиве.

Ее синтаксис:

=BYROW(диапазон; LAMBDA (переменная для обращения к каждой строке); вычисление с этой переменной))

То есть мы можем производить вычисления не с отдельной ячейкой и не со всем массивом, а с каждой строкой последовательно (на выходе мы получим массив значений в один столбец и с таким же числом строк, сколько в исходном диапазоне, — это будет результат вычисления с каждой строкой). Де-факто получается цикл — мы обрабатываем каждую строку последовательно.

Аналогично работает функция BYCOL — там, соответственно, будет обрабатываться каждый столбец массива.

Давайте посмотрим на пример. У нас диапазон C2:N13, в котором есть продажи за 12 месяцев. Посчитаем средние продажи в каждой строке одной формулой:

=BYROW(C2:N13;LAMBDA(строка;СРЗНАЧ(строка)))

Конечно, мы могли бы просто протянуть функцию =СРЗНАЧ(C2:N2) на несколько строк. Но этот пример показывает нам, как работает BYROW

Теперь давайте решим другую задачу: нам нужно выводить те продукты (сочетание: какой курс и какой формат — вебинары или саммари), у которых средняя выручка за год — выше 1500. Например, Магия Excel 2 + саммари — нужно выводить (среднее — 1522), а Как выбрать корм 2.0 + вебинары — нет (среднее — 1459).

Чтобы получать комбинацию «Курс — Формат», объединим два столбца амперсандом и добавим между ними разделитель, например пробел: A2:A13&" "&B2:B13

А чтобы вывести из этого виртуального (виртуального, потому что мы не будем, конечно, выводить его в ячейки отдельно в итоговой формуле, как выше, — это сделано лишь для того, чтобы мы посмотрели, как выглядит промежуточный результат) списка только те значения, которые соответствуют нашему условию (среднее выше 1500), применим функцию ФИЛЬТР. В общем виде так:

=ФИЛЬТР(A2:A13&" "&B2:B13; Среднее > 1500)

Ну а на деле, чтобы получить среднее по каждой строке, используем BYROW, как делали это выше:

=ФИЛЬТР(A2:A13&" " &B2:B13;

BYROW(C2:N13;LAMBDA(строка;СРЗНАЧ(строка)>1500)))

ФУНКЦИЯ REDUCE: ПОСЛЕДОВАТЕЛЬНО ОБРАБАТЫВАЕМ МАССИВ И ПОЛУЧАЕМ ОДНО ЗНАЧЕНИЕ В КАЧЕСТВЕ РЕЗУЛЬТАТА

Файл с примером: REDUCE.xlsx

Функция REDUCE применяет вычисление к каждому значению в массиве, как и MAP. Но на выходе возвращает только одно значение — «накопленный» результат, а не массив того же размера:

=REDUCE(начальное значение; массив; вычисление)

Вычисление — это функция LAMBDA, в которой первый аргумент — это нарастающий итог, накопленный результат, второй — это отдельное значение (на первом шаге — начальное значение, заданное в первом аргументе REDUCE, а далее — каждое очередное значение из массива), третий — вычисление:

=REDUCE(начальное значение; массив; LAMBDA(нарастающий итог; значение; вычисление))

В следующем примере мы обрабатываем десять ячеек с числами от 1 до 10 и на каждом шаге просто прибавляем к предыдущему шагу очередное значение. На выходе мы получаем одно значение (в отличие от SCAN, о которой ниже).

Пример применения REDUCE. Извлекаем из текстовой строки только цифры

Конечно, предыдущий пример не для практики, мы лишь рассмотрели синтаксис функции REDUCE, а на деле для решения задачи можно было просто использовать обычное суммирование.