Může Excel VLOOKUP vrátit všechny výsledky a spojit je s čárkou?
Sledovat video
- Cílem je zřetězit všechny textové odpovědi ze SVYHLEDÁNÍ
- Billova metoda: Použijte funkci VBA nazvanou GetAll
- Jedinečný seznam pomocí Odebrat duplikáty
- Mikeova metoda:
- Jedinečný seznam pomocí pokročilého filtru
- Přidána funkce TEXTJOIN v Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Kvůli funkci IF vyžaduje vzorec Ctrl + Shift + Enter při každé úpravě vzorce
- Alt AQOR Enter znovu spustí Pokročilý filtr!
Přepis videa
Epizoda 183: Připojte se ke všem zápasům VLOOKUP
Bill Jelen: Hej, vítej zpět. Je čas na další podcast Dueling Excel. Jsem Bill Jelen a přidám se k němu Mike Girvin pro Excel Is Fun. Toto je naše epizoda 183: Připojte se ke všem zápasům VLOOKUP.
(Hudba)
Dobře, dnešní otázka od Matta. Může VLOOKUP vrátit všechny výsledky a spojit je s čárkou mezi nimi. Například 109876, což jsou tyto dva, může vracet mezeru čárky Low Oil Zkontrolováno 12/12. A samozřejmě, kdyby jich bylo více, vrátilo by se to víc. Dobře, takže moje řešení zde bude používat nějaký VBA. Dobře, takže se ujistěte, že je uložen jako xlsm, nebo nemůžete spustit VBA nebo xlsb, ale ne xlsx - xlsx je jediný soubor, který nemůže spustit VBA. Stiskněte Alt + F11, ujistěte se, že používáte Dual183 nebo jakýkoli název vašeho sešitu. Vložte modul do prázdného modulu a my tento kód vložíme, dobře.
Pojďme se podívat na tuto funkci GetAll a tady je ID číslo, které hledáme, a pak rozsah, který chceme hledat. A začneme, vrátíme proměnnou nazvanou GetAll, takže začneme s tím, že se bude rovnat prázdné prázdné. Pro každou buňku v mém rozsahu, pokud je hodnota buňky to, co hledáme, vezmeme GetAll = GetAll & “” a pak Cell.Offset (0 řádků, 1 sloupec), jinými slovy hodnota to je hned vedle toho ID čísla, protože zpět ve VBA, tady je ID číslo. Pokud najdeme odpovídající identifikační číslo, chceme přejít o 1 sloupec. A teď, co kdybyste chtěli přejít 2 sloupce nebo 3 sloupce, pak změníte tento 0 řádků a 1 sloupec na 2. Dobře, také zkontrolujte, zda - nedáváme čárku, pokud je to první.Pokud je tedy proměnná GetAll aktuálně „“, nebudeme vkládat čárku, dobře?
Takže teď, když tu máme tuto funkci, sledujte, jak snadné je vyřešit Mattův problém. Pojdeme sem a vezmeme jeho ID, Ctrl + C a takto vložíme Ctrl + V. Data, Odebrat duplikáty, klikněte na OK. Existuje tedy jedinečný seznam ID a pak chceme říct = getall a hledáme tuto hodnotu v čárce E2. Když se podívám na tento rozsah, stisknu F4. F4 funguje stejně jako běžná funkce. A opět přesuňte Mattovu otázku z cesty, dvojitým kliknutím ji sestřelte. Bude to fungovat.
A zkusme to, zkusme zde něco šíleného. Pojďme udělat frázi 1 a jen je dejte spoustu, jako je fráze 1 až 10. Všechny tyto podepíšeme na 109999. Vložit a vložit sem. Zkopírujte tento vzorec dolů, upravte vzorec tak, aby šel úplně dolů, samozřejmě. Jo. A vrátí všechny tyto fráze. Dobře, takže to je moje řešení, VBA, malá funkce. Mike, podívejme se, co máš.
Mike Girvin: Díky ,. GetAll, to je skvělá funkce VBA. Dobře, přejdu k listu přímo tady. Již jsem ji převedl na tabulku Excel, takže když přidáme níže uvedené záznamy, doufejme, že se věci aktualizují.
První věc, kterou teď udělám ve dvou částech. Mohl bych zde udělat vzorec pro extrahování jedinečného seznamu, ale chci se podívat na jinou možnost: Pokročilý filtr má možnost extrahovat jedinečný seznam a lze jej aktualizovat. Zvýrazním pouze data sloupce ID, přes Rozšířený filtr nebo použiji klávesnici Alt, A, Q. Nyní seznam filtrů na místě, v žádném případě. Chci jej zkopírovat na jiné místo. Má pouze sloupec A a protože je to tabulka aplikace Excel, která se později rozbalí. Nemám žádná kritéria, chci to zkopírovat do D1 a zkontrolovat pouze jedinečné záznamy. Klikněte na OK.
Teď přijdu sem, vstoupí všechny komentáře a budu používat funkci, která funguje pouze v aplikaci Excel 2016 Office 365: = funkce TEXTJOIN. Samotná tato funkce stojí za to získat nejnovější verzi aplikace Excel. Toto je tak běžný úkol, který lidé chtějí dělat, spojit mnoho věcí dohromady. Nyní je náš oddělovač v „,“ a skvělé na této funkci je, že jí můžeme říci, že má ignorovat prázdné buňky. Teď můžu dát PRAVDA, 1 nebo Nechat to, Vynechat to. Takže to nechám, vynechám to. A tady potřebujeme náš text. Použijeme funkci IF k odfiltrování a získání pouze položek, které chceme. Řeknu zde prohlédnout celý tento sloupec: Název tabulky a poté v () Název pole, jsou kdokoli z vás = k tomuto relativnímu odkazu na buňku, to je logický test. Pokud bych měl kliknout na toto a stisknout klávesu F9 k vyhodnocení,teď můžete vidět, že máme pouze 2 TRUES, Ctrl + Z teď píšu čárku a s řadou Trues a Falses, teď mu mohu dát položky k vyzvednutí. Takže teď z tohoto rozsahu vybereme pouze položky, které mají PRAVDU. Čárka a chci se ujistit, že jsme vložili „“ - to se ukáže jako prázdná buňka, pokud jde o druhý argument v TEXTJOIN.
Teď se chystám zavřít závorky a nyní funkce IF vytvoří ten řetězec Trues a Falses, skutečné položky z tohoto rozsahu se vyzvednou, pokud to vidí True a všechny ostatní položky budou mít tuto prázdnou buňku. A Hádej co? TEXTJOIN zcela ignoruje všechny tyto prázdné buňky a vrátí pouze položky, které odpovídají tomuto ID, a poté jej spojí s tímto oddělovačem. Nyní je to rozhodně vzorec pole, který vyžaduje speciální stisk klávesy Ctrol + Shift + Enter. Argument logického testu drží naši operaci Array a tento argument nemůže správně vypočítat tuto operaci Array, pokud nepoužíváme klávesnici Ctrl + Shift + Enter. Nyní uzavřu závorky. Ve skutečnosti bychom mohli dokázat 1 právě tady v Textu 1, kdybych to všechno F9, mohli bychom vidět, že dostaneme 2 položky, zbytek těchto prázdných buněk bude ignorován. Ctrl + Z. Nyní, pojďmes zadejte to do buňky pomocí Ctrl + Shift + Enter. Okamžitě vyhledejte lištu vzorců. Tyto složené závorky vám Excel říká, že to pochopil a vypočítal to jako vzorec pole. Nyní mohu dvakrát kliknout a odeslat jej dolů. To vypadá dobře.
Jdu do poslední buňky a stisknu F2, abych ověřil, že všechny rozsahy vypadají správně. To, co teď nechci udělat, je, že nechci stisknout Enter, protože tento vzorec poté, co jsme jej vložili do režimu úprav, bude správně počítat, pouze pokud použijeme Ctrl + Shift + Enter; nebo protože jsme již zadali vzorec, můžeme použít klávesu Esc k návratu zpět na cokoli v buňce, než ji vložíme do režimu úprav.
Pojďme to otestovat. Kliknu do poslední buňky dole a stisknu Tab a potom zadám nové ID, Tab, Tab. Další nová deska, Tab, a už vidím, že jsem tady neměl dost práce. Já jsem, řekneme - Perfektní a pak Enter. Nyní se to nebude automaticky aktualizovat, jako kdybychom měli spoustu vzorců, které počítáme jedinečné položky a poté extrahujeme jedinečné položky, ale žádný problém. Sleduj tohle. Tento seznam jedinečných záznamů můžeme aktualizovat, protože jsme použili Rozšířený filtr a nezáleží na tom, ze které buňky začnete, protože když je vyvolán Rozšířený filtr, zapamatuje si rozsah extraktu a rozsahy, na které se původně díval. Můžete kliknout na Pokročilý filtr nebo použít klávesovou zkratku Alt + A + Q. Musíme vybrat Kopírovat do jiného umístění, ale podívejte se na to.Úplně si to pamatovalo a rozšířilo se na A13 díky funkci tabulky Excel. Pamatovalo si to rozsah extraktu. Musím zkontrolovat pouze jedinečné záznamy, ale kliknout na OK.
Teď musím přijít a zkopírovat tento vzorec. A pomocí funkce Advanced Filter a úžasné funkce TEXTJOIN můžete v operaci Array získat pouze položky, které odpovídají. Dobře, vrať se zpět.
Bill Jelen: Hej, Mike, to je úžasné. Dobře, shrnuji tuto epizodu. Použil jsem funkci VBA nazvanou GetAll a můj jedinečný seznam byl vytvořen pomocí Remove Duplicates, což je mnohem jednodušší než Advanced Filter, ale problém je, že je to jednorázová věc. Nepamatuje si předchozí nastavení. Mike vytvořil svůj jedinečný seznam pomocí Rozšířeného filtru, což znamená, že by mohl tento Rozšířený filtr později znovu provést, aniž by znovu určil vstupní rozsah a rozsah extrahování. A pak TEXTJOIN, krásná nová funkce, přidala Office 365. Mike říká, že sám o sobě je důvodem k získání nejnovější Office. Řekl jsem, že TEXTJOIN změní život. TEXTJOIN je úžasný, protože zvládne pole.
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.
Ach, chci poděkovat všem, že se zastavili. Uvidíme se příště pro další podcast Dueling Excel od a Excel Is Fun.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Duel183.xlsm
