Szukanie 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 masz 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 musisz uzyskać na egzaminie końcowym, aby osiągnąć ogólny średni cel procentowy wynoszący 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 podać, 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.
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, a zmiana komórki to B7 . Tak 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 to odwołanie do zakresu za pomocą obiektu RANGE.
Kod:
Sub Goal_Seek_Example1 () Range („B8”) End Sub
Teraz umieść kropkę i przejdź do opcji „Goal Seek”.
Pierwszym argumentem jest „Cel”. Musimy wprowadzić nasz końcowy cel, aby dotrzeć do ZASIĘGU 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”. 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, tj. Komórka B7.
Ok, uruchommy kod, aby zobaczyć, co należy zrobić w ostatnim temacie, aby osiągnąć ogólny średni procent 90.
Tak więc, z ostatniego przedmiotu, należy ocenić 95, aby uzyskać ogólną średnią 90.
VBA Goal Seek - przykład nr 2
Nauczyliśmy się, jak zastosować funkcję GOAL SEEK, aby znaleźć liczbę wymaganą do osiągnięcia celu. Teraz zobaczymy zaawansowane przykłady znajdowania końcowego wyniku egzaminu dla więcej niż jednego ucznia.
Poniżej znajdują się oczekiwane wyniki 5 przedmiotów po egzaminie.
Ponieważ znajdujemy cel 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 osiągnięcia 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 i 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źć wymaganą liczbę, aby osiągnąć docelową liczbę w połowie projektu lub procesu.
Rzeczy do zapamiętania
- Szukanie wyniku jest dostępne zarówno z narzędziami arkusza, jak i narzędziem VBA.
- Wynikowa komórka powinna zawsze zawierać formułę.
- Musimy wprowadzić wartość celu i zmienić odwołanie do komórki na narzędzie wyszukiwania celu.
