Как использовать функцию SORTBY в Excel

Microsoft Excel предоставляет мощные инструменты для компоновки, организации и визуализации данных. Иногда требуется создать несколько специфических представлений набора данных — и при этом не менять исходную таблицу. Функция SORTBY решает именно эту задачу: она создаёт отдельный массив, отсортированный по одному или нескольким критериям, сохраняя исходные ячейки нетронутыми.

Для чего нужна функция SORTBY

Коротко о сценариях использования:

• Создание нескольких независимых «видов» одной и той же таблицы (например, по регионам, по результатам, по приоритету) одновременно.
• Рейтинг внутри групп (сначала по региону, затем по результату) без перестановки исходных строк.
• Подготовка упорядоченных диапазонов для дальнейших вычислений (SUMIFS, VLOOKUP/XLOOKUP/INDEX+MATCH) или построения динамических панелей.

Преимущество SORTBY в том, что она сочетает удобство отдельного массива (как у SORT) и гибкость многокритериальной сортировки (как у инструментов Sort & Filter), но без риска нарушить оригинальные данные.

Пример: таблица продаж

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

Базовый синтаксис функции SORTBY

Синтаксис:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

Где:

• array — исходный диапазон, который вы хотите отсортировать (например, A2:D16).
• by_arrayN — диапазон с ключом сортировки (каждый by_array должен совпадать по размеру с array по числу строк или столбцов в зависимости от ориентации).
• sort_order — 1 для по возрастанию или -1 для по убыванию. Если не указан при единственном критерии, по умолчанию применяется возрастание.

Шаг за шагом: простой пример по штату

1. Выберите пустую ячейку, например F2.
2. Введите формулу:

=SORTBY(A2:D16, B2:B16)

3. Нажмите Enter.

Функция вернёт копию массива A2:D16, отсортированную по значению в B2:B16 по возрастанию (A→Z). Обратите внимание: имена продавцов остаются в том порядке, который соответствует сортировке по штату — они не будут автоматически упорядочены по алфавиту, если вы этого не укажете.

Важно: SORTBY возвращает массив. Если рядом есть заполненные ячейки, которые блокируют «выпадающий» диапазон, появится ошибка. Убедитесь, что справа и вниз от стартовой ячейки есть свободное место.

Сортировка по нескольким критериям

Чтобы сортировать сначала по одному полю, затем по другому, перечислите пары by_array и sort_order в нужном порядке. Пример — по штату (по возрастанию), затем по количеству продаж (по убыванию):

=SORTBY(A2:D16, B2:B16, 1, C2:C16, -1)

Пояснения:

• Первый критерий: B2:B16 — сортируем по штату в порядке возрастания (1).
• Второй критерий: C2:C16 — в рамках каждого штата сортируем по количеству продаж в порядке убывания (-1).

Если хотите сохранить оба варианта для сравнения, вставьте одну формулу в F2, другую — в K2.

Частые ошибки и как их исправлять

• “#REF!” при указании диапазона типа B2:C16 как by_array: нельзя объединять несколько столбцов в одном by_array. Указывайте каждый ключ отдельно: B2:B16, C2:C16.
• “#VALUE!” при неправильном sort_order: допускаются только 1 и -1. Любые другие значения приведут к ошибке.
• “#VALUE!” при несовпадении размеров: все by_array должны содержать столько же элементов, сколько и array по соответствующему измерению. Если array — 15 строк, то и каждый by_array должен иметь 15 строк.
• Ошибка динамического массива: если в ячейках вывода уже есть данные, формула не «выпадет» и вернёт сообщение о конфликте. Очистите область вывода.

Когда SORTBY не подходит — альтернативные подходы

• Если вы используете старые версии Excel без динамических массивов, SORTBY может быть недоступна. В этом случае используйте классические методы: вспомогательные столбцы + INDEX/MATCH, или сортировку через Sort & Filter (но она изменяет исходный набор).
• Если нужно сортировать по агрегированным значениям (например, по сумме продаж в разрезе штата), сначала создайте сводную таблицу или используйте LET/UNIQUE/SUMIFS для подготовки вспомогательных диапазонов, а затем примените SORTBY.
• Для визуализации и интерактивных панелей удобнее использовать Power Query: там сортировка и фильтрация выполняются на этапе загрузки данных, а исходный файл остаётся чистым.

Ментальные модели и эвристики

• Модель «вход → ключи → порядок → выход»: сначала укажите исходный массив, затем перечислите ключи сортировки и для каждого — направление.
• Эвристика безопасности: при первом тесте создавайте вывод в отдельной области листа или на новом листе, чтобы избежать перезаписи важной информации.
• Если ключи сортировки не соседствуют, не пытайтесь объединять их — указывайте отдельные диапазоны в нужной последовательности.

Краткая методология применения SORTBY (mini-SOP)

1. Проверить размер исходного диапазона (количество строк/столбцов).
2. Подготовить by_array-диапазоны с тем же количеством элементов.
3. Определить порядок сортировки для каждого ключа (1 или -1).
4. Вставить формулу в свободную область и проверить результат.
5. Зафиксировать формулу (опционально: обернуть в LET или сохранить версию листа).

