jak alfabetyzować w Excelu: sortowanie kolumn i wierszy A-z lub Z-a

Ten samouczek nauczy cię kilku szybkich i łatwych sposobów umieszczania programu Excel w porządku alfabetycznym. Zapewnia również rozwiązania dla nietrywialnych zadań, na przykład jak alfabetyzować według nazwiska, gdy wpisy zaczynają się od imienia.

alfabetyzacja w Excelu jest tak prosta jak ABC., Niezależnie od tego, czy sortujesz cały arkusz roboczy lub wybrany zakres, pionowo (kolumna) lub poziomo (wiersz), rosnąco (od A do Z) lub malejąco (Z do A), w większości przypadków zadanie można wykonać za pomocą kliknięcia przycisku. W niektórych sytuacjach wbudowane funkcje mogą jednak potknąć się, ale nadal można znaleźć sposób na sortowanie według kolejności alfabetycznej za pomocą formuł.

Ten samouczek pokaże Ci kilka szybkich sposobów alfabetyzacji w Excelu i nauczy, jak przewidywać i zapobiegać problemom z sortowaniem.,ize kolumna w Excelu

jak alfabetyzować w Excelu

ogólnie rzecz biorąc, istnieją 3 główne sposoby sortowania Alfabetycznie w Excelu: przycisk a-z lub z-a, funkcja sortowania i filtr., Poniżej znajdziesz szczegółowe wskazówki dotyczące każdej metody.

Jak sortować kolumnę Alfabetycznie

najszybszy sposób sortowania alfabetycznego w Excelu jest następujący:

1. Wybierz dowolną komórkę w kolumnie, którą chcesz sortować.
2. na karcie Dane, w grupie Sortuj i filtruj, kliknij A-Z, Aby sortować rosnąco lub z-A, aby sortować malejąco. Zrobione!,

te same przyciski można również uzyskać z zakładki Home > Grupa edycji > Sortuj i filtruj:

tak czy inaczej, Excel natychmiast ułoży Alfabetycznie Twoją listę:

Alfabetyzuj i zachowuj wiersze razem

Jeśli zestaw danych zawiera dwie lub więcej kolumn, możesz użyć przycisku A-Z LUB Z-A, aby umieścić jedną z kolumn w porządku alfabetycznym, a program Excel automatycznie przeniesie dane do innych kolumn, zachowując wiersze nienaruszone.

Jak widać w posortowanej tabeli po prawej stronie, powiązane informacje w każdym wierszu są przechowywane razem:

w niektórych sytuacjach, głównie gdy wybrana jest tylko jedna lub kilka komórek w środku zestawu danych, program Excel nie jest pewien, którą część danych posortować i prosi o instrukcje., Jeśli chcesz posortować całą tabelę, pozostaw zaznaczoną domyślną opcję Rozwiń zaznaczenie i kliknij Sortuj:

filtruj i alfabetyzuj w Excelu

innym szybkim sposobem sortowania alfabetycznego w Excelu jest dodanie filtra. Piękno tej metody polega na tym, że jest to konfiguracja Jednorazowa-po zastosowaniu filtra automatycznego opcje sortowania dla wszystkich kolumn są oddalone tylko o jedno kliknięcie myszy.

dodanie filtra do tabeli jest proste:

1. wybierz jeden lub kilka nagłówków kolumn.,
2. na karcie Home, w grupie edycji, kliknij Sortuj i filtruj> Filtr.
3. małe strzałki rozwijane pojawią się w każdym nagłówku kolumny. Kliknij strzałkę rozwijaną dla kolumny, którą chcesz umieścić w porządku alfabetycznym, i wybierz Sortuj od A do Z:

kolumna jest ułożona Alfabetycznie od razu, a mała strzałka w górę na przycisku filtrowania wskazuje kolejność sortowania (rosnąco):

aby Odwróć kolejność, wybierz Sortuj Z do a z menu rozwijanego filtr.,

aby usunąć filtr, wystarczy ponownie kliknąć przycisk Filtr.

Jak umieścić wiele kolumn w porządku alfabetycznym

Jeśli chcesz alfabetyzować dane w kilku kolumnach, użyj polecenia sortowania Excel, które daje większą kontrolę nad sortowaniem danych.

jako przykład dodajmy jeszcze jedną kolumnę do naszej tabeli, a następnie uporządkujmy pozycje Alfabetycznie najpierw według regionu, a następnie według nazwy:

aby to zrobić, wykonaj następujące kroki:

1. wybierz całą tabelę, którą chcesz posortować.,

   w większości przypadków można wybrać tylko jedną komórkę, a program Excel automatycznie wybierze resztę danych, ale jest to podejście podatne na błędy, zwłaszcza gdy w danych są pewne luki (puste komórki).

2. na karcie Dane, w grupie filtrów Sortuj&kliknij Sortuj
   
3. pojawi się okno dialogowe Sortuj z pierwszym poziomem sortowania utworzonym dla Ciebie automatycznie zgodnie z programem Excel.

   W rozwijanym polu Sortuj według wybierz kolumnę, którą chcesz najpierw alfabetyzować, Region w naszym przypadku., W pozostałych dwóch polach pozostaw domyślne ustawienia: sortowanie wartości komórek i kolejność-a do Z:
   

   Tip. Jeśli pierwsze rozwijane menu pokazuje litery kolumn zamiast nagłówków, zaznacz pole moje dane mają nagłówki.
4. kliknij przycisk Dodaj poziom, aby dodać następny poziom i wybierz opcje dla innej kolumny.

   w tym przykładzie drugi poziom sortuje wartości w kolumnie Nazwa Alfabetycznie od A do Z:
   

   Tip., Jeśli sortujesz według wielu kolumn o tych samych kryteriach, kliknij Kopiuj poziom zamiast Dodaj poziom. W takim przypadku będziesz musiał tylko wybrać inną kolumnę w pierwszym polu.
5. Dodaj więcej poziomów sortowania w razie potrzeby i kliknij OK.

Excel posortuje Twoje dane w określonej kolejności., Jak pokazano na poniższym zrzucie ekranu, nasza tabela jest ułożona Alfabetycznie dokładnie tak, jak powinna: najpierw według regionu, a następnie według nazwy:

jak sortować wiersze Alfabetycznie w Excelu

Jeśli dane są ułożone poziomo, możesz chcieć sortować je alfabetycznie między wierszami. Można to również zrobić za pomocą funkcji sortowania Excel. Oto jak:

1. Wybierz zakres, który chcesz posortować. Jeśli w tabeli znajdują się etykiety wierszy, których nie należy przesuwać, należy je pominąć.,
2. przejdź do zakładki Dane >Sortuj i filtruj grupę i kliknij Sortuj:
   
3. w oknie dialogowym Sortuj kliknij Opcje…
4. w małym oknie dialogowym Opcje sortowania wybierz Sortuj od lewej do prawej i kliknij OK, aby wrócić do sortowania
   
5. z listy rozwijanej Sortuj według wybierz numer wiersza, który chcesz uporządkować Alfabetycznie (wiersz 1 w tym przykładzie)., W pozostałych dwóch polach wartości domyślne będą działać dobrze, więc zachowamy je (wartości komórek w sortowaniu na polu, a od A do Z w polu kolejność) i klikamy OK:
   

w wyniku tego pierwszy wiersz w naszej tabeli jest sortowany w porządku alfabetycznym, a reszta danych jest odpowiednio ułożona, zachowując wszystkie korelacje między wpisami:

305e58b605 ” >

problemy z sortowaniem alfabetycznym w programie Excel

funkcje sortowania w programie Excel są niesamowite, ale jeśli pracujesz z niedoskonale ustrukturyzowanymi danymi, rzeczy mogą pójść bardzo źle., Oto dwie wspólne kwestie.

puste lub ukryte kolumny i wiersze

Jeśli w Twoich danych znajdują się puste lub ukryte wiersze i kolumny, A wybierzesz tylko jedną komórkę przed kliknięciem przycisku Sortuj, tylko część danych do pierwszego pustego wiersza i / lub kolumny zostanie posortowana.

łatwą poprawką jest wyeliminowanie spacji i odkrycie wszystkich ukrytych obszarów przed sortowaniem. Albo najpierw wybierz całą tabelę, a następnie Ustaw Alfabetycznie.,

nierozpoznawalne nagłówki kolumn

Jeśli nagłówki kolumn są sformatowane inaczej niż reszta danych, program Excel jest wystarczająco inteligentny, aby je zidentyfikować i wykluczyć z sortowania. Ale jeśli wiersz nagłówka nie ma specjalnego formatowania, nagłówki kolumn będą najprawdopodobniej traktowane jako zwykłe wpisy i wylądują gdzieś w środku posortowanych danych. Aby temu zapobiec, wybierz tylko wiersze danych, a następnie Sortuj.

