Excel formule met ORT

[RemcoH]

Hey hey,

Excuses voor al mijn vragen, ben met een werk project bezig;
dus niet bedoeld om dit forum te spammen.

Nogmaals een Excel vraagje dus. Hopelijk de laatste en tevens de lastigste.
Heb een illustratie toegevoegd van een Excel sheet met een dienstrooster.
Via gegevensvalidatie kan ik een dropdown menu maken met keuzes.
Nu wil ik mijn keuze verbinden aan een bepaald cao met ORT % (onregelmatigheidstoeslag)
en dat deze dan automatisch die getallen plaatst waar je in de illustratie nu de getallen
ziet staan. Dus kies je iets anders komen daar andere getallen te staan.
Nu wil ik ook dat wanneer ik de tijden invoer bij 'van' 'tot' hij dan berekend welke uren
onder welk tarief moeten komen te staan.
Heb het document 'dienstrooster' toegevoegd als bijlage, het blad 'bron' daarin staat de
verwerking van de ORT tarieven in een schema.
De ORT tarieven waarvan ik het 'bron sheet' heb gemaakt heb ik tevens toegevoegd als bestand
'ORT Tarieven algemeen (print versie).'
Heb dit forum doorzocht en iets vergelijkbaars gevonden. De bijlage met een rekencode
is ook toegevoegd als bijlage 'Rooster voorbeeld 5.' Misschien scheelt het iets?
Mocht het alleen met een macro kunnen, hoe doe ik dat dan?

Thanks wederom en ik weet het is een behoorlijke vraag.

 

[RemcoH]

Dit is de url op dit forum waar ik het bestand 'Rooster voorbeeld 5' vandaan heb gehaald.

http://www.helpmij.nl/forum/showthread.php/739923-excel-ort-berekenen

Het is een mooi voorbeeld (heeft alleen zijn 135% tarief niet ingevoerd, foutje denk ik).

Rekenformules die hij gebruikt zijn:

=24*(Overuren(B9;C9;ma_vr50)+Overuren(D9;E9;ma_vr50)+Overuren(F9;G9;ma_vr50)+Overuren(H9;I9;ma_vr50)+Overuren(J9;K9;ma_vr50)+Overuren(L9;M9;ma_vr50)+Overuren(N9;O9;za_50)+Overuren(P9;Q9;zo_50))

=24*(Overuren(B9;C9;ma_vr100)+Overuren(D9;E9;ma_vr100)+Overuren(F9;G9;ma_vr100)+Overuren(H9;I9;ma_vr100)+Overuren(J9;K9;ma_vr100)+Overuren(L9;M9;ma_vr100))

=24*(Overuren(B9;C9;ma_vr125)+Overuren(D9;E9;ma_vr125)+Overuren(F9;G9;ma_vr125)+Overuren(H9;I9;ma_vr125)+Overuren(J9;K9;ma_vr125)+Overuren(L9;M9;ma_vr125))

=24*(Overuren(B9;C9;ma_vr145)+Overuren(D9;E9;ma_vr145)+Overuren(F9;G9;ma_vr145)+Overuren(H9;I9;ma_vr145)+Overuren(J9;K9;ma_vr145)+Overuren(L9;M9;ma_vr145)+Overuren(N9;O9;za_145)+Overuren(P9;Q9;zo_145))

Ik begrijp de cel verwijzingen, zie de verwijzingen naar dagen; begrijp alleen niet hoe deze de tijden berekend. Zie namelijk geen verwijzing naar het tijden schema.

Kan ik mijn eerdere vraag via het drop-down menu realiseren? Of heeft iemand een betere suggestie?

Hoor het graag.

 

[RemcoH]

Iemand die het antwoord er al op heeft?

Heb het blad van de ORT toegevoegd als bijlage.

Mocht het niet via een drop-down menu mogelijk zijn...
Misschien dat mijn ORT tabellen dan in het 'voorbeeld bestand 5' verwerkt
kunnen worden? ? Dan krijg ik 6 verschillende tabellen waarbij dan aan
de rechterkant de uren worden genoteerd.
Het 'rooster voorbeeld bestand 5' heeft ook een extra dinsdag,
deze wil ik weg hebben. Maar wel zo dat de rest nog functioneert.

Wie is hier zo slim mee? ? Hoor het graag.

 

[sylvester-ponte]

Remco, hier een opzetje wel controleren hoor.

 

[RemcoH]

WAUW! ! Dat ziet er zeker goed uit!
Mag ik vragen hoe je dit gedaan hebt? Briljant. :thumb:

Ga hem nog even verder door testen en dan vervolgens implementeren in mijn
huidige document. Heb een vermoeden dat dit niet simpelweg met een CTRL + C
over te zetten is. ;-)

Als de testfase voorbij is en alles is OK, zal ik deze topic als beantwoord aanvinken.

Echt goed! @Sylvester = WAIT FOR IT... L E G E N D A R Y!

 

[sylvester-ponte]

als je op alt F11 drukt zie je de module waar de toegevoegde (zelf geknutselde) functies staan.
als je deze functies wil gebruiken in een ander excelbestand dan deze daar naar toe kopieren.
ze moeten in een module komen te staan. dan kun je ze gebruiken.

 

[RemcoH]

Hoi,

Bedankt voor de informatie. ;-) Ben helaas niet zo'n held met Excel formules.

Wil nog 1 ding vragen... met testen werkte alles naar behoren.
Nu wil ik alleen de lijst uitbreiden, zodat er 100 personen op geplaatst kunnen worden.

Zie illustratie:


De illustratie geeft een beeld hiervan weer. Het document is hier toegevoegd als
bijlage.
Met knippen en plakken werkt de CAO formule vaak niet en sommige cellen worden
groter gemaakt.

