Ma őrült kérdés. Van egy oszlop cikkszámokból. A cikkszámban 4-7 kötőjel található. Csak az alkatrész számának az első kötőjel utáni részét akarja kinyerni, de legfeljebb az utolsó kötőjelig. Ez egy párbajozó Excel-epizód.
Videót nézni
- A cél az, hogy megtalálja az első és utolsó kötőjelet, és mindent megtartson a kettő között
- A legnehezebb itt megtalálni az utolsó gondolatjelet
- Bill 1. módszer: Flash Fill
- Az első néhány kézi kitöltése (beleértve a különböző számú kötőjelet is)
- Jelölje ki az alatta lévő üres cellát
- Ctrl + E a Flash Fill-hez
- Mike 2. módszer:
- Használja a Power Query alkalmazást
- Az Excel 2016 programban a Power Query az Excel 2016 Get & Transform csoportjában található
- Az Excel 2010 és 2013 programban töltse le a Power Query alkalmazást a Microsoft oldaláról. Létrehoz egy új Power Query fület a szalagon
- Konvertálja adatait táblázattá a Ctrl + T gombbal
- Használja az Adatok felosztása a Power Query alkalmazásban - először a bal szélső, majd a jobb szélső vonalon
- Bill módszer 3:
- VBA Funkció, amely a cella végétől visszafelé iterál, hogy megtalálja az utolsó gondolatjelet
- Mike 4. módszer:
- A SUBSTITUTE segítségével keresse meg az N. kötőjel helyét
- A SUBSTITUTE az egyetlen szöveges függvény, amely lehetővé teszi egy példányszám megadását
- A példányszám megtalálásához használja
=LEN(A2)-LEN(SUBSTITUTE)
Videó átirat
Bill: Hé. Üdv újra. Itt az ideje egy újabb Dueling Excel podcastnak. Bill Jelen vagyok a MrExcel-től. (Mike Girvin csatlakozik az ExcelIsFun-tól. Ez a mi - 00:03) 185. rész: kivonat az elsőtől az utolsóig.
Rendben. A mai kérdést Anvar küldte el a YouTube-on. Hogyan tudok mindent kibontani az elsőtől az utolsóig, és megnézni ezeket az itt található adatait. Hatalmas számú kötőjel van, bárhol 3, 5, 6, 7 kötőjel, rendben?
Szóval, az első gondolatom az, hogy igen, hé, nagyon könnyű megtalálni az elsőt, igaz? = balra vagy = A2 MEGTALÁLVA MID, majd a -, +1 rendben van, de az utolsóig jutok -, ettől fájni fog a fejem, ugye, mert nos, hány kötőjelünk van? Megvehetnénk az A2 helyettesítőjét, helyettesítve a kötőjeleket, és összehasonlíthatnánk ennek hosszát, az eredeti hosszát. Ez megmondja a gondolatjelek számát, de most már tudom, hogy melyiket találjam meg, a 2., 3., 4., 5., de használjam a KERESET?
Készen álltam a VBA-ra menni, igaz? Ez a térdre rándulásom. Mondtam, várj egy kicsit. Azt mondtam, Anvar, az Excel melyik verziójában vagy? Azt mondja, az Excel 2016-ban vagyok. Azt mondtam, ez gyönyörű. Ha az Excel 2013-as vagy újabb verzióját használja, használhatnánk ezt a nagyszerű új funkciót, a flash kitöltést. A vakutöltéssel csak egy mintát kell adnunk neki, és elegendő mintát fogok adni neki, így nem csak az, hogy két kötőjellel veszek egyet, és ezt csinálom párszor. Biztosítani akarom, hogy így legyen néhány különböző kötőjelem. Csád az Excel csapatában tudja, mit keresek. Csád az a srác, aki megírta a flash kitöltés logikáját. Szóval kb. 3-at bekerülök oda, majd a CONTROL + E a parancsikon a DATA, majd a FLASH FILL használatához, és bizony, úgy tűnik, hogy helyesen cselekedett. Rendben, Mike.Lássuk, mi van.
Mike: Köszönöm, MrExcel. Igen. A flash kitöltés nyer. Ez a funkció, a flash kitöltés, az egyik modern Excel-eszköz, amely egyszerűen elképesztő. Ha egyszeri ügyletről van szó, és következetes mintázata van, akkor hé, én így csinálnám.
Hé, menjünk át a következő lapra. Most a flash kitöltés helyett valójában az energia lekérdezést is használhatjuk. Most az Excel 2016-ot használom, így van a GET & TRANSFORM csoport. Ez az erő lekérdezése. A korábbi verziókban, 2013-ban (10–2: 30-ig) valójában le kell töltenie az ingyenes lekérdezési bővítményt.
Most, hogy működőképes legyen az energia lekérdezés, ezt konvertálni kell Excel táblává. Most megint a flash fillet használnám, ha ez egyszeri üzlet lenne. Mikor használná a teljesítmény lekérdezést? Nos, ha valóban nagy adatai vannak, vagy ha külső forrásból származik, akkor ez a helyes út, vagy akár jobban is tetszik ez, mint ha 3-as vagy 4-es példát kellene beírnia a flash-kitöltésre, mert az erőforrás-lekérdezéssel kifejezetten azt mondják, hogy keresse meg az elsőt - és keresse meg az utolsót -.
Most ezt átalakítom egy Excel táblává. Egyetlen cellám van kiválasztva, végig üres cellák vannak. Az INSERT, TABLE oldalra megyek, vagy te a CONTROL + T billentyűzetet használod. Kattintson az OK vagy az ENTER gombra. Meg akarom nevezni ezt a táblázatot, ezért felmegyek a TÁBLÁZATI ESZKÖZÖK, A TERVEZÉS, a TULAJDONSÁGOK közé. Hívom ezt a STARTKEYTABLE-t és az ENTER-t. Most visszatérhetek a DATA-hoz, a FROM TABLE gombbal bekapcsolhatom az energiakérdezésbe. Ott az oszlopom. Ott van a név. Nem akarom megtartani ezt a nevet, mert a kimenetet exportáljuk az Excelbe, és más nevet szeretnék adni neki. Tehát hívom CLEANEDKEYTABLE-nek. Nincs szükségem arra a VÁLTOZOTT TÍPUSRA. Csak a forrást nézem. Most rákattinthatok az oszlopra, és közvetlenül a HOME-ban ott van a SPLIT gomb. Mondhatom SPLIT, ELhatárolóval. Úgy tűnik, már sejtette. ÉN'azt fogom mondani, hogy a BAL-TÖBB. Kattintson az OK gombra.
Ha átnézek ide, megváltozott típust látok. Nincs erre szükségem, ezért megszabadulok ettől a lépéstől. Csak SPLIT COLUMN by DELIMITER van. Most újra meg fogom csinálni, de ahelyett, hogy itt fent a SPLIT gombot használnám, kattints jobb egérgombbal a SPLIT COLUMN, DELIMITER BY-re, és nézd meg. Választhatjuk, hogy felosztjuk-e a LEGJOBB KIVÁLASZTÓVAL. Kattintson az OK gombra. Most nincs szükségem erre a két oszlopra, ezért jobb egérgombbal kattintok arra az oszlopra, amelyet meg akarok tartani, TÖRLÉS MÁS OSZLOPOK. Valójában X-et fogok kinyitni. Azt fogja mondani, BIZTOSAN, HOGY EZT TÖRLI? Azt fogom mondani, hogy igen, TÖRLÉS. Ott vannak a tiszta adataim.
Most feljöhetek a CLOSE & LOAD-ra. ZÁRNI ÉS TÖLTENI. Ez az új IMPORT párbeszédpanel. Azt szokták mondani, hogy LOAD TO, de szeretném betölteni egy asztalhoz, egy MEGLÉVŐ MUNKALAP-ra. Kattintson az összecsukás gombra. Kiválasztom a C1-et, összecsukom, kattintson az OK gombra, és megyünk. Teljesítmény lekérdezés az adatok tisztításához és csak a kívánt adatok megszerzéséhez. Rendben. Visszadobom.
Bill: Pontosan ott van a lényeg, JOBB KIVÁLÓ KORLÁTOZÓ a SPLIT COLUMN BY DELIMITER segítségével, az energia lekérdezés egyik legjobb tulajdonsága. Ez elképesztő.
Rendben. A térdre ránduló reakcióm - VBA UDF (érthetetlen - 05:34) nagyon könnyen elvégezhető VBA. Váltson ALT + F11-re. MEGADJA A MODULT. Ebben a modulban írja be ezt a kódot. Teljesen új funkciót fogok létrehozni (- 05:43), MIDPART-nak fogom nevezni, és átadok neki egy szöveget, és akkor azt fogom tenni, hogy a cella utolsó karakterétől a MYTEXT hosszától az 1-ig, -1. LÉPÉSig megy, és megnézi ezt a karaktert. Tehát, a MYTEXT MID-je, az i változó megmondja, hogy melyik karaktert nézzük az 1 hosszúságra. Amint találok egy -, a MYTEXT LEFT-jét veszem az i - 1 karaktertől kezdve, úgyhogy mindentől megszabadulok az utolsókig - egészen ki, és aztán győződjön meg róla, hogy nem megy keressen további kötőjeleket, az EXIT FOR kiszabadít engem ebből (érthetetlen - 06:17) hurokból,és onnan a könnyű rész. Csak a MYTEXT-et vesszük, a MYTEXT MID-jétől kezdjük (ahol a - 06:26 -ot használom) használom a FIND funkciót az első megtalálásához -, megyünk ennél 1-rel többet, és visszaküldjük.
Tehát térjünk vissza, ALT + Q, hogy visszatérjünk az Excel programhoz. = Ennek MIDPART lapja, és úgy tűnik, hogy működik. Másold le. Mike, van még egy? (= MIDPart (A2))
Mike: Nos, van még egy, de ez egy hosszú képlet lesz - nem olyan rövid, mint az UDF. Rendben, menjünk át a következő lapra. Most, ha képletet fogunk készíteni, és van szövegünk, és mindig más és más elválasztó van, valahogy meg kell szereznem az utolsó elválasztó helyzetét.
Ez most néhány lépést fog megtenni, de a SUBSTITUTE funkcióval kezdem. Át fogom nézni azt a szöveget, a régi szöveg, amelyet meg akarok találni, benne van ”, ez -, és mit akarok a helyére tenni vagy helyettesíteni? „”. Ez nem tesz semmit be. Most, ha én) és a CONTROL + ENTER, mit fog csinálni? (= PÓT (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.
Nos, tessék. Szeretnék köszönetet mondani mindenkinek, hogy betértek. Legközelebb találkozunk egy másik Dueling Excel podcast-szal és az ExcelIsFun-tól.
Fájl letöltése
Töltse le a minta fájlt innen: Duel185.xlsm
