Сортиране с формула с помощта на СОРТИРАНЕ и СОРТБИ - Съвети на Excel

Тази седмица на конференцията Ignite в Орландо, Флорида, Microsoft дебютира серия от нови, по-лесни формули за масиви в Excel. Ще разглеждам тези нови формули всеки ден тази седмица, но ако искате да прочетете напред:

Понеделник обхвана новата = A2: формула A20, грешката SPILL и новата функция SINGLE, която се изисква вместо имплицитно пресичане
Днес ще обхваща SORT и SORTBY
Сряда ще обхване FILTER
Четвъртък ще обхваща УНИКАЛЕН
Петък ще обхваща функциите SEQUENCE и RANDARRAY

Сортирането с формула в Excel, използвано да изисква безумна комбинация от формули. Разгледайте тези данни, които ще бъдат използвани в цялата статия.

Данни в A3: C11.

За да сортирате това с формула преди тази седмица, просто трябва да нокаутирате RANK, COUNTIF, MATCH, INDEX и INDEX. След като приключите с този набор от формули, ще бъдете готови за дрямка.

Старият начин за сортиране с формула

Джо Макдейд и неговият екип ни донесоха SORT и SORTBY.

Нека започнем със СОРТ. Ето синтаксиса=SORT(Array, (Sort Index), (Sort Order), (By Column))

Функцията SORT

Да предположим, че искате да сортирате A3: C16 по полето за оценка. Резултатът е третата колона в масива, така че вашият индекс за сортиране ще бъде 3.

Изборът за Поръчка за сортиране е 1 за възходящ или -1 за низходящ. Не се оплаквам, но никога няма да има поддръжка за Сортиране по цвят, Сортиране по формула или Сортиране по персонализиран списък, използвайки тази функция.

Посочете 3 като колона за сортиране и -1 като ред за сортиране за низходящо.

Четвъртият аргумент ще се използва рядко. Възможно е в диалоговия прозорец Сортиране да сортирате по колона вместо по редове. 99,9% от хората сортират по редове. Ако трябва да сортирате по колона, посочете True в последния аргумент. Този аргумент е по избор и по подразбиране е False.

Ако трябва да сортирате по колони, използвайте True в четвъртия аргумент

Ето резултатите от формулата. Благодарение на новия двигател за изгаряне, формулата се разлива в съседни клетки. Една формула в O2 произвежда това решение.

Не е необходимо да натискате Ctrl + Shift + Enter

Оригиналните данни са сортирани

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

Сортиране на две нива

Функцията SORTBY ви позволява да сортирате по нещо, което не е в резултатите

Синтаксисът на функцията SORTBY е =SORTBY(array, by_array1, sort_order1,)

СОРТИРАЙ нещо друго

Връщайки се към първоначалните данни. Кажете, че искате да сортирате по Екип, след това Резултат, но да показвате само имената. Можете да използвате SORTBY, както е показано тук.

Сортирайте колона A по колона B и колона C

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

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

На фигурата по-долу искате да сортирате произволно 13-те имена без повторения. Използвайте =SORTBY(A4:A16,RANDARRAY(13)). Прочетете повече за RANDARRAY в петък.

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

Ctrl + Shift + Enter напълно мъртъв ли е? Не. Все още има полза от него. Да предположим, че сте искали само първите 3 резултата от функцията SORT. Можете да изберете три клетки, да въведете функцията SORT и да я следвате с Ctrl + Shift + Enter. Това ще предотврати преливането на резултатите отвъд границите на оригиналната формула.