Om de laatste waarde in een dynamische lijst op te halen, gebruiken we de optie Gegevensvalidatie samen met de functies OFFSET en COUNTA in Microsoft Excel 2010/2013.
AANTAL: Het retourneert de telling van het aantal cellen dat waarden bevat.
Syntaxis van de functie "COUNTA": =COUNTA (waarde1, waarde2, waarde3… .)
Voorbeeld: In het bereik A1:A5 bevatten de cellen A2, A3 en A5 de waarden en zijn de cellen A1 en A4 leeg. Selecteer de cel A6 en schrijf de formule-
=AANTALLEN(A1:A5) de functie keert terug 3
OFFSET: Het retourneert een verwijzing naar een bereik dat een aantal rijen en kolommen uit een ander bereik of cel verschoven is.
Syntaxis van OFFSET-functie: =OFFSET (referentie, rijen, kolommen, hoogte, breedte)
Verwijzing:- Dit is de cel of het bereik waarvan u wilt compenseren.
Rijen en kolommen om te verplaatsen: - Hoeveel rijen u het startpunt wilt verplaatsen en beide kunnen positief, negatief of nul zijn.
Hoogte en breedte: - Dit is de grootte van het assortiment dat u wilt retourneren. Dit is een optioneel veld.
Laten we een voorbeeld nemen om de functie Offset in Excel te begrijpen.
We hebben gegevens in het bereik A1:D10. Kolom A bevat Productcode, kolom B bevat Hoeveelheid, kolom C bevat kosten per product en kolom D bevat Totale kosten. We moeten de waarde van cel C5 in cel E2 retourneren.
Volg de onderstaande stappen.
- Selecteer de cel E2 en schrijf de formule.
- =VERSCHUIVING(A1,4,2,1,1)en druk op Enter op het toetsenbord.
- De functie retourneert de waarde van cel C5.
In dit voorbeeld moeten we de waarde van cel C5 naar E2 halen. Onze referentiecel is de eerste cel in het bereik dat A1 is en C5 is 4 rijen lager en 2 kolommen rechts van A1. Daarom is de formule =OFFSET(A1,4,2,1,1) of =OFFSET(A1,4,2) (aangezien 1,1 optioneel is).
Laten we nu een voorbeeld nemen om de laatste waarde in een dynamische lijst op te halen.
We hebben landnamen in een reeks. Als we nu meer landen aan deze lijst toevoegen, zou deze automatisch beschikbaar moeten zijn in de vervolgkeuzelijst.
Om een dynamische lijst voor te bereiden, moeten we een formule maken die de laatste waarde in de kolom ophaalt en automatisch bijwerkt wanneer een nieuw nummer wordt toegevoegd.
Volg de onderstaande stappen: -
- Selecteer de cel B2.
- Ga naar het tabblad Gegevens, selecteer Gegevensvalidatie in de groep Gegevenshulpmiddelen.
- Het dialoogvenster "Gegevensvalidatie" verschijnt. Selecteer op het tabblad "Instellingen" "Aangepast" in de vervolgkeuzelijst Toestaan.
- Het formulevak wordt geactiveerd.
- Schrijf de formule in dit vak.
- =OFFSET(A:A,1,0,COUNTA(A:A)-1,1).
- Klik op OK.
- In dit stadium is de laatst bijgewerkte cel A11.
- Om te controleren of de gegevensvalidatie goed werkt, voegt u een plaatsnaam toe in cel A12.
Zodra u een invoer toevoegt in A12, wordt deze toegevoegd aan de vervolgkeuzelijst.
Dit is de manier waarop u een dynamische lijst kunt maken en er automatisch nieuwe items in kunt vullen in Microsoft Excel 2010 en 2013.
