Cum să separați o coloană de date Excel în două coloane. Cum se analizează datele în Excel.
Urmăriți videoclipul
- Prima metodă a lui Bill folosind Textul în coloane (găsită în fila Date).
- La pasul 1, alegeți delimitat. În pasul 2, alegeți un spațiu. Săriți pasul 3 făcând clic pe Terminare.
- Textul se va împărți la fiecare spațiu, așa că orice cu trei cuvinte va ajunge în 3 celule. Puneți-le înapoi împreună cu
=TEXTJOIN(" ",True,B2:E2)sau - cu
=B2&" "&C2&" "&D2 - Prima metodă a lui Mike folosește Power Query. Power Query este Get & Transform în 2016 sau o descărcare gratuită pentru 2010 sau 2013.
- Mai întâi, convertiți datele într-un tabel folosind Ctrl + T. Apoi, în Power Query, din Tabel. Coloana divizată, de Delimiter. Selectați Spațiu și apoi în cel mai stânga delimitator.
- Puteți redenumi o coloană făcând dublu clic!
- Închideți și încărcați în … și alegeți un nou loc pe foaia de lucru.
- A doua metodă a lui Bill este utilizarea Flash Fill. Tastați titluri noi în A, B și C. Flash Fill nu va funcționa dacă nu aveți titluri! Tastați un model pentru primele două rânduri.
- Mergeți la prima celulă goală din B și apăsați Ctrl + E. Repetați pentru coloana C.
- A doua metodă a lui Mike este utilizarea acestor formule:
- Pentru prima parte, utilizați
=LEFT(A2,SEARCH(" ",A2)-1) - Pentru a doua parte, utilizați
=SUBSTITUTE(A2,B2&" ","")
Transcriere video
(Muzică)
Bill Jelen: Bună, bine ați revenit, este timpul pentru un alt Dueling Excel Podcast. Sunt Bill Jelen din. Mi se va alătura Mike Girvin de la Excel Is Fun. Este al nostru
Episodul 182: Împărțirea datelor dintr-o celulă pentru a apărea în două celule.
În regulă, întrebarea de astăzi este trimisă de Tom. Există o modalitate de a împărți cu ușurință datele într-o singură celulă pentru ca datele să apară în două celule? De exemplu, 123 Main Street, vrea 123 într-o celulă și Main Street în altă celulă; sau, Howard și Howard și apoi End. Am petrecut nenumărate ore separând acest tip de date. Mi-ar plăcea să aud de la compania dvs., în timp ce există multe, multe modalități diferite de a face acest lucru.
Primul lucru pe care îl voi face este să selectez toate datele, Ctrl + Shift + Săgeată în jos și apoi Date, Text în coloane. Trimiteți text către coloane la pasul 1, datele sunt delimitate. Este delimitat de un spațiu și apoi faceți clic pe Finalizare. Acum, iată problema cu această metodă este că, dacă aveți 123 Main Street, va ajunge în 3 celule în loc de 2 celule. Oh, Power Query ar face acest lucru mult mai ușor, dar iată-ne. În regulă, așa că voi face că voi ieși departe în dreapta datelor, unde știu asta dincolo de locul în care totul este construit. Dacă sunt în Office 365, voi folosi TEXTJOIN. TEXTJOIN, acel lucru minunat, delimitatorul este un spațiu. Ignorați celulele goale Adevărat și apoi celulele pe care vreau să le concatenez împreună, așa că le copiez pe toate, Ctrl + V. Voi copia Ctrl + C și apoi Acasă, Lipire,Inserați ca valori și, în acest moment, pot șterge aceste 3 coloane suplimentare.
Ahh, dar nimeni nu are Office 365, nu? Deci, dacă nu aveți Office 365, trebuie să faceți = chestia asta și „” și aceea, și dacă ar fi mai multe „” și asta și dacă ar fi fost mai multe, continuați. În acest caz este inutil, pentru că nu există nimic în D, dar îți vine ideea. Ctrl + C, copiați-l în ultimul rând de date, Ctrl + V și apoi Ctrl + C, Alt + ESV pentru a face acele valori B. Și iată-ne, bine. Mike hai să vedem ce ai.
Mike Girvin: Mulțumesc. Hei, mi-ai făcut un lucru ușor aici pentru că ai menționat deja interogarea Get & Transform Power, vechiul Text în Coloane îți permite doar să spui un spațiu la fiecare personaj, nu? Ei bine, dacă folosim Power Query, putem folosi acel Delimitator și putem spune „Hei, doar împărțiți-vă la prima apariție”.
Acum, pentru a obține aceste date în Editorul de interogări, trebuie să le convertim într-un tabel Excel. Așa că merg la Insert, Table sau folosesc Ctrl + T. Tabelul meu are anteturi, butonul OK este evidențiat, astfel încât să pot face clic pe el cu mouse-ul sau să apăs pe Enter. Acum vreau să numesc acest tabel, așa că voi veni aici, OriginalData și Enter. Acum, acesta este un tabel Excel, putem ajunge la Date și acolo este From Table. Asta o va aduce din Excel în Editor. Coloana este selectată: fila Panglică principală, putem spune Împărțiți coloana după delimitator sau veniți aici și faceți clic dreapta, Împarte coloana după delimitator. Din meniul derulant, putem spune, hei, utilizați un spațiu și priviți acest lucru la cel mai stânga delimitator. Când dau clic pe OK, BOOM! Iata. Acum, voi numi ambele coloane: faceți dublu clic pe Partea 1 Enter, faceți dublu clic pe Partea 2 și Enter. Acum,Pot veni aici sau Close & Load, Close & Load To și pot alege unde să pun acest lucru. Cu siguranță vreau să-l arunc ca tabel, foaie de lucru nouă, foaie de lucru existentă. Evidențiați acest lucru, faceți clic pe butonul de restrângere. Voi spune D1, faceți clic pe OK, apoi faceți clic pe Încărcare. Și iată-ne, rezultatul nostru de interogare de putere.
În regulă, aruncă-te înapoi.
Bill Jelen: Oh, Mike, Power Query este minunat! Da, acesta este un mod minunat de parcurs. Iată un altul care ar putea funcționa dacă aveți Excel 2013 sau mai nou.
Și ceea ce vom face este să ieșim aici și să spunem prima parte și apoi a doua parte. Asigurați-vă că puneți aceste titluri că, dacă nu le puneți, nu trebuie să fie așa, ci trebuie să aibă titluri sau nu va funcționa. Voi pune 123 și Main Street și apoi vom pune Howard și End, așa. Acum că avem un mic model frumos acolo, ieșiți aici în fila Date și Flash Fill, care este Ctrl + E, apăsați Ctrl + E chiar acolo și apoi apăsați Ctrl + E chiar acolo. Lucru frumos este că nu trebuie să concatenăm datele împreună ca în exemplul meu. În regulă, Mike, înapoi la tine.
Mike Girvin: Ding-ding-ding. Acesta este câștigătorul fără îndoială. Flash Fill este modalitatea de a merge acolo. Observați, nu a trebuit să îl convertim într-un tabel sau să deschidem nicio casetă de dialog; tocmai ați tastat câteva exemple și apoi Ctrl + E.
Bine, bine, am putea face asta cu formule, chiar dacă Flash Fill ar fi probabil mai rapid. Uită-te la acest lucru, modelul la fel ca această celulă listă folosită în Flash Fill este totul înainte de primul spațiu și apoi tot după. Deci hei, voi folosi funcția STÂNGA, Textul este chiar acolo și câte caractere din stânga? Ei bine, voi căuta acel spațiu - 1 2 3 4 folosind funcția SEARCH, Găsiți text, spațiu și „”, în interiorul acestuia. Acum, observați că Căutarea ar conta pe degetele sale 1 2 3 4 și că ar ajunge la acel spațiu pe care îl doresc, acel spațiu așa că I -1) Ctrl + Enter, faceți dublu clic și trimiteți-l în jos. Deci, asta primește întotdeauna totul înainte de primul spațiu.
Acum, observați că avem deja textul aici, astfel încât să pot folosi funcția SUBSTITUTE. Textul pe care urmează să îl privesc este Full Data, virgulă, textul vechi pe care vreau să-l caut și apoi SUBSTITUT. Nimic nu este aproape 1 2 3. Vreau să adaug spațiul pe care tocmai l-am scos în formula anterioară, înapoi. Acum, va căuta 1 2 3, Space și apoi Howard, Space și așa mai departe, virgulă și apoi textul nou pe care vreau să îl substitui. Ei bine, pentru a-i spune SUBSTITUTULUI că doriți să-l înlocuiți cu nimic, spuneți „” fără spațiu între, Închideți paranteze și asta va funcționa. Ctrl + Enter, faceți dublu clic și trimiteți-l în jos. Bine? Aruncă-l înapoi la.
Bill Jelen: Hei! Bine, Mike, ambele metode au fost minunate. Să facem un rezumat rapid aici. Prima mea metodă folosind Textul în coloane: Pasul 1, alege Delimitat; Pasul 2, alegeți un spațiu, apoi faceți clic pe Finalizare. Problema este că, dacă aveți mai multe spații, va ajunge în mai multe celule. Trebuie să le pun la loc. Office 365 TEXTJOIN sau vechiul B2 & „” & C2 și așa mai departe.
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.
În regulă, vreau să le mulțumesc tuturor că au trecut pe aici. Ne vedem data viitoare pentru un alt Dueling Excel Podcast de la și Excel este distractiv.
Descărcare fișier
Descărcați exemplarul de fișier aici: Duel182.xlsm
