Jak odwrócić tabelę do góry nogami? I do czego to może się przydać?

Odwracanie tabeli przy pomocy opcji menu

Zacznijmy od tego jak odwrócić tabelę “normalnie”, czyli przy pomocy opcji w menu. Najpierw należy zrobić kolumnę z ciągiem liczb, a potem posłużyć się opcją Dane —> sortuj zakres. Tak jak na filmie:

Odwracanie tabeli przy użyciu formuły QUERY

Posłużymy się tu formułą SEQUENCE (której poświęciłem cały wpis) i formułą QUERY (też ma swój wpis).

Ogólna zasada jest taka jak przy odwracaniu za pomocą menu, ale działa automatycznie. Dopisujemy dodatkową kolmnę z ciągiem znaków, a następnie sortujemy po tej kolumnie. Ale jej nie wyświetlamy.

=Query( {A2:C\sequence(rows(A2:C))}; "select Col1, Col2, Col3 where Col1 is not null order by Col4 desc")

{A2:C \ sequence(rows(A2:C))} bierze pierwsze 3 kolumny, a na prawo od nich buduje ciąg o długości takiej ile jest wierszy w kolumnie A2:C. Zapis klamrowy pozwala tak je ustawić.

"select Col1, Col2, Col3 where Col1 is not null order by Col4 desc" oznacza wyświetl pierwsze 3 kolumny, pod warunkiem że kolumna 1 nie jest pusta, a następnie posortuj kolumny według zawartości kolumny czwartej.

Odwracanie tabeli przy użyciu sort() i array_constrain()

Mniej elegancki i mniej uniwersalny sposób odwracania tabeli to dodanie ciągu liczb do 4 kolumny, posortowanie zakresu po jej zawartości i przycięcie tabeli od prawej.

=array_constrain( sort( {A2:C21\sequence(rows(A2:C21))}; 4;0); rows(A2:C21);3)

Odwracanie tabeli przy użyciu SORT() i ROW()

Tu wykorzystujemy właściwości formuły sort.

Otóż jej składnia sort(zakres;kolumna sortowania;czy rosnąco; 2 kolumna sortowania; czy rosnąco...)

pozwala wpisać nr kolumny po której mamy sortować, a może ją stworzyć. W tym wypadku zrezygnujemy z formuły sequence(), a użyjemy row(zakres), która również jest w stanie stworzyć ciąg liczb na bazie numerów wierszy.

=SORT(A2:C;ROW(A2:A)*(A2:A<>"");0)

Tą formułę znalazłem na blogu infoinspiered.com i nieco uprościłem.

Działa jak złoto. Ale o co tu chodzi?

A2:C – wiadomo – to zakres, który sortujemy

ROW(A2:A)*(A2:A<>””) bierze listę z numerami wierszy z zakresu. Jeśli w wierszu coś jest to podaje numer wiersza, a jeśli nie ma nic to podaje zero (numer wiersza jest mnożony przez zero (fałsz) – wartość logiczną z testu A2:A<>””)

Ostatnie zero to informacja, że sortujemy malejąco.

Wyszukiwanie od dołu – czyli to do czego najczęściej przyda się odwracanie

Każdy kto korzysta z formuły vlookup (wyszukaj.pionowo) wie, że zwraca ona tylko pierwszą napotkaną wartość w tabeli, spełniającą kryteria. Jeśli chcemy znaleźć więcej wartości, używamy query lub filter, natomiast jeśli ostatnią, to należy do vlookup podstawić tabelę odwróconą.

Załóżmy, że w tabeli są aż 3 wyniki Stanisława Gałasia.

Na spróbujmy wyszukać jego wynik po imieniu. Użycie standardowego vlookup da nam wynik 1200 (założywszy, że w E2 jest “Stanisław”)

=vlookup(E2;A2:C;3;0) daje 1200

Natomiast jeśli odwrócimy tabelę to uzyskamy 834

=vlookup(E3;SORT(A2:C;ROW(A2:A)*(A2:A<>"");0);3;0) – daje 834

Dla czepialskich – wyszukiwanie uwzględaniające obie kolumny (imię i nazwisko), będzie wyglądać następująco:

Od góry:

=vlookup(E7;{arrayformula(A2:A&" "&B2:B)\C2:C};2;0)

(jeśli w E7 jest Stanisław Gałaś)

Od dołu

=vlookup(E8;SORT({arrayformula(A2:A&" "&B2:B)\C2:C};ROW(A2:A)*(A2:A<>"");0);2;0)

(jeśli w E8 jest Stanisław Gałaś)