Berekenen uurrooster

[SvenGoessens]

Hoi allen,

Ik tracht een bestandje te maken in Calc die ik gebruik om mijn uurrooster bij te houden. Gezien ik onregelmatige uren heb zou het moeten bepaalde waarden herkennen bijhouden. Het zou dus per maand moeten bijhouden (aantal dagen compensatieverlof, aantal dagen rust, nachtprestatie, verlof, prestatie op zaterdag, prestatie op zondag,..). Tot hiertoe werkt het redelijk behalve wat betreft die zaterdag- en zondaguren. Wel is het zo dat ik niet zoveel ken van Calc (Excel), alleen de basic stuff en ik dus vrijwel zeker ben dat de door mij gebruikte formules veel te omslachtig zijn.
Heeft iemand een idee op welke manier ik best te werk ga, VBA?

[eremmel]

Calc kent geen VBA (dat is puur Microsoft) er is wel OO Basic, maar dat moet u proberen te vermijden.
Als u een voorbeeld van uw document kunt plaatsen en met heel nauwkeurige definities komt van wat u wilt, is er hier genoeg kennis om u verder te helpen.
(zie 'Bijlage toevoegen').

[RPG]

Hallo

Heeft iemand een idee op welke manier ik best te werk ga

Ga eens naar een bibliotheek en zoek een boek over spreadsheets. Iets anders is leer gebruik te maken van zoekfuncties op het internet. Zoeken met google geeft meestal een nietszeggend resultaat. Als je zoek op een forum speciaal voor OOo dan wordt het resultaat al beter. Alhoewel je ook zeer goed kunt zoeken met google als je de vaardigheid daar voor hebt.
Ik denk dat er verschillende keren gevraagd is zowel op het Nederlandse forum als ook op het Engelse forum nar dit onderwerp.

In deze draad Omgaan met gegevens in tabellen staat wat algemene informatie over omgaan met gegevens.

Daar datums altijd intern als een getal opgeslagen worden kun je gewoon rekenen met datums Dat betekent dat je je moet gaan verdiepen in de opmaak methodes die OOo gebruikt. Hiervoor wordt het begrip stijlen gebruik.

Voorbeeld
Gisteravond 23.00 uur tot vandaag 7.00 uur

Berekenen
=vandaag 7.00 uur minteken Gisteravond 23.00 uur

Geeft als uitkomt na opmaak 8:00

VBA?

Vergeet dat voorlopig de eerste jaren

Ik hoop dat je het bovenstaande als een antwoord kunt lezen.
Romke

[RPG]

Hallo

Ik ben ook nog even aan het zoeken geslagen en vond dit:

http://forum.openoffice.org/nl/forum/vi ... p=853#p853
http://forum.openoffice.org/nl/forum/vi ... enst#p2082
http://forum.openoffice.org/nl/forum/vi ... 2445#p2445

Romke

[SvenGoessens]

Zoals gevraagd in bijlage het betrokken bestandje. In ieder geval al bedankt voor de reacties.
Voor wat betreft de codes:
CX : compensatieverlof = thuis
RX : rust = thuis
VV : verlof = thuis
KD : kredietdag = thuis
31 : de vroegen (=2 nachturen)
32 : de laten (=2 nachturen)
33 : de nacht (=8 nachturen)
921 en 903 zijn dagdiensten en geven geen recht op nachturen.
Lichtgeel is een zaterdag
Geel is een zondag
Groen is een feestdag = zondag
Bovendien geven zowel 31, 32, 33, 921 en 903 recht op 8 uur productiviteitspremie.

Vriendelijke groeten
Sven

uitrekenen diensttabel 2012.xlsx

(12.56 KiB) 599 keer gedownload

[eremmel]

Moet ik nu concluderen dat u in uw opzet geslaagd bent en uw document het resultaat is?

Ik krijg fouten bij het openen in Excel (MSO 2010). U kunt het misschien beter opslaan in *.ods formaat.

[SvenGoessens]

Beste eremmel,

Nee ben niet in mijn opzet geslaagd, hieronder de bijlage in .ods formaat.

Sven

uitrekenen diensttabel 2012.ods

(21.34 KiB) 419 keer gedownload

[eremmel]

Ik heb even zitten kijken naar uw sheet. U kunt in ieder geval we overweg met opmaak, sum(), countif().

De gegevens kloppen voor 2012, maar hoe gaat u het doen voor 2013, 2014? Het gehele document zou gestuurd moeten worden door een enkel jaartal.

U gebruikt op een aantal plaatsen de maand-namen. Het is handiger om daar een echte datum te gebruiken (eerste dag van de maand) en dan het formaat van de datum-waarde zo te kiezen dat alleen de maand-naam getoond wordt.

De opmaak op van Tableau is vast. Als u een dienst zou ruilen enz, dan moet u niet alleen de dienst aanpassen maar ook hoe deze getoond wordt (inclusief za, zo) diensten. Beter kunt u voorwaardelijke opmaak gebruiken om dat te regelen.

Kijk eens naar de datum-functies. U kunt een datum maken op basis van jaar, maand-nummer, dag-nummer; bepalen of een jaar een schrikkel-jaar is. Het dag-nummer van de week bepalen -> handig voor het weekeinde.

Verder moet een onderzoeken wat het verschil is tussen A1, $A1, A$1 en $A$1; dat maakt het invullen van 'Toegekend in de maand' veel eenvoudiger.
In Blad2.B4 kunt u beter als formule =COUNTIF($Tableau.$B2:$AF2;B$3) gebruiken en dan de cell naar rechts en beneden slepen.

[SvenGoessens]

Beste eremmel,

Eerste en vooral bedankt voor je reactie. Ik ga mij zeker eens verdiepen in de datum-functies, lijkt me zeer interessant. Mijn enige grote probleem is dat ik nog steeds niet weet hoe ik de sheet ga laten herkennen dat een bepaalde dienst, bijvoorbeeld 32, op een zondag valt en hij dan dus ook 8 zondaguren bijtelt (zelfde voor zaterdag).

Voor wat betreft die voorwaardelijke opmaak, daar had ik al eens mee 'gespeeld' maar kwam niet tot het gewenste resultaat (zal mijn schuld wel zijn ). Ik had in voorwaardelijke opmaak ingesteld dat de dagen dat ik thuis was in het rood werden weergegeven en de dagen dat ik werkte in het zwart. Probleem was dat ik op de zaterdag-, zon- en feestdagen de achtergrondkleur (lichtgeel, geel of groen) verloor.

Ik heb trouwens een beetje verder gewerkt aan het bestandje, toont nu meer wat ik zou willen. Ik had in gedachten een apart blad te gebruiken waarop hij bepaalde zaken (zaterdag- en zondaguren, productiviteitspremies) uitrekent en deze dan te laten weergeven op mijn blad 2 dat ik dan toestand noem. Weet niet of dit de versie was die ik vorige keer als bijlage heb toegevoegd

uitrekenen diensttabel 2012.ods

(21.34 KiB) 388 keer gedownload

Sven

[Johan]

Mag ik ook eens stoeien met uw rekenblad? Er is één en ander verandert in de bijlage, maar ik beweer niet dat dit ten goede komt. Ik maak zowel gebruik van eenvoudige oplossingen als van meer ingewikkelde formuleringen. Het is mijn bedoeling om u ideeën aan te reiken die u misschien tot een oplossing brengen. Bekijk het eens en stel de vragen die u nodig acht?

[eremmel]

Net als Johan ben ik voor u aan de slag gegaan om e.e.a. tot een echt rooster te maken.
Verander het jaartal in Cel A1 en alles past zich aan.

Dit houdt onder andere in:

1: Gebruik van namen:
Menu Invoegen -> Namen...

2: Conditionele formattering (meerdere niveaus, ik gebruik LO 3.6.4).
Ik heb stylen gemaakt voor { Feestdag, zaterdag, zondag, Centraal }, waarbij Centraal de basis is voor de andere stijlen. De kleur rood/zwart heb ik gemaakt door standaard de kleur rood te kiezen en dan met een voorwaardelijke opmaak getallen >0 zwart te maken. Verder is er nog een stijl voor niet ingevuld en bestaat niet.
De volgende formules zijn gebruikt voor de voorwaardelijke opmaak: (namen in het Engels voor zover aanwezig, zie punt 5). Hier is uitgegaan van cel B2
Geen schrikkeljaar -> NOT(ISSCHRIKKELJAAR(DATE(JAAR;MONTH($A2);B$1)))
Niet ingevuld -> ISBLANK(B2)
Feestdag -> not(ISNA(VLOOKUP(DATE(JAAR;MONTH($A2);B$1);FEESTDAGEN;1;0)))
Zaterdag -> WEEKDAY(DATE(JAAR;MONTH($A2);B$1);1)=6
Zondag -> WEEKDAY(DATE(JAAR;MONTH($A2);B$1);1)=7


3: Dropdown lijsten voor het kiezen van diensten
De diensten gedefinieerd in Blad2, deze reeks een naam gegeven: 'DIENSTEN'. En alle velden gekoppeld aan die lijst via Menu: Gegevens -> Geldigheid...

4: Tabel gemaakt voor alle feestdagen op Blad2 (Niet alle feestdagen ingevuld nog). Voor eerste paasdag is er een formule!

5: Mijn voorkeur gaat uit naar Engelse functie namen: meer documentatie op internet voorhanden en de Nederlandstalige namen kan ik nooit onthouden: Exrta -> Opties... --> L.O. Calc -> Formule -> 'Gebruik Engelse namen'.

6: De maanden in Tableau.A2:A13 zijn gemaakt door eerste van die maand te nemen van geselecteerde jaar. Het formaat (via Ctrl-1) is de maandnaam in lang formaat.

