Jak sortować według nazwiska w programie Excel (łatwy przewodnik)

Obejrzyj wideo - jak sortować według nazwiska w programie Excel

Jeśli pracujesz z zestawami danych nazw, sortowanie jest jednym z typowych zadań, które musisz często wykonywać.

Sortowanie danych alfabetycznie na podstawie pełnej nazwy jest dość łatwe, gdzie Excel używa do sortowania pierwszego znaku nazwy.

Ale co jeśli chcesz? sortuj dane według nazwiska w Excelu?

Chociaż nie jest to tak proste, nadal można to zrobić (wiele zależy również od struktury danych nazw).

Bez względu na to, jakiej metody użyjesz, będziesz musiał jakoś wyodrębnić nazwisko z pełnego nazwiska i umieścić je w osobnej kolumnie. Następnie możesz użyć tej kolumny do posortowania danych według nazwisk alfabetycznie.

W tym samouczku Excela pokażę, jak sortować kolumnę z nazwami na podstawie nazwiska.

Więc zacznijmy!

Wyodrębnij i posortuj według nazwiska za pomocą funkcji Znajdź i zamień

Pierwszym krokiem do sortowania według nazwiska jest umieszczenie nazwiska w osobnej kolumnie.

Możesz to zrobić, zastępując wszystko przed nazwiskiem spacją, aby pozostało tylko nazwisko.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz posortować te dane alfabetycznie, używając nazwiska.

Poniżej znajdują się kroki, aby posortować według nazwiska:

1. Wybierz zestaw danych zawierający nagłówek (w tym przykładzie będzie to A1:A10)
2. Skopiuj go w sąsiedniej kolumnie (jeśli sąsiednia kolumna nie jest pusta, wstaw nową kolumnę, a następnie skopiuj te nazwy)
3. Zmień nazwę skopiowanego nagłówka kolumny. W tym przykładzie nazwę to „Nazwisko”
4. Zaznacz wszystkie skopiowane nazwy (nie zaznaczaj nagłówka)
5. Przytrzymaj klawisz Control, a następnie naciśnij klawisz H. Spowoduje to otwarcie okna dialogowego Znajdź i zamień.
6. W polu Znajdź wpisz * (symbol gwiazdki, a po nim znak spacji)
7. Pozostaw pole Zamień na puste
8. Kliknij Zamień wszystko. Spowoduje to natychmiastowe zastąpienie wszystkich imion i pozostanie tylko z nazwiskami.

Powyższe kroki zachowałyby nazwisko i usunęłyby wszystko przed nim. Działa to dobrze, nawet jeśli masz drugie imiona lub prefiksy (takie jak Pan lub Pani).

Gdy masz już nazwiska w sąsiedniej kolumnie, możesz łatwo posortować zestaw danych (w tym pełne nazwiska) alfabetycznie na podstawie nazwiska.

Poniżej znajdują się kroki, aby posortować według nazwiska:

1. Wybierz cały zestaw danych z nagłówkami (w tym pełne imiona i wyodrębnione nazwiska). Możesz także dołączyć inne kolumny, które chcesz posortować wraz z nazwami
2. Kliknij kartę Dane
3. Kliknij Sortuj
4. W oknie dialogowym Sortuj upewnij się, że wybrana jest opcja „Moje dane mają nagłówki”.
5. W opcji „Sortuj według” wybierz nazwę kolumny, która ma tylko nazwisko
6. W „Sortuj według” wybierz „Wartości komórek”
7. W opcji Zamówienie wybierz „A do Z”
8. Kliknij OK

Powyższe kroki posortują cały wybrany zestaw danych na podstawie nazwiska.

Po zakończeniu możesz usunąć kolumnę, która ma nazwisko.

Pro Porada: W dowolnym momencie, jeśli uważasz, że możesz potrzebować oryginalnych danych z powrotem, musisz mieć sposób na cofnięcie sortowania tego zestawu danych. Aby to zrobić, w sąsiedniej kolumnie (lewej lub prawej) umieść numery seryjne przed sortowaniem. Teraz, jeśli potrzebujesz z powrotem oryginalnych danych, otrzymasz je, sortując według liczb.

Wyodrębnij i ułóż alfabetycznie według nazwiska za pomocą wzoru

Chociaż metoda pokazana powyżej (przy użyciu funkcji Znajdź i zamień) jest tym, co wolę pobierać wszystkie nazwiska i na ich podstawie sortować, jednym z ograniczeń jest to, że dane wynikowe są statyczne.

Oznacza to, że jeśli dodam więcej nazwisk do mojej listy, będę musiał powtórzyć ten sam proces, aby uzyskać nazwiska.

Jeśli jest to coś, czego nie chcesz, możesz użyć metody formuły, aby posortować dane według nazwisk.

Załóżmy, że masz zestaw danych, jak pokazano poniżej.

Poniżej znajduje się formuła, która wyodrębni nazwisko z pełnego imienia i nazwiska:

=PRAWO(A2,DŁ(A2)-ZNAJDŹ("",A2))

Powyższa formuła opiera się na wzorze z pełną nazwą (w tym przykładzie zawiera tylko imię i nazwisko). Wzorzec jest taki, że między imieniem a nazwiskiem będzie spacja.

Funkcja ZNAJDŹ służy do uzyskania pozycji znaku spacji. Ta wartość jest następnie odejmowana od całkowitej długości nazwiska, aby uzyskać całkowitą liczbę znaków w nazwisku.

Ta wartość jest następnie używana w funkcji PRAWO, aby uzyskać nazwisko.

Gdy masz już kolumnę z nazwiskiem, możesz sortować te dane (jest to szczegółowo omówione w pierwszej metodzie).

