[Excel] som van eerste 12 niet nul cellen?

[tHe_BiNk]

Okay, ik heb heel wat gegoogled, en in mijn excel bible gesnuffeled, maar niks gevonden.



Ik heb een range van vele jaren, nu wil ik weten wat de omzet was van een product in de eerste 12
maanden dat het product op de markt was. Maar de tijdsreeksen beginnen niet op het zelfde tijdstip. Nu is mijn vraag hoe krijg ik de som van de eerst 12 maanden? Vanaf eerste cel (van links naar rechts) die niet nul is plus nog 11 cellen. Hoe doe ik dit, bvd?

[ Voor 3% gewijzigd door tHe_BiNk op 21-02-2005 14:22 ]

[Woudloper]

Kan je dat niet gewoon doen het met een COUNTIF? Als je optelling van de eerste 11 gelijk is aan 0 dan heb je een positief resultaat...

[tHe_BiNk]

Hoe bedoel je dit, ik begrijp het niet?

[sander79]

Ik denk dat je toch een vbscriptje erop los moet laten om dit te laten werken.

[Woudloper]

tHe_BiNk schreef op maandag 21 februari 2005 @ 15:00:
Hoe bedoel je dit, ik begrijp het niet?

Tja, ik bedoelde ook niet COUNT, maar het moet natuurlijk SUM zijn, wat je kan doen is het volgende:

code:

=IF(SUM(A1:K1)>=0;"Eerste 11 gelijk aan 0";"Niet gelijk aan 0")

Kan je daar niets meer?

[F_J_K]

Het punt is dat je dat dan ook voor 2-13, 3-14, etc etc etc moet doen. Lijkt me niet handig

Dus inderdaad een stukje VBA (geen VBScript). Een FOR loopje per regel die loopt tot er een <> 0 langs komt en pakt dan de eerste 12.

[tHe_BiNk]

Kan ik niet gewoon de eerste niet nul cel identificeren (van links naar rechts), dit resultaat in een cel weergeven. En vervolgens dit als input van een sum gebruiken, bvd?

[ Voor 9% gewijzigd door tHe_BiNk op 21-02-2005 15:55 ]

[F_J_K]

Je had de functie VERGELIJKEN kunnen gebruiken, maar helaas is er hier geen sortering die er mee werkt.

[Verwijderd]

Wat dacht je van:

code:

 =SUM(OFFSET(A1;0;COUNTIF(1:1;0);1;12))

voor de eerste regel en dan

code:

 =SUM(OFFSET(A2;0;COUNTIF(2:2;0);1;12))

voor de tweede regel en dan zo verder??

Dit werkt natuurlijk alleen als er verderop in de regel geen nullen voorkomen. (zoals in je voorbeeld)

Ik wil de werking trouwens wel uitleggen als je er zelf met behulp van de Excel-help niet uitkomt.

[BtM909]

Weg met vb(a)script, gewoon lekker in Excel Ik zit met een deadline, dus zet hier even m'n probeersels. Zoals het er nu uitziet moet het gewoon gaan werken

Eerst verzin je een functie die de eerste waarde uit je lijst kan ophalen:

code:

[veld J2] =MATCH(0;A1:Z1)

Waarbij A1:Z1 de gehele range is

Vervolgens ga je een sum doen van 12 kolommen:

code:

=SUM(A1:L1)

Zoals je ziet is die range hier opgebouwd uit een begin en eindpunt. Wat ik bedacht: als je begintpunt met 7 opschuift (jouw voorbeeld regel 1), dan moet het eindpunt ook opschuiven (met offset dus)

code:

=SUM(OFFSET(A1;0;MATCH(0;A1:Z1)):OFFSET(L1;0;MATCH(0;A1:Z1)))

Belangrijk:
A1 is het beginpunt
L1 is de twaalfde kolom (default wil je dus 12 kolommen optellen).

Wellicht kan je met deze leiddraad je probleem oplossen

HTH

Als het goed is wordt die offset juist verplaatst, waardoor je altijd 12 kolommen optelt

Enige

[Verwijderd]

@BTM909:
Jouw oplossing zal niet werken, want je moet niet beginnen te tellen bij de cel met de eerste "0", maar de eerste cel met een grotere waarde dan "0". Verder in grote lijnen is het het zelfde idee als het mijne.

[BtM909]

Verwijderd schreef op maandag 21 februari 2005 @ 16:31:
@BTM909:
Jouw oplossing zal niet werken, want je moet niet beginnen te tellen bij de cel met de eerste "0", maar de eerste cel met een grotere waarde dan "0". Verder in grote lijnen is het het zelfde idee als het mijne.

Weet je wel wat match doet?
En als we het dan toch hebben over 'de andere werkt niet'. Die van mij telt wel netjes aaneengesloten 12 cellen

