Jak korzystać z wyszukiwania celu w programie Microsoft Excel w scenariuszach typu „co, jeśli”?

Analiza warunkowa programu Excel pozwala zobaczyć, jak zmiana komórki może wpłynąć na wynik formuły. W tym celu można użyć narzędzi Excela, aby obliczyć efekt zmiany wartości komórki w formule.

W programie Excel dostępne są trzy typy narzędzi analizy warunkowej: Menedżer scenariuszy, Wyszukiwanie celu i Tabela danych. Dzięki funkcji Wyszukiwanie celu możesz określić, jakie dane wejściowe są potrzebne do przekonwertowania formuły wstecz na określone dane wyjściowe.

Funkcja szukania celu w programie Excel działa metodą prób i błędów, więc jeśli nie zapewnia tego, czego chcesz, program Excel pracuje nad poprawą tej wartości, dopóki to nie nastąpi.

Co to są formuły wyszukiwania celu w programie Excel?

Wyszukiwanie celu w programie Excel zasadniczo dzieli się na trzy główne elementy:

Ustaw komórkę : Komórka, której chcesz użyć jako celu.
Cenić : Wartość, którą chcesz mieć jako swój cel.
Zmieniając komórkę : komórka, którą chcesz zmienić, aby osiągnąć wartość celu.

Po ustawieniu tych trzech ustawień program Excel spróbuje poprawić wartość w komórce, którą ustawiłeś Zmieniając komórkę aż do komórki, w której ustawiłeś Ustaw komórkę osiągnie ustaloną przez Ciebie wartość w Cenić .

Jeśli to wszystko brzmi dezorientująco, poniższe przykłady dadzą ci dobre wyobrażenie o tym, jak działa poszukiwanie celu.

Przykład poszukiwania bramki 1

Załóżmy na przykład, że masz dwie komórki (A i B) i trzecią komórkę, która oblicza średnią z tych dwóch.

Załóżmy teraz, że masz wartość statyczną dla A i chcesz podnieść średni poziom, zmieniając wartość dla B. Używając Szukania celu, możesz obliczyć, jaka wartość B da żądaną średnią.

Powiązane: Wskazówki dotyczące oszczędzania czasu w programie Microsoft Excel

jak powstrzymać google przed słuchaniem

Jak korzystać z wyszukiwania celu w programie Excel

W programie Excel kliknij komórkę C1 .
Na pasku formuły wprowadź następujący kod: |_+_| Ta formuła spowoduje, że Excel obliczy średnią wartości w komórkach A1 oraz B1 i wypisz to w komórce C1 . Możesz upewnić się, że formuła działa, wprowadzając liczby w A1 i B1. C1 powinien pokazać swoją średnią.
W tym przykładzie zmień wartość A1 komórka do 16 .
Wybierz komórkę C1 , a następnie ze wstążki przejdź do Dane patka.
Pod Zakładka Dane , Wybierz Co jeśli analiza i wtedy Poszukiwanie celu . Spowoduje to wyświetlenie okna Goal Seek.
W oknie Szukaj celu ustaw Ustaw komórkę do C1 . To będzie twoja komórka docelowa. Podświetlenie C1 przed otwarciem okna Goal Seek spowoduje automatyczne ustawienie go w Ustaw komórkę .
w Cenić , wstaw żądaną wartość celu. W tym przykładzie chcemy, aby nasza średnia wynosiła 26, więc wartość celu również wyniesie 26.
Wreszcie w Zmieniając komórkę , wybierz komórkę, którą chcesz zmienić, aby osiągnąć cel. W tym przykładzie będzie to komórka B2 .
Kliknij ok aby Poszukiwanie Celu działało swoją magią.

Po kliknięciu OK pojawi się okno dialogowe informujące, że Goal Seek znalazł rozwiązanie.

Wartość komórki A1 zmieni się na rozwiązanie, a to zastąpi poprzednie dane. Dobrym pomysłem jest uruchomienie wyszukiwania celu na kopii arkusza danych, aby uniknąć utraty oryginalnych danych.

Związane z: Szalone formuły Excela, które robią niesamowite rzeczy

Przykład poszukiwania bramki 2

Poszukiwanie celu może być przydatne, gdy używasz go w rzeczywistych scenariuszach. Jednak, aby w pełni wykorzystać swój potencjał Goal Seek, musisz najpierw mieć odpowiednią formułę.

W tym przykładzie weźmy mały przypadek bankowy. Załóżmy, że masz konto bankowe, które daje 4% roczne odsetki od pieniędzy, które masz na koncie.

Korzystając z analizy warunkowej i wyszukiwania celu, możesz obliczyć, ile pieniędzy musisz mieć na swoim koncie, aby uzyskać określoną kwotę miesięcznych płatności odsetek.

W tym przykładzie załóżmy, że chcemy co miesiąc otrzymywać 350 USD z płatności odsetek. Oto jak możesz to obliczyć:

jak działa snap score

W komórce A1 rodzaj Saldo .
W komórce A2 rodzaj Stawka roczna .
W komórce A4 rodzaj Miesięczny zysk .
W komórce B2 rodzaj 4% .
Wybierz komórkę B4 i na pasku formuły wprowadź poniższą formułę: |_+_| Miesięczny zysk będzie równy saldzie konta pomnożonemu przez stawkę roczną, a następnie podzielonej przez liczbę miesięcy w roku, czyli 12 miesięcy.
Przejdź do Dane zakładka, kliknij Co jeśli analiza, a następnie wybierz Poszukiwanie celu .
w Poszukiwanie celu okno, typ B4 w Ustaw komórkę .
Wpisz 350 w Cenić komórka. (To jest miesięczny zysk, który chcesz uzyskać)
Rodzaj B1 w Zmieniając komórkę . (Spowoduje to zmianę salda na wartość, która przyniesie 350 USD miesięcznie)
Kliknij ok . ten Status poszukiwania celu pojawi się okno dialogowe.

W oknie dialogowym Stan szukania celu zobaczysz, że funkcja Szukanie celu znalazła rozwiązanie w komórce B4. W komórce B1 widać rozwiązanie, które powinno wynosić 105 000.

Wymagania dotyczące poszukiwania celu

Dzięki funkcji Goal Seek możesz zobaczyć, że może zmienić tylko jedną komórkę, aby osiągnąć wartość celu. Oznacza to, że Poszukiwanie celu nie może rozwiązać i osiągnąć rozwiązania, jeśli ma więcej niż jedną zmienną.

W programie Excel nadal możesz rozwiązać więcej niż jedną zmienną, ale musisz użyć innego narzędzia o nazwie Solver. Możesz przeczytać więcej o Solverze Excela, czytając nasz artykuł na Jak korzystać z Solvera Excela .

Ograniczenia w poszukiwaniu celu

Formuła Goal Seek wykorzystuje proces prób i doskonalenia w celu osiągnięcia optymalnej wartości, co może stwarzać pewne problemy, gdy formuła generuje niezdefiniowaną wartość. Oto jak możesz sam to przetestować:

W komórce A1 , rodzaj 3 .
Wybierz komórkę C1 , a następnie w pasku formuły wprowadź poniższą formułę: |_+_| Ta formuła podzieli jeden przez A1-5, a jeśli A1-5 będzie wynosić 0, wynik będzie niezdefiniowaną wartością.
Przejdź do Dane zakładka, kliknij Co jeśli analiza a następnie wybierz Poszukiwanie celu .
w Ustaw komórkę , rodzaj C1 .
w Cenić , rodzaj 1 .
Wreszcie w Zmieniając komórkę rodzaj A1 . (Zmieni to wartość A1 na 1 w C1)
Kliknij ok .

Okno dialogowe Status wyszukiwania celu poinformuje Cię, że mogło nie znaleźć rozwiązania, i możesz dwukrotnie sprawdzić, czy wartość A1 jest odległa.

Rozwiązaniem tego szukania celu byłoby po prostu posiadanie 6 w A1. To da 1/1, co równa się 1. Ale w pewnym momencie podczas procesu prób i doskonalenia, Excel próbuje 5 w A1, co daje 1/0, co jest niezdefiniowane, zabijając proces.

Sposobem na obejście tego problemu w poszukiwaniu celu jest zastosowanie innej wartości początkowej, takiej, która może uniknąć niezdefiniowanego w procesie prób i doskonalenia.

Na przykład, jeśli zmienisz A1 na dowolną liczbę większą niż 5, a następnie powtórzysz te same kroki w poszukiwaniu celu dla 1, powinieneś otrzymać poprawny wynik.

która usługa dostawy jedzenia płaci najwięcej

Spraw, aby obliczenia były bardziej efektywne dzięki poszukiwaniu celu

Analiza warunkowa i wyszukiwanie celu mogą znacznie przyspieszyć obliczenia za pomocą formuł. Nauka właściwych formuł to kolejny ważny krok w kierunku zostania mistrzem Excela.

Udział Udział Ćwierkać E-mail 15 formuł Excela, które pomogą Ci rozwiązać problemy w prawdziwym życiu

Excel jest nie tylko dla biznesu. Oto kilka formuł Microsoft Excel, które pomogą Ci rozwiązać złożone codzienne problemy.

Czytaj dalej Powiązane tematy

Wydajność
Arkusz
Microsoft Excel
Porady dotyczące pakietu Microsoft Office
Analiza danych

O autorze Amir M. Wywiad(39 opublikowanych artykułów)

Amir jest studentem farmacji z pasją do technologii i gier. Lubi grać muzykę, jeździć samochodami i pisać słowa.

Więcej od Amira M. Bohlooli .a

Zapisz się do naszego newslettera

Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!

Kliknij tutaj, aby zasubskrybować