Hoe kan ik of welke code moet ik aan het blad toevoegen en waar om dit voor elkaar te krijgen?

Nogmaals dank.

 

[sylvester-ponte]

kopieer 2 hele rijen en plak ze dan er onder en dan zou het moeten werken .
ik heb wel een paar $ tekens weg moeten halen om op die manier de formules te kunnen kopiëren.
zie voorbeeld.

 

[RemcoH]

Hoi,

Komt heel erg dicht in de buurt. ;-)

Wil alleen nog dat hij onder de uren bij elkaar op telt.

Zie illustratie:


Krijg zelf of ##### als waarde of een getal wat niet juist is.

Heb de Cel aangepast op :mm.
Dat omdat Excel standaard een 24-uurs weergave gebruikt en anders niet door rekent.

Heb de volgende formules gebruikt Zonder resultaat:

=TEKST(E11-D11;"U:MM")

=SOM(O11:O109;"U:MM")

=TEKST(O11:O109;"U:MM")

=SOM(O11:O109)*24

=TEKST(O11:O109)*24

=SOM(O11:O109;"U:MM")*24

=TEKST(O11:O109;"U:MM")*24

=O11:O109

=SOM($O11:$O109)

=TEKST($O11:$O109)

=SOM($O11:$O109;"U:MM")

=O11+O13+O15+O17+O19+O21+O23+O25+O27+O29+O31+O33+O35+O37+O39+O41+O43+O45+O47+O49+O51+O53+O55+O57+O59+O61+O63+O65+O67+O69+O71+O73+O75+O77+O79+O81+O83+O85+O87+O89+O91+O93+O95+O97+O99+O101+O103+O105+O107+O109

Welke som moet ik wel maken?

 

[Haije]

Remco,

waarom herhaal je steeds die regel met de percentages?
zie bijlage voor een oplossing waarbij telkens verwezen wordt naar het percentage in rij 10.

Ook de formule in kolom O aangepast

 

[sylvester-ponte]

hallo Haije
ik tel in de bron ongeveer 13 verschillende percentages
dus er moeten nog wel een paar kolommetjes bij

 

[sylvester-ponte]

zo iets?

 

[RemcoH]

Thanks voor al jullie hulp en argumenten. Waardeer het.

@Haije. Ik herhaal steeds die regel omdat het hier betreft om verschillende inschalingen van de personen.
In jouw opzet rekent hij wel de uren e.d. uit, maar je ziet niet meer voor welke type CAO er gewerkt is en voor welke percentages.
Dat overzicht wil ik behouden, anders word het giswerk. Vandaar dus. Wel dank voor de input. ;-)

@Sylvester-ponte, thanks again!
Wil alleen nog dat de kolommen I-J-K-L-M-N onderin de uren bij elkaar laten optellen; per kolom.

De codes die ik gebruik, alleen werken niet:

=SOMPRODUCT((IS.ONEVEN(RIJ(N10:N109)))*(N10:N109))

=SOMPRODUCT(N(IS.ONEVEN(RIJ(N10:N109)))*(N10:N109))

=SOM((IS.ONEVEN(RIJ(N10:N109)))*(N10:N109))

=SOM(N(IS.ONEVEN(RIJ(N10:N109)))*(N10:N109))

Hij moet dus de oneven rijen bij elkaar optellen. Anders gaat hij de ORT getallen er ook bij
optellen.

Als laatste dat hij in cel N113 de optel maakt van cellen I111, J111, K111, L111, M111 & N111.
Dit zodat ik beide uitkomsten onderin kan blijven controleren. Omdat het ene het verschil tussen de fysieke
tijd uitrekent en de andere de ORT uren en dan vervolgens optelt.

Nogmaals heel erg veel dank voor alle hulp. Was me zelf niet gelukt. 'Waardering'. :thumb:

Overigens een alle nog fijne feestdagen toegewenst! !

 

[sylvester-ponte]

probeer deze eens:

 

[RemcoH]

Jaaa!! Dat is hem helemaal. :thumb: :thumb:

De laatste optel som kon ik gelukkig zelf. Toch nog iets. <zucht>

Hoe weet u dit allemaal? Werkt u voor Microsoft? Of gewoon erg goed in programmeren?

Heel erg bedankt. Ik vink de rasterlijnen weg in Excel en voer een achtergrond illustratie in
en hij word helemaal top. THANKS!

As said before...

Sylvester = WAIT FOR IT... L E G E N D A R Y!

 

[RemcoH]

Het uiteindelijke resultaat mede dank zij jullie:









Klein vraagje nog, wanneer ik extra tabbladen toevoeg aan het gehele
document (origineel) krijg ik soms het probleem dat alle waardes naar
##### veranderen. Bron bestand zit nog gewoon in het document.
Moet ik dan iets aan de verwijzingen veranderen?
Kon gelukkig een backup terug zetten waarin alles nog in orde is. Maak
een kopie die ik vervolgens bewerk om data verlies te voorkomen.
Hoe los ik dit op?

Danke Danke Danke.

 

[RemcoH]

Dankje mensen. Heb het al opgelost. Met een omweg, maar no problem.
Ik sluit dit onderwerp. ;-)

 

[Haije]

RemcoH,

puur om van te leren: mogen wij ook weten hoe?

NB: iedereen de beste wensen voor 2014

 

[RemcoH]

Hoi,

Jahoor... kon het origineel niet kopiëren naar een ander Excel tabblad zonder data verlies.
Kon echter wel een compleet nieuw blad aanmaken. Heb gewoon de gegevens blad per blad
over gekopieerd en instellingen aangepast zoals ze waren. Vandaar dat ik zei ''met een omweg."

 

[Haije]

Oké, bedankt voor de terugmelding.