Heeft u geen Power Pivot? Maakt niet uit. Het grootste deel van Power Pivot is ingebouwd in Excel 2013 en zelfs meer in Excel 2016. Vandaag is onze tip van Ash: tabellen samenvoegen in een draaitabel.
Zeven weken lang speel ik elke woensdag een van de favoriete tips van Ash Sharma. Ash is een productmanager in het Excel-team. Zijn team brengt draaitabellen en vele andere goede dingen. Tegenwoordig is Ash's favoriete functie het samenvoegen van meerdere gegevenssets met behulp van relaties en het gegevensmodel.
Stel dat uw IT-afdeling u de dataset geeft die wordt weergegeven in de kolommen A: D. Er zijn velden voor klant en markt. U moet bepaalde markten in regio's combineren. Elke klant behoort tot een sector. Regio en sector bevinden zich niet in de oorspronkelijke gegevens, maar u hebt opzoektabellen om deze informatie te verstrekken.
Normaal gesproken zou u de gegevens afvlakken door VERT.ZOEKEN te gebruiken om gegevens van de oranje en gele tabellen naar de blauwe tabel te halen. Maar aangezien het sleutelveld zich niet aan de linkerkant van elke tabel bevindt, moet u ofwel overschakelen naar INDEX en MATCH, of de opzoektabellen opnieuw rangschikken.
Vanaf Excel 2013 kunt u de opzoektabellen laten waar ze zijn en ze combineren in het draaitabelrapport zelf.
Om deze techniek te laten werken, moeten alle drie de tabellen als tabel zijn opgemaakt. Selecteer een cel in elke gegevensset en kies Home, Formaat als tabel of druk op Ctrl + T. De drie tabellen zullen in eerste instantie Tabel1, Tabel2 en Tabel3 heten. Ik gebruik het tabblad Ontwerp van tabelfuncties van het lint en geef elke tabel een nieuwe naam. Ik verander ook de kleur van elke tafel. In dit voorbeeld wordt de blauwe tabel Data genoemd. De oranje tabel is RegionTable. De gele tabel is SectorTable.
Opmerking
Sommigen zullen je vertellen dat je geeky namen zoals Fact, TblSector en TblRegion moet gebruiken. Als iemand je zo lastigvalt, steel dan gewoon zijn zakbeschermer en laat hem weten dat je de voorkeur geeft aan Engels klinkende namen.
Om de naam van een tabel te wijzigen, typt u een nieuwe naam in het vak aan de linkerkant van het tabblad Hulpmiddelen voor tabellen Ontwerpen. Tabelnamen mogen geen spaties bevatten.
Zodra de drie tabellen zijn gedefinieerd, gaat u naar het tabblad Gegevens en klikt u op Relaties.
Klik in het dialoogvenster Relaties beheren op Nieuw. Geef in het dialoogvenster Relatie maken op dat het veld Klant van de gegevenstabel is gerelateerd aan het veld Klant van de SectorTable. Klik OK.
Definieer nog een nieuwe relatie tussen het veld Markt in de velden Gegevens en Regiotabel. Nadat u beide relaties hebt gedefinieerd, ziet u ze in het dialoogvenster Relaties beheren.
Gefeliciteerd: je hebt zojuist een datamodel in je werkmap gebouwd. Het is tijd om een draaitabel te bouwen.
Selecteer de lege cel waar u uw draaitabel wilt laten verschijnen. Standaard kiest het dialoogvenster Draaitabel maken Gebruik het gegevensmodel van deze werkmap. De locatie van de draaitabel is standaard de cel die u hebt gekozen. Klik OK.
In de lijst met draaitabelvelden worden alle drie de tabellen weergegeven. Gebruik de driehoek aan de linkerkant van een tabel om de naam van de tabel uit te vouwen, zodat u de velden kunt zien.
Vouw de gegevenstabel uit. Selecteer het veld Opbrengst. Het gaat automatisch naar het gebied Waarden. Vouw de SectorTable uit. Kies het veld Sector. Het gaat naar het gebied Rijen. Vouw de RegionTable uit. Sleep het veld Regio naar het gebied Kolommen. U hebt nu een draaitabel met een samenvatting van de gegevens uit de drie tabellen.
Opmerking
In elk boek dat ik tot nu toe heb geschreven, gebruik ik een andere techniek om dit rapport op te bouwen. Nadat ik de drie tabellen heb gedefinieerd, kies ik cel A1 en Invoegen, draaitabel. Ik vink het vakje aan voor Deze gegevens toevoegen aan het gegevensmodel. Selecteer in de lijst Draaitabelvelden de optie Alles boven aan de lijst. Kies velden voor het rapport en definieer vervolgens de relaties achteraf. De hierboven beschreven techniek lijkt soepeler en vereist eigenlijk een klein beetje vooruit plannen. De mensen die Option Explicit in hun VBA-code gebruiken, zouden deze methode zeker leuk vinden.
Door de relaties in het datamodel voelt Excel meer aan als Access of SQL Server, maar dan met al het goede van Excel.
Ik vraag het Excel-team graag naar hun favoriete functies. Elke woensdag zal ik een van hun antwoorden delen. Met dank aan Ash Sharma voor het geven van dit idee.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Zoek niet op of je een relatie hebt"
John Michaloudis
