Wbudowane sortowanie danych w programie Excel jest niesamowite, ale nie jest dynamiczne. Jeśli posortujesz dane, a następnie dodasz do nich dane, będziesz musiał je posortować ponownie.
Sortuj dane w kolejności alfabetycznej
W tym poście pokażę Ci różne sposoby sortowania danych w kolejności alfabetycznej za pomocą formuł. Oznacza to, że możesz dodać dane, które automatycznie posortują je za Ciebie.
Gdy wszystkie dane są tekstem bez duplikatów
Załóżmy, że masz dane, jak pokazano poniżej:
W tym przykładzie wszystkie dane są w formacie tekstowym (bez liczb, pustych miejsc lub duplikatów). Aby to posortować, użyję kolumny pomocniczej. W kolumnie obok danych użyj następującej formuły LICZ.JEŻELI:
=LICZ.JEŻELI($A$2:$A$9;"<="&A2)
Ta formuła porównuje wartość tekstową ze wszystkimi innymi wartościami tekstowymi i zwraca jej względną rangę. Na przykład w komórce B2 zwraca 8, ponieważ istnieje 8 wartości tekstowych, które są mniejsze lub równe tekstowi „US” (w kolejności alfabetycznej).
Teraz, aby posortować wartości, użyj następującej kombinacji funkcji INDEKS, DOPASUJ i WIERSZE:
=INDEKS($A$2:$A$9;DOPASOWANIE(WIERSZE($B$2:B2);$B$2:$B$9,0))
Ta formuła po prostu wyodrębnia nazwy w kolejności alfabetycznej. W pierwszej komórce (C2) szuka nazwy kraju o najniższym numerze (Australia ma 1). W drugiej komórce zwraca Kanadę (która ma numer 2) i tak dalej…
Uczulony na kolumny pomocnika?
Oto formuła, która zrobi to samo bez kolumny pomocniczej.
=INDEKS($A$2:$A$9;DOPASUJ(WIERSZE($A$2:A2);LICZ.JEŻELI($A$2:$A$9;"<="&$A$2:$A$9);0))
To jest formuła tablicowa, więc użyj Control + Shift + Enter zamiast Enter.
Zostawię to do odszyfrowania.
Spróbuj sam… Pobierz przykładowy plik
Ta formuła działa dobrze, jeśli masz wartości tekstowe lub alfanumeryczne.
Ale zawodzi żałośnie, jeśli:
- Masz duplikaty w danych (spróbuj dwukrotnie podać US).
- W danych są puste miejsca.
- Masz mieszankę liczb i tekstu (spróbuj umieścić 123 w jednej z komórek).
Gdy dane są mieszanką liczb, tekstu, duplikatów i spacji
Teraz ten jest trochę trudny. Użyję 4 kolumn pomocniczych, aby pokazać, jak to działa (a następnie podam ogromną formułę, która zrobi to bez kolumn pomocniczych). Załóżmy, że masz dane, jak pokazano poniżej:
Możesz zobaczyć, że są zduplikowane wartości, puste i liczby. Dlatego użyję kolumn pomocniczych, aby rozwiązać każdy z tych problemów.
Kolumna pomocnika 1
Wprowadź następującą formułę LICZ.JEŻELI w kolumnie pomocnika 1
=LICZ.JEŻELI($A$2:$A$9;"<="&A2)
Ta formuła wykonuje następujące czynności:
- Zwraca 0 dla spacji.
- W przypadku duplikatów zwraca ten sam numer.
- Tekst i liczby są przetwarzane równolegle, a ta formuła zwraca tę samą liczbę dla tekstu i liczby (na przykład 123 i Indie otrzymują 1).
Kolumna pomocnika 2
Wprowadź następującą funkcję IS w kolumnie Pomocnika 2:
=--CZY.LICZBA(A2)
Kolumna pomocnika 3
Wprowadź następującą formułę w kolumnie Pomocnika 3:
=--NIEPUSTY(A2)
Kolumna pomocnika 4
Wprowadź następującą formułę w kolumnie Pomocnika 4
=JEŻELI(CZY.LICZBA(A2);B2,JEŻELI(CZY.PUSTE(A2);B2,B2+$C$10))+$D$10
Ideą tej formuły jest segregowanie pustych miejsc, liczb i wartości tekstowych.
- Jeśli komórka jest pusta, zwraca wartość z komórki B2 (która zawsze będzie równa 0) i dodaje wartość z komórki D10. Krótko mówiąc, zwróci całkowitą liczbę pustych komórek w danych
- Jeśli komórka jest wartością liczbową, zwróci rangę porównawczą i doda całkowitą liczbę pustych miejsc. Na przykład dla 123 zwraca 2 (1 to pozycja 123 w danych, a 1 pusta komórka)
- Jeśli jest to tekst, zwraca pozycję porównawczą i dodaje łączną liczbę wartości liczbowych i spacji. Na przykład w przypadku Indii dodaje pozycję porównawczą tekstu w tekście (która wynosi 1) oraz dodaje liczbę pustych komórek i liczbę wartości liczbowych.
Wynik końcowy - posortowane dane
Teraz użyjemy tych kolumn pomocniczych, aby uzyskać posortowaną listę. Oto wzór:
=JEŻELIBŁĄD(INDEKS($A$2:$A$9;DOPASOWANIE(MAŁY($E$2:$E$9;WIERSZE($F$2:F2))+$D$10);$E$2:$E$9,0)) ,"")
Ta metoda sortowania staje się teraz niezawodna. Pokazałem ci metodę dla 8 przedmiotów, ale możesz ją rozszerzyć do dowolnej liczby przedmiotów.
Spróbuj sam… Pobierz przykładowy plik
Jedna formuła do sortowania wszystkiego (bez kolumn pomocniczych)
Jeśli potrafisz obsługiwać ekstremalne formuły, oto formuła typu „wszystko w jednym”, która sortuje dane w kolejności alfabetycznej (bez kolumny pomocniczej).
Oto wzór:
=JEŻELIBŁĄD(INDEKS($A$2:$A$9;MATCH(MAŁY(NIE($A$2:$A$9=)) $ A 9 $, "<=" i $ A 2 $: $ A 9 $, COUNTIF (2 $ A $ 2 $ A $ 9, "< = "& $ A $ 2: $ A 9 $ 9) + SUMA (--ISNUMBER ($ A $2:$A$9))),WIERSZE($A$2:A2)+SUMA(--ISBLANK($A$2:$A$9))),NIE($A$2:$A$9="")*JEŚLI (CZY.LICZBA($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<=" &$A$2:$A$9)+SUMA(--CZY.LICZBA($A$2:$A$9))),0)),"")
Wprowadź tę formułę w komórce i przeciągnij ją w dół, aby uzyskać posortowaną listę. Ponadto, ponieważ jest to formuła tablicowa, użyj Control + Shift + Enter zamiast Enter.
Ta formuła ma praktyczne zastosowanie. Co myślisz? Chciałbym się od ciebie nauczyć. Zostaw swoje ślady w sekcji komentarzy!
