Përmbajtje
Formulimi i problemit
Tabelat kryesore janë një nga mjetet më të mahnitshme në Excel. Por deri më tani, për fat të keq, asnjë nga versionet e Excel nuk mund të bëjë një gjë kaq të thjeshtë dhe të nevojshme në fluturim sa ndërtimi i një përmbledhjeje për disa diapazon të të dhënave fillestare të vendosura, për shembull, në fletë të ndryshme ose në tabela të ndryshme:
Para se të fillojmë, le të sqarojmë disa pika. A priori, besoj se në të dhënat tona plotësohen kushtet e mëposhtme:
- Tabelat mund të kenë çdo numër rreshtash me çdo të dhënë, por ato duhet të kenë të njëjtin kokë.
- Nuk duhet të ketë të dhëna shtesë në fletët me tabela burimore. Një fletë - një tryezë. Për të kontrolluar, unë ju këshilloj të përdorni një shkurtore të tastierës Ctrl+fund, e cila ju zhvendos në qelizën e fundit të përdorur në fletën e punës. Në mënyrë ideale, kjo duhet të jetë qeliza e fundit në tabelën e të dhënave. Nëse kur klikoni mbi Ctrl+fund çdo qelizë boshe në të djathtë ose poshtë tabelës është e theksuar - fshini këto kolona boshe në të djathtë ose rreshtat poshtë tabelës pas tabelës dhe ruani skedarin.
Metoda 1: Ndërtoni tabela për një pivot duke përdorur Power Query
Duke filluar nga versioni 2010 për Excel, ekziston një shtesë falas e Power Query që mund të mbledhë dhe transformojë çdo të dhënë dhe më pas t'i japë ato si burim për ndërtimin e një tabele kryesore. Zgjidhja e problemit tonë me ndihmën e kësaj shtese nuk është aspak e vështirë.
Së pari, le të krijojmë një skedar të ri bosh në Excel - montimi do të bëhet në të dhe më pas do të krijohet një tabelë kryesore në të.
Pastaj në skedën Data (nëse keni Excel 2016 ose më vonë) ose në skedën Kërkesa e energjisë (nëse keni Excel 2010-2013) zgjidhni komandën Krijo pyetje - Nga skedari - Excel (Merr të dhëna - Nga skedari - Excel) dhe specifikoni skedarin burimor me tabelat që do të mblidhen:
Në dritaren që shfaqet, zgjidhni çdo fletë (nuk ka rëndësi se cila) dhe shtypni butonin më poshtë Ndryshim (Edit):
Dritarja e Redaktuesit të Query Query duhet të hapet në krye të Excel. Në anën e djathtë të dritares në panel Kërkoni Parametrat fshini të gjithë hapat e krijuar automatikisht përveç të parës - Burim (Burimi):
Tani shohim një listë të përgjithshme të të gjitha fletëve. Nëse përveç fletëve të të dhënave ka disa fletë të tjera anësore në skedar, atëherë në këtë hap detyra jonë është të zgjedhim vetëm ato fletë nga të cilat duhet të ngarkohet informacioni, duke përjashtuar të gjitha të tjerat duke përdorur filtrin në kokën e tabelës:
Fshini të gjitha kolonat përveç kolonës Dataduke klikuar me të djathtën në një titull kolone dhe duke zgjedhur Fshi kolonat e tjera (Hiq kolona të tjera):
Më pas mund të zgjeroni përmbajtjen e tabelave të mbledhura duke klikuar në shigjetën e dyfishtë në krye të kolonës (kutia e kontrollit Përdorni emrin origjinal të kolonës si parashtesë mund ta fikni):
Nëse keni bërë gjithçka në mënyrë korrekte, atëherë në këtë pikë duhet të shihni përmbajtjen e të gjitha tabelave të mbledhura njëra poshtë tjetrës:
Mbetet për të ngritur rreshtin e parë në kokën e tabelës me butonin Përdorni rreshtin e parë si kokë (Përdorni rreshtin e parë si kokë) tab Fillimi (Shtëpi) dhe hiqni titujt e tabelave të kopjuara nga të dhënat duke përdorur një filtër:
Ruani gjithçka që është bërë me komandën Mbyll dhe ngarko – Mbyll dhe ngarko në… (Mbyll & Ngarko - Mbylle & Ngarko në…) tab Fillimi (Shtëpi), dhe në dritaren që hapet, zgjidhni opsionin Vetëm lidhje (Vetëm lidhje):
Gjithçka. Mbetet vetëm për të ndërtuar një përmbledhje. Për ta bërë këtë, shkoni te skeda Insert – PivotTable (Fut - Tabela kryesore), zgjidhni opsionin Përdorni burimin e jashtëm të të dhënave (Përdor burimin e jashtëm të të dhënave)dhe më pas duke klikuar butonin Zgjidhni lidhjen, kërkesa jonë. Krijimi dhe konfigurimi i mëtejshëm i pivotit ndodh në një mënyrë krejtësisht standarde duke tërhequr fushat që na duhen në rreshtat, kolonat dhe zonën e vlerave:
Nëse të dhënat e burimit ndryshojnë në të ardhmen ose shtohen disa fletë të tjera dyqani, atëherë do të mjaftojë të përditësoni pyetjen dhe përmbledhjen tonë duke përdorur komandën Rifresko të gjitha tab Data (Të dhënat - Rifresko të gjitha).
Metoda 2. Ne bashkojmë tabelat me komandën UNION SQL në një makro
Një zgjidhje tjetër për problemin tonë përfaqësohet nga kjo makro, e cila krijon një grup të dhënash (cache) për tabelën kryesore duke përdorur komandën UNITY Gjuha e pyetjes SQL. Kjo komandë kombinon tabela nga të gjitha të specifikuara në grup Emrat e fletëve fletët e librit në një tabelë të vetme të dhënash. Kjo do të thotë, në vend që të kopjojmë dhe ngjitim fizikisht vargjet nga fletë të ndryshme në një, ne bëjmë të njëjtën gjë në RAM-in e kompjuterit. Pastaj makro shton një fletë të re me emrin e dhënë (ndryshore Emri i fletës së rezultateve) dhe krijon një përmbledhje të plotë (!) mbi të bazuar në cache-in e mbledhur.
Për të përdorur një makro, përdorni butonin Visual Basic në skedë zhvillues (Zhvilluesi) ose shkurtore të tastierës Ndalet+F11. Pastaj futim një modul të ri bosh përmes menysë Insert – Moduli dhe kopjoni kodin e mëposhtëm atje:
Sub New_Multi_Table_Pivot() Dim i Sa Long Dim arSQL() Si String Dim objPivotCache Si PivotCache Dim objRS Si Object Dim ResultSheetName Si String Dim SheetsEmrat si variant 'emri i fletës ku strumbullari rezultues do të shfaqet N' ResultSheet' emrat me tabela burimore SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'ne formojmë një cache për tabelat nga fletët nga SheetsNames With ActiveWorkbook ReDim arSQL(1 në (UBound(SeetsNames) + 1) ) Për i = LBound (SeetsNames) To UBound(SeetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Tjetra i Set objRS = CreateObject("ADODB.Recordset") objRS .Hap Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Burimi i të dhënave=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Fund me 'ri-krijo fletën për të shfaqur tabelën kryesore që rezulton Në gabim Rifillo Aplikacionin tjetër.DisplayAlerts = Fletë pune të rreme(ResultSheetName).Fshi Set wsPivot = Fletët e punës.Shto wsPivo t. Emri = ResultSheetName 'shfaq përmbledhjen e cache-it të krijuar në këtë fletë Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Asgjë me wsPivotacheter. objPivotCache = Nothing Range("A3").Zgjidh End With End Sub Makroja e përfunduar më pas mund të ekzekutohet me një shkurtore të tastierës Ndalet+F8 ose butonin Macros në skedën zhvillues (Zhvilluesi - Makro).
Disavantazhet e kësaj qasjeje:
- Të dhënat nuk përditësohen sepse cache nuk ka lidhje me tabelat burimore. Nëse ndryshoni të dhënat e burimit, duhet të ekzekutoni përsëri makro dhe të ndërtoni përsëri përmbledhjen.
- Kur ndryshoni numrin e fletëve, është e nevojshme të redaktoni kodin makro (arrit Emrat e fletëve).
Por në fund marrim një tabelë të vërtetë të plotë, të ndërtuar në disa vargje nga fletë të ndryshme:
Voila!
Shënim teknik: nëse ju merrni një gabim si "Ofruesi nuk është i regjistruar" gjatë ekzekutimit të makro, atëherë ka shumë të ngjarë që ju keni një version 64-bit të Excel ose një version jo të plotë të Office është instaluar (pa Access). Për të rregulluar situatën, zëvendësoni fragmentin në kodin makro:
Provider=Microsoft.Jet.OLEDB.4.0;
për:
Provider=Microsoft.ACE.OLEDB.12.0;
Dhe shkarkoni dhe instaloni motorin falas të përpunimit të të dhënave nga Access nga faqja e internetit e Microsoft - Microsoft Access Database Engine 2010 i rishpërndarë
Metoda 3: Konsolidoni Magjistarin e PivotTable nga versionet e vjetra të Excel
Kjo metodë është pak e vjetëruar, por ende vlen të përmendet. Duke folur zyrtarisht, në të gjitha versionet deri dhe duke përfshirë 2003, ekzistonte një opsion në Magjistarin e PivotTable për të "ndërtuar një strumbullar për disa vargje konsolidimi". Sidoqoftë, një raport i ndërtuar në këtë mënyrë, për fat të keq, do të jetë vetëm një pamje e dhimbshme e një përmbledhjeje reale të plotë dhe nuk mbështet shumë nga "çipet" e tabelave kryesore të zakonshme:
Në një strumbullar të tillë, nuk ka tituj kolonash në listën e fushave, nuk ka cilësim fleksibël të strukturës, grupi i funksioneve të përdorura është i kufizuar dhe, në përgjithësi, e gjithë kjo nuk është shumë e ngjashme me një tabelë kryesore. Ndoshta kjo është arsyeja pse, duke filluar nga viti 2007, Microsoft e hoqi këtë funksion nga dialogu standard kur krijon raporte të tabelave kryesore. Tani kjo veçori është e disponueshme vetëm përmes një butoni të personalizuar Magjistari i Tabelës Pivot(Magjistari i tabelës rrotulluese), e cila, nëse dëshironi, mund të shtohet në Shiritin e Veglave të Qasjes së Shpejtë nëpërmjet Skedari – Opsionet – Personalizo shiritin e veglave të aksesit të shpejtë – Të gjitha komandat (Skedari — Opsionet — Personalizo shiritin e veglave të aksesit të shpejtë — Të gjitha komandat):
Pasi të klikoni në butonin e shtuar, duhet të zgjidhni opsionin e duhur në hapin e parë të magjistarit:
Dhe më pas në dritaren tjetër, zgjidhni çdo varg me radhë dhe shtojeni atë në listën e përgjithshme:
Por, përsëri, kjo nuk është një përmbledhje e plotë, ndaj mos prisni shumë prej saj. Unë mund ta rekomandoj këtë opsion vetëm në raste shumë të thjeshta.
- Krijimi i raporteve me PivotTables
- Vendosni llogaritjet në PivotTables
- Çfarë janë makro, si t'i përdorin ato, ku të kopjoni kodin VBA, etj.
- Mbledhja e të dhënave nga fletë të shumta në një (shtesa PLEX)