Nepoužívejte VLOOKUP pomocí datového modelu. Takže máte dvě tabulky, které je třeba spojit s VLOOKUP, než budete moci udělat kontingenční tabulku. Pokud máte v počítači se systémem Windows Excel 2013 nebo novější, můžete to nyní udělat jednoduše a snadno.
Řekněme, že máte datovou sadu s informacemi o produktu, zákazníkovi a prodeji.
IT oddělení tam zapomnělo dát sektor. Zde je vyhledávací tabulka, která mapuje zákazníka na sektor. Je čas na SVĚTOVÝ VÝHLED, že?
Pokud máte Excel 2013 nebo Excel 2016, není třeba dělat VLOOKUPy, abyste se připojili k těmto datovým sadám. Obě tyto verze aplikace Excel začlenily stroj Power Pivot do základního Excelu. (Můžete to udělat také pomocí doplňku Power Pivot pro Excel 2010, ale existuje několik dalších kroků.)
V původní sadě dat i ve vyhledávací tabulce použijte Domů, Formátovat jako tabulku. Na kartě Nástroje tabulky přejmenujte tabulku z Tabulka1 na něco smysluplného. Použil jsem Data a sektory.
Vyberte jednu buňku v datové tabulce. Vyberte Vložit, Kontingenční tabulka. Počínaje Excelem 2013 existuje další pole Přidat tato data do datového modelu, které byste měli vybrat před kliknutím na OK.
Zobrazí se seznam polí kontingenční tabulky s poli z tabulky dat. Zvolte výnosy. Protože používáte datový model, v horní části seznamu se zobrazí nový řádek s nabídkou Aktivní nebo Vše. Klikněte na Vše.
Seznam polí kontingenční tabulky překvapivě nabízí všechny ostatní tabulky v sešitu. To je průkopnické. Ještě jste neprovedli SVYHLEDÁNÍ. Rozbalte tabulku Sektory a vyberte Sektor. Varují vás dvě věci, že je problém.
Nejprve se kontingenční tabulka zobrazí se stejným počtem ve všech buňkách.
Možná jemnějším upozorněním je žluté pole, které se zobrazí v horní části seznamu polí kontingenční tabulky, což znamená, že je třeba vytvořit vztah. Zvolte Vytvořit. (Pokud jste v aplikaci Excel 2010 nebo 2016, využijte své štěstí s automatickou detekcí.)
V dialogu Vytvořit vztah máte čtyři rozbalovací nabídky. Vyberte Data v tabulce, Zákazník ve sloupci (Zahraniční) a Sektory v části Související tabulka. Power Pivot automaticky vyplní odpovídající sloupec pod souvisejícím sloupcem (primární). Klikněte na OK.
Výsledná kontingenční tabulka je kombinací původních dat a vyhledávací tabulky. Nevyžadují se žádné VLOOKUPy.
Sledovat video
- Od aplikace Excel 2013 nabízí dialog Kontingenční tabulka datový model
- Toto je kódové slovo pro Power Pivot Engine
- Chcete-li použít datový model, vytvořte z každé tabulky v sešitu tabulku Ctrl + T.
- Vytvořte kontingenční tabulku z první tabulky
- V seznamu polí kontingenční tabulky změňte z Aktivní na Vše
- Vyberte pole z vyhledávací tabulky
- Vytvořte relaci nebo proveďte automatickou detekci
- Auto-Detect tam nebyl v roce 2013
- Děkujeme Colinu Michaelovi a Alejandrovi Quicenovi za to, že navrhli Power Pivot obecně.
Přepis videa
Naučte se Excel z podcastu, epizoda 2014 - Eliminujte VLOOKUP!
Podcasting celé této knihy, klikněte na „i“ v pravém horním rohu seznamu skladeb!
Ahoj, vítejte zpět na netcastu, já jsem Bill Jelen, tomuto se vlastně říká Eliminate VLOOKUP s datovým modelem! Nyní se omlouvám, je to Excel 2013 a novější, pokud jste zpět v aplikaci Excel 2010, musíte si stáhnout doplněk Power Pivot, který je samozřejmě zdarma v roce 2010. Takže to, co zde máme, je, že máme hlavní datová sada, je tu pole Zákazník a pak mám malou tabulku, která mapuje zákazníka na sektor, potřebuji vytvořit celkový výnos podle sektoru, že? Toto je SVĚTOVÝ VÝHLED, stačí SVĚTELNÝ VÝHLED, ale hej, díky Excelu 2013 nemusíme SVĚTOVÝ VÝROBEK! Obě jsem vytvořil do tabulky a na Table Tools, Design, přejmenuji tabulky, volám tohle Sektory a volám tohle Data, aby se to stalo tabulkou, stačí vybrat jednu buňku, stisknout Ctrl + T. Takže pokud máme nějaké nadpisy a některá čísla, když stisknete Ctrl + T,ptají se „Kde jsou data pro váš stůl?“, Můj stůl má záhlaví a pak jej nazývají Table3, vy mu říkáte něco jiného. Dobře, tak jsem vytvořil ty dvě tabulky, zbavím se této tabulky, dobře.
Aby tento trik fungoval, musí všechna data žít v tabulkách. Přejdeme na kartu Vložit, vybereme kontingenční tabulku a dole dole zde přidáme tato data do datového modelu. To zní velmi neškodně, že? Neexistuje nic jako bod vzplanutí, který říká: „Hej, umožní ti to dělat úžasné věci!“ A to, co zde říkají, to, co se snaží neříkat, je to - Oh, mimochodem, každá kopie aplikace Excel 2013 má za sebou motor Power Pivot. Víte, že pokud používáte Office 365, platíte 10 $ měsíčně a chtějí, abyste platili 12 $ nebo 15 $ měsíčně, abyste získali Power Pivot, další dva nebo pět babek. No, hej, pšč, neříkej, ve skutečnosti máš většinu Power Pivotu již v Excelu 2013. Dobře, takže kliknu na OK, načítání datového modelu trvá trochu déle, dobře, ale to je v pořádku, a hned tady,v polích kontingenční tabulky mám seznam všech polí. Určitě tedy chci ukázat výnosy, ale co se liší, je tady nahoře u Active a All. Když vyberu Vše, zobrazí se všechny tabulky v sešitu. Dobře, tak jdu do sektorů a řekl jsem, že chci dát sektor do oblasti řádků. Nyní se zpočátku zpráva bude mýlit, viz 6,7 milionu úplně dolů a toto žluté varování zde bude říkat, že musíte vytvořit vztah.a toto žluté varování zde řekne, že musíte vytvořit vztah.a toto žluté varování zde řekne, že musíte vytvořit vztah.
Dobře, v roce 2010 s Power Pivotem by to jen nabídlo AutoDetect, v roce 2013 AutoDetect vyřadili a v roce 2016 přinesli AutoDetect zpět, ano? Měl bych vám ukázat, jak CREATE vypadá, ale když kliknu na toto tlačítko CREATE, ach jo, to je ono, dobře, dobře. Takže z naší první tabulky Data mám pole s názvem Zákazník, ze souvisejících sektorů tabulky mám pole s názvem Zákazník a potom kliknete na OK, dobře. Ale dovolte mi, abych vám ukázal, jak cool je AutoDetect, pokud jste náhodou v roce 2016, zjistili to, jak úžasné je to, že? Nemusíte si dělat starosti s VLOOKUP a na konci čárka padne, pokud vás VLOOKUP bolí hlavou, budete milovat Datový model. Vzal tyto dva stoly, spojil je dohromady, víte, jako by to udělal Access, myslím, a vytvořil kontingenční stůl, naprosto úžasný.Zkontrolujte tedy datový model, až budete příště muset provést VLOOKUP mezi dvěma tabulkami. Toto a všech dalších 40 tipů je v knize. Klikněte na „i“ v pravém horním rohu. Knihu si můžete koupit, mít kompletní křížový odkaz na celou tuto sérii videí, celý srpen, celý září, sakra, můžeme to dokonce přenést do října, abychom celou věc zvládli.
Dobře, rekapitulujte dnes: počínaje Excelem 2013 nabízí dialog Kontingenční tabulka něco, čemu se říká datový model, je to kódové slovo pro motor Power Pivot. Před vytvořením kontingenčních tabulek vytvořte pomocí Ctrl + T tabulku z každého sešitu. Každému jsem dal čas navíc. Vytvořte kontingenční tabulku z první tabulky a poté v seznamu polí přejděte nahoru a změňte z Aktivní na Vše. Vyberte pole z vyhledávací tabulky a poté vás upozorní, že buď musíte vytvořit vztah, nebo AutoDetect, v roce 2013 musíte kliknout na VYTVOŘIT. Ale to je to, 4 kliknutí k jeho vytvoření, 5 pokud spočítáte tlačítko OK, takže opravdu, opravdu snadné.
Dobře, Colin, Michael a Alejandro Quiceno pro knihy obecně navrhli Power Pivot, díky nim, díky vám, že jste se zastavili, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2014.xlsx