podczas korzystania z okna dialogowego sortowanie upewnij się, że pole wyboru moje dane mają nagłówki jest zaznaczone.,

jak sortować alfabetycznie w Excelu za pomocą formuł

Microsoft Excel zapewnia wiele funkcji, aby poradzić sobie z wieloma różnymi zadaniami. Wielu, ale nie wszystkich. Jeśli stoisz przed wyzwaniem, dla którego nie ma wbudowanego rozwiązania, są szanse, że można to osiągnąć za pomocą formuły. Dotyczy to również sortowania alfabetycznego. Poniżej znajdziesz kilka przykładów, w których kolejność alfabetyczna może być wykonana tylko z formuł.,

Jak alfabetyzować w programie Excel według nazwiska

ponieważ istnieje kilka typowych sposobów pisania nazw w języku angielskim, czasami możesz znaleźć się w sytuacji, gdy wpisy zaczynają się od imienia, podczas gdy musisz alfabetyzować je według nazwiska:

opcje sortowania programu Excel nie mogą w tym przypadku pomóc, więc Uciekajmy się do formuł.,nsert następujące formuły w dwóch różnych komórkach, a następnie skopiować je w dół kolumn aż do ostatniej komórki z danymi:

w C2, wyodrębnić imię:

=LEFT(A2,SEARCH(" ",A2)-1)

W D2, wyciągnąć nazwisko:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

a następnie, konkatenowane części w odwrotnej kolejności oddzielone przecinkami:

=D2&", "&C2

szczegółowe wyjaśnienie formuł można znaleźć tutaj, na razie skupmy się na wynikach:

ponieważ musimy alfabetyzować nazwy, a nie formuły, konwertować je na wartości., W tym celu wybierz wszystkie komórki formuły (E2: E10)i naciśnij Ctrl + C, aby je skopiować. Kliknij prawym przyciskiem myszy wybrane komórki, kliknij wartości w opcji wklej i naciśnij klawisz Enter:

dobrze, jesteś prawie na miejscu!,y nazwisko:

w przypadku konieczności powrotu do oryginalnego formatu imienia nazwisko, istnieje trochę więcej pracy do zrobienia:

podziel nazwy na dwie części ponownie za pomocą poniższych formuł (gdzie E2 jest nazwą oddzieloną przecinkami):

uzyskaj imię:

=RIGHT(E2, LEN(E2) - SEARCH(" ", E2))

Pobierz nazwisko:

=LEFT(E2, SEARCH(" ", E2) - 2)

i połącz obie części:

=G2&" "&H2

wykonaj konwersję formuł do wartości jeszcze raz, i jesteś gotowy!,

proces może wyglądać nieco skomplikowany na papierze, ale zaufaj mi, w Twoim Excelu zajmie to tylko kilka minut. W rzeczywistości zajmie to jeszcze mniej czasu niż czytanie tego samouczka, nie mówiąc już o ręcznym alfabetyzowaniu nazw:)

Jak alfabetyzować każdy wiersz indywidualnie w programie Excel

w jednym z poprzednich przykładów omówiliśmy, jak alfabetyzować wiersze w programie Excel za pomocą okna dialogowego sortowanie. W tym przykładzie mieliśmy do czynienia ze skorelowanym zestawem danych. Ale co, jeśli każdy wiersz zawiera niezależne informacje? Jak układać Alfabetycznie każdy wiersz z osobna?,

Jeśli masz rozsądną liczbę wierszy, możesz sortować je jeden po drugim wykonując te kroki. Jeśli masz setki lub tysiące rzędów, byłoby to ogromną stratą czasu. Formuły mogą robić to samo znacznie szybciej.,

Załóżmy, że masz wiele wierszy danych, które powinny być ułożone alfabetycznie w następujący sposób:

na początek skopiuj etykiety wierszy do innego arkusza lub innej lokalizacji w tym samym arkuszu, a następnie użyj następującej formuły tablicy, aby umieścić każdy wiersz w porządku alfabetycznym (gdzie B2:D2 jest pierwszym wierszem w tabeli źródłowej):

=INDEX($B2:$D2, MATCH(COLUMNS($B2:B2), COUNTIF($B2:$D2, "<="&$B2:$D2), 0))

pamiętaj, że prawidłowym sposobem wprowadzania formuły tablicy w Excelu jest naciśnięcie CTRL + SHIFT + ENTER.,

