Kaley z Nashvillu pracuje na tabulce jízdenek. Pro každou událost si vybere jízdenkový plán. Tento lístkový plán by mohl označovat kdekoli od 4 do 16 typů lístků na akci. Kaley chce vzorec, který přejde do vyhledávací tabulky a vrátí * všechny * shody, podle potřeby vloží nové řádky.
I když nemám VLOOKUP, který by to mohl vyřešit, mohou to vyřešit nové nástroje Power Query zabudované do aplikace Excel 2016.
Poznámka
Pokud máte Windows pro Excel 2010 nebo Excel 2013, můžete si Power Query zdarma stáhnout z Microsoftu. Power Query zatím bohužel není k dispozici pro Excel pro Android, Excel pro iOS nebo Excel pro Mac.
Pro ilustraci: Mike McCann a mechanici se objevují v Allenově divadle s plánem lístků C. Jelikož ve vyhledávací tabulce jsou čtyři shodné řádky, chce Kaley čtyři řádky, které říkají Mike McCann a mechanici, každý s jinou shodou od vyhledávací tabulka.
Vyberte buňku v původní tabulce. Stisknutím kláves Ctrl + T označte tato data jako tabulku. Na kartě Nástroje tabulky přejmenujte tabulku z Tabulka1 na Zobrazení. Opakujte pro vyhledávací tabulku a nazvěte ji Vstupenky.
Vyberte buňku v tabulce Pořady. Na kartě Data vyberte možnost Z tabulky / rozsahu.
Po otevření editoru Power Query otevřete rozevírací nabídku Zavřít a načíst a zvolit Zavřít a Načíst do ….
V dialogovém okně Importovat data zvolte Pouze vytvořit připojení.
Přejděte k tabulce Vstupenky. Opakujte kroky pouze k vytvoření připojení k lístkům. V podokně Dotazy byste měli vidět obě připojení:
Vyberte libovolnou prázdnou buňku. Zvolte Data, Získat data, Zkombinovat dotazy, Sloučit.
V dialogu Sloučit je šest kroků. Třetí a čtvrtý se mi nezdají intuitivní.
- Z rozbalovací nabídky nahoře vyberte Shows
- Z druhé rozevírací nabídky vyberte vstupenky.
- Klikněte na záhlaví Ticket Plan v horní části a vyberte tento sloupec jako cizí klíč v tabulce Shows.
- Klikněte na záhlaví Ticket Plan v dolní části a vyberte tento sloupec jako klíčové pole ve vyhledávací tabulce.
- Otevřete typ spojení a vyberte Vnitřní (pouze odpovídající řádky).
- Klikněte na OK
Výsledky jsou zpočátku zklamáním. Uvidíte všechna pole z tabulky 1 a sloupec, který říká Tabulka, Tabulka, Tabulka.
Klikněte na ikonu Rozbalit v horní části sloupce Vstupenky.
Zrušte výběr plánu lístků, protože toto pole již máte. Zbývající pole se bude jmenovat Tickets.Ticket Type, pokud nezrušíte zaškrtnutí Use Original Name as Prefix.
Úspěch! Každý řádek pro každou show exploduje do více řádků.
Nejsem zvlášť spokojen s tříděním údajů. Řazení podle data způsobí, že se typy lístků roztřídí zvláštním způsobem.
Sledovat video
V dnešním případě bylo video natočeno po napsání článku. Navrhuji přidání sloupce sekvence do typů lístků pro ovládání pořadí řazení.
Přepis videa
Naučte se Excel z Podcastu, epizoda 2204: Vrátit všechny SVYHLEDÁNÍ.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka z Nashville Music City. Byl jsem tam dole v Nashvillu, někdo je zodpovědný za plánování načítání lístků do systému prodeje jízdenek, a tak máme toto: Máme seznam událostí - nadcházející události - máme datum, místo konání a plán vstupenek. Takže, třebaže se něco koná v Paláci, mohou existovat různé plány lístků - jako, možná, že podlaha je nakonfigurovaná, víte, se sedadly nebo možná je to jen stání, že?
Takže v závislosti na tom, jaký typ plánu lístku musíte přijít sem k vyhledávací tabulce a najít všechny odpovídající události, a v podstatě uděláme to, čemu říkám exploze VLOOKUP. Takže pokud je něco v Hannah C, půjde dolů do Hannah C a pokud v Hannah C bude - 1, 2, 3, 4, 5, 6-7 položek, budeme mít vrátit sedm řádků - což znamená, že budete muset vložit dalších šest řádků a zkopírovat tato data dolů. V pořádku.
Nyní to nebudeme dělat s VLOOKUP vůbec, ale dostanete koncept - děláme VLOOKUP a vracíme všechny odpovědi jako nové řádky. Dobře, takže vezmu oba tyto stoly a vytvořím je do skutečné tabulky pomocí Ctrl + T. První z nich se jmenovala Tabulka 1 - hrozné jméno, nazveme to Události nebo Přehlídky, řekněme tomu Přehlídky, takhle - a druhá, teď, hej, tady je to, co jsem se naučil, protože jsem to praktikoval - musíme sekvenční pole zde. Takže = ŘÁDEK (A1), poklepejte a zkopírujte to dolů a poté zkopírujte a vložte speciální hodnoty. V pořádku. Teď uděláme, že z toho uděláme tabulku - Ctrl + T, a my tomu budeme říkat Vstupenky.
V pořádku. Takže máme představení, máme lístky. Půjdu na kartu Data a jsem tady ve výstavě, chci říct, že chci získat svá data z tabulky nebo rozsahu - mimochodem, toto je Power Query. Pokud jste zpět v aplikaci Excel 2010 nebo 2013, můžete si ji zdarma stáhnout z Microsoftu, stáhněte si nástroj Power Query. Pokud používáte Mac nebo iOS nebo Android, bohužel, žádný Power Query pro vás. Dobře, takže z tabulky nebo dosahu … najděte někoho, kdo má-- najděte přítele, který má-- Windows PC a nechte je nastavit. V pořádku. Tady je tabulka, nebudeme s tím dělat nic, stačí zavřít a načíst, zavřít a načíst a poté říct „Pouze vytvořit připojení“, perfektní. Pojdeme sem k našemu druhému stolu: Získat data, Z tabulky nebo Rozsahu, tomuto neděláme nic, Zavřít a načíst,Zavřít a načíst do „Pouze vytvořit připojení“, OK. Takže teď máme, že máme připojení k první tabulce a připojení k druhé tabulce. Nebudeme sloučit tyto dva, což je v podstatě jako dělat VLOOKUP, nebo myslím, že databázový kloub je opravdu takový, jaký je. Zkombinujte dotazy, přejdeme ke sloučení. V pořádku.
Nyní v tomto dialogovém okně musíte udělat sedm věcí - a je to trochu matoucí - jako první tabulku zvolíme Shows; jako druhý stůl zvolte Vstupenky; vyberte, které pole mají společné, a může to být více polí - můžete kliknout s kontrolou - ale v tomto případě existuje pouze jeden plán lístku; a poté plán vstupenek; a potom změníme typ spojení na vnitřní spojení s „pouze odpovídajícími řádky“. V pořádku. Nyní kliknete na OK a myslíte si, že celý váš problém bude vyřešen, ale jste prostě rozdrceni, protože tady jsou všechna data z A - vůbec nezadali žádné nové řádky - a tady, jen nudné hloupé pole s názvem Vstupenky, které má právě stůl, stůl, stůl, hah.
Ale naštěstí je v horní části ikona Rozbalit a my ji budeme rozšiřovat - Nepotřebuji plán, už to mám - Typ lístku a sekvence. Nechci, aby se tomu říkalo Tickets.TicketType, což chce Power Query dělat - tak zruším zaškrtnutí tohoto políčka. V pořádku. Právě teď máme 17 řádků dat; když kliknu na OK, BAM! Je tu exploze. Takže Michael Seeley a Starlighter se objevují se všemi různými typy lístků, jako je tento. Dobře, a uvidíte, že se tyto typy lístků objevují v pořadí, to je skvělé. Ale Michael Seeley není další show, další show je 5. června. Takže když se to pokusím seřadit podle data - to mě přivádí k šílenství, nemohu to vysvětlit. Třídit podle data a Mike Man and the Mechanics přijde až na 65, ale pak jsou všechny lístky pokazené. Ony'mám špatnou sekvenci, a proto jsem musel udělat tuto sekvenci - cítí se tak. Můžu třídit podle sekvence. Takže teď, 6, 5, krásně, a pak v rámci toho jsou vstupenky správné. A ve skutečnosti v tuto chvíli už tento sloupec nepotřebujeme. Takže můžu kliknout pravým tlačítkem a odebrat a poté zavřít a načíst - tentokrát se chystám skutečně zavřít a načíst, ne zavřít a načíst - a máme náš výsledek. V pořádku.
Takže jsme přešli ze seznamu událostí na celý tento velký seznam, ale tady je ta úžasná část: Pokazil jsem to, Mike Man and Mechanics není Palace B, jeho Palace C. Takže se vracím k originálu vpravo nahoře ruční roh pro více informací o knize.
V pořádku. Témata v této epizodě: Kaley v Nashvillu musí udělat VLOOKUP, aby vrátil všechny zápasy, obvykle vkládá nové řádky. A je to databáze jízdenek, dobře? Nazvu to tedy VLOOKUP Explosion, protože každá show exploduje až do 16 řádků. Vyřešíme to pomocí Power Query a já jsem se dozvěděl, že Datum se objeví na nesprávné Sekvenci, pokud k typu tiketu nepřidáme pole Sekvence. Vytvořte obě sady do tabulky pomocí Ctrl + T; pojmenujte je pořady a vstupenky; a poté z každé tabulky získat data, z tabulky, zavřít a načíst, vytvořit pouze připojení; opakujte pro druhou tabulku; pak Data, Získat data, Zkombinovat dotazy, Sloučit; a pak to dialogové okno, je to pro mě docela matoucí - zvolte Události, zvolte Vstupenky, v obou klikněte na Typ vstupenky, změňte spoj na vnitřní spojení,klikněte na OK a pak získáte ten strašně neuspokojivý výsledek, kde je to jen sloupec, který říká Tabulka, Tabulka, Tabulka, Tabulka; v horní části klikněte na ikonu Rozbalit; zvolte pole Ticket Sequence; nepoužívejte předponu s názvem tabulky; a můžete třídit podle data, třídit podle sekvence; Zavřete a načtěte do tabulky. Krásná věc je, že pokud se změní podkladová data - stačí aktualizovat a máte své výsledky.
Now, hey, to download the workbook used from today's video, visit the URL down there in the YouTube description. Also a list there of the upcoming seminars-- I'd love to see you at one of my live Power Excel seminars.
I want to thank Kaley for showing up in Nashville and giving me that great question. I want to you for stopping by. I'll see you next time for another netcast from.
Download Excel File
To download the excel file: return-all-vlookups.xlsx
Power Query continues to amaze me. This is the second of a three-day series where the answer is Power Query:
- Tuesday: Convert a column of Date/Time to just date
- Today: Return All VLOOKUPs
- Thursday: Create a Survey for Each of 1100 items
I have an entire YouTube Playlist of things that I ended up solving with Power Query.
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
"Pokud máte pochybnosti, použijte funkci ROUND!"
Mike Girvin
