Een inleiding tot dynamisch bereik
De functie VERT.ZOEKEN wordt vaak gebruikt om informatie te vinden die is opgeslagen in tabellen in Excel. Dus als we bijvoorbeeld een lijst hebben met namen en leeftijden van mensen:
En dan kunnen we in een nabijgelegen cel de functie VERT.ZOEKEN gebruiken om de leeftijd van Paul te bepalen:
Tot nu toe is dit een vrij standaard. Maar wat gebeurt er als we nog wat namen aan de lijst moeten toevoegen? De voor de hand liggende gedachte zou zijn om het bereik in de VERT.ZOEKEN te wijzigen. In een echt complex model kunnen er echter verschillende verwijzingen naar de VERT.ZOEKEN zijn. Dit betekent dat we elke referentie zouden moeten veranderen - ervan uitgaande dat we wisten waar ze waren.
Excel biedt echter een alternatieve manier - een DYNAMIC-reeks genoemd. Dit is een bereik dat een update automatisch uitbreidt. Dit is perfect als uw lijsten steeds groter worden (bijv. maand-op-maand verkoopgegevens).
Om een dynamisch bereik in te stellen, hebben we een bereiknaam nodig - dus we noemen de onze AGE_DATA. De aanpak voor het instellen van dynamische bereiken verschilt tussen Excel 2007 en eerdere versies van Excel:
Klik in Excel 2007 op "Naam definiëren" onder formules:
Klik in eerdere versies van Excel op "Invoegen" en vervolgens op Namen.
Voer in het pop-upvenster de naam van ons dynamisch bereik in - dit is "LEEFTIJDGEGEVENS":
In het vak met het label "Verwijst naar" moeten we het bereik van onze gegevens invoeren. Dit wordt bereikt door een OFFSET-functie. Dit heeft 5 argumenten:
=OFFSET(Referentie, Rijen, Kolommen, Hoogte, Breedte)
- De Referentie is het adres van de LINKERBOVENSTE hoek van ons assortiment - in dit geval cel B5
- De rijen is het aantal rijen vanaf LINKS BOVEN dat we willen dat dat bereik is - wat in dit geval 0 is
- De Cols is het aantal rijen vanaf LINKS BOVEN dat we willen dat dat bereik is - wat in dit geval 0 is
- De hoogte van het bereik - zie hiervoor hieronder
- De Breedte van het bereik - dit is 2, we hebben TWEE kolommen in ons assortiment (de naam van de persoon en hun leeftijd)
Nu zal de hoogte van het bereik moeten variëren, afhankelijk van het aantal vermeldingen in onze tabel (die momenteel 7 is).
Natuurlijk willen we een manier om de rijen in onze tabel op te tellen die automatisch worden bijgewerkt - dus een manier om dit te doen is door de COUNTA-functie te gebruiken. Dit telt gewoon het aantal niet-lege cellen in een bereik. Aangezien onze namen in kolom B staan, is het aantal vermeldingen in onze gegevens AANTAL (B:B).
Merk op dat als je dit in een cel zou plaatsen, je de waarde 8 zou krijgen - omdat het de kop Namen bevat. Dat het echter niet uitmaakt.
Dus in het vak "Verwijst naar" plaatsen we:
=OFFSET($B$5,0,0,counta(B:B),2)
En klik op de knop OK. Ons dynamisch bereik is nu gecreëerd.
Keer nu terug naar de VERT.ZOEKEN-formules en vervang het bereik $B:4:$C11 door de naam van ons nieuwe dynamische bereik AGE_DATA, zodat we hebben:
Tot nu toe is er niets veranderd. Als we echter nog een paar namen aan onze tabel toevoegen:
En in de cel waar we Paul hadden, vervang het door een nieuwe naam zoals Pedro (die niet op de originele lijst stond):
En we zien dat Excel automatisch Pedro's leeftijd heeft teruggegeven - ook al hebben we de VERT.ZOEKEN-formules niet gewijzigd. In plaats daarvan is de reikwijdte van het dynamisch bereik uitgebreid met de extra namen.
Dynamische bereiken zijn erg handig wanneer we toenemende hoeveelheden gegevens hebben, vooral wanneer VERT.ZOEKEN en PIVOT-tabellen vereist zijn.
