[excel 2007] laatste 10 waarden kolom optellen

[nockelyk]

hey

kan iemand me vertellen hoe ik via excel in staat ben om de laatste 10 waarden van een kolom automatisch op te tellen? Dus excel selecteert de laatste 10 waarden van een kolom en telt deze allemaal op. Het probleem is ook dat er in de kolom door elkaar al dan niet waarden staan, door middel van de ALS functie werd ofwel een "-" toegewezen ofwel een getal,het is de bedoeling dat excel enkel de laatste 10 getallen optelt (dus de streepjes niet meegeteld).

Eveneens zoek ik ook een formule om de allereerste geldige waarde (dus opnieuw geen streepje) te vinden in een kolom.

Kan iemand me helpen?

[Reptile209]

Ik zou denk ik met een macro aan de slag gaan. Zoek de laatste rij in je sheet op (kan ongetwijfeld met een standaardfunctie, maar desnoods via een loop), tel terug totdat je 10 waarden hebt gevonden (of tot je bij de eerste rij bent). Idem met je tweede vraag: begin in rij 1 en zoek totdat je de eerste waarde tegenkomt anders dan een streepje.

[nockelyk]

bestaat er geen andere manier dan met macro, want om eerlijk te zijn heb ik daar niet echt kaas van gegeten van macro's, ken er amper iets van...

[pedorus]

Nou, in formule-vorm is het vrij lastig, dus daarom zal een macro sneller zijn.

Stel dat de waarden in a1:a40 staan. Voor probleem 1 (laatste 10 echte waardes opgeteld, of alle waardes als het er minder zijn):

code:

=SUM(IF(SUBTOTAL(2,INDIRECT("A"&ROW(1:40)&":A40"))<=10,A1:A40,0))

Dit is een array formula, invoeren met ctrl-shift-enter. Het kan ook zonder array formula trouwens, iets sneller en nog onduidelijker:

code:

=SUMPRODUCT(--(SUBTOTAL(2,INDIRECT("A"&ROW(1:40)&":A40"))<=10),A1:A40)

Voor probleem 2 (eerste geen-streepje):

code:

=INDEX(A1:A40,MATCH(TRUE,A1:A40<>"-",0))

Dit is ook een array formula. Of (eerste nummer):

code:

=INDEX(A1:A40,MATCH(TRUE,ISNUMBER(A1:A40),0))

(wat ook een array formula is)

[nockelyk]

echt bedankt voor uw hulp, ben ik héél veel mee , die methode voor de som van de laatste 10 variabelen te bepalen werkt. Na wat prutsen ben ik er dan ook in geslaagd om ze te gebruiken voor mijn variabelen.
maar in de methode om het eerste getal te bepalen moet een foutje zitten want hij geeft voortdurend een foutmelding, al verschillende dingen geprobeerd maar lukt maar niet...

[pedorus]

nockelyk schreef op woensdag 12 november 2008 @ 21:18:
maar in de methode om het eerste getal te bepalen moet een foutje zitten want hij geeft voortdurend een foutmelding, al verschillende dingen geprobeerd maar lukt maar niet...

Als je ctrl-shift-enter vergeet bij het invoeren, dan krijg je #N/A. Nadat je op ctrl-shift-enter hebt gedrukt ziet het er zo uit:

code:

{=INDEX(A1:A40,MATCH(TRUE,ISNUMBER(A1:A40),0))}

Ze zijn getest, vooral omdat het 1e probleem vrij tricky is...

[Verwijderd]

nockelyk schreef op woensdag 12 november 2008 @ 21:18:echt bedankt voor uw hulp, ben ik héél veel mee , die methode voor de som van de laatste 10 variabelen te bepalen werkt. Na wat prutsen ben ik er dan ook in geslaagd om ze te gebruiken voor mijn variabelen.
maar in de methode om het eerste getal te bepalen moet een foutje zitten want hij geeft voortdurend een foutmelding, al verschillende dingen geprobeerd maar lukt maar niet...