[ Voor 17% gewijzigd door BtM909 op 21-02-2005 17:11 ]

[Verwijderd]

BtM909 schreef op maandag 21 februari 2005 @ 17:10:
Weet je wel wat match doet?

Ik dacht het tenminste te weten. Die functie van jou zoekt de eerste "0" in het rijtje A1:Z1 en geeft dan de relatieve positie van die "0" in het het rijtje.

BtM909 schreef op maandag 21 februari 2005 @ 17:10:
En als we het dan toch hebben over 'de andere werkt niet'. Die van mij telt wel netjes aaneengesloten 12 cellen

Dat doet die van mij ook

Offtopic: voor de volledigheid: het was zeker niet lullig of afzeikerig bedoelt toen ik beweerde 'dat die van jou niet werkt'. Ik geloof alleen echt niet dat hij werkt en dan zijn de TS en jij er zeker bij gebaat als ik een verbetering post. Als ik ernaast zit en jij kunt me uitleggen waarom, zal ik de eerste zijn om dat toe te geven en heb ik weer wat excel kennis opgedaan. Om een lang verhaal kort te maken: No Offence

[tHe_BiNk]

code:

=VERGELIJKEN(">0";BB4864:FQ4864;1)

Geeft de 'error' #N/B en lijkt niet te werken.

Verwijderd schreef op maandag 21 februari 2005 @ 16:10:
Dit werkt natuurlijk alleen als er verderop in de regel geen nullen voorkomen. (zoals in je voorbeeld)

Er zitten wel nullen verderop in, geen sales, of als het product uit het assortiment gaat.

code:

=SOM(VERSCHUIVING(BB4869;0;AANTAL.ALS(BB4869:FQ4869;0);1;12))

Deze werkt alleen als er vanaf het eerstepunt geen nullen meer voorkomen. Maar dit is zeer vaak niet het geval. Hoe los ik dit op, bvd?

[ Voor 188% gewijzigd door tHe_BiNk op 27-02-2005 14:03 ]

[Thijs B]

Oke kon het niet laten uit te proberen.

http://www.xs4all.nl/~ahzwal/excelnerd.xls

tabel 1 omzet gegevens
tabel 2 indien er omzet in die maand is geweest krijgt de cel waarde 1
tabel 3 tabel 2 kan je optellen de eerste maand zal altijd waarde 1 hebben.
tabel 4 met een voorwaardelijkesom/matrix formule tel je tabel 1 de maanden indien die betreffende maand in tabel 3 de waarde 1 heeft.

hmm denk dat het voorbeeld duidelijker is dan mijn kromme uitleg

In dit voorbeeldje heb je dan de omzet van de eerste 3 maanden..

Zonder vb scripting
als je nooit met voorwaardelijke som hebt gewerkt moet je ff zoeken in de help..(control enter enzo bij invoeren)

[ Voor 15% gewijzigd door Thijs B op 21-02-2005 20:42 ]

[tHe_BiNk]

Thijs B schreef op maandag 21 februari 2005 @ 20:37:
Oke kon het niet laten uit te proberen.

http://www.xs4all.nl/~ahzwal/excelnerd.xls

Werkt perfect
Alleen is het niet echt mooi, zeker in mijn very big datafile (meerdere tabbladen), waardoor het ook niet echt dynamisch kan blijven, maar gelukkig kan ik alles in one go doen, dus who cares!. Thanks a 1.000.000!!!

Problem solved, thanks all for ya help!

[ Voor 7% gewijzigd door tHe_BiNk op 21-02-2005 21:16 ]

[BtM909]

Verwijderd schreef op maandag 21 februari 2005 @ 17:39:
[...]

Ik dacht het tenminste te weten. Die functie van jou zoekt de eerste "0" in het rijtje A1:Z1 en geeft dan de relatieve positie van die "0" in het het rijtje.

Nope, daarom zeg ik beter opletten . Match geeft in dit geval de laatste 0 weer; da's ook de reden dat ik het heb opgesplitst. Offset vraagt hoeveel cellen je wilt offsetten, mag jij raden hoeveel dat er zijn (hint: precies evenveel als de kolom die de laatste 0 bevat )

[...]

Dat doet die van mij ook

Waarom zeg je dan dit

Dit werkt natuurlijk alleen als er verderop in de regel geen nullen voorkomen. (zoals in je voorbeeld)

Wat die van mij dus wel netjes doet!

Offtopic: voor de volledigheid: het was zeker niet lullig of afzeikerig bedoelt toen ik beweerde 'dat die van jou niet werkt'. Ik geloof alleen echt niet dat hij werkt en dan zijn de TS en jij er zeker bij gebaat als ik een verbetering post. Als ik ernaast zit en jij kunt me uitleggen waarom, zal ik de eerste zijn om dat toe te geven en heb ik weer wat excel kennis opgedaan. Om een lang verhaal kort te maken: No Offence

