Excel 2019 (część 28): Analiza typu „co jeśli” (analiza „jeśli-to”)

Excel zawiera zaawansowane narzędzia do wykonywania złożonych obliczeń matematycznych, w tym analizę typu „co by było, gdyby”. Ta funkcja może pomóc Ci eksperymentować i odpowiadać na pytania z danymi, nawet jeśli są niekompletne. W tym artykule dowiesz się, jak korzystać z narzędzia analitycznego o nazwie Goal Seek.

Szukanie celu

Za każdym razem, gdy tworzysz formułę lub funkcję w programie Excel, musisz połączyć różne części, aby obliczyć wynik. Wyszukiwanie celu działa w odwrotny sposób: pozwala zacząć od pożądanego wyniku, a silnik obliczy wartość wejściową, która zapewni ten wynik. W artykule zostanie wykorzystanych kilka przykładów, aby pokazać, jak korzystać z funkcji Szukanie celu.

Przykład 1 użycia funkcji Szukanie celu

Załóżmy, że jesteś zapisany na zajęcia. Aktualnie masz 65 punktów, a aby zaliczyć ten przedmiot musisz zdobyć co najmniej 70 punktów. Na szczęście masz jeszcze jedno zadanie, które może pomóc Ci podnieść Twoją średnią ocen. Możesz użyć funkcji Szukanie celu, aby dowiedzieć się, jaki wynik potrzebujesz w ostatnim ćwiczeniu, aby zaliczyć przedmiot.

Na poniższym obrazku widać, że wyniki za pierwsze 4 ćwiczenia wynoszą 58, 70, 72 i 60. Chociaż nie wiadomo, jaki będzie wynik za piąte ćwiczenie, możesz napisać wzór – lub funkcję – aby to obliczyć potrzebny wynik końcowy. W tym przypadku każde zadanie ma taką samą wagę, więc wystarczy, że uśrednimy wszystkie pięć wyników, wpisując =ŚREDNIA(B2:B6). Podczas korzystania z funkcji Szukanie celu w komórce B6 zostanie wyświetlona minimalna liczba punktów wymagana do osiągnięcia tego końcowego zadania.

1. Wybierz komórkę, której wartość chcesz zmienić. Za każdym razem, gdy używasz funkcji Szukanie celu, musisz wybrać komórkę, która zawiera już formułę lub funkcję. Na przykład wybierzemy komórkę B7, ponieważ zawiera ona formułę =ŚREDNIA(B2:B6).

2. Na karcie Dane kliknij polecenie Analiza warunkowa , a następnie z menu rozwijanego wybierz opcję Szukanie celu .

3. Pojawi się okno dialogowe z trzema polami. Pierwsze pole, Ustaw komórkę:, będzie zawierać żądany wynik. W przykładzie wybrano komórkę B7.

Drugie pole, Do wartości: , to pożądany wynik. Na przykład wpisalibyśmy 70 , ponieważ musimy zdobyć przynajmniej tę liczbę, aby zaliczyć zajęcia.

Trzecie pole, Zmieniając komórkę:, to komórka, w której funkcja Goal Seek umieści swoją odpowiedź. Na przykład wybierzemy komórkę B6 , ponieważ chcemy określić wynik, jaki musimy uzyskać w ostatnim ćwiczeniu.

4. Kiedy skończysz, kliknij OK.

5. Okno dialogowe poinformuje Cię, czy funkcja Goal Seek znalazła odpowiedź, czy nie. Kliknij OK.

6. Wyniki pojawią się w określonej komórce. W tym przykładzie narzędzie Goal Seek obliczyło, że aby zaliczyć zadanie końcowe, potrzebny będzie wynik co najmniej 90 punktów.

Przykład 2 użycia poszukiwania celu

Załóżmy, że planujesz wydarzenie i chcesz zaprosić jak najwięcej osób, nie przekraczając budżetu wynoszącego 500 USD. Możesz użyć funkcji Szukanie celu, aby dowiedzieć się, ile osób zaprosić. W poniższym przykładzie komórka B5 zawiera formułę =B2+B3*B4 służącą do obliczenia całkowitego kosztu rezerwacji plus koszt na osobę.

1. Wybierz komórkę, której wartość chcesz zmienić. Na przykład wybierzemy komórkę B5.

2. Na karcie Dane kliknij polecenie Analiza warunkowa , a następnie z menu rozwijanego wybierz opcję Szukanie celu .

3. Pojawi się okno dialogowe z trzema polami. Pierwsze pole, Ustaw komórkę:, będzie zawierać żądany wynik. W przykładzie wybrano komórkę B5.

Drugie pole, Do wartości: , to pożądany wynik. Na przykład wprowadziłbym 500, ponieważ chcę wydać tylko 500 USD.

Trzecie pole, Zmieniając komórkę:, to komórka, w której funkcja Goal Seek umieści swoją odpowiedź. Na przykład wybrałbym komórkę B4, ponieważ chcę wiedzieć, ilu gości mogę zaprosić, nie wydając więcej niż 500 USD.

4. Kiedy skończysz, kliknij OK.

5. Okno dialogowe poinformuje Cię, czy funkcja Goal Seek znalazła odpowiedź, czy nie. Kliknij OK.

6. Wyniki pojawią się w określonej komórce. W tym przykładzie funkcja Goal Seek obliczyła odpowiedź na około 18,62. W tym przypadku ostateczna odpowiedź musi być liczbą całkowitą, dlatego konieczne jest zaokrąglenie odpowiedzi w górę lub w dół. Ponieważ zaokrąglenie w górę spowodowałoby przekroczenie budżetu, przykładem byłoby zaokrąglenie w dół do 18 gości.

Jak widać na powyższym przykładzie, w niektórych sytuacjach będzie wymagana odpowiedź będąca liczbą całkowitą. Jeśli funkcja Szukaj wyniku podaje liczbę dziesiętną, należy ją zaokrąglić w górę lub w dół, w zależności od sytuacji.

Inne rodzaje analizy „co by było, gdyby”.

W przypadku bardziej zaawansowanych projektów można rozważyć inne typy analizy typu „co by było, gdyby”: scenariusze i tabele danych. Zamiast zaczynać od pożądanego wyniku i cofać się, jak w przypadku szukania celu, opcje te pozwalają przetestować wiele wartości i zobaczyć, jak zmieniają się wyniki.

Skrypty umożliwiają zamianę wartości dla wielu komórek (do 32) jednocześnie. Możesz stworzyć dowolną liczbę scenariuszy, a następnie porównać je bez ręcznej zmiany wartości. Poniższy przykład wykorzystuje scenariusze do porównania różnych lokalizacji nadchodzącego wydarzenia.

Tabele danych umożliwiają wzięcie jednej lub dwóch zmiennych do formuły i zastąpienie ich dowolną liczbą różnych wartości, a następnie wyświetlenie wyników w tabeli. Ta opcja jest szczególnie wydajna, ponieważ wyświetla wiele wyników jednocześnie, w przeciwieństwie do scenariuszy lub poszukiwania celu. W poniższym przykładzie można zobaczyć 24 możliwe wyniki dla kredytu samochodowego.