Bekijk bijlage Voorbeeld uren MB.xlsx
Hoi Rinse,
Edit: dit heb ik geplaatst voordat ik jouw reactie zag.
Hierboven mijn versie met formules. Of eigenlijk 1 formule, want de formule in ‘Totalen uren Mei 2015’!B3 (zie onder) is gewoon gekopieerd naar rechts t/m kolom I en naar beneden t/m regel 33 (geplakt met alleen formules om de layout niet overhoop te gooien).
Het mooie van de formule is dat hij zelfs rekening houdt met feestdagen (zoals 5 mei in het voorbeeld): ik heb aangehouden dat alle zon- en feestdagen bij 16% horen en overige dagen bij 10%.
Daartoe heb ik een werkblad toegevoegd met een tabel “Feestdagen_2015”. Voordeel van een tabel is dat je gewoon datums kunt toevoegen en die worden dan automatisch meegenomen. Nu staat alleen 5 mei in de tabel.
Onderstaand duid ik de werkbladen met weekgegevens aan met “weekblad” (LOL
)
Formule in ‘Totalen uren Mei 2015’!B3:
Code:
=ALS(OF(EN(B$2=10%;OF(WEEKDAG($A3;2)=7;ISGETAL(VERGELIJKEN($A3;Feestdagen_2015[Datum];0))));
EN(B$2=16%;WEEKDAG($A3;2)<>7;ISFOUT(VERGELIJKEN($A3;Feestdagen_2015[Datum];0))));
"";
SOM.ALS(INDIRECT("'Week "&WEEKNUMMER($A3;2)&"'!$B$6:$B24");
B$1;
INDIRECT("'Week "&WEEKNUMMER($A3;2)&"'!$"&DEEL("CDEFGHI";WEEKDAG($A3;2);1)&"$6:$"&DEEL("CDEFGHI";WEEKDAG($A3;2);1)&"$24")))
Op het eerste gezicht is het natuurlijk een draak van een formule, maar bij nadere beschouwing valt het eigenlijk wel mee.
Eerst wordt er gecheckt of het betreffende vakje leeg moet blijven (“”), omdat het betreffende percentage (10% of 16%) niet van toepassing is op de betreffende datum:
in de 10%-kolommen komt een “” op zon- en feestdagen;
in de 16%-kolommen komt een “” op overige dagen.
Dit is de conditie in de ALS-functie in de formule:
Code:
OF(EN(B$2=10%;OF(WEEKDAG($A3;2)=7;ISGETAL(VERGELIJKEN($A3;Feestdagen_2015[Datum];0))));EN(B$2=16%;WEEKDAG($A3;2)<>7;ISFOUT(VERGELIJKEN($A3;Feestdagen_2015[Datum];0))))
De eigenlijke berekening is een SOM.ALS functie: de uren in de betreffende kolom van het betreffende weekblad worden opgeteld als de naam in B6:B24 van het betreffende weekblad gelijk is aan de naam op regel 1 van het blad met totalen.
Hierbij wordt met behulp van de INDIRECT-functie het juiste weekblad bepaald op basis van het weeknummer van de datum op de betreffende regel van het werkblad met totalen. Dus bijvoorbeeld bij 1 mei op regel 3 hoort week 18.
Belangrijk punt van aandacht is de weeknummering: als je Excel 2010 of hoger hebt, dan kun je in de WEEKNUMMER-functie het beste werken met 21 als 2e argument (i.p.v. 2).
De juiste kolomletter wordt bepaald op basis van het weekdagnummer van de betreffende datum (ma = 1 t/m zo = 7):
Code:
DEEL("CDEFGHI";WEEKDAG($A3;2);1)
Nog even recapituleren de 3 argumenten van de SOM.ALS functie:
Code:
1. De naam in B6:B24 van het betreffende weekblad: INDIRECT("'Week "&WEEKNUMMER($A3;2)&"'!$B$6:$B24")
2. Moet gelijk zijn aan: B$1
3. Optelbereik: INDIRECT("'Week "&WEEKNUMMER($A3;2)&"'!$"&DEEL("CDEFGHI";WEEKDAG($A3;2);1)&"$6:$"&DEEL("CDEFGHI";WEEKDAG($A3;2);1)&"$24"))
Het lijkt goed te werken. Misschien nog wat bijschaven met foutafhandeling.
