Herbers Excel-Forum - das Archiv

Bewerten Sie hier bitte das Excel-Portal

Zielwertsuche mit VBA

Bild

Betrifft: Zielwertsuche mit VBA
von: WalterK
Geschrieben am: 30.09.2003 22:23:17
Hallo miteinander!

Gibt es eine Möglichkeit, die Zielwertsuche automatisch nach der Eingabe
in einer Zelle einer bestimmten Spalte starten und berechnen zu lassen und dann das Ergebnis in der gleichen Zeile aber einer anderen Spalte einzutragen.

Beispiel:

Tabelle1
  
 ABCDEF
1 Abzug SVergibt20,00% Gesuchter
2Bruttoin %Abzug SVAbzug Lst.NettoNetto
3      
42.272,46 17,50%-397,68 -374,96 1.500,00 1.500,00 
52.000,00 18,00%-360,00 -328,00 1.312,18  
6      
7Die Berechnung der "veränderbaren Zelle" soll immer dann erfolgen,     
8wenn im Bereich G9:G13 eine Zahl eingegeben wird.     
9      
10Zielwertsuche     
11Zielzelle immer Spalte E, gleiche Zeile   
12Zielwert Betrag aus Spalte G   
13Veränderbare Zelle immer Spalte A, gleiche Zeile   
Formeln der Tabelle
C4 : =$A4*$B4*-1
D4 : =($A4+$C4)*D$1*-1
E4 : =SUMME(A4:D4)
C5 : =$A5*$B5*-1
D5 : =($A5+$C5)*D$1*-1
E5 : =SUMME(A5:D5)
Excel Tabellen einfach im Web darstellen   Excel Jeanie HTML  2.0    Download   Besten Dank im voraus. Servus, Walter
Bild

Betrifft: AW: Zielwertsuche mit VBA
von: heinz
Geschrieben am: 30.09.2003 23:25:06
was ist zielwertsuche?
Bild

Betrifft: was ist zielwertsuche, AW
von: axel.meyer
Geschrieben am: 01.10.2003 00:01:39
hallo heinz,

wenn du eine funktion hast, die nicht nach der variablen lösbar ist, für die du gerne ein ergebniswert hättest, dann kannst du diesen wert einfach so lange in der bestehenden gleichung variieren, bis die gleichung erfüllt ist.

bsp:

y = x·(0,5 - 3/x)^x (^für exponent)

diese gleichung kannst du nicht nach x auflösen. wenn du aber für y einen wert (zB 5) vorgegeben hast und jetzt wissen möchtest, wie groß das zugehörige x ist (zT meherer lösungen möglich!), dann kannst du das über die zielwertsuche machen. das findest du unter "extras-zielwertsuche". funktioniert so: du gibst eine zelle an, die den zielwert enthält, gibst die zelle an, die wirklich geändert werden soll (hier wird iteriert) und diejenige zelle, in der die formel steht, und die sich eben dadurch ändert, daß die "änder-dich-zelle" variiert wird.

probiers einfach mal.

gruß, axel
Bild

Betrifft: AW: was ist zielwertsuche, AW
von: heinz
Geschrieben am: 01.10.2003 12:43:55
sehr interessant - danke
Bild

Betrifft: ...gerne. (OT)
von: axel.meyer
Geschrieben am: 01.10.2003 20:54:31
Bild

Betrifft: AW: Zielwertsuche mit VBA
von: axel.meyer
Geschrieben am: 30.09.2003 23:53:00
hi walter,

trage das nachfolgende makro in das modul der tabelle ein, in der die ZW-suche laufen soll und passe die zellen nach deinen wünschen an. daß die zeile mit der zielwertsuche doppelt vorkommt ist absicht, da es nichts schadet, sie aber zT nicht anläuft, wenn sie nur einfach drin steht.

gruß, axel
ps:
nur falls du es noch nciht wissen solltest: das modul deiner tabelle findest du so:
Alt + F11 drücken, jetzt bist du in der entwicklungsumgebung, wo man makro-codes schreiben kann. hier müßte links oben ein kleines explorer-fenster sein (wenn nicht: Strg + r). hier sind unter VBA-Projekt->Mickeysoft Excel Objekte->Tabelle... auch irgendwo deine tabelle dabei. doppelklick hierauf, rechts erscheint ein großes weißes fenster, hier kannst du den unten stehenden code mit copy-past eintragen.

Private Sub worksheet_change(ByVal target As Excel.Range)
'zelle wg der automatisch gestartet wird
If target.Address = "$L$2" Then
'zielwertsuche
Range("L6").GoalSeek Goal:=Range("L2").Value, ChangingCell:=Range("M2")
Range("L6").GoalSeek Goal:=Range("L2").Value, ChangingCell:=Range("M2")
End If
End Sub

Bild

Betrifft: AW: Zielwertsuche mit VBA
von: WalterK
Geschrieben am: 01.10.2003 07:08:37
Hallo Axel!

Vielen Dank für Deine Mühe, es funktioniert ausgezeichnet. Danke auch für die ausführliche Hilfeanleitung.

Servus, Walter
Bild

Betrifft: AW: Zielwertsuche mit VBA
von: WalterK
Geschrieben am: 01.10.2003 07:23:05
Hallo!

Noch eine Zusatzfrage!
Den Code von Axel habe ich auf die Zeile 12 angepasst und funktioniert auch bestens. Was muss ich tun, damit der Code von Zeile 12 bis Zeile 25 läuft.

Besten Dank nochmals.
Servus, Walter
Bild

Betrifft: AW: Zielwertsuche mit VBA
von: axel.meyer
Geschrieben am: 01.10.2003 08:12:27
morgen walter,

man kann innerhalb der range-anweisung auf diese weise variabel arbeiten:

for i = 12 to 25
Range("L" & i)....
next i

wobei du für den teil "& i)" auch ganze formeln einsetzen kannst, solange das ergebnis eine integer-zahl ist, zB "& (i + 2)*3". auf diese weise kannst du mit einer einzigen schleife "12 to 15" fast immer alles abdecken.

solltest du mal die spalte variieren müssen, kannst du mit dieser syntax arbeiten:

Range(Cells(3,2),Cells(3,2)). das würde Range("B3") entsprechen, hat aber den vorteil, daß beide zahlen durch laufvariablen ersetzbar sind. dabei gilt: "zeile spalte", d.h. die erste zahl entspricht der zeile, die zweite der spalte.

gruß, axel
Bild

Betrifft: AW: Zielwertsuche mit VBA
von: WalterK
Geschrieben am: 01.10.2003 16:41:26
Hallo Axel!

Kann mich leider erst jetzt melden.
Nochmals vielen Dank für die Beantwortung meiner Fragen, versuche jetzt, Deine Infomationen in die Tat umzusetzen.

Servus, Walter
Bild

Betrifft: AW: Zielwertsuche mit VBA
von: WalterK
Geschrieben am: 01.10.2003 16:41:41
Hallo Axel!

Kann mich leider erst jetzt melden.
Nochmals vielen Dank für die Beantwortung meiner Fragen, versuche jetzt, Deine Infomationen in die Tat umzusetzen.

Servus, Walter
 Bild
Excel-Beispiele zum Thema " Zielwertsuche mit VBA"
SVERWEIS, WVERWEIS, INDEX, VERGLEICH und Zielwertsuche Beispiel für Zielwertsuche
Zielwertsuche über VBA