Eikö sinulla ole Power Pivotia? Ei väliä. Suurin osa Power Pivotista on sisäänrakennettu Excel 2013: een ja vielä enemmän Excel 2016: een. Ashin vihje yhdistää tänään taulukoita pivot-taulukkoon.
Joka keskiviikko seitsemän viikon ajan esitän yhden Ash Sharman suosikkivinkeistä. Ash on tuotepäällikkö Excel-tiimissä. Hänen tiiminsä tuo sinulle kääntöpöydät ja monia muita hyviä asioita. Tänään Ashin suosikki ominaisuus on yhdistää useita tietojoukkoja suhteiden ja tietomallin avulla.
Sano, että IT-osastosi antaa sinulle sarakkeissa A: D esitetyn tietojoukon. Asiakkaalle ja markkinoille on kenttiä. Sinun on yhdistettävä tietyt markkinat alueiksi. Jokainen asiakas kuuluu sektoriin. Alue ja Sektori eivät ole alkuperäisissä tiedoissa, mutta sinulla on hakutaulukot näiden tietojen toimittamiseksi.
Normaalisti tasoitat tiedot vetämällä VLOOKUP-toiminnolla tietoja oransseista ja keltaisista taulukoista siniseen. Mutta koska avainkenttä ei ole kunkin taulukon vasemmalla puolella, joudut joko vaihtamaan INDEX- ja MATCH-asetuksiin tai järjestämään hakutaulukot uudelleen.
Excel 2013: sta alkaen voit jättää hakutaulukot niiden sijaintiin ja yhdistää ne itse pivot-taulukkoraporttiin.
Jotta tämä tekniikka toimisi, kaikki kolme taulukkoa on muotoiltava taulukoiksi. Valitse yksi solu kussakin tietojoukossa ja valitse Koti, Alusta taulukkona tai paina Ctrl + T. Aluksi näitä kolmea taulukkoa kutsutaan nimellä Taulukko1, Taulukko2 ja Taulukko3. Käytän valintanauhan Taulukon työkalut -välilehteä ja nimeän jokaisen taulukon uudelleen. Muutan myös jokaisen pöydän väriä. Tässä esimerkissä sinistä taulukkoa kutsutaan nimellä Data. Oranssi taulukko on RegionTable. Keltainen taulukko on SectorTable.
Huomautus
Jotkut sanovat, että sinun tulisi käyttää geeky-nimiä, kuten Fact, TblSector ja TblRegion. Jos joku vaivaa sinua tällä tavoin, varasta vain heidän taskussuojuksensa ja ilmoita hänelle, että pidät mieluummin englanninkielisistä nimistä.
Jos haluat nimetä taulukon uudelleen, kirjoita uusi nimi Taulukon työkalut -välilehden vasemmalla puolella olevaan ruutuun. Taulukoiden nimissä ei saa olla välilyöntejä.
Kun kolme taulukkoa on määritelty, siirry Tiedot-välilehteen ja napsauta Suhteet.
Napsauta Hallitse suhteita -valintaikkunassa Uusi. Määritä Luo suhde -valintaikkunassa, että tietotaulukon Asiakas-kenttä liittyy SectorTable-asiakaskenttään. Napsauta OK.
Määritä toinen uusi suhde Market-kentän välillä Data- ja RegionTable-kentissä. Kun olet määrittänyt molemmat suhteet, näet ne Hallitse suhteita -valintaikkunassa.
Onnittelut: olet juuri rakentanut tietomallin työkirjaasi. On aika rakentaa pivot-taulukko.
Valitse tyhjä solu, jossa haluat pivot-taulukon näkyvän. Oletuksena Luo pivot-taulukko -valintaikkuna valitsee Käytä tämän työkirjan tietomallia. Kääntötaulukon sijainti on oletuksena valitsemasi solu. Napsauta OK.
Pivot-taulukon kentät -luettelossa luetellaan kaikki kolme taulukkoa. Laajenna taulukon nimeä kenttien näyttämiseksi taulukon vasemmalla puolella olevan kolmion avulla.
Laajenna Data-taulukko. Valitse Tuotto-kenttä. Se siirtyy automaattisesti Arvot-alueelle. Laajenna SectorTable. Valitse Sector-kenttä. Se siirtyy Rivit-alueelle. Laajenna RegionTable. Vedä Alue-kenttä Sarakkeet-alueelle. Sinulla on nyt pivot-taulukko, joka sisältää yhteenvedon kolmen taulukon tiedoista.
Huomautus
Kaikissa ennen tänään kirjoittamissani kirjoissa käytän eri tekniikkaa tämän raportin rakentamiseen. Kun olet määrittänyt kolme taulukkoa, valitsen solun A1 ja Insert, Pivot Table. Tarkistan Lisää nämä tiedot tietomalliin -valintaruudun. Valitse Pivot-taulukon kentät -luettelosta luettelon yläreunasta Kaikki. Valitse kentät raportille ja määritä sitten suhteet tosiasian jälkeen. Edellä kuvattu tekniikka näyttää sujuvammalta ja siihen liittyy todella pieni suunnittelu eteenpäin. Ihmiset, jotka käyttävät Option Explicitia VBA-koodissaan, haluavat ehdottomasti tämän menetelmän.
Datamallin suhteet saavat Excelin tuntemaan enemmän kuin Access tai SQL Server, mutta kaikella Excelin hyvyydellä.
Rakastan kysyä Excel-tiimiltä heidän suosikkiominaisuuksiaan. Joka keskiviikko jaan yhden heidän vastauksistaan. Kiitos Ash Sharmalle tämän idean toimittamisesta.
Päivän Excel-ajatus
Olen pyytänyt Excel Master -kaveriltani neuvoja Excelistä. Tämän päivän ajatus miettiä:
"Älä etsi, jos olet parisuhteessa"
John Michaloudis
