Danes je noro vprašanje. Imate stolpec s številkami delov. V številki dela je od 4 do 7 pomišljajev. Po prvem pomišljaju želite izvleči samo del številke dela, vendar do vključno zadnje črte, ne pa tudi. To je dvobojna Excelova epizoda.
Oglejte si video
- Cilj je najti prvi in zadnji pomišljaj in obdržati vse vmes
- Tukaj je najtežje najti zadnji pomišljaj
- Metoda računa 1: Flash Fill
- Ročno izpolnite prvih nekaj (vključno z nekaterimi z različnim številom pomišljajev)
- Izberite prazno celico pod tem
- Ctrl + E za bliskavico
- Mike metoda 2:
- Uporabite Power Query
- V programu Excel 2016 je Power Query v skupini Get & Transform v programu Excel 2016
- V Excelu 2010 in 2013 prenesite Power Query iz Microsofta. Na traku ustvari nov zavihek Power Query
- Pretvorite podatke v tabelo s pomočjo Ctrl + T
- Uporabite razdeljene podatke v Power Query - najprej za razdelitev na skrajni levi pomišljaj, nato za razdelitev na skrajno desno pomišljaj
- Metoda računa 3:
- Funkcija VBA, ki se ponovi od konca celice nazaj, da najde zadnji pomišljaj
- Mike metoda 4:
- S SUBSTITUTE poiščite lokacijo N-te pomišljajne črte
- SUBSTITUTE je edina besedilna funkcija, ki vam omogoča, da določite številko primerka
- Če želite poiskati številko primerka, uporabite
=LEN(A2)-LEN(SUBSTITUTE)
Video zapis
Bill: Hej. Dobrodošel nazaj. Čas je za nov podcast Duel Excel. Sem Bill Jelen iz MrExcela. (Pridružil se mi bo Mike Girvin iz ExcelIsFun. To je naša - 00:03) epizoda 185: izvleček od prve - do zadnje -.
Vredu. Današnje vprašanje je Anvar poslal na YouTube. Kako lahko izvlečem vse od prvega - do zadnjega - in preverim te podatke, ki jih ima tukaj. Obstaja ogromno pomišljajev, od 3, 5, 6, 7 črtic, v redu?
Torej, moja prva misel je, no, hej, res je enostavno najti prvo - kajne? = levo ali = SREDINA FIND-a od A2 in nato -, +1 v redu, ampak da pridem do zadnjega -, to bo bolelo glavo, kajne, kaj, no, koliko črtk imamo? Lahko vzamemo NAMESTO A2, ki nadomešča pomišljaje, in primerjamo dolžino prvotne dolžine. To mi pove število pomišljajev, zdaj pa vem, katero - najti, 2., 3., 4., 5., ampak ali uporabljam FIND?
Bil sem pripravljen iti na VBA, kajne? To je moja reakcija sunka. Rekel sem, počakaj sekundo. Rekel sem, Anvar, v kateri različici Excela si? Pravi, da sem v Excelu 2016. Rekel sem, da je čudovito. Če uporabljate Excel 2013 ali novejšo različico, bi lahko uporabili to odlično novo funkcijo, imenovano flash fill. Pri bliskovnem polnjenju mu moramo samo dati vzorec in dal mu bom dovolj vzorca, tako da ne gre le za to, da vzamem enega z dvema pomišljajema in to nekajkrat. Želim se prepričati, da imam na ta način nekaj različnih pomišljajev. Chad iz Excelove ekipe ve, kaj iščem. Chad je tip, ki je napisal logiko za flash fill. Torej, tam jih dobim približno 3, nato pa je tipka CONTROL + E bližnjica za uporabo PODATKOV in nato FLASH FILL in, seveda, izgleda, da je naredila prav. V redu, Mike.Poglejmo, kaj imate.
Mike: Hvala, MrExcel. Ja. Flash polnjenje zmaga. Ta funkcija, ki je tam, flash fill, je eno sodobnih Excelovih orodij, ki je preprosto neverjetno. Če gre za enkraten dogovor in imate dosleden vzorec, hej, tako bi to naredil.
Hej, pojdimo na naslednji list. Zdaj, namesto da bi uporabili flash fill, lahko dejansko uporabimo power query. Zdaj uporabljam Excel 2016, zato imam skupino GET & TRANSFORM. To je vprašanje moči. V starejših različicah, 2013 (do 10. - 2.30), morate dejansko prenesti dodatek za brezplačno poizvedbo.
Da bi poizvedba o napajanju delovala, jo je treba pretvoriti v Excelovo tabelo. Zdaj bi spet uporabil flash fill, če bi šlo za enkratni posel. Kdaj bi uporabili napajanje? No, če bi imeli res velike podatke ali bi prihajali iz zunanjega vira, bi to lahko storili, ali pa vam bo to morda celo bolj všeč, kot da bi morali vnesti 3 ali 4 primere za bliskovno polnjenje, ker lahko z vklopljeno poizvedbo posebej recite poiščite prvo - in poiščite zadnjo -.
Zdaj bom to pretvoril v Excelovo tabelo. Izbrala sem samo eno celico, prazne celice pa vse do konca. Pojdem na INSERT, TABLE ali pa uporabite tipkovnico CONTROL + T. Lahko kliknem V redu ali ENTER. To tabelo želim poimenovati, zato se bom povzpel do TABLE TOOLS, DESIGN, gor do LASTNOSTI. To bom poklical STARTKEYTABLE in ENTER. Zdaj se lahko vrnem na DATA in ga vključim v napajanje z gumbom FROM TABLE. Tam je moja kolumna. Tam je ime. Tega imena ne želim obdržati, ker bo izhod izvožen v Excel in mu želim dati drugo ime. Torej, poimenoval ga bom CLEANEDKEYTABLE. Ne potrebujem tega SPREMENJENEGA TIPA. Samo gledam vir. Zdaj lahko kliknem na stolpec in v HOME, tam je gumb SPLIT. Lahko rečem SPLIT, DELIMITER. Izgleda, da je že uganilo. JAZ'bom rekel NAJBOLJ. Kliknite V redu.
Če zdaj pogledam sem, vidim SPREMENJEN TIP. Tega ne rabim, zato se bom tega koraka znebil. Imam samo RAZDELLJEN STOLPEC Z DELIMERJEM. Zdaj bom to ponovil še enkrat, vendar namesto, da bi tukaj uporabil gumb RAZDELI, z desno miškino tipko kliknite RAZDELI STOLPEC Z DELIMERJEM in si oglejte to. Odločimo se lahko, da ga razdelimo DO NAJBOLJ DELIMERJA. Kliknite V redu. Zdaj ne rabim teh dveh stolpcev, zato bom z desno miškino tipko kliknil stolpec, ki ga želim obdržati, ODSTRANI DRUGE STOLPCE. Pravzaprav grem na X ta SPREMENJEN TIP. Pisalo bo, STE SIGURNO ŽELELI IZBRISATI TO? Rekel bom, da, IZBRIŠI. Tu so moji čisti podatki.
Zdaj lahko pridem do CLOSE & LOAD. ZAPRTI IN NAKLONI. To je novo pogovorno okno UVOZ. Včasih je pisalo LOAD TO, vendar ga želim naložiti v mizo na OBSTOJEČEM DELOVNEM LISTU. Kliknite gumb strni. Izbral bom C1, odstranil, kliknil V redu in gremo. Power poizvedba za čiščenje naših podatkov in pridobitev samo tistih podatkov, ki jih želimo. Vredu. Vrgel ga bom nazaj.
Bill: Tu je bistvo, NAJBOLJ DELIMER V RAZDELLJENEM STOLPCU Z DELIMITERJEM, ena izmed najboljših funkcij v poizvedbi o napajanju. To je super.
Vredu. Moja reakcija trzanja v kolenu - VBA UDF (nerazumljivo - 05:34) je zelo enostavna za izvedbo VBA. Preklopite na ALT + F11. VSTAVITE MODUL. V ta modul vnesite to kodo. Bom (ustvaril - 05:43) popolnoma novo funkcijo, poimenoval jo bom MIDPART, in dal ji bom nekaj besedila, potem pa bom bo šel od zadnjega znaka v tej celici od dolžine MYTEXT nazaj do 1, KORAK -1 in si ogledal ta znak. Torej, MID MYTEXT-a, ta spremenljivka i, nam pove, kateri znak iščemo za dolžino 1. Ali je -? Takoj, ko najdem -, grem LEVO MYTEXT-a, začenši z znakom i - 1, zato se znebim vsega do zadnjega - do konca, nato pa se prepričajte, da ne grem še naprej iščite več pomišljajev, EXIT FOR me bo rešil iz te (nerazumljive - 06:17) zanke,in od tam je enostaven del. Vzeli bomo samo MYTEXT, začnite pri MID MYTEXT, (kjer uporabljam - 06:26), s pomočjo funkcije FIND poiščite prvo -, pojdite 1 več kot to in jo vrnite nazaj.
Torej, vrnimo se nazaj, ALT + Q, da se vrnemo v Excel. = Zavihek MIDPART tega in zdi se, da deluje. Kopiraj to. Mike, imaš še enega? (= Del MID (A2))
Mike: No, imam še eno, toda to bo ena dolga formula - ne tako kratka kot tista UDF. V redu, pojdimo na naslednji list. Zdaj, če bomo naredili formulo in imamo nekaj besedila, vedno pa je različno število ločil, moram nekako dobiti položaj tega zadnjega ločila.
To bo trajalo nekaj korakov, vendar bom začel s funkcijo SUBSTITUTE. Bom pregledal to besedilo,, staro besedilo, ki ga želim najti, je ", to -, in kaj želim postaviti na njegovo mesto ali nadomestiti? »«. To ne bo nič. Zdaj, če I) in CONTROL + ENTER, kaj bo to storilo? (= NAMESTITEV (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.
No, tu ste. Vsem se želim zahvaliti, da so se ustavili. Naslednjič se vidimo za še en podcast Duel Excel od in ExcelIsFun.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Duel185.xlsm
