Как использовать SORTBY в Excel

Функция SORTBY позволяет динамически сортировать таблицы и диапазоны по одному или нескольким критериям, сохраняя исходные данные без изменений. Работает в Excel 2021 и Microsoft 365; возвращает динамический массив, поэтому учитывайте возможную ошибку #SPILL!. В статье — синтаксис, пошаговые примеры, сочетание с FILTER, сравнение с SORT, сценарии отказа, чек-лист и практические шаблоны.

Быстрые ссылки

• Синтаксис SORTBY
• Сортировка по одному столбцу
• Сортировка по нескольким столбцам
• Использование SORTBY вместе с FILTER
• SORT и SORTBY — чем отличаются

Совместимость и требования

Важно знать, что на момент написания (April 2025) функция SORTBY доступна в:

• Excel для Microsoft 365 (ПК и Mac)
• Excel 2021 и более поздних версиях
• Excel для Интернета
• мобильных и планшетных приложениях Excel

Если у вас более ранняя версия Excel, SORTBY не будет работать — используйте классические методы сортировки, сводные таблицы или формулы на основе INDEX/SMALL/LARGE.

Важно: SORTBY возвращает динамический массив. Убедитесь, что справа и ниже достаточно пустых ячеек, иначе появится ошибка #SPILL!.

Синтаксис SORTBY

=SORTBY(a, b1, b2, b3, b4...)

где

• a (обязательный) — массив или диапазон, который нужно отсортировать;
• b1 (обязательный) — первый массив или диапазон, по которому сортировать;
• b2 (необязательный) — порядок сортировки для b1 (1 для возрастания, -1 для убывания);
• далее можно указывать пары массив-порядок до 64 сочетаний.

Правила совместимости размеров

Аргументы a, b1, c1 и т.д. должны иметь совместимые размеры. Например, если a — диапазон из 10 строк, все сортирующие массивы тоже должны содержать 10 строк.

Пояснение терминов

• Динамический массив — результат формулы, который «выпадает» (spills) в соседние ячейки автоматически.
• Порядок сортировки — числовой флаг: 1 = по возрастанию/алфавиту, -1 = по убыванию/обратному алфавиту.

Пример 1. Сортировка по одному столбцу

Предположим, есть таблица T_Targets. Нужно получить новый отсортированный список по столбцу Points, но не менять исходную таблицу.

Шаги:

1. Подготовьте область для результата — скопируйте заголовки столбцов и оставьте достаточно свободных строк (например, ячейки G1:K1 для заголовков). Разместите формулу в верхней левой ячейке области результата (G2).
2. Введите формулу:

=SORTBY(T_Targets, T_Targets[Points], -1)

3. Нажмите Enter. Результат появится как динамический массив; вокруг него появитcя рамка, указывающая на спилл.

Примечания:

• Формула может находиться на другом листе или в другой рабочей книге. Если вы ссылаетесь на другую книгу, обе книги должны быть открыты, иначе появится #REF!.
• После заполнения результата можно применить условное форматирование к самому результату, например, подсветить строки с Met? = Yes.

Пример 2. Сортировка по нескольким столбцам

SORTBY удобен, когда нужно сортировать по нескольким критериям (например, сначала по Met?, затем по Points).

Формула:

=SORTBY(T_Targets, T_Targets[Met?], -1, T_Targets[Points], -1)

Логика:

• Первый критерий сортировки — столбец Met? (так, чтобы значения “Yes” оказались выше “No”);
• Второй критерий — Points в порядке убывания внутри каждой группы Met?.

Результат: сначала все строки с Met? = Yes в порядке убывания очков, затем — с Met? = No в порядке убывания очков.

Пример 3. Сортировка и фильтрация вместе

SORTBY работает в сочетании с FILTER, позволяя одновременно сортировать и отбирать подмножества данных.

Задача: получить список людей, у которых Points > 55, и отсортировать этот список по Points по убыванию.

Формула:

=FILTER(SORTBY(T_Targets, T_Targets[Points], -1), SORTBY(T_Targets[Points], T_Targets[Points], -1) > 55)

