[Excel] formule voor kolom in 1 keer aanpassen

[MBV]

Hallo,

Ik heb een rij gegevens in de bekende excel-opstelling: titeltje boven elke kolom, en eronder een hele bak cijfers. Stel dat dat kolom B-G is. Ik wil nu in kolom H een berekening uitvoeren op basis van de gegevens in B-G. Ik ken de methode van 'naar beneden sleuren', maar dit keer wil ik het makkelijk aanpasbaar houden. Ik wil dus in cel I1 een formule neerzetten, en die formule overal laten gebruiken. Weet iemand een manier om dat te doen?
nu:

code:

    A    B    C    D     E    F
1   a    b    c    d     e    som
2   1    5    2    6    10    =SUM(A1:E1)
3   4    2    4    1     3    =SUM(A2:E2)
...

straks

code:

    A    B    C    D     E    F        G
1   a    b    c    d     e    som     =SUM(A*:E*)
2   1    5    2    6    10    =FORMULE_VAN(G1)
3   4    2    4    1     3    =FORMULE_VAN(G1)
...

_{overgesimplificeerd voorbeeld, maar het geeft aan wat ik bedoel hoop ik}.

[ Voor 5% gewijzigd door MBV op 31-05-2005 14:06 ]

[JorisS]

• In regel 1 je formule aanmaken
• Vervolgens alle cellen selecteren waarin je de formule wilt kopieren.
• Via de menubalk Plakken speciaal / Paste Special kiezen (Edit/Bewerken)

Evt. Kun je met $ bepaalde cellen in je formule vastzetten. Dus $A$1, als je steeds naar A1 wilt verwijzen, $A1 als je de kolom A wilt vasthouden en A$1 als je dat met de rij wilt doen.

[MBV]

Dat ken ik al. Daar heb ik inmiddels heel veel hele smerige trucs mee uitgehaald om het gewenste resultaat te krijgen. Ik wil een formule 'on the fly' kunnen wijzigen, dus zonder alle 30636 regels naar beneden te scrollen. (niet overdreven, zijn complexity metrics per functie van een project van 1.5 mln regels code)
Wat dat betreft helpt dat paste special dus wel, maar stel dat je even bent vergeten dat je autofilter aan staat.. Ik wil het dus het liefst in 1 keer veranderen.

[Lustucru]

Met puur werkbladformules gaat het niet lukken, met drie regels VBA wel...
In het worksheet_change event controleer je af de cel met de 'moederformule' is gewijzigd en dan vervolgens een copy actie.
iets als

Visual Basic:

If target.row= 1 and target.column=3 then
      target.copy range("C2:C999")
end if

[ Voor 15% gewijzigd door Lustucru op 31-05-2005 14:13 ]

[MBV]

Jammer. Ik genereer nu namelijk een .csv file m.b.v. perl en daar kan je wel formules ingooien maar niet dit soort dingen Moet ik toch een lege worksheet maken en importeren ofzo.

Iig bedankt voor de reactie. Ik had het angstige vermoeden al.

_{en hoelang duurt een copy/paste op 30000 regels?}

[dailyleaf]

Anders een macro erover gooien, 1 keer voordoen en de macro doet de rest.

[MBV]

Een macro is verkapt VBscript. Dan maak ik het liever zelf (ondanks dat het vbscript is...)

[Lustucru]

MBV schreef op dinsdag 31 mei 2005 @ 14:15:
Jammer. Ik genereer nu namelijk een .csv file m.b.v. perl en daar kan je wel formules ingooien maar niet dit soort dingen Moet ik toch een lege worksheet maken en importeren ofzo.

Iig bedankt voor de reactie. Ik had het angstige vermoeden al.

Lekker moeilijk gaan doen Maak een add-in die een nieuw werkblad aanmaakt, de vba toekent en dan je csv binnenhaalt

_{en hoelang duurt een copy/paste op 30000 regels?}

<0,1 sec?

offtopic:
macro=vba !=vbscript

[ Voor 4% gewijzigd door Lustucru op 31-05-2005 14:31 ]

[MBV]

Ik maak denk ik wel 5 rijen bovenaan waarin de parameters staan voor een standaard formule. Parameters kan je makkelijk aanpassen, wil je een andere formule? Dan maar copy/pasten

En wat jij voorstelt zou ik zo doen, ware het niet dat ik dan echt te weinig tijd overhoud voor mijn scriptie

[ Voor 24% gewijzigd door MBV op 31-05-2005 14:49 ]

[Dido]

Ben je bekend met de kracht van shift+end, down?
maw, je staat in cel F1, ctrl+C, down, shift(ingedrukt houden), end, down, shift los, ctrl+V.

Benodigde tijd: < 0,1 seconde.

[MBV]

Yep. Ik had er alleen niet aan gedacht dat je 1 cel in 1 keer naar de hele zooi kan kopieeren
Gelukkig realiseerde ik me dat toen iemand over plakken speciaal begon . Wel raar eigenlijk, dat je niet een cel als 'programmeertaal'-functie kan gebruiken. Maar ja, zodra dingen in Excel op een programma beginnen te lijken kan je voor de onderhoudbaarheid vaak beter een programma schrijven

offtopic:
Zit nu met een formule van 3 regels te rommelen, leuk die haakjes

[Anoniem: 113297]

Dido schreef op dinsdag 31 mei 2005 @ 15:14:
Ben je bekend met de kracht van shift+end, down?
maw, je staat in cel F1, ctrl+C, down, shift(ingedrukt houden), end, down, shift los, ctrl+V.

Benodigde tijd: < 0,1 seconde.

of het volgende:formule inbrengen, muispijltje op rechteronderhoek cel plaatsen tot de pointer in een kruis verandert. dubbelklik:klaar.

[MBV]

Handig al die functies van excel. Hoe zou het toch komen dat niemand ze kent? Het is daarbij erg vervelend als je perongeluk dubbelklikt, lijkt me

[ Voor 5% gewijzigd door MBV op 01-06-2005 10:28 ]

[MBV]

En nu loop ik tegen het volgende probleem aan. Excel wordt met massale acties erg traag. Ik combineer in een kolom verschillende gegevens uit de andere rijen, waarvan 2 kolommen ook berekend zijn (verschil in dagen met vandaag bijv). Parameters veranderen bovenin (die dus ook voor alle 30000 rijen moet worden uitgevoerd) is zo gedaan, maar als ik ga sorteren moet hij ineens alles opnieuw uitrekenen, waardoor hij zo'n 2 minuten niet reageert . Ook een frequentietabel op de berekende kolom geeft een vergelijkbare wachttijd.

Weet iemand een truc om te zorgen dat bepaalde berekeningen niet steeds opnieuw worden uitgevoerd?

[Anoniem: 113297]

vrij basic toch:extra->opties->berekenen->handmatig.

[MBV]

Anoniem: 113297 schreef op woensdag 08 juni 2005 @ 13:57:
vrij basic toch:extra->opties->berekenen->handmatig.

Ik zou graag een paar kolommen vastzetten. Is dat ook basic?

als dat zo is trek ik me terug naar de devschuur

[ Voor 30% gewijzigd door MBV op 08-06-2005 14:19 . Reden: en dan is er ineens wat weg :/ ]

[superduper]

MBV schreef op woensdag 08 juni 2005 @ 14:16:
[...]

Ik zou graag een paar kolommen vastzetten. Is dat ook basic?

als dat zo is trek ik me terug naar de devschuur

Wat bedoel je met vastzetten? Toch niet de $ voor de kolom aanduiding, want anders moet je zeker naar de devschuur

[Anoniem: 113297]

ik heb de devschuur al gewaarschuwd dat er zodadelijk iemand met een beginners-all-purpose-symbolic-instruction-code-vraagje komt aanzetten
kolommen (&rijen) kan je vastzetten via het excelmenu venster->splitsen en het kruis dat dan verschijnt verslepen (een soort christusopdracht), daarna kan je via hetzelfde menu titels blokkeren.

[MBV]

Dat bedoel ik ook niet. Zo noob ben ik ook niet Ik bedoel dat hij bepaalde kolommen niet eindeloos gaat doorrekenen met sorteren

_heretic_: misschien moet je eens mijn post-history doornemen. vooral van iets langer terug, aangezien ik de laatste tijd weinig tijd heb dankzij een stageopdracht.

[Anoniem: 113297]

ik wou alleen te kennen geven dat ik je grapje begrepen had, het antwoord op je vraag dat ik daaropvolgend geef, kan inderdaad wel een andere interpretatie mogelijk maken van wat ik daarboven geschreven had, maar ik pleit onschuldig

dus als ik het nu goed begrepen heb, wil je dat enkel bepaalde kolommen niet herberekend worden als je sorteert, ipv een volledig werkblad. dit kan je via een omweg bereiken door de berekening zoals eerder aangegeven op handmatig te zetten, en dan enkel de kolommen te herberekenen die je nodig hebt dmv een vba statement (niet getest):

Visual Basic:

sub herberekenkolomc
  Worksheets(1).columns(3).Calculate
end sub

als je goed overweg kan met events kan je in een recente versie een event koppelen zodat dit automatisch gebeurt, in dat geval gedraagt het werkblad zich precies zo als je wenst.

[Anoniem: 146708]

Zelf gebruik ik Excel uit Office 2003 en daar doe ik het als volgt.
Ik maar een formule in rij één en als i goed is ga ik naar de linker onderhoek van de cel.
De cursor veranderd in een zwart kruisje. Als je nu dubbelklikt wordt de formule langs alle rijen gekopieerd.

Let op excel stopt als hij een lege cel/rij links van de te kopieeren formule (of andere waarde) tegen komt.

Dit werkt overigens niet als je de autofilter aan hebt staan.

[ Voor 55% gewijzigd door Anoniem: 146708 op 12-06-2005 12:52 ]