Jak korzystać z wyszukiwania celu w programie Excel

Wyszukiwanie wyniku to jedno z narzędzi analizy warunkowej programu Excel, które pomaga znaleźć prawidłową wartość wejściową formuły w celu uzyskania pożądanego wyniku. Pokazuje, jak jedna wartość w formule wpływa na inną. Innymi słowy, jeśli masz wartość docelową, którą chcesz osiągnąć, możesz użyć poszukiwania celu, aby znaleźć odpowiednią wartość wejściową do jej uzyskania.

Załóżmy na przykład, że uzyskałeś w sumie 75 punktów z danego przedmiotu i potrzebujesz co najmniej 90, aby uzyskać ocenę S z tego przedmiotu. Na szczęście masz jeszcze jeden test, który może pomóc Ci podnieść średni wynik. Możesz użyć Szukaj celu, aby dowiedzieć się, ile punktów potrzebujesz na tym końcowym teście, aby uzyskać ocenę S.

Szukanie celu wykorzystuje metodę prób i błędów, aby prześledzić problem, wprowadzając domysły, aż do uzyskania właściwego wyniku. Wyszukiwanie celu może znaleźć najlepszą wartość wejściową dla formuły w ciągu kilku sekund, których ręczne ustalenie zajęłoby dużo czasu. W tym artykule na kilku przykładach pokażemy, jak korzystać z narzędzia Goal Seek w programie Excel.

Składniki funkcji poszukiwania celu

Funkcja szukania celu składa się z trzech parametrów, a mianowicie:

  • Ustaw komórkę – To jest komórka, w której wprowadzana jest formuła. Określa komórkę, w której chcesz uzyskać żądany wynik.
  • Cenić – Jest to wartość docelowa / pożądana, którą chcesz uzyskać w wyniku operacji poszukiwania celu.
  • Zmieniając komórkę – Określa komórkę, której wartość należy dostosować, aby uzyskać żądany wynik.

Jak korzystać z wyszukiwania celu w programie Excel: Przykład 1

Aby zademonstrować, jak to działa na prostym przykładzie, wyobraź sobie, że prowadzisz stragan z owocami. Na poniższym zrzucie ekranu komórka B11 (poniżej) pokazuje, ile kosztowało Cię kupienie owoców na stragan, a komórka B12 pokazuje całkowity dochód ze sprzedaży tych owoców. A komórka B13 pokazuje procent twojego zysku (20%).

Jeśli chcesz zwiększyć swój zysk do „30%”, ale nie jesteś w stanie zwiększyć swojej inwestycji, musisz zwiększyć swoje przychody, aby osiągnąć zysk. Ale na ile? Poszukiwanie celu pomoże ci go znaleźć.

Używasz następującej formuły w komórce B13, aby obliczyć procent zysku:

=(B12-B11)/B12

Teraz chcesz obliczyć marżę zysku, aby procent zysku wynosił 30%. Możesz to zrobić za pomocą funkcji Szukaj celu w programie Excel.

Pierwszą rzeczą do zrobienia jest wybranie komórki, której wartość chcesz dostosować. Za każdym razem, gdy używasz wyszukiwania wyniku, musisz zaznaczyć komórkę zawierającą formułę lub funkcję. W naszym przypadku wybierzemy komórkę B13, ponieważ zawiera ona formułę do obliczenia procentu.

Następnie przejdź do zakładki „Dane”, kliknij przycisk „Co jeśli Analiza” w grupie Prognozy i wybierz „Szukanie celu”.

Pojawi się okno dialogowe Szukaj celu z 3 polami:

  • Ustaw komórkę – Wprowadź odwołanie do komórki zawierające formułę (B13). To jest komórka, która będzie miała żądany wynik.
  • Cenić – Wpisz żądany wynik, który chcesz osiągnąć (30%).
  • Zmieniając komórkę – Wstaw odwołanie do komórki dla wartości wejściowej, którą chcesz zmienić (B12), aby uzyskać żądany wynik. Po prostu kliknij komórkę lub ręcznie wprowadź odwołanie do komórki. Po wybraniu komórki Excel doda znak „$” przed literą kolumny i numerem wiersza, aby uczynić ją komórką bezwzględną.

Kiedy skończysz, kliknij „OK”, aby to przetestować.

Następnie pojawi się okno dialogowe „Status poszukiwania celu” i poinformuje Cię, czy znalazło rozwiązanie, jak pokazano poniżej. Jeśli znaleziono rozwiązanie, wartość wejściowa w „komórce zmiany (B12)” zostanie dostosowana do nowej wartości. Tego właśnie chcemy. W tym przykładzie analiza wykazała, że ​​aby osiągnąć cel 30% zysku, musisz osiągnąć przychód w wysokości 1635,5 USD (B12).

Kliknij „OK”, a Excel zmieni wartości komórek lub kliknij „Anuluj”, aby odrzucić rozwiązanie i przywrócić pierwotną wartość.

Wartość wejściowa (1635,5) w komórce B12 jest tym, co odkryliśmy za pomocą funkcji Goal Seek, aby osiągnąć nasz cel (30%).

O czym należy pamiętać podczas korzystania z funkcji wyszukiwania celu

  • Komórka zestawu musi zawsze zawierać formułę zależną od komórki „Przez zmianę komórki”.
  • Możesz używać wyszukiwania celu tylko dla jednej wartości wejściowej komórki naraz
  • Pole „Zmieniając komórkę” musi zawierać wartość, a nie formułę.
  • Jeśli Poszukiwanie celu nie może znaleźć prawidłowego rozwiązania, pokazuje najbliższą wartość, jaką może wytworzyć, i informuje, że komunikat „Poszukiwanie celu mogło nie znaleźć rozwiązania”.

Co zrobić, gdy wyszukiwanie celu programu Excel nie działa

Jeśli jednak masz pewność, że istnieje rozwiązanie, możesz spróbować rozwiązać ten problem na kilka sposobów.

Sprawdź parametry i wartości wyszukiwania celu

Należy sprawdzić dwie rzeczy: najpierw sprawdź, czy parametr „Ustaw komórkę” odnosi się do komórki z formułą. Po drugie, upewnij się, że komórka formuły (komórka Set) zależy bezpośrednio lub pośrednio od zmieniającej się komórki.

Dostosowywanie ustawień iteracji

W ustawieniach Excela możesz zmienić liczbę możliwych prób, jakie Excel może podjąć w celu znalezienia odpowiedniego rozwiązania, a także jego dokładności.

Aby zmienić ustawienia obliczania iteracji, kliknij „Plik” na liście zakładek. Następnie kliknij „Opcje” na dole.

W oknie Opcje programu Excel kliknij „Formuły” w lewym panelu.

W sekcji „Opcje obliczeń” zmień te ustawienia:

  • Maksymalna liczba iteracji — wskazuje liczbę możliwych rozwiązań, które Excel obliczy; im wyższa liczba, tym więcej iteracji może wykonać. Na przykład, jeśli ustawisz „Maksymalne iteracje” na 150, Excel przetestuje 150 możliwych rozwiązań.
  • Maksymalna zmiana – wskazuje dokładność wyników; mniejsza liczba zapewnia większą dokładność. Na przykład, jeśli wartość komórki wejściowej jest równa „0”, ale szukanie celu przestaje obliczać na „0,001”, zmiana tej wartości na „0,0001” powinna rozwiązać problem.

Zwiększ wartość „Maksymalne iteracje”, jeśli chcesz, aby program Excel przetestował więcej możliwych rozwiązań i zmniejsz wartość „Maksymalnej zmiany”, jeśli chcesz uzyskać dokładniejszy wynik.

Poniższy zrzut ekranu pokazuje wartość domyślną:

Brak okólników

Gdy formuła odwołuje się do własnej komórki, nazywa się to odwołaniem cyklicznym. Jeśli chcesz, aby wyszukiwanie celu programu Excel działało poprawnie, formuły nie powinny używać odwołań cyklicznych.

Przykład poszukiwania celu w Excelu 2

Załóżmy, że pożyczasz od kogoś pieniądze w wysokości „25 000 USD”. Pożyczają ci pieniądze z oprocentowaniem 7% miesięcznie na okres 20 miesięcy, co oznacza spłatę „1327 USD” miesięcznie. Ale możesz sobie pozwolić tylko na płacenie „1000 USD” miesięcznie przez 20 miesięcy z 7% odsetkiem. Poszukiwanie celu może pomóc Ci znaleźć kwotę pożyczki, która daje miesięczną płatność (EMI) w wysokości „1000 USD”.

Wprowadź trzy zmienne wejściowe, które będą potrzebne do obliczenia PMT, tj. stopa procentowa 7%, okres 20 miesięcy i kwota główna 25 000 USD.

Wprowadź następującą formułę PMT w komórce B5, która da ci kwotę EMI w wysokości 1327,97 USD.

Składnia funkcji PMT:

=PMT(stopa procentowa/12, termin, kapitał)

Formuła:

=PMT(B3/12;B4,-B2)

Wybierz komórkę B5 (komórka z formułą) i przejdź do Dane –> Analiza What If –> Szukanie celu.

Użyj tych parametrów w oknie „Szukanie celu”:

  • Ustaw komórkę – B5 (komórka zawierająca formułę obliczającą EMI)
  • Cenić – 1000 (wynik/cel, którego szukasz)
  • Zmieniając komórkę – B2 (jest to kwota kredytu, którą chcesz zmienić, aby osiągnąć wartość docelową)

Następnie naciśnij „OK”, aby zachować znalezione rozwiązanie, lub „Anuluj”, aby je odrzucić.

Analiza mówi, że maksymalna kwota, jaką możesz pożyczyć, to 18825 USD, jeśli chcesz przekroczyć swój budżet.

W ten sposób korzystasz z wyszukiwania celu w programie Excel.