[Excel 2008] Hoe 3x3 sommatie 'slepen'? *

[Mag]

Ik vond het onnodig om een nieuw topic voor deze kleine vraag te openen dus vraag hem hier maar (als de mods liever hebben dat ik alsnog/voortaan liever een nieuwe topic open voor kleine vragen laat me dit dan aub weten).

Als ik in een cel, bijvoorbeeld A1, alle getallen in een grid van bijvoorbeeld 3 bij 3 bij elkaar wil optellen, dus bijvoorbeeld B1:D3, hoe kan ik het dan zo krijgen dat in cel A2 niet B2:D4 wordt geteld maar de 3bij3 grid onder de vorige, dus B4:D6?

[Bolukan]

kijk eens bij verschuiving in combinatie met som. Dus iets van =SOM(Verschuiving(x,y,xx,yy))

[Mag]

Bolukan schreef op maandag 06 oktober 2008 @ 20:02:
kijk eens bij verschuiving in combinatie met som. Dus iets van =SOM(Verschuiving(x,y,xx,yy))

Dit helpt ook niet. Ik heb ondertussen mijn workmap iets aangepast zodat ik alleen maar de cellen B1:B4 hoef op te tellen in A1, dan B5:B8 in A2, etc. Ik wil dus echter dat ik een formule krijg die ik kan "uitrekken" in kolom A.

[F_J_K]

Verander =SOM(Verschuiving(A,x,y,xx,yy)) een klein beetje zodat je niet met x werkt, maar met 3*(RIJ()-offset).

Als ik begin in B3 en de formule begint in A3, werkt dit prima:

=SOM(VERSCHUIVING($B$3;3*(RIJ()-3);0;3;3))

18	1	1	1
45	2	2	2
72	3	3	3
	4	4	4
	5	5	5
	6	6	6
	7	7	7
	8	8	8
	9	9	9

offtopic:
Overigens is een kleine vraag zelf te beantwoorden en blijkt deze vraag best een eigen topic waard. Ik zal dus wel even afsplitsen

[Mag]

F_J_K schreef op dinsdag 07 oktober 2008 @ 08:38:
Verander =SOM(Verschuiving(A,x,y,xx,yy)) een klein beetje zodat je niet met x werkt, maar met 3*(RIJ()-offset).

Als ik begin in B3 en de formule begint in A3, werkt dit prima:

=SOM(VERSCHUIVING($B$3;3*(RIJ()-3);0;3;3))

18	1	1	1
45	2	2	2
72	3	3	3
	4	4	4
	5	5	5
	6	6	6
	7	7	7
	8	8	8
	9	9	9

offtopic:
Overigens is een kleine vraag zelf te beantwoorden en blijkt deze vraag best een eigen topic waard. Ik zal dus wel even afsplitsen

Zoals ik al zei heb ik wat veranderd wat ook een beter overzicht gaf, ik heb zeg maar elke rij bij elkaar opgeteld zodat ik ipv een grid van 3x3 te sommeren een grid van 3x1 sommeer, of in mijn geval 4x1 (dus 1 kolom breed, en 4 rijen onder elkaar).
Ik heb geprobeerd jouw formule daarvoor aan te passen maar zonder succes.
Om even wat specifieker te zijn over mijn workmap nu;
J2 t/m J4 moeten worden gesommeerd in M2.
J5 t/m J8 moeten worden gesommeerd in M3
etc

[F_J_K]

Ik zag dat latrer pas en bedacht dat het zonde van m'n tijd is om het aan te passen

Maar mijn methode is zo te zien nog steeds prima bruikbaar - alleen met wat andere waarden en die uittypen lijkt me niet nodig. Waar loop je dan vast / wat is dan je code?

[Verwijderd]

Laat maar. Ik ga hier geen oplossingen posten als de TS daar blijkbaar geen interesse in heeft.

[ Voor 77% gewijzigd door Verwijderd op 11-10-2008 21:28 ]

[Mag]

F_J_K schreef op dinsdag 07 oktober 2008 @ 11:08:
Ik zag dat latrer pas en bedacht dat het zonde van m'n tijd is om het aan te passen

Maar mijn methode is zo te zien nog steeds prima bruikbaar - alleen met wat andere waarden en die uittypen lijkt me niet nodig. Waar loop je dan vast / wat is dan je code?

Ik weet niet precies waar alle waardes voor waren dus ik heb geprobeerd =SOM(VERSCHUIVING($B$3;3*(RIJ()-3);0;3;3)) aan te passen;
=SOM(VERSCHUIVING($J$2;4*(RIJ()-4);0;4;1))

[F_J_K]

Ik weet niet precies waar alle waardes voor waren

Dat wil je sowieso weten voor je het 'in productie' gaat nemen, dus zoek even op wat de precieze eeeuhm functie van elke functie is en wat welke parameter doet. Reken dan eens met de hand, op papier door wat er in jouw code gebeurt (beginnend bij M2?), dan zie je waar het fout gaat

Edit: je komt dus uit bij negatieve getallen en dat zal niet de bedoeling zijn. Je offset klopt niet.

offtopic:
grizzlybeer: best jammer dat het met mij eens zijn als risico wordt bestempeld

[ Voor 22% gewijzigd door F_J_K op 07-10-2008 12:35 ]

[Verwijderd]

Laat maar. Ik ga hier geen oplossingen posten als de TS daar blijkbaar geen interesse in heeft.

[ Voor 89% gewijzigd door Verwijderd op 11-10-2008 21:28 ]

[Mag]

F_J_K schreef op dinsdag 07 oktober 2008 @ 12:21:
[...]
Dat wil je sowieso weten voor je het 'in productie' gaat nemen, dus zoek even op wat de precieze eeeuhm functie van elke functie is en wat welke parameter doet. Reken dan eens met de hand, op papier door wat er in jouw code gebeurt (beginnend bij M2?), dan zie je waar het fout gaat

Edit: je komt dus uit bij negatieve getallen en dat zal niet de bedoeling zijn. Je offset klopt niet.

offtopic:
grizzlybeer: best jammer dat het met mij eens zijn als risico wordt bestempeld

=SOM(VERSCHUIVING($J$2;4;1;4;1))

VERSCHUIVING(verw;rijen;kolommen;hoogte;breedte)

verw: ik verwijs naar de eerste cel; J2
rijen: het gaat om 4 cellen onder elkaar, 4 rijen dus; 4
kolommen: het is 1 kolom breed; 1
hoogte & breedte: hier loop ik een beetje vast, ik heb uiteraard de help functie van excel erbij gepakt e.d. maar wat is het verschil tussen hoogte en breedte en rijen en kolommen?
Ook snap ik niet waarom je de "RIJ" functie er tussen heb gedaan, ik neem aan dat dat, of de hoogte/breedte integer te maken heeft met het feit dat de 2e cel, M3, moet verwijzen naar J6?

[F_J_K]

Het verschil tussen rijen (omlaag) en hoogte (van de selectie) is toch duidelijk omschreven?
VERSCHUIVING($J$2;4;1;4;1)
Begin bij J2; ga 4 omlaag = J6; ga een naar rechts = K6; neem de volgende vier omlaag = K6:K9; K6:K9.
Sleep een regel naar beneden.
Begin bij J3; ga 4 omlaag = J7; ga een naar rechts = K7; neem de volgende vier omlaag = K7:K10; K7:K10.

Dan weet je meteen waarom die rij nodig is: om te zorgen dat je de offset goed zet (namelijk vier omlaag en niet een omlaag).

[Mag]

F_J_K schreef op dinsdag 07 oktober 2008 @ 13:36:
Het verschil tussen rijen (omlaag) en hoogte (van de selectie) is toch duidelijk omschreven?
VERSCHUIVING($J$2;4;1;4;1)
Begin bij J2; ga 4 omlaag = J6; ga een naar rechts = K6; neem de volgende vier omlaag = K6:K9; K6:K9.
Sleep een regel naar beneden.
Begin bij J3; ga 4 omlaag = J7; ga een naar rechts = K7; neem de volgende vier omlaag = K7:K10; K7:K10.

Dan weet je meteen waarom die rij nodig is: om te zorgen dat je de offset goed zet (namelijk vier omlaag en niet een omlaag).

Ok ik begin het een beetje te begrijpen . Alleen nog niet echt genoeg om ook echt te snappen hoe ik het moet verwerken. Ik zit nu maar gewoon random getalletjes te veranderen in de hoop dat het goed komt, maar wat ik ook doe, in de volgende cel, M3 dus, blijft verwezen worden naar J3 in plaats van J6

[Mag]

=SOM(VERSCHUIVING($J$2;4*(RIJ()+1);0;4;1))

Dat zou toch moeten kloppen? Het werkt in ieder geval niet... Damn ik snap er echt niks van

[F_J_K]

