Draaitabel over meerdere gegevensbereiken

Formulering van het probleem

Draaitabellen zijn een van de meest verbazingwekkende tools in Excel. Maar tot nu toe kan helaas geen van de versies van Excel zo'n eenvoudig en noodzakelijk ding doen als een samenvatting maken voor verschillende initiële gegevensbereiken die zich bijvoorbeeld op verschillende bladen of in verschillende tabellen bevinden:

Voordat we beginnen, laten we een paar punten verduidelijken. A priori ben ik van mening dat in onze gegevens aan de volgende voorwaarden is voldaan:

• Tabellen kunnen een willekeurig aantal rijen met alle gegevens hebben, maar ze moeten dezelfde kop hebben.
• Er mogen geen extra gegevens op de bladen met brontabellen staan. Eén blad - één tafel. Om te controleren, raad ik je aan om een ​​sneltoets te gebruiken Ctrl+Einde, waarmee u naar de laatst gebruikte cel in het werkblad gaat. Idealiter zou dit de laatste cel in de gegevenstabel moeten zijn. Als u op . klikt Ctrl+Einde elke lege cel rechts of onder de tabel wordt gemarkeerd - verwijder deze lege kolommen rechts of rijen onder de tabel na de tabel en sla het bestand op.

Methode 1: Maak tabellen voor een spil met Power Query

Vanaf de 2010-versie voor Excel is er een gratis Power Query-invoegtoepassing die alle gegevens kan verzamelen en transformeren en deze vervolgens als bron voor het bouwen van een draaitabel kan geven. Het oplossen van ons probleem met behulp van deze invoegtoepassing is helemaal niet moeilijk.

Laten we eerst een nieuw leeg bestand in Excel maken - daarin vindt montage plaats en vervolgens wordt er een draaitabel in gemaakt.

Dan op het tabblad Data (als je Excel 2016 of later hebt) of op het tabblad Power Query (als je Excel 2010-2013 hebt) selecteer de opdracht Query maken – Van bestand – Excel (Gegevens ophalen — Uit bestand — Excel) en specificeer het bronbestand met de tabellen die moeten worden verzameld:

Selecteer in het venster dat verschijnt een willekeurig blad (het maakt niet uit welke) en druk op de onderstaande knop Veranderen (Edit):

Het venster Power Query Query-editor zou bovenop Excel moeten openen. Aan de rechterkant van het venster op het paneel Verzoek om parameters verwijder alle automatisch gemaakte stappen behalve de eerste - bron (Bron):

Nu zien we een algemene lijst van alle bladen. Als er naast gegevensbladen nog enkele andere zijbladen in het bestand zijn, is het onze taak om in deze stap alleen die bladen te selecteren waaruit informatie moet worden geladen, met uitzondering van alle andere die het filter in de tabelkop gebruiken:

Verwijder alle kolommen behalve kolom Datadoor met de rechtermuisknop op een kolomkop te klikken en te selecteren Andere kolommen verwijderen (Verwijderen andere kolommen):

U kunt dan de inhoud van de verzamelde tabellen uitbreiden door op de dubbele pijl bovenaan de kolom te klikken (selectievakje Gebruik de originele kolomnaam als voorvoegsel je kan het uitzetten):

Als je alles goed hebt gedaan, zou je op dit punt de inhoud van alle tabellen onder elkaar moeten zien:

Het blijft over om de eerste rij naar de tabelkop te verhogen met de knop Gebruik de eerste regel als koptekst (Gebruik de eerste rij als kopteksten) tab Home (Home) en verwijder dubbele tabelkoppen uit de gegevens met behulp van een filter:

Bewaar alles gedaan met het commando Sluiten en laden – Sluiten en laden in… (Sluiten & laden — Sluiten & laden naar...) tab Home (Home)en selecteer in het geopende venster de optie Alleen verbinding (Alleen verbinding):

Alles. Het blijft alleen om een ​​​​samenvatting te maken. Ga hiervoor naar het tabblad Invoegen – draaitabel (Invoegen — draaitabel), kies de optie Externe gegevensbron gebruiken (Gebruik externe gegevensbron)en vervolgens door op de knop te klikken Verbinding selecteren, ons verzoek. Verdere creatie en configuratie van de spil gebeurt op een volledig standaard manier door de velden die we nodig hebben naar het rijen, kolommen en waardengebied te slepen:

Als de brongegevens in de toekomst veranderen of als er nog een paar winkelbladen worden toegevoegd, is het voldoende om de query en onze samenvatting bij te werken met de opdracht Ververs alles tab Data (Gegevens — Alles vernieuwen).

Methode 2. We verenigen tabellen met het UNION SQL-commando in een macro

