Venkite VLOOKUP naudodami duomenų modelį. Taigi, jūs turite dvi lenteles, kurias reikia sujungti su VLOOKUP, kad galėtumėte atlikti suvestinę lentelę. Jei „Windows“ kompiuteryje turite „Excel 2013“ ar naujesnę versiją, dabar tai galite padaryti paprastai ir lengvai.
Tarkime, kad turite duomenų rinkinį su gaminio, kliento ir pardavimo informacija.
IT skyrius pamiršo įdėti sektorių. Čia yra paieškos lentelė, kurioje klientai susiejami su sektoriais. Laikas VLOOKUP, tiesa?
Jei norite prisijungti prie šių duomenų rinkinių, nereikia atlikti VLOOKUP, jei turite „Excel 2013“ arba „Excel 2016“. Abi šios „Excel“ versijos įtraukė „Power Pivot“ variklį į pagrindinę „Excel“. (Tai galite padaryti ir naudodami „Power Pivot“ priedą, skirtą „Excel 2010“, tačiau yra keletas papildomų veiksmų.)
Originaliame duomenų rinkinyje ir paieškos lentelėje naudokite pagrindinį, formatuoti kaip lentelę. Skirtuke „Lentelės įrankiai“ pervardykite lentelę iš „Table1“ į kažką reikšmingo. Aš naudojau duomenis ir sektorius.
Duomenų lentelėje pasirinkite vieną langelį. Pasirinkite Įterpti, „Pivot“ lentelę. Pradedant „Excel 2013“, yra papildomas laukelis Pridėti šiuos duomenis prie duomenų modelio, kurį turėtumėte pasirinkti prieš spustelėdami Gerai.
Rodomas suvestinės lentelės laukų sąrašas su laukais iš lentelės Duomenys. Pasirinkite Pajamos. Kadangi naudojate duomenų modelį, sąrašo viršuje pasirodys nauja eilutė, siūlanti „Active“ arba „All“. Spustelėkite „Viskas“.
Keista, kad „PivotTable“ laukų sąraše pateikiamos visos kitos darbaknygės lentelės. Tai yra novatoriškas. Dar neatlikote VLOOKUP. Išplėskite lentelę Sektoriai ir pasirinkite Sektorius. Du dalykai jus įspėja, kad yra problema.
Pirmiausia, suvestinė lentelė visuose langeliuose rodoma tuo pačiu numeriu.
Galbūt subtilesnis įspėjimas yra geltonas langelis „PivotTable“ laukų sąrašo viršuje, nurodantis, kad turite sukurti santykius. Pasirinkite Sukurti. (Jei naudojatės „Excel 2010“ ar 2016 m., Pasisekė naudojant automatinį aptikimą.)
Dialoge Sukurti ryšį turite keturis išskleidžiamuosius meniu. Pasirinkite Duomenys lentelėje, Klientas - stulpelyje (Užsienis) ir Sektoriai - Susijusioje lentelėje. „Power Pivot“ automatiškai užpildys atitinkamą stulpelį, esantį susijusiame stulpelyje (pagrindinis). Spustelėkite Gerai.
Gauta suvestinė lentelė yra pirminių duomenų ir paieškos lentelės sutalpinimas. Nereikia jokių VLOOKUP.
Žiūrėti video
- Pradedant „Excel 2013“, dialogo lange „Pivot Table“ siūlomas duomenų modelis
- Tai yra „Power Pivot Engine“ kodinis žodis
- Norėdami naudoti duomenų modelį, iš kiekvienos darbaknygės lentelės sudarykite Ctrl + T lentelę
- Sukurkite sukamą lentelę iš pirmos lentelės
- „Pivot Table“ laukų sąraše pakeiskite iš „Active“ į „All“
- Paieškos lentelėje pasirinkite lauką
- Arba sukurkite ryšį, arba „Auto-Detect“
- „Auto-Detect“ nebuvo 2013 m
- Ačiū Colinui Michaelui ir Alejandro Quiceno, kurie apskritai pasiūlė „Power Pivot“.
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2014 m. Epizodo - pašalinkite VLOOKUP!
Transliuodami šią knygą, grojaraščio viršutiniame dešiniajame kampe spustelėkite „i“!
Ei, sveiki sugrįžę į internetinę transliaciją, aš Billas Jelenas, tai iš tikrųjų vadinama Pašalinti VLOOKUP naudojant duomenų modelį! Dabar atsiprašau, tai yra „Excel 2013“ ir naujesnė versija. Jei grįžote į „Excel 2010“, turite atsisiųsti „Power Pivot“ priedą, kuris, žinoma, yra nemokamas 2010 m. Taigi, ką mes čia turime, tai turime pagrindinis duomenų rinkinys, čia yra kliento laukas, tada turiu mažą lentelę, kurioje klientai susiejami su sektoriais, turiu susikurti visas pajamas pagal sektorius, tiesa? Tai VLOOKUP, tiesiog atlikite VLOOKUP, bet, ačiū, dėka „Excel 2013“, mes neturime daryti VLOOKUP! Aš abu padariau juos į lentelę, o „Table Tools“, „Design“ pavadinime lenteles, aš tai vadinu „Sectors“ ir vadinu „Data“, kad padarytumėte ją lentele, tiesiog pasirinkite vieną langelį, paspauskite Ctrl + T. Taigi, jei turime keletą antraščių ir skaičių, kai paspausite „Ctrl“ + T,jie klausia „Kur yra jūsų lentelės duomenys?“, Mano lentelėje yra antraštės, tada jie ją vadina 3 lentele, o jūs ją vadinate kažkaip kitaip. Gerai, taip aš sukūriau tas dvi lenteles, aš atsikratysiu šios lentelės, gerai.
Taigi, kad šis triukas veiktų, visi duomenys turi būti pateikti lentelėse. Einame į skirtuką Įterpti, pasirinkite „PivotTable“ ir čia pat, apačioje, pridėkite šiuos duomenis prie duomenų modelio. Tai skamba labai nekenksmingai, tiesa? Nėra nieko panašaus į mirksintį tašką, sakantį „Ei, tai leis tau padaryti nuostabių dalykų!“ Tai, ko jie čia sako, ko bando nesakyti, yra tai, kad, beje, kiekvienoje „Excel 2013“ kopijoje yra „Power Pivot“ variklis. Žinote, jei esate „Office 365“, mokate 10 USD per mėnesį, ir jie nori, kad mokėtumėte 12 arba 15 USD per mėnesį, kad gautumėte „Power Pivot“, papildomus du ar penkis pinigus. Na, ai, šš, nesakyk, iš tikrųjų didžiąją „Power Pivot“ dalį jau turite „Excel 2013“. Gerai, todėl spusteliu Gerai, duomenų modelio įkėlimas užtrunka šiek tiek ilgiau, gerai, bet viskas gerai ir iškart čia,„PivotTable“ laukuose turiu visų laukų sąrašą. Taigi, tikrai noriu parodyti pajamas, bet čia yra „Active“ ir „All“. Kai pasirenku viską, gaunu visas darbaknygės lenteles. Gerai, todėl einu į „Sektorius“ ir pasakiau, kad noriu įdėti sektorių į „Eilių“ sritį. Iš pradžių ataskaita bus neteisinga, visą kelią pamatysite 6,7 mln., O šis geltonas įspėjimas čia pasakys, kad turite sukurti santykius.ir šis geltonas įspėjimas čia pasakys, kad turite sukurti santykius.ir šis geltonas įspėjimas čia pasakys, kad turite sukurti santykius.
Gerai dabar, 2010 m. Naudojant „Power Pivot“, jis tiesiog pasiūlė „AutoDetect“, 2013 m. Jie išvedė „AutoDetect“, o 2016 m. - „AutoDetect“ atgal, gerai? Turėčiau jums parodyti, kaip atrodo CREATE, bet kai spustelėsiu šį CREATE mygtuką, o taip, viskas, gerai, gerai. Taigi iš mūsų pirmosios lentelės „Duomenys“ turiu lauką, pavadintą „Klientas“, iš susijusios lentelės „Sektoriai“, turiu lauką „Klientas“, tada spustelėkite Gerai, gerai. Bet leiskite man tiesiog parodyti, koks šaunus yra „AutoDetect“. Jei nutikote 2016 m., Jie suprato, kaip tai nuostabu, tiesa? Jums nereikia jaudintis dėl VLOOKUP, o kablelis krenta pabaigoje, jei dėl VLOOKUP jums skaudės galvą, jums patiks duomenų modelis. Paėmė šias dvi lenteles, sujungė jas, manau, kaip „Access“ padarys, manau, ir sukūrė „Pivot“ lentelę, visiškai nuostabi.Taigi patikrinkite duomenų modelį, kai kitą kartą turėsite atlikti VLOOKUP tarp dviejų lentelių. Šis ir visi kiti 40 patarimų yra knygoje. Spustelėkite tą „i“ viršutiniame dešiniajame kampe. Galite nusipirkti knygą, turėti išsamią nuorodą į visą šią vaizdo įrašų seriją. Visą rugpjūtį, visą rugsėjį, gi, mes netgi galime perkelti į spalį, kad visa tai padarytume.
Gerai, šiandien pakartokime: pradedant programoje „Excel 2013“, „Pivot Table“ dialogo lange yra kažkas, vadinamas „Data Model“, tai yra „Power Pivot“ variklio kodinis žodis. Prieš kurdami „Pivot“ lenteles, atlikite „Ctrl“ + T, kad sudarytumėte lentelę iš kiekvienos darbaknygės. Aš skyriau papildomą laiką kiekvienai jų pavadinti. Sukurkite „Pivot“ lentelę iš pirmos lentelės, tada laukų sąraše eikite į viršų ir pakeiskite iš „Active“ į „All“. Pasirinkite lauką iš paieškos lentelės, tada jis jus įspės, kad 2013 m. Turite sukurti ryšį arba „AutoDetect“, spustelėkite „Sukurti“. Bet ką, 4 paspaudimai jį sukuria, 5, jei skaičiuojate mygtuką Gerai, tai tikrai labai lengva padaryti.
Alrightas, Colinas, Michaelas ir Alejandro Quiceno knygoms pasiūlė „Power Pivot“, ačiū jiems, ačiū jums, kad užsukote, pamatysime kitą kartą, kai pasirodys dar viena transliacija iš
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2014.xlsx