Nope, maar vind het iets te kort door de bocht door zonder te testen gaat aannemen dat andermans code fout is. Denk je dat het fout is, dan wil ik best met je in discussie. Het wordt alleen een raar verhaaltje als jij niet hebt gechecked of het uberhaupt werkt

Even voor de duidelijkheid (en daarin hebben we beide dezelfde mening): ik wil jou niet afzeiken en zag jouw post niet als afzeikmateriaal. Ik probeer alleen m'n steentje bij te dragen

---

tHe_BiNk schreef op maandag 21 februari 2005 @ 21:14:
[...]


Werkt perfect
Alleen is het niet echt mooi, zeker in mijn very big datafile (meerdere tabbladen), waardoor het ook niet echt dynamisch kan blijven, maar gelukkig kan ik alles in one go doen, dus who cares!. Thanks a 1.000.000!!!

Problem solved, thanks all for ya help!

Zonder te pochen over mijn oplossing of die van plevuus ;): waarom probeer je het niet met een relatief simpele formule, bij onze oplossingen heb je een veld extra nodig. Voor zover ik zie heb je in de ExcelNerd iets meer cellen nodig

[ Voor 18% gewijzigd door BtM909 op 21-02-2005 21:24 ]

[tHe_BiNk]

BtM909 schreef op maandag 21 februari 2005 @ 21:22:
Zonder te pochen over mijn oplossing of die van plevuus ;): waarom probeer je het niet met een relatief simpele formule, bij onze oplossingen heb je een veld extra nodig. Voor zover ik zie heb je in de ExcelNerd iets meer cellen nodig

Als jij het ExcelNerd idee, wat werkt, in een formule kan zetten, ben je een held. Ik ben geen excel expert, dus ik ben al enorm blij, dat ik nu weet hoe ik het kan doen, kost een dag maar dan heb je ook wat.

[Thijs B]

ik geef gelijk toe, niet de mooiste oplossing , maar ik zou verder echt niet weten hoe je zo iets kan doen zonder vb scripting..

je kan de tabellen om het uit te rekenen ipv onder elkaar ook naast elkaar maken, en dan wat slimmer indelen waardoor je het dan met kopieren makkelijker op meerdere tab bladen enzo kan toepassen.

Wat wel een probleem kan zijn is dat de matrix formule bij 10.000+ regels ofzo aardig wat rekenkracht gaat verbruiken.....

En misschien kan je met een combinatie van bovenstaande voorbeelden en deze de boel wat efficienter maken.

[Verwijderd]

het voorbeeldbereik in onderstaande matrixformule moet je uiteraard aanpassen. om engels- naar nederlandstalige functies om te zetten kan je bv. googelen op ingrid bapleu, op haar site staat wel een tooltje als ik me goed herinner. bij matrixformules breng je de { } haakjes niet zelf in, maar bij het inbrengen van de formule druk je op ctrl+shift+enter, ipv. gewoon enter.

code:

={SUM(INDIRECT(ADDRESS(0;MATCH(TRUE;A1:S1<>0;0);3;FALSE) & ":" & ADDRESS(0;MATCH(TRUE;A1:S1<>0;0)+11;3;FALSE);FALSE))}
={SOM(INDIRECT(ADRES(0;VERGELIJKEN(WAAR;A1:S1<>0;0);3;ONWAAR) & ":" & ADRES(0;VERGELIJKEN(WAAR;A1:S1<>0;0)+11;3;ONWAAR);ONWAAR))}

edit:het lijkt me dat deze formule exact doet wat de TS vraagt.

[ Voor 21% gewijzigd door Verwijderd op 23-02-2005 12:20 . Reden: kleine verbetering (+11 ipv. +12) +vertaling ]

[Verwijderd]

BtM909 schreef op maandag 21 februari 2005 @ 21:22:
Nope, daarom zeg ik beter opletten . Match geeft in dit geval de laatste 0 weer;

Je hebt gelijk!! Match geeft inderdaad de laatste 0 en niet de eerste 0. Ik had in eerste instantie zelf ook wat zitten rommelen met Match en toen leek hij de eerste 0 te geven. Blijkbaar toch iets te rommelig liggen rommelen.

Waarom zeg je dan dit

• Dit werkt natuurlijk alleen als er verderop in de regel geen nullen voorkomen. (zoals in je voorbeeld)

Die formule van mij telt ook 12 aaneengesloten kolomen. Het gaat alleen mis als je een rijtje in de volgende vorm hebt:

code:

0 0 0 0 0 12 45 78 0 89 56 45 .......

