Wanneer u in een Excel werkblad maandelijks de omzet bijhoudt, om zo een historisch bestand te hebben, kunt u ook automatisch een grafiek maken die steeds de laatste 13 maanden laat zien. Dat kan automatisch en zonder VBA. U gebruikt daarvoor dynamische bereiknamen met de functie VERSCHUIVING (OFFSET).
In het volgende plaatje ziet u een uitsnede uit de tabel:
U gaat als volgt te werk:
- U maakt een dynamische bereiknaam DataX aan via Formules > Gedefinieerde namen > Namen beheren > Nieuw
- Voer de naam DataX in en bij verwijst naar voert u de volgende formule in:
=VERSCHUIVING(Data!$A$1;AANTALARG(Data!$A:$A)-1;0;-13;1) - In mijn geval heet het werkblad Data.
- U maakt een dynamische bereiknaam DataY aan via Formules > Gedefinieerde namen > Namen beheren > Nieuw
- Voer de naam DataY in en bij verwijst naar voert u de volgende formule in:
=VERSCHUIVING(DataX;0;1) - U markeert cellen A2 tot en met B7. Daarna selecteert u Invoegen > Grafieken > Gegroepeerde kolom.
- Klik op een van de staven in de grafiek. In de formulebalk ziet u de volgende formule staan:
=REEKS(;Data!$A$2:$A$7;Data!$B$2:$B$7;1) - U dient Data!$A$2:$A$7 te vervangen door naam werkmap!DataX
- en Data!$B$2:$B$7 door naam werkmap!DataY
- Let op: zorg dat de naam van de werkmap geen spaties bevat !
De grafiek ziet er als volgt uit:
In ons bestand is de laatste datum feb-15. Derhalve ziet u de tijdlijn lopen van feb-14 tot en met feb-15. Als de volgende maand de omzet van mrt-15 wordt toegevoegd, zal de grafiek automatisch de set laten zien van mrt-14 tot en met mrt-15.
Toelichting VERSCHUIVING functies:
DataX =VERSCHUIVING(Data!$A$1;AANTALARG(Data!$A:$A)-1;0;-13;1)
- Begin bij cel A1
- Ga zoveel rijen naar beneden als het aantal argumenten berekent in kolom A minus 1: daarmee belandt u in de laatste gevulde maand
- Ga nul kolommen naar rechts: u blijft dus op dezelfde locatie
- De hoogte is 13 rijen naar boven
- En de breedte is 1
- Daarmee hebt u de laatste 13 perioden gemarkeerd
DataY =VERSCHUIVING(DataX;0;1)
- Begin bij bereik DataX
- Ga nul rijen naar beneden: u blijft op dezelfde locatie
- Ga 1 kolom naar rechts
- Daarmee markeert u de bijbehorende laatste 13 omzetcijfers.
Auteur: Tony De Jonker
Excel-deskundige Tony de Jonker geeft tips en trucs, afkomstig van vragen uit de praktijk. Hij wil deze graag met u delen, zodat ook u er uw voordeel mee kunt doen.
Excel Experience Day 2019
Tony De Jonker is consultant en eigenaar van De Jonker Consultancy. Hij helpt bedrijven met specifieke kennis op het snijvlak van business/finance en het ontwikkelen van Office applicaties. Hij zal één van de Excel-experts zijn op de Excel Experience day op 12 december 2019 te Utrecht. De Excel Experience Day is dé jaarlijkse place-to-be voor de startende én ervaren Excelgebruikers die alles uit Excel willen halen!
U werkt dagelijks met Excel en weet hoe oneindig veel mogelijkheden en toepassingen deze tool biedt. Hoe kunt u nog sneller, slimmer en efficiënter omgaan met Excel?
Grijp uw kans om in één dag uw Excelkennis een boost te geven en ontdek nieuwe mogelijkheden, toepassingen en tips!
Dit kunt u verwachten:
| Ontmoet dé absolute experts op het gebied van Excel en Power BI: Microsoft keynote Itai Goldstein, Tom Ysewyn, David Loomans én MVP’s Tony De Jonker, Jan Karel Pieterse, Henk Vlootman en Michiel Rozema. | |
| Tijdbesparende en verrassende tips & trucs die u direct in uw eigen Excel-sheets kunt gebruiken. | |
| Keuze uit 12 experience sessies op alle niveau’s. | |
| Een breed scala aan functionaliteiten komen aan bod: functies & formules, draaitabellen, voorwaardelijke opmaak, Power Pivot, Power BI, … er is voor elk wat wils. | |
| Een experience op een bijzondere locatie: Pathé Utrecht Leidsche Rijn. |
