Jak korzystać z wyszukiwania celu i rozwiązywania w programie Excel do rozwiązywania nieznanych zmiennych

Excel jest bardzo zdolny, gdy masz wszystkie dane potrzebne do obliczeń.

Ale czy nie byłoby miło, gdyby mogło? rozwiąż nieznane zmienne ?

Dzięki funkcji Goal Seek i dodatku Solver jest to możliwe. A my pokażemy Ci jak. Czytaj dalej, aby uzyskać pełny przewodnik, jak rozwiązać pojedynczą komórkę za pomocą Szukania celu lub bardziej skomplikowane równanie za pomocą Solvera.

Jak korzystać z wyszukiwania celu w programie Excel

Wyszukiwanie celu jest już wbudowane w program Excel. Jest pod Dane zakładka, w Co jeśli analiza menu:

W tym przykładzie użyjemy bardzo prostego zestawu liczb. Mamy trzy czwarte wartości sprzedaży i roczny cel. Możemy użyć Goal Seek, aby dowiedzieć się, jakie liczby muszą być w Q4, aby osiągnąć cel.

Jak widać, aktualna łączna sprzedaż wynosi 114 706 sztuk. Jeśli chcemy sprzedać 250 tys. do końca roku, ile musimy sprzedać w IV kwartale? Poszukiwanie celu w programie Excel powie nam.

Oto jak korzystać z wyszukiwania celu, krok po kroku:

Kliknij Dane > Analiza warunkowa > Poszukiwanie celu . Zobaczysz to okno:
Umieść część równania „równa się” w Ustaw komórkę pole. Jest to liczba, którą program Excel spróbuje zoptymalizować. W naszym przypadku jest to suma bieżąca naszych wyników sprzedaży w komórce B5.
Wpisz swoją wartość celu w Cenić pole. Poszukujemy łącznie 250 000 sprzedanych jednostek, więc w tym polu wpiszemy „250 000”.
Powiedz Excelowi, którą zmienną rozwiązać w Zmieniając komórkę pole. Chcemy zobaczyć, jaka powinna być nasza sprzedaż w IV kwartale. Powiemy więc programowi Excel, aby rozwiązał dla komórki D2. Będzie wyglądać tak, gdy będzie gotowy do pracy:
Uderzyć ok rozwiązać dla swojego celu. Kiedy wygląda dobrze, po prostu uderz ok . Excel poinformuje Cię, gdy wyszukiwanie celu znajdzie rozwiązanie.
Kliknij ok ponownie, a w komórce, którą wybrałeś, zobaczysz wartość, która rozwiązuje twoje równanie Zmieniając komórkę .

W naszym przypadku rozwiązanie to 135 294 jednostki. Oczywiście mogliśmy to po prostu odkryć, odejmując sumę bieżącą od celu rocznego. Ale Poszukiwanie celu może być również użyte w komórce, która ma już w sobie dane . I to jest bardziej przydatne.

Zauważ, że Excel nadpisuje nasze poprzednie dane. To dobry pomysł na uruchom wyszukiwanie celu na kopii swoich danych . Dobrym pomysłem jest również zanotowanie na skopiowanych danych, że zostały one wygenerowane za pomocą wyszukiwania celu. Nie chcesz go pomylić z aktualnymi, dokładnymi danymi.

mój zewnętrzny dysk twardy się nie wyświetla

Więc Poszukiwanie Celu to przydatna funkcja Excela , ale to nie jest aż tak imponujące. Przyjrzyjmy się o wiele ciekawszemu narzędziu: dodatku Solver.

Co robi solver programu Excel?

Krótko mówiąc, Solver jest jak wielowymiarowa wersja Goal Seek . Pobiera jedną zmienną celu i dostosowuje wiele innych zmiennych, aż otrzyma żądaną odpowiedź.

Może znaleźć maksymalną wartość liczby, minimalną wartość liczby lub dokładną liczbę.

Działa w ramach ograniczeń, więc jeśli jedna zmienna nie może zostać zmieniona lub może się zmieniać tylko w określonym zakresie, Solver weźmie to pod uwagę.

To świetny sposób na rozwiązanie wielu nieznanych zmiennych w programie Excel. Ale znalezienie i używanie go nie jest proste.

Przyjrzyjmy się ładowaniu dodatku Solver, a następnie przejdźmy do sposobu korzystania z dodatku Solver w programie Excel 2016.

Jak załadować dodatek Solver

Excel nie ma domyślnie dodatku Solver. Jest to dodatek, więc podobnie jak inne zaawansowane funkcje programu Excel , musisz go najpierw załadować. Na szczęście jest już na twoim komputerze.

Zmierzać do Plik > Opcje > Dodatki . Następnie kliknij Udać się obok Zarządzaj: dodatki programu Excel .

Jeśli to menu zawiera coś innego niż „Dodatki programu Excel”, musisz to zmienić:

W wyświetlonym oknie zobaczysz kilka opcji. Upewnij się, że pole obok Dodatek Solver jest sprawdzany i trafia ok .

