Sisu
Probleemi sõnastamine
Pivot-tabelid on Exceli üks hämmastavamaid tööriistu. Kuid siiani ei suuda kahjuks ükski Exceli versioon käigupealt teha nii lihtsat ja vajalikku asja nagu kokkuvõtte koostamine mitme algandmevahemiku kohta, mis asuvad näiteks erinevatel lehtedel või erinevates tabelites:
Enne alustamist selgitame paar punkti. A priori usun, et meie andmetes on täidetud järgmised tingimused:
- Tabelites võib olla suvaline arv ridu mis tahes andmetega, kuid neil peab olema sama päis.
- Lähtetabelitega lehtedel ei tohiks olla lisaandmeid. Üks leht – üks laud. Juhtimiseks soovitan kasutada kiirklahvi Ctrl+Lõpp, mis viib teid töölehel viimati kasutatud lahtrisse. Ideaalis peaks see olema andmetabeli viimane lahter. Kui klõpsate Ctrl+Lõpp iga tabeli paremal või all olev tühi lahter on esile tõstetud – kustuta need paremal olevad tühjad veerud või tabeli järel olevad read tabeli all ja salvesta fail.
1. meetod: looge Power Query abil pivoti tabelid
Alates Exceli 2010. aasta versioonist on olemas tasuta Power Query lisandmoodul, mis suudab koguda ja teisendada mis tahes andmeid ning seejärel anda need allikaks pivot-tabeli koostamiseks. Meie probleemi lahendamine selle lisandmooduli abil pole sugugi keeruline.
Kõigepealt loome Excelis uue tühja faili – selles toimub montaaž ja seejärel luuakse sellesse pivot tabel.
Siis vahekaardil kuupäev (kui teil on Excel 2016 või uuem versioon) või vahekaardil Toite päring (kui teil on Excel 2010–2013) valige käsk Loo päring – failist – Excel (Andmete hankimine – failist – Excel) ja määrake lähtefail koos kogutavate tabelitega:
Ilmuvas aknas valige suvaline leht (pole vahet, milline) ja vajutage allolevat nuppu Muutma (Edit):
Power Query päringuredaktori aken peaks avanema Exceli kohal. Paneeli akna paremal küljel Päringu parameetrid kustutage kõik automaatselt loodud sammud, välja arvatud esimene - allikas (Allikas):
Nüüd näeme kõigi lehtede üldist loendit. Kui failis on lisaks andmelehtedele veel mõned kõrvallehed, siis selles etapis on meie ülesandeks valida ainult need lehed, millelt on vaja infot laadida, välistades kõik teised, kasutades tabeli päises olevat filtrit:
Kustutage kõik veerud, välja arvatud veerg kuupäevparemklõpsates veeru pealkirja ja valides Kustutage muud veerud (Eemalda muud veerud):
Seejärel saate kogutud tabelite sisu laiendada, klõpsates veeru ülaosas topeltnoolel (märkeruut Kasutage eesliitena algset veeru nime saate selle välja lülitada):
Kui tegite kõik õigesti, peaksite siin nägema kõigi üksteise alla kogutud tabelite sisu:
Jääb üle tõsta esimene rida nupuga tabeli päisesse Kasutage esimest rida päistena (Kasutage esimest rida päistena) tab Avaleht (Kodu) ja eemaldage andmetest dubleerivad tabelipäised filtri abil:
Salvestage kõik käsuga tehtu Sulgege ja laadige – sulgege ja laadige sisse… (Sule ja laadi – sulge ja laadi…) tab Avaleht (Kodu)ja valige avanevas aknas suvand Ainult ühendus (Ainult ühendus):
Kõik. Jääb vaid teha kokkuvõte. Selleks minge vahekaardile Sisesta – PivotTable (Sisesta – Pivot Table), valige suvand Kasutage välist andmeallikat (Kasutage välist andmeallikat)ja seejärel klõpsates nuppu Valige Ühendus, meie palve. Pivoti edasine loomine ja konfigureerimine toimub täiesti standardsel viisil, lohistades meile vajalikud väljad ridade, veergude ja väärtuste alale:
Kui lähteandmed edaspidi muutuvad või lisandub veel paar poelehte, siis piisab päringu ja meie kokkuvõtte värskendamisest käsuga Värskenda kõike tab kuupäev (Andmed – värskenda kõiki).
Meetod 2. Ühendame tabelid UNION SQL käsuga makros
Teine meie probleemi lahendus on see makro, mis loob käskluse abil pivot-tabeli jaoks andmestiku (vahemälu) UNITY SQL päringu keel. See käsk ühendab kõigi massiivi määratud tabelite SheetNames raamatu lehed ühtseks andmetabelisse. See tähendab, et erinevatelt lehtedelt ühele vahemike füüsilise kopeerimise ja kleepimise asemel teeme sama arvuti RAM-is. Seejärel lisab makro uue lehe antud nimega (muutuja ResultSheetName) ja loob selle põhjal kogutud vahemälu põhjal täisväärtusliku (!) kokkuvõtte.
Makro kasutamiseks kasutage vahekaardil nuppu Visual Basic arendaja (Arendaja) või klaviatuuri otsetee muu+F11. Seejärel sisestame menüü kaudu uue tühja mooduli Sisesta – moodul ja kopeerige sinna järgmine kood:
Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As Long Dim Dim objPivotCache As PivotCache Dim ObjRS Nagu Object Dim ResultSheetName As String Dim SheetsNames As Variant 'lehe nimi, kus kuvatakse tulemuseks olev leht Result nimed lähtetabelitega SheetsNames = Array("Alfa", "Beeta", "Gamma", "Delta") 'moodustame tabelite vahemälu lehtedest SheetsNames koos ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1)) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Järgmiseks i Set 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 ) Lõpp '-ga looge leht uuesti, et kuvada saadud liigendtabel. Tõrke korral Jätka järgmine rakendus.DisplayAlerts = Vale töölehed(ResultSheetName).Kustuta Määra wsPivot = Töölehed.Lisa wsPivo t. Name = ResultSheetName 'Kuva sellel lehel loodud vahemälu kokkuvõte Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Määra objPivotCache.Recordset = objRS Määra objRS = Mitte midagi Koos wsPivot objPivot=t3 SetA:tRtAin."CreateCache. objPivotCache = Nothing Range ("A3"). Valige End With End Sub Valmis makro saab seejärel käivitada kiirklahviga muu+F8 või vahekaardil makrod arendaja (Arendaja – makrod).
Selle lähenemisviisi miinused:
- Andmeid ei värskendata, kuna vahemällu pole lähtetabelitega ühendust. Kui muudate lähteandmeid, peate makro uuesti käivitama ja koostama uuesti kokkuvõtte.
- Lehtede arvu muutmisel on vaja redigeerida makrokoodi (massiivi SheetNames).
Kuid lõpuks saame tõelise täisväärtusliku pöördtabeli, mis on üles ehitatud mitmele vahemikule erinevatest lehtedest:
Viola!
Tehniline märkus: Kui makro käivitamisel kuvatakse tõrketeade, näiteks „Pakkuja pole registreeritud”, on teil tõenäoliselt Exceli 64-bitine versioon või installitud on Office'i mittetäielik versioon (juurdepääsu pole). Olukorra parandamiseks asendage makrokoodi fragment:
Pakkuja=Microsoft.Jet.OLEDB.4.0;
kuni:
Pakkuja=Microsoft.ACE.OLEDB.12.0;
Laadige alla ja installige Microsofti veebisaidilt Accessi tasuta andmetöötlusmootor – Microsoft Access Database Engine 2010 Redistributable
3. meetod: konsolideerige PivotTable-liigendtabeli viisard Exceli vanadest versioonidest
See meetod on veidi vananenud, kuid siiski väärt mainimist. Ametlikult öeldes oli kõigis versioonides kuni 2003. aastani (kaasa arvatud) PivotTable-liigendtabeli viisardis võimalus "ehitada pöördepunkt mitmele konsolideerimisvahemikule". Kuid sellisel viisil koostatud aruanne on kahjuks vaid haletsusväärne näidis tõelisest täisväärtuslikust kokkuvõttest ega toeta paljusid tavapäraste pivot-tabelite "kiipe".
Sellises pivotis pole väljaloendis veergude pealkirju, paindlikku struktuuriseadet, kasutatavate funktsioonide hulk on piiratud ja üldiselt pole see kõik väga sarnane pivot-tabeliga. Võib-olla sellepärast eemaldas Microsoft alates 2007. aastast selle funktsiooni pivot-tabeli aruannete loomisel standarddialoogist. Nüüd on see funktsioon saadaval ainult kohandatud nupu kaudu PivotTable-liigendtabeli viisard(Pivot Table Wizard), mille saab soovi korral kiirpääsuribale lisada kaudu Fail – Valikud – Kiirjuurdepääsu tööriistariba kohandamine – Kõik käsud (Fail — Valikud — Kohanda kiirjuurdepääsu tööriistariba — Kõik käsud):
Pärast lisatud nupul klõpsamist peate viisardi esimeses sammus valima sobiva valiku:
Seejärel valige järgmises aknas iga vahemik järjest ja lisage see üldloendisse:
Kuid jällegi ei ole see täielik kokkuvõte, nii et ärge oodake sellelt liiga palju. Seda võimalust saan soovitada ainult väga lihtsatel juhtudel.
- Aruannete loomine PivotTable-liigendtabelitega
- Seadistage PivotTable-liigendtabelites arvutused
- Mis on makrod, kuidas neid kasutada, kuhu kopeerida VBA koodi jne.
- Andmete kogumine mitmelt lehelt ühele (PLEXi lisandmoodul)