Šodien ir traks jautājums. Jums ir daļu numuru kolonna. Daļas numurā ir no 4 līdz 7 domuzīmēm. Jūs vēlaties iegūt tikai daļas numuru pēc pirmās domuzīmes un līdz pēdējai domuzīmei, bet neieskaitot. Šī ir duelis Excel epizode.
Skatīties video
- Mērķis ir atrast pirmo un pēdējo domuzīmi un saglabāt visu starp tām
- Šeit ir grūti atrast pēdējo domuzīmi
- Bila 1. metode: zibspuldzes aizpildīšana
- Manuāli aizpildiet dažus pirmos (ieskaitot dažus ar atšķirīgu domuzīmju skaitu)
- Atlasiet tukšo šūnu zem tā
- Ctrl + E, lai aizpildītu zibspuldzi
- Maika 2. metode:
- Izmantojiet Power Query
- Programmā Excel 2016 Power Query ir programmā Excel 2016 iegūt un pārveidot
- Programmā Excel 2010 un 2013 lejupielādējiet Power Query no Microsoft. Tas lentē izveido jaunu cilni Power Query
- Pārvērtiet datus tabulā, izmantojot Ctrl + T
- Izmantojiet sadalītos datus Power Query - vispirms, lai sadalītu pa kreiso domuzīmi, pēc tam, lai sadalītu pa labo un labo domuzīmi
- Bila metode 3:
- VBA Funkcija, kas atkārtojas no šūnas beigām atpakaļ, lai atrastu pēdējo domuzīmi
- Maika 4. metode:
- Izmantojiet SUBSTITUTE, lai atrastu N domuzīmes atrašanās vietu
- SUBSTITUTE ir vienīgā teksta funkcija, kas ļauj norādīt instances numuru
- Lai atrastu instances numuru, izmantojiet
=LEN(A2)-LEN(SUBSTITUTE)
Video atšifrējums
Bils: Hei. Laipni lūdzam atpakaļ. Ir pienācis laiks vēl vienai Dueling Excel podcast. Es esmu Bils Jelens no MrExcel. (Man pievienosies Maiks Girvins no ExcelIsFun. Tas ir mūsu - 00:03) 185. sērija: izvilkums no pirmās - līdz pēdējai -.
Labi. Šodienas jautājumu vietnē YouTube iesūtīja Anvar. Kā es varu iegūt visu no pirmā - līdz pēdējam - un pārbaudīt šos datus, kas viņam šeit ir. Ir ļoti daudz domuzīmju, kaut kur no 3, 5, 6, 7 domuzīmēm, labi?
Tātad, mana pirmā doma ir: labi, hei, ir ļoti viegli atrast pirmo - vai ne? = pa kreisi vai = A2 ATRASTĀ MID un pēc tam -, +1 labi, bet, lai tiktu līdz pēdējam, tas man sāpēs, labi, jo labi, cik mums ir domuzīmju? Mēs varētu ņemt aizvietotāju A2, aizstājot domuzīmes, un salīdzināt tā garumu, sākotnējo garumu. Tas man norāda domuzīmju skaitu, bet tagad es zinu, kuru - atrast, 2., 3., 4., 5., bet vai es izmantoju ATRAST?
Es biju gatavs doties uz VBA, vai ne? Tā ir mana ceļgala reakcija. Es teicu, pagaidi sekundi. Es teicu: Anvar, kurā Excel versijā tu esi? Viņš saka: Es esmu programmā Excel 2016. Es teicu, tas ir skaisti. Ja izmantojat programmu Excel 2013 vai jaunāku versiju, mēs varētu izmantot šo lielisko jauno funkciju, ko sauc par zibspuldzes aizpildīšanu. Izmantojot zibspuldzes aizpildīšanu, mums vienkārši jāpiešķir modelis, un es piešķiršu tam pietiekami daudz modeļa, tāpēc tas nav tikai tas, ka es ņemu vienu ar divām domuzīmēm un daru to pāris reizes. Es vēlos pārliecināties, ka man ir dažas dažādas domuzīmes. Čada Excel komandā zina, ko es meklēju. Čada ir puisis, kurš uzrakstīja zibspuldzes aizpildīšanas loģiku. Tātad, man tur ir apmēram 3 no tiem, un tad CONTROL + E ir īsceļš DATA un pēc tam FLASH FILL lietošanai, un, protams, izskatās, ka tas izdarīja pareizi. Labi, Maik.Apskatīsim, kas jums ir.
Maiks: Paldies, MrExcel. Jā. Uzvar zibspuldze. Šī funkcija turpat, zibspuldzes aizpildīšana, ir viens no mūsdienu Excel rīkiem, kas ir vienkārši pārsteidzošs. Ja tas ir vienreizējs darījums un jums ir konsekventa shēma, hey, es to darītu tā.
Hei, pārejam uz nākamo lapu. Tagad, tā vietā, lai izmantotu zibspuldzi, mēs faktiski varam izmantot enerģijas pieprasījumu. Tagad es izmantoju programmu Excel 2016, tāpēc man ir GET & TRANSFORM grupa. Tas ir jaudas vaicājums. Iepriekšējās versijās, 2013. gadā (līdz 10 - 2:30), jums faktiski ir jālejupielādē bezmaksas enerģijas vaicājuma pievienojumprogramma.
Tagad, lai darbotos jaudas vaicājums, tas ir jāpārvērš par Excel tabulu. Tagad es atkal izmantotu zibspuldzi, ja tas būtu vienreizējs darījums. Kad jūs izmantojat enerģijas pieprasījumu? Ja jums būtu patiešām lieli dati vai ja jūs nākat no ārēja avota, tas būtu pareizais ceļš, vai jums tas pat varētu patikt labāk, nekā zibatmiņas aizpildīšanai jāievada 3. vai 4. piemēri, jo ar enerģijas pieprasījumu mēs varam konkrēti sakiet atrodiet pirmo - un atrodiet pēdējo -.
Tagad es to pārveidošu par Excel tabulu. Man ir atlasīta viena šūna, tukšas šūnas visapkārt. Es eju uz INSERT, TABLE, vai arī jūs izmantojat tastatūru, CONTROL + T. Varu noklikšķināt uz OK vai ENTER. Es gribu nosaukt šo tabulu, tāpēc es pārietu uz GALDA INSTRUMENTI, DIZAINS, uz sadaļu ĪPAŠĪBAS. Es to saucu par STARTKEYTABLE un ENTER. Tagad es varu atgriezties pie DATA, ievadīt to enerģijas pieprasījumā, izmantojot pogu FROM TABLE. Tur ir mana kolonna. Tur ir nosaukums. Es nevēlos saglabāt šo vārdu, jo izeja tiks eksportēta uz Excel, un es vēlos tai piešķirt citu nosaukumu. Tātad, es to saucu par CLEANEDKEYTABLE. Man nav vajadzīgs tas PĀRMAIŅOTais VEIDS. Es tikai skatos avotā. Tagad es varu noklikšķināt uz kolonnas, un tieši HOME augšpusē ir poga SPLIT. Es varu teikt SPLIT, BY DELIMITER. Izskatās, ka tas jau ir uzminēts. Esm gatavojas teikt LEFT-Most. Noklikšķiniet uz Labi.
Now, if I look over here I see CHANGED TYPE. I don't need that so I'm going to get rid of that step. I only have SPLIT COLUMN BY DELIMITER. Now, I'm going to do this again but, instead of using the SPLIT button up here, right click down to SPLIT COLUMN, BY DELIMITER, and look at that. We can choose to split it by the RIGHT-MOST DELIMITER. Click OK. Now, I don't need these two columns so I'm going to right click the column I want to keep, REMOVE OTHER COLUMNS. I'm actually going to X this CHANGED TYPE out. It's going to say ARE YOU SURE YOU WANT TO DELETE THIS? I'm going to say, yes, DELETE. There's my clean data.
Now I can come up to CLOSE & LOAD. CLOSE & LOAD TO. This is the new IMPORT dialog box. It used to say LOAD TO but I want to load it to a table, on an EXISTING WORKSHEET. Click the collapse button. I'm going to select C1, uncollapse, click OK, and there we go. Power query to clean our data and get just the data we want. Alright. I'll throw it back to.
Bill: There’s the point right there, RIGHT-MOST DELIMITER in the SPLIT COLUMN BY DELIMITER, one of the cool features in power query. That's awesome.
Alright. My knee-jerk reaction -- VBA UDF (unintelligible - 05:34) really easy to do VBA. Switch over to ALT+F11. INSERT a MODULE. In that module, type this code. I'm going to (create a - 05:43) brand new function, I’m going to call it MIDPART, and I'm going to pass it some text, and then what I'm going to do is I'm going to go from the last character in that cell from the length of MYTEXT back to 1, STEP -1 and look at that character. So, the MID of MYTEXT, that variable i, tells us which character we're looking at for length of 1. Is it a -? As soon as I find a -, I'm going to take the LEFT of MYTEXT starting at character i - 1, so I get rid of everything for that last - all the way out, and then, make sure I don't go keep looking for more dashes, the EXIT FOR will get me out of this (unintelligible - 06:17) loop, and from there is the easy part. We're just going to take the MYTEXT, start at the MID of MYTEXT, (where I use the - 06:26) use the function FIND to find the first -, go 1 more than that, and return that back.
So, let's go back, ALT+Q, to return to Excel. = MIDPART tab of that, and it looks like it's working. Copy that down. Mike, do you have another one? (=MIDPart(A2))
Mike: Well, I do have another one,, but it's going to be one long formula -- not as short as that UDF. Alright, let's go over to the next sheet. Now, if we're going to do a formula and we have some text and there are always a different number of delimiters, somehow, I need to get the position of that last delimiter.
Now, this is going to take a few steps but I'm going to start with the SUBSTITUTE function. I'm going to look through that text, , the old text I want to find is in ”, that -, , and what do I want to put in its place or substitute? “”. That will put nothing in. Now, if I ) and CONTROL+ENTER, what is that going to do? (=SUBSTITUTE(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.
Nu, tur jums iet. Es gribu pateikties visiem par apstāšanos. Tiksimies nākamreiz, lai skatītu vēl vienu Dueling Excel Podcast no un ExcelIsFun.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Duel185.xlsm
