Jak korzystać z funkcji Excel Search Seek and Solver do rozwiązywania nieznanych zmiennych

  • Lesley Fowler
  • 0
  • 2061
  • 38
Reklama

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

Ale czy nie byłoby miło, gdyby mógł rozwiąż dla nieznanych zmiennych?

Dzięki Goal Seek i dodatkowi Solver może to zrobić. A my pokażemy Ci jak. Zapoznaj się z pełnym przewodnikiem, jak rozwiązać pojedynczą komórkę za pomocą funkcji Szukaj celu lub bardziej skomplikowanego równania z Solwerem.

Jak korzystać z funkcji szukania celu w programie Excel

Wyszukiwanie celów jest już wbudowane w program Excel. Jest pod Dane w zakładce 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ć funkcji Szukanie celu, aby dowiedzieć się, jakie liczby muszą być w czwartym kwartale, aby osiągnąć cel.

Jak widać, bieżąca łączna sprzedaż wynosi 114 706 sztuk. Jeśli chcemy sprzedać 250 000 do końca roku, ile musimy sprzedać w czwartym kwartale? Poszukiwanie celów Excela powie nam.

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

  1. Kliknij Dane> Analiza „co jeśli”> Poszukiwanie celu. Zobaczysz to okno:
  2. Połóż “równa się” część twojego równania w Ustaw komórkę pole. Jest to liczba, którą Excel będzie próbował zoptymalizować. W naszym przypadku jest to suma bieżąca naszych liczb sprzedaży w komórce B5.
  3. Wpisz wartość celu w Cenić pole. Szukamy w sumie 250 000 sprzedanych jednostek, więc postawimy “250 000” w tej dziedzinie.
  4. Powiedz Excelowi, dla której zmiennej należy zastosować rozwiązanie w Zmieniając komórkę pole. Chcemy zobaczyć, jaka powinna być nasza sprzedaż w czwartym kwartale. Więc powiemy Excelowi, aby rozwiązał dla komórki D2. Będzie to wyglądać tak, gdy będzie gotowe:
  5. Trafienie dobrze rozwiązać dla twojego celu. Kiedy wygląda dobrze, po prostu naciśnij dobrze. Excel poinformuje Cię, gdy funkcja Szukanie celu znajdzie rozwiązanie.
  6. Kliknij dobrze ponownie, a zobaczysz wartość, która rozwiązuje twoje równanie w wybranej komórce Zmieniając komórkę.

W naszym przypadku rozwiązaniem jest 135 294 jednostek. Oczywiście mogliśmy właśnie to stwierdzić, odejmując sumę bieżącą od rocznego celu. Ale Goal Seek można również wykorzystać na komórce, która już zawiera dane. I to jest bardziej przydatne.

Pamiętaj, że Excel zastępuje nasze poprzednie dane. To jest dobry pomysł, aby uruchom Goal Seek na kopii swoich danych. Dobrym pomysłem jest także zanotowanie skopiowanych danych, które zostały wygenerowane przy użyciu funkcji Szukanie celu. Nie chcesz mylić tego z aktualnymi, dokładnymi danymi.

Cel jest więc użyteczną funkcją programu Excel 16 formuł programu Excel, które pomogą Ci rozwiązać problemy z prawdziwego życia 16 formuł programu Excel, które pomogą Ci rozwiązać problemy z prawdziwym życiu Właściwe narzędzie to połowa pracy. Excel może rozwiązywać obliczenia i przetwarzać dane szybciej, niż można znaleźć kalkulator. Pokazujemy kluczowe formuły Excela i pokazujemy, jak z nich korzystać. , ale to nie jest aż tak imponujące. Rzućmy okiem na narzędzie, które jest znacznie bardziej interesujące: dodatek Solver.

Co robi program Excel Solver?

Krótko mówiąc, Solver jest jak wielowymiarowa wersja celu szukania. Pobiera jedną zmienną celu i dostosowuje wiele innych zmiennych, dopóki nie uzyska żądanej odpowiedzi.

Może rozwiązać dla maksymalnej wartości liczby, minimalnej wartości liczby lub dokładnej liczby.

I działa w ramach ograniczeń, więc jeśli jednej zmiennej nie można zmienić lub może ona zmieniać się 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 korzystanie z niego nie jest proste.

Rzućmy okiem na ładowanie dodatku Solver, a następnie przejdźmy do korzystania z Solvera w programie Excel 2016.

Jak załadować dodatek Solver

