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

Конечно, мы могли бы просто протянуть функцию =СРЗНАЧ(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 применяет вычисление к каждому значению в массиве, как и MAP. Но на выходе возвращает только одно значение — «накопленный» результат, а не массив того же размера:

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

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

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

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

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

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

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

Нам нужно:

1. Сделать массив из отдельных символов текста

Чтобы извлечь один любой символ, нужна функция ПСТР / MID. Напомним ее аргументы:

=ПСТР (текст; позиция извлекаемого символа; число символов)

Нам необходимо последовательно извлекать все символы по одному (третий аргумент ПСТР) от первого до последнего. Чтобы получить последовательность чисел от единицы до последнего символа в тексте, нужна функция ПОСЛЕД / SEQUENCE и нужно знать, сколько в каждом тексте символов (длину текстовой строки можно определить с помощью ДЛСТР / LEN):

=ПСТР (текст; ПОСЛЕД(1;ДЛСТР(текст)))

Формула, формирующая массив из отдельных символов текста

2. Далее необходимо пройтись по каждому символу и проверить, является ли он цифрой (числом)

Будем превращать его из текста (мы обсуждали эту тему — см. «Как превратить число в текстовом формате в настоящее число») в число с помощью ЗНАЧЕН / VALUE и проверять, будет ли полученное значение настоящим числом, с помощью ЕЧИСЛО / ISNUMBER.

ЕЧИСЛО(ЗНАЧЕН(символ))

Проверяем, является ли каждый символ (из массива с предыдущего скриншота) числом (де-факто цифрой) или нет.

3. Далее нужно будет собирать текст из всех найденных цифр: начинаем мы с «нулевого» текста (пустые кавычки) как первого значения в REDUCE, берем массив из всех символов нашего текста (это ПСТР (ПОСЛЕД)), проверяем каждый и те, что являются числами, приклеиваем к уже собранным предыдущим. Получится такая формула в общем виде:

=REDUCE("";массив из символов;

LAMBDA(число; символ;

ЕСЛИ(ЕЧИСЛО(ЗНАЧЕН(символ)); число&символ; число)))

И со всеми функциями и ссылками:

=REDUCE("";ПСТР(A2;ПОСЛЕД(1;ДЛСТР(A2));1);

LAMBDA(число; символ;

ЕСЛИ(ЕЧИСЛО(ЗНАЧЕН(символ)); число&символ; число)))

Итак, наша формула «пробегается» по массиву из отдельных символов, проверяет каждый функцией ЕЧИСЛО, и если эта функция возвращает ИСТИНА, то мы склеиваем собранную на предыдущих шагах последовательность цифр с этим символом (операция объединения во втором аргументе функции ЕСЛИ). Иначе пропускаем его (оставляем собранную последовательность как есть — последний аргумент функции ЕСЛИ).

ФУНКЦИЯ SCAN: ПОЛУЧАЕМ ПРОМЕЖУТОЧНЫЕ ИТОГИ ДЛЯ КАЖДОГО ЗНАЧЕНИЯ ИЗ МАССИВА

Функция SCAN работает как REDUCE, но возвращает массив, показывая все промежуточные значения, а не только итоговое.

REDUCE и SCAN могут принимать только один массив, а MAP — и несколько тоже.

Синтаксис функции похожий:

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

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

=SCAN(0; Выручка[Выручка]; LAMBDA(total; revenue; revenue + total))

Что, если мы хотим нарастающий итог по условию? Например, суммировать только те дни, в которые у нас работал Лемур.

Здесь нужна функция ЕСЛИ / IF, ведь мы будем проверять условие и либо прибавлять число, если в столбце B — «Лемур», либо оставлять накопленный итог без изменений.

В общем виде:

ЕСЛИ(Кто работал = "Лемур"; промежуточный итог + выручка; промежуточный итог)

Так как у нас в качестве обрабатываемого массива выступает столбец «Выручка», мы не можем напрямую в LAMBDA ссылаться на столбец «Кто работал»: его в SCAN нет. Но мы можем ссылаться на другие ячейки относительно ячеек обрабатываемого массива — тут пригодится функция СМЕЩ / OFFSET. Чтобы ссылаться на ячейку слева от заданной (в нашем случае это очередная ячейка массива), нам нужна такая конструкция (смещение на один столбец влево, «минус один» в аргументе СМЕЩ):

СМЕЩ(ячейка из массива;0;-1;1;1)

Вся формула будет выглядеть так. Здесь 0 — начальное значение; Выручка2[Выручка] — ссылка на столбец с