Разбор формулы:

• Внутренний SORTBY(T_Targets, T_Targets[Points], -1) формирует отсортированную таблицу — это аргумент array функции FILTER.
• Условие фильтрации SORTBY(T_Targets[Points], T_Targets[Points], -1) > 55 тоже возвращает отсортированный массив значений Points, по которому FILTER определяет, какие строки оставить.

Ошибки и распространённые проблемы

• #SPILL! — недостаточно свободного места для динамического массива. Удалите блокирующие ячейки или освободите область.
• #REF! — ссылка на внешний файл, который закрыт.
• Несовместимые размеры массивов — если массив сортировки имеет отличную длину от а, Excel вернёт ошибку.

Рекомендации по отладке:

• Временно используйте формулу в соседнем чистом листе, чтобы убедиться, что она «выпадает» как ожидается.
• Проверяйте размеры таблицы с помощью функций ROWS и COLUMNS, чтобы убедиться в совпадении размеров.

Когда SORTBY не подходит

• У вас старая версия Excel, где нет динамических массивов.
• Нужно получить статичный отсортированный список, который не должен обновляться при изменении исходных данных (в этом случае можно скопировать и вставить значения).
• Вы хотите сортировать по формуле, зависящей от позиций строки (индексная сортировка) — возможно, проще использовать INDEX с SMALL/LARGE.

Альтернативы и когда их использовать

• SORT — проще для одноуровневой сортировки по индексу столбца, но менее гибок при динамических именованных диапазонах.
• Сводные таблицы — лучше для сводных отчетов и быстрой агрегации; не подходят, если нужен точный исходный порядок строк в результирующем наборе.
• INDEX + MATCH + SEQUENCE — полезно в сложных случаях с вычисляемыми ключами и для обратной совместимости.

Сравнение SORT и SORTBY

Пошаговый рабочий процесс для внедрения SORTBY в отчёты (SOP)