Powyższa formuła zadziała, gdy masz tylko imię i nazwisko.

Ale co, jeśli masz też drugie imię. A może przed imieniem znajduje się zwrot grzecznościowy (np. Pan lub Pani)

W takim przypadku należy skorzystać z poniższego wzoru:

=PRAWY(A2,DŁ(A2)-ZNAJDŹ("@",ZAMIANA(A2,"","@",DŁ(A2)-DŁ(ZAMIANA(A2,"","")))))

Powyższa formuła wyszukuje pozycję ostatniego znaku spacji, a następnie wykorzystuje ją do wyodrębnienia nazwiska.

Zalecam stosowanie drugiej formuły we wszystkich przypadkach, jest ona bardziej niezawodna i może obsługiwać wszystkie przypadki (o ile nazwisko znajduje się na końcu imienia).

Uwaga: Te dwie formuły opierają się na warunku, że między każdym elementem nazwy jest tylko jeden znak spacji. W przypadku występowania podwójnych spacji lub spacji wiodących/końcowych ta formuła da nieprawidłowe wyniki. W takim przypadku najlepiej użyć funkcji TRIM, aby najpierw pozbyć się wszelkich spacji wiodących, końcowych i podwójnych, a następnie skorzystać z powyższej formuły.

Chociaż może się to wydawać skomplikowaną metodą, zaletą korzystania z formuły jest to, że sprawia, że ​​wyniki są dynamiczne. Jeśli dodasz więcej nazwisk do swojej listy, wystarczy, że skopiujesz formułę, a otrzymasz nazwisko.

Używanie tekstu do kolumn

Tekst do kolumn to znowu prosty i łatwy sposób dzielenia komórek w programie Excel.

Możesz określić ogranicznik (na przykład przecinek lub spację) i użyć go do podzielenia zawartości komórki. Po umieszczeniu podzielonych elementów w osobnych kolumnach możesz użyć kolumny z nazwiskiem do ułożenia danych w kolejności alfabetycznej.

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Poniżej znajdują się kroki, aby użyć tekstu do kolumny do sortowania według nazwiska:

1. Wybierz kolumnę, która ma nazwę (bez nagłówka)
2. Kliknij kartę Dane
3. W grupie „Narzędzia danych” kliknij opcję Tekst do kolumn. Spowoduje to otwarcie kreatora tekstu do kolumn
4. W kroku 1 kreatora „Konwertuj tekst na kolumny” wybierz „Rozdzielony” i kliknij Dalej
5. W kroku 2 wybierz „Spacja” jako ogranicznik (i odznacz cokolwiek innego, jeśli wybrano), a następnie kliknij przycisk Dalej.
6. W kroku 3 wybierz kolumnę imienia w podglądzie danych, a następnie wybierz opcję „Nie importuj kolumn (pomiń)”. Gwarantuje to, że imię nie jest częścią wyniku i otrzymujesz tylko nazwisko.
7. Również w kroku 3 zmień komórkę docelową na tę, która sąsiaduje z oryginalnymi danymi. Zapewni to, że otrzymasz nazwisko osobno, a oryginalne dane dotyczące nazwisk są nienaruszone.
8. Kliknij Zakończ

Po uzyskaniu wyniku możesz sortować według nazwiska.

Możesz także Tekst do kolumn, aby oddzielić imię i nazwisko, gdy masz przecinek jako separator.

Korzystanie z wypełniania Flash

Innym szybkim i szybkim sposobem na uzyskanie nazwisk jest użycie funkcji Flash Fill.

Flash Fill został wprowadzony w programie Excel 2013 i pomaga manipulować danymi poprzez identyfikowanie wzorców. Aby to zadziałało, musisz kilka razy pokazać oczekiwany wynik Flash Fill.

Gdy zidentyfikuje wzór, szybko wykona resztę pracy za Ciebie.

Załóżmy, że masz poniższy zestaw danych nazw.

Poniżej znajdują się kroki, aby użyć funkcji Flash Fill, aby uzyskać nazwisko, a następnie posortować za jego pomocą:

1. W komórce B2 wpisz tekst „Maury”. To jest wynik, którego oczekujesz w komórce.
2. Przejdź do następnej komórki i wprowadź nazwisko w sąsiedniej komórce (w tym przykładzie Elliot).
3. Wybierz obie komórki
4. Ustaw kursor nad prawą dolną częścią zaznaczenia. Zauważysz, że kursor zmienia się w ikonę plusa.
5. Kliknij go dwukrotnie (lub kliknij i przeciągnij w dół). To da ci pewien wynik w komórkach (prawdopodobnie nie będzie to pożądany wynik)
6. Kliknij ikonę Opcje autouzupełniania.
7. Kliknij Wypełnienie Flash

To da ci wynik, który prawdopodobnie będzie nazwiskami we wszystkich komórkach.

Mówię prawdopodobnie, ponieważ Flash Fill może nie działać w niektórych przypadkach. Ponieważ zależy to od zidentyfikowania wzorca, może nie być w stanie tego zrobić zawsze. Czasami wzór, który odszyfrowuje, może nie być właściwy.

W takich przypadkach należy wpisać oczekiwany wynik w jednej lub dwóch kolejnych komórkach, a następnie wykonać kroki 4-7.

Gdy masz już wszystkie nazwiska w kolumnie, możesz sortować dane na podstawie tych nazwisk.

Oto cztery różne sposoby sortowania danych według nazwiska. Najlepszą metodą byłoby użycie techniki Znajdź i zamień, ale jeśli chcesz, aby wyniki były dynamiczne, najlepszym rozwiązaniem jest metoda formuły.

Mam nadzieję, że ten samouczek okazał się przydatny.