Per maand contracturen optellen obv geboortedatum en functie

[Xbonk]

Voor een ziekenhuis wil ik een analyse doen van de nachtdienstbelasting van medewerkers. Medewerkers die 57 jaar of ouder zijn hoeven geen nachtdiensten meer te doen. Van alle medewerkers heb ik de geboortedatum dus kan ik vaststellen of zij 57 jaar of ouder zijn.

Nu wil ik voor de komende 5 jaar per maand weten hoeveel medewerkers 57 jaar of ouder zijn en dus wel/geen nachtdienst meer draaien. Vervolgens wil ik per maand optellen hun contracturen optellen indien zij nog geen 57 jaar of ouder zijn. Daar komt bij dat ik in een lijst meerdere functiegroepen heb en wil ik deze optelling ook nog per functiegroep hebben.

Dan kom ik al snel uit op de sommen.als formule waarbij ik als één van de voorwaardes de functiegroep benoem. De andere voorwaarde moet dan de leeftijd worden. Echter wil ik voor elke maand weten of medewerkers al 57 jaar of ouder zijn. Dus voor de maand januari 2024 wil ik weten wie er op dat moment 57 jaar of ouder is, voor de maand februari 2024 hetzelfde, etc. Kan ik dit versleuteld krijgen in een formule met sommen.als en zo ja, hoe dan? Of hoe krijg ik dit met een andere formule voor elkaar zonder gebruik te maken van een macro?

[Fr33z]

ja het kan maar het is veeel makkelijker om er hulpkolommen bij te maken.

[Patrick_6369]

Jep! Een paar hulptabellen doen wonderen.
Door een hulptabel per medewerker te maken met per maand de berekening of iemand 57 is (geeft als resultaat bijvoorbeeld cijfer 1) of nog niet (geeft als resultaat cijfer 0) kun je dit heel makkelijk doen.
Indien nodig maak je ook nog een hulptabel per medewerker met contacturen x de hierboven gesuggereerde dummyvariabele, dan wordt het nog eenvoudiger en overzichtelijker.
De berekening met de datums voor de leeftijd kan pittig zijn in Excel, maar online is daar ook genoeg over te vinden.
Loop je vast? Vraag het hier!

[Triggy]

SUMIFS gaat je niet lukken denk ik. Maar FILTER is je uitweg. Wel een redelijk nieuwe functie.

Als je echte datums invult in de tabelkoppen kan je onderstaande formules toepassen. Desnoods laat je met custom number formatting ("mmm") alleen de maand zien zodat het er weer hetzelfde uit ziet.

Cel D2

code:

=SOM(FILTER(D$13:D$21;(DATUMVERSCHIL($B$13:$B$21;D$12;"y")>=57)*($C$13:$C$21=$C1);0))

Cel D3

code:

=SOM(FILTER(D$13:D$21;(DATUMVERSCHIL($B$13:$B$21;D$12;"y")<57)*($C$13:$C$21=$C1);0))

Deze kan je zover je wil naar rechts "doortrekken".

De formules voor D5, D6, D8, D9 kan je gewoon kopieren.

p.s. (vertalen van de functies in NL: Add-in)

Hulpcellen... tsk tsk

[ Voor 3% gewijzigd door Triggy op 12-04-2024 12:26 . Reden: Formules vertaald van EN>NL ]

[Teun_2]

De oplossing van @Triggy is zeker eentje die werkt.
Ik vraag me alleen niet af of je een dergelijke analyse niet beter maakt in de workforce scheduling software die je gebruikt? Alle pakketten die ik hier ooit voor bekeken heb, hadden de mogelijkheden om dit weer te geven en ernaar te plannen.

[Dido]

offtopic:
@Teun_2 ik heb bij dit soort vragen altijd de vage hoop dat het om huiswerkvragen gaat, en er niet daadwerkelijk ergens een zorginstelling voor zijn vijfjarenplanning afhankelijk is van een goedwillende amateur met een Excel-sheet. Maar die hoop is soms ijdel

[Xbonk]

Bedankt voor het meedenken!

De filter-functie is helaas niet beschikbaar in onze versie van Excel (2019) zo lijkt het. In dat geval kom ik toch uit op hulpkolommen hoewel het natuurlijk het mooiste is om met één formule op te lossen, maar dat is dan ijdele hoop.

@Dido @Teun_2 Met veel gelach lees ik jullie berichten want dat is inderdaad het beeld wat veel mensen hebben, maar ik ben deze verbazing al te boven. De harde realiteit is echter dat veel software is gericht op ofwel roosters voor de komende maanden (bijv. Ortec) danwel terugkijken naar capaciteit (datawarehouse). Maar vooruitkijken over meerdere jaren is iets waar in ieder geval de systemen hier niet in voorzien en dus komt Excel om de hoek. Het is hier echter niet voor een heel ziekenhuis bedoeld maar voor een afdeling van ca. 150fte.

[Teun_2]

Xbonk schreef op dinsdag 16 april 2024 @ 15:17:
Bedankt voor het meedenken!

De filter-functie is helaas niet beschikbaar in onze versie van Excel (2019) zo lijkt het. In dat geval kom ik toch uit op hulpkolommen hoewel het natuurlijk het mooiste is om met één formule op te lossen, maar dat is dan ijdele hoop.

@Dido @Teun_2 Met veel gelach lees ik jullie berichten want dat is inderdaad het beeld wat veel mensen hebben, maar ik ben deze verbazing al te boven. De harde realiteit is echter dat veel software is gericht op ofwel roosters voor de komende maanden (bijv. Ortec) danwel terugkijken naar capaciteit (datawarehouse). Maar vooruitkijken over meerdere jaren is iets waar in ieder geval de systemen hier niet in voorzien en dus komt Excel om de hoek. Het is hier echter niet voor een heel ziekenhuis bedoeld maar voor een afdeling van ca. 150fte.

Voor langere termijn volg ik je, maar op kortere termijn is dit wel prima op te zetten. Voor langere termijn moet je sowieso wat abstracties maken. Ook daar kan je bijvoorbeeld de data van Ortec inladen in een PowerBI rapport om wat langere termijn prognoses en analyses te doen. Sterker: dat heb ik al eens opgezet. Ik erken ook wel dat er soms wat ad hoc vragen tussen de mazen van het net vallen en dan is Excel een prima tool. Voor deze specifieke vraag kan Ortec zeker wel wat voor je opzetten. De vraag is dan vooral of het budgetair haalbaar is.

Als FILTER geen oplossing is, zou je wel een SUMPRODUCT kunnen gebruiken en de kolom geboortedatum vergelijken met grensdatum en dan de kolom contracturen als waarden pakken. Werkt prima, maar conceptueel iets moeilijker te vatten. SUMPRODUCT function - Microsoft Support. Voorbeelden 2 en 3 zijn met gelijkheidstekens, maar het werkt ook met groter dan tekens.

[ Voor 19% gewijzigd door Teun_2 op 17-04-2024 10:33 ]