Gratis tips Excel - Dynamische keuzelijst en verticaal zoeken in een dynamisch gebied (1)


Tips Excel		go to : Index

Tip 44 - Deel 1
Deze Tip werd geschreven door Valentin, waarvoor mijn dank.

Dynamische keuzelijst en verticaal zoeken in een dynamisch gebied (1)
In Excel clip tip 046 wordt besproken hoe je een keuzelijst (dropdown) kan maken. In Cursus Excel 2007 Les 37 wordt Verticaal zoeken besproken.

In deze tip bespreken we: 1. De dynamische keuzelijst. 2. Verticaal zoeken in een dynamisch gebied. Wat is een dynamische keuzelijst? Met een dynamisch keuzelijst bedoelen we dat wanneer we meer gegevens aan een lijst (kolom of rij) toevoegen of verwijderen, deze wijziging ook automatisch in de keuzelijst doorgevoerd wordt.

Het voorbeeldbestand kan je hier downloaden

De dynamische keuzelijst.
Neem de gegevens over van de cellen A1 tot en met B10 onderstaande afbeelding.

We gaan een dynamische keuzelijst maken van de gegevens van kolom A.
We maken hiervoor gebruik van een formule met de functies VERSCHUIVING en AANTALARG.

De functie VERSCHUIVING maakt gebruik van 5 parameters nl. verwijzing, rijen, kolommen, [hoogte], [breedte].
Verwijzing is vereist. De verwijzing ten opzichte waarvan de verschuiving moet plaatsvinden. verwijzing moet een verwijzing zijn naar een cel of een bereik van aangrenzende cellen. Als dit niet het geval is, geeft VERSCHUIVING de foutwaarde #WAARDE! als resultaat.

Rijen is vereist. Het aantal rijen, omhoog of omlaag, waarnaar u de cel in de linkerbovenhoek wilt laten verwijzen. Als u bijvoorbeeld 5 opgeeft, komt de cel in de linkerbovenhoek van de resulterende verwijzing vijf rijen onder de verwijzing. rijen kan zowel een positief getal (oftewel een getal onder de uitgangsverwijzing) als een negatief getal zijn (oftewel een getal boven de uitgangsverwijzing).

Kolommen is vereist. Het aantal kolommen, naar links of naar rechts, waarnaar u de cel in de linkerbovenhoek wilt laten verwijzen. Als u bijvoorbeeld 5 opgeeft, komt de cel in de linkerbovenhoek van de resulterende verwijzing vijf kolommen rechts van de verwijzing. kolommen kan zowel een positief getal (oftewel een getal rechts van de uitgangsverwijzing) als een negatief getal zijn (oftewel een getal links van de uitgangsverwijzing).

Hoogte is optioneel. De hoogte, uitgedrukt in een aantal rijen, die u wilt toekennen aan de resulterende verwijzing. hoogte moet een positief getal zijn.

Breedte is optioneel. De breedte, uitgedrukt in een aantal kolommen, die u wilt toekennen aan de resulterende verwijzing. breedte moet een positief getal zijn.

De functie AANTALARG (aantal argumenten) telt het aantal niet-lege cellen in een bereik.

We beginnen met onze formule samen te stellen in een willekeurige cel, zo kunnen we beroep dan op de formule invoerhulp (de formulehulp hebben we niet ter beschikking wanneer we de formule willen samenstellen in het dialoogvenster “Nieuwe naam”, dat besproken wordt in punt 12).

We typen =VERSCHUIVING
De eerste parameter Verw(1) is de eerste cel van de dynamische lijst.


We klikken in cel A2, drukken de functietoets F4 (we maken de celverwijzing absoluut) typen puntkomma, de formule is nu: =VERSCHUIVING($A$2;

Voor de tweede parameter RIJEN(2) moeten we opgeven hoeveel rijen ten opzichte van de verwijzing we moeten verschuiven, vermits we ten opzichte van cel A2 geen rijen moeten verschuiven typen we een nul en vervolgens een puntkomma.


De formule is nu: =VERSCHUIVING($A$2;0;

Voor de derde parameter KOLOMMEN(3) moeten we opgeven hoeveel kolommen ten opzichte van de verwijzing we moeten verschuiven, vermits we ten opzichte van cel A2 geen kolommen moeten verschuiven typen we een nul en vervolgens een puntkomma.


De formule is nu: =VERSCHUIVING($A$2;0;0;

Voor de vierde parameter HOOGTE(4) moeten we de hoogte opgeven van het cellenbereik dat geselecteerd moet worden (met hoogte wordt bedoeld: uit hoeveel rijen moet het geselecteerde gebied bestaan)


Als we zien naar het voorbeeld is de hoogte 9 rijen nl. van A2 tot en met A10, we kunnen als vierde parameter (Hoogte) 9 opgeven, maar als er in de onderliggende rijen waarden worden ingegeven is de hoogte niet meer juist. We gaan om de hoogte te bepalen gebruik maken van de functie AANTALARG, we typen achter de laatste puntkomma AANTALARG( We moeten nu als parameter waarde1(5)een bereik selecteren. Als bereik nemen we de volledige kolom A. We klikken op de kolomkop van kolom A drukken de functietoets F4 en typen het sluitende haakje


De formule tot nu: =VERSCHUIVING($A$2;0;0;AANTALARG($A:$A)

Selecteer in de formulebalk het gedeelte AANTALARG($A:$A) in de formule en druk de functietoets F9, en als resultaat van de functie krijgen we 10(6).


De functie AANTALARG telt het aantal niet-lege cellen in een bereik, vermits we de hele kolom selecteren voor de functie AANTALARG wordt dus cel A1 wordt meegeteld. We zoeken echter het aantal cellen met gegevens vanaf cel A2 naar beneden, dus alle cellen die en gegevens bevatten en boven de eerste parameter van VERSCHUIVING (Verw) staan mogen niet meegerekend worden, in dit voorbeeld betreft het 1 cel nl. A1 die en gegevens bevat en boven de parameter Verw staat, we trekken deze waarde dan ook af van AANTALARG. Druk CTRL-Z, de formule wordt terug =VERSCHUIVING($A$2;0;0;AANTALARG($A:$A) en plaats de cursor achter het sluitende haakje van de formule. We typen achter het sluitende haakje -1 (min 1) en een puntkomma.

Als laatste parameter van de functie VERSCHUIVING (Breedte)(7)wordt gevraagd naar de breedte van het gebied (met breedte wordt bedoeld: uit hoeveel kolommen moet het geselecteerde gebied bestaan), vermits deze parameter optioneel en we geen bijkomende kolommen selecteren kunnen we deze weglaten.


Druk eenmaal Backspace (de puntkomma wordt gewist)en typ het sluitende haakje.

De formule is nu: =VERSCHUIVING($A$2;0;0;AANTALARG($A:$A)-1)


Selecteer in de formulebalk de hele formule, klik met de rechtermuisknop en selecteer Kopieren en druk ENTER. (Opgelet de formule geeft nu een foutmelding vermits de betreffende door de formule geselecteerde cellen A2 tot en met A10 niet kunnen weergegeven in de cel waarin de formule is samengesteld) Ga op het lint naar “Formules”(8) en klik op “Namen beheren”(9), of druk CTRL+F3.


We gaan verder in deel 2

Deel 1 - Deel 2 - Deel 3

Index