Jak korzystać z wyszukiwania celu w programie Excel (z przykładami z życia)

Jeśli znasz wynik formuły, ale nie znasz wartości wejściowej, która wygeneruje wynik, pomoże Ci program Microsoft Excel. Po prostu naucz się korzystać z Goal Seek w Excelu i przeprowadzaj symulacje formuł i dużych danych liczbowych bez zmiany rzeczywistych danych.

Niezależnie od tego, czy chodzi o finanse osobiste, inwestycje oszczędnościowe czy projekt biznesowy, świadome podejmowanie decyzji jest kluczem do sukcesu. Do świadomego podejmowania decyzji potrzebne są symulowane dane z symulacji baz danych w oparciu o formułę i cel. Narzędziem do takiej symulacji opartej na danych jest Excel Goal Seek.

Jeśli dopiero zaczynasz pracę z programem Excel lub masz pewne doświadczenie w korzystaniu z niego, ale nie jesteś ekspertem, czytaj dalej, aby poznać podstawy funkcji Wyszukiwanie celu w programie Excel oraz korzystania z funkcji Wyszukiwanie celu w programie Excel.

Co to jest szukanie celu w Excelu?

Wyszukiwanie celu to wbudowane narzędzie do analizy typu „co by było, gdyby” w programie Excel. Pokazuje, jak ostateczna wartość w formule wpływa na wartość wejściową. W prostych słowach podaje wartość wejściową dla formuły, aby uzyskać pożądany wynik z tej samej formuły.

Inwestorzy finansowi, menedżerowie finansowi, właściciele firm i specjaliści z zakresu zarządzania często korzystają z tego narzędzia do analizy typu „co-jeśli” w programie Excel do przewidywania danych wejściowych w celu uzyskania pożądanego wyniku z dowolnego modelu matematycznego.

Możesz także nazwać Goal Seek w Excelu, zautomatyzowanym systemem symulacji, który analizuje Twoją formułę połączoną z zestawami danych o dowolnym rozmiarze i podaje wartość wejściową. Jeśli wprowadzisz wartość wejściową do swojego modelu matematycznego, otrzymasz wynik, którego szukasz.

Możesz użyć tego narzędzia w prawie każdym scenariuszu, ale najważniejsze z nich to:

• Ile jednostek produktów musisz sprzedać, aby osiągnąć określony przychód z dodanymi rabatami i prowizjami agentów
• Po jakim oprocentowaniu musisz pożyczyć, aby móc spłacić pożyczkę w określonym terminie w określonych ratach miesięcznych
• Ile godzin pracy potrzeba, aby ukończyć projekt w określonym terminie

Gdzie jest szukanie celu w Excelu?

Excel Goal Seek to jedno z trzech narzędzi do analizy typu „co-jeśli” programu Microsoft Excel. Tak więc narzędzie można znaleźć w menu poleceń Analiza typu „co-jeśli”. W menu wstążki programu Excel kliknij kartę Dane, a następnie przejdź do skrajnej prawej strony wstążki. Tam powinieneś zobaczyć sekcję Prognoza. Wybierz przycisk analizy „co by było, gdyby”, a poszukiwanie celu jest numerem dwa na  rozwijanej liście .

Gdzie jest szukanie celu w Excelu

Lokalizacja narzędzia jest taka sama w aplikacji Excel dla komputerów Mac. W chwili pisania tego tekstu funkcja jest dostępna w następujących wersjach programu Excel:

• Excel dla Microsoft 365 (Windows i Mac)
• Excel 2021 (Windows i Mac)
• Dostępne również dla systemów Windows i Mac z programu Excel 2010 i nowszych

Wymagania dotyczące funkcji wyszukiwania celu w programie Excel

Excel Goal Seek wykona wszystkie obliczenia w zapleczu. Wszystko, co musisz zrobić, to zdefiniować następujące trzy parametry z modelu matematycznego lub tabeli danych:

Komponenty i parametry wyszukiwania celu programu Excel

• Wprowadź odwołanie do komórki w polu Ustaw komórkę. Ta komórka odniesienia musi zawierać formułę. Excel Goal Seek używa tej formuły do ​​wyprowadzenia wartości wejściowej.
• Musisz wpisać wynik lub cel, który chcesz osiągnąć w polu Do wartości .
• Zmieniając komórkę to odwołanie do komórki, w której potrzebne są dane wejściowe dla symulowanego modelu matematycznego.

Jak korzystać z funkcji wyszukiwania celu w programie Excel

