=ALS, zoeken in bereik maar lege cellen vermijden

[carloschouw]

Hoi,

Wie kan mij op weg helpen? Bekijk bijlage Map1.xlsx

Waar ik niet goed mee uit de voeten kan is:

Bovenste voorbeeld in de werkmap zijn de ingevoerde gegeven (rij 2 t/m 12) In rij 16 voer ik het weeknummer in waarvan ik de waarde van wil zien in rij 20 t/m...

Met een =ALS functie kan ik wel wat waarden naar voren halen maar het gaat sowieso over 52 weken in totaal (voorbeeld = 3 weken) Maar waar ik niet uit kom is:

Ik voer b.v. weeknummer 1 in, in B16. De cellen vanaf A20 zouden dan gevuld moeten worden met alle waarden uit kolom week 1 waarvan in MA t/m ZO (hele week dus per dag) waarvan er minimaal 1 dag een kenmerk "x" heeft. Het niet één MA t/m ZO een "x" dan moet deze waarde niet meegenomen worden. Er zou dus alleen waarden weergegeven moeten worden waarvan er een "x" vermeld staat.

Stel dat alleen MA een "x" heeft dan moeten de waarden van die cel incl DI t/m ZO (die dus geen waarde hebben) en alle waarde uit kolom A t/m J ingevuld moeten worden.

Hoop dat mijn uitleg enigszins duidelijk is... Iemand ervaring hiermee?

Groet, Carlo

 

[MarcelBeug]

Dit is ruimschoots uitgebreider dan de titel doet vermoeden...

Maar goed, ik heb een oplossing met 5 gedefinieerde namen en slechts 2 verschillende formules

De gedefinieerde namen zijn:

[B]AantalAfd:[/B] =SOM(--(DgPAfd>0))
[B]AfdIndex:[/B] =KLEINSTE(ALS(DgPAfd;RIJ(Blad1!$A$5:$A$12)-RIJ(Blad1!$A$5)+1);RIJEN(Blad1!T$20:T20))
[B]DgPAfd:[/B] =PRODUCTMAT(--(WkMat="x");{1\1\1\1\1\1\1})
[B]SKol:[/B] =VERGELIJKEN(Blad1!$B$16;Blad1!$3:$3;0)
[B]WkMat:[/B] =INDEX(Blad1!$5:$5;;SKol):INDEX(Blad1!$12:$12;;SKol+6)

In volgorde van berekening:
SKol is de StartKolom waar de weekmatrices beginnen (dus 11 voor week 1, 18 voor week 2 etc.)
WkMat is de matrix met kruisjes van de betreffende week, dus voor week 1: K5:Q12
DgPAfd is het aantal kruisjes per afdeling in de betreffende week, dus voor week 1: 4 0 0 4 5 4 7 0
AantalAfd is het aantal afdelingen met tenminste 1 kruisje in de betreffende week
AfdIndex is het relatieve regelnummer van de afdeling op de huidige regel.
LET OP: AfdIndex verschilt per regel; voor week 1 is dit 1 op regel 20, 4 op regel 21, 5 op regel 22, enzovoorts.
De vermelde formule is de formule op regel 20, d.w.z. voordat ik deze naam definieerde, had ik eerst een cel op regel 20 geselecteerd.

Formules:

A20: =ALS(RIJEN(A$20:A20)>AantalAfd;"";INDEX(A$5:A$12;AfdIndex))
K20: =ALS(RIJEN(K$20:K20)>AantalAfd;"";INDEX(WkMat;AfdIndex;KOLOMMEN($K20:K20))&"")

De formule in A20 is doorgetrokken t/m J27.
De formule in K20 is doorgetrokken t/m Q27.

 

[carloschouw]

WOW.. fantastisch! En wat een uitleg, thanks!

Het was precies wat ik bedoelde, meteen fors wat nieuws geleerd

Bedankt voor je uitgebreide en goede hulp en een goed weekend.. Carlo