1. Проверить совместимость Excel на всех рабочих станциях (Microsoft 365 / Excel 2021+).
2. Определить исходную таблицу и убедиться в наличии таблицы Excel (Ctrl+T) с именованными столбцами.
3. Подготовить область для вывода: скопировать заголовки и выделить пустую область с запасом.
4. Составить формулу SORTBY, начиная с массива a и добавляя пары критерий-порядок по приоритету.
5. Вставить формулу в верхнюю левую ячейку области результата.
6. Проверить отсутствие ошибок (#SPILL!, #REF!, #VALUE!).
7. Применить дополнительное форматирование и защиту листа при необходимости.
8. Зафиксировать в документации: какая формула где используется и какие критерии сортировки применяются.

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

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

Чек-лист для использования SORTBY

• Исходные данные оформлены как таблица Excel (рекомендуется).
• Имена столбцов корректны и уникальны.
• В целевой области достаточно пустых ячеек для спилла.
• Проверены внешние ссылки (если используются).
• Установлены правила форматирования для результата (если нужно).

Шаблоны формул и шпаргалка

Частые шаблоны:

• Только один критерий, убывание:

=SORTBY(Table, Table[Points], -1)

• Два критерия: сначала булево/категориальное поле, потом число:

=SORTBY(Table, Table[Status], -1, Table[Score], -1)

• Комбинация с FILTER для ограничения по условию:

=FILTER(SORTBY(Table, Table[Score], -1), Table[Score] > 50)

• Сортировка по вычисляемому ключу (формула внутри SORTBY):

=SORTBY(Table, Table[Score] + Table[Bonus], -1)

Тестовые сценарии и критерии приёмки (QA)

1. Добавить строку в середине таблицы; проверить, что в результате она появляется и сортируется корректно.
2. Изменить значение в столбце критерия сортировки; проверить немедленное обновление результата.
3. Закрыть внешний файл с данными; проверить, что появляется #REF! при ссылке на закрытый файл.
4. Уменьшить или увеличить число строк в исходной таблице; проверить совместимость размеров массивов.

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

• Правило приоритетов: определите самый важный критерий (1), затем вспомогательные (2, 3…). SORTBY применяет приоритет по порядку аргументов.
• Модель стабильности: если важен относительный порядок внутри группы, сначала сортируйте по внешнему признаку, затем по внутреннему.
• Модель отказоустойчивости: используйте именованные столбцы и таблицы, чтобы формулы не ломались при изменении структуры листа.

Советы по производительности

• Большие таблицы с множеством вложенных функций FILTER/SORTBY могут быть медленнее. Оптимизируйте расчётную область и избавьтесь от лишних volatile-функций.
• Если нужно статичное состояние, после получения результата скопируйте диапазон и вставьте как значения.

Матрица совместимости и миграционные заметки

• Excel 365 / Excel 2021: поддержка полная.
• Excel 2019 и старше: отсутствует — используйте SORT, сводные таблицы или формулы на основе INDEX/MATCH.
• Excel Online: поддержка есть, но учтите ограничение производительности при больших наборах данных.

Примеры «когда это не работает» и обходные пути

Сценарий: нужно сортировать набор данных, который приходит как текстовый CSV и не преобразован в таблицу. Ошибка: несоответствие диапазонов. Решение: преобразовать диапазон в таблицу (вкладка Вставка → Таблица) и использовать структурированные имена.

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

Пример использования с INDEX для обратной совместимости

Если ваша организация использует старые версии Excel, можно вручную смоделировать многокритериальную сортировку через вспомогательные столбцы с составными ключами и затем применять INDEX + MATCH. Это длиннее, но даёт совместимость.

Решение проблем безопасности и приватности

• SORTBY сам по себе не отправляет данные наружу; но если вы ссылаетесь на внешние рабочие книги, убедитесь, что доступ к этим файлам ограничен.
• При публикации на SharePoint/OneDrive проверьте права доступа: динамические формулы будут вычисляться для каждого пользователя в контексте их сессии.

Быстрая шпаргалка по эффективности (чек-шиет)

• Используйте именованные таблицы — упрощает поддержку.
• Ограничьте количество вложенных вычислений в SORTBY.
• Для больших массивов проверяйте влияние на время пересчёта.

Пример Mermaid: выбор подхода

flowchart TD
  A[Нужно преобразовать данные для анализа?] --> B{Требуется статичный результат?}
  B -- Да --> C[Использовать SORTBY, затем Paste Values]
  B -- Нет --> D{Ваша версия Excel поддерживает SORTBY?}
  D -- Да --> E[Использовать SORTBY '+FILTER если нужно']
  D -- Нет --> F[Использовать сводную таблицу или INDEX/MATCH]
  E --> G[Проверить #SPILL! и применить форматирование]
  C --> G
  F --> G

Короткий набор шаблонов для социальных сетей и анонса

OG заголовок: SORTBY в Excel: сортировка по нескольким критериям OG описание: Научитесь сортировать динамические массивы с SORTBY, комбинировать с FILTER и избегать ошибок #SPILL!. Простые шаблоны и чек-лист.

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

• SORTBY — гибкая функция для многокритериальной сортировки, возвращающая динамический массив.
• Используется в Excel 2021 и Microsoft 365; до 64 пар массив-порядок.
• Часто комбинируется с FILTER для одновременно фильтрации и сортировки.

Ключевые выводы

• Используйте именованные таблицы для устойчивых ссылок.
• Убедитесь в совместимости размеров массивов.
• При необходимости используйте Paste Values для фиксации результата.

Полезные формулы и шпаргалка — краткий список

• Сортировка по одному столбцу по убыванию:

=SORTBY(TableName, TableName[Column], -1)

• Сортировка по нескольким столбцам:

=SORTBY(TableName, TableName[Primary], -1, TableName[Secondary], 1)

• Сортировка с фильтрацией:

=FILTER(SORTBY(Table, Table[Score], -1), Table[Score] > Threshold)

• Комбинированный ключ:

=SORTBY(Table, Table[Region] & Table[Category], 1)

Еще один способ быстро получить новый представимый набор данных без изменения исходника — сводная таблица. Она отлично подходит для агрегации и быстрого анализа, когда не требуется точное сохранение исходных строк в новом порядке.