Работа с массивами данных

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

Массив и диапазон: в чем разница

Группа ячеек на листе называется диапазоном. Его можно увидеть, выделить или скопировать. Например, A2:B6 — это диапазон из 10 ячеек.

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

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

• Диапазон A2:B6  — исходная таблица товаров;

• Массив СОРТ(A2:B6; 2; -1) — те же товары, но отсортированные по убыванию стоимости.

Исходная таблица товаров (диапазон) остается неизменной, в то время как массивы позволяют по-разному работать с этими данными.

Типы массивов и синтаксис

Массивы, как и диапазоны, могут быть одномерными (строка или столбец) и двумерными (таблица).

Диапазонные массивы работают с данными в ячейках таблицы. Константные массивы хранят данные прямо в формуле и их не обязательно записывать в отдельные ячейки.

Диапазонные массивы

Набор значений, заданный в ячейках таблицы:

• B1:D1 — данные в одной строке;

• A2:A4 — данные в одном столбце;

• B2:D4 — прямоугольный диапазон.

Константные массивы

Массивы, данные которых задаются в формуле вручную и не используют ссылок на ячейки, называют константными массивами. Они подходят для фиксированных наборов значений.

Фигурные скобки {} обозначают начало и конца массива. Точка с запятой ; разделяет элементы в столбцах. Двоеточие : разделяет строки массива.

Набор значений, заданный непосредственно в формуле:

• {2;3;4} — данные в одной строке;

• {2:3:4} — данные в одном столбце;

• {4;6;8:6;9;12:8;12;16} — прямоугольный диапазон.

Операции с массивами

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

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

Размер и содержимое итогового массива динамически обновляется при изменении исходных значений. Полученные данные, в зависимости от результатов вычислений, могут «разливаться» на соседние ячейки. Если формула возвращает несколько значений, они автоматически заполнят необходимое количество ячеек вниз или вправо.

Когда массив не может расшириться на нужные ячейки, возникает ошибка #ПЕРЕНОС!. Чтобы исправить ошибку, устраните возможные причины:

• занятые ячейки — в диапазоне уже есть данные, не являющиеся частью массива;

• объединенные ячейки — блокируют автоматическое расширение;

• границы листа — массив пытается выйти за пределы таблицы;

• ручное вмешательство — попытка изменить отдельную ячейку внутри массива.

Формулы массива

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

• умножение всех элементов на число — =A1:A10 * 2 или ={1:2:3:4} * 2;
• поэлементное сложение двух массивов — =B1:B10 + C1:C10 или ={1:2:3:4} + {5:6:7:8};
• условные вычисления — =(A1:A10 > 5) * B1:B10.

Функции массива

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

Название	Описание	Синтаксис
МЕДИН (MUNIT)	Создает единичную матрицу указанного размера.	МЕДИН(размер) Пример =МЕДИН(4) Создает единичную матрицу 4×4.
МОБР (MINVERSE)	Возвращает обратную матрицу.	МОБР(диапазон) Пример =МОБР(A1:B2) Вычисляет обратную матрицу для A1:B2.
МОПРЕД (MDETERM)	Вычисляет определитель квадратной матрицы.	МОПРЕД(диапазон) Пример =МОПРЕД(A1:C3) Возвращает определитель матрицы 3×3.
МУМНОЖ (MMULT)	Выполняет матричное умножение.	МУМНОЖ(массив1; массив2) Пример =МУМНОЖ(A1:B2; C1:D2) Умножает две матрицы 2×2.
РОСТ (GROWTH)	Вычисляет прогноз экспоненциального роста.	РОСТ(известные_y; [известные_x]; [новые_x]; [конст]) Пример =РОСТ(B2:B12; A2:A12; A13:A15) Прогнозирует значения для периодов A13–A15.
СОРТ (SORT)	Сортирует данные в диапазоне или массиве по возрастанию или убыванию.	СОРТ(диапазон; [индекс_сортировки]; [порядок_сортировки]; [по_столбцу]) Пример =СОРТ(A1:A10) Возвращает массив упорядоченных значений.
СУММПРОИЗВ (SUMPRODUCT)	Суммирует произведения соответствующих элементов.	СУММПРОИЗВ(массив1; [массив2]; ...) Пример =СУММПРОИЗВ(A2:A5; B2:B5) Суммирует попарные произведения.
СУММКВРАЗН (SUMXMY2)	Возвращает сумму квадратов разностей элементов.	СУММКВРАЗН(массив_x; массив_y) Пример =СУММКВРАЗН(A2:A5; B2:B5) Вычисляет Σ(x-y)2.
СУММРАЗНКВ (SUMX2MY2)	Возвращает сумму разностей квадратов элементов.	СУММРАЗНКВ(массив_x; массив_y) Пример =СУММРАЗНКВ(A2:A5; B2:B5) Вычисляет Σ(x2-y2).
СУММСУММКВ (SUMX2PY2)	Возвращает сумму сумм квадратов элементов.	СУММСУММКВ(массив_x; массив_y) Пример =СУММСУММКВ(A2:A5; B2:B5) Вычисляет Σ(x2+y2).
ТЕНДЕНЦИЯ (TREND)	Вычисляет прогноз линейного тренда.	ТЕНДЕНЦИЯ(известные_y; [известные_x]; [новые_x]; [конст]) Пример =ТЕНДЕНЦИЯ(B2:B12; A2:A12; A13:A15) Прогнозирует значения по линейному тренду.
ТРАНСП (TRANSPOSE)	Транспонирует диапазон или массив (меняет строки и столбцы местами).	ТРАНСП(диапазон) Пример =ТРАНСП(A1:C3) Преобразует матрицу 3×3 в 3 строки и 3 столбца.
УНИК (UNIQUE)	Возвращает уникальные значения из указанного диапазона.	УНИК(диапазон; [по_столбцам]; [точно_один_раз]) Пример =УНИК(A2:A100) Возвращает список уникальных значений из столбца A.
ФИЛЬТР (FILTER)	Фильтрует данные по заданным условиям.	ФИЛЬТР(диапазон; условие; [если_нет_совпадений]) Пример =ФИЛЬТР(A1:A100; B1:B100>50) Возвращает массив значений, которые больше 50.
ЧАСТОТА (FREQUENCY)	Вычисляет распределение значений по заданным интервалам.	ЧАСТОТА(диапазон; интервалы) Пример =ЧАСТОТА(B2:B50; {10;20;30}) Возвращает частоту значений в интервалах 0–10, 10–20, 20–30.