Pivot-taulukko useilla tietoalueilla

Ongelman muotoilu

Pivot-taulukot ovat yksi Excelin upeimmista työkaluista. Mutta valitettavasti toistaiseksi mikään Excelin versioista ei pysty tekemään niin yksinkertaista ja tarpeellista asiaa lennossa kuin rakentamaan yhteenvetoa useille lähtötietoalueille, jotka sijaitsevat esimerkiksi eri taulukoissa tai eri taulukoissa:

Ennen kuin aloitamme, selvennetään pari kohtaa. Ensinnäkin uskon, että seuraavat ehdot täyttyvät tiedoissamme:

• Taulukoissa voi olla kuinka monta riviä tahansa dataa, mutta niillä on oltava sama otsikko.
• Lähdetaulukoita sisältävillä taulukoilla ei saa olla ylimääräisiä tietoja. Yksi arkki - yksi pöytä. Ohjaamiseen suosittelen käyttämään pikanäppäintä Ctrl+pää, joka siirtää sinut laskentataulukon viimeksi käytettyyn soluun. Ihannetapauksessa tämän pitäisi olla tietotaulukon viimeinen solu. Jos kun napsautat Ctrl+pää mikä tahansa tyhjä solu taulukon oikealla tai alapuolella on korostettu – poista nämä tyhjät sarakkeet oikealta tai rivit taulukon alapuolelta ja tallenna tiedosto.

Tapa 1: Luo taulukoita pivotia varten Power Queryn avulla

Excelin 2010-versiosta alkaen on ilmainen Power Query -apuohjelma, joka voi kerätä ja muuntaa mitä tahansa dataa ja antaa sen sitten lähteeksi pivot-taulukon rakentamiseen. Ongelmamme ratkaiseminen tämän apuohjelman avulla ei ole ollenkaan vaikeaa.

Ensin luodaan uusi tyhjä tiedosto Exceliin – siihen tehdään kokoonpano ja sitten siihen luodaan pivot-taulukko.

Sitten välilehdelle Päiväys (jos sinulla on Excel 2016 tai uudempi) tai välilehdellä Tehokysely (jos sinulla on Excel 2010-2013) valitse komento Luo kysely - tiedostosta - Excel (Hae tiedot – tiedostosta – Excel) ja määritä lähdetiedosto kerättävillä taulukoilla:

Valitse näkyviin tulevasta ikkunasta mikä tahansa arkki (ei väliä mikä) ja paina alla olevaa painiketta Muutos (Muokata):

Power Query Query Editor -ikkunan pitäisi avautua Excelin päälle. Paneelin ikkunan oikealla puolella Pyydä parametreja poista kaikki automaattisesti luodut vaiheet paitsi ensimmäinen - lähde (Lähde):

Nyt näemme yleisen luettelon kaikista arkeista. Jos tiedostossa on datalehtien lisäksi muita sivulehtiä, niin tässä vaiheessa meidän tehtävämme on valita vain ne taulukot, joista tiedot on ladattava, jättäen pois kaikki muut taulukon otsikon suodattimen avulla:

Poista kaikki sarakkeet paitsi sarake Päiväysnapsauttamalla hiiren kakkospainikkeella sarakkeen otsikkoa ja valitsemalla Poista muut sarakkeet (Poista muut sarakkeet):

Voit sitten laajentaa kerättyjen taulukoiden sisältöä napsauttamalla kaksoisnuolta sarakkeen yläosassa (valintaruutu Käytä etuliitteenä alkuperäistä sarakkeen nimeä voit sammuttaa sen):

Jos teit kaiken oikein, tässä vaiheessa sinun pitäisi nähdä kaikkien peräkkäin koottujen taulukoiden sisältö:

Jää vielä nostaa ensimmäinen rivi taulukon otsikkoon painikkeella Käytä ensimmäistä riviä otsikoina (Käytä ensimmäistä riviä otsikoina) kieleke Koti (Koti) ja poista päällekkäiset taulukon otsikot tiedoista suodattimen avulla:

Tallenna kaikki komennolla tehty Sulje ja lataa – Sulje ja lataa… (Sulje ja lataa – Sulje ja lataa…) kieleke Koti (Koti)ja valitse avautuvassa ikkunassa vaihtoehto Vain yhteys (Vain yhteys):

Kaikki. On vain tehtävä yhteenveto. Voit tehdä tämän siirtymällä välilehdelle Lisää – Pivot-taulukko (Lisää — Pivot-taulukko), valitse vaihtoehto Käytä ulkoista tietolähdettä (Käytä ulkoista tietolähdettä)ja sitten napsauttamalla painiketta Valitse yhteys, pyyntömme. Pivotin lisäluominen ja konfigurointi tapahtuu täysin tavallisella tavalla vetämällä tarvitsemamme kentät riveille, sarakkeille ja arvoalueille:

Jos lähdetiedot muuttuvat tulevaisuudessa tai muutama kauppalehti lisätään, riittää, että päivität kyselyn ja yhteenvedon komennolla Päivitä kaikki kieleke Päiväys (Data – Päivitä kaikki).

Tapa 2. Yhdistämme taulukot UNION SQL -komennolla makrossa

