Dnes je šílená otázka. Máte sloupec čísel dílů. V čísle dílu je kdekoli od 4 do 7 pomlček. Chcete extrahovat pouze část čísla dílu po první pomlčce a až po poslední pomlčku, ale bez ní. Toto je soubojová Excel epizoda.
Sledovat video
- Cílem je najít první a poslední pomlčku a udržet vše mezi nimi
- Nejtěžší částí je najít poslední pomlčku
- Bill Metoda 1: Flash Fill
- Ručně vyplňte prvních pár (včetně některých s různým počtem pomlček)
- Vyberte prázdnou buňku pod tím
- Ctrl + E do Flash Fill
- Mike Metoda 2:
- Použijte Power Query
- V aplikaci Excel 2016 je Power Query ve skupině Získat a transformovat v aplikaci Excel 2016
- V aplikaci Excel 2010 a 2013 si stáhněte Power Query od společnosti Microsoft. Vytvoří novou kartu Power Query na pásu karet
- Převeďte svá data do tabulky pomocí Ctrl + T
- Použijte Rozdělit data v Power Query - nejprve se rozdělte na pomlčku úplně vlevo, pak se rozdělte na pomlčku nejvíce vpravo
- Metoda vyúčtování 3:
- Funkce VBA, která iteruje od konce buňky dozadu, aby našla poslední pomlčku
- Mike Metoda 4:
- Pomocí SUBSTITUTE vyhledejte umístění N-té pomlčky
- SUBSTITUTE je jediná textová funkce, která umožňuje zadat číslo instance
- Chcete-li zjistit, které číslo instance, použijte
=LEN(A2)-LEN(SUBSTITUTE)
Přepis videa
Bill: Hej. Vítej zpět. Je čas na další podcast Dueling Excel. Jsem Bill Jelen z MrExcel. (Přidá se ke mně Mike Girvin z ExcelIsFun. Toto je naše - 00:03) epizoda 185: výňatek z první - do poslední -.
V pořádku. Dnešní otázku zasílá Anvar na YouTube. Jak mohu extrahovat vše od prvního - po poslední - a zkontrolovat tato data, která zde má. Existuje obrovské množství pomlček, kdekoli od 3, 5, 6, 7 pomlček, dobře?
Moje první myšlenka je, no, hej, je opravdu snadné najít první -, že? = vlevo nebo = MID Z HLEDÁNÍ A2 a poté -, +1 v pořádku, ale abych se dostal k poslednímu -, bude mě bolet hlava, že, protože, no, kolik pomlček máme? Mohli bychom vzít SUBSTITUT A2, nahradit pomlčky a porovnat jeho délku, původní délku. To mi říká počet pomlček, ale teď vím, které - najít 2., 3., 4., 5., ale použiji FIND?
Byl jsem připraven jít do VBA, že? To je moje reakce. Řekl jsem, počkej chvíli. Řekl jsem, Anvare, v jaké verzi Excelu jsi? Říká, že jsem v aplikaci Excel 2016. Řekl jsem, to je krásné. Pokud používáte Excel 2013 nebo novější, mohli bychom použít tuto skvělou novou funkci nazvanou flash fill. S bleskovou výplní mu musíme dát vzor a já mu dám dostatek vzoru, takže nejde jen o to, že si vezmu jednu se dvěma čárkami a udělám to několikrát. Chci se ujistit, že mám takto několik různých pomlček. Chad z týmu Excel ví, co hledám. Chad je člověk, který napsal logiku pro bleskovou výplň. Takže tam mám asi 3 z nich a pak je CONTROL + E zkratka pro použití DATA a pak FLASH FILL a určitě to vypadá, že udělal správnou věc. Dobře, Mike.Uvidíme, co máte.
Mike: Díky, pane Excel. To jo. Flash fill vyhrává. Tato funkce, flash fill, je jedním z moderních nástrojů Excel, který je prostě úžasný. Pokud je to jednorázová dohoda a máte konzistentní vzor, hej, tak bych to udělal.
Hej, pojďme k dalšímu listu. Nyní, místo použití flash fill, můžeme skutečně použít power query. Teď používám Excel 2016, takže mám skupinu GET & TRANSFORM. To je dotaz na sílu. V dřívějších verzích 2013 (do 10 - 2:30) je ve skutečnosti nutné stáhnout doplněk bezplatného dotazu na energii.
Nyní, aby fungoval energetický dotaz, je třeba jej převést na tabulku Excel. Opět bych použil bleskovou výplň, pokud se jednalo o jednorázovou dohodu. Kdy byste použili power query? Pokud jste měli opravdu velká data nebo přicházeli z externího zdroje, bylo by to způsob, jak jít, nebo by se vám to mohlo líbit lépe, než kdybyste museli psát 3 nebo 4 příklady pro flash fill, protože s power query můžeme konkrétně řekněte najít první - a najít poslední -.
Nyní to převedu na tabulku aplikace Excel. Mám vybranou jedinou buňku, prázdné buňky po celém obvodu. Jdu do INSERT, TABLE, nebo použijete klávesnici, CONTROL + T. Mohu kliknout na OK nebo ENTER. Chci pojmenovat tuto tabulku, takže přejdu na TABULOVÉ NÁSTROJE, DESIGN, nahoru na VLASTNOSTI. Zavolám tomu STARTKEYTABLE a ENTER. Nyní se mohu vrátit k DATA, přivést jej do dotazu napájení pomocí tlačítka FROM TABLE. Tady je můj sloup. Je to jméno. Nechci si tento název ponechat, protože výstup bude exportován do Excelu a chci mu dát jiný název. Takže to nazvu CLEANEDKEYTABLE. Ten ZMĚNĚNÝ TYP nepotřebuji. Jen se dívám na zdroj. Teď můžu kliknout na sloupec a hned v HOME je tlačítko SPLIT. Mohu říci SPLIT, ODDĚLOVAČEM. Vypadá to, že to už bylo uhodnuto. Jářeknu LEVĚ NEJVĚTŠÍ. Klikněte na OK.
Nyní, když se podívám sem, vidím ZMĚNĚNÝ TYP. To nepotřebuji, takže se toho kroku zbavím. Mám pouze SPLIT COLUMN BY DELIMITER. Teď to udělám znovu, ale místo použití tlačítka SPLIT zde, klikněte pravým tlačítkem dolů na SPLIT COLUMN, BY DELIMITER, a podívejte se na to. Můžeme se rozhodnout jej rozdělit podle ODMĚNU VPRAVO. Klikněte na OK. Nyní tyto dva sloupce nepotřebuji, takže kliknu pravým tlačítkem na sloupec, který si chci ponechat, ODSTRANIT DALŠÍ SLOUPCE. Vlastně se chystám X tento ZMĚNĚNÝ TYP vydat. Bude to říkat JISTĚ SI, ŽE TO CHCETE VYMAZAT? Řeknu, ano, ODSTRANIT. Tady jsou moje čistá data.
Teď můžu přijít na CLOSE & LOAD. ZAVŘÍT A ZATÍŽIT. Toto je nové dialogové okno IMPORT. Říkalo se LOAD TO, ale chci to načíst do tabulky, na EXISTUJÍCÍM PRACOVNÍM LISTU. Klikněte na tlačítko sbalit. Vyberu C1, rozbalím, kliknu na OK a je to. Energetický dotaz k vyčištění našich dat a získání pouze těch dat, která chceme. V pořádku. Hodím to zpět.
Bill: Právě tam je bod, SPRÁVNĚ ODDĚLOVAČ ve SPLIT COLUMN BY DELIMITER, jedna z nejlepších funkcí v dotazu na sílu. To je úžasné.
V pořádku. Moje reakce na kolena - VBA UDF (nesrozumitelný - 05:34), opravdu snadné dělat VBA. Přepněte na ALT + F11. VLOŽTE MODUL. V tomto modulu zadejte tento kód. Chystám se (vytvořit - 05:43) zbrusu nové funkce, budu jí říkat MIDPART a předám jí nějaký text, a pak to, co udělám, je, že přejdeme od posledního znaku v této buňce z délky MYTEXT zpět na 1, KROK -1 a podíváme se na tento znak. Takže MID of MYTEXT, tato proměnná i, nám říká, na který znak se díváme na délku 1. Je to -? Jakmile najdu -, vezmu VLEVO MYTEXTU počínaje znakem i - 1, takže se toho posledního zbavím všeho - úplně ven, a pak se ujistěte, že nejdu hledejte další pomlčky, EXIT FOR mě dostane z této (nesrozumitelné - 06:17) smyčky,a odtud je snadná část. Prostě vezmeme MYTEXT, začneme uprostřed MYTEXTU, (kde používám - 06:26), pomocí funkce FIND najdu první -, přejdeme o 1 víc a vrátíme to zpět.
Pojďme se tedy vrátit ALT + Q a vrátit se do Excelu. = Karta MIDPART a vypadá to, že to funguje. Zkopírujte to dolů. Miku, máš ještě jednu? (= MIDPart (A2))
Mike: No, mám ještě jeden, ale bude to jeden dlouhý vzorec - ne tak krátký jako ten UDF. Dobře, pojďme k dalšímu listu. Nyní, pokud budeme dělat vzorec a máme nějaký text a vždy existuje jiný počet oddělovačů, nějak musím zjistit pozici posledního oddělovače.
Nyní to bude trvat několik kroků, ale začnu s funkcí SUBSTITUTE. Podívám se skrz ten text, „starý text, který chci najít, je v“, to -,, a co chci dát na jeho místo nebo nahradit? "". To nic nevloží. Teď, když já) a CONTROL + ENTER, co to udělá? (= NÁHRADA (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, tady to máš. Chci poděkovat všem, že se zastavili. Uvidíme se příště pro další podcast Dueling Excel od a ExcelIsFun.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Duel185.xlsm
