Šiandien beprotiškas klausimas. Turite dalių numerių stulpelį. Dalies numeryje yra nuo 4 iki 7 brūkšnių. Norite išskleisti tik dalies numerį po pirmojo brūkšnio ir iki paskutinio brūkšnio, bet neįskaitant. Tai „Excel“ dvikovos epizodas.
Žiūrėti video
- Tikslas yra surasti pirmąjį ir paskutinį brūkšnį ir išlaikyti viską tarp jų
- Sunkiausia čia rasti paskutinį brūkšnį
- 1 Billo metodas: „Flash Fill“
- Neautomatiškai užpildykite keletą pirmųjų (įskaitant kai kuriuos su skirtingu brūkšnių skaičiumi)
- Pasirinkite tuščią langelį po juo
- „Ctrl“ + E, kad užpildytumėte „Flash“
- Mike 2 metodas:
- Naudokite „Power Query“
- „Excel 2016“ „Power Query“ yra „Excel 2016“ grupėje Gauti ir transformuoti
- „Excel 2010“ ir „2013“ atsisiųskite „Power Query“ iš „Microsoft“. Sukuriamas naujas juostos „Power Query“ skirtukas
- Konvertuokite duomenis į lentelę naudodami „Ctrl“ + T
- „Power Query“ naudokite padalijimo duomenis - pirmiausia, jei norite padalyti kairiausiu brūkšniu, tada - ties dešiniuoju
- 3 metodas:
- VBA funkcija, kuri kartojasi nuo langelio galo atgal, kad surastų paskutinį brūkšnį
- Mike 4 metodas:
- Naudokite SUBSTITUTE, kad rastumėte N-ojo brūkšnio vietą
- SUBSTITUTE yra vienintelė teksto funkcija, leidžianti nurodyti egzemplioriaus numerį
- Norėdami sužinoti, kurio egzemplioriaus numerį, naudokite
=LEN(A2)-LEN(SUBSTITUTE)
Vaizdo įrašo nuorašas
Bilas: Ei. Sveikas sugrįžęs. Atėjo laikas gauti dar vieną „Dueling Excel“ tinklalaidę. Aš esu Billas iš „MrExcel“. (Prie manęs prisijungs Mike'as Girvinas iš „ExcelIsFun“. Tai yra mūsų - 00:03) 185 epizodas: ištrauka nuo pirmojo iki paskutinio.
Gerai. Šios dienos klausimą „YouTube“ atsiuntė Anvaras. Kaip aš galiu išgauti viską nuo pirmo iki paskutinio ir patikrinti šiuos jo turimus duomenis čia. Yra daug brūkšnių, bet kur nuo 3, 5, 6, 7 brūkšnių, gerai?
Taigi, mano pirmoji mintis yra, gerai, ei, tikrai lengva rasti pirmąją - tiesa? = kairė arba = ATRASTA A2 viduryje ir tada -, +1 gerai, bet norint patekti į paskutinę - tai sukels galvą, tiesa, nes, na, kiek mes turime brūkšnių? Galėtume pakeisti A2 pakaitalą, pakeisdami brūkšnelius, ir palyginti jo ilgį, pradinį ilgį. Tai nurodo man brūkšnių skaičių, bet dabar aš žinau, kurį - rasti, 2, 3, 4, 5, bet ar naudoti RASTI?
Aš buvau pasirengęs eiti į VBA, tiesa? Tokia mano kelio trūkčiojimo reakcija. Aš pasakiau, palauk sekundę. Aš pasakiau: Anvarai, kokią „Excel“ versiją naudojate? Jis sako: „Aš esu„ Excel 2016 “. Aš sakiau, kad gražu. Jei naudojate „Excel 2013“ ar naujesnę versiją, galėtume naudoti šią puikią naują funkciją, vadinamą „flash fill“. Naudodami blykstės užpildymą, mes tiesiog turime suteikti jam modelį, o aš duosiu pakankamai modelio, todėl ne tik tai, kad aš imu vieną su dviem brūkšneliais ir darau tai porą kartų. Noriu įsitikinti, kad taip turiu kelis skirtingus brūkšnelius. Čadas iš „Excel“ komandos žino, ko ieškau. Čadas yra tas vaikinas, kuris parašė „flash fill“ logiką. Taigi, ten gaunu apie 3 iš jų, tada „CONTROL + E“ yra DATA ir tada „FLASH FILL“ naudojimo spartusis klavišas, ir, tikrai, atrodo, kad jis pasielgė teisingai. Gerai, Mike.Pažiūrėkime, ką turite.
Mike: Ačiū, MrExcel. Taip. „Flash“ užpildas laimi. Ši funkcija, „flash fill“, yra viena iš šiuolaikinių „Excel“ įrankių, kuri yra tiesiog nuostabi. Jei tai vienkartinis sandoris ir jūs turite nuoseklų modelį, ei, aš taip daryčiau.
Ei, pereikime prie kito lapo. Dabar, užuot naudoję „Flash“ užpildymą, iš tikrųjų galime naudoti energijos užklausą. Dabar naudoju „Excel 2016“, todėl turiu grupę GET & TRANSFORM. Tai galios užklausa. Ankstesnėse versijose, 2013 m. (Iki 10–2:30), iš tikrųjų turite atsisiųsti nemokamą maitinimo užklausos priedą.
Dabar, norint, kad maitinimo užklausa veiktų, ją reikia konvertuoti į „Excel“ lentelę. Dabar vėl naudočiau „flash fill“, jei tai būtų vienkartinis sandoris. Kada naudotumėte maitinimo užklausą? Na, jei turėtumėte tikrai didelius duomenis arba gautumėte iš išorinio šaltinio, tai būtų kelias, ar jums tai gali patikti geriau, nei kad turėtumėte įvesti 3 ar 4 pavyzdžius, jei norite užpildyti „Flash“, nes naudodami maitinimo užklausą galime konkrečiai pasakykite, kad raskite pirmąjį - ir raskite paskutinį.
Dabar aš konvertuosiu tai į „Excel“ lentelę. Turiu pasirinktą vieną langelį, tuščias langelius. Einu į „INSERT“, „LENTELĖ“ arba naudojate klaviatūrą „CONTROL + T“. Aš galiu spustelėti OK arba ENTER. Noriu pavadinti šią lentelę, todėl pereisiu prie STALO ĮRANKIŲ, DIZAINO, iki SAVYBIŲ. Aš tai pavadinsiu STARTKEYTABLE ir ENTER. Dabar galiu grįžti prie DUOMENŲ, perkelti jas į maitinimo užklausą naudodamas mygtuką IŠ LENTELĖS. Ten mano kolona. Yra vardas. Nenoriu palikti šio vardo, nes išvestis bus eksportuota į „Excel“ ir noriu suteikti jai kitą pavadinimą. Taigi, aš tai pavadinsiu CLEANEDKEYTABLE. Man nereikia to PAKEISTO TIPO. Aš tik žiūriu į šaltinį. Dabar galiu spustelėti stulpelį ir, tiesiai į viršų HOME, yra mygtukas SPLIT. Galiu pasakyti „SPLIT“, PASTATYTA. Panašu, kad tai jau atspėjo. Aš 'm pasakysiu LABIAUSI-DAUGIAU. Spustelėkite Gerai.
Dabar, jei peržvelgsiu čia, matau PASIKEITUS TIPUS. Man to nereikia, todėl atsikratysiu to žingsnio. Turiu tik SPLIT COLUMN BY DELIMITER. Dabar aš tai darysiu dar kartą, bet užuot naudojęs mygtuką SPLIT čia, dešiniuoju pelės mygtuku spustelėkite žemyn iki SPLIT COLUMN, BY DELIMITER ir pažiūrėkite į tai. Mes galime pasirinkti jį padalyti tiesiai į dešinę. Spustelėkite Gerai. Dabar man nereikia šių dviejų stulpelių, todėl dešiniuoju pelės mygtuku spustelėkite norimą išlaikyti stulpelį, PAŠALINKITE KITUS SKILTIS. Aš iš tikrųjų ketinu X šį PAKEISTĄ TIPĄ. Tai sakys, AR JŪS BŪTINAI NORI TAI IŠTRINTI? Aš pasakysiu: taip, IŠtrinti. Yra mano švarūs duomenys.
Dabar galiu sugalvoti UŽDARYTI IR ĮKELTI. UŽDARYTI IR ĮKELTI. Tai naujas dialogo langas IMPORTUOTI. Anksčiau sakydavo „LOAD TO“, bet aš noriu jį pakrauti prie stalo, ESAMOJE DIRBTUVE. Spustelėkite sutraukimo mygtuką. Aš ketinu pasirinkti C1, nesutraukti, spustelėti Gerai ir einame. „Power užklausa“, kad išvalytume savo duomenis ir gautume tik norimus duomenis. Gerai. Aš jį išmesiu atgal.
Sąskaita: Čia yra esmė, DAUGIAUSIAI SKIRTUMO NUSTATYMAS SPLIT COLUMN BY DELIMITER, viena iš šauniausių maitinimo užklausos funkcijų. Tai nuostabu.
Gerai. Mano reakcija į kelią - VBA UDF (nesuprantama - 05:34) tikrai lengva atlikti VBA. Perjunkite į ALT + F11. ĮDĖTI MODULĮ. Tame modulyje įveskite šį kodą. Aš ketinu (sukurti - 05:43) visiškai naują funkciją, pavadinsiu ją „MIDPART“ ir perduosiu jai tekstą, o tada aš eis nuo paskutinio to langelio simbolio nuo MYTEXT ilgio iki 1, -1 ŽINGSNIS ir pažvelk į tą simbolį. Taigi, MYTEXT, M Kai tik rasiu -, eisiu į kairę MYTEXT, pradedant nuo simbolio i - 1, taigi aš atsikratysiu visko, kad būtų paskutinis - iki galo, tada įsitikinkite, kad neišvažiuosiu toliau ieškokite daugiau brūkšnių, „EXIT FOR“ išves mane iš šios (nesuprantamos - 06:17) kilpos,ir iš ten yra lengvoji dalis. Mes tiesiog imsime MYTEXT, pradėsime nuo MYTEXT MID vidurio (kur aš naudoju - 06:26) naudokite funkciją RASTI, kad surastumėte pirmąjį -, eikime dar 1 ir grįšime atgal.
Taigi grįžkime atgal, ALT + Q, kad grįžtume į „Excel“. = To MIDPART skirtukas ir atrodo, kad jis veikia. Nukopijuokite tai žemyn. Mike, ar turi dar vieną? (= MIDPart (A2))
Mike'as: Na, aš turiu kitą, bet tai bus viena ilga formulė - ne tokia trumpa, kaip tas UDF. Gerai, pereikime prie kito lapo. Dabar, jei darysime formulę ir turėsime šiek tiek teksto, bet visada yra skirtingas atribotojų skaičius, kažkaip turiu sužinoti paskutinio skiriamojo elemento poziciją.
Dabar tai atliks kelis veiksmus, bet aš pradėsiu nuo funkcijos PAKEISTI. Aš peržvelgsiu tą tekstą, yra senas tekstas, kurį noriu rasti “, tai -, ir ką aš noriu įdėti į jo vietą ar pakeisti? „“. Tai nieko nepadės. Dabar, jei aš ir „CONTROL + ENTER“, ką tai darys? (= PAKEITIMAS (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.
Na, važiuok. Noriu padėkoti visiems už tai, kad užsukote. Kitą kartą pamatysime kitą „Dueling Excel“ tinklalaidę iš „ExcelIsFun“.
Atsisiųsti failą
Failo pavyzdį atsisiųskite čia: „Duel185.xlsm“
