Szukajcie celu w VBA

Poszukiwanie celu w VBA to proces, dzięki któremu możemy osiągnąć cel, który chcemy osiągnąć. Jest to proces, który pokazuje, jak wiele musimy zrobić, aby osiągnąć wyznaczony cel. Załóżmy, że pociąg przyjeżdża codziennie o 10 rano. Pociąg kursuje od poniedziałku do piątku między A i B. A biorąc pod uwagę średni czas przyjazdu tego pociągu z A do B, to dokładnie 10 rano bez żadnych opóźnień. Jeśli więc podano następujące godziny przyjazdu pociągu 10:16, 10:01, 9:57, 10:04 na kolejne 4 dni od poniedziałku do czwartku. Aby utrzymać średni czas 10 rano, który jest docelowym celem dla tego pociągu, musi on przybyć o godzinie 9.45, aby utrzymać średni czas przyjazdu o 10 rano. To może być jeszcze fajniejsze, jeśli mamy coś do automatyzacji. Nadchodzi VBA Goal Seek .

Składnia szukania celu:

Poniżej znajduje się składnia VBA Goal Seek.

Gdzie,

  • Zakres: komórka zakresu docelowego, która zawiera bieżący limit.
  • Cel : Limit lub cel, który chcemy osiągnąć.
  • Zmiana komórki: komórka, w której zobaczymy wymagany limit do osiągnięcia.

Jak korzystać z funkcji szukania celu w programie Excel VBA?

Dowiemy się, jak korzystać z funkcji Szukaj celu w programie Excel za pomocą kodu VBA.

Możesz pobrać ten szablon Excel Vek Goal Seek Excel tutaj - Szablon Excel Vek Goal Seek Excel

Poszukiwanie celu VBA - przykład nr 1

Średnia dokładność, którą pracownicy powinni utrzymywać w 95% tygodniowo. Poniżej mamy dane dotyczące dokładności pracownika od poniedziałku do czwartku ze średnią dokładnością 92, 25%, co jest wymienione w komórce C8, jak pokazano poniżej.

Teraz, zgodnie ze składnią Poszukiwania celu, mamy komórkę Zakres, która ma bieżącą wartość do karmienia i Wartość docelową, która jest średnią dokładnością 90%, która ma zostać osiągnięta przez tego pracownika.

W tym przykładzie, w którym dokładność pracy pracownika jest monitorowana. W tym celu wykonaj następujące kroki:

Krok 1 : Otwórz moduł z karty menu Wstaw, jak pokazano poniżej.

Krok 2: Teraz napisz procedurę podrzędną VBA Goal Seek, jak pokazano poniżej. Możemy wybrać dowolną nazwę, aby to zdefiniować.

Kod:

 Sub Goal_Seek () End Sub

Krok 3: Zgodnie ze składnią wybierzemy komórkę Range, która zawiera bieżącą średnią do podania. Tutaj ta komórka to C8.

Kod:

 Sub Range_Seek () Range („C8”). Napis końcowy

Krok 4: Teraz wybierz funkcję Szukanie celu i zgodnie ze składnią wybierz wartość docelową, którą musimy osiągnąć. Tutaj cel ten jest dokładny w 90%.

Kod:

 Sub Goal_Seek () Zakres („C8”). GoalSeek Cel: = 90 End Sub

Krok 5: Teraz wreszcie wybierz zakres tej komórki, w którym musimy zobaczyć wymaganą dokładność do osiągnięcia. Tutaj ta komórka to C6 z odwołaniem do komórki w piątek.

Kod:

 Sub Goal_Seek () Range („C8”). GoalSeek Cel: = 90, ChangingCell: = Range („C6”) End Sub

Krok 6: Po zakończeniu skompilujemy kod i uruchomimy go. Zobaczymy, że komórka C6 ma wartość Changing Cell jako 81. Oznacza to, że jeśli pracownikowi udało się osiągnąć nawet 81% dokładności w piątek, to z łatwością będzie w stanie osiągnąć dokładność 90%.

Poszukiwanie celu VBA - przykład 2

W tym przykładzie mamy dane dotyczące czasu zawracania (TAT lub czas do zakończenia działania) pewnego pracownika zespołu, który wykonuje to samo zadanie każdego dnia. 5 pracowników wykonuje tę samą czynność od poniedziałku do piątku. Docelowy czas zakończenia tej aktywności wynosi 50 minut. Musimy także dowiedzieć się, ile czasu potrzebuje każdy pracownik, aby utrzymać średni czas ukończenia wynoszący 50 minut lub mniej. W tym celu wykonaj następujące kroki:

Krok 1: W module napisz podprocedurę dla szukania celu VBA, jak pokazano poniżej.

Kod:

 Sub Goal_Seek2 () End Sub

Krok 2: Zdefiniuj zmienną, według której możemy liczyć od miejsca, w którym musimy rozpocząć szukanie celu jako długie.

Kod:

 Sub Goal_Seek2 () Dim A As Long End Sub

Krok 3: Teraz ponownie zdefiniuj 2 dodatkowe zmienne za pomocą DIM o typie danych Range . Pierwszy będzie użyty do śledzenia bieżącej średniej końcowej, a drugi zostanie użyty do połączenia komórki referencyjnej, w której chcemy uzyskać wynik.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim Final Final Av As As Range Dim Reference As Range End Sub

Krok 4: Zdefiniuj inną zmienną jako liczbę całkowitą, aby ustawić cel, który chcemy osiągnąć.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim Final Final Av As As Dim Dim Reference As Range Dim Target As Integer End Sub

Krok 5: Tutaj naszym celem jest 50 minut, więc ustaw zmienną docelową na tę wartość.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim Final Final Av As As Range Dim Reference As Range Dim Integer Target = 50 End Sub

Krok 6: Otwórz pętlę For-Next, jak pokazano poniżej.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim Final Final Av As As Dim Dim Reference As Range Dim Integer Target = 50 For Next A End Sub

Krok 7: Teraz wybierz zakres tych komórek, który ma wartości. Tutaj te wiersze mają od 3 do 7 liczb.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim Final Avg As Range Dim Reference As Range Dim Integer Target = 50 For A = 3 to 7 Next A End Sub

Krok 8: Ustaw końcową średnią, skąd chcemy się dostać. Tutaj ta komórka jest w rzędzie 9.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim FinalAvg As Range Dim Reference As Range Dim Integer Target = 50 For A = 3 do 7 Set FinalAvg = Cells (9, A) Next A End Sub

Krok 9: Podobnie ustaw zakres komórek odniesienia, w którym chcemy zobaczyć wartość szukania celu, aby osiągnąć cel, a wiersz komórki to 7.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim FinalAvg As Range Dim Reference As Range Dim Integer Target = 50 For A = 3 do 7 Set FinalAvg = Cells (9, A) Set Reference = Cells (7, A) Next A End Pod

Krok 10: W końcu zastosuj VBA Goal Seek z odpowiednią składnią, jak pokazano poniżej.

Kod:

 Sub Goal_Seek2 () Dim A As Long Dim FinalAvg As Range Dim Reference As Range Dim Integer Target = 50 For A = 3 do 7 Set FinalAvg = Cells (9, A) Set Reference = Cells (7, A) FinalAvg.GoalSeek Cel, odniesienie Następny A End Sub

Krok 11: Teraz skompiluj cały kod, naciskając klawisz funkcyjny F8, a następnie uruchom go, jeśli nie zostanie znaleziony błąd.

Przekonamy się, w wierszu 7 przy pomocy Goal Seek uzyskamy czas w minutach, który każdy pracownik musi osiągnąć w piątek, aby utrzymać średnio 50 minut lub mniej.

Tak działa VBA Goal Seek.

Plusy Excel VBA Poszukiwanie celu

  • Wyszukiwanie celów VBA jest bardzo pomocne w śledzeniu wydajności na różne sposoby.
  • Można to również wykorzystać w wielu pracach statystycznych, w których musimy śledzić i ukierunkowywać limit danych, który chcemy osiągnąć.
  • To określa dokładną wartość, którą osoba musi osiągnąć, aby osiągnąć cel.

Rzeczy do zapamiętania

  • Można to wykorzystać zarówno w Excelu, jak i VBA.
  • Komórka wyniku ze średnią lub inną funkcją musi zawsze mieć formułę.
  • Możemy wprowadzić wartość docelową w polu Cel w składni Szukanie celu, albo możemy to wcześniej zdefiniować osobno.
  • Możemy zmienić i zaktualizować wartość docelową zgodnie z naszymi potrzebami i wymaganiami.
  • Tego zadania nie można wykonać, rejestrując opcję makr dostępną na karcie programisty.
  • Po zakończeniu pamiętaj o zapisaniu pliku programu Excel w formacie aktywacji makr, aby zachować zapisany kod.

Polecany artykuł

To jest przewodnik po VBA Goal Seek. Tutaj omawiamy, jak korzystać z funkcji Szukaj celu w Excelu VBA wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -

  1. Kolekcja VBA (przykłady)
  2. Instrukcje VBA IF | Szablony programu Excel
  3. Jak korzystać z funkcji sortowania Excel VBA?
  4. VBA While Loop (przykłady z szablonem Excel)
  5. Funkcja środowiska VBA

Kategoria:

VBA