Dzisiaj jest szalone pytanie. Masz kolumnę z numerami części. Numer części zawiera od 4 do 7 myślników. Chcesz wyodrębnić tylko część numeru części po pierwszym myślniku i do ostatniego myślnika, ale nie uwzględniając. To jest pojedynkowy odcinek programu Excel.
Obejrzyj wideo
- Celem jest znalezienie pierwszego i ostatniego kreski i zachowanie wszystkiego pomiędzy
- Najtrudniejsze jest znalezienie ostatniej kreski
- Metoda rachunku 1: Wypełnienie błyskawiczne
- Wpisz ręcznie kilka pierwszych (w tym niektóre z różną liczbą kresek)
- Wybierz pustą komórkę poniżej
- Ctrl + E do Flash Fill
- Metoda Mike'a 2:
- Użyj dodatku Power Query
- W programie Excel 2016 dodatek Power Query znajduje się w grupie Pobieranie i przekształcanie w programie Excel 2016
- W programach Excel 2010 i 2013 pobierz dodatek Power Query firmy Microsoft. Tworzy nową kartę Power Query na Wstążce
- Przekonwertuj dane na tabelę za pomocą Ctrl + T
- Użyj opcji Podziel dane w dodatku Power Query - najpierw podziel na kreskę znajdującą się najbardziej po lewej, a następnie podziel na kreskę znajdującą się najbardziej po prawej stronie
- Metoda płatności 3:
- Funkcja VBA, która wykonuje iterację od końca komórki wstecz, aby znaleźć ostatnią myślnik
- Metoda Mike'a 4:
- Użyj SUBSTITUTE, aby znaleźć położenie n-tego myślnika
- SUBSTITUTE to jedyna funkcja tekstowa, która umożliwia określenie numeru instancji
- Aby znaleźć numer instancji, użyj
=LEN(A2)-LEN(SUBSTITUTE)
Transkrypcja wideo
Bill: Hej. Witamy spowrotem. Czas na kolejny podcast Dueling Excel. Jestem Bill Jelen z MrExcel. (Dołączy do mnie Mike Girvin z ExcelIsFun. To jest nasz - 00:03) odcinek 185: fragment od pierwszego - do ostatniego -.
W porządku. Dzisiejsze pytanie zostało wysłane przez firmę Anvar na YouTube. Jak mogę wyodrębnić wszystko od pierwszego - do ostatniego - i sprawdzić te dane, które ma tutaj. Jest ogromna liczba myślników, od 3, 5, 6, 7 myślników, dobrze?
Tak więc moja pierwsza myśl brzmi: cóż, hej, naprawdę łatwo jest znaleźć pierwszą - prawda? = w lewo lub = ŚRODEK ZNAJDU na A2, a następnie -, +1 w porządku, ale aby dojść do ostatniego -, to sprawi, że boli mnie głowa, prawda, ponieważ, no cóż, ile mamy kresek? Moglibyśmy użyć SUBSTYTUTU A2, zastępując myślniki, i porównać długość tego z oryginalną długością. To mówi mi o liczbie myślników, ale teraz wiem, które - aby znaleźć, 2, 3, 4, 5, ale czy używam FIND?
Byłem gotowy, aby przejść do VBA, prawda? To moja odruchowa reakcja. Powiedziałem, poczekaj chwilę. Powiedziałem: Anvar, w której wersji programu Excel jesteś? Mówi, jestem w programie Excel 2016. Powiedziałem, że to piękne. Jeśli korzystasz z programu Excel 2013 lub nowszego, możemy skorzystać z tej wspaniałej nowej funkcji o nazwie flash fill. W przypadku wypełnienia błyskawicznego musimy po prostu nadać mu wzór i zamierzam nadać mu wystarczającą ilość wzoru, więc nie chodzi tylko o to, że biorę jeden z dwoma kreskami i robię to kilka razy. Chcę się upewnić, że mam w ten sposób kilka różnych myślników. Chad z zespołu Excela wie, czego szukam. Chad to facet, który napisał logikę do flashowania. Tak więc mam tam około 3 z nich, a następnie CONTROL + E to skrót do używania DATA, a następnie FLASH FILL i, oczywiście, wygląda na to, że zrobił to dobrze. W porządku, Mike.Zobaczmy, co masz.
Mike: Dzięki, MrExcel. Tak. Błyskawiczne wypełnienie wygrywa. Ta funkcja, flash fill, jest jednym z nowoczesnych narzędzi programu Excel, które jest po prostu niesamowite. Jeśli to jednorazowa transakcja i masz spójny wzorzec, hej, tak bym to zrobił.
Hej, przejdźmy do następnego arkusza. Teraz zamiast używać funkcji flash fill, możemy faktycznie użyć zapytania power query. Teraz używam programu Excel 2016, więc mam grupę GET & TRANSFORM. To jest pytanie o moc. We wcześniejszych wersjach, 2013 (do 10 - 2:30), faktycznie musisz pobrać darmowy dodatek Power Query.
Teraz, aby funkcja power query działała, musi zostać przekonwertowana na tabelę programu Excel. Teraz znowu użyłbym funkcji flash fill, gdyby była to jednorazowa umowa. Kiedy użyjesz zapytania Power? Cóż, gdybyś miał naprawdę duże zbiory danych lub pochodził z zewnętrznego źródła, byłby to właściwy sposób, a może nawet spodoba ci się to lepiej niż wpisywanie 3 lub 4 przykładów dla wypełniania flashowego, ponieważ dzięki zapytaniu mocy konkretnie powiedz znajdź pierwszy - i znajdź ostatni -.
Teraz zamierzam przekonwertować to na tabelę programu Excel. Mam wybraną jedną komórkę, dookoła puste komórki. Idę do INSERT, TABLE, lub używasz klawiatury, CONTROL + T. Mogę kliknąć OK lub ENTER. Chcę nazwać tę tabelę, więc przejdę do TABLE TOOLS, DESIGN, do WŁAŚCIWOŚCI. Nazwę to STARTKEYTABLE i ENTER. Teraz mogę wrócić do DANYCH, wprowadzić je do zapytania zasilania za pomocą przycisku FROM TABLE. Jest moja kolumna. Jest to imię. Nie chcę zachować tej nazwy, ponieważ wynik zostanie wyeksportowany do programu Excel i chcę nadać mu inną nazwę. Więc nazwę to CLEANEDKEYTABLE. Nie potrzebuję tego ZMIENIONEGO TYPU. Patrzę tylko na źródło. Teraz mogę kliknąć kolumnę i zaraz w HOME jest przycisk SPLIT. Mogę powiedzieć SPLIT, BY DELIMITER. Wygląda na to, że już zgadłem. JA'powiem LEFT-MOST. Kliknij OK.
Teraz, jeśli spojrzę tutaj, zobaczę ZMIENIONY TYP. Nie potrzebuję tego, więc zamierzam pozbyć się tego kroku. Mam tylko SPLIT COLUMN BY DELIMITER. Teraz zrobię to ponownie, ale zamiast używać przycisku SPLIT tutaj, kliknij prawym przyciskiem myszy, aby PODZIELIĆ KOLUMNĘ, PRZEZ DELIMITER i spójrz na to. Możemy zdecydować się na podzielenie go przez OGRANICZNIK W PRAWO. Kliknij OK. Teraz nie potrzebuję tych dwóch kolumn, więc mam zamiar kliknąć prawym przyciskiem kolumnę, którą chcę zachować, USUŃ INNE KOLUMNY. Właściwie mam zamiar X tego ZMIENIONEGO TYPU. Pojawi się informacja CZY NA PEWNO CHCESZ TO USUNĄĆ? Powiem tak, USUŃ. Mam czyste dane.
Teraz mogę podejść do CLOSE & LOAD. ZAMKNIJ I ZAŁADUJ DO. To jest nowe okno dialogowe IMPORTUJ. Kiedyś było napisane LOAD TO, ale chcę załadować to do tabeli, w ISTNIEJĄCYM ARKUSZU. Kliknij przycisk zwijania. Mam zamiar wybrać C1, rozwinąć zwijanie, kliknąć OK i gotowe. Power query, aby wyczyścić nasze dane i uzyskać tylko te, których potrzebujemy. W porządku. Wrócę do.
Bill: Tutaj jest punkt, PRAWY NAJBARDZIEJ DELIMITER w SPLIT COLUMN BY DELIMITER, jedna z fajnych funkcji w Power Query. To cudownie.
W porządku. Moja odruchowa reakcja - VBA UDF (niezrozumiałe - 05:34) naprawdę łatwe do zrobienia VBA. Przełącz na ALT + F11. WŁÓŻ MODUŁ. W tym module wpisz ten kod. Zamierzam (stworzyć - 05:43) zupełnie nową funkcję, nazwij ją MIDPART i przekażę mu jakiś tekst, a potem zamierzam zrobić przechodzę od ostatniego znaku w tej komórce od długości MYTEKST do 1, KROK -1 i spójrz na ten znak. Tak więc MID MYTEXT, ta zmienna i, mówi nam, którego znaku szukamy dla długości 1. Czy to jest -? Jak tylko znajdę -, zamierzam skręcić w LEWO od MYTEXT, zaczynając od znaku i - 1, więc pozbywam się wszystkiego na koniec - do końca, a potem upewniam się, że nie idę szukaj dalej kresek, EXIT FOR wyciągnie mnie z tej (niezrozumiałej - 06:17) pętli,a stamtąd jest łatwa część. Po prostu weźmiemy MATEKST, zacznijmy od środka MYTEKSTU (gdzie używam - 06:26) użyj funkcji ZNAJDŹ, aby znaleźć pierwszy -, przejdź o 1 więcej i zwróć to z powrotem.
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.
Cóż, proszę bardzo. Chcę podziękować wszystkim za zatrzymanie się. Do zobaczenia następnym razem w kolejnym podcastie Dueling Excel od i ExcelIsFun.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Duel185.xlsm
