Gratis tips Excel - Waarde eenmaal kiezen in keuzelijst (2)


Tips Excel		go to : Index

Tip 68
Deze Tip werd geschreven door Valentin, waarvoor mijn dank.

Waarde eenmaal kiezen in keuzelijst - Deel 2
We moeten een manier vinden om de plaats die de namen innemen in het cellengebied F3 tot en met F22 en die nog niet gebruikt zijn een numerieke waarde te geven. We doen dat met de rest van de formule: ALS($H$3:$H$22="";RIJ($H$3:$H$22)-RIJ($H$3)+1);RIJEN($I$3:I3) Het eerste deel: ALS($H$3:$H$22=""; Als de cellen H3 tot en met H22 leeg zijn, (vergeet niet dat dit een matrixformule is, dus het geheel van het cellenbereik wordt berekent) geeft dit als resultaat : {WAAR;WAAR;WAAR;WAAR;ONWAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR; WAAR;WAAR;WAAR;WAAR;WAAR;WAAR;WAAR} De ONWAAR komt van de naam ”Marc” in cel H7 in ons voorbeeld. Cel H3 =leeg, H4=leeg, H5 =leeg, H6 =leeg, H7=niet leeg, H8 =leeg, enz. Van het tweede deel: RIJ($H$3:$H$22)-RIJ($H$3)+1) bekijken we eerst het gedeelte RIJ($H$3:$H$22) Dit geeft de matrix {3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22} De functie =RIJ geeft als resultaat het rijnummer geteld vanaf rij 1(vanaf cel H1). We moeten echter in onze index (F3:F22) kiezen tussen 1 en 20 ( de 20 namen ). We moeten dus een matrix verkrijgen van 1 tot 20, dat doen we met -RIJ($H$3)+1) we trekken het rijnummer van cel H3 ( =3) af van alle getallen in de matrix, dat geeft dan {0,1,2,3,4,5,6,7,8,9,10;11;12;13;14;15;16;17;18;19} en tellen vervolgens bij alle getallen in de matrix 1 bij, resultaat: {1,2,3,4,5,6,7,8,9,10;11;12;13;14;15;16;17;18;19,20} Het resultaat van het Als gedeelte van de formule: ALS($H$3:$H$22="";RIJ($H$3:$H$22)-RIJ($H$3)+1) geeft dan:
{1;2;3;4;ONWAAR;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20} Cel F3 heeft dan een numerieke waarde van 1, F4 numerieke waarde van 2, cel F5 heeft dan een numerieke waarde van 3, cel F6 heeft dan een numerieke waarde van 4, F7 heeft geen numerieke waarde, cel F8 heeft dan een numerieke waarde van 6, enz. Nu moeten we de inhoud van cellen die een numerieke waarde hebben overbrengen naar I3 tot en met I23. We doen dat met de tweede parameter van =KLEINSTE, voor cel I3 is dat RIJEN($I$3:I3) van I3 tot I3 =1 (De functie RIJEN geeft als resultaat het aantal rijen in een verwijzing.) Wanneer we de formule naar beneden kopiëren wordt dat voor cel I4 RIJEN($I$3:I4) van I3 tot I4 =2, enz.
Dus de kleinste waarde gaat naar I3, de tweede kleinste waarde gaat naar I4, enz. Voor cel I3 is de formule dus: =ALS(RIJEN($I$3:I3)>AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22);""; INDEX($F$3:$F$22;KLEINSTE(ALS($H$3:$H$22="";RIJ($H$3:$H$22)-RIJ($H$3)+1);RIJEN($I$3:I3)))) We drukken CTRL+SHIFT+ENTER de formule verandert nu in een matrixformule: {=ALS(RIJEN($I$3:I3)>AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22);""; INDEX($F$3:$F$22;KLEINSTE(ALS($H$3:$H$22="";RIJ($H$3:$H$22)-RIJ($H$3)+1);RIJEN($I$3:I3))))} Vervolgens kopiëren we de formule naar beneden tot en met cel I22.

Nu moeten we nog onze dynamische keuzelijst maken voor de cellen C3 tot en met C22. We doen dat 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.

Een voorbeeld: ik heb in cel A1 de formule getypt =VERSCHUIVING(A7;-4;3;2;2)
Dit wil zeggen: ga vanuit cel A7 4 rijen omhoog, 3 kolommen naar rechts, de hoogte is 2 cellen en de breedte is 2 cellen
Resultaat: een matrix van de waarden die in he cellengebied C3 tot en met D4 staan. ={"B"\"C";"F"\"G"} De schuine streep \ in het resultaat is het scheidingsteken voor kolommen en de puntkomma ; voor rijen.


De formule voor de dynamische keuzelijst: typ in een lege cel (maakt niet uit welke, zo kunnen we weer gebruik maken van de formule invoerhulp) de volgende formule: =VERSCHUIVING($I$3;0;0;AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22)) De eerste parameter ( verwijzing) = I3 De tweede parameter (rijen) = 0 De derde parameter (kolommen) = 0 Als vierde parameter (hoogte) gebruiken we de functie AANTALARG = AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22)) De vijfde parameter (breedte) is optioneel en gebruiken we niet.

Vervolgens selecteren en knippen we de formule vanuit de formulebalk(8), en we plaatsen de cursor in een andere cel.


We gaan op het lint naar “Formules”(9) en klikken op “Namen beheren”(10).

In het dialoogvenster “Namen beheren” klikken we op “Nieuw”(11).


In het volgende dialoogvenster “Nieuwe naam” wissen we de gegevens in het vak “Verwijst naar”(12) en plakken we de formule, en in het vak “Naam”(13) typen we een toepasselijke naam voor onze lijst. En klikken OK.


Ter informatie: GratisCursus.be denkt aan zijn cursisten, denk ook aan GratisCursus.be

>

We gaan verder in Deel 3
Deel 1 - Deel 2 - Deel 3



Index