Vyvarujte sa VLOOKUPU pomocou dátového modelu. Takže máte dve tabuľky, ktoré je potrebné spojiť s VLOOKUP, skôr ako urobíte kontingenčnú tabuľku. Ak máte program Excel 2013 alebo novší na počítači so systémom Windows, teraz to môžete urobiť jednoducho a ľahko.
Povedzme, že máte súbor údajov s informáciami o produkte, zákazníkovi a predaji.
IT oddelenie tam zabudlo vložiť sektor. Tu je vyhľadávacia tabuľka, ktorá mapuje zákazníka do sektoru. Je čas na VYHĽADÁVANIE, však?
Ak máte Excel 2013 alebo Excel 2016, nie je potrebné robiť VLOOKUPy, aby ste sa pripojili k týmto množinám údajov. Obe tieto verzie programu Excel začlenili motor Power Pivot do základného programu Excel. (Môžete to urobiť aj pomocou doplnku Power Pivot pre Excel 2010, ale je tu niekoľko ďalších krokov.)
V pôvodnej množine údajov aj vo vyhľadávacej tabuľke použite položky Domov, Formátovať ako tabuľku. Na karte Tabuľkové nástroje premenujte tabuľku z tabuľky1 na niečo zmysluplné. Použil som údaje a sektory.
Vyberte jednu bunku v tabuľke údajov. Vyberte Vložiť, Kontingenčná tabuľka. Od verzie Excel 2013 existuje ďalšie políčko Pridať tieto údaje do dátového modelu, ktoré by ste mali zvoliť pred kliknutím na tlačidlo OK.
Zobrazí sa zoznam polí kontingenčnej tabuľky s poľami z tabuľky údajov. Vyberte Výnosy. Pretože používate dátový model, v hornej časti zoznamu sa zobrazí nový riadok, ktorý ponúka Aktívne alebo Všetky. Kliknite na možnosť Všetky.
Zoznam polí kontingenčnej tabuľky prekvapivo ponúka všetky ostatné tabuľky v zošite. Je to prelomové. Ešte ste neurobili SVETROHLÁSENIE. Rozbaľte tabuľku Sektory a vyberte Sektor. Stávajú sa vás dve veci, ktoré vás varujú, že je problém.
Najskôr sa kontingenčná tabuľka zobrazí s rovnakým počtom vo všetkých bunkách.
Snáď tým jemnejším varovaním je žlté pole v hornej časti zoznamu polí kontingenčnej tabuľky, ktoré naznačuje, že je potrebné vytvoriť vzťah. Vyberte možnosť Vytvoriť. (Ak sa nachádzate v programe Excel 2010 alebo 2016, urobte šťastie pomocou funkcie Automatická detekcia.)
V dialógovom okne Vytvoriť vzťah máte štyri rozbaľovacie ponuky. Vyberte možnosť Údaje v časti Tabuľka, Zákazník v stĺpci (Zahraničné) a Sektory v časti Súvisiaca tabuľka. Power Pivot automaticky vyplní zodpovedajúci stĺpec pod súvisiacim stĺpcom (primárny). Kliknite na tlačidlo OK.
Výsledná kontingenčná tabuľka je zlúčením pôvodných údajov a vyhľadávacej tabuľky. Nevyžadujú sa žiadne VLOOKUPy.
Pozeraj video
- Od verzie Excel 2013 ponúka dialógové okno Kontingenčná tabuľka dátový model
- Toto je kódové slovo pre Power Pivot Engine
- Ak chcete použiť dátový model, vytvorte z každej tabuľky v zošite tabuľku Ctrl + T
- Zostavte kontingenčnú tabuľku z prvej tabuľky
- V zozname polí kontingenčnej tabuľky zmeňte nastavenie z Aktívne na Všetky
- Vyberte pole z vyhľadávacej tabuľky
- Vytvorte vzťah alebo Auto-Detect
- V roku 2013 tam funkcia Auto-Detect nebola
- Ďakujem Colinovi Michaelovi a Alejandrovi Quicenovi za všeobecné navrhnutie doplnku Power Pivot.
Prepis videa
Naučte sa Excel z podcastu, epizóda 2014 - Eliminujte VLOOKUP!
Ak podcastujete celú túto knihu, kliknite na písmeno „i“ v pravom hornom rohu zoznamu skladieb!
Ahoj, vitajte späť na netcastu, som Bill Jelen, tomuto sa v skutočnosti hovorí Eliminovať VLOOKUP pomocou dátového modelu! Teraz sa ospravedlňujem, toto je Excel 2013 a novšie, ak ste späť v programe Excel 2010, musíte si ísť stiahnuť doplnok Power Pivot, ktorý je samozrejme zadarmo ešte v roku 2010. Takže to, čo tu máme, je, že máme hlavná množina údajov, je tu pole Zákazník a potom mám malú tabuľku, ktorá mapuje zákazníka do sektoru, potrebujem vytvoriť celkový príjem podľa sektoru, nie? Toto je SVETLÝ VÝHĽAD, stačí urobiť SVETLÝ VÝHĽAD, ale hej, vďaka programu Excel 2013 nemusíme SVETLÁK robiť! Oboje som vytvoril do tabuľky a na Tabuľkové nástroje, Dizajn, premenujem tabuľky, volám tento Sektory a volám tento Údaj, aby som z toho urobil tabuľku, stačí zvoliť jednu bunku, stlačiť Ctrl + T. Takže ak máme nejaké nadpisy a niektoré čísla, po stlačení Ctrl + T,opýtajú sa „Kde sú údaje pre vašu tabuľku?“, Moja tabuľka má hlavičky a potom ju nazývajú Table3, vy ju nazývate inak. Dobre, tak som vytvoril tieto dve tabuľky. Zbavím sa tejto tabuľky, dobre.
Aby tento trik fungoval, musia všetky údaje žiť v tabuľkách. Prejdeme na kartu Vložiť, vyberieme kontingenčnú tabuľku a vpravo dole dole pridáme tieto údaje do údajového modelu. Znie to veľmi neškodne, nie? Nie je nič ako bod blesku, ktorý hovorí: „Hej, umožní ti to robiť úžasné veci!“ A to, čo tu hovoria, to, čo sa snažia nepovedať, je to - Oh, mimochodom, každá kópia Excelu 2013 má za sebou motor Power Pivot. Viete, že ak využívate Office 365, platíte 10 dolárov mesačne a chcú, aby ste platili 12 alebo 15 dolárov mesačne, aby ste získali Power Pivot, ďalšie dva alebo päť dolárov. No, hej, pšč, nehovorte, v skutočnosti máte väčšinu doplnku Power Pivot už v programe Excel 2013. V poriadku, takže kliknem na tlačidlo OK, načítanie dátového modelu trvá o niečo dlhšie, v poriadku, ale je to v poriadku a hneď potom. tu,v poliach kontingenčnej tabuľky mám zoznam všetkých polí. Takže určite chcem ukázať výnosy, ale to, čo sa líši, je tu pri Active a All. Keď vyberiem Všetky, zobrazia sa mi všetky tabuľky v zošite. Dobre, tak idem do sektorov a povedal som, že chcem vložiť sektor do oblasti Rows. Teraz bude správa spočiatku nesprávna, pozri tých 6,7 milióna úplne dole a toto žlté varovanie tu bude znamenať, že si musíte vytvoriť vzťah.a toto žlté varovanie tu bude znamenať, že si musíte vytvoriť vzťah.a toto žlté varovanie tu bude znamenať, že si musíte vytvoriť vzťah.
V poriadku, teraz v roku 2010 s Power Pivotom, to by len ponúklo AutoDetect, v roku 2013 vzali AutoDetect a v roku 2016 priniesli AutoDetect späť, dobre? Mal by som vám ukázať, ako vyzerá CREATE, ale keď kliknem na toto tlačidlo CREATE, ach jo, to je všetko, dobre, dobre. Takže z našej prvej tabuľky Údaje mám pole s názvom Zákazník, zo súvisiacich sektorov tabuľky mám pole s názvom Zákazník a potom kliknete na OK, v poriadku. Ale ukážem vám, aký super je AutoDetect, ak ste náhodou v roku 2016, prišli na to, aké je to úžasné, však? S VLOOKUP sa nemusíte báť a na konci padne čiarka. Ak vám VLOOKUP bude bolieť hlava, budete milovať Dátový model. Zobral som tieto dva stoly, spojil ich dokopy, viete, myslím, že to urobí Access, a vytvoril som kontingenčnú tabuľku, úplne úžasnú.Skontrolujte si teda dátový model, keď nabudúce budete musieť urobiť VÝHĽAD medzi dvoma tabuľkami. Toto a všetkých ďalších 40 tipov je v knihe. Kliknite na „i“ v pravom hornom rohu. Môžete si kúpiť knihu, mať kompletný krížový odkaz na celú túto sériu videí, celý august, celý september, sakra, môžeme to preniesť aj do októbra, aby sme celú vec zvládli.
Dobre, rekapitulujte dnes: od verzie Excel 2013 ponúka dialógové okno kontingenčnej tabuľky niečo, čo sa nazýva dátový model, je to kódové slovo pre motor Power Pivot. Pred vytvorením kontingenčných tabuliek pomocou klávesovej skratky Ctrl + T vytvorte tabuľku z každého zošita. Na pomenovanie každého z nich som si dal čas. Z prvej tabuľky zostavte kontingenčnú tabuľku a potom v zozname polí prejdite nahor a zmeňte nastavenie z Aktívne na Všetky. Vyberte pole z vyhľadávacej tabuľky a potom vás upozorní, že buď musíte vytvoriť vzťah, alebo funkciu AutoDetect v roku 2013 kliknúť na VYTVORIŤ. Ale to je to, 4 kliknutia na jeho vytvorenie, 5 ak spočítate tlačidlo OK, takže naozaj, naozaj ľahké.
Alright, Colin, Michael a Alejandro Quiceno pre knihy navrhli všeobecne Power Pivot, vďaka nim, vďaka vám, že ste sa zastavili, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2014.xlsx
