Сортировка данных в 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]). Здесь используются первые три аргумента:
- Массив для сортировки: Укажите диапазон с данными, исключая заголовки.
- Индекс столбца сортировки: Определите номер столбца в указанном массиве, по которому будет проводиться сортировка. Например, для сортировки по третьему столбцу используйте число
3. - Порядок сортировки: Используйте
1для сортировки по возрастанию и-1для сортировки по убыванию.
Например, формула Русская версия: =СОРТ(B3:D9; 3; -1)English version: =SORT(B3:D9, 3, -1) отсортирует диапазон B3:D9 по третьему столбцу (столбец D) в порядке убывания.
Многоуровневая сортировка
Для сортировки по нескольким критериям используйте константы массивов во втором и третьем аргументах. Например, чтобы отсортировать данные сначала по команде (столбец C) по возрастанию, а затем по сумме (столбец D) по убыванию, используйте формулу: Русская версия: =СОРТ(B3:D9; {2\3}; {1\-1})English version: =SORT(B3:D9, {2,3}, {1,-1}).
Функция СОРТПО / SORTBY для сортировки по другому диапазону
Функция СОРТПО / SORTBY более гибкая: она позволяет сортировать один диапазон по значениям другого. Допустим, вам нужно вывести список продуктов, отсортированный по сумме продаж, но без отображения самих сумм. Формула будет выглядеть так: Русская версия: =СОРТПО(B3:B9; D3:D9; -1)English version: =SORTBY(B3:B9, D3:D9, -1). Здесь диапазон продуктов B3:B9 сортируется по убыванию на основе значений в D3:D9.
Бонус: Случайная сортировка
Случайная сортировка может пригодиться для жеребьевки или случайного выбора. Комбинируйте СОРТПО / SORTBY с функцией СЛУЧМАССИВ / RANDARRAY. Например, чтобы случайным образом отсортировать список сотрудников, используйте формулу: Русская версия: =СОРТПО(B3:B10; СЛУЧМАССИВ(8))English version: =SORTBY(B3:B10, RANDARRAY(8)). При пересчёте случайные ключи обновляются; в настольном Excel для Windows его можно вызвать F9. Повтор прежней перестановки возможен. Чтобы закрепить результат, скопируйте его и вставьте как значения.
Использование SORT и СОРТПО / SORTBY кардинально меняет подход к построению отчетов в Excel, делая их автоматизированными, динамичными и не требующими ручного обновления. Внедряйте эти функции в свои рабочие процессы для повышения эффективности.