[Excel] Optelsom in rij meekopieren bij invoegen rij

[RustyTank]

Ik heb een sheet, met zo'n 1000 regels, met tussendoor optelsommen. Nu wil ik hier regels in toevoegen, waarbij de regels gesorteerd staan op alfabet. De som moet dan inclusief de ingevoegde regel worden. Formules met $ bieden niet de oplossing.

dus het eerste deel telt rij 1-12 op, daarna 13-29, daarna 30-89 bijvoorbeeld. Nu plak ik eronder 20 regels, die ik daarna weer sorteer. Nu moet ie optellen 1-15, 16-32, 33-96 enz.

De optelling tussen 16-32 blijft uiteraard goed, aangezien hier geen regels tussen gevoegd zijn, die van 1-15 telt nu 3-15. Ik wil dus dat ie daar gewoon 1-15 pakt.

Ik heb al gezocht in de topics hier en via google, maar ik kom er niet uit.

[G33rt]

Je wilt dus dat excel niet van je A1 een C1 maakt als je dingen toe loopt te voegen? En je hebt zeker weten $A$1 geprobeerd als beginwaarde? Voor beide stukken moeten een dollarteken; een zet de kolom vast en de ander de rij

[Verwijderd]

Is er een kenmerk dat bepaalt waarom bepaalde rijen geselecteerd worden voor een optelling (alle rijen met A, B, C, etc. )?

Als dat zo is zou je een macro kunnen maken die elk gebied selecteert en het vervolgens een vaste naam geeft. Deze naam kan dan in de optelling gebruikt worden zodat je daarin geen directe verwijzing naar cellen hebt.

Voorbeeld:
Aaa
Asdf
Aeroj
=Som(GebiedA)
Bfjas
Bsjfklajf
=Som(GebiedB)
Cojfwo
Clflasjf
Clsfl
Cjsfp
=Som(GebiedC)
.
.
.

[RustyTank]

Nee, de rijen in de kolom die moeten worden opgeteld hebben geen vaste waarde.

In kolom a staat een codering / naam, in kolom b het getal dat opgeteld moet worden.

[onkl]

Wat is de achterliggende logica van je groepsindeling.
Dus: wat is het verschil tussen regel 15 en regel 16 of tussen 32 en 33 na het invoegen?

Waar je eens mee kan spelen is Subtotals (Data-Subtotals), het zit iig. in de richting waar jij het over hebt, mits de codering in kolom A per groep hetzelfde is.

[ Voor 10% gewijzigd door onkl op 21-09-2004 10:08 ]

[RustyTank]

onkl schreef op 21 september 2004 @ 10:07:
Wat is de achterliggende logica van je groepsindeling.
Dus: wat is het verschil tussen regel 15 en regel 16 of tussen 32 en 33 na het invoegen?

Waar je eens mee kan spelen is Subtotals (Data-Subtotals), het zit iig. in de richting waar jij het over hebt, mits de codering in kolom A per groep hetzelfde is.

Ik knip en plak dus extra rijen uit een andere sheet, die daarna weer gesorteerd worden in kolom a. Kolom b moet dan dus alles in W-20 blijven optellen. De hoofcodering is W-20, met sub coderingen -100 dus w-20-100 is volledige code.

Kolom A Kolom B
W-20
W-20-100 12
W-20-110 15
W-20-910 28
W-20-920 569
W-20-930 8
W-20-Total 632
W-30
W-30-100 255
w-30-100 656
W-30-200 217
W-30-300 2
W-30-400 5
W-30-500 8
W-30-600 0
W-30-910 2
W-30-920 56
W-30-930 55
W-30-Total 1256
W-32
W-32-100 0
W-32-100 60
W-32-100 0
W-32-110 0
W-32-120 0
W-32-200 0
W-32-910 0
W-32-920 0
W-32-930 0
W-32-Total 60
W-35

[ Voor 4% gewijzigd door RustyTank op 21-09-2004 10:26 ]

[onkl]

Probeer het even handmatig, het lijkt erop dat Subtotaal je ding is. Kan je eventueel ook nog wel autoelectrische VBA dingen van maken, maar dat wordt moeilijker.

[RustyTank]

Ik geloof niet dat ik helemaal begrijp wat je bedoeld. Moet ik hier dan ook een IF funcite combineren?

[Verwijderd]

Als ik het goed begrijp wil je alle codes die beginnen met w-20 bij mekaar optellen, alle codes die beginnen met w-30 bij mekaar optellen en zo verder.

Ik denk dat je het beste een SUMIF functie kunt gebruiken.

[RustyTank]

Verwijderd schreef op 21 september 2004 @ 11:48:
Als ik het goed begrijp wil je alle codes die beginnen met w-20 bij mekaar optellen, alle codes die beginnen met w-30 bij mekaar optellen en zo verder.

Ik denk dat je het beste een SUMIF functie kunt gebruiken.

Klopt, maar ik ben hier wat mee aan het pielen geweest en ik kom er niet uit. Enige suggesties zijn zeer welkom.

[Verwijderd]

Het een beetje lastig tips geven als je je probleem niet precies aan kunt duiden, maar ik zal toch een poging wagen.

Ten eerste: Bestudeer gewoon het excel hulp-blad voor de SUMIF funtie eens goed!!

Ik denk dat jij iets van een formule in de volgende vorm moet gebruiken:

Voor W-20: =SUMIF(A:A;"W-20";B:B)
Voor W-30: =SUMIF(A:A;"W-30";B:B)
Voor W-40: =SUMIF(A:A;"W-40";B:B)
etc.
.

[RustyTank]

Verwijderd schreef op 21 september 2004 @ 13:05:
Het een beetje lastig tips geven als je je probleem niet precies aan kunt duiden, maar ik zal toch een poging wagen.

Ten eerste: Bestudeer gewoon het excel hulp-blad voor de SUMIF funtie eens goed!!

Ik denk dat jij iets van een formule in de volgende vorm moet gebruiken:

Voor W-20: =SUMIF(A:A;"W-20";B:B)
Voor W-30: =SUMIF(A:A;"W-30";B:B)
Voor W-40: =SUMIF(A:A;"W-40";B:B)
etc.
.

Ja, op deze manier had ik het ook geprobeerd, maar hier loop ik tegen het probleem aan dat ik een cross reference fout krijg aangezien de sumif functie ook in de B:B range zit. Een extra kolom toevoegen is geen optie, ik hoop dat je nog andere ideeen hebt

[onkl]

SumIF is alleen een oplossing als je bijvoorbeeld je subtotalen in een ander werkblad wilt zien. anders heb je problemen met kruisverwijzingen, sorteren etc.
Wat ik voorstelde was:
Zet bovenaan kolom A (in A1 dus) "Naam", Bovenin kolom B "waarde".
Selecteer je hele datablok.
Kies Data-Subtotals. piel wat met je instelingen, klaar.
Wil je data toevoegen:
1: selecteer alles, Subtotals-Remove All
2: Voeg data toe
3: Sorteer
4: Opnieuw Subtotals.
En je lijstje is er weer.

RustyTank schreef op 21 september 2004 @ 10:44:
Ik geloof niet dat ik helemaal begrijp wat je bedoeld. Moet ik hier dan ook een IF funcite combineren?

Subtotalen is geen functie, het is 1 van die veel te hippe exceldingen a la voorwaardelijke opmaak etc. Je hoeft in bovenstaand voorbeeld dus geen enkele functie in te voeren

[ Voor 31% gewijzigd door onkl op 21-09-2004 13:45 ]

[Verwijderd]

RustyTank schreef op 21 september 2004 @ 13:19:
Ja, op deze manier had ik het ook geprobeerd, maar hier loop ik tegen het probleem aan dat ik een cross reference fout krijg aangezien de sumif functie ook in de B:B range zit. Een extra kolom toevoegen is geen optie, ik hoop dat je nog andere ideeen hebt

Begrijp ik het goed dat je dus je subtotalen ook in kolom B hebt staan onderaan (of bovenaan??) de groep waarvover dit subtotaal berekend moet worden?

Wat staat er dan voor dit subtotaal in kolom A ? Ik neem aan dat je daar een handige waarde hebt gekozen zodat het subtotaal met sorteren automatisch in de goede rij komt te staan?

[RustyTank]

Verwijderd schreef op 21 september 2004 @ 13:52:
[...]


1Begrijp ik het goed dat je dus je subtotalen ook in kolom B hebt staan onderaan (of bovenaan??) de groep waarvover dit subtotaal berekend moet worden?

2Wat staat er dan voor dit subtotaal in kolom A ? Ik neem aan dat je daar een handige waarde hebt gekozen zodat het subtotaal met sorteren automatisch in de goede rij komt te staan?

1) Ja, het subtotaal staat onderaan de groep waarover het subtotaal berekend moet worden.

2) Ja, zoals in het voorbeeld staat komt er in kolom a door het soreteren van W-20-100, w-20-200 als laatste w-20-totaal. Op die laatste regel van de groep moet dus ook het sub totaal komen.

[onkl]

RustyTank schreef op 21 september 2004 @ 14:08:
[...]


1) Ja, het subtotaal staat onderaan de groep waarover het subtotaal berekend moet worden.

2) Ja, zoals in het voorbeeld staat komt er in kolom a door het soreteren van W-20-100, w-20-200 als laatste w-20-totaal. Op die laatste regel van de groep moet dus ook het sub totaal komen.

