Môže program Excel VLOOKUP vrátiť všetky výsledky a spojiť ich s čiarkou?
Pozeraj video
- Cieľom je spojiť všetky textové odpovede z VYHĽADÁVANIA
- Billova metóda: Použite funkciu VBA s názvom GetAll
- Jedinečný zoznam pomocou funkcie Odstrániť duplikáty
- Mikeova metóda:
- Jedinečný zoznam pomocou rozšíreného filtra
- V Office 365 pridaná funkcia TEXTJOIN
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Kvôli funkcii IF vyžaduje vzorec pri každej úprave vzorca Ctrl + Shift + Enter
- Alt AQOR Enter znovu spustí Pokročilý filter!
Prepis videa
Epizóda 183: Pripojte sa ku všetkým zápasom VLOOKUP
Bill Jelen: Hej, vitaj späť. Je čas na ďalší podcast Dueling Excel. Ja som Bill Jelen, z ktorého ma doplní Mike Girvin za Excel Is Fun. Toto je naša epizóda 183: Pripojte sa ku všetkým zápasom VLOOKUP.
(Hudba)
Dobre, dnešná otázka od Matta. Môže funkcia VLOOKUP vrátiť všetky výsledky a spojiť ich s čiarkou medzi nimi. Napríklad 109876, ktoré sú tu tieto dva, môže vrátiť čiarku s nízkym obsahom oleja Začiarknuté 12/12. A samozrejme, keby ich bolo viac, viac by sa to vrátilo. Dobre, takže moje riešenie tu bude používať nejaký VBA. Dobre, takže sa uistite, či je uložený ako xlsm, alebo že nemôžete spustiť VBA alebo xlsb, ale nie xlsx - xlsx je jediný súbor, ktorý nemôže spustiť VBA. Stlačíme Alt + F11, aby sme sa uistili, že používate Dual183 alebo akýkoľvek názov vášho zošita. Vložte modul do prázdneho modulu a my tento kód vložíme, dobre.
Pozrime sa na túto funkciu GetAll a tu je ID číslo, ktoré hľadáme, a potom rozsah, ktorý chceme hľadať. A začneme tým, že vrátime premennú nazvanú GetAll, takže začneme tým, že sa bude rovnať blanku. Pre každú bunku v mojom rozsahu, ak hodnota bunky je to, čo hľadáme, vezmeme GetAll = GetAll & “” a potom Cell.Offset (0 riadkov, 1 stĺpec), inými slovami hodnota to je hneď vedľa tohto identifikačného čísla, pretože späť vo VBA, tu je identifikačné číslo. Ak nájdeme zodpovedajúce identifikačné číslo, chceme prejsť o 1 stĺpec vyššie. Čo by sa stalo, keby ste chceli prejsť o 2 stĺpce alebo o 3 stĺpce, potom zmeníte tento riadok na 0 a 1 na hodnotu 2. Dobre, tiež skontrolujte, či - nedávame čiarku, ak je to prvý.Takže ak je premenná GetAll momentálne „“, nebudeme vkladať čiarku, dobre?
Takže teraz, keď tu máme túto funkciu, sledujte, aké ľahké je vyriešiť Mattov problém. Ideme sem a vezmeme jeho ID, Ctrl + C a takto prilepme Ctrl + V. Údaje, Odstrániť duplikáty, kliknite na OK. Existuje teda jedinečný zoznam ID a potom chceme povedať = getall a túto hodnotu hľadáme v čiarke E2. Keď sa pozriem na tento rozsah, stlačím kláves F4. F4 funguje rovnako ako bežná funkcia. A opäť Matovu otázku presuniete z cesty, dvojitým kliknutím ju zostrelíte. Bude to fungovať.
A skúsme to, skúsme tu niečo šialené. Urobme frázu 1 a len ich vložte veľa ako frázu 1 až 10. Všetky tieto podpisy podpíšeme na 109999. Prilepte a potom prilepte sem. Skopírujte tento vzorec dole, upravte vzorec tak, aby prešiel samozrejme úplne dole. Áno. A vráti všetky tieto vety. Dobre, takže to je moje riešenie, VBA, malá funkcia. Mike, pozrime sa, čo máš.
Mike Girvin: Vďaka ,. GetAll, to je úžasná funkcia VBA. Dobre, prejdem k listu priamo tu. Už som to previedol na tabuľku programu Excel, takže keď pridáme záznamy nižšie, dúfam, že sa veci aktualizujú.
Prvá vec, ktorú urobím, je teraz rozdelená na dve časti. Mohol by som tu urobiť vzorec na extrahovanie jedinečného zoznamu, ale chcem sa pozrieť na inú možnosť: Pokročilý filter má možnosť extrahovať jedinečný zoznam a je možné ho aktualizovať. Zvýrazním iba údaje stĺpca ID, prejdem na Advanced Filter alebo použijem klávesnicu Alt, A, Q. Teraz, Filter list in-place, no way. Chcem to skopírovať na iné miesto. Má iba stĺpec A a pretože ide o tabuľku programu Excel, ktorá sa neskôr rozšíri. Nemám žiadne kritériá, chcem to skopírovať do D1 a skontrolovať iba jedinečné záznamy. Kliknite na tlačidlo OK.
Teraz sem prídem, vstúpim do časti Všetky komentáre a budem používať funkciu, ktorá funguje iba v Exceli 2016 Office 365: = funkcia TEXTJOIN. Samotná táto funkcia stojí za to získať najnovšiu verziu Excelu. Toto je taká bežná úloha, ktorú ľudia chcú robiť, spájať veľa vecí dohromady. Teraz je náš oddeľovač v zátvorkách „,“ a vynikajúce na tejto funkcii je, že jej môžeme povedať, aby ignorovala prázdne bunky. Teraz môžem uviesť TRUE, 1 alebo Nechať, vynechať. Takže to nechám, vynechaj to. A tu potrebujeme náš text. Použijeme funkciu IF na odfiltrovanie a získanie iba tých položiek, ktoré chceme. Poviem tu, prezrieť si celý tento stĺpec: Názov tabuľky a potom v () Názov poľa, sú ktokoľvek z vás = k tomuto relatívnemu odkazu na bunku, to je logický test. Ak by som mal kliknúť na toto a stlačiť kláves F9 na vyhodnotenie,mohli ste vidieť práve teraz, máme iba 2 TRUES, Ctrl + Z teraz napíšem čiarku a s poľom Trues a Falses teraz môžem dať veci na výber. Takže teraz z tohto rozsahu vyberieme iba položky, ktoré majú PRAVDU. Čiarka a chcem vložiť „“ - ktoré sa v prípade druhého argumentu v TEXTJOIN zobrazí ako prázdna bunka.
Teraz zavriem zátvorku a teraz funkcia IF vytvorí ten reťazec Trues a Falses, skutočné položky z tohto rozsahu sa vyberú, ak to uvidí ako pravda, a všetky ostatné položky budú mať túto prázdnu bunku. A hádaj čo? TEXTJOIN bude úplne ignorovať všetky tieto prázdne bunky a vráti iba položky, ktoré sa zhodujú s týmto ID, a potom ho spojí s týmto oddeľovačom. Teraz je to určite vzorec poľa, ktorý vyžaduje špeciálne stlačenie klávesov Ctrol + Shift + Enter. Argument logického testu drží našu operáciu Array a tento argument nemôže správne vypočítať túto operáciu Array, pokiaľ nepoužívame klávesovú skratku Ctrl + Shift + Enter. Teraz idem uzavrieť zátvorky. Vlastne by sme mohli dokázať 1 tu v texte 1, keby som toto všetko F9, mohli by sme vidieť, že dostaneme 2 položky, zvyšok tých prázdnych buniek bude ignorovaný. Ctrl + Z. Teraz, poďmes zadajte to do bunky pomocou Ctrl + Shift + Enter. Okamžite vyhľadajte vzorec. Tieto zložené zátvorky sú v programe Excel a hovoria vám, že to pochopili, a vypočítali to ako vzorec poľa. Teraz na ňu môžem dvakrát kliknúť a odoslať ju. To vyzerá dobre.
Idem do poslednej bunky a stlačím kláves F2, aby som overil, či všetky rozsahy vyzerajú správne. Čo teraz nechcem urobiť, je, že nechcem stlačiť kláves Enter, pretože tento vzorec po vložení do režimu úprav sa bude správne počítať, iba ak použijeme kombináciu klávesov Ctrl + Shift + Enter; alebo pretože sme už zadali vzorec, môžeme sa pomocou klávesu Esc vrátiť späť na čokoľvek v bunke predtým, ako ju nastavíme do režimu úprav.
Poďme si to otestovať. Kliknem do poslednej bunky tu a kliknem na Tab a potom napíšem nové ID, Tab, Tab. Ďalšia nová nahrávka, Tab, a už vidím, že som tu nemal dosť práce. Ja, ideme dať - Perfektné a potom Enter. Teraz sa to nebude automaticky aktualizovať, akoby sme mali kopu vzorcov, že počítame jedinečné položky a potom extrahujeme jedinečné položky, ale žiadny problém. Sleduj. Tento zoznam jedinečných záznamov môžeme aktualizovať, pretože sme použili rozšírený filter a nezáleží na tom, z ktorej bunky začínate, pretože pri vyvolaní rozšíreného filtra si zapamätá rozsah extraktu a rozsahy, na ktoré sa pôvodne díval. Môžete kliknúť na Rozšírený filter alebo použiť klávesnicu Alt + A + Q. Musíme zvoliť Kopírovať na iné miesto, ale pozrite sa na to.Je to úplne zapamätané a rozšírené na A13 kvôli funkcii tabuľky Excel. Pamätalo si to rozsah extraktu. Musím skontrolovať iba jedinečné záznamy, ale kliknite na tlačidlo OK.
Teraz musím prísť a tento vzorec skopírovať. A ste tu, vďaka rozšírenému filtru a úžasnej funkcii TEXTJOIN v operácii Array získate iba zodpovedajúce položky. Dobre, vráť sa späť k.
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, chcem sa poďakovať všetkým, že sa zastavili. Uvidíme sa nabudúce pri ďalšom podcaste Dueling Excel od spoločnosti Excel Is Fun.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Duel183.xlsm
