Obejrzyj wideo – jak sortować według nazwiska w Excelu
Jeśli pracujesz z nazwami zestawów danych, sortowanie jest jednym z typowych zadań, które musisz często wykonywać.
dość łatwo jest sortować dane Alfabetycznie na podstawie pełnej nazwy, gdzie Excel używa pierwszego znaku nazwy do sortowania.
ale co jeśli chcesz sortować dane według nazwiska w Excelu?,
chociaż nie jest to tak proste, to jednak można to zrobić (wiele zależy również od tego, w jaki sposób dane nazw są strukturyzowane).
bez względu na to, jakiej metody użyjesz, będziesz musiał w jakiś sposób wyodrębnić nazwisko z pełnej nazwy i umieścić je w osobnej kolumnie. Możesz użyć tej kolumny do sortowania danych według nazwiska Alfabetycznie.
w tym samouczku Excela pokażę Ci, jak sortować kolumnę z nazwami na podstawie nazwiska.
więc zaczynajmy!,
Ten samouczek obejmuje:
Wyodrębnij I posortuj według nazwiska za pomocą znajdź i zamień
pierwszym krokiem do sortowania według nazwiska jest uzyskanie nazwiska w oddzielnej kolumnie.
możesz to zrobić, zastępując wszystko przed nazwiskiem pustym, aby pozostało tylko nazwisko.
Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz posortować te dane Alfabetycznie przy użyciu nazwiska.,
poniżej znajdują się kroki sortowania według nazwiska:
- Wybierz zbiór danych zawierający nagłówek (w tym przykładzie będzie to A1:A10)
- skopiuj go do sąsiedniej kolumny (jeśli sąsiadująca kolumna nie jest pusta, włóż nową kolumnę, a następnie skopiuj te nazwy)
- zmień nazwę skopiowanego nagłówka kolumny. W tym przykładzie, I will name is 'nazwisko'
- Wybierz wszystkie skopiowane nazwy (nie zaznaczaj nagłówka)
- przytrzymaj klawisz Sterujący, a następnie naciśnij klawisz H. Spowoduje to otwarcie okna dialogowego Znajdź i zamień.,
- w polu Znajdź, wpisz * (symbol gwiazdki, po którym następuje znak spacji)
- pozostaw Zastąp pole puste
- kliknij Zastąp wszystko. To natychmiast zastąpi wszystkie imię i pozostanie tylko nazwisko.
powyższe kroki zachowają nazwisko i usuną wszystko przed nim. Działa to dobrze, nawet jeśli masz drugie imiona lub przedrostki(takie jak Mr. lub Ms).,
Po umieszczeniu nazwisk w sąsiedniej kolumnie można łatwo posortować zbiór danych (łącznie z pełnymi nazwami) Alfabetycznie na podstawie nazwiska.
poniżej znajdują się kroki sortowania według nazwiska:
- wybierz cały zestaw danych z nagłówkami (w tym pełne nazwy i wyodrębnione nazwiska)., Możesz również dołączyć inne kolumny, które chcesz sortować wraz z nazwami
- kliknij kartę Dane
- kliknij Sortuj
- w oknie dialogowym Sortuj upewnij się, że wybrano opcję „Moje dane mają nagłówki”.,
- w opcji”Sortuj według”wybierz nazwę kolumny, która ma tylko nazwisko
- w opcji”Sortuj na”Wybierz” wartości komórek „
- w opcji zamówienia wybierz „A do Z”
- kliknij OK
powyższe kroki posortują cały wybrany zestaw danych na podstawie nazwiska.
Po zakończeniu możesz usunąć kolumnę z nazwiskiem.,
wyodrębnij i Alfabetyzuj według nazwiska za pomocą formuły
podczas gdy metoda pokazana powyżej (za pomocą znajdź i Zamień) jest tym, co wolę uzyskać wszystkie nazwiska i sortować na jego podstawie, 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ł ponownie wykonać ten sam proces, aby uzyskać nazwiska.
Jeśli jest to coś, czego nie chcesz, możesz użyć metody formula do sortowania danych 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łnej nazwy:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
powyższa formuła opiera się na wzorze z pełną nazwą (który zawiera tylko imię i nazwisko w tym przykładzie)., Wzorzec polega na tym, że między imieniem a nazwiskiem będzie znak spacji.
funkcja FIND służy do uzyskania pozycji znaku spacji. Wartość ta jest następnie odejmowana od całkowitej długości nazwy, aby uzyskać całkowitą liczbę znaków w nazwisku.
wartość ta jest następnie używana w odpowiedniej funkcji, aby uzyskać nazwisko.
gdy już masz kolumnę nazwisko, możesz posortować te dane (jest to szczegółowo opisane w pierwszej metodzie).
powyższa formuła będzie działać, gdy masz tylko imię i nazwisko.
ale co jeśli masz też drugie imię., Mr lub MS.)
W takim przypadku musisz użyć poniższej formuły:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
powyższa formuła znajduje pozycję ostatniego znaku spacji, a następnie używa jej do wyodrębnienia nazwiska.
polecam użycie drugiej formuły we wszystkich przypadkach, i jest bardziej niezawodny i może obsługiwać wszystkie przypadki (o ile nazwisko jest na końcu nazwy).
Uwaga: Te dwie formuły opierają się na warunku, że między każdym elementem nazwy jest tylko jeden znak spacji., W przypadku, gdy istnieją podwójne spacje lub spacje wiodące/końcowe, ta formuła da nieprawidłowe wyniki. W takim przypadku najlepiej jest użyć funkcji TRIM, aby najpierw pozbyć się wszelkich spacji prowadzących, końcowych i podwójnych, a następnie użyć powyższej formuły.
chociaż może to wydawać się skomplikowaną metodą, zaletą stosowania formuły jest to, że sprawia, że wyniki są dynamiczne. Jeśli dodasz więcej nazwisk do listy, wszystko, co musisz zrobić, to skopiować formułę, a ona poda ci 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 (taki jak przecinek lub spacja) i użyć go do podzielenia zawartości komórki. Po rozdzieleniu elementów w osobnych kolumnach można użyć kolumny, która ma Nazwisko, aby uporządkować dane Alfabetycznie.
Załóżmy, że masz zestaw danych, jak pokazano poniżej:
poniżej znajdują się kroki, aby użyć tekstu do kolumny, aby posortować według nazwy:
- wybierz kolumnę, która ma nazwę (z wyłączeniem nagłówka)
- kliknij kartę Dane
- w grupie „Narzędzia danych” kliknij opcję Tekst do kolumn., Spowoduje to otwarcie Kreatora tekstu na kolumny
- w Kroku 1 „Konwertuj tekst na kolumny kreatora”, wybierz „rozdzielane” i kliknij Dalej
- w Kroku 2, Wybierz „Spacja” jako ogranicznik (i odznacz cokolwiek innego, jeśli zaznaczone), a następnie kliknij przycisk Dalej.
- w kroku 3 wybierz kolumnę imię w Podglądzie danych, a następnie wybierz opcję ” Nie Importuj kolumn (pomiń)”. Gwarantuje to, że imię nie jest częścią wyniku, a otrzymasz tylko nazwisko.,
- również w Kroku 3 Zmień komórkę docelową na tę, która sąsiaduje z oryginalnymi danymi. Zapewni to, że nazwisko zostanie podane osobno, a oryginalne dane imion będą nienaruszone.
- kliknij Zakończ
Po uzyskaniu wyniku możesz sortować według nazwiska.
Możesz również pisać do kolumn, aby oddzielić imię i nazwisko, gdy masz przecinek jako separator.,
Korzystanie z Flash Fill
innym szybkim i szybkim sposobem uzyskania nazwisk jest użycie funkcji Flash Fill.
Flash Fill został wprowadzony w programie Excel 2013 i pomaga manipulować danymi poprzez identyfikację wzorców. Aby to zadziałało, musisz pokazać Flash wypełnij wynik, którego oczekujesz kilka razy.
Po zidentyfikowaniu wzorca szybko wykona resztę pracy za Ciebie.
Załóżmy, że masz poniższy zbiór danych nazw.,
poniżej znajdują się kroki, aby użyć Flash Fill, aby uzyskać nazwisko, a następnie posortować je za pomocą:
- w komórce B2 wprowadź tekst 'Maury'. Jest to wynik, którego oczekujesz w komórce.
- przejdź do następnej komórki i wprowadź nazwisko nazwy w sąsiedniej komórce(w tym przykładzie).
- zaznacz obie komórki
- najedź kursorem na prawą dolną część zaznaczenia. Zauważysz, że kursor zmienia się na ikonę plusa.
- kliknij go dwukrotnie (lub kliknij i przeciągnij w dół)., Daje to pewien wynik w komórkach (prawdopodobnie nie będzie to wynik, który chcesz)
- kliknij ikonę opcji autouzupełniania.
- kliknij wypełnienie Flash
To da ci wynik, który prawdopodobnie będzie nazwiskami we wszystkich komórkach.
mówię prawdopodobne, ponieważ Flash Fill może nie działać w niektórych przypadkach. Ponieważ zależy to od identyfikacji wzorca, może nie być w stanie tego zrobić zawsze. A czasami wzór, który rozszyfruje, może nie być właściwy.,
w takich przypadkach należy wprowadzić oczekiwany wynik w jednej lub dwóch kolejnych komórkach, a następnie wykonać kroki 4-7.
gdy masz wszystkie nazwiska w kolumnie, możesz sortować dane na podstawie tych nazwisk.
są więc cztery różne sposoby sortowania danych według nazwiska. Najlepszą metodą byłoby użycie techniki znajdź i zastąp, ale jeśli chcesz, aby Twoje wyniki były dynamiczne, metoda formuły jest do zrobienia.
Mam nadzieję, że ten tutorial był dla ciebie przydatny.,
Możesz również polubić następujące samouczki programu Excel:
- jak sortować według koloru w Excelu
- jak sortować arkusze robocze w Excelu
- jak sortować dane w Excelu za pomocą VBA
- automatycznie sortować dane w porządku alfabetycznym za pomocą formuły
- jak sortować dane na wielu poziomach w Excelu