Astăzi este o întrebare nebună. Aveți o coloană cu numere de piese. Numărul piesei include între 4 și 7 liniuțe. Doriți să extrageți doar porțiunea numărului piesei după prima liniuță și până la, dar fără a include ultima liniuță. Acesta este un episod Excel în duel.
Urmăriți videoclipul
- Scopul este să găsiți prima și ultima liniuță și să păstrați totul între ele
- Partea dificilă aici este găsirea ultimei liniuțe
- Metoda de facturare 1: Flash Fill
- Completați manual primele câteva (inclusiv unele cu un număr diferit de liniuțe)
- Selectați celula goală de sub aceasta
- Ctrl + E la Flash Fill
- Mike Metoda 2:
- Folosiți Power Query
- În Excel 2016, Power Query se află în grupul Obțineți și transformați în Excel 2016
- În Excel 2010 și 2013, descărcați Power Query de la Microsoft. Se creează o nouă filă Power Query în panglică
- Convertiți datele într-un tabel folosind Ctrl + T
- Utilizați divizarea datelor în Power Query - mai întâi pentru a împărți la linia din stânga, apoi pentru a împărți la linia din dreapta
- Metoda de facturare 3:
- Funcție VBA care iterează de la capătul celulei înapoi pentru a găsi ultima liniuță
- Metoda Mike 4:
- Utilizați SUBSTITUTE pentru a găsi locația liniei a N-a
- SUBSTITUTE este singura funcție de text care vă permite să specificați un număr de instanță
- Pentru a găsi numărul instanței, utilizați
=LEN(A2)-LEN(SUBSTITUTE)
Transcriere video
Bill: Hei. Bine ai revenit. Este timpul pentru un alt podcast Dueling Excel. Sunt Bill Jelen de la MrExcel. (Mi se va alătura Mike Girvin de la ExcelIsFun. Acesta este episodul nostru - 00:03) 185: extras de la primul - la ultimul -.
Bine. Întrebarea de astăzi este trimisă de Anvar pe YouTube. Cum pot extrage totul de la primul - până la ultimul - și să verific aceste date pe care le are aici. Există un număr imens de liniuțe, de la 3, 5, 6, 7 liniuțe, bine?
Deci, primul meu gând este, bine, hei, este foarte ușor să-l găsești pe primul - nu? = stânga sau = MID din FIND de A2 și apoi -, +1 bine, dar pentru a ajunge la ultimul -, asta mă va face să mă doară capul, nu, pentru că, bine, câte liniuțe avem? Am putea lua SUBSTITUTUL lui A2, înlocuind cratimele și să comparăm lungimea acestuia, lungimea inițială. Asta îmi spune numărul de liniuțe, dar acum știu care - să găsesc, al 2-lea, al 3-lea, al 4-lea, al 5-lea, dar folosesc FIND?
Eram gata să merg la VBA, nu? Aceasta este reacția mea de genunchi. Am spus, așteaptă o secundă. Am spus, Anvar, în ce versiune de Excel te afli? El spune că sunt în Excel 2016. Am spus că este frumos. Dacă sunteți în Excel 2013 sau mai nou, am putea folosi această nouă funcție extraordinară numită flash fill. Cu umplere cu bliț, trebuie doar să-i dăm un model, iar eu îl voi oferi suficient de mult, așa că nu doar că iau unul cu două liniuțe și fac asta de câteva ori. Vreau să mă asigur că am câteva liniuțe diferite în acest fel. Ciadul din echipa Excel știe ce caut. Chad este tipul care a scris logica pentru umplerea cu bliț. Deci, am aproximativ 3 dintre ele acolo și apoi CONTROL + E este comanda rapidă pentru utilizarea DATA și apoi FLASH FILL și, destul de sigur, se pare că a făcut ceea ce trebuie. Bine, Mike.Să vedem ce ai.
Mike: Mulțumesc, MrExcel. Da. Flash fill câștigă. Această caracteristică chiar acolo, umplerea cu bliț, este unul dintre instrumentele Excel moderne, care este pur și simplu uimitor. Dacă este o afacere unică și aveți un model consistent, hei, așa aș face-o eu.
Hei, să trecem la următoarea foaie. Acum, în loc să folosim umplerea cu bliț, putem folosi de fapt interogarea de alimentare. Acum, folosesc Excel 2016, așa că am grupul GET & TRANSFORM. Aceasta este interogarea de putere. În versiunile anterioare, 2013 (la 10 - 2:30), trebuie să descărcați suplimentul pentru interogare gratuită.
Acum, pentru ca interogarea de energie să funcționeze, aceasta trebuie convertită într-un tabel Excel. Acum, din nou, aș folosi flash fill dacă acest lucru ar fi o singură dată. Când ați utiliza interogarea de alimentare? Ei bine, dacă ați avea date foarte mari sau proveniți dintr-o sursă externă, aceasta ar fi calea de urmat sau chiar s-ar putea să vă placă acest lucru mai bine decât să tastați 3 sau 4 exemple pentru umplere flash, deoarece, cu interogare de putere, putem specific spune că găsești primul - și găsește ultimul -.
Acum, voi converti acest lucru într-un tabel Excel. Am selectată o singură celulă, celule goale tot timpul. Mă duc la INSERT, TABLE sau folosiți tastatura, CONTROL + T. Pot face clic pe OK sau ENTER. Vreau să numesc acest tabel, așa că voi merge la TABLE TOOLS, PROIECTARE, în PROPRIETĂȚI. Voi numi asta STARTKEYTABLE și ENTER. Acum pot să mă întorc la DATA, să-l aduc la interogarea de alimentare folosind butonul FROM TABLE. Uite coloana mea. Uite numele. Nu vreau să păstrez acest nume, deoarece ieșirea va fi exportată în Excel și vreau să îi dau un nume diferit. Deci, îl voi numi CLEANEDKEYTABLE. Nu am nevoie de TIPUL DE MODIFICAT. Mă uit doar la sursă. Acum pot face clic pe coloană și, chiar în HOME, există butonul SPLIT. Pot spune SPLIT, BY DELIMITER. Se pare că a ghicit deja. Eu 'Voi spune CEL MAI STÂNG. Faceți clic pe OK.
Acum, dacă mă uit aici, văd TIP SCHIMBAT. Nu am nevoie de asta, așa că voi scăpa de acel pas. Am doar COLONĂ SPLITĂ CU DELIMITATOR. Acum, voi face acest lucru din nou, dar, în loc să folosesc butonul SPLIT aici, faceți clic dreapta în jos la SPLIT COLUMN, BY DELIMITER, și uitați-vă la asta. Putem alege să îl împărțim cu DREPTUL-MULȚI DELIMITATOR. Faceți clic pe OK. Acum, nu am nevoie de aceste două coloane, așa că voi face clic dreapta pe coloana pe care doresc să o păstrez, ÎNLĂTURĂ ALTE CULOANE. De fapt, voi merge la X acest TIP SCHIMBAT. Va spune că Ești sigur că vrei să ștergi asta? Voi spune, da, ȘTERGE. Sunt datele mele curate.
Acum pot veni la CLOSE & LOAD. ÎNCHIS ȘI ÎNCARCĂ. Aceasta este noua casetă de dialog IMPORT. Se spunea ÎNCĂRCARE, dar vreau să-l încarc pe o masă, pe o FIȘĂ DE LUCRU EXISTENTĂ. Faceți clic pe butonul de restrângere. Am de gând să selectez C1, să desfac, să fac clic pe OK și iată-ne. Solicitați o interogare pentru a ne curăța datele și a obține doar datele dorite. Bine. Îl voi arunca înapoi.
Factură: Ideea este chiar acolo, DREPT-MULTE DELIMITER în COLOANA SPLITĂ CU DELIMITER, una dintre caracteristicile interesante din interogarea de putere. Asta-i grozav.
Bine. Reacția mea de genunchi - VBA UDF (de neînțeles - 05:34) foarte ușor de făcut VBA. Treceți la ALT + F11. INSERAȚI UN MODUL. În acel modul, tastați acest cod. Voi crea (crea o - 05:43) o funcție nouă, o voi numi MIDPART și îi voi transmite un text, iar apoi ceea ce voi face este că sunt voi merge de la ultimul caracter din acea celulă de la lungimea MYTEXT înapoi la 1, PASUL -1 și uită-te la acel caracter. Deci, MID din MYTEXT, acea variabilă i, ne spune la ce personaj ne uităm pentru lungimea 1. Este un -? De îndată ce găsesc un -, voi lua STÂNGA MYTEXT începând cu caracterul i - 1, așa că scap de tot pentru ultimul - până la capăt, și apoi, asigură-mă că nu merg continuați să căutați mai multe liniuțe, EXIT FOR mă va scoate din această buclă (neinteligibilă - 06:17),și de acolo este partea ușoară. Vom lua MYTEXT-ul, vom începe de la MID-ul MYTEXT-ului (unde folosesc - 06:26) folosim funcția FIND pentru a-l găsi pe primul -, mergem 1 mai mult decât atât și îl returnăm înapoi.
Deci, să ne întoarcem, ALT + Q, pentru a reveni la Excel. = Fila MIDPART a acestuia și se pare că funcționează. Copiați-l. Mike, mai ai unul? (= MIDPart (A2))
Mike: Ei bine, am încă una, dar va fi o formulă lungă - nu la fel de scurtă ca UDF. Bine, să trecem la foaia următoare. Acum, dacă vom face o formulă și avem un text și există întotdeauna un număr diferit de delimitatori, cumva, trebuie să obțin poziția ultimului delimitator.
Acum, acest lucru va face câțiva pași, dar voi începe cu funcția SUBSTITUTE. Voi căuta prin textul respectiv, textul vechi în care vreau să găsesc este ”, că -,, și ce vreau să pun în locul său sau să-l înlocuiesc? „”. Asta nu va pune nimic. Acum, dacă eu) și CONTROL + ENTER, ce va face asta? (= SUBSTITUT (A2, „-”, „”))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
Ei bine, iată-te. Vreau să mulțumesc tuturor pentru că am trecut pe aici. Ne vedem data viitoare pentru un alt podcast Dueling Excel de la și ExcelIsFun.
Descărcare fișier
Descărcați exemplarul de fișier aici: Duel185.xlsm
