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

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

Нам нужно:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Файл с примером: Нарастающий итог SCAN.xlsx

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

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

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

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

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

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

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

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

В общем виде:

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