Kaley z Nashvillu pracuje na tabuľke lístkov. Pre každú udalosť si vyberie cestovný plán. Tento plán predaja lístkov by mohol naznačovať kdekoľvek od 4 do 16 typov vstupeniek na podujatie. Kaley chce vzorec, ktorý prejde do vyhľadávacej tabuľky a vráti * všetky * zhody a podľa potreby vloží nové riadky.
Aj keď nemám VLOOKUP, ktorý by to dokázal vyriešiť, môžu to vyriešiť nové nástroje Power Query zabudované do Excelu 2016.
Poznámka
Ak máte verziu Excel pre Windows 2010 alebo Excel 2013 pre Windows, môžete si program Power Query bezplatne stiahnuť z Microsoftu. Power Query zatiaľ nie je k dispozícii pre Excel pre Android, Excel pre iOS alebo Excel pre Mac.
Pre ilustráciu: Mike McCann a mechanici sa objavujú v Allenovom divadle s plánom lístkov C. Pretože vo vyhľadávacej tabuľke sú štyri zodpovedajúce riadky, Kaley chce štyri riadky, ktoré hovoria Mike McCann a mechanici, každý s iným zápasom od vyhľadávacia tabuľka.
Vyberte bunku v pôvodnej tabuľke. Stlačením klávesov Ctrl + T označte tieto údaje ako tabuľku. Na karte Tabuľkové nástroje premenujte tabuľku z Tabuľky1 na Zobraziť. Zopakujte postup pre vyhľadávaciu tabuľku a nazvite ju Vstupenky.
Vyberte bunku v tabuľke Zobrazenia. Na karte Údaje vyberte možnosť Z tabuľky / rozsahu.
Po otvorení editora Power Query otvorte rozbaľovaciu ponuku Zatvoriť a načítať a vyberte možnosti Zavrieť a Načítať do ….
V dialógovom okne Import údajov vyberte možnosť Iba vytvoriť pripojenie.
Prejdite na tabuľku Vstupenky. Zopakujte kroky uvedené iba v časti Vytvoriť pripojenie k lístkom. Na table Dotazy by sa mali zobraziť obe pripojenia:
Vyberte ľubovoľnú prázdnu bunku. Vyberte údaje, získať údaje, kombinovať dotazy, zlúčiť.
V dialógovom okne Zlúčenie je šesť krokov. Tretí a štvrtý sa mi nezdajú intuitívne.
- V rozbaľovacej ponuke hore vyberte možnosť Relácie
- V druhej rozbaľovacej ponuke vyberte možnosť Vstupenky.
- Kliknite na nadpis Plán vstupeniek v hornej časti a vyberte tento stĺpec ako cudzí kľúč v tabuľke Zobrazenia.
- Kliknite na nadpis Plán vstupeniek v dolnej časti a vyberte tento stĺpec ako kľúčové pole vo vyhľadávacej tabuľke.
- Otvorte typ spojenia a vyberte možnosť Vnútorné (iba zodpovedajúce riadky).
- Kliknite na tlačidlo OK
Výsledky sú spočiatku sklamaním. Zobrazujú sa všetky polia z tabuľky 1 a stĺpec s tabuľkou, tabuľkou, tabuľkou.
Kliknite na ikonu Rozbaliť v hornej časti stĺpca Vstupenky.
Zrušte výber plánu lístkov, pretože toto pole už máte. Zvyšné pole sa bude volať Tickets.Ticket Type, pokiaľ nezrušíte začiarknutie políčka Použiť pôvodný názov ako predponu.
Úspech! Každý riadok pre každú šou exploduje do viacerých riadkov.
Nie som zvlášť spokojný s triedením údajov. Zoradenie podľa dátumu spôsobí, že sa typy lístkov roztriedia zvláštnym spôsobom.
Pozeraj video
V dnešnom prípade bolo video natočené po napísaní článku. Navrhujem pridanie stĺpca sekvencie do typov lístkov na kontrolu poradia triedenia.
Prepis videa
Naučte sa program Excel z Podcastu, epizóda 2204: Vrátiť všetky VJEDNOTKY.
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dnešná otázka od Nashville Music City. Bol som tam dole v Nashville, niekto je zodpovedný za naplánovanie načítania lístkov do systému predaja lístkov, a tak máme toto: Máme zoznam udalostí - nadchádzajúce udalosti - máme dátum, miesto konania a plán lístkov. Takže, aj keď sa niečo koná v paláci, môžu existovať rôzne plány lístkov - napríklad, možno je nakonfigurovaná podlaha, viete, so sedadlami, alebo možno je to iba státie, že?
Takže v závislosti od toho, aký typ plánu lístkov musíte prejsť sem k vyhľadávacej tabuľke a nájsť všetky zodpovedajúce udalosti, urobíme v podstate to, čo ja nazývam VLOOKUP explózia. Takže ak je niečo v Hannah C, pôjde to dole do Hannah C a ak bude v Hannah C - 1, 2, 3, 4, 5, 6 - 7 položiek, budeme mať Ak chcete vrátiť sedem riadkov - čo znamená, že budete musieť vložiť ďalších šesť riadkov a tieto údaje skopírovať. Dobre.
Teraz to nebudeme robiť s VLOOKUPOM, ale máte koncept - robíme VLOOKUP a všetky odpovede vraciame ako nové riadky. Dobre, tak vezmem obidve tieto tabuľky a pomocou Ctrl + T z nich urobím skutočnú tabuľku. Prvý z nich sa volá Tabuľka 1 - hrozné meno, nazvime to Udalosti alebo predstavenia, nazvime to ukážky, a tak - a druhý, teraz, hej, tu je to, čo som sa naučil, pretože som to praktizoval - musíme mať sekvenčné pole tu. Takže = RIADOK (A1), dvakrát kliknite, skopírujte to nadol a potom skopírujte a prilepte špeciálne hodnoty. Dobre. Teraz urobíme to, čo z toho urobí tabuľku - Ctrl + T, a nazvime ju Tikety.
Dobre. Takže máme predstavenia, máme lístky. Prejdem na kartu Údaje a som tu v relácii, chcem povedať, že chcem získať svoje údaje z tabuľky alebo rozsahu - mimochodom, toto je Power Query. Ak ste späť v programe Excel 2010 alebo 2013, môžete si ho zadarmo stiahnuť z lokality Microsoft, stiahnite si nástroj Power Query. Ak používate počítač Mac alebo iOS alebo Android, je nám ľúto, pre vás nie je k dispozícii žiadny Power Query. Dobre, takže podľa tabuľky alebo rozsahu … nájdite niekoho, kto má-- nájdite priateľa, ktorý má-- Windows PC, a nechajte ich nastaviť. Dobre. Tu je tabuľka, nebudeme s tým robiť nič, iba Zavrieť a načítať, Zavrieť a načítať do a potom povedať „Iba vytvoriť spojenie“, perfektné. Prídeme sem k nášmu druhému stolu: Získať údaje, Z tabuľky alebo rozsahu, s týmto nič nerobíme, Zavrieť a načítať,Zatvoriť a načítať do, „Iba vytvoriť pripojenie“, OK. Takže teraz máme, že máme spojenie s prvou tabuľkou a spojenie s druhou tabuľkou. Nebudeme spájať tieto dva, čo je v podstate ako robiť VLOOKUP alebo myslím, že databázový kĺb je v skutočnosti taký, aký je. Skombinujte dotazy, ideme na zlúčenie. Dobre.
Teraz v tomto dialógovom okne musíte urobiť sedem vecí - a je to trochu mätúce - ako prvú tabuľku vyberieme Shows; ako druhý stôl zvoľte Vstupenky; vyberte, ktoré pole majú spoločné, a môže to byť viac polí - môžete kliknúť s klávesovou skratkou - ale v tomto prípade existuje iba jeden plán lístkov; a potom Plán lístkov; a potom zmeníme typ spojenia na vnútorné spojenie s „iba zodpovedajúcimi riadkami“. Dobre. Teraz kliknete na OK a myslíte si, že sa celý váš problém vyrieši, ale ste iba zdrvení, pretože tu sú všetky údaje z A - vôbec nevložili žiadne nové riadky - a sem, iba nudné hlúpe pole s názvom Lístky, ktoré má len stôl, stôl, stôl, ha.
Ale, chvalabohu, na vrchole toho je ikona Rozbaliť a my to rozšírime … Nemusím si robiť plán, ten už mám-- Typ lístka a postupnosť. Nechcem, aby sa to volalo Tickets.TicketType, čo chce robiť Power Query - tak zruším začiarknutie tohto políčka. Dobre. Momentálne máme 17 riadkov údajov; keď kliknem na OK, BAM! Je tu výbuch. Takže Michael Seeley a Starlighter's sa objavujú so všetkými rôznymi typmi lístkov, ako je tento. Dobre, a uvidíte, že sa tieto typy lístkov objavujú postupne, to je skvelé. Ale Michael Seeley nie je ďalšia šou, ďalšia šou je 5. júna. Takže keď sa to pokúsim zoradiť podľa dátumu - toto ma privádza do šialenstva, nemôžem to vysvetliť. Zoradiť podľa dátumu a Mike Man and the Mechanics má až 65, ale potom sú lístky všetky pokazené. Onimám nesprávnu postupnosť, a preto som musel urobiť túto postupnosť - cíti sa tak. Môžem triediť podľa postupnosti. Takže teraz, 6, 5, nádherne, a potom v rámci toho sú vstupenky správne. V skutočnosti v tomto okamihu už tento stĺpec nepotrebujeme. Takže môžem kliknúť pravým tlačidlom myši a odstrániť a potom Zatvoriť a načítať-- tentoraz sa chystám skutočne Zatvoriť a načítať, nie Zavrieť a načítať do-- a máme náš výsledok. Dobre.
Takže sme prešli zo zoznamu udalostí k tomuto celému veľkému zoznamu, ale tu je úžasná časť: pokazil som to, Mike Man and Mechanics nie je Palace B, jeho palác C. Vrátim sa teda k originálu vpravo hore -ručný roh pre viac informácií o knihe.
Dobre. Témy v tejto epizóde: Kaley v Nashville musí urobiť VLOOKUP, aby vrátil všetky zápasy, zvyčajne vkladá nové riadky. A je to databáza cestovných lístkov, dobre? Takže to budem volať VLOOKUP Explosion, pretože každé predstavenie bude explodovať až do 16 riadkov. Použijeme na to riešenie Power Query a dozvedel som sa, že Dátum sa objaví v nesprávnej sekvencii, pokiaľ k typu tiketu nepridáme pole Sekvencia. Vytvorte obe sady do tabuľky pomocou kombinácie klávesov Ctrl + T; premenujte ich na Show a lístky; a potom z každej tabuľky získať údaje, z tabuľky, zavrieť a načítať a vytvoriť iba pripojenie; opakujte pre druhú tabuľku; potom Data, Get Data, Combine Queries, Merge; a potom to dialógové okno, je to pre mňa dosť mätúce - zvoľte Udalosti, zvoľte Vstupenky, v oboch kliknite na Typ vstupenky, zmeňte spoj na vnútorné spojenie,kliknite na OK, a potom získate ten strašne sklamaný výsledok, keď je to iba stĺpec, ktorý hovorí Tabuľka, Tabuľka, Tabuľka, Tabuľka; v hornej časti kliknite na ikonu Rozbaliť; zvoľte pole Ticket Sequence; nezadávajte predponu názvom tabuľky; a môžete triediť podľa dátumu, triediť podľa sekvencie; Zatvorte a načítajte do tabuľky. Krásne je, že ak sa zmenia podkladové údaje - stačí obnoviť a máte svoje výsledky.
Ak si chcete stiahnuť zošit použitý z dnešného videa, navštívte adresu URL dole v popise služby YouTube. Tiež tam zoznam budúcich seminárov - rád by som vás videl na jednom z mojich živých seminárov Power Excel.
Chcem poďakovať Kaley za to, že sa objavila v Nashville a položila mi túto skvelú otázku. Chcem, aby si sa zastavil. Uvidíme sa nabudúce na ďalšom netcaste od.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: return-all-vlookups.xlsx
Power Query ma stále udivuje. Toto je druhá z trojdňových sérií, kde odpoveďou je Power Query:
- Utorok: Preveďte stĺpec Dátum a čas na iba dátum
- Dnes: Vrátiť všetky SVYHLEDANIE
- Štvrtok: Vytvorte prieskum pre každú z 1100 položiek
Mám celý zoznam videí YouTube, ktorý obsahuje veci, ktoré som nakoniec vyriešil pomocou Power Query.
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Ak máte pochybnosti, použite funkciu ROUND!“
Mike Girvin
