Izogibajte se VLOOKUP-u z uporabo podatkovnega modela. Torej imate dve tabeli, ki ju morate združiti z VLOOKUP, preden lahko ustvarite vrtilno tabelo. Če imate v računalniku z operacijskim sistemom Windows Excel 2013 ali novejšo različico, lahko zdaj to storite preprosto in enostavno.
Recimo, da imate nabor podatkov s podatki o izdelkih, kupcih in prodaji.
Oddelek za informacijske tehnologije je pozabil postaviti sektor. Tu je iskalna tabela, ki stranko preslika v sektor. Čas je za VLOOKUP, kajne?
Če imate Excel 2013 ali Excel 2016. V teh dveh naborih podatkov ni treba delati VLOOKUP-ov, da bi združili te nabore podatkov. Obe različici Excela sta v jedro Excela vključili pogon Power Pivot. (To lahko storite tudi z dodatkom Power Pivot za Excel 2010, vendar obstaja nekaj dodatnih korakov.)
V izvirnem naboru podatkov in v iskalni tabeli uporabite Domov, Oblikuj kot tabelo. Na zavihku Orodja za tabele preimenujte tabelo iz Tabela1 v nekaj smiselnega. Uporabil sem podatke in sektorje.
V podatkovni tabeli izberite eno celico. Izberite Vstavi, vrtilna tabela. Od Excel 2013 naprej obstaja dodatno polje Dodaj te podatke v podatkovni model, ki ga morate izbrati, preden kliknete V redu.
Seznam polj vrtilne tabele se prikaže s polji iz tabele podatkov. Izberite Prihodek. Ker uporabljate podatkovni model, se na vrhu seznama prikaže nova vrstica, ki ponuja Aktivno ali Vse. Kliknite Vse.
Presenetljivo je, da seznam polj vrtilne tabele ponuja vse ostale tabele v delovnem zvezku. To je prelomno. Še niste naredili VLOOKUPA. Razširite tabelo Sektorji in izberite Sektor. Dve stvari vas opozorita, da obstaja težava.
Najprej se v vseh celicah prikaže vrtilna tabela z enako številko.
Morda je bolj subtilno opozorilo rumeno polje na vrhu seznama polj vrtilne tabele, ki označuje, da morate ustvariti odnos. Izberite Ustvari. (Če uporabljate Excel 2010 ali 2016, si privoščite srečo s samodejnim zaznavanjem.)
V pogovornem oknu Ustvari razmerje imate štiri spustne menije. Pod Tabela izberite Podatki, Stranka pod Stolpec (tuje) in Sektorji pod Sorodno tabelo. Power Pivot bo samodejno izpolnil ustrezni stolpec pod Sorodnim stolpcem (Primarno). Kliknite V redu.
Nastala vrtilna tabela je mešanje prvotnih podatkov in iskalne tabele. Niso potrebni VLOOKUP-i.
Oglejte si video
- Z začetkom v Excelu 2013 pogovorno okno vrtilne tabele ponuja podatkovni model
- To je kodna beseda za Power Pivot Engine
- Če želite uporabiti podatkovni model, iz vsake tabele v delovnem zvezku naredite tabelo Ctrl + T
- Iz prve tabele sestavite vrtilno tabelo
- Na seznamu polj vrtilne tabele spremenite iz Aktivno v Vse
- V iskalni tabeli izberite polje
- Ustvari zvezo ali samodejno zaznaj
- Leta 2013 samodejnega zaznavanja ni bilo
- Hvala Colinu Michaelu in Alejandru Quicenu, ki sta na splošno predlagala Power Pivot.
Video zapis
Naučite se Excel iz podcasta, epizoda 2014 - Odpravite VLOOKUP!
Če objavite celotno knjigo, v zgornjem desnem kotu za seznam predvajanja kliknite »i«!
Hej, dobrodošli nazaj v omrežju, jaz sem Bill Jelen, temu se pravzaprav reče Eliminate VLOOKUP s podatkovnim modelom! Zdaj se opravičujem, to je Excel 2013 in novejši, če ste spet v Excelu 2010, morate prenesti dodatek Power Pivot, ki je seveda brezplačen že leta 2010. Torej, kar imamo tukaj, imamo glavni nabor podatkov, tukaj je polje kupec, nato pa imam majhno tabelo, ki stranko preslika v sektor, ustvariti moram celotni prihodek po sektorju, kajne? To je VLOOKUP, samo naredite VLOOKUP, ampak hej, zahvaljujoč Excelu 2013, nam ni treba narediti VLOOKUPA! Oboje sem naredil v tabelo, na Orodja za tabele, Oblikovanje pa preimenujem tabele, temu pravim Sektorji in imenujem Podatki, da ga naredim v tabelo, izberite eno celico, pritisnite Ctrl + T. Torej, če imamo nekaj naslovov in nekaj številk, ko pritisnete Ctrl + T,vprašajo "Kje so podatki za vašo tabelo?", Moja tabela ima glave, nato pa jo pokličejo Table3, vi pa drugače. V redu, tako sem ustvaril ti dve tabeli, znebil se bom te tabele, v redu.
Da bi ta trik deloval, morajo biti vsi podatki v tabelah. Pojdimo na zavihek Vstavi, izberite vrtilno tabelo in tukaj spodaj na dnu dodajte te podatke v podatkovni model. To se sliši zelo neškodljivo, kajne? Nič ni kot utripajoča točka, ki pravi: "Hej, omogočil vam bo neverjetne stvari!" In kar tukaj govorijo, česar se trudijo, da ne bi rekli, je … Oh, mimogrede, vsaka kopija programa Excel 2013 ima za seboj motor Power Pivot. Veste, če uporabljate Office 365, plačujete 10 USD na mesec in želijo, da plačate 12 ali 15 USD na mesec, da dobite Power Pivot, dodatna dva ali pet dolarjev. No, hej, ššš, ne povej, večino Power Pivota imaš dejansko že v Excelu 2013. V redu, tako da kliknem V redu, traja malo več časa, da naložim podatkovni model, v redu, ampak to je v redu in takoj tukaj,v poljih vrtilne tabele imam seznam vseh polj. Torej, vsekakor želim prikazati prihodek, toda kaj drugače je tukaj z Active in All. Ko izberem Vse, dobim vse tabele v delovnem zvezku. V redu, zato grem v sektorje in rekel sem, da želim postaviti sektor na območje vrstic. Zdaj bo poročilo na začetku napačno, glejte 6,7 milijona do konca, in to rumeno opozorilo tukaj bo reklo, da morate vzpostaviti zvezo.in to rumeno opozorilo tukaj bo reklo, da morate vzpostaviti odnos.in to rumeno opozorilo tukaj bo reklo, da morate vzpostaviti odnos.
Zdaj, leta 2010 s Power Pivotom, je samo ponudil AutoDetect, leta 2013 so AutoDetect odstranili, leta 2016 pa AutoDetect, kajne? Moral bi vam pokazati, kako izgleda CREATE, toda ko kliknem na gumb CREATE, oh ja, to je to, v redu, dobro. Torej iz naše prve tabele Podatki imam polje z imenom Stranka, iz povezanih tabel Sektorji imam polje z naslovom Stranka in nato v redu klikneš V redu. Ampak naj vam samo pokažem, kako kul je AutoDetect. Če ste slučajno leta 2016, so ugotovili, kako super je to, kajne? Za VLOOKUP vam ni treba skrbeti in vejica pade na koncu. Če vas VLOOKUP boli v glavi, vam bo všeč podatkovni model. Vzel sem ti dve tabeli, ju združil, saj veste, kot bi to storil Access, in verjetno ustvaril vrtilno tabelo, popolnoma neverjetno.Torej preverite podatkovni model, ko boste morali naslednjič narediti VLOOKUP med dvema tabelama. No, ta in vseh ostalih 40 nasvetov je v knjigi. V zgornjem desnem kotu kliknite ta i. Knjigo lahko kupite, imate popoln sklic na celotno serijo videoposnetkov, ves avgust, ves september, hudiča, morda bomo celo prenesli v oktober, da bomo vse skupaj naredili.
V redu, povzemite danes: od Excel 2013 se v pogovornem oknu vrtilne tabele ponuja nekaj, kar se imenuje podatkovni model, to je kodna beseda za motor Power Pivot. Preden ustvarite vrtilne tabele, naredite Ctrl + T, da ustvarite tabelo iz vsakega delovnega zvezka. Vzel sem dodaten čas, da sem jih poimenoval. Iz prve tabele sestavite vrtilno tabelo, nato pa se na seznamu polj pomaknite na vrh in spremenite iz Aktivno v Vse. V iskalni tabeli izberite polje in nato vas bo opozorilo, da morate bodisi ustvariti razmerje bodisi AutoDetect, v letu 2013 morate klikniti USTVARI. Ampak to je, 4 klike, da ga ustvarite, 5, če štejete gumb V redu, tako res zelo enostavno.
V redu, Colin, Michael in Alejandro Quiceno so za knjige na splošno predlagali Power Pivot, zahvaljujoč se jim, zahvaljujoč vam, da ste se ustavili, se naslednjič vidimo za še eno oddajo!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2014.xlsx