Excel domyślnie nie ma Solvera. Jest to dodatek, więc podobnie jak inne zaawansowane funkcje programu Excel Power Up Excel z 10 dodatkami do przetwarzania, analizy i wizualizacji danych jak profesjonalista Power Up Excel z 10 dodatkami do przetwarzania, analizy i wizualizacji danych jak profesjonalny Excel Vanilla jest niesamowity, ale możesz go zwiększyć dzięki dodatkom. Bez względu na dane, które musisz przetworzyć, istnieje szansa, że ​​ktoś stworzył dla nich aplikację Excel. Oto wybór. , musisz go najpierw załadować. Na szczęście jest już na twoim komputerze.

Zmierzać do Plik> Opcje> Dodatki. Następnie kliknij Iść obok Zarządzaj: Dodatki do programu Excel.

Jeśli to menu mówi coś innego niż “Dodatki do programu Excel,” musisz to zmienić:

W wyświetlonym oknie zobaczysz kilka opcji. Upewnij się, że pole obok Dodatek Solver jest zaznaczone i naciśnij dobrze.

Zobaczysz teraz Solver przycisk w Analiza grupa Dane patka:

Jeśli korzystałeś już z pakietu narzędzi do analizy danych Jak wykonać podstawową analizę danych w programie Excel Jak wykonać podstawową analizę danych w programie Excel Excel nie jest przeznaczony do analizy danych, ale nadal może obsługiwać statystyki. Pokażemy Ci, jak korzystać z dodatku Data Analysis Toolpak do uruchamiania statystyk programu Excel. zobaczysz przycisk Analiza danych. Jeśli nie, Solver pojawi się sam.

Po załadowaniu dodatku przyjrzyjmy się, jak go używać.

Jak korzystać z Solvera w programie Excel

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

  1. Kliknij Dane> Solver. Zobaczysz okno Parametry Solvera poniżej. (Jeśli nie widzisz przycisku Solver, zapoznaj się z poprzednią sekcją dotyczącą ładowania dodatku Solver).
  2. Ustaw cel komórki i powiedz Excelowi swój cel. Cel znajduje się w górnej części okna Solvera i składa się z dwóch części: komórki celu i wyboru maksymalizacji, minimalizacji lub określonej wartości.

    Jeśli wybierzesz Max, Excel dostosuje zmienne, aby uzyskać jak największą liczbę w docelowej komórce. Min jest odwrotnie: Solver zminimalizuje liczbę celów. Wartość pozwala określić konkretną liczbę, której Solver ma szukać.
  3. Wybierz zmienne komórki, które Excel może zmienić. Komórki zmienne są ustawiane za pomocą Zmieniając zmienne komórki pole. Kliknij strzałkę obok pola, a następnie kliknij i przeciągnij, aby wybrać komórki, z którymi Solver powinien współpracować. Zauważ, że są wszystkie komórki to może się różnić. Jeśli nie chcesz, aby komórka się zmieniała, nie zaznaczaj jej.
  4. Ustaw ograniczenia dla wielu lub pojedynczych zmiennych. Wreszcie dochodzimy do ograniczeń. Tutaj Solver jest naprawdę potężny. Zamiast zmieniać dowolną komórkę zmienną na dowolną liczbę, można określić ograniczenia, które należy spełnić. Aby uzyskać szczegółowe informacje, zobacz sekcję dotyczącą ustawiania ograniczeń poniżej.
  5. Po umieszczeniu wszystkich tych informacji naciśnij Rozwiązać aby uzyskać odpowiedź. Excel zaktualizuje dane, aby uwzględnić nowe zmienne (dlatego zalecamy najpierw utworzenie kopii danych).

Możesz także generować raporty, które omówimy pokrótce w naszym przykładzie Solver poniżej.

Jak ustawić ograniczenia w Solver

Możesz powiedzieć Excelowi, że jedna zmienna musi być większa niż 200. Podczas wypróbowywania różnych wartości zmiennych, Excel nie przejdzie poniżej 201 dla tej konkretnej zmiennej.

Aby dodać ograniczenie, kliknij Dodaj przycisk obok listy ograniczeń. Otrzymasz nowe okno. Wybierz komórkę (lub komórki) do ograniczenia w Odniesienie do komórki pole, a następnie wybierz operatora.

Oto dostępni operatorzy:

  • <= (mniejszy lub równy)
  • = (równy)
  • => (większe bądź równe)
  • int (musi być liczbą całkowitą)
  • kosz (musi być 1 lub 0)
  • AllDifferent

AllDifferent jest trochę mylące. Określa, że ​​każda komórka w wybranym zakresie Odniesienie do komórki musi być inną liczbą. Ale określa również, że muszą one zawierać się między 1 a liczbą komórek. Więc jeśli masz trzy komórki, skończysz na liczbach 1, 2 i 3 (ale niekoniecznie w tej kolejności)

Na koniec dodaj wartość ograniczenia.

