67

Массивы в гугл таблицах (GS14)

Привет, дорогие подписчики и читатели Пикабу.

Продолжая тему мануала по гугл таблицам решил сегодня написать пост про массивы.

Сразу скажу, что я не представляю как эта история реализована в Excel, поэтому не смогу сравнить синтаксис и область применения, да и большая часть формул, которые я покажу - не будут работать в детище чипирователя великой Руси.

Что такое массив (array)?

Прежде всего это объект. Объект, который содержит в себе набор данных. Массивы в ГТ могут быть одномерными (строка или столбец) и многомерными - целиковая таблица (дефакто это одномерный массив состоящий из одномерных массивов).

Сейчас я покажу как они записываются, но нужно отметить нюанс. Для русской и английской версии синтаксис будет отличаться. Я буду использовать английский вариант, т.к. он удобнее как мне кажется.


Формула типа ={1;2} будет являться "вертикальным" массивом, "верхний" элемент которого равен 1, а "нижний" - 2.

Формула типа ={1\2} будет являться "горизонтальным" массивом", "левый" элемент - 1, "правый" - 2. В английской версии эта формула записывается как ={1,2}.

Формула типа = { { 1;2} , {3; 4}} будет выглядеть так:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

В своем посте про switch case я показывал, как такого рода массивы могут быть использованы в функции ВПР (VLOOKUP). Ссылка: Swtich case в гугл таблицах или не самый очевидный ВПР (GS8)


И это далеко не единственный способ задать массив.

Второй вариант - arrayformula(). Эта функция не имеет русского эквивалента. arrayformula повторяет формулу, которая в ней записана для каждого элемента массива. На выходе, как правило, она также дает массив. Пример:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Что характерно - в случае arrayformula и еще ряда функция (таких как ВПР (второй аргумент), filter, счётеслимн и т.д.) они принимают аргументы в качестве массива, при этом не требуют от пользователя явной записи в виде массива (через фигурные скобки). Даже простая функция СУММ принимает на вход именно массив. Зная это - мы можем делать вложенные функции.

Приведу повторно пример из поста: Фильтры и ВПРы в ГТ (GS2)

У нас есть следующая таблица:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Давайте с помощью фильтр достанем из нее все слова, внутри которых есть бука "е".

Тут нам поможет функция REGEXMATCH() - соответствие текста определенному регулярному выражению. О самих регулярках мы поговорим в будущем. Пока нам нужно только находить букву Е. Сама функция REGEXMATCH() возвращает 1 или 0, т.е. входит регулярка в текст или не входит. Наглядно это выглядит так:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Н.Б. Формула прописана только в ячейке H1. Благодаря формуле массива она сама протянулась вдоль диапазона G1:G10.

Теперь поместим формулу из ячейки H1 в самый обычный фильтр.

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Результат оказывается похожим на правду.

Н.Б. Фильтр понимает, что все значения и все сверки ему нужно пройти построчно. Поэтому внутри самого фильтра arrayformula можно не использовать.

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Как мы используем arrayformula в работе?

Обычная история - есть пополняемый реестр, в котором нужно постоянно протягивать формулы.

Например, заполняемая форма. Положим у нас есть форма, которую заполняют сотрудники при тратах корп денег и нам нужно по логину почты сотрудника для каждой записи формы протянуть его ФИО. Делается это с помощью обычного ВПР. В excel нам помогла бы умная таблица, которая сама протягивает за нас формулы. Здесь такого нет. Давайте чуть усложним кейс и положим, что мы не знаем сколько сотрудников будут заполнять, а делать справочник заранее нам долго. Тогда нам понадобится список всех НОВЫХ логинов, которых мы еще не внесли в справочник. Приступим.

Первым делом сделаем имитацию формы. Вот такая получилась заготовка:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Первым делом прописываем фильтр, который будет показывать нам новые логины. Нам потребуется комбинация isna(vlookup()). Детально про нее я рассказывал в посте: Фильтры и ВПРы в ГТ (GS2)

