Beste creatievelingen,
Met Excel ben ik al een tijd aan het stoeien om in een planningsbestand, een aantal optellingen te doen aan de hand van een somproduct formule. (matrix)
In het planningsblad staan:
in kolom F9:F296 per regel verschillende disciplines vermeld.
In kolom G9:G296 per regel verschillende vestigingen vermeld.
In kolom M9:M296 per regel wel of geen uren verschilt per regel (M is een specifieke dag, N de dag er na, etc…
In een rekenblad heb ik een soort matrix (kruistabel) waarmee ik aan de hand van “x” kan vinken of ik een zoekterm wel of niet wil gebruiken. Ik wil dan doormiddel van een formule de gegevens uit de planning filteren. Bijvoorbeeld, alle E uren op vestiging 2 en 3. Zie voorbeeld.
A B C D E F G H
1 Dis. 1 Dis. 2 Dis. 3 Vest. 1 Vest. 2 Vest. 3 Vest.4 Dag (M)
2 X x X Formule
3 x X X x Formule
4 x X x X X Formule
5 x x Formule
De bedoeling is dat het volgende gebeurt:
Kolom A t/m C = zoekterm 1 (kunnen meerdere zoektermen zijn, bijvoorbeeld rij 3 waar gezocht wordt op Dis. 1 en Dis.2
Kolom D t/m G = zoekterm 2, dit kunnen ook meerdere zoektermen zijn zoals hier boven omschreven.
De formule zou moeten zijn als: Als (A of B of C = X ) en (D of E of F of G = X) dan aangekruiste velden opzoeken en optellen. Dus als voorbeeld als: A=X, D=X en E=X, dan regels optellen waarbij A+D of A+E is.
Voorbeeld formule in cel H2 :
Deze formule functioneert wel, alleen kijkt die enkel naar 1 discipline. In dit geval alleen in kolom A
Deze formule kijkt alleen naar 1 discipline per vestiging en ik zou formule een aantal keer achter elkaar kunnen plakken waardoor er een hele lange formule ontstaat die doet wat ik wil. Maar nu zoek ik een korte formule om ruimte en rekentijd te besparen. De formule komt een hele boel keer voor op het rekenblad.
Dit is ongeveer het geen dat ik zoek, alleen functioneert deze dan niet. Omdat ik met deze formule zoek met twee variabelen, willekeurige invulling in kolom A2
2 en in E2:H2.
En dit is de formule aan elkaar geplakt die werkt, maar lang is, en zeker als je hem 5 keer moet hebben om 5 dagen bij elkaar te pakken om een werkweek te hebben.
Deze formule doet voor het voorbeeld wat ik verlang, al heb ik wel het aantal criteria verminderd in het voorbeeld.
Formule in Cel H2 en dan door kopiëren naar beneden.
En als je deze dan 5x aan elkaar plakt, heb je een werkweek te pakken, alleen is ie dan wel erg lang. (de M kolomen worden dan wel N, … en daarna O, en P en Q) (zie voorbeeld in cel H12 en I12)
Het geen dat ik eigenlijk wil bereiken is dat ik niet per regel een aparte formule heb staan, maar voor iedere regel de zelfde, voor het gemak.
Mocht iemand een idee hebben wat mij een eind op weg zou helpen graag. ’t Is een soort uit de hand gelopen hobby project van me geworden.
Oh ja, ik gebruik overigens Excel 2007
Alvast bedankt,
Martijn
Ps. Als voorbeeld heb ik er een bijlage bijgedaan.
Met Excel ben ik al een tijd aan het stoeien om in een planningsbestand, een aantal optellingen te doen aan de hand van een somproduct formule. (matrix)
In het planningsblad staan:
in kolom F9:F296 per regel verschillende disciplines vermeld.
In kolom G9:G296 per regel verschillende vestigingen vermeld.
In kolom M9:M296 per regel wel of geen uren verschilt per regel (M is een specifieke dag, N de dag er na, etc…
In een rekenblad heb ik een soort matrix (kruistabel) waarmee ik aan de hand van “x” kan vinken of ik een zoekterm wel of niet wil gebruiken. Ik wil dan doormiddel van een formule de gegevens uit de planning filteren. Bijvoorbeeld, alle E uren op vestiging 2 en 3. Zie voorbeeld.
A B C D E F G H
1 Dis. 1 Dis. 2 Dis. 3 Vest. 1 Vest. 2 Vest. 3 Vest.4 Dag (M)
2 X x X Formule
3 x X X x Formule
4 x X x X X Formule
5 x x Formule
De bedoeling is dat het volgende gebeurt:
Kolom A t/m C = zoekterm 1 (kunnen meerdere zoektermen zijn, bijvoorbeeld rij 3 waar gezocht wordt op Dis. 1 en Dis.2
Kolom D t/m G = zoekterm 2, dit kunnen ook meerdere zoektermen zijn zoals hier boven omschreven.
De formule zou moeten zijn als: Als (A of B of C = X ) en (D of E of F of G = X) dan aangekruiste velden opzoeken en optellen. Dus als voorbeeld als: A=X, D=X en E=X, dan regels optellen waarbij A+D of A+E is.
Voorbeeld formule in cel H2 :
Code:
=SOM(SOMPRODUCT(ALS(((Planning!$F$9:$F$296=$A$1)*($A2="x")*(Planning!$G$9:$G$296=$D$1:$G$1)*($D2:$G2="x"));Planning!M$9:M$296)))Deze formule kijkt alleen naar 1 discipline per vestiging en ik zou formule een aantal keer achter elkaar kunnen plakken waardoor er een hele lange formule ontstaat die doet wat ik wil. Maar nu zoek ik een korte formule om ruimte en rekentijd te besparen. De formule komt een hele boel keer voor op het rekenblad.
Dit is ongeveer het geen dat ik zoek, alleen functioneert deze dan niet. Omdat ik met deze formule zoek met twee variabelen, willekeurige invulling in kolom A2
2 en in E2:H2.
Code:
=SOM(SOMPRODUCT(ALS(((Planning!$F$9:$F$296=$A$1:$D$1)*($A2:$D2="x")*(Planning!$G$9:$G$296=$E$1:$H$1)*($E2:$H2="x"));Planning!M$9:M$296)))En dit is de formule aan elkaar geplakt die werkt, maar lang is, en zeker als je hem 5 keer moet hebben om 5 dagen bij elkaar te pakken om een werkweek te hebben.
Deze formule doet voor het voorbeeld wat ik verlang, al heb ik wel het aantal criteria verminderd in het voorbeeld.
Formule in Cel H2 en dan door kopiëren naar beneden.
Code:
=SOM(SOMPRODUCT(ALS(((Planning!$F$9:$F$296=$D$1:$G$1)*($D2:$G2="x")*(Planning!$G$9:$G$296=$A$1)*($A2="x"));Planning!M$9:M$296));SOMPRODUCT(ALS(((Planning!$F$9:$F$296=$D$1:$G$1)*($D2:$G2="x")*(Planning!$G$9:$G$296=$B$1)*($B2="x"));Planning!M$9:M$296));SOMPRODUCT(ALS(((Planning!$F$9:$F$296=$D$1:$G$1)*($D2:$G2="x")*(Planning!$G$9:$G$296=$C$1)*($C2="x"));Planning!M$9:M$296)))En als je deze dan 5x aan elkaar plakt, heb je een werkweek te pakken, alleen is ie dan wel erg lang. (de M kolomen worden dan wel N, … en daarna O, en P en Q) (zie voorbeeld in cel H12 en I12)
Het geen dat ik eigenlijk wil bereiken is dat ik niet per regel een aparte formule heb staan, maar voor iedere regel de zelfde, voor het gemak.
Mocht iemand een idee hebben wat mij een eind op weg zou helpen graag. ’t Is een soort uit de hand gelopen hobby project van me geworden.
Oh ja, ik gebruik overigens Excel 2007
Alvast bedankt,
Martijn
Ps. Als voorbeeld heb ik er een bijlage bijgedaan.
Laatst bewerkt door een moderator:
