Waarde eenmaal kiezen in keuzelijst – Deel 1

Op vraag van Maurice en Harm heeft Valentin volgende tip geschreven.
In Excel tip 041 wordt uitgelegd hoe je vermijdt dat er duplicaten worden ingetypt, in deze tip bespreken we hoe je duplicaten vermijdt door gebruik te maken van een keuzelijst, die verkleint bij het maken van een keuze.

Wat we willen verkrijgen is wanneer we een naam kiezen uit de keuzelijst(1), dat die naam dan verwijderd wordt uit de keuzelijst.

We beginnen eerst met het maken van een lijst van de werknemers in de cellen F3 tot en met F22(2) (in dit voorbeeld 20 werknemers).

Dan gaan we bepalen wat er moet komen in de cellen H3 tot en met H22 wanneer er een naam gekozen wordt uit de keuzelijst.
We typen de volgende formule in cel H3:
=ALS(OF($C$3:$C$22=F3);F3;””) en drukken CTRL+SHIFT+ENTER, de formule wordt nu een matrixformule  {=ALS(OF($C$3:$C$22=F3);F3;””)}.
Met nog steeds cel H3 geselecteerd kopiëren we met de vulgreep de formule naar beneden tot en met cel H22(3)

Nu de formule voor de namen die nog niet gebruikt zijn

We typen in cel I3:
=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))))
En 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))))}

Hoe werkt deze formule?
het eerste gedeelte
ALS(RIJEN($I$3:I3)>AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22) ;””
De functie RIJEN geeft als resultaat het aantal rijen in een verwijzing.
De functie AANTALARG telt het aantal niet-lege cellen in een bereik.
Als het aantal rijen van het bereik I3 tot en met I3 (= 1 rij) groter is dan AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22) geef dan als resultaat NIETS, als we de formule naderhand naar beneden kopiëren verandert de formule =ALS(RIJEN(I$3:I22)>AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22);””
Als het aantal rijen van het bereik I3 tot en met I22 (= 20 rijen) groter is dan AANTALARG($F$3:$F$22)-AANTALARG($C$3:$C$22) geef dan als resultaat NIETS.

Vb. voor cel I22: als in cel C10 een naam gekozen is uit de lijst (in dit voorbeeld: Marc).
Als het aantal rijen van het bereik I3 tot en met I22 (=20)(4) groter is dan AANTALARG($F$3:$F$22) (=20)(5) -AANTALARG($C$3:$C$22) (=-1) (6) (=19)geef dan als resultaat NIETS(7).
Het tweede gedeelte van de formule, dus als niet aan de ALS voorwaarde wordt voldaan,
We nemen als voorbeeld de formule voor cel I3 (dus de eerste rij van het cellengebied  I3 tot en met I22)
INDEX($F$3:$F$22;KLEINSTE(ALS($H$3:$H$22=””;RIJ($H$3:$H$22)-RIJ($H$3)+1);RIJEN($I$3:I3))))

De functie =INDEX geeft als resultaat een waarde of de verwijzing naar een waarde vanuit een tabel of bereik.
De syntaxis van de functie INDEX heeft de volgende parameter: INDEX(matrix, rij_getal,[kolom_getal]).
Parameter :1 Matrix   =vereist. Een cellenbereik of een matrixconstante.

Als matrix slechts één rij of kolom bevat, is het bijbehorende argument rij_getal of kolom_getal optioneel.

Als matrix meerdere rijen en kolommen bevat en alleen rij_getal of kolom_getal wordt gebruikt, geeft INDEX als resultaat een matrix van de gehele rij of kolom in matrix.

Parameter 2: rij_getal   =vereist. Selecteert de rij in matrix waaruit een waarde moet worden opgehaald. Als u rij_getal weglaat, is kolom_getal een verplicht argument.

Parameter 3: kolom_getal   =optioneel. Selecteert de kolom in matrix waaruit een waarde moet worden opgehaald. Als u kolom_getal weglaat, is rij_getal een verplicht argument.

INDEX($F$3:$F$22 = het cellenbereik waaruit we de namen moeten ophalen.
voor de tweede parameter van index, rij getal gebruiken we de formule:
KLEINSTE(ALS($H$3:$H$22=””;RIJ($H$3:$H$22)-RIJ($H$3)+1);RIJEN($I$3:I3)
De functie =KLEINSTE bepaalt de op k-1 na kleinste waarde in een gegevensbereik. Gebruik deze functie om waarden met een bepaalde relatieve positie in een gegevensverzameling op te halen.
De functie =KLEINSTE heeft 2 parameters
Parameter 1:matrix  =vereist. Een matrix of een bereik met numerieke gegevens waarin u de op k-1 na kleinste waarde wilt bepalen.

Parameter 2: k  =vereist. De positie (geteld vanaf de kleinste waarde) in de matrix of het cellenbereik met gegevens.
Een voorbeeld van de functie =KLEINSTE
In onderstaand voorbeeld hebben we in het cellenbereik A1 tot en met A10 een reeks getallen, en in cel C1 de formule =KLEINSTE(A1:A10;3).
De formule werkt als volgt: geef uit het cellengebied A1 tot en met A10 de derde kleinste waarde.

Ter informatie: swotster.com denkt aan zijn cursisten, denk ook aan swotster.com

We gaan verder in Deel 2

Deel 1 – Deel 2 – Deel 3

Cursussen

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats