Excel zawiera zaawansowane narzędzia do wykonywania skomplikowanych obliczeń matematycznych, w tym analizę warunkową. Ta funkcja może pomóc w eksperymentowaniu z danymi i udzielaniu odpowiedzi na pytania, nawet jeśli dane są niekompletne. W tej lekcji nauczysz się, jak korzystać z narzędzia analizy warunkowej o nazwie Szukaj wyniku.
Obejrzyj poniższy film, aby dowiedzieć się więcej o analizie warunkowej i szukaniu wyniku.
Szukanie wyniku
Za każdym razem, gdy tworzysz formułę lub funkcję w Excelu, łączysz różne części, aby obliczyć wynik. Szukanie wyniku działa w odwrotny sposób: pozwala rozpocząć od pożądanego wyniku i oblicza wartość wejściową, która da ten wynik. Skorzystamy z kilku przykładów, aby pokazać, jak korzystać z Szukania wyniku.
Aby użyć funkcji Szukaj wyniku (przykład 1):
Powiedzmy, że zapisałeś się na zajęcia. Obecnie masz ocenę 65 i potrzebujesz co najmniej 70, aby zaliczyć klasę. Na szczęście masz jeszcze jedno ostatnie zadanie, które może podnieść Twoją średnią. Możesz użyć funkcji Szukaj wyniku, aby dowiedzieć się, jakiej oceny potrzebujesz z ostatniego zadania, aby zaliczyć klasę.
Na poniższym obrazku widać, że oceny z pierwszych czterech zadań to 58, 70, 72 i 60. Chociaż nie wiemy, jaka będzie piąta ocena, możemy napisać formułę - lub funkcję - obliczającą ocenę końcową. W takim przypadku każde zadanie jest ważone jednakowo, więc musimy tylko uśrednić wszystkie pięć ocen, wpisując = ŚREDNIA (B2: B6). Po użyciu funkcji Szukaj wyniku komórka B6 pokaże nam minimalną ocenę, jaką będziemy musieli wykonać w ramach tego zadania.
Wybierz komórkę z wartością, którą chcesz zmienić. Za każdym razem, gdy używasz funkcji Szukaj wyniku, musisz wybrać komórkę, która zawiera już formułę lub funkcję. W naszym przykładzie wybierzemy komórkę B7, ponieważ zawiera ona formułę = ŚREDNIA (B2: B6).
Na karcie Dane kliknij polecenie Analiza warunkowa, a następnie z rozwijanego menu wybierz opcję Szukaj wyniku.
Pojawi się okno dialogowe z trzema polami. Pierwsze pole, Ustaw komórkę:, będzie zawierało pożądany wynik. W naszym przykładzie komórka B7 jest już zaznaczona.
Drugie pole, wartość:, jest pożądanym wynikiem. W naszym przykładzie wprowadzimy 70, ponieważ musimy uzyskać przynajmniej tyle, aby zaliczyć klasę.
Trzecie pole, zmieniając komórkę: jest komórką, w której funkcja Szukaj wyniku umieści swoją odpowiedź. W naszym przykładzie wybieramy komórkę B6, ponieważ chcemy określić ocenę, jaką musimy uzyskać z końcowego zadania.
Po zakończeniu kliknij przycisk OK.
Okno dialogowe poinformuje Cię, czy funkcja Szukaj wyniku była w stanie znaleźć rozwiązanie. Kliknij OK.
Wynik pojawi się w określonej komórce. W naszym przykładzie funkcja Szukaj wyniku obliczyła, że będziemy musieli zdobyć co najmniej 90 punktów z ostatniego zadania, aby uzyskać pozytywną ocenę.
Aby użyć funkcji Szukaj wyniku (przykład 2):
Załóżmy, że planujesz wydarzenie i chcesz zaprosić jak najwięcej osób, nie przekraczając budżetu 500$. Możemy użyć funkcji Szukaj wyniku, aby dowiedzieć się, ile osób zaprosić. W naszym przykładzie poniżej komórka B5 zawiera formułę = B2 + B3 * B4 w celu obliczenia całkowitego kosztu rezerwacji pokoju oraz kosztu na osobę.
Wybierz komórkę z wartością, którą chcesz zmienić. W naszym przykładzie wybieramy komórkę B5.
Na karcie Dane kliknij polecenie Analiza warunkowa, a następnie z rozwijanego menu wybierz opcję Szukaj wyniku.
Pojawi się okno dialogowe z trzema polami. Pierwsze pole, Ustaw komórkę:, będzie zawierało pożądany wynik. W naszym przykładzie komórka B5 jest już zaznaczona.
Drugie pole, wartosć:, jest pożądanym wynikiem. W naszym przykładzie wprowadzimy 500, ponieważ chcemy wydać tylko 500$.
Trzecie pole, Zmieniając komórkę:, to komórka, w której funkcja Szukaj wyniku umieści odpowiedź. W naszym przykładzie wybieramy komórkę B4, ponieważ chcemy wiedzieć, ilu gości możemy zaprosić, nie wydając więcej niż 500$.
Po zakończeniu kliknij przycisk OK.
Okno dialogowe poinformuje Cię, czy funkcja Szukaj wyniku była w stanie znaleźć rozwiązanie. Kliknij OK.
Wynik pojawi się w określonej komórce. W naszym przykładzie funkcja Szukaj wyniku podała odpowiedź równą około 18,62. W takim przypadku nasz ostateczny wynik musi być liczbą całkowitą, więc będziemy musieli zaokrąglić odpowiedź w górę lub w dół. Ponieważ zaokrąglanie w górę spowodowałoby przekroczenie naszego budżetu, zaokrąglamy w dół do 18 gości.
Jak widać na powyższym przykładzie, niektóre sytuacje będą wymagały podania liczby całkowitej. Jeśli funkcja Szukaj wyniku podaje liczbę dziesiętną, należy zaokrąglić ją w górę lub w dół, w zależności od sytuacji.
Inne rodzaje analizy warunkowej
W przypadku bardziej zaawansowanych projektów warto rozważyć inne rodzaje analizy warunkowej: scenariusze i tabele danych. Zamiast zaczynać od pożądanego wyniku i pracować wstecz, jak w przypadku funkcji Szukaj wyniku, opcje te pozwalają przetestować wiele wartości i zobaczyć, jak zmieniają się wyniki.
Scenariusze pozwalają zastąpić wartości wieloma komórkami (do 32) jednocześnie. Możesz utworzyć dowolną liczbę scenariuszy, a następnie porównać je bez ręcznej zmiany wartości. W poniższym przykładzie wykorzystujemy scenariusze do porównania różnych miejsc na nadchodzące wydarzenie.
Aby uzyskać więcej informacji o scenariuszach, przeczytaj ten artykuł od Microsoft.
Tabele danych pozwalają uwzględnić jedną lub dwie zmienne w formule i zastąpić je dowolną liczbą różnych wartości, a następnie wyświetlić wyniki w tabeli. Ta opcja jest szczególnie wydajna, ponieważ pokazuje wiele wyników jednocześnie, w przeciwieństwie do scenariuszy lub opcji Szukaj wyniku. W poniższym przykładzie możemy zobaczyć 24 możliwe wyniki dla kredytu samochodowego.
Aby uzyskać więcej informacji na temat tabel danych, przeczytaj ten artykuł od Microsoft.
Kliknij kartę Challenge w lewym dolnym rogu skoroszytu.
W komórce B8 utwórz funkcję, która oblicza średnią sprzedaży w B2: B7.
Skoroszyt pokazuje miesięczne kwoty sprzedaży Dave'a za pierwszą połowę roku. Jeśli osiągnie średnią 200 000$ w połowie roku, otrzyma 5% premii. Użyj opcji Szukaj wyniku, aby dowiedzieć się, ile musi sprzedać w czerwcu, aby uzyskać średnią 200 000$.
Po zakończeniu skoroszyt powinien wyglądać następująco:
