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

На этой неделе на конференции Ignite в Орландо, Флорида, Microsoft представила серию новых, более простых формул массива в Excel. На этой неделе я буду рассказывать об этих новых формулах каждый день, но если вы хотите читать дальше:

В понедельник была рассмотрена новая формула = A2: A20, ошибка SPILL и новая функция SINGLE, необходимая вместо неявного пересечения.
Сегодня мы поговорим о SORT и SORTBY.
Среда будет охватывать ФИЛЬТР
Четверг накроет UNIQUE
В пятницу будут рассмотрены функции SEQUENCE и RANDARRAY.

Сортировка с помощью формулы в Excel раньше требовала безумного сочетания формул. Взгляните на эти данные, которые будут использоваться в этой статье.

Данные в A3: C11.

Чтобы отсортировать это с помощью формулы до этой недели, вам просто нужно выбить RANK, COUNTIF, MATCH, INDEX и INDEX. Как только вы закончите с этим набором формул, вы будете готовы вздремнуть.

Старый способ сортировки с помощью формулы

Джо МакДэйд и его команда принесли нам SORT и SORTBY.

Начнем с СОРТИРОВКИ. Вот синтаксис=SORT(Array, (Sort Index), (Sort Order), (By Column))

Функция СОРТИРОВКИ

Допустим, вы хотите отсортировать A3: C16 по полю Score. Оценка - это третий столбец в массиве, поэтому ваш индекс сортировки будет равен 3.

Возможные варианты порядка сортировки: 1 по возрастанию или -1 по убыванию. Я не жалуюсь, но никогда не будет поддержки сортировки по цвету, сортировки по формуле или сортировки по настраиваемому списку с использованием этой функции.

Задайте 3 как столбец сортировки и -1 как порядок сортировки по убыванию.

Четвертый аргумент будет использоваться редко. В диалоговом окне «Сортировка» можно сортировать по столбцу, а не по строкам. 99,9% людей сортируют по строкам. Если вам нужно отсортировать по столбцу, укажите True в последнем аргументе. Этот аргумент является необязательным и по умолчанию имеет значение False.

Если вам нужно отсортировать по столбцам, используйте True в 4-м аргументе

Вот результаты формулы. Благодаря новому механизму расчета формула распространяется на соседние ячейки. Одна формула в O2 дает это решение.

Нажимать Ctrl + Shift + Enter не нужно.

Исходные данные отсортированы

Что делать, если вам нужна двухуровневая сортировка? Сортировать по столбцу 2 по возрастанию и столбцу 3 по убыванию? Предоставьте константу массива для 2-го и 3-го аргументов:=SORT(A2:C17,(2;3),(1;-1))

Двухуровневая сортировка

Функция СОРТБИ позволяет сортировать по чему-то, чего нет в результатах

Синтаксис функции СОРТБИ: =SORTBY(array, by_array1, sort_order1,)

СОРТБИ что-то еще

Возвращаемся к исходным данным. Скажем, вы хотите отсортировать по команде, а затем по оценке, но отображать только имена. Вы можете использовать SORTBY, как показано здесь.

Сортировать столбец A по столбцу B и столбцу C

Случайное тестирование на наркотики и случайное без повторов

Сложные сценарии, такие как случайное тестирование на наркотики и случайное без повторов, становятся невероятно простыми, если вы комбинируете SORT с RANDARRAY.

На рисунке ниже вы хотите отсортировать 13 имен случайным образом без повторов. Используйте =SORTBY(A4:A16,RANDARRAY(13)). Узнайте больше о RANDARRAY в пятницу.

Сортировка случайным образом без повторов

Ctrl + Shift + Enter полностью мертв? Нет, это все еще есть применение. Допустим, вам нужны только 3 лучших результата функции SORT. Вы можете выбрать три ячейки, ввести функцию СОРТИРОВКИ и следовать за ней с помощью Ctrl + Shift + Enter. Это предотвратит выход результатов за пределы исходной формулы.