Poniżej znajdziesz kilka przykładowych scenariuszy, w których możesz użyć Wyszukiwania celu w programie Excel do przewidywania danych wejściowych dla żądanych danych wyjściowych na podstawie formuły matematycznej. Czynności te są podobne w przypadku urządzeń z systemem Windows 11 i macOS.

1. Szukaj celu, aby przewidzieć wynik egzaminu

Załóżmy, że musisz przyspieszyć swoje plany studiów w ostatnim semestrze studiów, aby osiągnąć średni wynik 85% po ukończeniu studiów. Oto jak możesz zastosować formułę Goal Seek, aby zasymulować wynik potrzebny w ostatnim semestrze, jeśli masz wyniki z pozostałych siedmiu semestrów:

• Utwórz nagłówki kolumn o nazwach Semester i Score w arkuszu programu Excel.
• Wpisz Semestr I do Semestr VIII w kolumnie Semestr .
• Wpisz wyniki wszystkich siedmiosemestralnych egzaminów z wyjątkiem semestru VIII .
• Utwórz średni wynik semestru , korzystając z następującego wzoru:

=ŚREDNIA(B2:B9)

• Dostosuj odwołania do komórek zgodnie z własnym arkuszem programu Excel.
• Teraz wywołaj funkcję szukania celu .
• Komórka Ustaw powinna być komórką z formułą, w której obliczono średnią.
• Komórka Do wartości powinna zawierać żądany średni wynik semestralny, który wynosi 85%.
• Wprowadź odwołanie do pustej komórki dla wyniku egzaminu z semestru VIII w polu Zmieniając komórkę .

Dowiedz się, jak korzystać z funkcji wyszukiwania celu, aby przewidzieć wynik egzaminu

• Kliknij OK , aby uzyskać wynik w semestrze VIII, który musisz uzyskać, aby uzyskać średni wynik ukończenia szkoły wynoszący 85%.

Musisz zdobyć 98% w ostatnim semestrze, aby uzyskać docelowy wynik ukończenia studiów.

 Microsoft Excel: jak łatwo zarządzać arkuszami

2. Przewiduj kwotę pożyczki za pomocą funkcji wyszukiwania celu

Załóżmy, że potrzebujesz pożyczyć kapitał na 40 lat na stałe oprocentowanie (8%) ze stałą miesięczną spłatą dla swojej firmy. Teraz wiesz, że możesz płacić 1800 USD miesięcznie ze zwrotów firmy. Nie wiesz jednak, ile musisz pożyczyć na powyższe oprocentowanie na powyższy okres. Możesz łatwo obliczyć kwotę do pożyczenia, wykonując następujące kroki:

• Utwórz wiersze dla następujących składników: Roczna stopa procentowa , Lata do spłaty , Kwota pożyczki i Raty miesięczne .
• Wprowadź odpowiednie wartości dla powyższych składników w odpowiednich komórkach, z wyjątkiem Kwoty pożyczki , którą musisz obliczyć za pomocą funkcji Goal Seek.
• Teraz zastąp wartość Raty miesięczne następującą formułą PMT w programie Excel:

=PMT(B1/12;B2*12;B3;0)

• Wywołaj funkcję Wyszukiwanie celu z poziomu Dane > Analiza co-jeśli > Wyszukiwanie celu .
• Komórka Set powinna odwoływać się do komórki z formułą PMT.
• Wartość powinna wynosić 1500 USD.
• Zmieniając komórkę powinna być wartość Kwota pożyczki .

Naucz się przewidywać kwotę pożyczki za pomocą funkcji wyszukiwania celu

• Kliknij przycisk OK , aby przeprowadzić symulację kwoty pożyczki za pomocą obliczeń iteracyjnych.

Zgodnie z funkcją Goal Seek, kwota pożyczki musi wynosić 215 731 USD, jeśli chcesz ją pożyczyć na 40 lat z roczną stopą procentową 8% i płacić miesięczną ratę w wysokości 1500 USD.

3. Znajdź cel sprzedaży za pomocą funkcji wyszukiwania celu

Jeśli jesteś szefem sprzedaży i marketingu lub pracujesz jako dyrektor ds. sprzedaży i musisz stworzyć cel sprzedaży, aby osiągnąć określony cel w zakresie przychodów, Goal Seek jest idealnym narzędziem. Na przykład Twoja firma sprzedaje obecnie 1000 jednostek produktów.

Sugerowana cena detaliczna produktu wynosi 50 USD, a hurtownikom oferujesz również rabat w wysokości 10%. Chcesz jednak zwiększyć docelowe przychody swojej firmy ze sprzedaży produktów do 75 000 USD bez zmiany sugerowanej ceny detalicznej i stopy dyskontowej. W takim razie ile produktów musisz sprzedać? Sprawdźmy, korzystając z funkcji Goal Seek poniżej:

