Как работать с двумерными массивами в Google Sheets без ручного копирования формул

В Google Sheets двумерный массив — это прямоугольный блок значений, с которым формула работает целиком, а не по одной ячейке. Если диапазон занимает несколько строк и несколько столбцов, например A2:D10, его можно обрабатывать как единую таблицу: сразу считать, отбирать, перестраивать и выводить новый результат на лист.

Практический смысл простой: вместо того чтобы писать формулу в одну ячейку и потом копировать её вправо и вниз, вы задаёте логику один раз. Результат возвращается сразу на несколько строк и столбцов. Такой подход особенно полезен для отчётов, матриц план-факт, таблиц с товарами по месяцам и других листов, где данные регулярно обновляются.

Главная смена привычки при работе с массивами — думать не отдельной ячейкой, а формой всего диапазона.

Что считается двумерным массивом и чем он отличается от обычного диапазона

Технически диапазон — это адресуемая область листа, например B2:D13. А массив — это то, как формула воспринимает и возвращает набор значений. Один и тот же диапазон можно использовать по-разному:

• как обычную ссылку — когда формула берёт одно значение или считает результат для одной ячейки;
• как массив — когда формула обрабатывает весь блок и возвращает сразу несколько значений.

Если массив состоит из одного столбца или одной строки, он одномерный. Если в нём есть и несколько строк, и несколько столбцов, это двумерный массив. Для таблиц это естественный формат: строки обычно представляют объекты, а столбцы — признаки, периоды или показатели.

В Google Sheets многие массивные результаты расширяются автоматически на соседние ячейки. Поэтому для массивных формул важно не только само вычисление, но и размер результата: заранее полезно понимать, на сколько строк и столбцов он развернётся.

Когда массивная формула удобнее, чем протягивание вручную

Ручное протягивание формул хорошо работает в маленьких таблицах, которые почти не меняются. Но у него есть слабые места: можно забыть дотянуть формулу до новых строк, случайно изменить одну ячейку в середине столбца или получить смесь разных версий одной и той же логики.

Особенно заметна польза в двумерных задачах: когда нужно поэлементно сравнить две матрицы одинаковой формы, выбрать часть таблицы по условию или собрать компактный отчёт из большого исходного диапазона.

Как применять ARRAYFORMULA к двумерным диапазонам

ARRAYFORMULA позволяет вывести результат формулы сразу в несколько строк и столбцов, а также использовать с массивами функции и выражения, которые обычно мыслятся как ячейка-за-ячейкой. Для двумерных расчётов это удобно, когда одна и та же операция должна применяться ко всему блоку.

Самый понятный пример — поэлементное вычисление между двумя диапазонами одинакового размера. Допустим, в B2:D13 находятся количества по товарам и месяцам, а в G2:I13 — цены для тех же позиций. Тогда формула:

=ARRAYFORMULA(B2:D13*G2:I13)

вернёт новую таблицу того же размера: каждая ячейка результата будет равна произведению соответствующих ячеек двух исходных блоков.

По той же схеме работают и другие операции:

• =ARRAYFORMULA(B2:D13+G2:I13) — поэлементное сложение;
• =ARRAYFORMULA(B2:D13-G2:I13) — поэлементная разница;
• =ARRAYFORMULA(B2:D13/G2:I13) — поэлементное деление, если во втором диапазоне нет нулей.

Здесь есть ключевое ограничение: диапазоны должны быть одинаковой формы. Если один массив имеет размер 12 на 3, а другой 11 на 3, поэлементное сопоставление не сработает так, как ожидается. Это одна из самых частых причин ошибок в массивных расчётах.

Ещё одна практическая деталь: хотя многие формулы в современных Google Sheets и так возвращают массивный результат, явное использование ARRAYFORMULA помогает сделать намерение формулы понятным. Когда вы открываете таблицу через месяц, сразу видно, что расчёт был задуман именно как работа с блоком данных.

Как ограничить слишком большой результат

Иногда формула возвращает массив больше, чем нужно для отчёта или примера. В таком случае можно использовать ARRAY_CONSTRAIN, чтобы оставить только нужный фрагмент по числу строк и столбцов.

=ARRAY_CONSTRAIN(SEQUENCE(100;10);10;3)

Сначала SEQUENCE создаёт массив 100 на 10, а затем ARRAY_CONSTRAIN оставляет только первые 10 строк и 3 столбца. Это не меняет исходную логику вычисления, а просто обрезает вывод до нужного размера.

Как сочетать FILTER, INDEX и SEQUENCE для выборки и перестройки таблицы

