Sortowanie arkusza według własnych reguł

Załóżmy, że masz posortować tabelę według pór roku (np. kolekcję ubrań). Nie jesteś w stanie tego zrobić używając zwykłego sortowania alfabetycznego czy wg liczb. Podobnie, gdy spróbujesz ustawić spis piłkarzy według ich pozycji, od bramkarza do napastnika. W takiej sytuacji potrzebujesz własnych reguł.

Przy lekturze warto posiłkować się działającym arkuszem z pobranymi danymi.

W tym wpisie posłużę się przykładem listy polskich medalistów w boksie. Posortujemy ich według kategorii wagowych. Od najlżejszych do superciężkich. Bez patrzenia na wartości kilogramów (które zmieniały się na przestrzeni lat)

Ze stron encyklopedii PWN ściągnąłem tabelę:

Ustalamy własną tabelę sortowania

Tabelę wpisuję ręcznie. W pierwszej kolumnie są nazwy kategorii, a w drugiej ich porządana kolejność:

Jeśli tabela jest krótka lub nie mamy miejsca w arkuszu, możemy tabelę pomocniczą zapisywać wewnątrz formuł:

{"papierowa"\1;"musza"\2;"kogucia"\3;"piórkowa"\4; ... }

Dodajemy pomocniczą kolumnę do sortowania

W tym celu używamy formuły VLOOKUP (WYSZUKAJ.PIONOWO), która dla wartości z kolumny E wyszuka wartość w tabeli pomocniczej. Liczba 2 w formule oznacza, że ma być wyświetlana zawartość drugiej kolumny tabeli pomocniczej.

Jak już ustaliliśmy wyszukiwanie, dla pojedynczej komórki, rozszerzmy działanie VLOOKUP na całą kolumnę E przy pomocy ARRAYFORMULA:

Sortujemy według pomocniczej kolumny

Następny krok to wyświetlenie całej tabeli już posortowanej. Użyjemy do tego formuły SORT

SORT(tabela; numer kolumny; czy rosnąco)

Wyłączamy wyświetlanie pomocniczej kolumny

Nie chcemy, żeby nasza pomocnicza kolumna była widoczna dla oglądających arkusz. Możemy ją schować ręcznie korzystając z menu:

Jest jeszcze lepsza metoda, pozwalająca pozbyć się kolumny przy pomocy formuły. To ARRAY_CONSTRAIN:

ARRAY_CONSTRAIN(tabela; ile wierszy wyświetlić; ile kolumn wyświetlić)

Wsadźmy więc formułę sortującą w Array_constrain i każmy wyświetlić tylko pierwsze 4 kolumny:

Łączymy wszystkie etapy w jedną formułę

Na koniec pozbędziemy się też pomocnicznej kolumny F. Skoro wygenerowaliśmy jej zawartość przy pomocy jednej formuły VLOOKUP z ARRAYFORMULA, możemy tą formułę zagnieździć w większej formule.

Wszystkie elementy już widzieliście:

{B5:E47\....} – to oznacza, że bierzemy tabelę B5:E47, a na prawo od niej wstawimy jeszcze jedną kolumnę (piątą).

arrayformula(vlookup(E5:E47;O4:P15;2;false)) – to formuła pobierająca dane z tabeli pomocniczej.

sort(....;5;0) – sortuje wygenerowaną zawartość według jej piątej kolumny

Arrray_constrain(....;1000;4) przycina posortowaną już tabelę, wyświetlając tylko pierwsze 4 kolumny.

Da się tabelę zaimportować od razu posortowaną

Tu trochę wychodzimy poza temat.

Jeśli importujemy tabelę z sieci przy pomocy IMPORTHTML czy z innego Arkusza przy pomocy IMPORTRANGE, możemy pociągnąć ją od razu posortowaną wedle naszych własnych kryteriów.

Korzystając z IMPORTHTML ciągniemy tabelę. Używamy też formuły QUERY by pozbyć się jej nagłówków i żeby do vlookup wstawić tylko czwartą jej kolumnę.