• Utwórz wiersze dla znanych składników, takich jak Liczba sprzedanych jednostek , Sugerowana cena detaliczna i Rabat .
• Oblicz przychód za pomocą następującego wzoru:

=B2*(B3*(1-B4))

Dowiedz się, jak znaleźć cel sprzedaży za pomocą funkcji wyszukiwania celu

• Teraz otwórz Goal Seek i ustaw następujące parametry:
  • Ustaw komórkę : $B$5
  • Wartość : 75 000 USD
  • Zmieniając komórkę : $B$2

Upewnij się, że dostosujesz odwołania do komórek zgodnie z własnymi danymi i arkuszem programu Excel. Naciśnij OK , a otrzymasz docelową sprzedaż produktów, która wynosi 1667 sztuk przy tej samej sugerowanej cenie detalicznej i stopie dyskontowej.

Wyszukiwanie celu w programie Excel: często zadawane pytania

How Do I Use Goal Seek in Excel for Multiple Cells?

Wyszukiwanie celu w programie Excel umożliwia symulowanie danych wejściowych formuły matematycznej w celu uzyskania z góry określonego wyniku. Niestety nie obsługuje uwzględniania żadnych zmiennych ani wielu komórek. W przypadku wielu komórek i analizy typu „co-jeśli” opartej na odmianach można użyć dodatku Solver do programu Excel.

What Is the Shortcut for Goal Seek?

Obecnie nie ma dedykowanego skrótu do funkcji Wyszukiwanie celu w programie Excel. Możesz użyć klawisza Alt w programie Excel, a następnie nacisnąć kilka klawiszy na klawiaturze, aby wywołać narzędzie Goal Seek bez użycia myszy. Kroki są opisane tutaj:

Press Alt on Excel > without clicking anywhere on the app press A > press W > press G to open Goal Seek

How Do I Make My Goal Seek More Accurate?

Czasami funkcja Goal Seek może uzyskać wartość wejściową w postaci ułamków dziesiętnych. Jeśli potrzebujesz automatycznie zaokrąglonej figury, możesz zwiększyć jej dokładność. Oto jak możesz to zrobić:

• Kliknij kartę Plik i wybierz Opcje na pasku bocznym.
• W oknie Opcje programu Excel kliknij opcję Formuły .
• Zaznacz opcję Włącz funkcję obliczeń iteracyjnych .

Jak zoptymalizować wyszukiwanie celu w programie Excel

• Zwiększ pole Maksymalna liczba iteracji do 150 lub więcej, aby umożliwić programowi Excel testowanie bardziej wykonalnych rozwiązań.
• Zwiększ także wartość Maksymalna zmiana z wartości domyślnej 0,001 do 0,00001.

Can I Do Goal Seek on Multiple Cells?

Nie można wykonać wyszukiwania celu w programie Excel dla wielu komórek.

What Are the Advantages of Goal Seek?

Oto zalety funkcji Goal Seek w Excelu:

• Goal Seek to najprostsza symulacja dostępna dla analizy What-If w programie Excel
• Wychodzi z pudełka z aplikacją komputerową Excel
• Goal Seek działa w backendzie i nie wymaga modyfikacji całej bazy danych
• Przydatne do modelowania finansowego

What Are the Limitations of Goal Seek in Excel?

Wyszukiwanie celu w programie Excel ma następujące wady:

• Wyszukiwanie celu nie będzie działać, jeśli istnieją  odwołania cykliczne
• Podczas przeprowadzania symulacji szukania celu nie można wprowadzać żadnych zmiennych
• Nie działa na wielu komórkach
• Wyszukiwanie celu w programie Excel nie jest dostępne w aplikacji Excel Web App ani Excel dla aplikacji sieci Web

Poszukiwanie celu w Excelu: ostatnie słowa

Czytając powyższy artykuł na temat wyszukiwania celu w programie Excel, powinieneś mieć podstawową lub średnią wiedzę na temat przeprowadzania analizy „co by było, gdyby” w programie Excel. Wyszukiwanie celu w programie Excel jest szczególnie przydatne w zadaniach matematycznych, w których istnieje jasna formuła, wartość wejściowa, kilka zmiennych powiązanych z formułą i jeden cel do osiągnięcia.

Zostaw poniżej swój komentarz na temat swoich doświadczeń z powyższym przewodnikiem krok po kroku dotyczącym funkcji Excel Goal Seek. Jeśli znasz inne techniki analizy typu „co-jeśli” programu Excel, daj mi znać.

Następnie,  jak porównać tekst w programie Excel .