Täna on hull küsimus. Teil on veerg osade numbritest. Osanumbris on kuskil 4–7 kriipsu. Sa tahad välja tõmmata ainult osa osa numbrist pärast esimest ja kuni viimase kriipsuni, kuid mitte. See on kahevõistluse Exceli episood.
Vaata videot
- Eesmärk on leida esimene ja viimane kriips ning hoida kõik selle vahel
- Raske osa on siin viimase kriipsu leidmine
- Arve 1. meetod: välgu täitmine
- Esimesed paar (sh mõned erineva kriipsude arvuga) täitke käsitsi
- Valige selle all tühi lahter
- Flash-täitmiseks klahvikombinatsioon Ctrl + E
- Mike 2. meetod:
- Kasutage Power Query
- Excelis 2016 on Power Query Exceli 2016 rühmas Hangi ja muuda
- Rakendustes Excel 2010 ja 2013 laadige Microsoftist alla Power Query. See loob lindile uue vahekaardi Power Query
- Teisendage oma andmed tabeliks, kasutades klahvikombinatsiooni Ctrl + T
- Kasutage Power Query'is jagatud andmeid - kõigepealt jagage vasakpoolses ja seejärel kõige paremas kriipsus
- Arve meetod 3:
- VBA Funktsioon, mis kordub lahtri otsast tagasi, et leida viimane kriips
- Mike 4. meetod:
- N-nda kriipsu asukoha leidmiseks kasutage nuppu SUBSTITUTE
- SUBSTITUTE on ainus tekstifunktsioon, mis võimaldab teil määrata eksemplari numbri
- Eksemplari numbri leidmiseks kasutage
=LEN(A2)-LEN(SUBSTITUTE)
Video ärakiri
Bill: Hei. Tere tulemast tagasi. On aeg teise Dueling Exceli taskuhäälingu jaoks. Olen Bill Jelen MrExcelist. (Minuga liitub Mike Girvin ExcelIsFunist. See on meie - 00:03) osa 185: väljavõte esimesest - viimaseni.
Hästi. Tänase küsimuse saatis Anvar YouTube'is. Kuidas ma saan kõigest esimesest välja tõmmata - kuni viimase - ja vaadata neid andmeid, mis tal siin on. Kriipse on tohutult palju, kõikjal alates 3, 5, 6, 7 kriipsust, eks?
Minu esimene mõte on, et noh, hei, esimest on tõesti lihtne leida, eks? = vasakule või = KESKMINE leitavast A2-st ja siis -, +1 korras, kuid viimaseni jõudmiseks - teeb see mu pea valutama, eks, sest noh, kui palju meil on kriipse? Võiksime asendada kriipsud asendades asendaja A2 ja võrrelda selle pikkust, algset pikkust. See ütleb mulle kriipsude arvu, kuid nüüd tean, kumb - leida, 2., 3., 4., 5., kuid kas ma kasutan LEIA?
Ma olin valmis VBAsse minema, eks? See on minu põlve-jerk-reaktsioon. Ma ütlesin, et oota hetk. Ma ütlesin, Anvar, mis Exceli versioonis sa oled? Ta ütleb, et olen Excelis 2016. Ma ütlesin, see on ilus. Kui olete rakenduses Excel 2013 või uuem, võiksime kasutada seda suurepärast uut funktsiooni, mida nimetatakse välgu täitmiseks. Välklambiga peame lihtsalt andma talle mustri ja ma annan talle piisavalt mustrit, nii et mitte ainult see, et võtan ühe kahe kriipsuga ja teen seda paar korda. Ma tahan veenduda, et mul oleks nii mõnedki erinevad kriipsud. Tšaad Exceli meeskonnas teab, mida ma otsin. Tšaad on tüüp, kes kirjutas välgu täitmise loogika. Niisiis, saan umbes 3 neist sinna ja siis on CONTROL + E otsetee DATA ja seejärel FLASH FILL kasutamiseks ning tõepoolest tundub, et see tegi õiget asja. Hästi, Mike.Vaatame, mis teil on.
Mike: Tänan, MrExcel. Jah. Väljatäide võidab. See funktsioon, Flash-täitmine, on üks tänapäevaseid Exceli tööriistu, mis on lihtsalt hämmastav. Kui see on ühekordne tehing ja teil on järjepidev muster, siis hei, ma teeksin seda nii.
Hei, läheme üle järgmise lehe juurde. Nüüd saame välkkiirete täitmise asemel kasutada ka võimsuse päringuid. Nüüd kasutan rakendust Excel 2016, nii et mul on rühm HÕLMA ja TEISE TEISEMINE. See on võimsuse päring. Varasemates versioonides, 2013 (kuni 10–2:30), peate tegelikult alla laadima tasuta energiapäringu lisandmooduli.
Nüüd tuleb energiapäringu töötamiseks see teisendada Exceli tabeliks. Nüüd, kui see oleks ühekordne tehing, kasutaksin taas välkkiiret täitmist. Millal kasutaksite energiapäringut? Noh, kui teil on tõesti suuri andmeid või kui olete pärit välisest allikast, oleks see õige tee või see võib teile isegi meeldida paremini, kui peate sisestama välgu täitmiseks 3 või 4 näidet, sest toite päringuga saame konkreetselt öelge, et leidke esimene - ja leidke viimane -.
Nüüd teisendan selle Exceli tabeliks. Mul on valitud üks lahter, tühjad lahtrid ümberringi. Avan loendi INSERT, TABLE või kasutate klaviatuuri CONTROL + T. Ma saan klõpsata OK või ENTER. Ma tahan sellele tabelile nime panna, nii et lähen üles tabelite tööriistadesse, kujundus, üles omadustele. Helistan sellele STARTKEYTABLE ja ENTER. Nüüd saan naasta DATA juurde, tuua see nupule FROM TABLE toite päringutesse. Seal on minu veerg. Seal on nimi. Ma ei taha seda nime säilitada, sest väljund eksporditakse Excelisse ja ma tahan sellele teise nime anda. Nii et ma nimetan seda CLEANEDKEYTABLE. Mul pole seda MUUDATUD TÜÜPI vaja. Ma lihtsalt vaatan allikat. Nüüd saan klõpsata veerul ja paremal HOME-is on nupp SPLIT. Ma võin öelda, et SPLIT, piiritleja. Tundub, et seda on juba aimata. MinaMa ütlen VASAK-KÕIGE. Klõpsake nuppu OK.
Kui ma siia vaatan, näen ma MUUDATUD TÜÜPI. Mul pole seda vaja, nii et ma lähen sellest sammust lahti. Mul on ainult SPLIT COLUMN by DELIMITER. Nüüd teen seda uuesti, kuid selle asemel, et siin nuppu SPLIT kasutada, klõpsake hiire parema nupuga nuppu SPLIT COLUMN, DELIMITER BY, ja vaadake seda. Saame valida, kas jagada see kõige paremaks piirajaks. Klõpsake nuppu OK. Nüüd pole mul neid kahte veergu vaja, nii et paremklõpsan veerul, mida soovin säilitada, EEMALDA TEISED VEERUD. Ma lähen tegelikult X-i selle MUUDETUD TÜÜPI välja. See ütleb, et KAS SA KINDLASTI SEDA KUSTUTADA? Ma ütlen, et jah, KUSTUTA. Seal on minu puhtad andmed.
Nüüd saan tulla üles SULETUD & LAADI. SULGE JA LAADI. See on uus IMPORT-dialoogiboks. Varem öeldi LOAD TO, aga ma tahan selle laadida tabelisse, OLEMASOLEVALE TÖÖLEHTELE. Klõpsake ahendamise nuppu. Ma valin C1, tühjendage klõps, klõpsake nuppu OK ja seal me läheme. Power-päring meie andmete puhastamiseks ja just soovitud andmete saamiseks. Hästi. Viskan selle tagasi.
Bill: Siin on mõte, PAREM-KÕIGE PIIRAJA veerus SPLIT COLIM by DELIMITER, mis on üks võimsamaid funktsioone toite päringus. See on suurepärane.
Hästi. Minu reaktsioon põlvedele - VBA UDF (arusaamatu - 05:34) on VBA-d väga lihtne teha. Lülitage ALT + F11. Sisestage moodul. Sellesse moodulisse tippige see kood. Ma lähen (loo - 05:43) täiesti uue funktsiooni, ma nimetan seda MIDPART-ks ja edastan sellele teksti ja siis ma teen läheb selle lahtri viimasest tähemärgist MYTEXT pikkusest tagasi väärtuseni 1, STEP -1 ja vaatab seda märki. Niisiis, selle muutuja i MYTEXT MID ütleb meile, millist tähemärki me vaatame pikkusega 1. Kas see on -? Niipea, kui leian -, võtan MYTEXTi VASAKU, alustades tähemärgist i - 1, nii et ma vabanen sellest kõigest - lõpuni ja veenduge, et ma ei läheks jätkake uute kriipsude otsimist, EXIT FOR viib mind sellest (arusaamatust - 06:17) silmusest välja,ja sealt edasi on lihtne osa. Me võtame lihtsalt MYTEXTi, alustame MYTEXTi keskpaigast (kus ma kasutan - 06:26) kasutage funktsiooni LEIDA, et leida esimene -, läheksime veel 1 juurde ja tagastaksime selle tagasi.
Naaseme Excelisse naasmiseks ALT + Q. = Selle vahekaart MIDPART ja tundub, et see töötab. Kopeerige see üles. Mike, kas sul on veel üks? (= MIDPart (A2))
Mike: Noh, mul on veel üks, aga see saab olema üks pikk valem - mitte nii lühike kui see UDF. Hästi, läheme üle järgmise lehe juurde. Kui nüüd teeme valemit ja meil on teksti ja eraldajaid on alati erinev arv, pean kuidagi saama selle viimase eraldaja positsiooni.
Nüüd võtab see paar sammu, kuid alustan funktsiooniga ASENDA. Ma vaatan selle teksti läbi, vana tekst, mille tahan leida, on sees ”, see -, ja mida ma tahan selle asemele panna või asendada? „”. See ei pane midagi sisse. Kui ma) ja CONTROL + ENTER, siis mida see tegema hakkab? (= ASENDA (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.
Noh, seal sa lähed. Tahan kõiki tänada peatumiste eest. Järgmisel korral näeme veel ühe Dueling Exceli taskuhäälingu saatjalt ja ExcelIsFun.
Laadige fail alla
Laadige näidisfail alla siit: Duel185.xlsm