Ik denk dat e.e.a. overweldigend is, maar ja, je hebt toch enige kennis nodig om zo iets te maken.

Edit: Bijlage bevat nog een foutje in kolom A (eerste dag van de maand berekenen): download versie 1f (zie hieronder)

[eremmel]

@ Sven: Ik heb nog een vraag over de uren op zaterdag/zondag. Zijn er diensten die in de nacht vallen over middernacht heen? Moeten die dan voor bv 4 uur op zaterdag en 4 uur op zondag meetellen?

[SvenGoessens]

Beste eremmel,

Als ik op zaterdagavond de nacht doe, begin ik om 22u00. Dit wil zeggen 2 uur zaterdag, 6 uur zondag en 8 nachturen.

[eremmel]

Het is logisch dat de berekening van za/zo uren lastig is. Ik ben uitgegaan dat dus alleen dienst 33 verdeeld moet worden over twee dagen. Bekijk de sheet maar eens. Ik vond nog een foutje in mijn eerder geplaatste sheet: de dagen van de week waren niet goed aangegeven.

Berekenen van de uren op zaterdag/zondag
Voor alle diensten rekenen we 8 uur per dienst op die za/zo, behalve 33. Daar rekenen we 2 uur voor op de za/zo en 6 uur als die dienst op vr/za valt. Daar hebben we dus ook nog het probleem dat we naar de vorige maand moeten kijken. (en zelf naar het vorige jaar: een 33 die op oudjaarsdag valt.

Een mogelijke oplossing.
In Calc kun je met cellen rekenen; bv A1+A2 of SUM(A1:A3) enz. Je kunt ook met matrices rekenen en dat is best lastig maar in het geval van dit probleem erg handig. Je kunt een expressie maken als {=ISEVEN(B2:B100)}. Merk op de { } om de formule. Je krijgt nu een vector van data met als elementen { ISEVEN(B2); ISEVEN(B3), …, ISEVEN(B100) }, dus een lijst van WAAR / ONWAAR. Deze vector kun je combineren met andere vectoren die even lang zijn in een expressie. En zo'n vector-expressie kun je optellen met de functie SUMPRODUCT().

Omdat die expressies vaak lang zijn, is het handig om deze als 'naam' te definieren. Een voorbeeld is de vector voor alle zaterdagen volgens de maand van de datum die in kolom A vermeld is. Als ik in cel Tableau.B2 sta kan ik de volgende formule maken voor de zondagen:
WEEKDAY(DATE(JAAR;MONTH($A2);$Tableau.$B$1:$AF$1);1)=7
een kortere variant is (de datum van A2 is de eerste van de maand):
WEEKDAY($A2+$Tableau.$B$1:$AF$1-1;1)=7
Dit is een vector van 31 lang die op kijkt naar dagnummers volgens $Tableau.$B$1:$AF$1 en naar de maand in kolom $A, maar de regel van de maand is dezelfde als waar ik de formule gebruik (en ook het blad waar ik de formule gebruik).
Deze kunnen we combineren met de diensten en zo het aantal uren uitrekenen.
Het aantal diensten in een maand is (we moeten rekening houden met het maandnummer in kolom A). Dus we gebuiken een offset. En we willen alleen de diensten die uren opleveren rIsDienstMaandVector:
ISNUMBER(OFFSET($Tableau.$A$1;MONTH($A2);1;1;31))
Vervolgens maken we een vector voor de 33-dienst rIs33DienstMaandVector:
OFFSET($Tableau.$A$1;MONTH($A4);1;1;31)=33
met deze twee maken we een vector voor de niet-33 diensten: rIsNot33DienstMaandVector:
NOT(rIs33DienstMaandVector)*rIsDienstMaandVector
Voor de zaterdag uren moeten we ook nog de 33 diensten op vrijdag weten daarom hebben we nog een rIsVrijdagMaandVector nodig:
WEEKDAY(DATE(JAAR;MONTH($A4);$Tableau.$B$1:$AF$1);1)=5

Nu kunnen we de uren op zaterdag voor een maand berekenen:
1. 8 x aantal niet 33-dienst op zaterdag:
=8 * SUMPRODUCT(rIsZaterdagMaandVector;rIsNot33DienstMaandVector)
2. 6 x aantal 33 diensten op vrijdag:
=6 * SUMPRODUCT(rIsVrijdagMaandVector;rIs33DienstMaandVector)
3. 2 x aantal 33 diensten op zaterdag:
=2 * SUMPRODUCT(rIsZaterdagMaandVector;rIs33DienstMaandVector)
4. Correctie voor 33 dienst als vrijdag de laatste dag van de vorige maand.:
=6 * (rWeekDagLaatsteDagVorigeMaand=6)*rIs33DienstLaatsteDagVorigeMaand

Voor zondag gaat het op gelijke wijze.