opzoeken en cumulatief optellen

[RdS]

Goededag,

Ik probeer een overzicht te maken om getallen te vergelijken.

2010 zit nu bijvoorbeeld in November.
Nu wil ik graag weten wat er gebeurd is tot/met November 2009.

Ik heb in een sheet de hele data van 2009 staan.
Nu moet excel de cumulatieve waarde opzoeken op basis van de maand die ik selecteer.

Ik selecteer maart, dan moet excel de waarde van Januari+Februari+Maart optellen en het resultaat in een cel zetten.

In tabblad voorblad kan de maand gekozen worden in B2.


Bekijk bijlage cumulatieve opzoekfunctie.xlsx


de functie Sum(index(data!C6:S6,0,Match(voorblad!B2,data!C4:S4,0))) Heb ik geprobeerd, alleen hiermee heb ik de mogelijkheid om losse maanden te selecteren en niet een cumulatieve optelling te maken.

 

[oeldere]

met opzoeken in een tabel?

zie bijlage.

 

[WHER]

Een alternatief zonder tabel.
De oplossing van oeldere geeft wel een beter overzicht van wat je nu juist aan het optellen bent.

 

[RdS]

Hi Wher,

Dankje voor je oplossing. Deze werkt goed.

Maar zou je de formule willen uitleggen wat deze stap voor stap doet ? Ik wil graag in staat zijn om deze ook in het vervolg te gebruiken.

SUM(Data!C6:INDEX(Data!C6:S6,0,MATCH(voorblad!B2,Data!C4:S4,0)))-SUMPRODUCT((LEFT(Data!$C$4:$S$4,1)="Q")*((COLUMN(Data!$C$4:$S$4)-2)<MATCH(voorblad!$B$2,Data!$C$4:$S$4,0))*(Data!$C$6:$S$6))

 

[WHER]

Het eerste deel herken je waarschijnlijk uit je post #1:
"sum(C6 tot index(C6:S6;match etc..)" maakt de som van alle getallen tot april.
Helaas staan daar "kwartaal results" zoals Q1, Q2 etc.. tussen die niet mogen meegeteld worden, dus moeten we die aftrekken van onze som, voor zover ze tussen C6 en de gezochte maand staan.
De sumproduct functie rekent dat uit door te bepalen welke getallen hiervoor in aanmerking komen.
Op rij 4 moet de tekst beginnen met "Q" dus "LEFT(Data!$C$4:$S$4,1)="Q"
De kolom waarin het getal voorkomt moet vóór (links van) de gezochte maand staan, het kolomnummer moet kleiner zijn als de waarde die we vinden via "match": COLUMN(Data!$C$4:$S$4)-2)<MATCH(voorblad!$B$2,Data!$C$4:$S$4,0)
Deze twee voorwaarden geven ons een reeks true/false die na vermenigvuldiging met elkaar veranderen in 1'en en 0'en. Deze reeks 1'en en 0'en vermenigvuldigen we met alle getallen in C6:S6 en tellen deze op (=sumproduct:vermenigvuldig en sommeer), dat geeft ons het totaal van de kwartaalsommen die we van onze som moesten aftrekken.

 

[RdS]

De kolom waarin het getal voorkomt moet vóór (links van) de gezochte maand staan, het kolomnummer moet kleiner zijn als de waarde die we vinden via "match": COLUMN(Data!$C$4:$S$4)-2)<MATCH(voorblad!$B$2,Data!$C$4:$S$4,0)
Deze twee voorwaarden geven ons een reeks true/false die na vermenigvuldiging met elkaar veranderen in 1'en en 0'en. Deze reeks 1'en en 0'en vermenigvuldigen we met alle getallen in C6:S6 en tellen deze op (=sumproduct:vermenigvuldig en sommeer), dat geeft ons het totaal van de kwartaalsommen die we van onze som moesten aftrekken.

Dit gedeelte snap ik nog steeds niet...
Waarom is de -2 er ?
Het vergelijk met de Match snap ik ook niet helemaal.

Kan je het misschien nog eens proberen uit te leggen?

 

[sylvester-ponte]

best ingewikkeld zo'n formule

het kan ook met een zelf gemaakte function:

Function Optelling(Tabel As Range, Tot As String, Optional Niet As String = "Q")
    Dim Kolom As Integer, Rij As Integer
    Rij = 3
    For Kolom = 1 To Tabel.Columns.Count
        If Left(Tabel(1, Kolom), 1) <> Niet Then
            Optelling = Optelling + Tabel(Rij, Kolom)
            If LCase(Tabel(1, Kolom)) = LCase(Tot) Then Exit Function
        End If
    Next Kolom
End Function

dit is meestal makkerlijker te doorgronden en aan te passen.

in het gele vakje staat de function: Bekijk bijlage sumcumul(2.xls

groet sylvester

 

[WHER]

De -2 staat daar omdat "COLUMN(Data!$C$4:$S$4) begint in kolom C = de derde kolom, en we willen beginnen bij 1 omdat de resultaten van "MATCH(voorblad!$B$2,Data!$C$4:$S$4,0)" ook beginnen bij 1.
De beste raad om zulke formules te leren begrijpen (en schrijven) is: gebruik de knop "evaluate formula" uit de toolbar "formula auditing" om te zien hoe de formule zich stap voor stap afwikkelt met alle tussenstappen.
Deze knop/toolbar ziet er uit als in bijlage.

 

[RdS]

Oke, nu snap ik hem.
Het is een mooie formule !

Dankjewel voor de hulp en voor de tip.

 

[RdS]

Nu heb ik hem toegepast op mijn andere bestand.

Alleen om een kwartaal te berekenen is mijn formule een beetje monster achtig geworden.
Februari moet kwartaal 1 zijn. Omdat de gegevens van 2009 in de volgende cel berekend worden kan je niet kiezen voor opzoeken naar Q1.
Via een vlookup heb ik d1,d2,d3 laten vullen op basis van een maand.

=SUM(INDEX('2010'!E559:U559,0,MATCH(itq!$D$1,'2010'!$E$558:$U$558,0)),IF(ISNA(SUM(INDEX('2010'!E559:U559,0,MATCH(itq!$D$2,'2010'!$E$558:$U$558,0)))),0,SUM(INDEX('2010'!E559:U559,0,MATCH(itq!$D$2,'2010'!$E$558:$U$558,0)))),IF(ISNA(SUM(INDEX('2010'!E559:U559,0,MATCH(itq!$D$3,'2010'!$E$558:$U$558,0)))),0,SUM(INDEX('2010'!E559:U559,0,MATCH(itq!$D$3,'2010'!$E$558:$U$558,0)))))


Kan dit niet netter?

 

[sylvester-ponte]

hoi Rds

heb je mijn functie getest?

groet sylvester

 

[RdS]

Hi sylvester,

Nee die heb ik niet geprobeerd. Ik wil liever niet met VBA werken aangezien ik daar nog te weinig vanaf weet.
Wanneer ik het met formules kan redden is beter voor mij.

 

[WHER]

Rds,
Kun je met een klein voorbeeldbestand je vraag in post 10 verduidelijken, op basis van de tekst kan ik me daarbij weinig voorstellen.

 

[RdS]

Ik heb een bestandje toegevoegd.

Even niet kijken naar alle Ref fouten, heb een hoop data verwijderd dus linkjes lopen niet meer.

edit:
Krijg hem niet geupload... na xlsx en zip is het bestand bijna 300kb en kan max 100kb uploaden.