dan telt hij vanaf de 45 ipv vanaf de 12 en telt hij de 0 tussen 78 en 89 ook mee terwijl dat niet moest. Als ik nu jouw formule wel goed begrijp, gaat het daarmee ook mis in zo'n situatie.

Nope, maar vind het iets te kort door de bocht door zonder te testen gaat aannemen dat andermans code fout is. Denk je dat het fout is, dan wil ik best met je in discussie. Het wordt alleen een raar verhaaltje als jij niet hebt gechecked of het uberhaupt werkt

Ik had het dus wel getest. Alleen had ik dat niet goed gedaan.

Even voor de duidelijkheid (en daarin hebben we beide dezelfde mening): ik wil jou niet afzeiken en zag jouw post niet als afzeikmateriaal. Ik probeer alleen m'n steentje bij te dragen

Mooi

[BtM909]

Verwijderd schreef op woensdag 23 februari 2005 @ 11:26:
[...]
Die formule van mij telt ook 12 aaneengesloten kolomen. Het gaat alleen mis als je een rijtje in de volgende vorm hebt:

code:

0 0 0 0 0 12 45 78 0 89 56 45 .......

dan telt hij vanaf de 45 ipv vanaf de 12 en telt hij de 0 tussen 78 en 89 ook mee terwijl dat niet moest. Als ik nu jouw formule wel goed begrijp, gaat het daarmee ook mis in zo'n situatie.

Kijk zijn we het weer met elkaar eens.. TS wil nl. dat die 0 tussen 78 en 89 meegeteld wordt Dus beide oplossingen werken

pfew, nu de TS nog even overtuigen

[Verwijderd]

BtM909 schreef op woensdag 23 februari 2005 @ 11:27:
[...]

Kijk zijn we het weer met elkaar eens.. TS wil nl. dat die 0 tussen 78 en 89 meegeteld wordt Dus beide oplossingen werken

pfew, nu de TS nog even overtuigen

je zei net dat jouw voorstel naar de laatste nul zoekt. van zodra dat er een nul ergens in het bereik staat zal hij dus vanaf daar beginnen te tellen (89 ipv 12 in het voorbeeld), wat foutief is. ook wanneer er lege cellen zijn achteraan het bereik, zal het foutlopen.

[Verwijderd]

BtM909 schreef op woensdag 23 februari 2005 @ 11:27:
Dus beide oplossingen werken

NEE! Beide oplossingen werken niet. Het gaat mis in het genoemde voorbeeld rijtje:

code:

0 0 0 0 0 12 45 78 0 89 56 45 .......

Mijn formule zal beginnen bij de 45 en vanaf daar 12 kolommen tellen. Jouw formule zal (als ik hem goed begrepen heb) bij de 89 beginnen en vanaf daar 12 kolommen tellen. Beide niet wat de TS wil. Onze formules werken (zoals ik gelijk al had aangegeven) aleen goed als geen tussenliggende nullen zijn. BV:

code:

0 0 0 0 0 12 45 78 65 89 56 45 .......

De formule van Heretic zou wel eens kunnen werken. Ik heb nu helaas geen tijd om me te verdiepen in matrixformules om het goed te kunnen controleren.

[tHe_BiNk]

code:

={SUM(INDIRECT(ADDRESS(0;MATCH(TRUE;A1:S1<>0;0);3;FALSE) & ":" & ADDRESS(0;MATCH(TRUE;A1:S1<>0;0)+12;3;FALSE);FALSE))}

Deze werkt, alleen met je +12 gebruiken en niet +11.

[ Voor 54% gewijzigd door tHe_BiNk op 27-02-2005 15:22 ]

[Verwijderd]

ik vond het al raar dat ik me eerst vergist zou hebben.
nee hoor, blij dat het werkt.

[jlrensen]

Volgens mij kan het ook wat simpeler, en zonder extra formules:

ik heb in rij 1, kolom A t/m Q de volgende waarden gezet:

0 0 0 4 5 0 5 4 3 12 2 3 6 7 2 8 9

het aantal cellen aan het begin dat 0 heeft, kun je dan als volgt vinden: =MATCH(0;A1:Q1;1)
deze formule geeft het hoogste volgnummer waar het getal exact 0 is in de gegeven range, in dit geval 3

Hiermee kun je dan OFFSET gebruiken: =SUM(OFFSET(A1:L1;0;MATCH(0;A1:Q1;1)))

neem de som van A1 t/m L1, dit zijn twaalf cellen, en schuif dat 3 (de gevonden waarde op)

Kort, simpel en zonder hulpcellen

[Verwijderd]

bij bepaalde herhalingen van nul waarden gaat dit ook niet correct werken (het werkt wel in het voorbeeld dat je gegeven hebt)