Магия таблиц. 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[Выручка] — ссылка на столбец с
Поделиться книгой в соц сетях:
Обратите внимание, что комментарий должен быть не короче 20 символов. Покажите уважение к себе и другим пользователям!