Funkcja szukania wyniku w programie Excel: kompletny przewodnik i przykłady

Ostatnia aktualizacja: 02/12/2025
Autor: Isaac
  • Goal Seek to narzędzie do analizy typu „co by było, gdyby”, które ustala pożądany wynik i oblicza wartość pojedynczej komórki potrzebną do jego osiągnięcia.
  • Świetnie sprawdza się w rozwiązywaniu problemów finansowych, akademickich i biznesowych, gdzie istnieje jasna formuła i tylko jedna niewiadoma.
  • Należy zawsze skonfigurować komórkę docelową za pomocą formuły, żądanej wartości i komórki, która zmienia się w zależności od tej formuły.
  • W przypadku scenariuszy obejmujących wiele zmiennych i ograniczeń zaleca się użycie narzędzia Solver, które rozszerza możliwości analizy i optymalizacji.

Funkcja szukania celu w programie Excel

Funkcja szukania wyniku w Excelu to jedno z tych narzędzi, które pozostają niezauważone, dopóki się ich nie odkryje, a potem trudno sobie wyobrazić, jak można było bez nich żyć. Pozwala ona powiedzieć Excelowi: „To jest to, co chcę osiągnąć”, a program sam oblicza wartość, jaką powinna mieć konkretna komórka, aby obliczenia zadziałały.

Zamiast gorączkowo próbować różnych liczb, aż znajdziesz właściwą, możesz skorzystać z funkcji Goal Seek, aby pozwolić Excelowi wykonać ciężką pracę . Jest ona niezwykle przydatna w finansach (pożyczki, inwestycje, budżety osobiste ), nauce (oceny, średnie ocen, stypendia) i wszelkich obliczeniach, w których utworzyłeś formułę, a brakuje Ci zmiennej.

Czym właściwie jest funkcja Szukanie wyniku w programie Excel?

Funkcja szukania wyniku jest częścią narzędzi analizy warunkowej programu Excel . Zamiast pytać się: „Co się stanie z wynikiem, jeśli zmienię te dane?”, odwracasz podejście: wskazujesz programowi Excel, jaki wynik chcesz uzyskać i prosisz go o obliczenie, jaką wartość powinna przyjąć jedna z komórek objętych formułą.

Innymi słowy, narzędzie działa „odwrotnie” niż normalnie : znasz już potrzebną wartość końcową i pozwalasz Excelowi znaleźć wartość wejściową, która sprawi, że formuła zwróci ten wynik. Z matematycznego punktu widzenia przypomina to rozwiązywanie równania z jedną niewiadomą bez konieczności ręcznego jej wyodrębniania.

Funkcja ta jest dostępna w programie Excel, a także w innych pakietach biurowych, takich jak ONLYOFFICE Docs , gdzie została wbudowana w arkusze kalkulacyjne, aby oferować ten sam rodzaj analizy scenariuszy. We wszystkich przypadkach filozofia jest taka sama: wyznacz cel, wybierz zmienną i pozwól programowi iterować, aż znajdzie odpowiednią wartość.

Typowe sytuacje, w których narzędzie Goal Seek okazuje się bardzo przydatne, to m.in. obliczanie spłat pożyczek , planowanie oszczędności emerytalnych , projektowanie budżetów osobistych i firmowych oraz analiza sprzedaży niezbędna do osiągnięcia określonego zysku lub progu rentowności.

Przykłady narzędzia do szukania celu

Kluczowe koncepcje przed użyciem funkcji Goal Seek

Zanim zaczniesz korzystać z narzędzia, ważne jest, aby dokładnie określić trzy rzeczy: komórkę docelową, pożądaną wartość oraz komórkę, która ma zostać zmieniona . Jeśli poprawnie zidentyfikujesz te elementy, reszta będzie prosta.

Komórka docelowa to ta, w której znajduje się wynik formuły, której wartość chcesz przyjąć. Na przykład może to być średnia ocen końcowych, miesięczna rata kredytu lub całkowity zysk z produktu. Ważne: ta komórka musi zawierać formułę, a nie odręcznie wpisaną liczbę.

Wartość pożądana to liczba, którą komórka docelowa ma osiągnąć. Może to być minimalna średnia wymagana do utrzymania stypendium, maksymalna miesięczna wpłata, na jaką pozwala Twój budżet, lub zysk, jaki wyznaczyłeś jako cel dla swojej firmy. Wartość tę ustalasz na podstawie swojego celu.

Komórka , która się zmienia, to niewiadoma w problemie – dane, które Excel ma obliczyć. Musi to być komórka będąca częścią formuły w komórce docelowej. Może to być saldo zadłużenia, kwota pożyczki, liczba lat spłaty, cena sprzedaży, liczba sprzedanych jednostek itp.

Istotne jest, aby komórka docelowa była zależna, bezpośrednio lub pośrednio, od komórki modyfikowanej . Jeśli w formule nie ma między nimi relacji, funkcja Goal Seek nie znajdzie żadnych wyników. Zasadniczo narzędzie to zastępuje klasyczną metodę prób i błędów zautomatyzowanym procesem iteracji.

Jak działa Goal Seek krok po kroku: przykład pożyczki

Korzystanie z Goal Seek w przypadku pożyczek

Klasycznym przykładem zrozumienia metody Goal Seek jest pożyczka z regularnymi spłatami . Wyobraź sobie, że wiesz, ile pieniędzy chcesz pożyczyć, na ile miesięcy chcesz ją spłacać i jaką maksymalną ratę możesz sobie pozwolić, ale nie wiesz, jakie oprocentowanie jest potrzebne, aby te kwoty się zgadzały.

Do tego typu obliczeń używasz funkcji PMT programu Excel , która zwraca kwotę do zapłaty w każdym okresie. Kluczem jest to, że znasz już wysokość miesięcznej raty, więc użyj funkcji Goal Seek, aby program Excel dostosowywał stopę procentową, aż osiągniesz tę kwotę.

  Jak odzyskać uszkodzony dokument Word: kompletny i praktyczny przewodnik

1. Przygotuj arkusz kalkulacyjny pożyczki

Po pierwsze, warto uporządkować dane w małej tabeli , aby zachować porządek. Możesz utworzyć coś takiego w nowym arkuszu:

  • W formacie A1: „Kwota pożyczki”. W B1:kapitał, o który chcesz wnioskować, na przykład 100000.
  • W formacie A2: „Termin w miesiącach”. W B2: liczba miesięcy, na przykład 180 (co odpowiadałoby 15 latom).
  • W formacie A3: „Stopa procentowa”. W B3Na razie zostaw to pole puste, bo będzie nieznane.
  • W formacie A4: „Płatność”. W B4:tutaj mamy wzór na PŁATNOŚĆ.

Teraz w komórce B4 wprowadź formułę =PMT(B3/12;B2;B1) . Dzielisz B3 przez 12, ponieważ stopa procentowa jest zazwyczaj wyrażana w skali roku, a ratę obliczasz miesięcznie. Ponieważ komórka B3 jest pusta, Excel założy 0% odsetek i obliczy miesięczną ratę wyłącznie na podstawie okresu i kwoty.

W tym konkretnym przykładzie możesz zobaczyć, że obliczone szanse wynoszą około 555,56 , ale ta wartość nie jest jeszcze dla Ciebie istotna. Ważne jest, aby mieć teraz gotową strukturę formuły do ​​użycia funkcji Goal Seek.

2. Uruchom funkcję Goal Seek, aby znaleźć stopę procentową

Po wprowadzeniu formuły możesz teraz otworzyć narzędzie analizy. Przejdź do karty Dane i w grupie Polecenia prognozowania lub Narzędzia danych wybierz Analiza warunkowa > Szukaj wyniku . Otworzy się okno dialogowe z trzema polami.

W polu „Ustaw komórkę” określ komórkę zawierającą formułę, której wynik chcesz poprawić, w tym przypadku B4. Jest to komórka docelowa, w której znajduje się obliczenie miesięcznej płatności za pomocą funkcji PMT.

W polu „Wartość za” wpisz kwotę, którą chcesz faktycznie zapłacić. Jeśli planujesz płacić 900 miesięcznie, wpisz tutaj -900 . Znak minus jest ważny, ponieważ w Excelu płatności dokonywane z własnej kieszeni są traktowane jako kwoty ujemne.

W polu „Zmiana komórki” wprowadź odwołanie do komórki, którą Excel powinien dostosować, aby uzyskać tę płatność. W tym przykładzie będzie to komórka B3, gdzie zostanie wprowadzona stopa procentowa. Po kliknięciu przycisku OK funkcja szukania wyniku rozpocznie iterację, zmieniając stopę procentową, aż znajdzie taką, która pozostawi miesięczną płatność na poziomie -900.

Po zakończeniu narzędzie wyświetli okno stanu z wynikiem . Obliczona stopa procentowa będzie widoczna w komórce B3, a nowa płatność w komórce B4. Excel zazwyczaj znajduje dość dokładne rozwiązanie, jeśli formuła jest poprawna, a problem ma sens liczbowy.

Na koniec możesz sformatować komórkę B3 jako wartość procentową z karty Narzędzia główne, wybierając format procentowy i dostosowując liczbę miejsc dziesiętnych za pomocą ikon zwiększania lub zmniejszania miejsc dziesiętnych. Dzięki temu stopa procentowa będzie wyświetlana w bardziej czytelnej formie.

Inne przykłady wykorzystania: weksle, inwestycje i kredyty

Skuteczność metody Goal Seek nie ogranicza się do pożyczek. Można ją zastosować w wielu codziennych sytuacjach, gdy masz cel liczbowy i brakuje Ci zmiennej , aby go osiągnąć. Przyjrzyjmy się kilku typowym praktycznym przykładom.

1. Wiedza o tym, jaką ocenę musisz uzyskać na ostatnim egzaminie

Jednym z najbardziej intuicyjnych przykładów są oceny . Wyobraź sobie, że masz już kilka ocen i został Ci tylko jeden egzamin. Znasz minimalną średnią ocen, która pozwoli Ci zdać kurs lub utrzymać stypendium, ale nie wiesz, jaka ocena będzie potrzebna na tym ostatnim egzaminie.

Załóżmy, że potrzebujesz średniej 70, aby móc przystąpić do egzaminu końcowego lub kontynuować pobieranie stypendium. Zdałeś już trzy egzaminy, został Ci jeszcze jeden i chcesz, aby Excel podpowiedział Ci, jaką ocenę musisz uzyskać na czwartym egzaminie , aby Twoja średnia końcowa wyniosła co najmniej 70.

Najpierw wpisz znane oceny w kolumnie, na przykład B2:B4. Pozostaw komórkę B5 pustą, ponieważ będzie to ocena z nadchodzącego egzaminu, a w innej komórce, powiedzmy B7, wpisz formułę obliczania średniej: =ŚREDNIA(B2:B5) . To będzie komórka docelowa.

Otwórz narzędzie Szukanie wyniku z menu Dane > Analiza warunkowa > Szukanie wyniku i skonfiguruj je w następujący sposób :

  • Ustaw komórkę:B7 (komórka zawierająca formułę obliczającą średnią).
  • Wartość dla:70 (średnia, którą chcesz osiągnąć).
  • Zmiana komórki:B5 (ocena z egzaminu).
  Rozwiązywanie typowych problemów w telewizorach Smart TV z systemem Android

Po potwierdzeniu, Excel obliczy wartość, jaką musi mieć komórka B5, aby średnia w komórce B7 wyniosła 70. Na przykład, może to wskazywać, że do osiągnięcia wymaganej średniej potrzebna jest ocena 85 na ostatnim egzaminie. Tę samą koncepcję można zastosować do średnich ważonych, wykorzystując funkcje takie jak SUMA.ILOCZYNÓW i SUMA , jeśli każdy test ma inną wagę.

2. Średnie ważone i oceny o różnych wagach

Na wielu kursach nie wszystkie oceny są oceniane jednakowo. Na przykład zadania mogą być warte 30%, testy 20%, a egzamin końcowy 50%. W takich przypadkach zamiast prostej średniej zazwyczaj stosuje się wzór średniej ważonej.

Typowym sposobem konfiguracji jest umieszczenie ocen w jednej kolumnie, a wag (dziesiętnych lub procentowych) w drugiej. Aby uzyskać ocenę końcową, można użyć kombinacji SUMA(oceny;wagi)/SUMA(wagi) .

Komórka z formułą średniej ważonej będzie Twoją komórką docelową. Następnie, tak jak poprzednio, użyj funkcji Szukaj wyniku, aby Excel dostosowywał ocenę z konkretnego egzaminu, aż ogólna ocena osiągnie, powiedzmy, 50% lub wartość wymaganą do zdania.

Stosując to podejście, możesz odkryć na przykład, że potrzebujesz 57,5% na egzaminie końcowym , aby przy uwzględnieniu wagi każdego komponentu ocena z kursu osiągnęła wymagane 50%.

3. Planowanie inwestycji i oszczędzania

Wyznaczanie celów jest również pomocnym narzędziem podczas przygotowywania planów inwestycyjnych lub oszczędnościowych . Jeśli znasz już oczekiwany zwrot z inwestycji i ramy czasowe, możesz zadać sobie pytania takie jak: „Ile muszę wpłacać miesięcznie, aby osiągnąć skumulowaną kwotę X?” lub „Jaka jest początkowa kwota inwestycji, jeśli chcę osiągnąć określony zwrot?”.

Te scenariusze obejmują funkcje finansowe, takie jak FV (wartość przyszła) , PV (wartość bieżąca) , RATE (stopa procentowa ) lub sam PMT (wartość bieżąca). Konstruujesz formułę opisującą Twoją sytuację (na przykład wartość przyszłą serii okresowych wpłat) i pozostawiasz miesięczną wpłatę lub kapitał początkowy jako zmienną.

Po skonfigurowaniu modelu, za pomocą funkcji Goal Seek ustawiasz cel oszczędności lub zwrotu w komórce wyników, a następnie Excel dostosowuje okresową składkę lub początkową kwotę inwestycji. Działa to tak samo, jak w przypadku pożyczek, ale po stronie oszczędności lub inwestycji.

4. Ograniczenia kredytowe i budżetowe

Wracając do kredytów, możesz pójść o krok dalej. Wyobraź sobie, że obliczyłeś za pomocą funkcji PŁATNOŚĆ, ile powinieneś płacić rocznie za pożyczkę w wysokości 12 000 euro na 20 lat z oprocentowaniem 5%. Wynik to miesięczna rata w wysokości około 962,91 euro, ale Twój maksymalny budżet to 900 euro.

Dzięki funkcji Goal Seek możesz podejść do problemu z odwrotnej perspektywy: chcesz, aby roczna rata wyniosła -900 , a kwota pożyczki została skorygowana. Konfigurujesz narzędzie tak, aby komórką docelową była komórka zawierająca wynik PŁATNOŚCI, pożądaną wartością było -900, a komórką, która ulega zmianie, był kapitał pożyczki.

Wynik może wynieść na przykład około 11 216. W ten sposób dokładnie wiesz, ile możesz pożyczyć, nie przekraczając limitu 900 rocznie. Jeśli zamiast ustawiać kapitał, chcesz zachować tę samą kwotę pożyczki i zmienić okres spłaty , po prostu wybierz komórkę zawierającą liczbę lat lub miesięcy jako tę, którą chcesz zmienić.

5. Analiza korzyści i niezbędnych działań sprzedażowych

W świecie biznesu narzędzie Goal Seek przydaje się w odpowiedzi na pytania takie jak: „ Ile jednostek muszę sprzedać, aby zarobić 50 000? ” lub „Po jakiej cenie muszę sprzedać, aby osiągnąć określoną marżę, biorąc pod uwagę koszty i zdolność produkcyjną?”.

Możesz utworzyć formułę zysku, taką jak (Cena – Koszt) × Liczba sprzedanych jednostek , i użyć jej jako komórki docelowej. W zależności od potrzeb możesz ustawić pożądany zysk i pozostawić komórkę, która będzie się zmieniać w zależności od liczby sprzedanych jednostek lub ceny jednostkowej.

Na przykład, jeśli ustawisz docelowy zysk na poziomie 50 000, możesz zlecić funkcji Goal Seek obliczenie, ile jednostek należy sprzedać, utrzymując cenę i koszt na stałym poziomie . Możesz też ustawić wolumen sprzedaży ograniczony mocami produkcyjnymi i pozwolić, aby cena dostosowywała się w celu osiągnięcia pożądanego zysku.

Różnice między narzędziami Goal Seek i Solver

Ważne jest zrozumienie, gdzie kończy się użyteczność funkcji Goal Seek , a gdzie zaczyna się użyteczność Solvera , kolejnego bardzo zaawansowanego dodatku do Excela. Oba są przydatne w analizie scenariuszy, ale nie rozwiązują tych samych typów problemów.

  8 najlepszych programów dla firmy

Funkcja szukania wyniku została zaprojektowana do rozwiązywania problemów z pojedynczą zmienną . Oznacza to, że może zmienić tylko jedną komórkę, aby osiągnąć wartość docelową. Jeśli masz więcej niż jedną niewiadomą, funkcja nie daje oczekiwanych rezultatów i tutaj pojawia się Solver.

Solver pozwala pracować z wieloma komórkami zmiennych jednocześnie , ustawiać ograniczenia (na przykład, że niektóre komórki nie mogą być ujemne, że nie można przekroczyć określonego maksimum itd.) i decydować, czy celem jest maksymalizacja, minimalizacja, czy osiągnięcie równej wartości. To znacznie bardziej kompleksowe narzędzie do optymalizacji.

Na przykład, jeśli chcesz wiedzieć, jaką ocenę potrzebujesz z angielskiego i chemii, aby średnia z tych dwóch przedmiotów i innych przedmiotów wynosiła 84, masz problem z dwiema zmiennymi . Funkcja szukania wyniku nie zadziała, ale Solver tak. W takim przypadku ustaw komórkę celu na średnią, ustaw wartość na 84 i określ, że komórki, które mogą się zmieniać, to oceny z angielskiego i chemii.

Innym typowym przykładem zastosowania Solvera jest maksymalizacja zysku przy pewnych ograniczeniach , np. gdy firma sprzedaje kilka usług, z których każda przynosi jej zysk, i chce znaleźć kombinację jednostek do sprzedaży, która zmaksymalizuje łączny zysk, przy jednoczesnym spełnieniu pewnych warunków (minimalna sprzedaż jednej usługi, maksymalna sprzedaż innej, globalny limit jednostek itp.).

Typowe problemy przy korzystaniu z funkcji szukania wyniku i jak ich uniknąć

Chociaż narzędzie Goal Seek jest bardzo łatwe w użyciu, czasami może nie dostarczyć rozwiązania lub zwrócić nietypowe wyniki. W większości przypadków problem leży w konfiguracji lub właściwościach numerycznych problemu , a nie w samym narzędziu.

Częstą przyczyną jest to, że komórka docelowa nie zależy od komórki, która jest zmieniana . Jeśli formuła w komórce docelowej nie dotyczy komórki określonej jako zmienna (bezpośrednio lub pośrednio), program Excel nie musi niczego modyfikować, a funkcja szukania wyniku zakończy się niepowodzeniem.

Odwołania cykliczne również mogą powodować problemy ; są to formuły, w których komórka staje się zależna od samej siebie. Może to uniemożliwić zbieżność obliczeń lub spowodować wyłączenie niektórych funkcji programu Excel. Zaleca się sprawdzenie, czy formuły są spójne i nie powtarzają się rekurencyjnie bez żadnej kontroli.

Kolejną kwestią wartą uwagi są opcje iteracji w programie Excel . Jeśli maksymalna liczba iteracji lub maksymalna dopuszczalna zmiana między iteracjami jest zbyt niska, funkcja szukania wyniku może przedwcześnie się poddać lub uzyskać niedokładne przybliżenie. Parametry te można dostosować w menu Plik > Opcje > Formuły, zwiększając maksymalną liczbę iteracji lub dostosowując maksymalną zmianę.

Na koniec warto sprawdzić, czy Twój cel ma sens liczbowy . Jeśli wymagasz czegoś niemożliwego ze względu na ograniczenia Twojego modelu (na przykład nierealistycznie niskiej miesięcznej raty za ogromną pożyczkę z bardzo wysokim oprocentowaniem), to normalne, że Excel nie będzie w stanie znaleźć żadnej odpowiedniej wartości.

Narzędzie Szukaj wyniku w programie Excel, jeśli jest używane mądrze, staje się bardzo skutecznym narzędziem do udzielania odpowiedzi na wszelkiego rodzaju pytania liczbowe: pozwala przejść od pytania „co się stanie, jeśli to zmienię?” do pytania „to jest to, co chcę osiągnąć, powiedz mi, co muszę zmienić” i dzięki temu oszczędzasz mnóstwo czasu prób i błędów zarówno w zarządzaniu swoimi finansami osobistymi, jak i analizowaniu swojej firmy lub studiów.

Podobne artykuły:
Wyznaczaj cele finansowe, aby odnieść sukces
Isaac

Isaac

Pisarz z pasją zajmujący się światem bajtów i technologii w ogóle. Uwielbiam dzielić się swoją wiedzą poprzez pisanie i właśnie to będę robić na tym blogu, pokazywać Ci wszystkie najciekawsze rzeczy o gadżetach, oprogramowaniu, sprzęcie, trendach technologicznych i nie tylko. Moim celem jest pomóc Ci poruszać się po cyfrowym świecie w prosty i zabawny sposób.