Draaitabelachtige rapporten met formules

De rijkoppen zijn de unieke regio's. Gebruik UNIQUE met SORT zodat ze omlaag overlopen in kolom E en gesorteerd blijven.

Plaats dit in E2:

De regio's vullen nu vanzelf E2 en de cellen eronder. Net als bij samenvattingstabellen vormt deze lijst het anker waarnaar het hele raster terugverwijst.

De kolomkoppen zijn de kwartalen die over een rij zijn verspreid. Je kunt Q1, Q2, Q3 en Q4 handmatig typen, of ze horizontaal laten overlopen door TRANSPOSE rond UNIQUE te zetten.

In F1 zet dit de unieke kwartalen bovenaan:

TRANSPOSE keert een verticale lijst om naar een horizontale, zodat een kolom met kwartalen een rij met koppen wordt. Nu staan beide assen van het raster klaar.

Vul nu het gegevensgedeelte. Elke cel heeft het totaal nodig voor de regio van zijn rij en het kwartaal van zijn kolom. SUMIFS verwerkt gemakkelijk twee voorwaarden.

Schrijf in de eerste cel van het gegevensgedeelte, F2:

Dit telt het Bedrag op wanneer Regio gelijk is aan het label links en Kwartaal gelijk is aan de kop erboven. Het is één kruispunt van de draaitabel.

De dollartekens zorgen ervoor dat je één formule door te kopiëren over het hele raster kunt vullen. Bestudeer de gemengde verwijzingen:

• $E2 vergrendelt de kolom op E, maar laat de rij verschuiven, zodat elke rij zijn eigen regio leest.
• F$1 vergrendelt de rij op 1, maar laat de kolom verschuiven, zodat elke kolom zijn eigen kwartaal leest.
• $C$2:$C$500 is volledig vergrendeld, omdat het gegevensbereik nooit verschuift.

Kopieer F2 over alle kwartalen en omlaag langs alle regio's; elke cel past zichzelf precies aan.

Als F2 correct is ingevoerd, selecteer je de cel en sleep je de vulgreep naar rechts over de kolommen met kwartalen en vervolgens omlaag langs de rijen met regio's. Excel herschrijft de relatieve delen voor je.

• Cel G2 wordt Regio $E2 en Kwartaal G$1.
• Cel F3 wordt Regio $E3 en Kwartaal F$1.

Het resultaat is een volledige kruistabel waarin elk kruispunt een totaal heeft. Je hebt geen draaitabelassistent nodig en de tabel wordt opnieuw berekend zodra de gegevens in Sales veranderen.

Een echte draaitabel toont eindtotalen. Voeg rechts een kolom Totaal en onderaan een rij Totaal toe met gewone SUM over elke rij.

Plaats voor het rijtotaal van de eerste regio dit in de kolom na het laatste kwartaal:

Voor een kolomtotaal tel je de cellen in het gegevensgedeelte van dat kwartaal over de rijen op. Deze totalen aan de randen maken het rapport compleet en laten lezers in één oogopslag controleren of de cijfers kloppen.

Als je programma dit ondersteunt, kun je het kopiëren vermijden door overloopverwijzingen rechtstreeks aan SUMIFS door te geven. Gebruik de overlopende koppen als criteria.

Deze ene formule berekent het totaal voor elk kruispunt van regio en kwartaal:

Hier is E2# de verticale regiolijst en F1# de horizontale kwartaalijst. Excel combineert ze in één keer tot een volledig raster. De methode met slepen is compatibeler, maar dit is de elegante, moderne versie.

Rapporten worden inzichtelijker wanneer ze het aandeel tonen, niet alleen de bedragen. Voeg een kolom toe waarin het totaal van elke regio als percentage van het eindtotaal wordt weergegeven.

Als het rijtotaal van de regio in J2 staat en het eindtotaal in J10, schrijf je:

Door het eindtotaal met $J$10 te vergrendelen, kun je de formule omlaag vullen voor alle regio's, terwijl steeds door dezelfde noemer wordt gedeeld. Stel de kolom in als percentage en lezers zien direct welke regio's het grootste aandeel hebben.

Een paar gewoonten houden een draaitabel met formules betrouwbaar:

• Verwijs naar ruime volledige bereiken, zoals rijen 2 tot en met 500, zodat nieuwe rijen worden meegenomen.
• Vergrendel gegevensbereiken met volledige $-ankers; alleen de verwijzingen naar koppen mogen verschuiven.
• Laat onder en rechts lege ruimte, zodat overlopende koppen en totalen plaats hebben.

Als je dit goed doet, heeft dit rapport geen onderhoud nodig. Voer nieuwe verkopen in en het raster, de totalen en de labels worden allemaal vanzelf bijgewerkt.

Je hebt een draaitabel nagebouwd met alleen formules:

• UNIQUE met SORT bouwde de rijkoppen op in een overlopende kolom.
• TRANSPOSE verspreidde de kolomkoppen over een rij.
• SUMIFS met de gemengde verwijzingen $E2 en F$1 vulde elk kruispunt, door te slepen of met overloopverwijzingen zoals E2# en F1#.
• SUM voegde randen met eindtotalen toe.

Het hele raster wordt direct opnieuw berekend. Vervolgens maak je het dashboard interactief met vervolgkeuzelijsten die de meetwaarden aansturen.