Jak podzielić kolumnę danych programu Excel na dwie kolumny. Jak analizować dane w programie Excel.
Obejrzyj wideo
- Pierwsza metoda Billa wykorzystująca tekst na kolumny (znajduje się na karcie Dane).
- W kroku 1 wybierz rozdzielany. W kroku 2 wybierz spację. Pomiń krok 3, klikając Zakończ.
- Tekst zostanie podzielony w każdym miejscu, więc wszystko, co zawiera trzy słowa, zostanie umieszczone w 3 komórkach. Połącz je z powrotem za pomocą
=TEXTJOIN(" ",True,B2:E2)lub - z
=B2&" "&C2&" "&D2 - Pierwsza metoda Mike'a używa dodatku Power Query. Dodatek Power Query można pobrać i przekształcić w 2016 r. Lub bezpłatnie pobrać w 2010 lub 2013 r.
- Najpierw przekonwertuj dane na tabelę za pomocą Ctrl + T. Następnie w dodatku Power Query z tabeli. Podzielona kolumna według separatora. Wybierz spację, a następnie skrajny lewy ogranicznik.
- Możesz zmienić nazwę kolumny, klikając dwukrotnie!
- Zamknij i wczytaj do… i wybierz nowe miejsce w arkuszu.
- Drugą metodą Billa jest użycie Flash Fill. Wpisz nowe nagłówki w A, B i C. Funkcja Flash Fill nie zadziała, jeśli nie masz nagłówków! Wpisz wzór dla pierwszych dwóch rzędów.
- Przejdź do pierwszej pustej komórki w B i naciśnij Ctrl + E. Powtórz dla kolumny C.
- Drugą metodą Mike'a jest użycie tych formuł:
- W pierwszej części użyj
=LEFT(A2,SEARCH(" ",A2)-1) - W drugiej części użyj
=SUBSTITUTE(A2,B2&" ","")
Transkrypcja wideo
(Muzyka)
Bill Jelen: Hej, witaj ponownie, czas na kolejny podcast Dueling Excel. Jestem Bill Jelen z. Dołączy do mnie Mike Girvin z Excel Is Fun. To jest nasze
Odcinek 182: Podziel dane z jednej komórki, aby pojawiły się w dwóch komórkach.
W porządku, dzisiejsze pytanie przesłał Tom. Czy istnieje sposób na łatwe podzielenie danych w jednej komórce, aby dane pojawiały się w dwóch komórkach? Na przykład 123 Main Street chce 123 w jednej komórce, a Main Street w innej; lub Howard i Howard, a następnie End. Spędziłem niezliczone godziny na oddzielaniu tego rodzaju danych. Byłbym wdzięczny za kontakt z Twoją firmą, skoro istnieje wiele, wiele różnych sposobów, aby to zrobić.
Pierwszą rzeczą, którą zamierzam zrobić, jest zaznaczenie wszystkich danych, Ctrl + Shift + strzałka w dół, a następnie dane, tekst na kolumny. Tekst na kolumny w kroku 1, dane są rozdzielane. Jest oddzielony spacją, a następnie kliknij przycisk Zakończ. Problem z tą metodą polega na tym, że jeśli masz 123 Main Street, skończy się ona w 3 komórkach zamiast 2. Och, Power Query bardzo by to ułatwiło, ale oto jesteśmy. W porządku, więc zamierzam wyjść daleko na prawo od Danych, gdzie wiem, że poza tym, gdzie wszystko jest zbudowane. Jeśli korzystam z Office 365, zamierzam użyć TEXTJOIN. TEXTJOIN, ta niesamowita rzecz, separator to spacja. Zignoruj puste komórki Prawda, a następnie komórki, które chcę połączyć w ten sposób, i po prostu skopiuj je wszystkie, Ctrl + V. Skopiuję Ctrl + C a potem Home, Paste,Wklej jako wartości iw tym momencie mogę usunąć te 3 dodatkowe kolumny.
Ahh, ale nikt nie ma Office 365, prawda? Tak więc, jeśli nie masz Office 365, musisz zrobić = to i „” i tamto, a jeśli było więcej „” i tamtych, a jeśli było więcej, kontynuuj. W tym przypadku jest to bezcelowe, ponieważ w D nie ma nic więcej, ale masz pomysł. Ctrl + C, skopiuj go do ostatniego wiersza danych, Ctrl + V, a następnie Ctrl + C, Alt + ESV, aby utworzyć te wartości B. I oto jesteśmy, w porządku. Mike, zobaczmy, co masz.
Mike Girvin: Dzięki. Hej, lobbowałeś mnie tutaj jako łatwy, ponieważ wspomniałeś już o Get & Transform Power Query, stary tekst na kolumny pozwala tylko na wypowiedzenie spacji przy każdym znaku, prawda? Cóż, jeśli korzystamy z dodatku Power Query, możemy użyć tego separatora i powiedzieć „Hej, po prostu podziel przy pierwszym wystąpieniu”.
Teraz, aby uzyskać te dane w edytorze zapytań, musimy przekonwertować je na tabelę programu Excel. Więc idę w górę do wstawiania, tabeli lub używam Ctrl + T. Moja tabela ma nagłówki, przycisk OK jest podświetlony, więc mogę kliknąć myszą lub po prostu nacisnąć Enter. Teraz chcę nazwać tę tabelę, więc przejdę tutaj, OriginalData i Enter. To jest tabela programu Excel, możemy przejść do danych i tam jest z tabeli. Spowoduje to przeniesienie go z programu Excel do edytora. Kolumna jest zaznaczona: Strona główna Karta wstążki, możemy powiedzieć Podziel kolumnę według separatora lub podejdź tutaj i kliknij prawym przyciskiem myszy Podziel kolumnę według separatora. Z listy rozwijanej możemy powiedzieć, hej, użyj spacji i spójrz na ten ogranicznik znajdujący się najbardziej po lewej stronie. Kiedy kliknę OK, BUM! Tu jest. Teraz mam zamiar nazwać obie te kolumny: dwukrotnie kliknij Część 1 Enter, kliknij dwukrotnie Część 2 i Enter. Teraz,Mogę podejść tutaj lub Zamknij i załaduj, Zamknij i załaduj do i mogę wybrać, gdzie to umieścić. Zdecydowanie chcę zrzucić to jako tabelę, nowy arkusz roboczy, istniejący arkusz. Podświetl to, kliknij przycisk zwijania. Powiem D1, kliknij OK, a następnie kliknij Załaduj. I gotowe, nasze wyjście Power Query.
W porządku, wróć do.
Bill Jelen: Och, Mike, Power Query jest niesamowity! Tak, to świetny sposób. Oto kolejny, który może działać, jeśli masz program Excel 2013 lub nowszy.
To, co zamierzamy zrobić, to przyjść tutaj i powiedzieć pierwszą część, a potem drugą część. Upewnij się, że umieścisz te nagłówki, że jeśli nie umieścisz tych nagłówków, nie muszą to być te, ale muszą mieć nagłówki, inaczej to nie zadziała. Ustawię 123 i Main Street, a potem Howard i End, w ten sposób. Teraz, gdy mamy tam ładny mały wzór, przejdź do zakładki Data i Flash Fill, czyli Ctrl + E, naciśnij Ctrl + E tam, a następnie naciśnij Ctrl + E tam. Piękne jest to, że nie musimy łączyć danych razem, jak w moim przykładzie. W porządku, Mike, wracając do ciebie.
Mike Girvin: Ding-ding-ding. To bez wątpienia zwycięzca. Błyskawiczne wypełnianie to sposób na osiągnięcie tego celu. Zauważ, że nie musieliśmy konwertować go na tabelę ani otwierać żadnego okna dialogowego; po prostu wpisałem kilka przykładów, a następnie Ctrl + E.
W porządku, moglibyśmy to zrobić za pomocą formuł, mimo że Flash Fill prawdopodobnie byłby szybszy. Spójrz na to, wzorzec, podobnie jak ta komórka listy, użyty w Flash Fill, to wszystko przed pierwszą spacją, a potem wszystko później. Więc hej, zamierzam użyć funkcji LEFT, tekst jest tutaj i ile znaków od lewej? Cóż, zamierzam wyszukać tę przestrzeń - 1 2 3 4 za pomocą funkcji SZUKAJ, Znajdź tekst, spację i „” w tym. Teraz zauważ, że wyszukiwarka liczy na palcach 1 2 3 4 i to dostanie się do tego miejsca, które chcę, do tego miejsca, więc I -1) Ctrl + Enter, kliknij dwukrotnie i wyślij go w dół. Więc to zawsze dostaje wszystko przed pierwszą spacją.
Zauważ, że mamy już tekst, więc mogę użyć funkcji SUBSTITUTE. Tekst, który mam zamiar przejrzeć, to Pełne dane, przecinek, Stary tekst, którego chcę szukać, a następnie ZASTĘP. Nic nie jest prawie 1 2 3. Właściwie chcę dodać spację, którą właśnie usunąłem w poprzedniej formule, z powrotem. Teraz będzie szukać 1 2 3, spacji, a następnie Howarda, spacji i tak dalej, przecinka, a następnie nowy tekst, który chcę zastąpić. Cóż, aby powiedzieć SUBSTITUTE, że chcesz go zastąpić niczym, mówisz „” bez spacji pomiędzy, Close Nawias i to zadziała. Ctrl + Enter, kliknij dwukrotnie i wyślij. W porządku? Po prostu wrzuć to z powrotem do.
Bill Jelen: Hej! W porządku, Mike, obie twoje metody były niesamowite. Zróbmy tutaj krótkie podsumowanie. Moja pierwsza metoda z użyciem tekstu na kolumny: Krok 1, wybierz opcję Rozdzielany; Krok 2, wybierz spację, a następnie kliknij Zakończ. Problem polega na tym, że jeśli masz wiele spacji, skończy się to w wielu komórkach. Muszę je złożyć z powrotem. Office 365 TEXTJOIN lub stara wersja B2 & „” i C2 i tak dalej.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.
W porządku, chcę podziękować wszystkim za zatrzymanie się. Do zobaczenia następnym razem w kolejnym podcastie Dueling Excel od i Excel to zabawa.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Duel182.xlsm