Sorry, foutje:
Wil je dat W-20-100 en W-20-200 in hetzelfde subtotaal komen?
->maak nog een (verborgen) kolom aan waar =LEFT(A1,4) oid. instaat, sleep naar beneden en laat de subtotalen tegen die kolom berekenen.

[RustyTank]

onkl schreef op 21 september 2004 @ 14:12:
[...]


Sorry, foutje:
Wil je dat W-20-100 en W-20-200 in hetzelfde subtotaal komen?
->maak nog een (verborgen) kolom aan waar =LEFT(A1,4) oid. instaat, sleep naar beneden en laat de subtotalen tegen die kolom berekenen.

Die kolom heb ik al, het probleem blijft alleen dat het SUMIF functie in dezelfde range staat als de getallen die opgeteld moeten worden waardoor je een cross ref fout krijgt.

[Verwijderd]

RustyTank schreef op 21 september 2004 @ 14:08:
2) Ja, zoals in het voorbeeld staat komt er in kolom a door het soreteren van W-20-100, w-20-200 als laatste w-20-totaal. Op die laatste regel van de groep moet dus ook het sub totaal komen.

Oke, ik denk dat het dan op de volgende manier wel zou moeten kunnen:

Zet in kolom B op de plek waar je het subtotaal wil gewoon een SUM fuctie en bepaal de range waarover je wil sommeren met behulp van een MATCH functie. Die MATCH functie laat je dan in Kolom A zoeken naar "w-20-totaal", "w-30-totaal" enzovoorts.

De preciese functie is nu wat lastiger en je zult zelf wel even moeten pielen om het werkend te krijgen. Mischien heb je ook nog wel de OFFSET functie nodig.

[onkl]

RustyTank, Plevuus, imho is iedere aanpak met Sumif, Sum of andere celfuncties een heilloze weg. Je krijgt hoe dan ook sorteer- en crossreference problemen als je, zoals TS aangaf wilt sorteren na toevoegen van nieuwe records.
Dit was juist de reden dat ik mijn voorkeur uitsprak voor de subtotaal functionaliteit van Excel. Excel is hiermee namelijk zelf in staat subtotalen in te voegen en te verwijderen, wat je van een enorme hoeveelheid gedoe afhelpt.
Ben benieuwd waarom TS dit niet wil gebruiken.

[Verwijderd]

Onkl, RustyTank = TS.

Ja, ik denk ook niet dat mijn laatst genoemde oplossing de meest ideale is. Ik zou zelf namelijk een kolom toevoegen en daar een SUMIF functie gebruiken, maar dat is wat de TS niet wil. Ik probeer de TS zoveel naar zijn wensen te helpen en ik denk dat het mogelijk is wat wat hij wil.

[RustyTank]

onkl schreef op 21 september 2004 @ 15:03:
RustyTank, Plevuus, imho is iedere aanpak met Sumif, Sum of andere celfuncties een heilloze weg. Je krijgt hoe dan ook sorteer- en crossreference problemen als je, zoals TS aangaf wilt sorteren na toevoegen van nieuwe records.
Dit was juist de reden dat ik mijn voorkeur uitsprak voor de subtotaal functionaliteit van Excel. Excel is hiermee namelijk zelf in staat subtotalen in te voegen en te verwijderen, wat je van een enorme hoeveelheid gedoe afhelpt.
Ben benieuwd waarom TS dit niet wil gebruiken.

Ik ga hier nog even verder induiken

[RustyTank]

Ik ben even iets verder in de subtotalen gedoken, dit werkt idd vrij aardig voor wat ik wil! Eerst plakken, dan sorteren en dan opnieuw subtotal aanzetten.

Ik wil het alleen nu nog iets moeilijker maken! Ik heb naast w-10 w-20 ect hetzelfde voor een p-10 p-20 etc etc in dezelfde kolom.
Kan ik naast subtotal voor de w-10 serie ook nog een subtotal krijgen voor w-** en P-** ???

[ Voor 4% gewijzigd door RustyTank op 21-09-2004 16:39 ]

[onkl]

RustyTank schreef op 21 september 2004 @ 16:34:
Ik wil het alleen nu nog iets moeilijker maken! Ik heb naast w-10 w-20 ect hetzelfde voor een p-10 p-20 etc etc in dezelfde kolom.
Kan ik naast subtotal voor de w-10 serie ook nog een subtotal krijgen voor w-** en P-** ???

Ben bang dat dat niet gaat lukken, iig niet via de subtotals manier.

VBA kan het wel, weet alleen niet of jij VBA kan en hoe belangrijjk het is.
(je moet dan wel het hele idee achter het blad omgooien)