W programie Excel bardzo łatwo jest podzielić pierwsze, drugie i nazwisko, które pojawiają się w tej samej kolumnie, na osobne kolumny, używając różnych sposobów.
Załóżmy, że otrzymałeś listę kontaktów ze wszystkimi imionami i nazwiskami wymienionymi w pełnych imionach w jednej kolumnie i prawdopodobnie będziesz musiał oddzielić imię, drugie imię i nazwisko oraz podzielić je na osobne kolumny. Istnieje kilka różnych sposobów rozdzielania nazw — za pomocą funkcji Tekst do kolumn, wypełnienia Flash i formuł.
W programie Excel naprawdę łatwo jest podzielić nazwy z jednej kolumny na dwie lub więcej kolumn. W tym artykule pokażemy, jak podzielić nazwy na różne kolumny w programie Excel, używając różnych sposobów.
Jak dzielić nazwy w programie Excel
Istnieją trzy proste sposoby dzielenia nazw w programie Excel. W zależności od struktury danych i tego, czy nazwy podziału mają być statyczne, czy dynamiczne, wybierz jedną z następujących metod:
- Podziel nazwy za pomocą funkcji Tekst na kolumny
- Oddziel nazwy za pomocą formuł
- Oddziel nazwy za pomocą funkcji Flash Fill
Oddziel nazwy za pomocą kreatora tekstu do kolumny
Kreator tekstu na kolumnę to jeden z najczęstszych sposobów oddzielania imion i nazwisk w programie Excel oraz drugich imion. Upewnij się, że obok nazw, które zamierzasz podzielić, znajdują się puste kolumny, ponieważ pełne nazwy zostaną podzielone na osobne kolumny.
Na przykład masz poniższy zestaw danych z pełnymi nazwiskami i chcesz podzielić/oddzielić imię i nazwisko oraz przechowywać je w oddzielnych komórkach.
Najpierw zaznacz kolumnę pełnych nazw, które chcesz oddzielić. Następnie przejdź do zakładki „Dane” i kliknij opcję „Tekst do kolumn” w sekcji „Narzędzia danych”.
Otworzy się Kreator konwersji tekstu na kolumny. W pierwszym kroku kreatora wybierz opcję „Rozdzielone” i kliknij „Dalej”.
W kroku 2 z 3 Kreatora konwersji tekstu na kolumny wybierz separator oddzielający dane, usuń wszelkie inne znaczniki wyboru i kliknij „Dalej”. W naszym przypadku spacja oddziela imię i nazwisko, więc wybieramy ten ogranicznik.
Sekcja Podgląd danych poniżej w oknie pokazuje, w jaki sposób są analizowane Twoje imiona i nazwiska.
W kroku 3 z 3 wybierasz format danych i miejsce docelowe, a następnie klikasz „Zakończ”.
Zwykle domyślny „Ogólny” będzie działał dobrze dla większości typów danych. W polu „Destination” określ lokalizację, w której chcesz wyświetlić wynik. Musisz podać adres pierwszej komórki w kolumnie, w której chcesz uzyskać wyniki (w naszym przypadku B2).
Pamiętaj, że jeśli nie określisz komórki docelowej, kreator zastąpi oryginalne dane, więc pamiętaj, aby wybrać pustą kolumnę.
Po kliknięciu przycisku „Zakończ” natychmiast rozdzieli pełne imiona i nazwiska na dwie oddzielne kolumny (Imię i Nazwisko).
Wykonaj te same kroki, jeśli masz imię, drugie imię i nazwisko, a Twoje imiona zostaną podzielone na trzy kolumny zamiast na dwie.
Notatka: Wynik tej metody jest statyczny. Oznacza to, że jeśli zmienisz oryginalną nazwę, musisz zrobić to od nowa, aby podzielić nazwy.
Podziel nazwy oddzielone przecinkami
Jeśli imiona i nazwiska są oddzielone przecinkami, wykonaj poniższe czynności, aby usunąć przecinek i podzielić imię i nazwisko.
W poniższym przykładzie imiona są sformatowane w formacie odwrotnym (nazwisko, imię), gdzie najpierw pojawia się nazwisko, po nim przecinek, a następnie imię.
Wybierz nazwy i przejdź do Dane -> Tekst do kolumny. W kroku 1 wybierz „Ogranicznik” i kliknij „Dalej”. W kroku 2 w sekcji Ograniczniki zaznacz „Przecinek” (,) jako ogranicznik, ponieważ Twoje imiona są oddzielone przecinkami.
W ostatnim kroku wybierasz format danych jako „Ogólny”, określasz miejsce docelowe i klikasz „Zakończ”.
Teraz będziesz mieć imiona w osobnych kolumnach.
Oddzielne nazwy za pomocą formuł
Kreator tekstu do kolumn umożliwia szybkie i łatwe rozdzielanie nazw. Jeśli jednak chcesz edytować oryginalne nazwy i potrzebujesz dynamicznej metody, która będzie aktualizować się automatycznie za każdym razem, gdy zmienisz nazwy, to dzielenie nazw za pomocą formuł jest właściwym wyborem. Do oddzielania nazw można używać funkcji LEWO, PRAWO, ŚRODEK, DŁ. i SZUKAJ lub ZNAJDŹ.
Oddziel imię i nazwisko w programie Excel za pomocą formuł
Zdobądź imię
Załóżmy, że masz poniższy zestaw danych i chcesz oddzielić imię do osobnej komórki. Aby uzyskać imię, musisz połączyć funkcję ZNAJDŹ i LEWO w jedną formułę.
Użyj następującej formuły, aby uzyskać imię:
=LEWO(A2,ZNAJDŹ("",A2)-1)Ta formuła używa funkcji ZNAJDŹ, aby znaleźć pozycję znaku spacji („ ”) między imieniem a nazwiskiem i odejmuje 1, aby wykluczyć samą spację. Numer ten jest następnie podawany do funkcji LEWO, która używa tego numeru pozycji do wyodrębnienia całego tekstu przed nim. Możesz również użyć funkcji SZUKAJ zamiast funkcji ZNAJDŹ.
Po wprowadzeniu formuły w pustej komórce (B2) przeciągnij uchwyt wypełniania w dół do innych komórek, aby zastosować tę formułę, a wszystkie imiona zostały podzielone na kolumnę B, jak pokazano poniżej:
Możesz zagnieździć funkcję SZUKAJ i ZNAJDŹ wewnątrz funkcji LEWO, aby wyodrębnić imię. Główna różnica między tymi dwiema funkcjami polega na tym, że FIND rozróżnia wielkość liter, podczas gdy SEARCH nie uwzględnia wielkości liter.
Jeśli masz przecinek (,) zamiast spacji między imieniem a nazwiskiem, użyj przecinka jako pierwszego argumentu w funkcji ZNAJDŹ:
=LEWO(A2,ZNAJDŹ(",",A2)-1)Zdobądź nazwisko
Teraz, jeśli chcesz wyodrębnić nazwisko, użyj funkcji PRAWO. Poniższa formuła wyodrębni nazwisko z tego samego zbioru danych:
=PRAWO(A2,DŁ(A2)-ZNAJDŹ("",A2))Formuła najpierw znajduje pozycję znaku spacji, odejmuje tę liczbę od całkowitej długości ciągu (która jest zwracana przez funkcję LEN), a następnie ta liczba jest dostarczana do funkcji PRAWY, aby wyodrębnić tyle znaków z końca ciągu (Nazwa).
Oddziel imię, drugie imię i nazwisko w programie Excel za pomocą formuł
Dzielenie nazw zawierających drugie imię wymaga różnych formuł, w zależności od używanego formatu imienia i nazwiska.
Aby uzyskać Imię jeśli masz drugie imię lub inicjał drugiego imienia, użyj tej samej formuły LEFT FIND, którą już znasz.
Zdobądź nazwisko
Powyższa formuła PRAWY ZNAJDŹ działa dobrze, gdy jest tylko imię i nazwisko, nie będzie zbyt przydatna, jeśli oryginalne imiona zawierają drugie imię lub inicjał drugiego imienia. Dzieje się tak, ponieważ nie uwzględniłeś dwóch spacji w nazwie.
Aby uzyskać nazwisko, gdy masz również drugie imię, użyj tej formuły:
=PRAWO(A2,DŁ(A2)-SZUKAJ(" ",A2,SZUKAJ(" ",A2,1)+1))Aby wyodrębnić nazwisko, najpierw określ położenie drugiego znaku spacji za pomocą zagnieżdżonych funkcji SEARCH, dodaj 1 do SEARCH(”, „, A2,1), aby rozpocząć wyodrębnianie od następnego znaku. Następnie odejmij pozycję drugiej spacji od całkowitej długości ciągu i uzyskaj długość nazwiska jako liczbę wynikową. Następnie podaj tę wynikową liczbę funkcji RIGHT, aby wyodrębnić liczbę znaków z końca ciągu.
Zdobądź drugie imię
Funkcja MID używa trzech argumentów, pierwszy argument określa tekst lub adres komórki, drugi określa pozycję początkową, a ostatni argument określa liczbę znaków, aby wyodrębnić drugie imię z tej pozycji.
Składnia:
=MID(tekst, numer_początkowy, liczba_znaków)Aby uzyskać drugie imię, wprowadź tę formułę w pustą komórkę:
=MID(A2,SEARCH(" ",A2)+1,SEARCH("",A2,SEARCH("",A2)+1)-SEARCH("",A2)-1)Zobaczmy, jak działa ta złożona formuła:
Aby wyodrębnić drugie imię lub inicjał drugiego imienia, musisz określić położenie obu spacji w pełnym imieniu. Aby znaleźć pozycję pierwszego znaku spacji, wprowadź to SZUKAJ("",A2) w argumencie „numer_startu” i dodaj 1, aby rozpocząć wyodrębnianie od następnego znaku.
Następnie, aby dowiedzieć się, jaka jest długość drugiego imienia, umieść to SEARCH(" ",A2,SEARCH("",A2)+1)-SEARCH("",A2)-1 funkcja zagnieżdżona w argumencie „liczba_znaków”, która odejmuje pozycję pierwszego odstępu od pozycji drugiego odstępu i odejmuje 1 od wyniku, aby usunąć spację końcową. Ostateczny wynik powie ci, ile znaków należy wyodrębnić.
Teraz funkcja MID z pozycją początkową drugiego imienia i cyfr do wyodrębnienia oddziela drugie imię od pełnego imienia (A2).
Oddziel nazwy w programie Excel za pomocą wypełniania Flash
Flash fill jest najczęściej używany do automatycznego wypełniania danych o określonym wzorze. Może być również używany do dzielenia nazw. Jest dostępny tylko w Excel 2013, 2016, 2019 i 365.
Załóżmy, że masz poniższy zbiór danych i chcesz uzyskać tylko imiona z pełnych imion.
W komórce sąsiadującej z oryginalną nazwą wpisz imię. W takim przypadku wpisz „Steve” w komórce B2.
Następnie zacznij wpisywać imię w drugiej komórce kolumny. Podczas pisania, jeśli program Excel wykryje wzorzec, funkcja Flash Fill automatycznie wyświetli listę imion w innych komórkach (w kolorze szarym).
Gdy zobaczysz listę nazwisk w kolorze szarym i jeśli te imiona są prawidłowe, po prostu naciśnij klawisz „Enter”, a Flash Fill automatycznie wypełni resztę kolumny imionami.
Wykonaj tę samą procedurę, aby oddzielić nazwiska w osobnej kolumnie.
Wynik:
Flash Fill działa, wykrywając wzorzec w tych danych i podążając za tym wzorcem, jednocześnie podając zmodyfikowane dane. Na początku, gdy wpiszesz imię w pierwszej komórce, Flash Fill nie rozpoznaje wzorca. Ale kiedy ponownie zaczniesz wpisywać imię w drugiej komórce, Flash Fill rozpozna wzór i wyświetli sugestię podziału imion. Następnie po prostu naciśnij klawisz „Enter”.
Ogólnie rzecz biorąc, funkcja Flash Fill jest domyślnie włączona. Jeśli to nie działa w Twoim Excelu, po wpisaniu imienia w pierwszej komórce, możesz po prostu wybrać drugą komórkę i kliknąć przycisk „Wypełnij Flash” z grupy Narzędzia danych na karcie „Dane”.
Alternatywnie możesz również nacisnąć „Ctrl” + „E”, aby uzyskać te same wyniki.
Teraz pozostałe komórki zostaną wypełnione imionami.
Czasami możesz nie widzieć sugestii wzorku w kolorze szarym, w takim przypadku możesz użyć uchwytu wypełnienia, aby uzyskać wynik Wypełnianie błyskawiczne.
Najpierw wpisz nazwy ręcznie w dwóch komórkach i zaznacz obie te komórki. Następnie umieść kursor w prawym dolnym rogu zaznaczenia. Zauważysz, że kursor zmienia się z małego zielonego kwadratu (ikona wypełniacza) na ikonę plusa.
Następnie kliknij dwukrotnie ikonę plusa. To wypełni resztę komórek. W tym momencie wyniki są nieprawidłowe, zobaczysz, że oba imiona są powtarzane raz za razem. Następnie w prawym dolnym rogu wynikowych danych zobaczysz małą ikonę automatycznego wypełniania, jak pokazano poniżej. Kliknij ikonę „Automatyczne wypełnianie” i wybierz „Wypełnianie błyskiem”.
To wypełniłoby imiona we wszystkich komórkach:
Usuń drugie imię
Możesz także użyć narzędzia Flash Fill, aby pozbyć się drugiego imienia z pełnego imienia i nazwiska.
Załóżmy na przykład, że masz poniższy zestaw danych i chcesz uzyskać tylko imię i nazwisko bez drugiego imienia lub inicjału drugiego imienia.
Aby uzyskać imiona bez drugiego imienia lub inicjału drugiego imienia, ręcznie wpisz „Lord Stark” w sąsiedniej komórce. Następnie w drugiej sąsiedniej komórce wpisz „Daenerys Targaryen”. Podczas pisania funkcja Flash Fill rozpozna wzór i wyświetli listę imion bez drugich imion (w kolorze szarym).
Jeśli sugestia jest prawidłowa, naciśnij klawisz „Enter”, a Flash Fill automatycznie wypełni pozostałe komórki nazwami bez drugiego imienia.
Jeśli chcesz uzyskać tylko drugie imiona bez imienia i nazwiska, wprowadź drugie imię w pierwszych dwóch komórkach i użyj narzędzia Flash Fill, aby pobrać drugie imiona ze wszystkich pełnych imion w kolumnie.
Ten samouczek pokazuje, jak oddzielać nazwy podczas manipulowania danymi tekstowymi. Metody te mogą być również pomocne podczas pracy z innymi formami danych, takimi jak adresy, nazwy produktów, nazwy marek itp.