July 4

Формулы массива и новые функции (как СОРТ, ФИЛЬТР, ПОСЛЕД и другие) в Р7-Офис

В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами. При это формулы массива в Р7 работают как “старые” формулы массива (возможно, это когда-нибудь изменится). Так что новыми функциями пользоваться не так удобно, как в новом Excel, но зато они в принципе есть, в отличие от Excel 2019, допустим :)

Про разницу в работе старых и новых формул массива в Excel можно узнать из этого видео — это один из 60 уроков курса "Магия Excel" в МИФе.

Итак, вот некоторые из функций, которые появились в Excel 2021/365 (благодаря динамическим массивам; тому, что формула, будучи в одной ячейке, возвращает результат больше одной ячейки) и работают в Р7:

— УНИК (возвращает уникальные значения или строки)

— СОРТ (сортирует диапазон/массив)

— ФИЛЬТР (фильтрует данные по одному или нескольким условиям)

— СЛУЧМАССИВ (возвращает массив случайных чисел)

— ВЫБОРСТОЛБЦ и ВЫБОРСТРОК (извлекают заданные столбцы или строки)

— ВСТОЛБИК и ГСТОЛБИК (объединяют массивы в один вертикально или горизонтально)

— ПОСТОЛБЦ и ПОСТРОК (превращают двумерный диапазон/массив в одномерный; таблицу в список, в общем)

— СВЕРНСТОЛБЦ и СВЕРНСТРОК (наоборот — одномерный диапазон/массив в двумерный)

— ПОСЛЕД (возвращает последовательность чисел с заданными параметрами)

— ТЕКСТПОСЛЕ, ТЕКСТДО, ТЕКСТРАЗД (извлекают текст до или после разделителя или разделяет текст на отдельные значения по разделителю)

Все эти функции в общем случае возвращают массив, а не одно значение — это предопределено их логикой — соответственно, им требуется несколько ячеек для вывода результата.

Как работают формулы массива в Excel

В Exce 2021/365 или Google Таблицах формулы массива можно вводить в одну ячейку, хотя результат может занимать несколько.

Из этого следует несколько нюансов:

— если формуле не хватит пустых ячеек для вывода результата — она не будет удалять и переписывать ваши данные; появится ошибка #SPILL! / #ПЕРЕНОС! в Excel или ошибка #REF! в Google Таблицах.

В Google Таблицах подсказка расскажет нам про то, в какой ячейке есть введенные нами данные/формулы.

В Excel на размеры выводимого диапазона укажет синий пунктир.

— вы не сможете удалить часть результата — удалить или изменить можно формулу только в одной (левой верхней в рамках выводимого диапазона) ячейке, куда вы ее вводили. В 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, увы, пока книг нет, в официальном руководстве про формулы массива тоже информации нет.