Szukanie wyniku, część zestawu narzędzi do analizy typu „co by było, gdyby” programu Excel, to zaawansowana funkcja używana do obliczeń wstecznych. Zasadniczo, jeśli znasz pożądany wynik formuły, ale nie masz pewności, jaka wartość wejściowa spowoduje ten wynik, możesz użyć funkcji Szukaj wyniku, aby go znaleźć. Niezależnie od tego, czy jesteś analitykiem finansowym, studentem czy właścicielem firmy, zrozumienie sposobu korzystania z funkcji Goal Seek może znacząco usprawnić proces rozwiązywania problemów. W tym przewodniku zbadamy, czym jest funkcja Goal Seek, jak uzyskać do niej dostęp i zademonstrujemy jej praktyczne zastosowania na różnych przykładach, począwszy od dostosowywania planów spłaty kredytu po obliczanie minimalnych wyników egzaminów i nie tylko.

• Przykład 1: Korekta planu spłaty kredytu
• Przykład 2: Ustalenie minimalnej liczby punktów za zdanie egzaminu
• Przykład 3: Obliczanie liczby głosów wymaganych do zwycięstwa w wyborach
• Przykład 4: Osiągnięcie docelowego zysku w firmie

Co to jest szukanie celu w programie Excel?

Szukaj celu to integralna funkcja programu Microsoft Excel, część pakietu narzędzi do analizy typu „co by było, gdyby”. Umożliwia użytkownikom wykonanie odwrotnych obliczeń – zamiast obliczać wynik danych wejściowych, określasz pożądany wynik, a Excel dostosowuje wartość wejściową, aby osiągnąć ten cel. Ta funkcja jest szczególnie przydatna w scenariuszach, w których trzeba znaleźć wartość wejściową, która doprowadzi do określonego wyniku.

• Regulacja z jedną zmienną:
  Funkcja Goal Seek może zmieniać tylko jedną wartość komórki wejściowej na raz.
• Wymaga formuły:
  Podczas stosowania funkcji Szukaj celu komórka docelowa musi zawierać formułę.
• Zachowaj integralność formuły:
  Wyszukiwanie celu nie zmienia formuły w komórce docelowej; zmienia wartość w komórce wejściowej, od której zależy formuła.

Dostęp do wyszukiwania celów w programie Excel

Aby uzyskać dostęp do funkcji Szukaj celu, przejdź do Dane tab, kliknij Co jeśli analiza > Szukaj celu. Zobacz zrzut ekranu:

A później Szukaj celu wyskakuje okno dialogowe. Oto wyjaśnienie trzech opcji w oknie dialogowym Szukanie celu w programie Excel:

• Ustaw komórkę: komórka docelowa zawierająca formułę, którą chcesz osiągnąć. Ta komórka musi zawierać formułę, a funkcja Goal Seek dostosuje wartości w innej powiązanej komórce, aby wartość tej komórki osiągnęła ustawiony cel.
• Cenić: Wartość docelowa, którą chcesz „Ustaw komórkę" osiągnąć.
• Zmieniając komórkę: Komórka wejściowa, którą chcesz dostosować w funkcji Szukanie celu. Wartość w tej komórce zostanie zmieniona, aby zapewnić wartość w polu „Ustaw komórkę" spotyka "Cenić" cel.
  
  

W następnej sekcji na podstawie szczegółowych przykładów omówimy, jak korzystać z funkcji wyszukiwania celu w programie Excel.

Korzystanie z funkcji szukania celu z przykładami

W tej sekcji omówimy cztery typowe przykłady demonstrujące użyteczność funkcji Szukanie celu. Przykłady te będą dotyczyć zarówno dostosowań finansów osobistych, jak i strategicznych decyzji biznesowych, oferując wgląd w to, jak można zastosować to narzędzie w różnych rzeczywistych scenariuszach. Każdy przykład ma na celu lepsze zrozumienie, jak skutecznie używać funkcji Szukanie celu do rozwiązywania różnych typów problemów.

Przykład 1: Korekta planu spłaty kredytu

Scenariusz: Osoba planuje zaciągnąć pożyczkę w wysokości 20,000 5 USD i zamierza ją spłacić w ciągu 5 lat przy rocznej stopie procentowej wynoszącej 377.42%. Wymagana miesięczna spłata wynosi 500 USD. Później zdaje sobie sprawę, że może zwiększyć miesięczną spłatę o XNUMX dolarów. Biorąc pod uwagę, że całkowita kwota kredytu i roczna stopa procentowa pozostają takie same, ile lat zajmie spłata pożyczki?

Jak pokazano w poniższej tabeli:

• B1 zawiera kwotę pożyczki 20000 dolarów.
• B2 zawiera okres kredytowania 5 (lat).
• B3 zawiera roczną stopę procentową w wysokości 5%.
• B5 zawiera miesięczną opłatę obliczaną według wzoru =PMT(B3/12,B2*12,B1).
  
  

Tutaj pokażę Ci, jak korzystać z funkcji Szukaj celu, aby wykonać to zadanie.

Krok 1: Aktywuj funkcję szukania celu

Idź do Dane tab, kliknij Co jeśli analiza > Szukaj celu.

Krok 2: Skonfiguruj ustawienia wyszukiwania celu

1. W Ustaw komórkę wybierz formułę B5.
2. W Cenić wpisz planowaną miesięczną kwotę spłaty -500 (liczba ujemna oznacza płatność).
3. W Zmieniając komórkę wybierz komórkę B2, którą chcesz dostosować.
4. Kliknij OK.
   
   

Wynik

Połączenia Stan poszukiwania celu pojawi się okno dialogowe informujące, że znaleziono rozwiązanie. Jednocześnie wartość w komórce B2, którą podałeś w polu „Przez zmianę komórki” zostanie zastąpiona nową wartością, a komórka z formułą otrzyma wartość docelową na podstawie zmiany zmiennej. Kliknij OK zastosować zmiany

Teraz spłata kredytu zajmie mu około 3.7 roku.

Przykład 2: Ustalenie minimalnej liczby punktów za zdanie egzaminu

Scenariusz: Student ma obowiązek przystąpić do 5 egzaminów, każdy o jednakowej wadze. Aby zdać egzamin, student musi osiągnąć średni wynik 70% ze wszystkich egzaminów. Po ukończeniu pierwszych 4 egzaminów i znaniu ich wyników, uczeń musi teraz obliczyć minimalny wynik wymagany na piątym egzaminie, aby mieć pewność, że ogólna średnia osiągnie lub przekroczy 70%.

Jak pokazano na zrzucie ekranu poniżej:

• B1 zawiera punktację z pierwszego egzaminu.
• B2 zawiera ocenę z drugiego egzaminu.
• B3 zawiera punktację z trzeciego egzaminu.
• B4 zawiera ocenę z czwartego egzaminu.
• B5 będzie zawierać ocenę z piątego egzaminu.
• B7 to średni wynik pozytywny obliczany według wzoru =(B1+B2+B3+B4+B5)/5.
  
  

Aby osiągnąć ten cel, możesz zastosować funkcję Szukaj celu w następujący sposób:

Krok 1: Aktywuj funkcję szukania celu

Idź do Dane tab, kliknij Co jeśli analiza > Szukaj celu.

Krok 2: Skonfiguruj ustawienia wyszukiwania celu

1. W Ustaw komórkę wybierz komórkę z formułą B7.
2. W Cenić wpisz średni wynik pozytywny 70%.
3. W Zmieniając komórkę wybierz komórkę (B5), którą chcesz dostosować.
4. Kliknij OK.
   
   

Wynik

Połączenia Stan poszukiwania celu pojawi się okno dialogowe informujące, że znaleziono rozwiązanie. Jednocześnie komórka B7 określona w polu „Zmieniając komórkę” zostanie automatycznie wprowadzona z pasującą wartością, a komórka z formułą otrzyma wartość docelową na podstawie zmiany zmiennej. Kliknij OK zaakceptować rozwiązanie.

Aby więc osiągnąć wymaganą średnią ogólną i zdać wszystkie egzaminy, student musi uzyskać na ostatnim egzaminie co najmniej 71%.

Przykład 3: Obliczanie głosów wymaganych do zwycięstwa w wyborach

Scenariusz: Podczas wyborów kandydat musi obliczyć minimalną liczbę głosów wymaganą do zapewnienia większości. Jaka jest minimalna liczba głosów, jaką musi uzyskać kandydat, aby uzyskać więcej niż 50% i wygrać, przy znanej łącznej liczbie oddanych głosów?

Jak pokazano na zrzucie ekranu poniżej:

• Komórka B2 zawiera całkowitą liczbę głosów oddanych w wyborach.
• Komórka B3 pokazuje aktualną liczbę głosów otrzymanych przez kandydata.
• Komórka B4 zawiera pożądaną większość (%) głosów, którą kandydat chce uzyskać, obliczaną według wzoru =B2/B1.
  
  

Aby osiągnąć ten cel, możesz zastosować funkcję Szukaj celu w następujący sposób:

Krok 1: Aktywuj funkcję szukania celu

Idź do Dane tab, kliknij Co jeśli analiza > Szukaj celu.

Krok 2: Skonfiguruj ustawienia wyszukiwania celu

1. W Ustaw komórkę wybierz komórkę z formułą B4.
2. W Cenić wpisz żądany procent głosów większości. W tym przypadku, aby wygrać wybory, kandydat musi uzyskać ponad połowę (czyli ponad 50%) ogółu ważnych głosów, więc wpisuję 51%.
3. W Zmieniając komórkę wybierz komórkę (B2), którą chcesz dostosować.
4. Kliknij OK.
   
   

Wynik

Połączenia Stan poszukiwania celu pojawi się okno dialogowe informujące, że znaleziono rozwiązanie. Jednocześnie komórka B2 określona w polu „Zmieniając komórkę” zostanie automatycznie zastąpiona nową wartością, a komórka z formułą otrzyma wartość docelową na podstawie zmiany zmiennej. Kliknij OK zastosować zmiany.

Aby wygrać wybory, kandydat potrzebuje więc co najmniej 5100 głosów.

Przykład 4: Osiąganie docelowego zysku w firmie

Scenariusz: Firma dąży do osiągnięcia docelowego zysku w wysokości 150,000 XNUMX USD w bieżącym roku. Biorąc pod uwagę zarówno koszty stałe, jak i zmienne, muszą określić niezbędny przychód ze sprzedaży. Ile jednostek sprzedaży potrzeba, aby osiągnąć ten cel zysku?

Jak pokazano na zrzucie ekranu poniżej:

• B1 zawiera koszty stałe.
• B2 zawiera koszty zmienne na jednostkę.
• B3 zawiera cenę jednostkową.
• B4 zawiera sprzedane jednostki.
• B6 to przychód całkowity, który oblicza się ze wzoru =B3*B4.
• B7 to koszt całkowity, który oblicza się ze wzoru =B1+(B2*B4).
• B9 to bieżący zysk ze sprzedaży, który oblicza się ze wzoru =B6-B7.
  
  

Aby uzyskać łączną liczbę jednostek sprzedaży w oparciu o docelowy zysk, możesz zastosować funkcję Szukaj celu w następujący sposób.

Krok 1: Aktywuj funkcję szukania celu

Idź do Dane tab, kliknij Co jeśli analiza > Szukaj celu.

Krok 2: Skonfiguruj ustawienia wyszukiwania celu

1. W Ustaw komórkę wybierz komórkę z formułą, która zwraca zysk. Oto komórka B9.
2. W Cenić wpisz docelowy zysk 150000.
3. W Zmieniając komórkę wybierz komórkę (B4), którą chcesz dostosować.
4. Kliknij OK.
   
   

Wynik

Pojawi się okno dialogowe Status poszukiwania celu, pokazujące, że znaleziono rozwiązanie. Jednocześnie komórka B4 określona w polu „Zmieniając komórkę” zostanie automatycznie zastąpiona nową wartością, a komórka z formułą otrzyma wartość docelową na podstawie zmiany zmiennej. Kliknij OK zaakceptować zmiany.

W efekcie firma musi sprzedać co najmniej 6666 egzemplarzy, aby osiągnąć docelowy zysk w wysokości 150,000 XNUMX dolarów.

Typowe problemy i rozwiązania dotyczące wyszukiwania celów

W tej sekcji wymieniono niektóre typowe problemy i odpowiadające im rozwiązania dotyczące funkcji Goal Seek.

1. W poszukiwaniu celu nie można znaleźć rozwiązania

• Powód: Dzieje się tak zazwyczaj, gdy wartości docelowej nie można osiągnąć przy użyciu żadnej możliwej wartości wejściowej lub jeśli początkowe założenia są zbyt odległe od potencjalnego rozwiązania.
• Rozwiązanie: Dostosuj wartość docelową, aby upewnić się, że mieści się ona w możliwym zakresie. Wypróbuj także różne początkowe domysły, bliżej oczekiwanego rozwiązania. Upewnij się, że formuła w „Ustaw komórkę” jest poprawna i może logicznie wygenerować żądaną wartość „Do wartości”.

2. Niska prędkość obliczeń

• Powód: Wyszukiwanie celu może być powolne w przypadku dużych zbiorów danych, złożonych formuł lub gdy początkowe założenia są dalekie od rozwiązania i wymagają większej liczby iteracji.
• Rozwiązanie: Uprość formuły, jeśli to możliwe, i zmniejsz rozmiar danych. Upewnij się, że początkowe założenie jest jak najbardziej zbliżone do oczekiwanego rozwiązania, aby zmniejszyć liczbę potrzebnych iteracji.

3. Problemy z dokładnością

• Powód: Podczas korzystania z funkcji Szukanie celu możesz zauważyć, że czasami Excel powie, że znalazł rozwiązanie, ale nie jest ono dokładnie tym, czego oczekiwałeś — blisko, ale nie wystarczająco blisko. Funkcja wyszukiwania wyniku może nie zawsze zapewniać bardzo dokładne wyniki ze względu na precyzję obliczeń i zaokrąglenia programu Excel.
  
  Na przykład, aby obliczyć konkretne potęgowanie liczby i użyć funkcji Szukaj wyniku, aby znaleźć podstawę, która pozwala uzyskać pożądany wynik. Tutaj zmienię wartość wejściową w komórce A1 (podstawa), tak aby komórka A3 (wynik potęgowania) odpowiadała docelowemu wynikowi wynoszącemu 25.
  
  Jak widać na zrzucie ekranu poniżej, funkcja Goal Seek podaje przybliżoną wartość, a nie dokładny pierwiastek.
  
• Rozwiązanie: Zwiększ liczbę miejsc dziesiętnych pokazywanych w opcjach obliczeń programu Excel, aby uzyskać większą dokładność, a następnie ponownie uruchom funkcję Szukaj celu. Wykonaj następujące czynności.
  
  1. Kliknij filet > Opcje otworzyć Opcje programu Excel okno.
  2. Wybierz Wzory w lewym okienku i w Opcje obliczeniowe sekcji, zwiększ liczbę miejsc po przecinku w Maksymalna zmiana skrzynka. Tutaj zmieniam 0.001 do 0.000000001 i kliknij OK.
     
  3. Uruchom ponownie funkcję Goal Seek, a otrzymasz dokładny katalog główny, jak pokazano na zrzucie ekranu poniżej.
     

4. Ograniczenia i metody alternatywne

• Powód: Funkcja wyszukiwania wyniku może dostosować tylko jedną wartość wejściową i może nie być odpowiednia w przypadku równań wymagających jednoczesnego dostosowania wielu zmiennych.
• Rozwiązanie: W scenariuszach wymagających dostosowania wielu zmiennych użyj Solvera Excela, który jest potężniejszy i pozwala na ustawienie ograniczeń.

Goal Seek to potężne narzędzie do wykonywania odwrotnych obliczeń, oszczędzające żmudnego zadania ręcznego testowania różnych wartości w celu przybliżenia celu. Wyposażoni w wnioski z tego artykułu, możesz teraz z pewnością zastosować funkcję Goal Seek, wynosząc analizę danych i wydajność na nowy poziom. Dla tych, którzy chcą głębiej zagłębić się w możliwości Excela, nasza strona internetowa oferuje mnóstwo samouczków. Tutaj znajdziesz więcej porad i wskazówek dotyczących Excela.