Арсенал Google таблиц, как говорится из коробки и совершенно бесплатно, имеет множество функций, которые отсутствуют в MS Exсel или доступ к ним закрыт версиям ниже Office 365.
Например: FILTER, UNIQUE, SORT.
Такие функции дают возможность, работать с массивами, то есть в ячейке можно ссылаться на нужный диапазон данных и выполнять необходимые действия со всем диапазоном.
ArrayFormula, аналог формулы массива в MS Excel.
Отображает значения, полученные с помощью формулы массива, в нескольких строках и столбцах, позволяет использовать массивы в функциях, не предназначенных для этого.
Синтаксис
ARRAYFORMULA(формула_массива)
Одновременное нажатие клавиш CTRL+SHIFT+ENTER во время редактирования формулы автоматически добавляет код ArrayFormula (в начало формулы).
Не работает в связке ИНДЕКС/ПОИСКОПЗ.
Что делает?
Представим задачу: поиск соответствий в таблице при помощи ВПР (VLOOKUP) на 100 000 строк, данные в которой пополняются.
ArrayFormula преобразует 100 тыс. формул в одну, автоматически расширяющуюся при добавлении новых значений, без вашего участия.
Как происходит чудо
1. Введем в ячейку обычную формулу: =ВПР(A2;G:H;2;0):
2. Заменим ссылку на ячейку ссылкой на диапазон: =ВПР(A2:A;G:H;2;0):
3. Нажимаем CTRL+SHIFT+ENTER, магия: =ArrayFormula(ВПР(A2:A;G:H;2;0)):
4. Формула ищет совпадения по всему столбцу, предсказуемо выводя ошибку # H/Д. Исправим обычной проверкой, на пустую строку: =ArrayFormula(ЕСЛИ(A2:A=""; ;ВПР(A2:A;G:H;2;0))):
ВАЖНО: на пути у формулы не должно быть заполненных ячеек, иначе она сломается выдав ошибку # ССЫЛ!
Бонус на последок
Cделать формулу менее уязвимой от "рукастых" пользователей, можно убрав её в шапку таблицы.
Для этого в начало/конец добавьте фигурные скобки, в кавычках напишите заголовок столбца и поставьте точку с запятой:
={"Ваше название"; ArrayFormula(ЕСЛИ(A2:A=""; ;ВПР(A2:A;G:H;2;0)))}
Спасибо, что дочитали до конца!
Если Вам было интересно, ставьте лайк, пишите, что думаете в комментариях и подписывайтесь на канал, если еще не сделали этого.