Szukanie celu w VBA | Jak korzystać z funkcji Goal Seek, aby znaleźć wartość w Excel VBA?

Szukaj celu w Excel VBA

Goal Seek to narzędzie dostępne w excel VBA, które pomaga nam znaleźć wymaganą liczbę, którą należy osiągnąć, aby osiągnąć wyznaczony cel.

Na przykład, jesteś studentem i chcesz uzyskać średni wynik 90% z sześciu dostępnych przedmiotów. W tej chwili zdałeś 5 egzaminów i został ci tylko jeden przedmiot. Twoje przewidywane wyniki z pięciu ukończonych przedmiotów to 89, 88, 91, 87, 89 i 90. Teraz chcesz wiedzieć, ile potrzebujesz punktów z końcowe badanie mające na celu osiągnięcie ogólnego średniego celu procentowego wynoszącego 90%.

Można to zrobić za pomocą funkcji GOAL SEEK w arkuszu programu Excel, a także w kodowaniu VBA. Zobaczmy, jak to działa z VBA.

Składnia wyszukiwania celów w języku VBA

W VBA Goal Seek musimy określić wartość, którą zmieniamy i dojść do ostatecznego docelowego wyniku, więc podaj odwołanie do komórki za pomocą obiektu VBA RANGE, później możemy uzyskać dostęp do opcji GOAL SEEK.

Poniżej znajduje się składnia wyszukiwania celów w języku VBA.

Zakres (): w tym przypadku musimy podać odwołanie do komórki, w której musimy osiągnąć docelową wartość.
Cel: w tym argumencie musimy wpisać, jaki jest cel, który próbujemy osiągnąć.
Zmiana komórki: w tym argumencie musimy podać, zmieniając wartość komórki potrzebną do osiągnięcia celu.

Przykłady wyszukiwania celów w programie Excel VBA

Poniżej znajdują się przykłady wyszukiwania celów w Excel VBA.

Możesz pobrać ten szablon VBA Goal Seek Excel tutaj - VBA Goal Seek Excel Template

VBA Goal Seek - przykład nr 1

Weźmy na przykład tylko średni wynik egzaminu. Poniżej znajduje się oczekiwana ocena 5 przedmiotów z zaliczonego egzaminu.

Najpierw musimy obliczyć, jaki jest średni wynik z ukończonych 5 przedmiotów. Zastosuj funkcję ŚREDNIA w komórce B8.

W tym przykładzie naszym celem jest 90, zmiana komórki to B7 . Więc Goal Seek pomoże nam znaleźć docelowy wynik z ostatniego przedmiotu, aby osiągnąć ogólną średnią 90.

Uruchom podprocedurę w module klasy VBA.

Kod:

 Sub Goal_Seek_Example1 () End Sub

Teraz potrzebujemy wyniku w komórce B8, więc podaj odwołanie do zakresu za pomocą obiektu RANGE.

Kod:

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

Teraz umieść kropkę i wybierz opcję „Goal Seek”.

Pierwszym argumentem jest „Cel”, dlatego musimy wprowadzić nasz końcowy cel, aby dotrzeć do ZAKRESU B8. W tym przykładzie staramy się osiągnąć cel 90.

Kod:

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

Następnym argumentem jest „Zmiana komórki”, dlatego musimy podać, w której komórce potrzebujemy nowej wartości, aby osiągnąć cel.

Kod:

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

W tym przykładzie nasza zmieniająca się komórka to komórka Sub 6, czyli komórka B7.

Ok, uruchommy kod, aby zobaczyć, co należy zrobić w ostatnim temacie, aby uzyskać ogólny średni procent 90.

Tak więc, w ostatnim przedmiocie musi zostać punktowane 95, aby uzyskać ogólną średnią 90.

VBA Goal Seek - przykład nr 2

Dowiedzieliśmy się, jak zastosować funkcję GOAL SEEK, aby znaleźć liczbę wymaganą do osiągnięcia celu. Teraz zobaczymy zaawansowany przykład znajdowania końcowego wyniku egzaminu dla więcej niż jednego ucznia.

Poniżej znajdują się oczekiwane wyniki 5 przedmiotów po egzaminie.

Ponieważ szukamy celu dla więcej niż jednego ucznia, musimy użyć pętli, poniżej znajduje się kod dla Ciebie.

Kod:

 Sub Goal_Seek_Example2 () Dim k As Long Dim Result Cell As Range Dim ChangingCell As Range Dim TargetScore As Integer TargetScore = 90 For k = 2 to 5 Set ResultCell = Cells (8, k) Set ChangingCell = Cells (7, k) ResultCell.GoalSeek TargetScore, ChangingCell Next k End Sub

Ten kod przejdzie przez wszystkie wyniki uczniów i poda ostateczny wynik egzaminu wymagany do uzyskania ogólnej średniej 90.

Więc otrzymaliśmy teraz wynik końcowy,

Student A musi uzyskać tylko 83 punkty, aby zapewnić ogólne 90 procent, a Student D musi uzyskać 93.

Ale spójrz na studenta B & C, muszą uzyskać po 104 punkty na końcowym egzaminie, co nie jest w ogóle możliwe.

W ten sposób, korzystając z analizy GOAL SEEK, możemy znaleźć liczbę wymaganą do osiągnięcia docelowej liczby w połowie projektu lub procesu.

Rzeczy do zapamiętania

Wyszukiwarka celów jest dostępna zarówno z narzędziem arkusza, jak i narzędziem VBA.
Komórka wynikowa powinna zawsze zawierać formułę.
Musimy wprowadzić wartość celu i zmienić odwołanie do komórki do narzędzia wyszukiwania celu.