Для реальной работы мало уметь только считать матрицу целиком. Часто нужно сначала выбрать подходящие данные, потом взять из них определённые строки или столбцы, а иногда ещё и задать форму будущего результата. Здесь удобны связки из FILTER, INDEX и SEQUENCE.

FILTER: отобрать строки или столбцы

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

Например, чтобы взять из таблицы A2:F только строки, где в первом столбце есть данные, можно использовать:

=FILTER(A2:F;NOT(ISBLANK(A2:A)))

Результат — компактный двумерный массив без пустых строк в начале выборки.

Для FILTER важно помнить два правила:

• все условия должны быть одного типа: либо по строкам, либо по столбцам;
• длина условия должна совпадать с размером диапазона по соответствующему измерению.

Если нужно отобрать и строки, и столбцы, обычно используют один FILTER поверх результата другого.

INDEX: взять строку, столбец или точку из массива

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

Например:

=INDEX(A2:D10;2;3)

вернёт значение из второй строки и третьего столбца выбранного диапазона.

Но для массивных сценариев ещё важнее другое свойство: если указать 0 в аргументе строки или столбца, можно вернуть весь столбец или всю строку из массива. Это удобно, когда вы сначала сузили таблицу через FILTER, а затем хотите взять из результата только один столбец или одну строку для следующего шага отчёта.

Например, после фильтрации можно обращаться уже не к исходной таблице, а к её массивному результату. Это делает формулу компактнее и уменьшает число повторных ссылок на один и тот же диапазон.

SEQUENCE: задать форму и порядок

SEQUENCE создаёт массив последовательных чисел и тем самым помогает быстро задать размер будущего блока. Базовый пример:

=SEQUENCE(4;3)

вернёт таблицу 4 на 3. Такое поведение удобно в трёх случаях:

• нужно быстро создать шаблон нужной формы;
• нужно проверить, как будет разливаться двумерный результат;
• нужно построить служебную сетку индексов для дальнейшей обработки.

В связке с INDEX и FILTER это помогает не просто отобрать строки, а ещё и аккуратно собрать из них компактный вывод нужного размера.

Типичные ошибки при работе с двумерными массивами

• Несовпадение размеров массивов. Для поэлементных операций блоки должны иметь одинаковое число строк и столбцов.
• Неправильная длина условий в FILTER. Условие должно соответствовать диапазону по строкам или по столбцам, иначе фильтрация вернёт ошибку.
• Смешение строковых и столбцовых условий в одном FILTER. По данным справки такие условия нельзя смешивать в одном вызове функции.
• Слишком большой захват пустых строк. Если формула обращается к диапазону с большим запасом, в результирующем массиве может оказаться лишняя пустота. Обычно это решают предварительным FILTER по непустым строкам.
• Неясная форма будущего результата. Если заранее не понимать, сколько строк и столбцов вернёт формула, сложнее встроить её в отчётный лист. В таких случаях помогают SEQUENCE для проверки формы и ARRAY_CONSTRAIN для ограничения вывода.

Практический кейс: расчётная таблица одной формулой

Учебный пример. Пусть на листе есть две матрицы одинаковой структуры:

• в B2:D13 — фактические продажи по товарам и месяцам;
• в G2:I13 — план по тем же товарам и месяцам.

Нужно получить отдельную таблицу отклонений без копирования формулы по всей сетке. Тогда в верхнюю левую ячейку отчётного блока достаточно поместить:

=ARRAYFORMULA(B2:D13-G2:I13)

Формула сразу вернёт весь двумерный результат: отклонение для каждого товара в каждом месяце.

Если в отчёт нужно вывести не всю матрицу, а только верхнюю часть, например первые 5 строк и 3 столбца, можно сразу ограничить массив:

=ARRAY_CONSTRAIN(ARRAYFORMULA(B2:D13-G2:I13);5;3)

А если исходные данные приходят не в виде готовой матрицы, а как длинная таблица строк, типичный порядок действий такой:

1. через FILTER убрать пустые или неподходящие записи;
2. через INDEX взять из результата нужный столбец, строку или фрагмент;
3. при необходимости ограничить итоговый блок через ARRAY_CONSTRAIN.

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

Итоговый ориентир такой: если задача относится к целому прямоугольному блоку данных, сначала определите форму массива, затем подберите функцию под роль. ARRAYFORMULA нужна для массового расчёта, FILTER — для отбора, INDEX — для доступа к части массива, SEQUENCE — для задания формы и индексов. Это и позволяет в Google Sheets обходиться без ручного копирования формул по строкам и столбцам.