Stel je voor dat we een aantal verkoopcijfers hebben voor een bedrijf:
En dat we de totaalcijfers voor het jaar tot nu toe willen vinden. We kunnen een vervolgkeuzelijst als volgt toevoegen:
Zodat we de huidige maand kunnen specificeren. Daarom willen we nu het jaar tot nu toe voor maart uitwerken. Het eenvoudigste formaat zou zijn om formules te hebben die zich over het hele bereik uitstrekken:
En dan zouden we de formules gewoon elke maand veranderen.
Excel laat echter een andere benadering toe. We kunnen een dynamisch bereik instellen waarvan de grootte varieert met de maand waarin we ons bevinden. Als we de maand in de vervolgkeuzelijst wijzigen, verandert de grootte van het bereik.
Dus voor de maand maart is het bereik 3 kolommen lang, en voor de maand juni zou het 6 maanden zijn.
De grootte van het assortiment wordt bepaald door de maand. Een manier om dit te formuleren is door de functie Maand te gebruiken:
=Maand(c8)
Waar c8 het celadres is van onze vervolgkeuzelijst. De voorkeursmethode is echter om de MATCH-functie te gebruiken om de positie van de huidige maanden in alle maanden in ons rapport te bepalen:
MATCH(c8,$c$3:$j$3,0)
Waar:
• c8 is het celadres van de huidige maand
• C3:J3 is het adres van al onze maanden
• 0 is om een exacte overeenkomst te garanderen
Nu kunnen we de grootte van ons dynamisch bereik specificeren met de OFFSET-functie die 5 argumenten heeft:
=OFFSET(referentie, rijen, kolommen, hoogte, breedte)
Waar:
• Referentie is de linkerbovenhoek van ons dynamisch bereik - cel C5 - de eerste cel die we willen optellen
• Rijen - het aantal rijen lager dan onze basiscel - dit is 0
• Cols - het aantal cols tegenover onze basisoproep - dit is 0
• De breedte van ons dynamisch bereik - in dit geval 3. Omdat we echter willen dat het bereik per maand varieert, zullen we hier onze MATCH-formules plaatsen
• Dit is de hoogte van ons dynamisch bereik dat 1 . is
Onze OFFSET-formules zijn dus:
= OFFSET(c5,0,0,MATCH(c8,$c$3:$j$3,0),1)
Ten slotte moeten we Excel vertellen om dit te SOM om de volledige formules te geven als:
= SOM(OFFSET(c5,0,0,MATCH(c8,$c$3:$j$3,0),1))
Wij hebben:
Als we nu de maand in de vervolgkeuzelijst wijzigen, stroomt het juiste jaar tot nu toe door:
Aangezien dit een automatische update is, heeft deze aanpak de volgende voordelen:
• Het is niet nodig om de formules elke maand te wijzigen
• Omdat er minder formulewijzigingen zijn, is er minder ruimte voor fouten
• De spreadsheet kan worden gebruikt door iemand met beperkte Excel-kennis - ze kunnen gewoon de vervolgkeuzelijst wijzigen en hebben geen last van formules
