Excel Sortuj według wzoru przy użyciu SORTOWANIA i SORTOWANIA - Porady programu Excel

W tym tygodniu na konferencji Ignite w Orlando na Florydzie firma Microsoft zadebiutowała serią nowych, łatwiejszych formuł tablicowych w programie Excel. Będę omawiał te nowe formuły każdego dnia w tym tygodniu, ale jeśli chcesz czytać dalej:

W poniedziałek omówiono nową formułę = A2: A20, błąd SPILL i nową funkcję SINGLE wymaganą w miejsce niejawnego przecięcia
Dzisiaj obejmie SORT i SORTBY
Środa obejmie FILTER
Czwartek będzie dotyczył UNIQUE
Piątek obejmie funkcje SEQUENCE i RANDARRAY

Sortowanie według formuły w programie Excel wymagało szalonej kombinacji formuł. Spójrz na te dane, które będą używane w tym artykule.

Dane w A3: C11.

Aby posortować to za pomocą formuły przed tym tygodniem, wystarczyłoby wyeliminować RANK, COUNTIF, MATCH, INDEX i INDEX. Gdy skończysz ten zestaw formuł, będziesz gotowy na drzemkę.

Stary sposób sortowania według formuły

Joe McDaid i jego zespół przynieśli nam SORTOWANIE i SORTOWANIE.

Zacznijmy od SORTOWANIA. Oto składnia=SORT(Array, (Sort Index), (Sort Order), (By Column))

Funkcja SORT

Powiedzmy, że chcesz posortować A3: C16 według pola Wynik. Wynik to trzecia kolumna w tablicy, więc indeks sortowania będzie wynosił 3.

Dostępne opcje kolejności sortowania to 1 - rosnąco lub -1 - malejąco. Nie narzekam, ale nigdy nie będzie obsługi Sortuj według koloru, Sortuj według wzoru lub Sortuj według listy niestandardowej za pomocą tej funkcji.

Określ 3 jako kolumnę sortowania i -1 jako kolejność sortowania malejąco.

Czwarty argument będzie rzadko używany. W oknie dialogowym Sortuj można sortować według kolumn zamiast wierszy. 99,9% ludzi sortuje według wierszy. Jeśli chcesz sortować według kolumn, określ wartość True w ostatnim argumencie. Ten argument jest opcjonalny i domyślnie ma wartość False.

Jeśli chcesz posortować według kolumn, użyj wartości True w czwartym argumencie

Oto wyniki formuły. Dzięki nowemu silnikowi Calc formuła rozlewa się na sąsiednie komórki. Jedna formuła w O2 daje to rozwiązanie.

Nie ma potrzeby wciskania Ctrl + Shift + Enter

Oryginalne dane są sortowane

A co jeśli potrzebujesz sortowania dwupoziomowego? Sortuj według kolumny 2 rosnąco i kolumny 3 malejąco? Podaj stałą tablicową dla drugiego i trzeciego argumentu:=SORT(A2:C17,(2;3),(1;-1))

Sortowanie dwupoziomowe

Funkcja SORT WEDŁUG umożliwia sortowanie według elementów, których nie ma w wynikach

Składnia funkcji SORT WG: =SORTBY(array, by_array1, sort_order1,)

SORTBY coś innego

Wracając do oryginalnych danych. Powiedzmy, że chcesz posortować według zespołu, a następnie wyniku, ale pokaż tylko nazwy. Możesz użyć SORTBY, jak pokazano tutaj.

Sortuj kolumnę A według kolumny B i kolumny C.

Losowe testy narkotykowe i losowe bez powtórzeń

Trudne scenariusze, takie jak losowe testy narkotykowe i losowe bez powtórzeń, stają się oszałamiające, gdy połączysz SORT z RANDARRAY.

Na poniższym rysunku chcesz losowo posortować 13 nazw bez powtórzeń. Użyj =SORTBY(A4:A16,RANDARRAY(13)). Przeczytaj więcej o RANDARRAY w piątek.

Sortowanie losowe bez powtórzeń

Czy Ctrl + Shift + Enter jest całkowicie martwy? Nie. Nadal jest pożytek. Powiedzmy, że chcesz tylko 3 najlepsze wyniki funkcji SORT. Możesz wybrać trzy komórki, wpisać funkcję SORT i wykonać ją za pomocą Ctrl + Shift + Enter. Zapobiegnie to wydostaniu się wyników poza granice oryginalnej formuły.