Czy funkcja WYSZUKAJ.PIONOWO w programie Excel może zwrócić wszystkie wyniki i połączyć je przecinkiem między nimi?
Obejrzyj wideo
- Celem jest połączenie wszystkich odpowiedzi tekstowych z WYSZUKAJ.PIONOWO
- Metoda Billa: użyj funkcji VBA o nazwie GetAll
- Unikalna lista za pomocą opcji Usuń duplikaty
- Metoda Mike'a:
- Unikalna lista za pomocą filtru zaawansowanego
- Dodano funkcję TEXTJOIN w Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Ze względu na funkcję JEŻELI formuła wymaga kombinacji klawiszy Ctrl + Shift + Enter za każdym razem, gdy edytujesz formułę
- Alt AQOR Enter ponownie uruchomi filtr zaawansowany!
Transkrypcja wideo
Odcinek 183: Dołącz do wszystkich dopasowań VLOOKUP
Bill Jelen: Hej, witaj z powrotem. Czas na kolejny podcast Dueling Excel. Jestem Bill Jelen z, dołączy do mnie Mike Girvin w programie Excel Is Fun. To jest nasz Odcinek 183: Dołącz do wszystkich dopasowań WYSZUKAJ.PIONOWO.
(Muzyka)
Dobrze, dzisiejsze pytanie od Matta. Czy WYSZUKAJ.PIONOWO może zwrócić wszystkie wyniki i połączyć je ze spacją przecinkiem między nimi. Na przykład 109876, które są tutaj tymi dwoma, czy może zwrócić przecinek Niski poziom oleju. Sprawdzono 12/12. I oczywiście, gdyby było ich więcej, zwróciłoby więcej. W porządku, więc moje rozwiązanie będzie używać języka VBA. W porządku, więc upewnij się, że jest zapisany jako xlsm lub nie możesz uruchomić VBA lub xlsb, ale nie xlsx - xlsx to jedyny plik, na którym nie można uruchomić VBA. Naciśniemy Alt + F11, upewnij się, że korzystasz z Dual183 lub jakiejkolwiek innej nazwy twojego skoroszytu. Wstaw moduł do pustego modułu, a my wkleimy ten kod, dobrze.
Przyjrzyjmy się tej funkcji GetAll, a oto numer identyfikacyjny, którego szukamy, a następnie zakres, który chcemy sprawdzić. Zaczynamy, zwrócimy zmienną o nazwie GetAll, więc zaczynamy od tego, że jest równe pustemu miejscu. Dla każdej komórki w moim zakresie, jeśli szukamy wartości komórki, weźmiemy GetAll = GetAll & „”, a następnie Cell.Offset (0 wierszy, 1 kolumna), innymi słowy wartość to jest tuż obok tego numeru identyfikacyjnego, ponieważ w VBA jest to numer identyfikacyjny. Jeśli znajdziemy pasujący numer identyfikacyjny, chcemy przejść o 1 kolumnę. A co by było, gdybyś chciał przejść o 2 kolumny lub o 3 kolumny, a więc zmień to 0 wierszy i 1 kolumnę na 2. W porządku, sprawdź też, czy - nie wstawiamy spacji przecinkiem, jeśli tak jest pierwszy.Więc jeśli zmienna GetAll ma obecnie wartość „”, to nie będziemy wstawiać spacji przecinkiem, dobrze?
Więc teraz, gdy mamy tutaj tę funkcję, zobacz, jak łatwo jest to rozwiązać problem Matta. Przyjdziemy tutaj i weźmy jego identyfikatory, Ctrl + C i wklejamy Ctrl + V w ten sposób. Dane, Usuń duplikaty, kliknij OK. Jest więc unikalna lista identyfikatorów, a następnie chcemy powiedzieć = getall i szukamy tej wartości w przecinku E2. Przeglądając ten zakres tutaj, nacisnę F4. F4 działa jak zwykła funkcja. I znowu odsuwając pytanie Matta, kliknij dwukrotnie, aby to zestrzelić. To będzie działać.
Spróbujmy, spróbujmy tutaj czegoś szalonego. Zróbmy Frazę 1 i po prostu umieśćmy kilka z nich, jak frazy od 1 do 10. Podpisujemy je pod numerem 109999. Wklej, a następnie wklej tutaj. Skopiuj tę formułę, edytuj formułę, aby oczywiście przeszła na sam koniec. Tak. I zwróci wszystkie te frazy. W porządku, więc to moje rozwiązanie, VBA, tam mała funkcja. Mike, zobaczmy, co masz.
Mike Girvin: Dzięki. GetAll, to niesamowita funkcja VBA. W porządku, przejdę do arkusza tutaj. Przekonwertowałem go już na tabelę Excela, więc mam nadzieję, że kiedy dodamy rekordy poniżej, wszystko się zaktualizuje.
Teraz pierwszą rzeczą, którą zamierzam zrobić w dwóch częściach. Mógłbym tutaj zrobić formułę wyodrębniania unikalnej listy, ale chcę spojrzeć na inną opcję: Filtr zaawansowany ma opcję wyodrębniania unikalnej listy i można ją zaktualizować. Podświetlę tylko dane kolumny ID, przejdź do filtru zaawansowanego lub użyję klawisza Alt, A, Q. Teraz lista filtrów na miejscu, nie ma mowy. Chcę go skopiować w inne miejsce. Otrzymał tylko kolumnę A i ponieważ jest to tabela programu Excel, która zostanie później rozszerzona. Nie mam żadnych kryteriów, chcę skopiować to do D1 i zaznaczyć tylko unikalne rekordy. Kliknij OK.
Teraz przejdę tutaj, wszystkie komentarze zostaną wprowadzone i zamierzam użyć funkcji, która działa tylko w programie Excel 2016 Office 365: = funkcja TEXTJOIN. Już sama ta funkcja jest warta zdobycia najnowszej wersji programu Excel. To tak powszechne zadanie, które ludzie chcą robić, łączyć wiele rzeczy razem. Teraz nasz separator w „,”, a wielką zaletą tej funkcji jest to, że możemy powiedzieć jej, aby ignorowała puste komórki. Teraz mogę ustawić PRAWDA, 1 lub Zostaw to, Pomiń. Więc zostawię to, pomiń. I tutaj potrzebujemy naszego tekstu. Zamierzamy użyć funkcji IF, aby odfiltrować i uzyskać tylko te elementy, które chcemy. Powiem, że przejrzyj całą tę kolumnę tutaj: Nazwa tabeli, a następnie w () Nazwa pola, czy ktoś z was = do tego względnego odwołania do komórki, to jest test logiczny. Gdybym miał to kliknąć i nacisnąć klawisz F9, aby ocenić,widać, że w tej chwili mamy tylko 2 TRUES, Ctrl + Z teraz wpisuję przecinek iz tablicą prawd i fałszów, teraz mogę dać jej elementy do wybrania. Więc teraz wybierzemy tylko te elementy, które mają tutaj wartość TRUE z tego zakresu. Przecinek i chcę się upewnić, że wstawiono „” - to pojawi się jako pusta komórka w odniesieniu do drugiego argumentu w TEXTJOIN.
Teraz zamknę nawias, a teraz funkcja JEŻELI utworzy ten ciąg Prawd i fałszów, rzeczywiste elementy z tego zakresu zostaną pobrane, jeśli zobaczy, że jest to prawda, a wszystkie inne elementy będą miały tę pustą komórkę. I zgadnij co? TEXTJOIN całkowicie zignoruje wszystkie te puste komórki i zwróci tylko elementy pasujące do tego identyfikatora, a następnie połączy je z tym separatorem. Teraz jest to zdecydowanie formuła Array, która wymaga specjalnego naciśnięcia klawisza Ctrol + Shift + Enter. Logiczny argument testowy zawiera naszą operację Array i ten argument nie może poprawnie obliczyć tej operacji Array, chyba że użyjemy klawiatury Ctrl + Shift + Enter. Teraz zamknę nawiasy. Właściwie moglibyśmy udowodnić 1 tutaj w tekście 1, jeśli F9 to wszystko, moglibyśmy zobaczyć, że otrzymujemy 2 elementy, reszta tych pustych komórek zostanie zignorowana. Ctrl + Z. Teraz pozwól's wprowadź to do komórki za pomocą Ctrl + Shift + Enter. Natychmiast spójrz w górę na pasek formuły. Te nawiasy klamrowe to Excel, który mówi, że zrozumiał i obliczył to jako wzór tablicowy. Teraz mogę kliknąć dwukrotnie i wysłać. To dobrze wygląda.
Mam zamiar przejść do ostatniej komórki i nacisnąć klawisz F2, aby sprawdzić, czy wszystkie zakresy wyglądają poprawnie. Teraz, czego nie chcę, to nie chcę wciskać Enter, ponieważ ta formuła po umieszczeniu jej w trybie edycji będzie obliczać poprawnie tylko wtedy, gdy użyjemy Ctrl + Shift + Enter; lub, ponieważ już wprowadziliśmy formułę, możemy po prostu użyć klawisza Esc, aby powrócić do tego, co jest w komórce, zanim umieścimy ją w trybie edycji.
Teraz przetestujmy to. Zamierzam kliknąć ostatnią komórkę tutaj i nacisnąć Tab, a następnie wpisać nowy identyfikator, kartę, kartę. Kolejny nowy rekord, Tab, i już widzę, że nie miałem tu wystarczająco dużo pracy. Jestem, zamierzamy wstawić - Perfect, a następnie Enter. Teraz nie będzie się to automatycznie aktualizować, tak jak gdybyśmy mieli kilka formuł, w których liczymy unikalne przedmioty, a następnie wyodrębniamy unikalne przedmioty, ale nie ma problemu. Patrz na to. Możemy zaktualizować tę listę unikatowych rekordów, ponieważ użyliśmy filtru zaawansowanego i nie ma znaczenia, od której komórki zaczniesz, ponieważ po wywołaniu filtru zaawansowanego zapamiętuje zakres wyodrębniania i zakresy, na które pierwotnie patrzył. Możesz kliknąć Filtr zaawansowany lub użyć klawiatury Alt + A + Q. Musimy wybrać opcję Kopiuj do innej lokalizacji, ale spójrz na to.Całkowicie zapamiętany i rozszerzony do A13 dzięki funkcji tabeli Excel. Zapamiętał zakres ekstraktu. Muszę tylko sprawdzić unikalne rekordy, ale kliknij OK.
Teraz muszę podejść i skopiować tę formułę. I gotowe, używając filtru zaawansowanego i niesamowitej funkcji TEXTJOIN z, w operacji Array, aby uzyskać tylko te elementy, które pasują. W porządku, wróć do.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Och, hej, chcę podziękować wszystkim za zatrzymanie się. Do zobaczenia następnym razem w kolejnym podcastie Dueling Excel od i Excel Is Fun.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Duel183.xlsm
