uitsplitsen van diensten in weekrooster

[hvandenburg]

Ik ben bezig met het “vereenvoudigen” van een invulrooster. Bij het invullen wil ik graag direct zien hoe de verhouding is, aantal dagen en deze gesplitst in avond-, nacht-, en weekenddiensten.

Ik heb ik de volgende formules ingevoerd:
INGEROOSTERDE DIENSTEN DEZE WEEK (is volgens mij goed)

=ALS(AANTAL.ALS($E$5:$K$50;M23)=0;"";AANTAL.ALS($E$5:$K$50;M23))
WAARVAN AVONDDIENSTEN
=SUMPRODUCT(($E$5:$K$50=M23)*($B$5:$B$50>=(12,5/24)))
WAARVAN NACHTDIENSTEN
=SUMPRODUCT(($E$5:$K$50=M23)*($B$5:$B$50>=(0,5/24)))
WAARVAN WEEKENDDIENSTEN (is volgens mij goed)
=ALS(AANTAL.ALS($J$5:$K$50;M23)=0;"";AANTAL.ALS($J$5:$K$50;M23))

Wat mij nu niet lukt, is de splitsing avond en nachtdienst.
Zodra er een nachtdienst ingevuld wordt komt deze ook bij de avonddienst.
<zie bijlage>

Avond-, en Nachtdiensten beginnen op verschillende tijden.
Een avonddienst kan o.a. beginnen om 12:30 uur en een nachtdienst om 20:30 uur e/o om 0:30 uur.

M23 = de verwijzing naar de werknemer (dus Cel M23)
Zwart = avonddienst
Rood = nachtdienst

 

[gelens]

HvandenBurg

Ik hoop dat ik de vraag goed begrepen heb.
De antwoorden in het bijgevoegde bestand sluiten met jouw oplossing.

 

[wiki]

is het een optie om de cellen in een bepaalde kleur te tellen
="http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm"

of de letters uit te breiden van a
naar aa, an, aw?

gr wim

 

[hvandenburg]

Gerard hartelijk bedank voor de snelle reactie.
formule ingevoerd in het org. rooster en wat ik tot nu toe geprobeerd heb, het werkt.
Formule is wel erg lang hier had ik nooit op gekomen.

Wim
De medewerkers hebben een kleur en de link werkt niet.

 

[gelens]

hvandenBurg

Graag gedaan.

Zou je de vraag als opgelost kunnen zetten ?

 

[dnties]

@wiki/Wim en topic-starter: De link van wiki, maar dan zo dat het werkt vanuit het forum:
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

Zal wel niet meer nodig zijn nu, want de oplossing van gelens/Gerard werkt maar voor de volledigheid dan maar.

Tijs.

 

[hvandenburg]

Tijs,
Toch is de link wel interessant. Ik denk dat ik deze kan gebruiken als toevoeging.

 

[hvandenburg]

toch de vraag weer geopend.
e.e.a. getest. in eerste instantie bijzonder blij met de oplossing van gerard. toen bleek dat toch niet alles werkte. avonddiensen worden niet allemaal geteld en ondanks de formule bij de nachtdienst kan die niet korter en overzichtelijker.

 

[gelens]

HvandenBurg

De volgende formule neemt nu alle avonddiensten mee tussen 12:30 en 19:00 uur

=SOMPRODUCT(($E$5:$K$50=M23)*($B$5:$B$50>=(12,5/24))*($B$5:$B$50<=19/24))

Kan jij aangeven wat de 1e mogelijke begintijd is voor een avonddienst ? Daarnaast ook de laatst mogelijke tijd.

 

[popipipo]

http://www.worksheet.nl/forumexcel/showthread.php?t=63797
probleem is opgelost

Wat is nu het probleem??

 

[hvandenburg]

ik ben even een verklaring verschuldigd waarom ik het probleem van opgelost naar onopgelost heb veranderd.
De oplossing door gerard werkte in eerste instantie correct. ik heb dat bij de bovenste paar diensten uitgeprobeerd.
Was heel tevreden en heb daardoor het probleem op opgelost gezet.
Daarna heb ik later willekeurig de letter A ingevuld en toen bleek dat niet alles werd meegeteld. toen voelde ik mij een oen omdat ik niet alles had gecontroleerd.
Wat is nu het probleem: Alle A"tjes in het zwart worden niet geteld. Als ik de formule van gerard moet gebruiken dan moet elke dienst in die formule gezet worden. daarom mijn vraag is er een kortere formule. de formule van popipipo was kort en bondig maar niet voor de avonddiensten en als ik die aanpaste ging de nachtdiensten weer helemaal mis.
alsnog een nieuw excel als bijlage met zichtbaar de formule van popipipo.

 