Waar staat de code en waar staat de data?

NB stel dat de code staat in A2, A3, etc. Dan betekent 4*(RIJ()+1 betekent dat je aangeeft dat in A2 komt de som van J10 t/m J13 en in A2 de som van J14 t/m J17.
Zeg ik even uit m'n hoofd zonder F1 te checken hoe het ook alweer zat.

[ Voor 16% gewijzigd door F_J_K op 08-10-2008 20:55 ]

[Mag]

F_J_K schreef op woensdag 08 oktober 2008 @ 20:52:
Waar staat de code en waar staat de data?

NB stel dat de code staat in A2, A3, etc. Dan betekent 4*(RIJ()+1 betekent dat je aangeeft dat in A2 komt de som van J10 t/m J13 en in A2 de som van J14 t/m J17.
Zeg ik even uit m'n hoofd zonder F1 te checken hoe het ook alweer zat.

De code staat in de kolom M
De data in kolom J.
In M2 moet J2:J5 bij elkaar opgeteld worden, in M3 J6:9, etc.

[Mag]

Ik hoop dat een bumpje mij niet kwalijk wordt genomen.

[CoRrRan]

Dit werkt voor mij in Open Office, dus ik weet niet helemaal of Excel het ook op dezelfde manier oplost, maar ik geloof dat het precies hetzelfde is:

code:

=SUM(OFFSET($J$2;4*(ROW(M2)-2);0;4;1))

Deze formule staat in cell M2 en kan naar beneden worden gesleept, waarbij in M2 J2 t/m J5 wordt opgeteld en in M3 J6 t/m J9.

Je gebruikt als referentie de allereerste cel van de kolom waar je waarden wilt sommeren. Het tweede argument zorgt ervoor dat elke keer als de formule een regel lager komt, Excel een verschuiving in de data kolom maakt van 4 rijen naar beneden. Het derde argument blijft op 0 staan omdat je niet naar een andere kolom wilt (je blijft immers in kolom "J"). Het vierde argument en vijfde argument zorgt ervoor dat vanaf de (referentie+offset)-cell een nieuwe range als uitvoer van je "OFFSET"-functie komt van 4 rijen bij 1 kolom.

Hopelijk kom je hier weer wat verder mee?

Voornamelijk het 2e argument van de OFFSET functie is belangrijk, aangezien je dus eerst Excel moet laten berekenen in welke cel je bezig bent. Als hij dat weet, moeten er 2 rijen vanaf gehaald worden, omdat je begint in de 2e rij van een worksheet. Daarna vermenigvuldig je de uitkomst van die formule met 4. Voor cel M2 heb je dus als uitgerekende waarde "4 x 0" staan, aangezien in die cell de uitvoer van "ROW()" "2" is. Voor M3 is het "4 x 1" etc.

[F_J_K]

offtopic:
Ik hoop dat me niet kwalijk wordt genomen wat je nog niet snap

Nog een keer mijn voorbeeld van F_J_K in "[Excel 2008] Hoe 3x3 sommatie 'slepen'? *"

Om het je makkelijker te maken pak ik alleen de eerste kolom. A3 =SOM(VERSCHUIVING($B$3;3*(RIJ()-3);0;3;1))
A3 t/m B11 is dan

6	1
15	2
24	3
	4
	5
	6
	7
	8
	9

offtopic:
Waar A4 en A5 natuurlijk omlaag gekopieerde formules zijn.

Als je in een andere rij begint, moet je simpelweg de offset veranderen (de -3) en als het aantal op te tellen rijen anders is, is het alleen de vermenigvuldiging en de hoogte aanpassen (de *3 resp. 3). Ergo: de som van een verschuiving die, afhankelijk van de rij waar de formule zelf staat, 0,4,8,etc rijen onder J2 begint en 4 rijen hoog/diep is.

Anyway; je doet de offset precies verkeerd om. Vul zoals gezegd gewoon eens met de hand in. Op fysiek papier (of in je hoofd).

Je zegt de eerste formule in J2 te hebben staan. In welke rij staat J2? 2. Dus '=SOM(VERSCHUIVING($J$2;4*(RIJ()+1);0;4;1))' is de som van J(2+4*(2+1)) t/m J(2+4*(2+1)+3). Lijkt me niet de bedoeling. Pas je offset aan, zodanig dat je begint bij 4*0 ipv. 4*3. Ofewel, minus de rij waar de eerste formule staat.

Edit: zie hierboven dus