Привет, дорогие подписчики и читатели Пикабу.
Продолжая тему мануала по гугл таблицам решил сегодня написать пост про массивы.
Сразу скажу, что я не представляю как эта история реализована в Excel, поэтому не смогу сравнить синтаксис и область применения, да и большая часть формул, которые я покажу - не будут работать в детище чипирователя великой Руси.
Что такое массив (array)?
Прежде всего это объект. Объект, который содержит в себе набор данных. Массивы в ГТ могут быть одномерными (строка или столбец) и многомерными - целиковая таблица (дефакто это одномерный массив состоящий из одномерных массивов).
Сейчас я покажу как они записываются, но нужно отметить нюанс. Для русской и английской версии синтаксис будет отличаться. Я буду использовать английский вариант, т.к. он удобнее как мне кажется.
Формула типа ={1;2} будет являться "вертикальным" массивом, "верхний" элемент которого равен 1, а "нижний" - 2.
Формула типа ={1\2} будет являться "горизонтальным" массивом", "левый" элемент - 1, "правый" - 2. В английской версии эта формула записывается как ={1,2}.
Формула типа = { { 1;2} , {3; 4}} будет выглядеть так:
В своем посте про switch case я показывал, как такого рода массивы могут быть использованы в функции ВПР (VLOOKUP). Ссылка: Swtich case в гугл таблицах или не самый очевидный ВПР (GS8)
И это далеко не единственный способ задать массив.
Второй вариант - arrayformula(). Эта функция не имеет русского эквивалента. arrayformula повторяет формулу, которая в ней записана для каждого элемента массива. На выходе, как правило, она также дает массив. Пример:
Что характерно - в случае arrayformula и еще ряда функция (таких как ВПР (второй аргумент), filter, счётеслимн и т.д.) они принимают аргументы в качестве массива, при этом не требуют от пользователя явной записи в виде массива (через фигурные скобки). Даже простая функция СУММ принимает на вход именно массив. Зная это - мы можем делать вложенные функции.
Приведу повторно пример из поста: Фильтры и ВПРы в ГТ (GS2)
У нас есть следующая таблица:
Давайте с помощью фильтр достанем из нее все слова, внутри которых есть бука "е".
Тут нам поможет функция REGEXMATCH() - соответствие текста определенному регулярному выражению. О самих регулярках мы поговорим в будущем. Пока нам нужно только находить букву Е. Сама функция REGEXMATCH() возвращает 1 или 0, т.е. входит регулярка в текст или не входит. Наглядно это выглядит так:
Н.Б. Формула прописана только в ячейке H1. Благодаря формуле массива она сама протянулась вдоль диапазона G1:G10.
Теперь поместим формулу из ячейки H1 в самый обычный фильтр.
Результат оказывается похожим на правду.
Н.Б. Фильтр понимает, что все значения и все сверки ему нужно пройти построчно. Поэтому внутри самого фильтра arrayformula можно не использовать.
Как мы используем arrayformula в работе?
Обычная история - есть пополняемый реестр, в котором нужно постоянно протягивать формулы.
Например, заполняемая форма. Положим у нас есть форма, которую заполняют сотрудники при тратах корп денег и нам нужно по логину почты сотрудника для каждой записи формы протянуть его ФИО. Делается это с помощью обычного ВПР. В excel нам помогла бы умная таблица, которая сама протягивает за нас формулы. Здесь такого нет. Давайте чуть усложним кейс и положим, что мы не знаем сколько сотрудников будут заполнять, а делать справочник заранее нам долго. Тогда нам понадобится список всех НОВЫХ логинов, которых мы еще не внесли в справочник. Приступим.
Первым делом сделаем имитацию формы. Вот такая получилась заготовка:
Первым делом прописываем фильтр, который будет показывать нам новые логины. Нам потребуется комбинация isna(vlookup()). Детально про нее я рассказывал в посте: Фильтры и ВПРы в ГТ (GS2)
Получилось следующим образом:
Внесем один из логинов в справочник и пропишем в столбец М формулу массива, которая будет автоматически для всех строк таблицы подтягивать ФИО.
Косметическим исправлением будет добавление в M2 функции iferror, которая будет убирать записи #N/A.
Выглядит это таким образом.Теперь заполнение таблицы будет поэтапным (нам не нужно заранее собирать справочник для ВПРа), формулы подтягиваться будут постоянно. Особенно это удобно для таблиц, работа которых происходит на бекенде, чтобы не нужно было в них постоянно заходить и эти формулы протягивать.
Какие могут быть ошибки связанные с фильтрами или массивами?
Первое - синтаксис. ГТ автоматически закроет для вас обычные скобки, но за фигурными - нужно следить самостоятельно. Если получаете ошибку типа "Formula parse error" - с большой вероятность вы налажали с фигурными скобками.
Второе - если использовать arrayformula по беконечному диапазону (типа А:А), при это расположить ее в ячейке B2 - таблица будет пытаться постоянно достроить саму себя, т.к. формула массива всегда будет обработать на одну строку больше чем есть. Это бесконечный цикл. Нужно удалять формулу, чистить лишние строки и переписывать.
Есть ряд формул, которые очень тяжело ложатся в массивы, т.к. не подразумевают перебор, а хавают в себя все что дают. Например функция join().
Третье - записи внутри массивов нельзя править руками. ЕСли в таблице сверху я попробую вручную внести данные в ячейку М4, то будет следующее:
Там где есть риск ручных правок - лучше не использовать такие формулы или выстраивать архитектуру доки таким образом, чтобы ручных правок не было.
По традиции - ссылка на док: https://docs.google.com/spreadsheets/d/1mU6d4ZBzgXQyx3I7EQHi...
