Excel 2010 - kolomverwijzing afronden naar boven mogelijk?

[Mackmora]

Hallo Tweakers community,

Op het werk ben ik bezig om een excel formulier te bouwen waarin we per periode het aantal fouten kunnen opvragen. (en hiervan een grafiek genereren)
Nou zit ik met een probleem om de juiste periode te laten berekenen in de formule.

Type formule: SOMMEN.ALS formule telt het aantal waarden in een kolom op als aan meerdere criteria voldaan wordt.

-$G$34 is een verwijzing naar een dropdown lijst van de periode nummers 1-13.(1 periode = 4 weken) (als er niks ingevuld is dan worden de waarden van de alle data gebruikt voor een Jaar overzicht)
-Logboek C4-C10003 is de kolom waar de weeknummers in staan (gegenereerd a.d.h.v. ingevulde datum)

Optelbereik Criteriumbereik 1 Criterium 1
Formule: SOMMEN.ALS(Logboek!$E$4:$E$10003;Logboek!$D$4:$D$10003;$C$35;
Criteriumbereik 2 Criterium 2
Logboek!$C$4:$C$10003;$G$34)

Optelbereik Criteriumbereik 2 Criterium 1
Formule idee: SOMMEN.ALS(Logboek!$E$4:$E$10003;Logboek!$D$4:$D$10003;$C$35;
Criteriumbereik 2 Criterium 2
AFRONDEN.BOVEN(Logboek$C$4:$C$10003/4;1);$G$34)

Echter werkt dit niet. En ik ben van plan om het excel logboek niet op te vullen met kolommen met waarden die alleen achter de schermen nodig zijn.

Is er een methode om in een SOMMEN.ALS formule verwerkt de periode te achterhalen a.d.h.v. datum of weeknummer?
....
Uitgevoerd in Excel 2010 NL
...
MvG Mackmora

[Arjan90]

Je moet hiervoor, als ik je vraag goed lees, een matrixformule gebruiken. Uit mijn hoofd CTRL+SHIFT+ENTER als je de formule hebt ingevuld. Anders kan er geen berekening worden uitgevoerd over een bereik.

Probeer eens een SOMMEN.ALS met twee criteria: >= de startweek van die periode en <= de eindweek van die periode. Laat je even weten of het is gelukt?

[ Voor 51% gewijzigd door Arjan90 op 03-08-2016 13:19 . Reden: Matrixformule werkt niet ]

[Lustucru]

Ik heb werkelijk geen flauw idee wat je wilt bereiken en wat er niet lukt. Wil je de totalen van één periode? Gebruik dan

code:

Sommen.als([optelbereik];
logboekWeeknrKolom;">=" & [berekening 1e week];
logboekWeeknrKolom;"<=" & [berekening laatste week];)

Voor een mooi grafiekje over de periodes heen gebruik je een draaitabel/grafiek

[Mackmora]

Hallo Beide

@Arjan90
Jouw voorbeeld werkt inderdaad.
Hiermee kan ik dus voor elke periode keuze de begin/eindweek vastleggen in de formule, waarmee dus gefilterd wordt wanneer een periode keuze geselecteerd is.
Dit wordt wel een lange formule zo, 2 x 13 = 26 criteria toevoegen. (Als het makkelijker kan houdt ik me aanbevolen.
Ik laat wel weten wanneer het gelukt is.
Edit: Ik heb nou een half A4 formule gemaakt, maar het werkt wel. Nou nog 59 variaties hiervan maken, en 60 met een ander tabblad verwijzing. Wish me luck.

@Lustucru
Inderdaad de totalen van 1 periode.
Is dit VBA code? Daar heb ik helaas nog geen ervaring mee, dat is nog een doel om te leren.

Hieronder wat uitleg:
Alle metingen die uitgevoerd worden en buiten specificatie of een actiegrens vertonen worden in het logboek ingevuld. (kolom range 10000 om een heel jaar in 1 excelsheet te zetten)
Hierbij wordt een nummer en de datum toegevoegd. omdat er bij ons veel info per week opgeslagen wordt, wordt het weeknummer vanuit de datum berekend. (rapportage gebeurt per periode van 4 weken)
Dan krijg je dus kolom A nummers, B datum, C week, D lijn code, E felskop 1, F felskop 2 etc.
Met de AFRONDEN.BOVEN formule probeer ik dus de waarden in kolom C van de week nummers te delen door 4 en af te ronden op het hele getal. (week 1 = 0.25 = periode 1, week 2 = 0.5 = periode 1)

MvG Mackmora

[ Voor 6% gewijzigd door Mackmora op 08-08-2016 20:54 . Reden: Correctie/toevoeging ]

[Lustucru]

Mackmora schreef op donderdag 04 augustus 2016 @ 18:43:
Edit: Ik heb nou een half A4 formule gemaakt, maar het werkt wel. Nou nog 59 variaties hiervan maken, en 60 met een ander tabblad verwijzing. Wish me luck.

@Lustucru
Inderdaad de totalen van 1 periode.
Is dit VBA code? Daar heb ik helaas nog geen ervaring mee, dat is nog een doel om te leren.

Nee, dat is geen VBA. Gewoon de excelfunctie sommen.als(). Arjan 90 en ik zeggen min of meer hetzelfde. Je rekent niet het weeknummer om naar een periode maar de periode naar mogelijke weeknummers. Om je op weg te helpen een verder uitgewerkt voorbeeld:
Als in kolom C je weeknummers staan, in kolom D de getallen die je op wilt tellen en in A1 staat de periode waarvan je het totaal wilt weten dan doet de volgende formule het werk.

code:

=SOMMEN.ALS($D$1:$D$1000;$C$1:$C$1000;">" & (A1-1)*4;$C$1:$C$1000;"<=" & A1*4)

In gewone taal: tel de waarde in kolom D op, als op dezelfde regel het weeknummer groter is dan het (periodegetal-1)*4 én het weeknummer kleiner is of gelijk aan het weeknummer maar 4. Periode 2 telt dus alles op voor de weeknummers 5 t/m 8.

[Mackmora]

Hallo allen,

Hij is nu gereed, en de methode van Lustucru werkt beter/kleinere formule:
Dit is nu de formule i.p.v. een half A4 in word.
=ALS($G$34>=1;SOMMEN.ALS(Logboek!$E$4:$E$10003;Logboek!$C$4:$C$10003;">"&($G$34-1)*4;Logboek!$C$4:$C$10003;"<="&$G$34*4;Logboek!$D$4:$D$10003;$C$35); SOMMEN.ALS(Logboek!$E$4:$E$10003;Logboek!$D$4:$D$10003;$C$35))

oftewel, Als een periode keuze gemaakt wordt dan geldt ALS waar: formule van Lustucru+ extra criteria; als niet waar: alle waarden berekenen + extra criteria.

Dank allebei
MvG Mackmora