pedorus schreef op woensdag 12 november 2008 @ 21:57:Als je ctrl-shift-enter vergeet bij het invoeren, dan krijg je #N/A. Nadat je op ctrl-shift-enter hebt gedrukt ziet het er zo uit:

code:

{=INDEX(A1:A40,MATCH(TRUE,ISNUMBER(A1:A40),0))}

Ze zijn getest, vooral omdat het 1e probleem vrij tricky is...

als je de ctrl-shift-enter combinatie correct uitvoert, dan blijft meest waarschijnlijk vervanging van de komma door de puntkomma.als mogelijk probleem over.

heel erg netjes, pedorus!

[nockelyk]

het lukt nog steeds niet hoor, maar ben vrij zeker dat het aan mijn computer ligt, dit weekend ga ik naar huis, ik zal het daar nog eens proberen en waarschijnlijk lukt het op de computer thuis wel. ik laat zeker weten of het dan niet lukt

[nockelyk]

nog een klein vraagje, hoe kan ik in die eerste formule in plaats van de som van de laatste 10 variabelen, de laatste 3 optellen? Ik dacht dat ik enkel diende die "10" te vervangen door een 3 maar dan klopt de uitkomst niet meer...

=SUM(IF(SUBTOTAL(2,INDIRECT("A"&ROW(1:40)&":A40"))<=10,A1:A40,0))

[pedorus]

Nou, die "10" slaat toch echt op de 10 in "de laatste 10". Als je hem in 3 verandert, krijg je dus "de laatste 3". Misschien vergeet je ctrl-shift-enter ofzo (dan krijg je 0, of de som van de kolom)

[Chris03]

Waarom vervang je de streepjes in de ALS-formules niet door nulletjes? Met streepjes kun je nu eenmaal niet rekenen, dus je maakt het jezelf niet bepaald makkelijk.

Je kunt de nulletjes zelfs opmaken als streepjes, zodat je hetzelfde ziet als nu (als je er echt aan gehecht bent).

Bij celeigenschappen (CTRL+1) kun je kiezen voor aangepaste opmaak, dan kun je ook streepjes selecteren voor nulletjes.

[Verwijderd]

nockelyk schreef op donderdag 13 november 2008 @ 21:24:
nog een klein vraagje, hoe kan ik in die eerste formule in plaats van de som van de laatste 10 variabelen, de laatste 3 optellen? Ik dacht dat ik enkel diende die "10" te vervangen door een 3 maar dan klopt de uitkomst niet meer...

dat is nochtans precies wat je moet doen :

code:

=SOM(ALS(SUBTOTAAL(2;INDIRECT("A"&RIJ(1:40)&":A40"))<=3;A1:A40;0))

bevestigen met ctrl+shift+enter : om volledig duidelijk te zijn, in plaats dat je gewoon enter drukt om de formule te bevestigen, moet je een matrixformule bevestigen met c+s+e : je houdt CTRL en SHIFT toets ingedrukt, druk bijkomend enter in en laat alle toetsen los. het volgende verschijnt in de formulebalk

code:

{=SOM(ALS(SUBTOTAAL(2;INDIRECT("A"&RIJ(1:40)&":A40"))<=3;A1:A40;0))}

bemerk de accolades die een matrix(=array)formule aanduiden.
bij foutmeldingen kan je als alternatief dit proberen in te voeren via de visual basic editor. selecteer de cel waarin de formule moet komen en druk de toetsencombinatie ALT+F11 in om naar het vbe-venster te gaan, daarna op CTRL+g om naar het direct venster te gaan. plak daarin hetvolgende

Visual Basic:

activecell.FormulaArray="=SUM(IF(SUBTOTAL(2,INDIRECT(""A""&ROW(1:40)&"":A40""))<=3,A1:A40,0))"