Toinen ratkaisu ongelmamme on tämä makro, joka luo tietojoukon (välimuisti) pivot-taulukolle komennolla UNITY SQL-kyselykieli. Tämä komento yhdistää taulukot kaikista taulukossa määritellyistä SheetNames kirjan arkit yhdeksi tietotaulukoksi. Toisin sanoen sen sijaan, että kopioimme ja liitämme fyysisesti alueita eri arkeilta yhdelle, teemme saman tietokoneen RAM-muistissa. Sitten makro lisää uuden arkin annetulla nimellä (muuttuja ResultSheetName) ja luo siitä täyden (!) yhteenvedon kerätyn välimuistin perusteella.

Jos haluat käyttää makroa, käytä Visual Basic -painiketta välilehdellä kehittäjä (Kehittäjä) tai pikanäppäimellä muut+F11. Sitten lisäämme uuden tyhjän moduulin valikon kautta Insert – moduuli ja kopioi sinne seuraava koodi:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames Kuten Variant 'taulukon nimi, jossa tuloksena oleva taulukko RevolutionP tulee näkyviin nimet lähdetaulukoilla SheetsNames = Array("Alfa", "Beta", "Gamma", "Delta") 'muodostamme välimuistin taulukoille SheetsNames-taulukoista ActiveWorkbookilla ReDim arSQL(1 To (UBound(SheetsNames) + 1)) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Seuraavaksi i Aseta objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Lopeta 'luo taulukko uudelleen näyttääksesi tuloksena olevan pivot-taulukon Virheestä Jatka seuraavaan sovellukseen.DisplayAlerts = Väärät laskentataulukot(ResultSheetName).Poista Aseta wsPivot = Työarkit.Lisää wsPivo t. Name = ResultSheetName 'näytä luotu välimuistin yhteenveto tässä taulukossa. Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Aseta objRS = Ei mitään komennolla wsPivot objananR3 Set(CreateWestCache)."CreateWestCache. objPivotCache = Ei mitään Range("A3").Valitse Lopeta End With End Sub    

Valmis makro voidaan sitten suorittaa pikanäppäimellä muut+F8 tai välilehden Makrot-painiketta kehittäjä (Kehittäjä – makrot).

Tämän lähestymistavan haitat:

• Tietoja ei päivitetä, koska välimuistilla ei ole yhteyttä lähdetaulukoihin. Jos muutat lähdetietoja, sinun on suoritettava makro uudelleen ja laadittava yhteenveto uudelleen.
• Kun muutat arkkien määrää, on tarpeen muokata makrokoodia (array SheetNames).
  

Mutta lopulta saamme todellisen täysimittaisen pivot-taulukon, joka on rakennettu useille eri levyille:

Voilà!

Tekninen huomautus: Jos saat virheilmoituksen, kuten "Toimittaja ei rekisteröity", kun suoritat makroa, sinulla on todennäköisesti 64-bittinen Excel-versio tai Officen epätäydellinen versio on asennettu (ei pääsyä). Korjaa tilanne korvaamalla makrokoodin fragmentti:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

että:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Ja lataa ja asenna ilmainen tietojenkäsittelymoottori Accessista Microsoftin verkkosivustolta – Microsoft Access Database Engine 2010 Redistributable

Tapa 3: konsolidoi ohjattu pivot-taulukko Excelin vanhoista versioista

Tämä menetelmä on hieman vanhentunut, mutta silti mainitsemisen arvoinen. Muodollisesti kaikissa versioissa vuoteen 2003 saakka, ohjatussa pivot-taulukon toiminnossa oli vaihtoehto "rakentaa pivot useille konsolidointialueille". Tällä tavalla rakennettu raportti on kuitenkin valitettavasti vain säälittävä näyte todellisesta täysimittaisesta yhteenvedosta, eikä se tue monia tavanomaisten pivot-taulukoiden "siruja":

Tällaisessa pivotissa kenttäluettelossa ei ole sarakeotsikoita, joustavaa rakenneasetusta ei ole, käytettävien funktioiden joukko on rajoitettu, ja yleensä tämä kaikki ei ole kovin samanlainen kuin pivot-taulukko. Ehkä tästä syystä Microsoft poisti vuodesta 2007 alkaen tämän toiminnon vakiovalintaikkunasta pivot-taulukkoraportteja luodessaan. Nyt tämä ominaisuus on käytettävissä vain mukautetun painikkeen kautta Pivot-taulukon ohjattu toiminto(Pivot Table Wizard), joka voidaan haluttaessa lisätä Quick Access Toolbariin kautta Tiedosto – Asetukset – Mukauta pikatyökaluriviä – Kaikki komennot (Tiedosto — Asetukset — Mukauta pikatyökaluriviä — Kaikki komennot):

Kun olet napsauttanut lisättyä painiketta, sinun on valittava sopiva vaihtoehto ohjatun toiminnon ensimmäisessä vaiheessa:

Valitse sitten seuraavassa ikkunassa kukin alue vuorotellen ja lisää se yleiseen luetteloon:

Mutta jälleen kerran, tämä ei ole täysi tiivistelmä, joten älä odota siltä liikaa. Voin suositella tätä vaihtoehtoa vain hyvin yksinkertaisissa tapauksissa.

• Raporttien luominen pivot-taulukoilla
• Määritä laskelmat Pivot-taulukoissa
• Mitä makrot ovat, miten niitä käytetään, minne kopioidaan VBA-koodi jne.
• Tiedonkeruu useilta arkeilta yhdeksi (PLEX-lisäosa)