[Excel] Als constuctie. Ik kom er niet meer uit

[Verwijderd]

Ik heb de volgende Excle sheet (simpele versie van gemaakt):


naam_________________periode_____________Begin__________eind

naam1___________20030101-20031001_____01/01/2003_____31/01/2003
naam2___________20030101-20031001
naam2___________20031101-20031205
naam2___________20040101-20040201_____01/01/2003_____01/02/2004
naam3___________20030101-20031001_____01/01/2003_____31/01/2003


Een persoon kan dus meerdere perioden werkzaam zijn. In begin moet het begin komen van zijn eerste periode en in eind moet het eind van zijn laatste periode komen. Ik weet niet hoeveel periodes een persoon aan het werk is, dat kan 1 periode zijn maar ook 10 (of nog meer). Ik gebruik nu de volgende constructie en dat werkt prima bij 2 periodes maar bij meer periodes gaat het lastig worden:

=ALS(BD1117=BD1118;(DEEL($BM1117;1;8));"test")

Als er maar 1 periode is moet in begin en eind natuurlijk gewoon het begin en eind van die ene periode komen.

[whoami]

Dit hoort niet thuis in P&W, maar in Software Algemeen

P&W -> SA

[Verwijderd]

Oeps sorry, foutje

[nixan]

=MAX((A$1:A$8=C1)*(B$1:B$8))
deze formule bevestigen met F2 en Ctrl+Shift+Enter

Als je bovenstaande formule invoert en bevestigt als Array.
Dan zoekt Excel in de cellen A1:A8 naar cellen die voldoen aan het criterium =C1

Van de cellen die aan dat criterium voldoen, neemt Excel de hoogste waarde uit de cellen B1:B8

voorbeeld
a1:a8 b1:b8
x.............1
x.............2
x.............3
y.............5
x.............1

als c1=x, dan is de uitkomst van de bovenstaande formule 3

Met behulp van bovenstaande formule, kun je voor elke naam (of beter: personeelsnummer) de hoogste waarde in de datumkolom vinden.
Dat je door 'MAX' te veranderen in 'MIN' de minimale waarde vinden, kun je zelf ook bedenken.

[Verwijderd]

Ga ik proberen, bedankt

[Verwijderd]

Ok, deze constructie werkt goed maar hoe zorg ik ervoor dat alles automatisch gaat? Het is niet de bedoeling dat c1 iedere keer handmatig ingegeven moet worden.

[nixan]

Zoals je kunt zien, is de verwijzing naar c1, de enige variabele verwijzing in deze formule.
Als je de formule dus (bijvoorbeeld vanuit cel d1) naar beneden kopieert, dan verwijst de cel vervolgens naar c2, c3 enz.

als je in kolom C dan een lijst van alle personeelsnummers/namen maakt, dan worden dus alle namen gecontroleerd.

[Verwijderd]

Ik gebruik nu de volgende formule:

=MIN((BD$1116:BD$1129=BD$1116)*(BO$1116:BO$1129))

In de volgende cell staat de volgende formule:

=MIN((BD$1116:BD$1129=BD$1117)*(BO$1116:BO$1129))

BD1116 en BD1117 zijn hetzelfde toch krijg ik verschillende resultaten, wat doe ik verkeerd?

[nixan]

heb je de formule bevestigd met F2 en Ctrl+Shift+Enter?

[Verwijderd]

Dat had ik blijkbaar niet goed gedaan, nu wel maar nu krijg ik overal als resultaat 0

[grootw]

nixan schreef op 23 december 2003 @ 12:45:
heb je de formule bevestigd met F2 en Ctrl+Shift+Enter?

Wat doet F2 en Ctrl+Shift+Enter???

[Verwijderd]

die maakt een array van je formule

[Verwijderd]

Ik heb er nu dus een array van gemaakt (er staan netjes {} omheen) maar het resultaat blijft overal 0. als ik de accolades weg haal dan krijg ik wel een resultaat maar doet ie alles (logischerwijs) per regel.

[Verwijderd]

Een stapje verder, toch iets met de array. De max functie doet het prima, de MIN functie geeft constant 0 terug

[Verwijderd]

De MIN functie heeft toch geen beperkingen?

Dit is de max functie: {=MAX((BD$1116:BD$1129=BD$1116)*(BP$1116:BP$1129))}

en dit de min functie: {=MIN((BD$1116:BD$1129=BD$1116)*(BO$1116:BO$1129))}

De max functie gaat prima en de min functie geeft enkel 0 terug

Sterker nog, als ik de MIN wijzig in MAX krijg ik gewoon het juiste resultaat, wat is nu het cruciale verschil tussen MIN en MAX dat dit niet werkt?

[ Voor 23% gewijzigd door Verwijderd op 23-12-2003 13:53 ]

[nixan]

Dat komt doordat er lege cellen in je bereik zitten. De waarde van deze cellen is 0 en dus het laagst.
Het is grappig om te zien, dat de lege cel blijkbaar niet aan de voorwaarde hoeft te voldoen die je in de formule stelt.

Er zijn wel een paar work-arounds te bedenken:
-zoals het beperken van je bereik, tot het bereik dat je werkelijk heb ingevuld
-of zoals het maken van een nieuwe gegevenstabel naast je basisgegevens, die
.1. de gegevens uit de eerste tabel 1-op-1 overneemt en
.2.de lege cellen overneemt met een afwijkende waarde als "n.b." o.i.d.

Dit zijn slechts work-arounds, misschien dat er een Excel-goeroe in de zaal is, die je echt verder kan helpen.

[Verwijderd]

Ik heb de cellen nu even keihard gevuld. De formule is nu de volgende:
=MIN((BD$1116:BD$1118=BD$1116)*(BR$1116:BR$1118))

br1116 is nu 1
br1117 is nu 2
br1118 is nu 3

Nu gaat ie goed


Zodra ik de formule wijzig in:

=MIN((BD$1116:BD$1119=BD$1116)*(BR$1116:BR$1119)) gaat ie mis.
In BD1116 t/m BD1118 staat iedere keer dezelfde werknemer. in BD1119 staat de eerste keer een nieuwe en nu gaat het dus mis.

Het blijkt dat de selectie de volgende getallen opleverd 1/2/2/0 . Die nul hoort er eigenlijk niet bij want die ontstaat doordat er nu een andere medewerker op de regel staat (die van BD1119). Eigenlijk zou die 0 dus niet in de selectie moeten zitten.

[ Voor 62% gewijzigd door Verwijderd op 23-12-2003 14:56 ]

[Verwijderd]

Heeft niemand een idee hoe ik kan voorkomen dat de 0 in de selectie komt?