W tym przewodniku dowiesz się, jak alfabetyzować w programie Excel za pomocą funkcji sortowania i filtrowania oraz formuł do porządkowania i formatowania danych w kolejności.
Microsoft Excel to skuteczne oprogramowanie do organizowania ważnych danych, takich jak finanse, dane sprzedaży, informacje o klientach itp. Ułożenie danych w arkuszach w porządku alfabetycznym jest jednym z najlepszych sposobów szybkiego organizowania, uzyskiwania dostępu i odwoływania się do tych danych. Alfabetyzacja zakresu komórek oznacza sortowanie zakresu alfabetycznie w programie Excel. Dane można sortować w obie strony, w kolejności rosnącej lub malejącej.
Załóżmy na przykład, że masz duży zestaw danych z informacjami o zamówieniach klienta i chcesz znaleźć historię zamówień konkretnego klienta. Przeczesywanie całej listy stanie się nie lada udręką. Zamiast tracić czas na przewijanie, możesz uporządkować kolumny alfabetycznie, aby szybko znaleźć swoje dane.
Istnieją różne sposoby alfabetyzacji danych w programie Excel, w zależności od typu zestawu danych, nad którym pracujesz. Excel umożliwia sortowanie pojedynczej kolumny lub pojedynczego wiersza lub wybranego zakresu lub całego arkusza, a także opcje grupowania wielu kolumn lub wierszy podczas ich sortowania. Nauczmy się alfabetyzować dane w arkuszach kalkulacyjnych Excel według wierszy lub kolumn.
Dlaczego alfabetyzacja danych w programie Excel
Alfabetyzacja danych będzie miała wiele zalet:
- Sprawia, że dane są bardziej sensowne i łatwiejsze do odczytania.
- Ułatwia użytkownikowi wyszukanie określonej wartości lub nazwy klienta w arkuszu danych programu Excel.
- Pomaga użytkownikowi wizualnie zidentyfikować zduplikowane rekordy, dzięki czemu możemy zapobiec błędom wprowadzania danych.
- Umożliwia użytkownikom łatwe grupowanie kolumn lub list, dzięki czemu użytkownicy mogą przeglądać je obok siebie.
Sortowanie alfabetyczne (sortowanie alfabetyczne) to łatwy, powszechny sposób szybkiego sortowania informacji. Jest to szczególnie przydatne w przypadku bardzo dużych zbiorów danych. W sumie istnieją 4 sposoby alfabetyzacji danych w programie Excel: przycisk A-Z lub Z-A, funkcja sortowania, funkcja filtrowania i formuły.
Alfabetyzacja kolumny w Excelu
Najszybszym sposobem sortowania alfabetycznego w programie Excel jest użycie wbudowanej funkcji sortowania.
Najpierw otwórz arkusz kalkulacyjny zawierający zestaw danych. Zaznacz komórki lub kolumnę, które chcesz posortować, i nie zaznaczaj pustych komórek. Następnie przejdź do zakładki „Dane” i kliknij „A-Z”, aby posortować rosnąco lub „Z-A”, aby posortować malejąco w grupie Sortuj i filtruj.
Jeśli pomiędzy nimi znajdują się puste komórki, program Excel zakłada, że wszystkie dane zostały posortowane i zatrzymuje się nad czarną komórką. Jeśli chcesz posortować całą kolumnę, wybierz dowolną komórkę w kolumnie i skorzystaj z powyższej opcji.
Dostęp do tych samych opcji można również uzyskać za pomocą narzędzia „Sortuj i filtruj” w sekcji Edycja na karcie „Strona główna”.
Po kliknięciu przycisku natychmiast ułóż listę alfabetycznie w programie Excel.
Jak sortować i przechowywać wiersze razem w programie Excel
Możesz łatwo posortować kolumnę, jeśli masz tylko jedną kolumnę w arkuszu, ale staje się to trochę skomplikowane, gdy arkusz zawiera wiele kolumn obok kolumny, którą chcesz posortować. Czasami pracujemy również nad arkuszami kalkulacyjnymi, w których po posortowaniu kolumny musimy zachować nienaruszone odpowiednie wiersze.
Na przykład, jeśli mamy arkusz kalkulacyjny z listą ocen uczniów. Kiedy sortujemy kolumnę nazwisk uczniów w kolejności alfabetycznej, chcemy również, aby znaki wpisane w rzędach obok nich również się odpowiednio przesuwały.
W takich przypadkach możesz użyć przycisku „A-Z” lub „Z-A”, aby posortować jedną z kolumn w kolejności alfabetycznej, a on automatycznie ułoży dane w innych kolumnach, utrzymując wiersze razem. Zobaczmy, jak to działa na innym przykładzie.
Najpierw wybierz pierwszą kolumnę, według której chcesz posortować inne kolumny. Następnie wybierz „A-Z” lub „Z-A” na karcie Dane lub na karcie Strona główna, aby posortować dane w jednej kolumnie, a program Excel automatycznie odpowiednio zmieni kolejność pozostałych kolumn.
Po kliknięciu przycisku sortowania pojawi się okno dialogowe Ostrzeżenie o sortowaniu. Wybierz przycisk opcji „Rozszerz wybór” i kliknij przycisk „Sortuj”.
Dzięki temu wiersze będą razem podczas sortowania kolumn, bez niezgodności jakichkolwiek danych (jak pokazano poniżej).
Jeśli wybierzesz opcję „Kontynuuj z bieżącym wyborem”, Excel posortuje tylko wybraną kolumnę.
Jak sortować alfabetycznie według wielu kolumn
Jeśli chcesz posortować alfabetycznie zestaw danych według więcej niż jednej kolumny, możesz to zrobić za pomocą funkcji sortowania programu Excel.
Na przykład, chcemy uporządkować poniższą tabelę alfabetycznie najpierw według kraju, a następnie według imienia:
Najpierw wybierz całą tabelę, którą chcesz posortować alfabetycznie, następnie przejdź do zakładki „Dane” i w grupie Sortuj i filtruj kliknij „Sortuj”.
Lub kliknij ikonę „Sortuj i filtruj” w sekcji Edycja na karcie „Strona główna” i wybierz „Sortowanie niestandardowe”, aby uzyskać dostęp do tej samej funkcji.
Pojawi się okno dialogowe Sortuj i pokaże pierwszą kolumnę (parametr), w której tabela zostanie posortowana. W menu rozwijanym „Sortuj według” wybierz kolumnę podstawową, według której chcesz ułożyć dane w kolejności alfabetycznej, w naszym przypadku „Kraj”, a w menu „Zamówienie” wybierz „A do Z” lub „Z do A”. .
Następnie kliknij przycisk „Dodaj poziom”, aby dodać drugi poziom sortowania. Wybierz drugą kolumnę (w naszym przypadku Imię), według której chcesz uporządkować dane w polu „Sortuj według” i wybierz „A do Z” lub „Z do A”. Jeśli Twoja tabela ma nagłówki u góry, zaznacz pole wyboru „Moje dane mają nagłówki”, aby można było pominąć nagłówki podczas ich sortowania. W razie potrzeby dodaj więcej poziomów sortowania i kliknij „OK”.
Tabela jest ułożona alfabetycznie: najpierw według kraju, a następnie według imienia, jak pokazano poniżej.
Jak sortować wiersze alfabetycznie w programie Excel
Czasami możesz chcieć ułożyć alfabetycznie wiersze w programie Excel, a nie kolumny. Można to zrobić za pomocą funkcji sortowania programu Excel. Załóżmy na przykład, że masz arkusz kalkulacyjny, w którym kolumny w pierwszym wierszu od B do T zawierają nazwy miast, w których liczba sklepów dostępnych w ramach różnych typów sklepów, a kolumna A zawiera typ sprzedawcy. Komórki służą do śledzenia liczby sklepów w każdym mieście w różnych kategoriach sprzedawców.
Najpierw zaznacz wszystkie wiersze, które chcesz ułożyć alfabetycznie; Jeśli nie chcesz przenosić etykiet wierszy, pozostaw je poza swoim wyborem. Teraz przejdź do zakładki „Dane” i kliknij „Sortuj” w grupie Sortuj i filtruj.
W oknie dialogowym Sortuj kliknij przycisk „Opcje”.
Pojawi się kolejne małe okno dialogowe z opcją wyboru „Sortuj od lewej do prawej” lub „Sortuj od góry do dołu”. Wybierz opcję „Sortuj od lewej do prawej”, aby zamiast tego sortować alfabetycznie według wierszy, i kliknij „OK”, aby wrócić do okna dialogowego Sortuj.
Teraz wybierz wiersz, który chcesz ułożyć alfabetycznie z listy rozwijanej „Sortuj według” (w tym przykładzie wiersz 1). W drugim polu zachowaj wartości komórek w polu „Sortuj według” i wybierz „od A do Z” (kolejność rosnąco) w polu Kolejność. Następnie kliknij „OK”, aby zamknąć okno dialogowe.
Teraz pierwszy wiersz w naszej tabeli jest posortowany w kolejności alfabetycznej (rosnąco), a pozostałe wiersze są odpowiednio uporządkowane, jak pokazano poniżej.
Jak alfabetycznie posortować kolumnę za pomocą filtra
Innym szybkim sposobem sortowania alfabetycznego w programie Excel jest użycie funkcji Filtruj. Po zastosowaniu filtrów do kolumn dostęp do opcji sortowania dla wszystkich kolumn można uzyskać za pomocą jednego kliknięcia myszy.
Aby dodać filtr do kolumn, wybierz jedną lub więcej kolumn i kliknij opcję „Filtr” w grupie Sortuj i filtruj na karcie „Dane”. Jeśli chcesz zastosować filtr do całej tabeli, zaznacz dowolną komórkę w tabeli i kliknij opcję „Filtruj”.
Możesz również uzyskać dostęp do opcji Filtruj, klikając narzędzie „Sortuj i filtruj” w grupie Edytowanie na karcie „Strona główna” i wybierając „Filtr”.
W każdym nagłówku kolumny pojawi się mała strzałka rozwijana. Kliknij strzałkę dowolnej kolumny, którą chcesz posortować alfabetycznie, i wybierz „Sortuj od A do Z” lub „Sortuj od Z do A”.
Spowoduje to uporządkowanie żądanej kolumny w wybranej kolejności alfabetycznej, a jeśli wybierzesz „Sortuj od A do Z”, na przycisku filtru pojawi się mała strzałka skierowana w górę, wskazująca porządek sortowania (rosnąco):
Zaawansowane sortowanie w niestandardowym zamówieniu
Sortowanie alfabetyczne nie jest idealne dla wszystkich danych alfabetycznych. Czasami dane mogą być uporządkowane alfabetycznie, ale nie jest to najskuteczniejszy sposób ich sortowania.
Wyobraźmy sobie sytuację, w której możesz mieć listę zawierającą nazwy miesięcy lub dni tygodnia, sortowanie alfabetyczne nie jest w takiej sytuacji przydatne. Bardziej sensowne jest posortowanie tej listy chronologicznie. Jeśli posortujesz od A do Z, uporządkuje miesiące w kolejności alfabetycznej, najpierw będzie kwiecień, potem sierpień, luty, czerwiec i tak dalej. Ale nie tego chcesz. Na szczęście bardzo łatwo jest uporządkować chronologicznie w Excelu za pomocą opcji Zaawansowane sortowanie niestandardowe.
Zacznij od wybrania tabeli, którą chcesz posortować. Następnie wybierz opcję „Sortowanie niestandardowe” w sekcji „Sortuj i filtruj” w sekcji Edycja na karcie Strona główna.
W oknie dialogowym Sortuj wybierz kolumnę zawierającą miesiące roku w sekcji kolumn, posortuj według „Wartości”, jak zwykle, a w sekcji Kolejność wybierz „Lista niestandardowa”.
W oknie dialogowym Listy niestandardowe możesz utworzyć własną listę. Istnieje kilka domyślnych list niestandardowych, w tym dni tygodnia, skrócone miesiące, miesiące roku i tak dalej. Wybierz odpowiednią opcję sortowania zgodnie z potrzebami (w naszym przypadku styczeń, luty, ..) i kliknij „OK”. Następnie ponownie kliknij „OK”, aby posortować listę chronologicznie.
Zauważysz, że pomyślnie posortowałeś listę według kolejności miesięcznej.
Jak sortować alfabetycznie w programie Excel za pomocą formuł programu Excel
Jeśli jesteś fanem formuł, możesz użyć formuł programu Excel do ułożenia listy w kolejności alfabetycznej. Dwie formuły, których można użyć do alfabetyzacji danych, to LICZ.JEŻELI i WYSZUKAJ.PIONOWO.
Na przykład mamy listę nazwisk, które chcemy ułożyć alfabetycznie za pomocą formuły.
Aby to posortować, dodamy tymczasową kolumnę o nazwie „Porządek sortowania” do istniejącej tabeli.
W komórce (A2) obok danych użyj następującej formuły LICZ.JEŻELI:
=LICZ.JEŻELI($B$2:$B$20;"<="&B2) Powyższa formuła porównuje wartość tekstową w komórce B2 ze wszystkimi innymi wartościami tekstowymi (B3:B20) i zwraca jej względną rangę. Na przykład w komórce B2 zwraca 11, ponieważ istnieje 11 wartości tekstowych, które są mniejsze lub równe tekstowi „Nancy” w kolejności alfabetycznej. Wynikiem jest porządek sortowania nazwiska pracownika w komórce B2.
Następnie użyj uchwytu wypełniania, przeciągnij tę formułę, aby wypełnić ją w całym zakresie. Daje to porządek sortowania każdej z nazw na liście.
Teraz musimy uporządkować dane pokazane na powyższym zrzucie ekranu w oparciu o numer porządku sortowania, a do tego użyjemy funkcji WYSZUKAJ.PIONOWO.
Składnia:
=WYSZUKAJ.PIONOWO(,A:B,2,0)Tutaj „numer sortowania” reprezentuje liczby w kolejności rosnącej od 1 do 20. W porządku malejącym liczby powinny być od 20 do 1.
Tutaj mamy kolumnę D dla posortowanych nazw. W komórce D2 wprowadź następującą formułę WYSZUKAJ.PIONOWO:
=WYSZUKAJ.PIONOWO(1,A:B,2,0)Podobnie dla drugiej i trzeciej komórki musisz użyć formuły jako :
=WYSZUKAJ.PIONOWO(2,A:B;2,0) oraz =WYSZUKAJ.PIONOWO(3;A:B;2,0) i tak dalej…
Po wprowadzeniu tej formuły WYSZUKAJ.PIONOWO do każdej komórki obok danych lista jest ułożona alfabetycznie.
Zamiast ręcznie wpisywać w/w formułę (1-20) do każdej komórki, możesz również skorzystać z funkcji wiersza, aby ułatwić sobie pracę. Funkcja Row () zwraca numer wiersza bieżącej komórki. Tak więc za pomocą funkcji wiersza formuła będzie wyglądać tak:
=WYSZUKAJ.PIONOWO(WIERSZ()-1;A:B;2,0)Ta formuła da ci taki sam wynik jak powyższa formuła.
A następnie użyj uchwytu wypełniania, aby przeciągnąć tę formułę, aby wypełnić ją w całym zakresie.
Jak alfabetycznie ułożyć wpisy według nazwiska?
Czasami często musimy sortować alfabetycznie arkusze danych z nazwiskami. W takich przypadkach, mimo że imiona zaczynają się od imion, należy je ułożyć alfabetycznie według nazwiska. Możesz to zrobić za pomocą formuł tekstowych programu Excel.
Aby to zrobić, najpierw musisz wyodrębnić imię i nazwisko z pełnych nazwisk do dwóch różnych kolumn. Następnie odwróć nazwy, posortuj je, a następnie przywróć ich pierwotną formę.
Z pełną nazwą w A2 wprowadź następujące formuły w dwóch różnych komórkach (B2 i C2), a następnie skopiuj formuły w dół kolumn (za pomocą uchwytu wypełniania) aż do ostatniej komórki z danymi:
Aby wyodrębnić imię, wprowadź tę formułę w komórce C2:
=LEWO(A2,SZUKAJ(””,A2)-1)
Aby wyodrębnić nazwisko, wprowadź tę formułę w komórce D2:
=PRAWO(A2,DŁ(A2)-SZUKAJ("",A2,1))
A następnie w komórce E2 połączono imiona i nazwiska w odwrotnej kolejności oddzielone przecinkiem:
=D2&", "&C2
Oddzieliliśmy imiona i nazwiska i odwróciliśmy je. Teraz musimy je ułożyć alfabetycznie i odwrócić je z powrotem. Jak widać powyżej, kiedy wyodrębniamy nazwy za pomocą formuły, kolumny C, D i E faktycznie zawierają formuły, ale są sformatowane tak, aby wyglądały jak wartości. Musimy więc przekonwertować formułę na wartości.
Aby to zrobić, najpierw zaznacz wszystkie komórki formuły (E1:E31) i naciśnij Ctrl + C by je skopiować. Kliknij prawym przyciskiem myszy wybrane komórki, kliknij ikonę „Wartości” w sekcji „Opcje wklejania” i naciśnij klawisz „Enter”.
Następnie wybierz dowolną komórkę w wynikowej kolumnie i kliknij opcję „A do Z” lub „Z do A” na karcie „Dane” lub „Strona główna”, zgodnie z własnymi potrzebami.
Po kliknięciu przycisku pojawi się okno dialogowe Ostrzeżenie o sortowaniu. Wybierz opcję „Rozszerz wybór” i kliknij przycisk „Sortuj”.
Jak widać, cała kolumna ułożona alfabetycznie według nazwiska.
Teraz, jeśli chcesz przywrócić go z powrotem do oryginalnego „formatu imienia i nazwiska”, wystarczy wprowadzić te same formuły, ale z różnymi odniesieniami do komórek.
Musisz ponownie rozdzielić nazwę (E2) na dwie części, korzystając z poniższych wzorów:
W G2 wyodrębnij imię:
=PRAWO(E2,DŁ(E2)-SZUKAJ(" ",E2))
W G2 pociągnij nazwisko:
=LEWO(E2,SZUKAJ("",E2)-2)
I połącz te dwie części, aby uzyskać oryginalne pełne imię i nazwisko:
=G2&" "&H2
Za każdym razem, gdy wprowadzasz formułę w pierwszej komórce, skopiuj formułę w dół kolumny (za pomocą uchwytu wypełniania) aż do ostatniej komórki z danymi.
Teraz wszystko, co musisz zrobić, to jeszcze raz przekonwertować formuły na konwersję wartości, tak jak zrobiliśmy to powyżej, i gotowe. to zrobione i.