Izbjegavajte VLOOKUP pomoću podatkovnog modela. Dakle, imate dvije tablice koje je potrebno spojiti s VLOOKUP-om prije nego što možete napraviti pivot tablicu. Ako na računalu sa sustavom Windows imate Excel 2013 ili noviji, to sada možete učiniti jednostavno i jednostavno.
Recimo da imate skup podataka s podacima o proizvodu, kupcu i prodaji.
IT odjel zaboravio je tamo staviti sektor. Evo tablice pretraživanja koja mapira kupca u sektor. Vrijeme je za VLOOKUP, zar ne?
Nije potrebno raditi VLOOKUP-ove da biste se pridružili tim skupovima podataka ako imate Excel 2013 ili Excel 2016. Obje ove verzije Excela ugradile su Power Pivot engine u jezgru programa Excel. (To biste mogli učiniti i pomoću programskog dodatka Power Pivot za Excel 2010, ali postoji nekoliko dodatnih koraka.)
I u izvornom skupu podataka i u tablici pretraživanja koristite Početna, Oblikuj kao tablicu. Na kartici Alati tablice preimenujte tablicu iz Tablice1 u nešto smisleno. Koristio sam podatke i sektore.
Odaberite jednu ćeliju u tablici podataka. Odaberite Umetanje, zaokretna tablica. Počevši od programa Excel 2013, postoji dodatni okvir Dodaj ove podatke u model podataka koji biste trebali odabrati prije nego što kliknete U redu.
Popis polja zaokretne tablice pojavljuje se s poljima iz tablice podataka. Odaberite Prihod. Budući da koristite podatkovni model, na vrhu popisa pojavit će se novi redak koji nudi Aktivno ili Sve. Kliknite Sve.
Iznenađujuće, popis polja zaokretnih tablica nudi sve ostale tablice u radnoj knjizi. Ovo je prijelomno. Još niste napravili VLOOKUP. Proširite tablicu Sektori i odaberite Sektor. Dvije stvari vas upozoravaju da postoji problem.
Prvo se zaokretna tablica pojavljuje s istim brojem u svim ćelijama.
Možda je suptilnije upozorenje žuti okvir koji se pojavljuje na vrhu popisa polja zaokretne tablice naznačujući da trebate stvoriti odnos. Odaberite Stvori. (Ako ste u programu Excel 2010 ili 2016, uzmite svoju sreću s automatskim otkrivanjem.)
U dijaloškom okviru Stvori odnos imate četiri padajuća izbornika. Odaberite podatke ispod tablice, kupac pod stupcem (inozemni) i sektore pod povezanom tablicom. Power Pivot automatski će ispuniti odgovarajući stupac ispod Povezani stupac (Primarno). Kliknite U redu.
Rezultirajuća zaokretna tablica predstavlja miješanje izvornih podataka i tablice pretraživanja. Nisu potrebna VLOOKUP-a.
Gledaj video
- Počevši od programa Excel 2013, dijaloški okvir Zaokretna tablica nudi model podataka
- Ovo je kodna riječ za Power Pivot Engine
- Da biste koristili podatkovni model, iz svake tablice u radnoj knjizi izradite tablicu Ctrl + T
- Izgradite zaokretnu tablicu od prve tablice
- Na popisu polja zaokretne tablice promijenite iz Active u All
- Odaberite polje iz tablice pretraživanja
- Ili stvorite vezu ili Automatski prepoznaj
- Auto-Detect nije bio tamo 2013. godine
- Hvala Colinu Michaelu i Alejandru Quicenu što su uopće predložili Power Pivot.
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2014. - Uklonite VLOOKUP!
Podcastajući cijelu ovu knjigu, kliknite "i" u gornjem desnom kutu za popis za reprodukciju!
Hej, dobrodošao natrag na netcast, ja sam Bill Jelen, ovo se zapravo zove Eliminiraj VLOOKUP s podatkovnim modelom! Sad se ispričavam, ovo je Excel 2013 i novije verzije, ako ste se vratili u Excel 2010, morate preuzeti dodatak Power Pivot, koji je naravno besplatan još 2010. Dakle, ovo što imamo ovdje je da imamo i svoj glavni skup podataka, ovdje se nalazi polje Kupac, a zatim imam malu tablicu koja mapira kupca u sektor, moram stvoriti ukupan prihod po sektorima, zar ne? Ovo je VLOOKUP, samo napravite VLOOKUP, ali hej, zahvaljujući Excelu 2013, ne moramo raditi VLOOKUP! Oboje sam pretvorio u tablicu, a na Alatima za tablice, Dizajn, preimenujem tablice, nazivam ovaj Sektori i zovem Podaci, da bih ga pretvorio u tablicu, samo odaberite jednu ćeliju, pritisnite Ctrl + T. Dakle, ako imamo neke naslove i brojeve, kada pritisnete Ctrl + T,pitaju "Gdje su podaci za vašu tablicu?", Moja tablica ima zaglavlja, a zatim je zovu Tablica3, a vi to zovete drugačije. U redu, tako sam stvorio te dvije tablice, riješit ću se ove tablice, u redu.
Dakle, da bi ovaj trik uspio, svi podaci moraju živjeti u tablicama. Idemo na karticu Umetni, odaberite zaokretnu tablicu i odmah ovdje na dnu, dodajte ove podatke u model podataka. Ovo zvuči vrlo bezazleno, zar ne? Ne postoji ništa poput bljeskalice koja govori: "Hej, omogućit će vam nevjerojatne stvari!" A ono što ovdje govore, ono što pokušavaju ne reći je da - Usput, svaka kopija programa Excel 2013 iza sebe ima Power Pivot engine. Znate, ako ste u sustavu Office 365, plaćate 10 dolara mjesečno, a oni žele da platite 12 ili 15 dolara mjesečno da biste dobili Power Pivot, dodatnih dva ili pet dolara. Pa, hej, ššš, nemoj reći, ti zapravo većinu Power Pivota već imaš u programu Excel 2013. U redu, pa kliknem U redu, treba malo više vremena za učitavanje podatkovnog modela, u redu, ali to je u redu, i tačno preko ovdje,u poljima zaokretne tablice imam popis svih polja. Dakle, želim pokazati prihod, svakako, ali ono što je drugačije ovdje je kod Active i All. Kad odaberem All, dobivam sve tablice u radnoj knjižici. U redu, idem u Sektore i rekao sam da želim staviti sektor u područje Redova. Sad će, u početku, izvještaj biti pogrešan, pogledajte 6,7 milijuna skroz dolje, a ovo žuto upozorenje ovdje reći će da morate stvoriti vezu.a ovo žuto upozorenje ovdje reći će da morate stvoriti vezu.a ovo žuto upozorenje ovdje reći će da morate stvoriti vezu.
U redu sada, 2010. godine s Power Pivotom, samo bi ponudio AutoDetect, 2013. izvadili su AutoDetect, a 2016. vratili AutoDetect, u redu? Trebao bih vam pokazati kako izgleda CREATE, ali kad kliknem na ovaj gumb CREATE, oh da, to je to, u redu, dobro. Dakle, iz naše prve tablice Podaci imam polje pod nazivom Kupac, iz povezanih sektora tablice imam polje pod nazivom Kupac, a zatim kliknete U redu, u redu. Ali samo da vam pokažem koliko je AutoDetect cool, ako se slučajno nađete u 2016. godini, shvatili su, kako je to strašno, zar ne? Ne morate se brinuti zbog VLOOKUP-a, a zarez pada na kraju, ako vas VLOOKUP ozlijedi u glavi, svidjet će vam se podatkovni model. Uzeo te dvije tablice, spojio ih zajedno, znate, kao što bi to pretvorio i Access, i stvorio pivot tablicu, apsolutno nevjerojatno.Dakle, provjerite model podataka kad sljedeći put budete trebali vršiti VLOOKUP između dvije tablice. Pa, ovaj i svih ostalih 40 savjeta nalaze se u knjizi, kliknite ono „i“ u gornjem desnom kutu. Možete kupiti knjigu, imati potpunu unakrsnu referencu na cijelu ovu seriju videozapisa, cijeli kolovoz, cijeli rujan, dovraga, možda čak i prenesemo u listopad da završimo cijelu stvar.
U redu, rezimirajte danas: počevši od programa Excel 2013, dijaloški okvir Pivot Table nudi nešto što se zove Data Model, to je kodna riječ za Power Pivot engine. Prije nego što izradite svoje pivot tablice, napravite Ctrl + T da biste iz svake radne knjige napravili tablicu, uzeo sam dodatno vrijeme da bih ih imenovao. Izgradite zaokretnu tablicu od prve tablice, a zatim se na popisu polja popnite na vrh i promijenite iz Active u All. Odaberite polje iz tablice pretraživanja, a zatim će vas upozoriti da ili morate stvoriti vezu, ili AutoDetect, 2013. godine, morate kliknuti STVORI. Ali to je ono, 4 klika da biste ga stvorili, 5 ako izbrojite gumb OK, tako da je stvarno, vrlo jednostavno za napraviti.
U redu, Colin, Michael i Alejandro Quiceno predložili su Power Pivot općenito za knjige, zahvaljujući njima, hvala vam što ste navratili, vidimo se sljedeći put za još jedno emitiranje od!
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2014.xlsx
