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

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

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

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

Функция SORT принимает три основных аргумента для гибкой настройки:

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

Например, формула =SORT(Б3:Г9, 3, -1) (=SORT(B3:D9, 3, -1)) отсортирует диапазон B3:D9 по третьему столбцу (столбец D) в порядке убывания.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Похожие статьи

Генерация всех комбинаций с помощью функции BASE в Excel

Определение страны по телефонному номеру в Excel с помощью формулы

Пользовательские форматы чисел в Excel: полное руководство