Neturite „Power Pivot“? Nesvarbu. Didžioji „Power Pivot“ dalis yra įdiegta „Excel 2013“ ir dar daugiau programoje „Excel 2016.“. Šiandien mūsų „Ash“ patarimas sujungia lenteles suvestinėje lentelėje.
Kiekvieną trečiadienį septynias savaites pateikiu vieną mėgstamiausių Ash Sharma patarimų. Ash yra „Excel“ komandos produktų vadovas. Jo komanda pateikia jums sukamas lenteles ir daug kitų gerų dalykų. Šiandien Ash mėgstamiausia funkcija yra prisijungti prie kelių duomenų rinkinių naudojant „Relationships“ ir „Data Model“.
Tarkime, kad jūsų IT skyrius pateikia duomenų rinkinį, parodytą A stulpeliuose: D. Yra laukai klientui ir rinkai. Tam tikras rinkas turite sujungti į regionus. Kiekvienas klientas priklauso sektoriui. Regiono ir sektoriaus nėra pirminiuose duomenyse, tačiau jūs turite paieškos lenteles, kuriose pateikiama ši informacija.
Paprastai duomenis sulygintumėte naudodami VLOOKUP, kad ištrauktumėte duomenis iš oranžinės ir geltonos lentelių į mėlyną lentelę. Bet kadangi raktų laukas nėra kairėje kiekvienos lentelės pusėje, turėsite arba pereiti prie INDEX ir MATCH, arba iš naujo sutvarkyti paieškos lenteles.
Nuo „Excel 2013“ galite palikti paieškos lenteles ten, kur jos yra, ir sujungti jas pačioje „Pivot“ lentelės ataskaitoje.
Kad ši technika veiktų, visos trys lentelės turi būti suformatuotos kaip lentelės. Kiekviename duomenų rinkinyje pasirinkite po vieną langelį ir pasirinkite Pagrindinis, Formatuoti kaip lentelę arba paspauskite „Ctrl“ + T. Trys lentelės iš pradžių vadinsis „Table1“, „Table2“ ir „Table3“. Aš naudoju juostelės skirtuką „Table Tools Design“ ir pervardiju kiekvieną lentelę. Aš taip pat keičiu kiekvienos lentelės spalvą. Šiame pavyzdyje mėlyna lentelė vadinama duomenimis. Oranžinė lentelė yra „RegionTable“. Geltona lentelė yra „SectorTable“.
Pastaba
Kai kurie jums pasakys, kad turėtumėte naudoti tokius geekinius pavadinimus kaip „Fact“, „TblSector“ ir „TblRegion“. Jei kas nors jums nepatinka, tiesiog pavogkite jų kišenės apsaugą ir praneškite, kad jums labiau patinka angliškai skambantys vardai.
Norėdami pervardyti lentelę, kairėje skirtuko „Lentelės įrankių dizainas“ kairėje pusėje esančiame laukelyje įveskite naują pavadinimą. Lentelių pavadinimuose neturėtų būti tarpų.
Kai bus apibrėžtos trys lentelės, eikite į skirtuką Duomenys ir spustelėkite Santykiai.
Dialogo lange Tvarkyti santykius spustelėkite Naujas. Dialoge Kurti ryšį nurodykite, kad duomenų lentelės kliento laukas yra susijęs su „SectorTable“ kliento lauku. Spustelėkite Gerai.
Apibrėžkite dar vieną naują ryšį tarp „Market“ lauko duomenų ir „RegionTable“ laukuose. Apibrėžę abu santykius, juos pamatysite dialogo lange Tvarkyti santykius.
Sveikiname: ką tik sukūrėte duomenų modelį savo darbaknygėje. Atėjo laikas sukurti sukamą lentelę.
Pasirinkite tuščią langelį, kuriame norite rodyti suvestinę lentelę. Pagal numatytuosius nustatymus dialogo langas Kurti suvestinę lentelę pasirinks Naudoti šios darbaknygės duomenų modelį. „Pivot“ lentelės vieta bus numatytoji pagal pasirinktą langelį. Spustelėkite Gerai.
„Pivot“ lentelių laukų sąraše bus pateiktos visos trys lentelės. Norėdami išplėsti lentelės pavadinimą ir rodyti laukus, naudokite lentelės kairėje esantį trikampį.
Išskleiskite duomenų lentelę. Pasirinkite lauką Pajamos. Jis bus automatiškai perkeltas į Vertybių sritį. Išskleiskite „SectorTable“. Pasirinkite lauką Sektorius. Jis bus perkeltas į eilutę. Išskleiskite RegionTable. Vilkite regiono lauką į sritį Stulpeliai. Dabar turėsite suvestinę lentelę, kurioje apibendrinami trijų lentelių duomenys.
Pastaba
Kiekvienoje knygoje, kurią parašiau anksčiau, naudoju kitokią techniką kurdamas šį pranešimą. Apibrėžęs tris lenteles, pasirenku langelį A1 ir „Insert, Pivot Table“. Pažymiu langelį Pridėti šiuos duomenis prie duomenų modelio. Sąrankos lentelės laukų sąraše sąrašo viršuje pasirinkite Visi. Pasirinkite ataskaitos laukus ir po to apibrėžkite ryšius. Aukščiau aprašyta technika atrodo sklandesnė ir iš tikrųjų apima nedidelį planavimą. Žmonės, kurie naudoja „Option Explicit“ savo VBA kode, tikrai norėtų šio metodo.
Dėl duomenų modelio sąsajų „Excel“ jaučiasi labiau kaip „Access“ arba „SQL Server“, tačiau visais „Excel“ privalumais.
Man patinka paprašyti „Excel“ komandos mėgstamiausių funkcijų. Kiekvieną trečiadienį pasidalinsiu vienu jų atsakymu. Ačiū Ash Sharma už šios idėjos pristatymą.
„Excel“ dienos mintis
Aš paprašiau savo „Excel Master“ draugų patarimo apie „Excel“. Šiandienos mintis apmąstyti:
„Neieškokite, jei esate santykiuose“
Jonas Michaloudis
