VBA-Zielsuche

Zielsuche in Excel VBA

Zielsuche ist das in Excel VBA verfügbare Tool, mit dem wir die erforderliche Anzahl finden können, um das festgelegte Ziel zu erreichen.

Sie sind beispielsweise Student und haben eine durchschnittliche Punktzahl von 90% aus sechs verfügbaren Fächern angestrebt. Bis jetzt haben Sie 5 Prüfungen abgeschlossen und es bleibt nur ein Fach übrig. Ihre erwarteten Ergebnisse aus fünf abgeschlossenen Fächern sind 89, 88, 91, 87, 89 und 90. Jetzt möchten Sie wissen, wie viel Sie benötigen, um Punkte zu erzielen die Abschlussprüfung, um das durchschnittliche Gesamtziel von 90% zu erreichen.

Dies kann durch Verwendung von GOAL SEEK im Excel-Arbeitsblatt sowie in der VBA-Codierung erfolgen. Mal sehen, wie es mit VBA funktioniert.

VBA-Zielsuchsyntax

In VBA Goal Seek müssen wir den Wert angeben, den wir ändern, und zum endgültigen Zielergebnis gelangen. Geben Sie daher die Zellreferenz mithilfe des VBA RANGE-Objekts an. Später können wir auf die Option GOAL SEEK zugreifen.

Unten finden Sie die Syntax der Zielsuche in VBA.

  • Range (): In diesem Fall müssen wir die Zellreferenz dort angeben, wo wir den Zielwert erreichen möchten.
  • Ziel: In diesem Argument müssen wir das Ziel eingeben, das wir erreichen wollen.
  • Ändern der Zelle: In diesem Argument müssen wir liefern, indem wir ändern, welchen Zellenwert wir benötigen, um das Ziel zu erreichen.

Beispiele für Excel VBA Goal Seek

Im Folgenden finden Sie Beispiele für die Zielsuche in Excel VBA.

Sie können diese VBA-Zielsuch-Excel-Vorlage hier herunterladen - VBA-Zielsuch-Excel-Vorlage

VBA-Zielsuche - Beispiel 1

Nehmen wir nur das Beispiel der durchschnittlichen Prüfungspunktzahl. Nachfolgend finden Sie die erwartete Punktzahl von 5 Probanden aus der abgeschlossenen Prüfung.

Zuerst müssen wir die durchschnittliche Punktzahl der abgeschlossenen 5 Fächer ermitteln. Wenden Sie die DURCHSCHNITTLICHE Funktion in der B8-Zelle an.

In diesem Beispiel ist unser Ziel 90, das Ändern der Zelle ist B7 . Goal Seek hilft uns also, die Zielpunktzahl aus dem letzten Thema zu finden, um den Gesamtdurchschnitt von 90 zu erreichen.

Starten Sie die Unterprozedur im VBA-Klassenmodul.

Code:

 Sub Goal_Seek_Example1 () End Sub

Jetzt benötigen wir das Ergebnis in der B8-Zelle. Geben Sie diese Bereichsreferenz mithilfe des RANGE-Objekts an.

Code:

 Sub Goal_Seek_Example1 () Range ("B8") End Sub

Setzen Sie nun einen Punkt und geben Sie die Option "Zielsuche" ein.

Das erste Argument ist "Ziel". Dazu müssen wir unser Endziel eingeben, um in BEREICH B8 zu gelangen. In diesem Beispiel versuchen wir, das Ziel von 90 zu erreichen.

Code:

 Sub Goal_Seek_Example1 () Range ("B8"). GoalSeek Goal: = 90 End Sub

Das nächste Argument ist "Ändern der Zelle". Dazu müssen wir angeben, in welcher Zelle wir den neuen Wert benötigen, um das Ziel zu erreichen .

Code:

 Sub Goal_Seek_Example1 () Range ("B8"). GoalSeek Goal: = 90, ChangingCell: = Range ("B7") End Sub

In diesem Beispiel ist unsere sich ändernde Zelle eine Sub 6-Zelle, dh eine B7-Zelle.

Ok, lassen Sie uns den Code ausführen, um zu sehen, was im letzten Thema getan werden muss, um den durchschnittlichen Gesamtprozentsatz von 90 zu erreichen.

Im letzten Fach muss also 95 Punkte erzielt werden, um den Gesamtdurchschnitt von 90 zu erreichen.

VBA-Zielsuche - Beispiel 2

Wir haben gelernt, wie Sie GOAL SEEK anwenden, um die zur Erreichung des Ziels erforderliche Anzahl zu ermitteln. Jetzt sehen wir ein fortgeschrittenes Beispiel für das Finden der endgültigen Prüfungsergebnisse für mehr als einen Schüler.

Nachfolgend finden Sie die erwarteten Ergebnisse von 5 Probanden nach der Prüfung.

Da wir das Ziel für mehr als einen Schüler finden, müssen wir Schleifen verwenden. Nachfolgend finden Sie den Code für Sie.

Code:

 Sub Goal_Seek_Example2 () Dim k As Long Dim ResultCell As Range Dim ChangingCell As Range Dim TargetScore As Integer TargetScore = 90 Für k = 2 bis 5 Set ResultCell = Cells (8, k) Set ChangingCell = Cells (7, k) ResultCell.GoalSeek TargetScore, ChangingCell Next k End Sub

Dieser Code durchläuft alle Punktzahlen der Schüler und erreicht die endgültige Prüfungspunktzahl, die erforderlich ist, um den Gesamtdurchschnitt von 90 zu erreichen.

Also haben wir das Endergebnis jetzt als,

Schüler A muss nur 83 Punkte erzielen, um die 90 Prozent zu erreichen, und Schüler D muss 93 Punkte erzielen.

Aber schauen Sie sich Student B & C an, sie müssen in der Abschlussprüfung jeweils 104 Punkte erzielen, was überhaupt nicht möglich ist.

Auf diese Weise können wir mithilfe der GOAL SEEK-Analyse die erforderliche Anzahl finden, um die angestrebte Anzahl in der Mitte des Projekts oder Prozesses zu erreichen.

Dinge, an die man sich erinnern sollte

  • Die Zielsuche ist sowohl mit dem Arbeitsblatt-Tool als auch mit dem VBA-Tool verfügbar.
  • Die Ergebniszelle sollte immer eine Formel enthalten.
  • Wir müssen den Zielwert eingeben und die Zellreferenz auf das Zielsuchwerkzeug ändern.