Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

Сортировка данных в Excel с помощью формул SORT и SORTBY

forest_paw 09.01.2026 1822

Сортировка данных в Excel с помощью формул SORT и СОРТПО / SORTBY

Автоматическая сортировка данных в Excel — ключевой навык для создания динамических отчетов и дашбордов. В этой статье мы разберем, как использовать мощные функции SORT и СОРТПО / SORTBY для решения этой задачи без ручного вмешательства пользователя.

При стандартных русских региональных настройках разделитель столбцов в константе массива — обратная косая черта, строк — точка с запятой. Поэтому многоуровневый пример использует горизонтальные массивы {2\3} и {1\-1}; в английских настройках — {2,3} и {1,-1}. Эквивалент без констант: =СОРТПО(B3:D9;C3:C9;1;D3:D9;-1) / =SORTBY(B3:D9,C3:C9,1,D3:D9,-1).

СОРТ / SORT, СОРТПО / SORTBY и СЛУЧМАССИВ / RANDARRAY доступны в Microsoft 365, Excel 2021/2024 для Windows и Mac и в Excel для Интернета. В Excel 2016/2019 этих функций нет. Размещайте результат в свободной области вне таблицы Excel; исходные данные сортировка формулой не переставляет.

Материал поможет, если вам нужны понятные формулы в Excel, разбор функций на русском и английском и практические приемы для расчетов без ошибок.

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

Как работает функция SORT

Синтаксис: =СОРТ(массив;[индекс_сортировки];[порядок_сортировки];[по_столбцу]) / =SORT(array,[sort_index],[sort_order],[by_col]). Здесь используются первые три аргумента:

  1. Массив для сортировки: Укажите диапазон с данными, исключая заголовки.
  2. Индекс столбца сортировки: Определите номер столбца в указанном массиве, по которому будет проводиться сортировка. Например, для сортировки по третьему столбцу используйте число 3.
  3. Порядок сортировки: Используйте 1 для сортировки по возрастанию и -1 для сортировки по убыванию.

Например, формула Русская версия: =СОРТ(B3:D9; 3; -1)English version: =SORT(B3:D9, 3, -1) отсортирует диапазон B3:D9 по третьему столбцу (столбец D) в порядке убывания.

Заголовки в B2:D2. Данные в B3:D9. Используйте формулу для сортировки данных по сумме в столбце D. Это 3-й столбец в данных. Справа введите =SORT(B3:D9,3,-1). 3 означает 3-й столбец. -1 означает по убыванию. Используйте 1 для сортировки по возрастанию. Заголовки не переносятся — для их копирования потребуется другая формула.
Заголовки в B2:D2. Данные в B3:D9. Используйте формулу для сортировки данных по сумме в столбце D. Это 3-й столбец в данных. Справа введите =SORT(B3:D9,3,-1). 3 означает 3-й столбец. -1 означает по убыванию. Используйте 1 для сортировки по возрастанию. Заголовки не переносятся — для их копирования потребуется другая формула.

Многоуровневая сортировка

Для сортировки по нескольким критериям используйте константы массивов во втором и третьем аргументах. Например, чтобы отсортировать данные сначала по команде (столбец C) по возрастанию, а затем по сумме (столбец D) по убыванию, используйте формулу: Русская версия: =СОРТ(B3:D9; {2\3}; {1\-1})English version: =SORT(B3:D9, {2,3}, {1,-1}).

Если нужно отсортировать по 2 столбцам, используйте константы массивов: =SORT(B3:D9,{2,3},{1,-1}). Это сортирует по команде в C по возрастанию, а затем по сумме в D по убыванию.
Если нужно отсортировать по 2 столбцам, используйте константы массивов: =SORT(B3:D9,{2,3},{1,-1}). Это сортирует по команде в C по возрастанию, а затем по сумме в D по убыванию.

Функция СОРТПО / SORTBY для сортировки по другому диапазону

Функция СОРТПО / SORTBY более гибкая: она позволяет сортировать один диапазон по значениям другого. Допустим, вам нужно вывести список продуктов, отсортированный по сумме продаж, но без отображения самих сумм. Формула будет выглядеть так: Русская версия: =СОРТПО(B3:B9; D3:D9; -1)English version: =SORTBY(B3:B9, D3:D9, -1). Здесь диапазон продуктов B3:B9 сортируется по убыванию на основе значений в D3:D9.

Нужны продукты из B, отсортированные по суммам в D, но без сумм в результате. Используйте =SORTBY(B3:B9,D3:D9,-1).
Нужны продукты из B, отсортированные по суммам в D, но без сумм в результате. Используйте =СОРТПО(B3:B9;D3:D9;-1) / =SORTBY(B3:B9,D3:D9,-1).

Бонус: Случайная сортировка

Случайная сортировка может пригодиться для жеребьевки или случайного выбора. Комбинируйте СОРТПО / SORTBY с функцией СЛУЧМАССИВ / RANDARRAY. Например, чтобы случайным образом отсортировать список сотрудников, используйте формулу: Русская версия: =СОРТПО(B3:B10; СЛУЧМАССИВ(8))English version: =SORTBY(B3:B10, RANDARRAY(8)). При пересчёте случайные ключи обновляются; в настольном Excel для Windows его можно вызвать F9. Повтор прежней перестановки возможен. Чтобы закрепить результат, скопируйте его и вставьте как значения.

Алфавитный список сотрудников в B3:B10. Они сортируются случайным образом с помощью =SORTBY(B3:B10,RANDARRAY(8)).
Алфавитный список сотрудников в B3:B10. Они сортируются случайным образом с помощью =СОРТПО(B3:B10;СЛУЧМАССИВ(8)) / =SORTBY(B3:B10,RANDARRAY(8)).

Использование SORT и СОРТПО / SORTBY кардинально меняет подход к построению отчетов в Excel, делая их автоматизированными, динамичными и не требующими ручного обновления. Внедряйте эти функции в свои рабочие процессы для повышения эффективности.

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.