Kas teil pole Power Pivoti? Pole tähtis. Suurem osa Power Pivotist on sisse ehitatud rakendusse Excel 2013 ja veelgi enam rakendusse Excel 2016. Täna ühendab meie Ash'i näpunäide tabelid pöördtabelis.
Igal kolmapäeval seitsme nädala jooksul esitan Ash Sharma ühte lemmiknõu. Ash on Exceli meeskonna tootejuht. Tema meeskond toob teieni pöördlauad ja palju muud head. Täna on Ash'i lemmikfunktsioon mitme andmekogumiga ühendamine, kasutades suhteid ja andmemudelit.
Oletame, et teie IT-osakond annab teile veergudes A kuvatud andmekogumi: D. Kliendi ja turu jaoks on väljad. Peate ühendama teatud turud piirkondadeks. Iga klient kuulub sektorisse. Piirkonda ja sektorit ei ole algandmetes, kuid selle teabe edastamiseks on teil otsingutabelid.
Tavaliselt tasandate andmeid, kasutades VLOOKUP-i, et tõmmata oranžist ja kollasest tabelist andmed sinisesse tabelisse. Kuid kuna võtmeväli ei asu iga tabeli vasakul küljel, peate kas lülituma INDEX- ja MATCH-funktsioonidele või korraldama otsingutabelid uuesti.
Alates Excel 2013-st saate otsingu tabelid jätta oma kohale ja kombineerida need pöördtabeli aruandes ise.
Selle tehnika toimimiseks peavad kõik kolm tabelit vormindama tabelina. Valige igas andmekogumis üks lahter ja valige Kodu, Vormindamine tabelina või vajutage klahvikombinatsiooni Ctrl + T. Need kolm tabelit nimetatakse algselt tabeliteks 1, tabeliks 2 ja tabeliks 3. Kasutan lindi vahekaarti Tabelitööriistade kujundus ja nimetan iga tabeli ümber. Muudan ka iga laua värvi. Selles näites nimetatakse sinist tabelit andmeteks. Oranž laud on RegionTable. Kollane laud on SectorTable.
Märge
Mõned ütlevad teile, et peaksite kasutama geeky nimesid nagu Fact, TblSector ja TblRegion. Kui keegi teile niimoodi vaeva näeb, siis lihtsalt varastage tema taskukaitse ja andke teada, et eelistate inglise keeles kõlavaid nimesid.
Tabeli ümbernimetamiseks tippige vahekaardi Tabelitööriistade kujundus vasakpoolsesse kasti uus nimi. Tabelite nimedes ei tohiks olla tühikuid.
Kui kolm tabelit on määratletud, minge vahekaardile Andmed ja klõpsake valikul Seosed.
Klõpsake dialoogis Suhete haldamine nuppu Uus. Dialoogis Suhte loomine määrake, et andmetabeli väli Klient on seotud SectorTable'i kliendi väljaga. Klõpsake nuppu OK.
Andmete ja RegionTable'i väljade vahel määrake veel üks uus suhe turu välja vahel. Pärast mõlema suhte määratlemist näete neid dialoogis Suhete haldamine.
Palju õnne: olete just töövihikusse koostanud andmemudeli. On aeg luua pöördtabel.
Valige tühi lahter, kus soovite oma pöördtabeli kuvada. Vaikimisi valib PivotTable-liigendtabeli loomine dialoogi Kasuta selle töövihiku andmemudelit. Pöördtabeli asukoht on vaikimisi valitud lahter. Klõpsake nuppu OK.
Pivoti tabeli väljade loendis on loetletud kõik kolm tabelit. Kasutage tabeli vasakul asuvat kolmnurka, et laiendada väljade kuvamiseks tabeli nime.
Laiendage tabelit Andmed. Valige väli Tulud. See liigub automaatselt alale Väärtused. Laiendage tabelit SectorTable. Valige väli Sektor. See liigub ridade alale. Laiendage RegionTable-i. Lohistage väli Piirkond alale Veerud. Teil on nüüd pöördtabel, mis võtab kokku kolme tabeli andmed.
Märge
Igas raamatus, mille olen täna kirjutanud, kasutan selle aruande koostamiseks erinevat tehnikat. Pärast kolme tabeli määratlemist valin lahtri A1 ja Insert, Pivot Table. Märkin ruudu Lisa need andmed andmemudelisse. Valige loendis Pivot Table Fields loendi ülaservast Kõik. Valige aruande jaoks väljad ja määrake pärast fakti seosed. Ülalkirjeldatud tehnika näib sujuvam ja hõlmab tegelikult väikest plaanimist ette. Inimesed, kes kasutavad VBA-koodis Option Explicitit, sooviksid seda meetodit kindlasti.
Andmemudeli seosed muudavad Exceli pigem Accessi või SQL Serveri sarnaseks, kuid Exceli kõigi headustega.
Armastan küsida Exceli meeskonnalt nende lemmikfunktsioone. Igal kolmapäeval jagan ühte nende vastust. Täname Ash Sharmat selle idee edastamise eest.
Exceli päeva mõte
Olen küsinud oma Exceli meistri sõpradelt Exceli kohta nõu. Tänane mõte mõelda:
"Ärge otsige, kui olete suhtes"
John Michaloudis
