Блог

Массивные формулы и динамические массивы в Excel: FILTER, SORT и UNIQUE

2026-07-14 17:08
Введение
Современный Excel содержит множество инструментов для автоматизации работы с данными. Одним из наиболее важных нововведений последних версий стали динамические массивы.
Динамические массивы позволяют одной формуле возвращать сразу несколько значений и автоматически заполнять соседние ячейки. Благодаря этому можно создавать гибкие отчеты, списки и аналитические таблицы без копирования формул вниз по столбцу.
Среди самых востребованных функций динамических массивов можно выделить FILTER, SORT и UNIQUE. Они помогают отбирать данные, сортировать их и получать списки уникальных значений буквально одной формулой.
Для примеров создадим следующую таблицу.
📸 Исходная таблица данных.
FILTER — отбираем только нужные записи
Синтаксис
=FILTER(массив;условие)
Пример
Необходимо вывести только сотрудников из Алматы.
Формула:
=FILTER(A2:C11;B2:B11="Алматы")
📸 Результат работы функции FILTER.
После ввода формулы Excel автоматически создаст новый список, содержащий только сотрудников из Алматы.
Когда полезна FILTER
Функцию удобно использовать для:
  • подготовки отдельных отчетов;
  • создания дашбордов;
  • анализа продаж по регионам;
  • работы с большими базами данных.
Главное преимущество — список обновляется автоматически, если в исходной таблице появляются новые записи.
SORT — сортировка данных одной формулой
Очень часто после отбора данных возникает необходимость отсортировать их по определенному показателю.
Функция SORT создает отсортированную копию диапазона, не изменяя исходную таблицу.
Синтаксис
=SORT(массив;номер_столбца;порядок)
где:
  • 1 — сортировка по возрастанию;
  • -1 — сортировка по убыванию.
Пример
Отсортируем сотрудников по размеру продаж от большего к меньшему.
Формула:
=SORT(A2:C11;3;-1)
📸 Сортировка продаж по убыванию.
После выполнения формулы в верхней части списка окажутся сотрудники с наибольшим объемом продаж.
Когда использовать SORT
Функция полезна для:
  • построения рейтингов;
  • формирования ТОП-10 сотрудников;
  • сортировки товаров по прибыли;
  • подготовки отчетов для руководителей.
Поскольку исходные данные остаются без изменений, можно одновременно создавать несколько вариантов аналитики.
UNIQUE — получаем список уникальных значений
Еще одна частая задача — получение списка без повторений.
Раньше для этого приходилось удалять дубликаты вручную или использовать сложные формулы. Теперь достаточно функции UNIQUE.
Синтаксис
=UNIQUE(массив)
Пример
Получим список уникальных городов из нашей таблицы.
Формула:
=UNIQUE(B2:B11)
📸 Результат работы функции UNIQUE.
Excel автоматически исключает повторяющиеся значения и формирует компактный список.
Практическое применение
UNIQUE часто используют для создания:
  • списков клиентов;
  • перечней товаров;
  • справочников регионов;
  • выпадающих списков.
Комбинируем функции для более мощной аналитики
Настоящая сила динамических массивов раскрывается при сочетании нескольких функций.
SORT + UNIQUE
Допустим, необходимо получить список городов без повторений и сразу отсортировать его по алфавиту.
Формула:
=SORT(UNIQUE(B2:B11))
📸 Список уникальных городов в алфавитном порядке.
Такой подход особенно удобен при подготовке справочников и фильтров для дашбордов.
FILTER + SORT
Теперь попробуем вывести только сотрудников из Алматы и отсортировать их по объему продаж.
Формула:
=SORT(FILTER(A2:C11;B2:B11="Алматы");3;-1)
📸 FILTER и SORT в одной формуле.
Получаем готовый рейтинг сотрудников Алматы без создания дополнительных таблиц и вспомогательных столбцов.
Возможные ошибки
Ошибка #SPILL!
Иногда Excel может показать ошибку:
#SPILL!
Это означает, что формуле недостаточно свободного места для вывода результатов. Необходимо очистить ячейки, в которые должен развернуться динамический массив.
Несовместимая версия Excel
Функции FILTER, SORT и UNIQUE доступны в Excel 365 и Excel Online. В более старых версиях программы они могут отсутствовать.
Вывод
Динамические массивы кардинально изменили подход к работе с данными в Excel. Если раньше для отбора, сортировки и удаления дубликатов приходилось использовать дополнительные столбцы, фильтры или сложные комбинации формул, то сегодня многие задачи можно решить буквально одной строкой кода.
Функция FILTER помогает мгновенно получать только нужные записи по заданным критериям, SORT позволяет создавать динамические рейтинги и упорядоченные списки, а UNIQUE автоматически формирует перечни без повторяющихся значений. При этом результаты обновляются автоматически при изменении исходных данных, что делает отчеты более гибкими и значительно сокращает время на их поддержку.
Особую ценность эти функции приобретают при совместном использовании. Комбинируя FILTER, SORT и UNIQUE между собой, можно создавать интерактивные отчеты, динамические справочники, списки для выпадающих меню и элементы дашбордов без применения VBA и Power Query.
Освоение динамических массивов — это один из самых эффективных способов повысить свою продуктивность в Excel. Независимо от того, работаете ли вы с продажами, финансами, HR-аналитикой или операционной отчетностью, эти функции помогут автоматизировать рутинные операции, сократить количество ошибок и сделать анализ данных более быстрым и удобным.
Начните с простых примеров из этой статьи, а затем попробуйте комбинировать функции между собой. Уже после нескольких практических задач вы заметите, насколько проще становится работа с большими таблицами и регулярно обновляемыми данными.