Jeśli nie jesteś zbyt zadowolony z formuł tablicy Excel, wykonaj następujące kroki, aby wprowadzić ją poprawnie w arkuszu roboczym:

1. wpisz formułę w pierwszej komórce (w naszym przypadku G2) i naciśnij Ctrl + Shift + Enter. Gdy to zrobisz, Excel zamknie formułę w {nawiasy klamrowe}. Nie próbuj wpisywać klamry ręcznie, To nie zadziała.
2. wybierz komórkę formuły (G2) i przeciągnij uchwyt wypełnienia w prawo, aby skopiować formułę do innych komórek pierwszego rzędu (do komórki I2 w tym przykładzie).,
3. Wybierz wszystkie komórki formuły w pierwszym wierszu (G2: I2)i przeciągnij uchwyt wypełnienia w dół, aby skopiować formułę do innych wierszy.

Jeśli twój zestaw danych ma spacje, zawiń formułę w funkcji IFERROR:

=IFERROR(INDEX($B2:$D2,MATCH(COLUMNS($B2:B2),COUNTIF($B2:$D2,"<="&$B2:$D2),0)), "")

Niestety, nie ma łatwego rozwiązania dla duplikatów. Jeśli znasz jeden, proszę podziel się w komentarzach!,

jak działa ta formuła

powyższa formuła opiera się na klasycznej kombinacji dopasowania indeksu używanej do wykonywania wyszukiwania poziomego w programie Excel. Ale ponieważ potrzebujemy rodzaju „alfabetycznego wyszukiwania”, przebudowaliśmy go w ten sposób:

COLUMNS($B2:B2) dostarcza wartość wyszukiwania. Ze względu na sprytne użycie odniesień bezwzględnych i względnych, zwracana liczba jest zwiększana o 1, gdy idziemy w prawo. Oznacza to, że dla G2 wartość lookup wynosi 1, dla H2 – 2, dla I2 – 3.,

MATCH wyszukuje wartość lookup obliczoną przez COLUMNS() w tablicy lookup zwracanej przez COUNTIF () i zwraca jej względną pozycję. Na przykład dla G2 wartość lookup wynosi 1, która znajduje się na 3 pozycji w tablicy lookup, więc MATCH zwraca 3.

wreszcie, INDEX pobiera rzeczywistą wartość na podstawie jej względnej pozycji w wierszu. Dla G2 pobiera trzecią wartość z zakresu B2: D2, czyli Aria.,

Jak sortować każdą kolumnę Alfabetycznie w programie Excel

Jeśli masz do czynienia z niezależnymi podzbiorami danych zorganizowanymi pionowo w kolumnach, możesz łatwo dostosować powyższą formułę, aby alfabetyzować każdą kolumnę indywidualnie., Wystarczy zastąpić kolumny() wierszami(), dodać kilka współrzędnych kolumn absolutnych i współrzędnych wierszy względnych, a twoja formuła jest gotowa:

=INDEX(A$3:A$5,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$5,"<="&A$3:A$5),0))

pamiętaj, że jest to formuła tablicy, która powinna być uzupełniona Ctrl + Shift + Enter:

Oprócz dostarczania rozwiązań do zadań, które są niemożliwe do wykonania dzięki wbudowanym opcjom sortowania excel, formuły mają jeszcze jedną (choć sporną 🙂 zaletę-sprawiają, że sortowanie jest dynamiczne. Dzięki wbudowanym funkcjom będziesz musiał korzystać z danych za każdym razem, gdy dodawane są nowe wpisy., Dzięki formułom możesz dodawać nowe dane w dowolnym momencie, a posortowane listy będą aktualizowane automatycznie.

Jeśli wolisz, aby nowy układ alfabetyczny stał się statyczny, zastąp formuły ich wynikami za pomocą Wklej specjalnych> wartości.

aby przyjrzeć się bliżej formułom omówionym w tym samouczku, zapraszamy do pobrania naszego arkusza alfabetycznego Excela. Dziękuję za przeczytanie i mam nadzieję, że do zobaczenia na naszym blogu w przyszłym tygodniu!,

• jak sortować karty Alfabetycznie w Excelu
• jak sortować losowo w Excelu
• jak filtrować i sortować według koloru komórki, koloru czcionki i ikony
• jak sortować według wierszy, nazw kolumn i w niestandardowej kolejności