Ważne jest, aby pamiętać, że możesz wybierz wiele komórek do odwołania do komórki. Jeśli na przykład 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 także użyć pola wyboru w głównym oknie Solvera, aby upewnić się, że wszystkie wartości, dla których nie określiłeś ograniczeń, są nieujemne. Jeśli chcesz, aby zmienne były ujemne, odznacz to pole.

Przykład solvera

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

Mamy w nim pięć różnych miejsc pracy, z których każde płaci inną stawkę. Mamy również liczbę godzin, jaką teoretyczny pracownik przepracował przy każdej z tych prac w danym tygodniu. Możemy użyć dodatku Solver, aby dowiedzieć się, jak zmaksymalizować całkowitą płacę, zachowując pewne zmienne w ramach pewnych ograniczeń.

Oto ograniczenia, których użyjemy:

  • Brak pracy może spaść poniżej czterech godzin.
  • Job 2 musi być ponad osiem godzin.
  • Job 5 musi być mniej niż jedenaście godzin.
  • Łączna liczba przepracowanych godzin musi wynosić równa 40.

Pomocne może być zapisanie takich ograniczeń przed użyciem Solvera.

Oto jak skonfigurowaliśmy to w Solver:

Po pierwsze, zwróć uwagę na to Utworzyłem kopię tabeli więc nie zastępujemy oryginalnego, który zawiera nasze aktualne godziny pracy.

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

Uderzmy Rozwiązać 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.

Aby zachować nowe wartości, upewnij się Zachowaj rozwiązanie Solver jest zaznaczony i wciśnięty dobrze.

Jeśli chcesz uzyskać więcej informacji, możesz wybrać raport z prawej strony okna. Wybierz wszystkie żądane raporty, powiedz Excelowi, czy chcesz je obrysować (polecam), i naciśnij dobrze.

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

W naszym przypadku raporty nie są zbyt ekscytujące i nie ma tam zbyt wielu interesujących informacji. Ale jeśli uruchomisz bardziej skomplikowane równanie Solvera, możesz znaleźć przydatne informacje dotyczące raportowania w tych nowych arkuszach. Po prostu kliknij + przycisk z boku dowolnego raportu, aby uzyskać więcej informacji:

Zaawansowane opcje Solvera

Jeśli nie wiesz zbyt wiele na temat statystyk, możesz zignorować zaawansowane opcje Solvera i po prostu uruchomić go takim, jakim jest. Ale jeśli wykonujesz duże, złożone obliczenia, możesz przyjrzeć się im.

Najbardziej oczywista jest metoda rozwiązywania:

Możesz wybierać między GRG Nieliniowe, Simplex LP i Ewolucyjne. Excel zapewnia proste wyjaśnienie, kiedy należy użyć każdego z nich. Lepsze wyjaśnienie wymaga pewnej wiedzy na temat statystyki. Naucz się statystyki za darmo z tych 6 zasobów. Dowiedz się statystyki za darmo z tych 6 zasobów. Statystyka ma reputację przedmiotu trudnego do zrozumienia. Ale uczenie się od właściwego zasobu pomoże ci w krótkim czasie zrozumieć wyniki ankiety, raporty wyborcze i zadania w statystykach. i regresja.

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

Ponownie, jeśli nie wiesz, co to wszystko oznacza, nie martw się o to. Jeśli chcesz dowiedzieć się więcej o tym, której metody rozwiązywania użyć, w programie Engineer Excel znajduje się dobry artykuł, który ją przedstawia. Jeśli chcesz maksymalnej dokładności, ewolucyjna jest prawdopodobnie dobrym rozwiązaniem. Pamiętaj tylko, że zajmie to dużo czasu.

Poszukiwanie celu i solver: Przenoszenie programu Excel na następny poziom

Teraz, gdy znasz się na podstawach rozwiązywania nieznanych zmiennych w programie Excel, masz zupełnie nowy świat obliczeń w arkuszach kalkulacyjnych.

Poszukiwanie celu może pomóc Ci zaoszczędzić czas, przyspieszając niektóre obliczenia, a Solver dodaje ogromnej mocy do możliwości obliczeniowych Excela Jak obliczać podstawowe statystyki w Excelu: Przewodnik dla początkujących Jak obliczać podstawowe statystyki w Excelu: Przewodnik dla początkujących Microsoft Excel może robić statystyki! Możesz obliczyć wartości procentowe, średnie, odchylenie standardowe, błąd standardowy i testy T studenta. .

Chodzi tylko o to, żeby się z nimi dobrze czuć. Im więcej ich użyjesz, tym bardziej się przydadzą.

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




Jeszcze bez komentarzy

O nowoczesnej technologii, prostej i niedrogiej.
Twój przewodnik w świecie nowoczesnych technologii. Dowiedz się, jak korzystać z technologii i gadżetów, które nas otaczają każdego dnia i dowiedz się, jak odkrywać ciekawe rzeczy w Internecie.