Чек-листы по ролям

Аналитик:

• Убедиться, что все ключи содержат корректные столбцы.
• Проверить на пустые или неконсистентные значения, которые меняют порядок.
• Тест: сравнить выборку с ручной сортировкой.

Менеджер/Отчётность:

• Выбрать отображаемые поля (array) и место для вывода.
• Согласовать критерии сортировки (например, по KPI в убывании).

Специалист по вводу данных:

• Держать поля штата/имён/дат в единообразном формате (без лишних пробелов).
• Проверить правильность форматов чисел, чтобы сортировка по убыванию/возрастанию работала корректно.

Критерии приёмки

• Выходной массив содержит те же строки, что и исходный диапазон (не теряются данные).
• Сортировка выполнена в правильном порядке для каждого критерия.
• Формула не вызывает ошибок при изменении исходных данных (динамическое обновление).

Тестовые случаи и приёмочные проверки

1. Обычная сортировка по одному столбцу (строка/день): ожидание — упорядоченный массив по возрастанию.
2. Многокритериальная сортировка: ожидание — первичный и вторичный ключи работают совместно.
3. Несовпадающие размеры by_array: ожидание — #VALUE!.
4. Блокировка области вывода: ожидание — предупреждение о конфликте динамического массива.
5. Наличие пустых значений в ключе: ожидание — пустые значения помещаются в начало при возрастании.

Сравнение с близкими функциями (cheat sheet)

• SORT(array, [sort_index], [sort_order]) — сортирует массив, но имеет ограничение по одному критерию в простом варианте; работает хорошо для одноуровневой сортировки.
• SORTBY(array, by_array1, [sort_order1], …) — гибче для нескольких ключей; каждый by_array задаётся отдельно.
• Sort & Filter (лента) — изменяет исходную таблицу, подходит для однократных перестроений в мастер-таблице.
• Power Query — лучше для ETL-процессов и больших трансформаций перед загрузкой.

Совместимость и миграция

SORTBY поддерживается в Microsoft 365 и в современных версиях Excel на настольных платформах и в Excel для веба. Если вы готовите файл для пользователей старых версий (например, классические Excel 2016 без поддержки динамических массивов), документируйте формулы или предоставляйте альтернативные листы с заранее созданными результатами.

Дополнительные приёмы и трюки

• Комбинируйте SORTBY с FILTER и UNIQUE для вывода упорядоченных, уникальных наборов (например, топ‑5 уникальных продавцов по региону).
• Используйте LET для повышения читаемости длинных формул и уменьшения повторов диапазонов.
• Для сложных требований сначала агрегируйте данные (SUMIFS/AGGREGATE или PivotTable), затем сортируйте агрегат через SORTBY.

Пример с FILTER + SORTBY

Чтобы получить уникальные имена продавцов по штату и отсортировать их по количеству продаж:

=SORTBY(
  FILTER(A2:D16, B2:B16="California"),
  FILTER(C2:C16, B2:B16="California"), -1
)

Этот приём возвращает все строки для California и сортирует их по количеству продаж в порядке убывания.

Галерея крайних случаев

• Пустые строки в середине массива: сортировка продолжит работать, но пустые значения окажутся в начале при возрастании.
• Некорректные типы данных (текст вместо чисел): текстовые значения сортируются по алфавиту и могут исказить ожидаемый числовой порядок.
• Перекрытие с диапазоном вывода: формула не выполнится — сначала устраните перекрытие.

Безопасность и приватность

Формулы SORTBY работают локально в файле. Если вы делитесь рабочей книгой в облаке, убедитесь, что в результирующем массиве не раскрываются конфиденциальные данные ненадлежащим лицам.

Краткое резюме

Функция SORTBY — универсальный инструмент для создания отдельных, многокритериально отсортированных представлений данных без изменения исходной таблицы. Она экономит время, упрощает подготовку отчётов и хорошо сочетается с FILTER, UNIQUE и LET.

Быстрый чек-лист перед использованнием

• Убедитесь, что ваша версия Excel поддерживает динамические массивы.
• Подготовьте одинаковые по размеру диапазоны для array и всех by_array.
• Зарезервируйте свободное пространство для вывода массива.
• Тестируйте на небольших выборках прежде чем применять к крупным таблицам.

Полезные напоминания

• sort_order = 1 или -1 только.
• Указывайте каждый ключ сортировки отдельным диапазоном.
• Для агрегированных показателей сначала агрегируйте, затем сортируйте.

Социальный дайджест (короткое объявление)

SORTBY в Excel помогает быстро формировать множественные представления одних и тех же данных: сортируйте по нескольким критериям, не трогая исходную таблицу. Идеально для рейтингов, региональных отчётов и динамических панелей.

Конец

Если нужно, могу подготовить готовые шаблоны формул (для топ‑N, групповой агрегации, комбинированного фильтра) по вашей конкретной таблице.