[hvandenburg]

sorry verkeerde excelbestand mee genomen.
hierbij de juiste

 

[popipipo]

Mijn formule gaat ervan uit dat de avonddienst altijd om 16:30 uur begint.

Maar wat versta je onder avonddienst, van hoe laat tot hoe laat begint deze?
Onderstaand voorbeeld kun je gebruiken voor avonddiensten die beginnen van 14:00:00 tot en met 20:59:59 uur
Je kunt het zelf aanpassen als dit niet klopt

=SUMPRODUCT(($E$5:$K$50=M23)*($B$5:$B$50>=([COLOR="Blue"]14[/COLOR]/24)*($B$5:$B$50<([COLOR="Red"]21[/COLOR]/24)))

Of gebruik kolom A waar je de benaming van de dienst hebt staan? ( neer zet.)
Dit ipv op tijden te gaan zoeken.
Dan krijg je zoiets:

=SUMPRODUCT(($E$5:$I$50=M23)*($A$5:$A$50=O20))

 

[hvandenburg]

Ik begrijp alle formules maar wat mijn probleem is dat niet alle diensten om dezelfde tijd beginnen laat staan eindigen.
Als ik bovenstaande formule gebruik dan moet ik elke dienst afzonderlijk in de formule zetten.

Ik ben optie 2 gegaan. Er is een kollom tussen gezet en deze ingevuld en de diensten vernoemd als avonddienst en/of nachtdienst. Daarna deze kollom verborgen. In de formule aangegeven om in deze kollom naar avond of nachtdienst gezocht moet worden. Diverse diensten ingevuld en het werkt. SUPER. (dit had ik veel eerder moeten doen)

Voor de avonddienst =IF(SUMPRODUCT((($F$5:$L$50=N23)*($B$5:$B$50=P20)))=0;"";SUMPRODUCT((($F$5:$L$50=N23)*($B$5:$B$50=P20))))
Voor de nachtdienst =IF(SUMPRODUCT((($F$5:$L$50=N23)*($B$5:$B$50=Q20)))=0;"";SUMPRODUCT((($F$5:$L$50=N23)*($B$5:$B$50=Q20))))

Ik heb nu nog een vraag kan dit ook met de telling van het aantal gewerkte uren.
Als er één dienst geen weekenddienst is hoe kan ik die er uit halen. (voorbeeld: zondagavond aanvang dienst 22:00 uur tot 07:00 uur. Dit is officieel geen weekend dienst meer want het weekend eindigt om 24:00 uur)

 

[popipipo]

Je hebt dan in kolom E de werktijden staan die je wilt optellen.
Deze kolom moet je dan achter de bestaande formule plaatsen.

......*($E$5:$E$50)

Zo kun je dus elke voorwaarde er achter zetten.

 

[hvandenburg]

Ik weet niet wat ik fout doe of wat er fout gaat. Bij de telling van de totale uren werkt het niet. (Het gaat alleen om de kollom omgerekende uren) De één gaat goed (zie rode cellen). De ander telt hij de uren niet op (zie donker groene cellen). Bij de ander neemt hij totaal andere uren (zie lichtgroene cellen). Ik heb de formule exact overgenomen met aanpassingen naar de juiste cellen.
=SOM.ALS(F10:F50;N4;E5:E50)+SOM.ALS(G10:G50;N4;E5:E50)+SOM.ALS(H10:H50;N4;E5:E50)+SOM.ALS(I10:I50;N4;E5:E50)+SOM.ALS(J10:J50;N4;E5:E50)+SOM.ALS(K10:K50;N4;E5:E50)+SOM.ALS(L10:L50;N4;E5:E50)
Bij de gesplitste uren (avond, nacht en weekend) gaat de berekening wel goed. De desbetreffende cellen C, D, E en O24 t/m R39 staan op [uu]:mm. Ik heb al verschillen formules uitgeprobeerd maar niets helpt. Toch maar weer mijn laatste voorbeeldrooster bijgevoegd.

hans

 

[popipipo]

=SOM.ALS([COLOR="Red"]F10[/COLOR]:F50;N4[COLOR="Red"];E5[/COLOR]:E50)

Het bereik moet wel gelijk staan
Dus of F5:F50 met E5:E50
of F10:F50 met E10:E50

 

[hvandenburg]

dom van mij. helemmal overheen gekeken. dat krijg je met het copieeren.
even snel aangepast en getest. tot nu toe ok.
ik zal een orgineel rooster maken en als dit goed is kan ik dit probleem afsluitn.
bedankt