Toch is soms een soort draaitabel met teksten nodig. Daarvoor vind je hier een paar oplossingen.
Er is een nieuwere versie van dit blog waarbij een tekst-draaitabel gemaakt wordt m.b.v. Power Query. Deze is HIER te vinden. Het bestand werkt waarschijnlijk alleen in de Excel-desktop-versie onder Windows omdat de online- en de Mac-versie nog niet over voldoende mogelijkheden beschikken (augustus 2022)
Een echte draaitabel, waarbij je kunt dubbelklikken in het waarden gedeelte waardoor een nieuw werkblad met de detaillering wordt getoond is het niet. Wel kun je van een tabel met gegevens een soort draaitabel maken. Hieronder staan drie oplossingen.
Draaitabel met nummerkolom en twee tekstkolommen
Stel je hebt een aantal personen die werken met Excel. Sommige zijn beginners, andere zijn gevorderde, allrounder of expert. Deze informatie is verwerkt zoals in onderstaande tabel.
De wens is om een (draai)tabel te maken met vier categorie-kolommen waarin per kolom de juiste personen worden genoemd.
Maar hoe doe je dat? Je zult er voor moeten zorgen dat elke naam in een categorie een unieke waarde krijgt waardoor je het op een unieke plaats in de draaitabel kunt zetten.
Zoals je hiernaast al ziet staat links van de draaitabel een nummer-kolom. Zo kun je in de draaitabel gaan zoeken naar de eerste beginner, de tweede beginner enz.
In de tabel met namen en categorieën moet je daarom aangeven waar de eerste beginner staan, waar de tweede enz. Dat kan met de =AANTAL.ALS()-formule. Met deze formule kun je berekenen hoe vaak de categorie vanaf het begin tot aan de actieve rij vermeld staat. De categorieën staan in het bereik B4:B23. Als je in de cel C4 de hulpformule =AANTAL.ALS($B$4:B4;B4) plaatst en deze omlaag kopieerd, dan wordt bij de eerste "beginner" een 1 vermeld en bij de tweede "beginner" een 2 enz. Maar omdat dit ook voor de andere categorieën wordt vermeld heb je nog geen unieke code. Daarom wordt de formule uitgebreid met de naam van de categorie door in cel C4 de formule =B4&AANTAL.ALS($B$4:B4;B4). Zo onstaat een kolom met unieke teksten:
In cel G4 kan de volgende formule worden geplaatst: =INDEX($A$4:$A$23;VERGELIJKEN(G$3&$F4;$C$4:$C$23;0)). Dit levert echter bij "beginner12" een foutmelding omdat deze niet voor komt in de tabel. Daarom moet de formule nog uitgebreid worden met een =ALS.FOUT() formule waardoor de totale formule wordt: =ALS.FOUT(INDEX($A$4:$A$23;VERGELIJKEN(G$3&$F4;$C$4:$C$23;0));"").
Deze formule kan naar rechts en naar beneden gekopieerd worden. Zorg er voor dat er voldoende rijen met formules zijn, anders worden niet alle namen weergegeven. Je zou hiervoor de volgende formule in cel G2 kunnen plaatsen: =ALS(AANTALARG(G4:J23)<AANTALARG(A4:A23);"NIET ALLE NAMEN ZIJN ZICHTBAAR";"")
Draaitabel met twee tekstkolommen alfabetisch gesorteerd.
Bovenstaande methode laat de namen zien in de volgorde zoals ze in de invoertabel staan. Dat is soms lastig als je op zoek bent naar een bepaalde naam.
In cel C4 geeft de formule =AANTAL.ALS() aan of het de eerste, de tweede enz. waarde is met dezelfde tekst. Dat doen we door het aantal gelijke teksten te tellen vanaf cel C4 tot aan de actuele rij. Maar met deze formule kan ook de rang berekend worden van de actuele rij in het gehele bereik. Dit kan door te berekenen hoeveel teksten kleiner zijn dat de actuele tekst.
De formule =AANTAL.ALS($A$4:$A$23;"<="&A4) berekent hoeveel namen kleiner zijn dan de naam in de actuele rij.
Als we willen kijken naar de categorie en de alfabetische volgorde binnen de betreffende categorie, dan moeten we zoeken naar twee waarden in twee verschillende kolommen. Dat kan met de formule =AANTALLEN.ALS().
In het voorbeeldbestand, dat hieronder te downloaden is, staat een tweede hulpkolom in kolom D. In cel D4 staat de formule: =B4&AANTALLEN.ALS($B$4:$B$23;B4;$A$4:$A$23;"<="&A4)
In de hulpkolom zie je dat de volgorde van de nummering in kolom D anders is dan in kolom C.
En zo krijg je met dezelfde formules in de "draaitabel" een andere volgorde van namen, namelijk per kolom gesorteerd op alfabet.
Draaitabel met drie tekstkolommen
Stel dat naast de namen en de categorieën ook nog een kolom met bijvoorbeeld de regio aanwezig is en dat dus niet op nummer-categorie maar op regio-categorie een draaitabel gemaakt moet worden.
Het gevaar hierbij is dat er geen unieke code meer is en dat de combinatie van categorie en regio meerdere keren voor zou kunnen komen. Alleen als de combinatie van categorie en regio maar één keer voor komt, dan zou de bijbehorende naam moeten worden vermeld. En als de combinatie vaker voor komt, dan zou er een waarschuwing getoond moeten worden.
Een hulpkolom is nodig om de combinatie van categorie en regio te maken. Dat kan eenvoudig met het &-teken (=B4&C4).
Daarna moeten we in de "draaitabel" eerst uitzoeken of de combinatie vaker voor komt. Zo ja, dan wordt de tekst "MEERDERE" getoond en anders wordt de persoonnaam met =INDEX() en =VERGELIJKEN() opgezocht.
De formule in cel G4 wordt hier: =ALS.FOUT(ALS(AANTAL.ALS($D$4:$D$23;G$3&$F4)>1;"MEERDERE";INDEX($A$4:$A$23;VERGELIJKEN(G$3&$F4;$D$4:$D$23;0)));""). Deze formule kan ook naar beneden en naar rechts gekopieerd worden.
Het gevaar dat een combinatie van categorie en regio meer dan eens voor komt, heb ik nog niet met een formule kunnen voorkomen. Ik zou graag met gegevens-validatie dat willen regelen zodat de gebruiker maar één keer een combinatie van twee velden kan invoeren maar dat is mij nog niet gelukt. Wel zou je met een voorwaardelijke opmaak ook weer kunnen tellen hoevaak de combinatie voor komt en als dit meer dan 1x voor komt, dan zou de opmaak kunnen wijzigen.
Het voorbeeld bestand kun je HIER downloaden.
En is iets niet duidelijk, stuur dan een berichtje via het contactformulier van mijn website.