Zobaczysz teraz Solver przycisk w Analiza grupa Dane patka:

Jeśli już korzystałeś z Pakiet narzędzi do analizy danych , zobaczysz przycisk Analiza danych. Jeśli nie, Solver pojawi się sam.

Teraz, po załadowaniu dodatku, przyjrzyjmy się, jak z niego korzystać.

Jak korzystać z dodatku Solver w programie Excel

Każda czynność dodatku Solver składa się z trzech części: celu, komórek zmiennych i ograniczeń. Przejdziemy przez każdy ze stopni.

Kliknij Dane > Solver . Zobaczysz okno Parametry dodatku Solver poniżej. (Jeśli nie widzisz przycisku Solver, zobacz poprzednią sekcję dotyczącą ładowania dodatku Solver).
Ustaw cel swojej komórki i powiedz Excelowi swój cel. Cel znajduje się u góry okna dodatku Solver i składa się z dwóch części: komórki celu oraz wyboru maksymalizacji, minimalizacji lub określonej wartości. Jeśli wybierzesz Maks. , Excel dostosuje zmienne, aby uzyskać największą możliwą liczbę w komórce celu. Min jest odwrotnie: Solver zminimalizuje liczbę celów. Wartość pozwala określić konkretną liczbę, której Solver ma szukać.
Wybierz komórki zmiennych, które program Excel może zmienić. Zmienne komórki są ustawione za pomocą Zmieniając komórki zmiennych pole. Kliknij strzałkę obok pola, a następnie kliknij i przeciągnij, aby zaznaczyć komórki, z którymi powinien pracować dodatek Solver. Zwróć uwagę, że są to wszystkie komórki które mogą się różnić. Jeśli nie chcesz, aby komórka się zmieniała, nie zaznaczaj jej.
Ustaw ograniczenia dla wielu lub pojedynczych zmiennych. Wreszcie dochodzimy do ograniczeń. W tym miejscu Solver jest naprawdę potężny. Zamiast zmieniać dowolną z komórek zmiennych na dowolną liczbę, możesz określić ograniczenia, które muszą być spełnione. Aby uzyskać szczegółowe informacje, zobacz sekcję dotyczącą ustawiania ograniczeń poniżej.
Gdy wszystkie te informacje są na miejscu, naciśnij Rozwiązywać aby uzyskać odpowiedź. Excel zaktualizuje Twoje dane, aby uwzględnić nowe zmienne (dlatego zalecamy najpierw utworzenie kopii danych).

Możesz również generować raporty, które krótko omówimy w naszym przykładzie dodatku Solver poniżej.

Jak ustawić ograniczenia w dodatku Solver

Możesz powiedzieć programowi Excel, że jedna zmienna musi być większa niż 200. Próbując różnych wartości zmiennych, program Excel nie spadnie poniżej 201 z tą konkretną zmienną.

Aby dodać wiązanie, kliknij Dodać obok listy ograniczeń. Dostaniesz nowe okno. Wybierz komórkę (lub komórki), która ma być ograniczona w Odniesienie do komórki pole, a następnie wybierz operatora.

Oto dostępni operatorzy:

<= (mniejszy lub równy)
= (równy)
=> (większy lub równy)
int (musi być liczbą całkowitą)
jestem (musi być 1 lub 0)
WszystkieRóżne

WszystkieRóżne jest trochę mylące. Określa, że każda komórka w zakresie, który wybierzesz dla Odniesienie do komórki musi być inną liczbą. Ale określa również, że muszą być od 1 do liczby komórek. Więc jeśli masz trzy komórki, otrzymasz liczby 1, 2 i 3 (ale niekoniecznie w tej kolejności)

Na koniec dodaj wartość ograniczenia.

Pamiętaj, że możesz wybierz wiele komórek dla odniesienia do komórki. Jeśli chcesz, aby sześć zmiennych miało wartości powyżej 10, możesz wybrać je wszystkie i powiedzieć Solverowi, że muszą być większe lub równe 11. Nie musisz dodawać ograniczenia dla każdej komórki.

Możesz również użyć pola wyboru w głównym oknie dodatku Solver, aby upewnić się, że wszystkie wartości, dla których nie określono ograniczeń, są nieujemne. Jeśli chcesz, aby zmienne były ujemne, odznacz to pole.

Przykład rozwiązywania

Aby zobaczyć, jak to wszystko działa, użyjemy dodatku Solver, aby wykonać szybkie obliczenia. Oto dane, od których zaczynamy:

W nim mamy pięć różnych prac, z których każda płaci inną stawkę. Mamy też liczbę godzin, jaką pracownik teoretyczny przepracował na każdym z tych stanowisk w danym tygodniu. Możemy użyć dodatku Solver, aby dowiedzieć się, jak zmaksymalizować łączną płacę przy zachowaniu pewnych zmiennych w ramach pewnych ograniczeń.

Oto ograniczenia, których użyjemy:

Brak miejsc pracy może spaść poniżej czterech godzin.
Praca 2 musi być więcej niż osiem godzin .
Praca 5 musi być mniej niż jedenaście godzin .
Całkowita liczba przepracowanych godzin musi być równy 40 .

Pomocne może być wypisanie ograniczeń w ten sposób przed użyciem dodatku Solver.

Oto jak ustawiliśmy to w Solver:

Po pierwsze, zauważ, że Utworzyłem kopię tabeli więc nie nadpisujemy oryginalnej, która zawiera nasze aktualne godziny pracy.

Po drugie, zobacz, że wartości w ograniczeniach większe niż i mniejsze niż są jeden wyżej lub niżej niż to, o czym wspomniałem powyżej. To dlatego, że nie ma opcji „większe lub mniejsze”. Są tylko większe niż lub równe i mniejsze niż lub równe.

Chodźmy uderzyć Rozwiązywać i zobacz, co się stanie.

Solver znalazł rozwiązanie! Jak widać po lewej stronie okna powyżej, nasze zarobki wzrosły o 130 USD. I wszystkie ograniczenia zostały spełnione.

jak kupować luzem do odsprzedaży?

Aby zachować nowe wartości, upewnij się, że Zachowaj rozwiązanie Solver jest sprawdzony i trafiony ok .

Jeśli chcesz uzyskać więcej informacji, możesz wybrać raport z prawej strony okna. Wybierz wszystkie raporty, które chcesz, powiedz Excelowi, czy chcesz je przedstawić (polecam) i naciśnij ok .

Raporty są generowane na nowych arkuszach w skoroszycie i zawierają informacje o procesie, przez który dodatek Solver przeszedł, aby uzyskać odpowiedź.

W naszym przypadku raporty nie są zbyt ekscytujące i nie ma w nich zbyt wielu ciekawych informacji. Ale jeśli uruchomisz bardziej skomplikowane równanie dodatku Solver, możesz znaleźć przydatne informacje dotyczące raportowania w tych nowych arkuszach roboczych. Wystarczy kliknąć + przycisk z boku dowolnego raportu, aby uzyskać więcej informacji:

Zaawansowane opcje dodatku Solver

Jeśli nie wiesz zbyt wiele o statystykach, możesz zignorować zaawansowane opcje Solvera i po prostu uruchomić go tak, jak jest. Ale jeśli prowadzisz duże, złożone obliczenia, możesz chcieć się im przyjrzeć.

Najbardziej oczywista jest metoda rozwiązywania:

Możesz wybierać między GRG Nonlinear, Simplex LP i Evolutionary. Excel zawiera proste wyjaśnienie, kiedy należy używać każdego z nich. Lepsze wyjaśnienie wymaga pewnej znajomości statystyki i regresji.

Aby dostosować dodatkowe ustawienia, po prostu naciśnij Opcje przycisk. Możesz powiedzieć Excelowi o optymalności liczb całkowitych, ustawić ograniczenia czasu obliczeń (przydatne w przypadku ogromnych zestawów danych) i dostosować sposób, w jaki metody rozwiązywania GRG i Evolutionary wykonują obliczenia.

Ponownie, jeśli nie wiesz, co to oznacza, nie martw się. Jeśli chcesz dowiedzieć się więcej o tym, której metody rozwiązywania użyć, inżynier Excel ma dobry artykuł, który to dla ciebie przedstawia . Jeśli chcesz maksymalnej dokładności, Evolutionary jest prawdopodobnie dobrym sposobem. Pamiętaj tylko, że zajmie to dużo czasu.

Poszukiwanie celu i rozwiązywanie problemów: przenoszenie programu Excel na wyższy poziom

Teraz, gdy znasz już podstawy rozwiązywania nieznanych zmiennych w programie Excel, otwiera się przed Tobą zupełnie nowy świat obliczeń w arkuszach kalkulacyjnych.

Wyszukiwanie celu może pomóc Ci zaoszczędzić czas, wykonując niektóre obliczenia szybciej, a dodatek Solver dodaje ogromną ilość mocy do Zdolności kalkulacyjne Excela .

To tylko kwestia tego, żeby się z nimi komfortowo. Im częściej ich używasz, tym bardziej się przydadzą.

Czy w arkuszach kalkulacyjnych używasz wyszukiwania celu lub dodatku Solver? Jakie inne wskazówki możesz udzielić, aby uzyskać z nich najlepsze odpowiedzi? Podziel się swoimi przemyśleniami w komentarzach poniżej!

Udział Udział Ćwierkać E-mail 5 wskazówek, jak doładować swoje maszyny VirtualBox Linux

Masz dość słabej wydajności oferowanej przez maszyny wirtualne? Oto, co powinieneś zrobić, aby zwiększyć wydajność VirtualBox.

Czytaj dalej Powiązane tematy

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

O autorze Potem Albright(506 opublikowanych artykułów)

Dann jest konsultantem ds. strategii treści i marketingu, który pomaga firmom generować popyt i leady. Prowadzi również bloga o strategii i content marketingu na dannalbright.com.

Więcej od Danna Albrighta

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ć