tellen op datum

[WIMLIN]

Ik wil graag weten op welke datum werknemer 1 het aantal van 20 heeft behaald.

In onderstaand voorbeeld is er geen match in periode 2
In onderstaand voorbeeld is dan periode 3 op 15-03-2016.



Werknemer Datum Periode
1 30-01-16 2 7,5
1 05-03-16 3 7,5
1 09-03-16 3 10,5
1 12-03-16 3 18
1 15-03-16 3 21
1 16-03-16 3 24



Is dit mogelijk?

 

[AlexCEL]

Een mogelijke oplossing:

=ALS.FOUT(INDEX(B:B;KLEINSTE(ALS((A2:A7=[COLOR="#FF0000"]1[/COLOR])+(C2:C7=[COLOR="#0000FF"]3[/COLOR])+(D2:D7>=[COLOR="#FF8C00"]20[/COLOR])=3;RIJ(2:7);9^9);1));"geen match")

Dit een matrixformule dus afsluiten met Control+Shift+Enter.

Bereiken/verwijzingen zul je naar behoefte aan moeten passen.
Rood = verwijzing naar werknemer
Blauw = verwijzing naar periode
Oranje = te behalen aantal.

Zie ook bijlage.

 

[WIMLIN]

In dit voorbeeld werkt het perfect. Zo zoek ik het precies. Alleen als ik het probeer toe te passen op mijn bestand krijg ik overal "Geen Match" te zien. Kan dit eventueel te maken hebben met de functie rij?

 

[AlexCEL]

A) heb je de formule afgesloten met Control+Shift+Enter na invoeren? Je moet daarna accolades ({ }) om de formule zien staan.

B) zoals gezegd in eerdere post moet je de bereiken aanpassen naar behoefte. Enkele voorbeelden:

Tabel begint op B17: =ALS.FOUT(INDEX(C:C;KLEINSTE(ALS((B18:B23=1)+(D18:D23=3)+(E18:E23>=20)=3;RIJ(18:23);9^9);1));"geen match")
Tabel begint op Q37: =ALS.FOUT(INDEX(R:R;KLEINSTE(ALS((Q37:Q42=1)+(S37:S42=3)+(T37:T42>=20)=3;RIJ(37:42);9^9);1));"geen match")
Bereik wordt langer: =ALS.FOUT(INDEX(B:B;KLEINSTE(ALS((A2:A100=1)+(C2:C100=3)+(D2:D100>=20)=3;RIJ(2:100);9^9);1));"geen match")
etc.

Nogmaals: zie opmerking A.

 

[snb]

of arrayformule ?

=MIN(IF((C2:C7=H4)*(D2:D7>H5);B2:B7))

 

[WIMLIN]

Een arrayformule moet ik die ook afsluiten met een "}"

Ik heb een nieuw voorbeeld bestand gemaakt.

Het tabblad Urenlijst is normaal gesproken ene draaitabel en loopt t/m regel 3207
Het tabblad Output is het blad waar ik de matrix formule in heb gezet.

Hopelijk is dan mijn vraag dan duidelijker.

 

[snb]

@wimlin

Vraag je werkgever, als je met een softwareprogramma moet werken, om een cursus om daarmee te leren werken.
Wat een array/matrixformule is wordt daar dan haarfijn uitgelegd.

 

[WIMLIN]

Sorry dat ik misschien niet slim overkom voor excel. Werk er wel veel mee. Alleen een matrixformule gebruik ik nooit. Sorry voor het ongemak.

 

[AlexCEL]

Formules klopt wel, maar er staat in jouw voorbeeld (versie 0.1) echter een verwijzingsfout in H4 (#VERW!). Dat betekent automatisch de uitkomst "geen match" door de ALS.FOUT functie die ingrijpt.

Verander dit in:

=ALS.FOUT(INDEX(Urenlijst!$H:$H;KLEINSTE(ALS(((Urenlijst!D2:D49=B4)+(Urenlijst!I2:I49=[COLOR="#FF0000"]F4[/COLOR])+(Urenlijst!J2:J49>=20))=3;RIJ(Urenlijst!H2:H49);9E+99);1));"geen match")

Sluit af met Control+Shift+Enter. De datum komt er nu uitrollen (wel de celeigenschappen nog op "datum" zetten").

PS:
bijvoorbeeld hier meer informatie over matrixformules: http://www.h2o-betterwin.nl/know-how/tutorials/excel-matrixformules-basis/

 

[WIMLIN]

Super Bedankt!

 

[snb]

heb je de formule uit #5 al geprobeerd ?

 

[WIMLIN]

@snb ben ik ook mee bezig om dat te proberen. Die formule is een stuk korter. Maar die geeft nu als antwoord #naam

 

[AlexCEL]

@snb
Ik heb hem wel geprobeerd uiteraard (korter is altijd beter), maar kreeg niet de gewenste uitkomsten. Aangezien er altijd rijen zijn zonder dat aan de 3 voorwaarden wordt voldaan is de uitkomst van deze formule (het minimum van de matrix) altijd 0 ("ONWAAR") volgens mij. Daarom heb ik bij de ALS-functie toegevoegd dat deze een groot getal meekrijgt als de voorwaarden alle 3 niet waar zijn. Verder heb ik kolom F met 1 vermenigvuldigd om de formule werkend te krijgen. Ik kwam vervolgens uit op:

=MIN(ALS((Urenlijst!D2:D49=B4)*(Urenlijst!I2:I49=F4)*(Urenlijst!J2:J49>=20);1*Urenlijst!$F$2:$F$49;9^9))

@WIMLIN
Dat komt waarschijnlijk omdat de formule voor de Engelse versie van Excel is, in jouw nieuwe voorbeeldje zou de formule zoiets moeten zijn als hierboven (let weer op: matrix-formule, dus afsluiten met Control+Shift+Enter). Realiseer je ook dat deze formule niet als uitkomst "geen match" geeft als er geen overeenkomsten gevonden zijn. Als je deze functionaliteit wel in wilt bouwen wordt de formule alweer behoorlijk langer.

 

[snb]

@Alex
Boolaan wordt genegeerd door Min.

 

[AlexCEL]

@snb: hartelijk dank, weer wat geleerd. Toch kreeg ik als uitkomst 0... met geen mogelijkheid kan ik er een datum uitkrijgen. Enig idee hoe dit dan kan?