Получилось следующим образом:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Внесем один из логинов в справочник и пропишем в столбец М формулу массива, которая будет автоматически для всех строк таблицы подтягивать ФИО.

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Косметическим исправлением будет добавление в M2 функции iferror, которая будет убирать записи #N/A.

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Выглядит это таким образом.Теперь заполнение таблицы будет поэтапным (нам не нужно заранее собирать справочник для ВПРа), формулы подтягиваться будут постоянно. Особенно это удобно для таблиц, работа которых происходит на бекенде, чтобы не нужно было в них постоянно заходить и эти формулы протягивать.


Какие могут быть ошибки связанные с фильтрами или массивами?

Первое -  синтаксис. ГТ автоматически закроет для вас обычные скобки, но за фигурными - нужно следить самостоятельно. Если получаете ошибку типа "Formula parse error" - с большой вероятность вы налажали с фигурными скобками.

Второе - если использовать arrayformula по беконечному диапазону (типа А:А), при это расположить ее в ячейке B2 - таблица будет пытаться постоянно достроить саму себя, т.к. формула массива всегда будет обработать на одну строку больше чем есть. Это бесконечный цикл. Нужно удалять формулу, чистить лишние строки и переписывать.

Есть ряд формул, которые очень тяжело ложатся в массивы, т.к. не подразумевают перебор, а хавают в себя все что дают. Например функция join().

Третье - записи внутри массивов нельзя править руками. ЕСли в таблице сверху я попробую вручную внести данные в ячейку М4, то будет следующее:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Там где есть риск ручных правок - лучше не использовать такие формулы или выстраивать архитектуру доки таким образом, чтобы ручных правок не было.


По традиции - ссылка на док: https://docs.google.com/spreadsheets/d/1mU6d4ZBzgXQyx3I7EQHi...

Дубликаты не найдены

MS, Libreoffice & Google docs

454 поста12.5K подписчика

Добавить пост

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

Подробнее
Лучшие посты за сегодня
4913

Все мужики одинаковые

Все мужики одинаковые Картинка с текстом, Мужчины, Мемы, Повтор, Две женщины орут на кота, Секс
Показать полностью 1
4888

Трогательное фото

Трогательное фото
4678

До сих пор в любом госучреждении

До сих пор в любом госучреждении
4461

Как доводить все дела до конца!

Показать полностью
4392

Мечта

Мечта Скриншот, Twitter, Жизнь, Мечта, Грустный юмор
Показать полностью 1
4075

У меня на это две причины!

3950

Намеки

Намеки Муж, Жена, Бар, Картинка с текстом, Диалог
Показать полностью 1
3513

Шведской полиции было достаточно

3261

Авианосец

Авианосец
3254

Вежливость города берет, а что берет грубость?

Показать полностью
3250

Ответ на пост «Сепарация» 

3162

Скрутить или не скрутить

Скрутить или не скрутить Мемы, Авто, Перекупщики, Верховный суд
3121

Самозанятость

2904

Спокойной ночи 2

Спокойной ночи 2 Картинка с текстом, Спокойной ночи
Показать полностью 1
2877

Программист, обнародовавший видеоархив пыток заключенных, объявлен в розыск

Программист, обнародовавший видеоархив пыток заключенных, объявлен в розыск ФСИН, МВД, Фашизм, Повтор, Новости, Негатив
Программист, обнародовавший видеоархив пыток заключенных, объявлен в розыск ФСИН, МВД, Фашизм, Повтор, Новости, Негатив

Показать полностью 2
2605

Ответ на пост «Вопрос про измены» 

Показать полностью
2533

Сотворение деда

Сотворение деда
2530

Космос? Ну-ну)

Космос? Ну-ну) Скриншот, Таксист, Неучи, Космос, Длиннопост
Показать полностью 1
2372

Дроздов из мира электроники

2354

Объявление

Объявление Объявление, Юмор, Вредные привычки, Накипело, Свинство, Крипота, Не мусорьте!, Чистомэн
Похожие посты закончились. Возможно, вас заинтересуют другие посты по тегам: