Evitați VLOOKUP folosind modelul de date. Deci, aveți două tabele care trebuie unite cu VLOOKUP înainte de a putea face un tabel pivot. Dacă aveți Excel 2013 sau mai nou pe un computer cu Windows, acum puteți face acest lucru simplu și ușor.
Spuneți că aveți un set de date cu informații despre produse, clienți și vânzări.
Departamentul IT a uitat să introducă sectorul acolo. Iată un tabel de căutare care mapează clientul pe sector. E timpul pentru o VLOOKUP, nu?
Nu este necesar să faceți VLOOKUP-uri pentru a vă alătura acestor seturi de date dacă aveți Excel 2013 sau Excel 2016. Ambele versiuni de Excel au încorporat motorul Power Pivot în nucleul Excel. (Puteți face acest lucru și cu ajutorul programului de completare Power Pivot pentru Excel 2010, dar există câțiva pași suplimentari.)
Atât în setul de date original, cât și în tabelul de căutare, utilizați Acasă, Format ca tabel. În fila Instrumente de tabel, redenumiți tabelul din Tabelul 1 în ceva semnificativ. Am folosit date și sectoare.
Selectați o celulă din tabelul de date. Alege Insert, Pivot Table. Începând din Excel 2013, există o casetă suplimentară Adăugați aceste date la modelul de date pe care ar trebui să o selectați înainte de a face clic pe OK.
Lista câmpurilor din tabelul pivot apare cu câmpurile din tabelul de date. Alegeți Venituri. Deoarece utilizați Modelul de date, o linie nouă apare în partea de sus a listei, oferind Activ sau Toate. Faceți clic pe Toate.
În mod surprinzător, lista Câmpuri din tabelul pivot oferă toate celelalte tabele din registrul de lucru. Acest lucru este revoluționar. Nu ați făcut încă o VLOOKUP. Extindeți tabelul Sectoare și alegeți Sector. Două lucruri se întâmplă pentru a vă avertiza că există o problemă.
În primul rând, tabelul pivot apare cu același număr în toate celulele.
Poate că avertismentul mai subtil este că apare o casetă galbenă în partea de sus a listei Câmpuri din tabelul pivot care indică faptul că trebuie să creați o relație. Alegeți Creați. (Dacă vă aflați în Excel 2010 sau 2016, luați-vă noroc cu Auto-Detect.)
În dialogul Creare relație, aveți patru meniuri derulante. Alegeți Date sub Tabel, Client sub Coloană (străin) și Sectoare sub Tabel asociat. Power Pivot va completa automat coloana potrivită sub Coloana conexă (primară). Faceți clic pe OK.
Tabelul pivot rezultat este un mashup al datelor originale și al tabelului de căutare. Nu sunt necesare VLOOKUP-uri.
Urmăriți videoclipul
- Începând cu Excel 2013, dialogul Tabelului pivot oferă modelul de date
- Acesta este cuvântul de cod pentru motorul Power Pivot
- Pentru a utiliza modelul de date, creați un tabel Ctrl + T din fiecare tabel din registrul de lucru
- Construiți un tabel pivot din primul tabel
- În lista de câmpuri a tabelului pivot, treceți de la Activ la Toate
- Alegeți un câmp din tabelul de căutare
- Fie creați relația, fie Detectare automată
- Auto-Detect nu a fost acolo în 2013
- Mulțumim lui Colin Michael și Alejandro Quiceno pentru sugerarea Power Pivot în general.
Transcriere video
Aflați Excel din podcast, episodul 2014 - Eliminați VLOOKUP!
Podcasting această carte întreagă, faceți clic pe „i” în colțul din dreapta sus pentru lista de redare!
Bună, bine ați revenit pe netcast, eu sunt Bill Jelen, de fapt se numește Eliminare VLOOKUP cu modelul de date! Acum îmi cer scuze, acesta este Excel 2013 și mai nou, dacă sunteți din nou în Excel 2010, trebuie să mergeți să descărcați suplimentul Power Pivot, care, desigur, este gratuit în 2010. Deci, ceea ce avem aici este că avem principalul set de date, există un câmp Client aici, iar apoi am un mic tabel care mapează clientul pe sector, trebuie să creez venituri totale pe sectoare, nu? Acesta este un VLOOKUP, doar faceți un VLOOKUP, dar hei, datorită Excel 2013, nu trebuie să facem un VLOOKUP! Le-am transformat pe amândouă într-un tabel, iar pe Instrumentele de masă, Proiectare, redenumesc tabelele, îl numesc Sectoare și îl numesc Data, pentru a le transforma într-un tabel, doar alegeți o celulă, apăsați Ctrl + T. Deci, dacă avem niște titluri și câteva numere, când apăsați Ctrl + T,ei întreabă „Unde sunt datele pentru tabelul tău?”, tabelul meu are anteturi și apoi îl numesc Table3, tu îl numești altceva. Bine, așa am creat acele două tabele, o să scap de acest tabel, bine.
Deci, pentru ca acest truc să funcționeze, toate datele trebuie să trăiască în tabele. Mergem la fila Inserare, alegem PivotTable și chiar aici, în partea de jos, adăugăm aceste date la Modelul de date. Sună foarte inofensiv, nu? Nu există nimic ca punctul de aprindere care să spună „Hei, te va lăsa să faci lucruri uimitoare!” Și ceea ce spun aici, ceea ce încearcă să nu spună este că - O, apropo, fiecare copie a Excel 2013 are motorul Power Pivot în spate. Știți, dacă sunteți în Office 365, plătiți 10 USD pe lună și ei vor să plătiți 12 USD sau 15 USD pe lună pentru a obține Power Pivot, cei doi sau cinci dolari suplimentari. Ei bine, hei, shh, nu spune, de fapt, ai cea mai mare parte a Power Pivot deja în Excel 2013. Bine, așa că fac clic pe OK, durează puțin mai mult pentru a încărca modelul de date, bine, dar este OK și chiar peste Aici,în câmpurile PivotTable, am o listă cu toate câmpurile. Așadar, vreau să arăt venituri, cu siguranță, dar ceea ce este diferit este aici cu Active and All. Când aleg Toate, primesc toate tabelele din registrul de lucru. Bine, așa că mă duc la sectoare și am spus că vreau să pun sectorul în zona Rânduri. Acum, inițial, raportul va fi greșit, vedeți 6,7 milioane până la capăt, iar acest avertisment galben de aici va spune că trebuie să creați o relație.iar acest avertisment galben de aici va spune că trebuie să creați o relație.iar acest avertisment galben de aici va spune că trebuie să creați o relație.
Bine acum, în 2010 cu Power Pivot, ar fi oferit AutoDetect, în 2013 au eliminat AutoDetect, iar în 2016 au readus AutoDetect, bine? Ar trebui să vă arăt cum arată CREATE, dar când fac clic pe acest buton CREATE, da, asta e, bine, bine. Deci, din primul nostru tabel Date, am un câmp numit Client, din tabelul aferent Sectoare, am un câmp numit Client și apoi faceți clic pe OK, bine. Dar permiteți-mi să vă arăt cât de grozav este AutoDetect, dacă se întâmplă să fiți în 2016, acolo, și-au dat seama, cât de minunat este asta, nu? Nu trebuie să vă faceți griji cu privire la VLOOKUP, iar virgula cade la final, dacă VLOOKUP vă face rău capul, veți iubi Modelul de date. Am luat aceste două tabele, le-am unit, știi, așa cum ar face Access, cred, și am creat un tabel pivot, absolut uimitor.Deci, verificați modelul de date data viitoare când trebuie să faceți o VLOOKUP între două tabele. Ei bine, acest lucru și toate celelalte 40 de sfaturi sunt în carte, dați clic pe „i” din colțul din dreapta sus. Puteți cumpăra cartea, puteți avea o referință încrucișată completă la această întreagă serie de videoclipuri, pe tot august, pe tot septembrie, naiba, am putea chiar să trecem în octombrie pentru a finaliza totul.
Bine, recapitulează astăzi: începând cu Excel 2013, dialogul Tabelului pivot oferă ceva numit Model de date, este cuvântul de cod pentru motorul Power Pivot. Înainte de a vă crea tabelele pivot, faceți Ctrl + T pentru a crea un tabel din fiecare registru de lucru, am luat timp suplimentar pentru a le numi pe fiecare. Construiți un tabel pivot din primul tabel și apoi în lista de câmpuri, mergeți în partea de sus și treceți de la Activ la Toți. Alegeți un câmp din tabelul de căutare și apoi vă va avertiza că fie trebuie să creați o relație, fie AutoDetect, în 2013, trebuie să faceți clic pe CREARE. Dar asta este, 4 clicuri pentru a-l crea, 5 dacă numărați butonul OK, deci foarte ușor de făcut.
Bine, Colin, Michael și Alejandro Quiceno au sugerat Power Pivot, în general, pentru cărți, datorită lor, mulțumită pentru că ați trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2014.xlsx