Een andere oplossing voor ons probleem wordt weergegeven door deze macro, die een gegevensset (cache) voor de draaitabel maakt met behulp van de opdracht EENHEID SQL-querytaal. Deze opdracht combineert tabellen van alle gespecificeerd in de array Bladnamen vellen van het boek in een enkele gegevenstabel. Dat wil zeggen, in plaats van het fysiek kopiëren en plakken van reeksen van verschillende bladen naar één, doen we hetzelfde in het RAM van de computer. Vervolgens voegt de macro een nieuw blad toe met de opgegeven naam (variabele ResultaatBladnaam) en maakt er een volwaardige (!) samenvatting van op basis van de verzamelde cache.

Om een ​​macro te gebruiken, gebruikt u de Visual Basic-knop op het tabblad ontwikkelaar (Ontwikkelaar) of sneltoets anders+F11. Dan voegen we een nieuwe lege module in via het menu Invoegen – Module en kopieer daar de volgende code:

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 As Variant 'bladnaam waar het resulterende draaipunt wordt weergegeven ResultSheetName = "Pivot" 'een array van blad namen met brontabellen SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'we vormen een cache voor tabellen van sheets uit SheetsNames Met ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Vervolgens stel ik objRS = CreateObject("ADODB.Recordset") objRS in .Open Join$( arSQL, "UNION ALL"), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Eindigen met 'maak het blad opnieuw om de resulterende draaitabel weer te geven Bij fout Hervat volgende toepassing.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'toon de gegenereerde cache-samenvatting op dit blad Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:3") objPivotCache = Nothing Range ("A3"). Selecteer End With End Sub    

De voltooide macro kan vervolgens worden uitgevoerd met een sneltoets anders+F8 of de knop Macro's op het tabblad ontwikkelaar (Ontwikkelaar — Macro's).

Nadelen van deze aanpak:

• De gegevens worden niet bijgewerkt omdat de cache geen verbinding heeft met de brontabellen. Als u de brongegevens wijzigt, moet u de macro opnieuw uitvoeren en de samenvatting opnieuw maken.
• Bij het wijzigen van het aantal vellen is het noodzakelijk om de macrocode (array Bladnamen).
  

Maar uiteindelijk krijgen we een echte volwaardige draaitabel, gebouwd op verschillende reeksen van verschillende bladen:

Voila!

Technische notitie: als je een foutmelding krijgt zoals "Provider niet geregistreerd" bij het uitvoeren van de macro, dan heb je hoogstwaarschijnlijk een 64-bits versie van Excel of een onvolledige versie van Office is geïnstalleerd (geen toegang). Om de situatie op te lossen, vervangt u het fragment in de macrocode:

	 Aanbieder=Microsoft.Jet.OLEDB.4.0;  

tot:

	Aanbieder=Microsoft.ACE.OLEDB.12.0;  

En download en installeer de gratis gegevensverwerkingsengine van Access van de Microsoft-website – Microsoft Access Database Engine 2010 Redistributable

Methode 3: Draaitabelwizard consolideren vanuit oude versies van Excel

Deze methode is een beetje achterhaald, maar toch het vermelden waard. Formeel gezien was er in alle versies tot en met 2003 een optie in de PivotTable Wizard om “een spil te bouwen voor meerdere consolidatiebereiken”. Een rapport dat op deze manier is samengesteld, zal helaas slechts een jammerlijke schijn zijn van een echte volwaardige samenvatting en ondersteunt niet veel van de "chips" van conventionele draaitabellen:

In zo'n pivot zijn er geen kolomkoppen in de lijst met velden, is er geen flexibele structuurinstelling, is het aantal gebruikte functies beperkt en lijkt dit alles over het algemeen niet erg op een draaitabel. Misschien is dat de reden waarom Microsoft deze functie vanaf 2007 uit het standaarddialoogvenster heeft verwijderd bij het maken van draaitabelrapporten. Deze functie is nu alleen beschikbaar via een aangepaste knop Wizard Draaitabel(Wizard draaitabel), die desgewenst kan worden toegevoegd aan de werkbalk Snelle toegang via Bestand – Opties – Werkbalk Snelle toegang aanpassen – Alle opdrachten (Bestand — Opties — Werkbalk Snelle toegang aanpassen — Alle opdrachten):

Nadat u op de toegevoegde knop hebt geklikt, moet u bij de eerste stap van de wizard de juiste optie selecteren:

En selecteer vervolgens in het volgende venster elk bereik om de beurt en voeg het toe aan de algemene lijst:

Maar nogmaals, dit is geen volwaardige samenvatting, dus verwacht er niet te veel van. Ik kan deze optie alleen in zeer eenvoudige gevallen aanbevelen.

• Rapporten maken met draaitabellen
• Berekeningen instellen in draaitabellen
• Wat zijn macro's, hoe ze te gebruiken, waar VBA-code te kopiëren, enz.
• Gegevensverzameling van meerdere bladen naar één (PLEX add-on)