Формулы массива и новые функции (как СОРТ, ФИЛЬТР, ПОСЛЕД и другие) в Р7-Офис
В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами. При этом формулы массива в Р7 работают как “старые” формулы массива (возможно, это когда-нибудь изменится). Так что новыми функциями пользоваться не так удобно, как в новом Excel, но зато они в принципе есть, в отличие от Excel 2019, допустим 🙂
Про разницу в работе старых и новых формул массива в Excel можно узнать из этого видео — это один из 60 уроков курса "Магия Excel" в МИФе.
Итак, вот некоторые из функций, которые появились в Excel 2021/365 (благодаря динамическим массивам; тому, что формула, будучи в одной ячейке, возвращает результат больше одной ячейки) и работают в Р7:
— УНИК (возвращает уникальные значения или строки)
— СОРТ (сортирует диапазон/массив)
— ФИЛЬТР (фильтрует данные по одному или нескольким условиям)
— СЛУЧМАССИВ (возвращает массив случайных чисел)
— ВЫБОРСТОЛБЦ и ВЫБОРСТРОК (извлекают заданные столбцы или строки)
— ВСТОЛБИК и ГСТОЛБИК (объединяют массивы в один вертикально или горизонтально)
— ПОСТОЛБЦ и ПОСТРОК (превращают двумерный диапазон/массив в одномерный; таблицу в список, в общем)
— СВЕРНСТОЛБЦ и СВЕРНСТРОК (наоборот — одномерный диапазон/массив в двумерный)
— ПОСЛЕД (возвращает последовательность чисел с заданными параметрами)
— ТЕКСТПОСЛЕ, ТЕКСТДО, ТЕКСТРАЗД (извлекают текст до или после разделителя или разделяет текст на отдельные значения по разделителю)
Все эти функции в общем случае возвращают массив, а не одно значение — это предопределено их логикой — соответственно, им требуется несколько ячеек для вывода результата.
Как работают формулы массива в Excel
В Exce 2021/365 или Google Таблицах формулы массива можно вводить в одну ячейку, хотя результат может занимать несколько.
Из этого следует несколько нюансов:
— если формуле не хватит пустых ячеек для вывода результата — она не будет удалять и переписывать ваши данные; появится ошибка #SPILL! / #ПЕРЕНОС! в Excel или ошибка #REF! в Google Таблицах.
— вы не сможете удалить часть результата — удалить или изменить можно формулу только в одной (левой верхней в рамках выводимого диапазона) ячейке, куда вы ее вводили. В Excel вы будете видеть формулу в других ячейках в строке формул, но она будет выделена серым — редактировать тут ее нельзя. В Google Таблицах в других ячейках в строке формул будет значение, выводимое в этой ячейке.
Как работали старые формулы массива
Старые формулы массива в Excel работали по-другому — нельзя было ввести формулу в одной ячейке и получить результат за пределами этой ячейки. Формулы массива были, но вы должны были заранее выделить диапазон нужного размера и ввести формулу в этот диапазон нажатием клавиш Ctrl + Shift + Enter (про формулы массива даже есть книга с таким названием).
Вот на примере функции ТРАНСП / TRANSPOSE (которая транспонирует диапазон, меняет строки и столбцы местами, иначе говоря). В новой версии Excel или в Google Таблицах все просто: ввели функцию, сослались на диапазон, который нужно транспонировать, нажали Enter и готово):
В старой версии нужно понять, какого размера будет результат и заранее выделить диапазон такого размера и тогда вводить формулу:
И после этого не забыть нажать Ctrl + Shift + Enter!
На формулу массива укажут фигурные скобки. Вводить их вручную нельзя. Они появятся при вводе формулы с помощью этого сочетания:
Вот что случалось, если мы выделяли лишние ячейки при вводе старой формулы массива: ошибки #Н/Д (#N/A) в них.
И это может пригодиться нам в Р7 🙂 Где формулы массива работают как в старых версиях Excel.
P.S. Чтобы удалить новую формулу массива, достаточно выделить одну ячейку с формулой и нажать Delete. Для удаления старых формул (с фигурными скобками, введенных “в диапазон”) нужно выделить весь диапазон перед нажатием Delete.
Как новые функции и формулы массива работают в Р7
И вот в Р7-Офис получается некая комбинация: тут есть новые функции вроде ФИЛЬТР или УНИК, но формулы массива работают только в духе старого Excel— нам нужно сначала выделить диапазон и потом ввести формулу нажатием Ctrl + Shift + Enter.
Как быть? Есть несколько сценариев.
Вариант 1 — функция точно вернет заданное нами количество ячеек и оно не будет меняться — например ВЫБОРСТРОК.
В примере мы сортируем таблицу по шестому столбцу — это сумма сделки, а потом берем по две строки с начала и конца полученного отсортированного диапазона. Получаем две крупнейших и две самых маленьких сделки. Так как мы точно понимаем, что строк всегда в результате будет четыре (это задано в формуле в аргументах функции ВЫБОРСТРОК), то можем спокойно выделять четыре строки, столько столбцов, сколько в таблице, и вводить формулу через Ctrl + Shift + Enter:
Вариант 2 — выделяем диапазон с запасом, если размер результата будет меняться.
В большинстве случаев мы не будем знать, сколько значений/строк будет возвращать формула в будущем. Если добавятся уникальные значения, функция УНИК потребует больше ячеек; если в таблице добавятся строки, то ФИЛЬТР или СОРТ тоже будут возвращать результат большего размера.
Так что тут остается выделять строки с запасом и смириться, что пока что в “лишних” ячейках будут ошибки #Н/Д. Например, в следующем случае, когда мы выводим заголовки из первой строки и отфильтрованные строки по продукту “Консультация” из таблицы “Сделки”:
{=ВСТОЛБИК(A1:G1;ФИЛЬТР(Сделки;Сделки[Продукт]="Консультация"))}
(Функция ВСТОЛБИК “склеивает” несколько массивов вертикально)
Тут у вас может возникнуть вопрос — а не заменить ли нам эти #Н/Д с помощью функции вроде ЕСНД (которая проверяет наличие этой ошибки и если она есть — заменяет ошибку на другой аргумент). Но, увы, эта функция не будет работать с массивом 🙁
Поэтому мы применим другой трюк: создадим правило условного форматирования с функцией ЕНД — она проверяет наличие этой ошибки и возвращает ИСТИНА, если она есть. Сошлемся на первую ячейку нашего диапазона с формулой массива (в примере K2) и зададим прозрачный фон и белый шрифт. Тогда #Н/Д не будет видно:
Вариант 3 — на выходе одно значение. Вводим формулу в одну ячейку
Если на выходе ваша формула выдает одно значение — например, если у вас на верхнем уровне есть агрегирующая функция вроде СУММ или СЧЁТ, а внутри нее функции, возвращающие массив — то все просто, нажимать Ctrl + Shift + Enter для ввода не нужно, результат получим и так, и он априори будет занимать одну ячейку.
Например, суммируем отфильтрованные суммы (извините за такой повтор) — результат будет одним значением:
Агрегированное значение может быть и текстом, а не числом. В следующем примере мы объединяем в одну текстовую строку несколько адресов почты (отфильтрованных по отделу) с помощью функции ОБЪЕДИНИТЬ, чтобы потом превратить в ссылку на отправку письма этим сотрудникам:
=ОБЪЕДИНИТЬ("разделитель";1;ФИЛЬТР(...))
Up Up and Array — книга про динамические массивы в Excel.
Магия таблиц — у меня в книге есть информация по новым функциям. Скриншоты и примеры — в Excel. Но много информации и про Google Таблицы.
Про Р7, увы, пока книг нет, в официальном руководстве про формулы массива тоже информации нет.