Rozwiąż funkcję w Excelu. Włącz, użyj przypadku ze zrzutami ekranu

„Wyszukaj rozwiązanie” to dodatek do Excela, dzięki któremu możliwe jest wybranie najlepszego rozwiązania problemów w oparciu o określone ograniczenia. Funkcja umożliwia planowanie pracowników, dystrybucję kosztów czy inwestycji. Wiedząc, jak działa ta funkcja, zaoszczędzisz czas i wysiłek.

Co to jest wyszukiwanie rozwiązań

W połączeniu z różnymi innymi opcjami w Excelu istnieje jedna mniej popularna, ale niezwykle potrzebna funkcja „Wyszukaj rozwiązanie”. Pomimo tego, że znalezienie go nie jest łatwe, poznanie go i korzystanie z niego pomaga w rozwiązaniu wielu problemów. Opcja przetwarza dane i daje optymalne rozwiązanie spośród dozwolonych. W artykule opisano bezpośrednie działanie funkcji Wyszukaj rozwiązanie.

Jak włączyć funkcję „Wyszukaj rozwiązanie”

Pomimo skuteczności, omawiana opcja nie znajduje się w widocznym miejscu na pasku narzędzi lub menu kontekstowym. Większość użytkowników pracujących w programie Excel nie zdaje sobie sprawy z jego obecności. Domyślnie ta funkcja jest wyłączona, aby ją wyświetlić, wykonaj następujące czynności:

1. Otwórz „Plik”, klikając odpowiednią nazwę.
2. Kliknij sekcję „Ustawienia”.
3. Następnie wybierz podsekcję „Dodatki”. Tutaj zostaną wyświetlone wszystkie dodatki do programu, poniżej pojawi się napis „Zarządzanie”. Po prawej stronie pojawi się wyskakujące menu, w którym należy wybrać „Dodatki programu Excel”. Następnie kliknij „Idź”.
   

   

4. Na monitorze zostanie wyświetlone dodatkowe okno „Dodatki”. Zaznacz pole obok żądanej funkcji i kliknij OK.
5. Żądana funkcja pojawi się na wstążce po prawej stronie sekcji „Dane”.

O modelach

Informacje te będą niezwykle przydatne dla osób, które dopiero zapoznają się z pojęciem „modelu optymalizacji”. Przed skorzystaniem z „Szukaj rozwiązania” zaleca się zapoznanie się z materiałami na temat metod budowy modeli:

• rozważana opcja pozwoli na wskazanie najlepszej metody alokacji środków na inwestycje, załadunek lokalu, dostarczenie towaru lub inne działania, w których konieczne jest znalezienie najlepszego rozwiązania.
• „Optymalna metoda” w takiej sytuacji oznaczałaby: zwiększenie przychodów, redukcję kosztów, poprawę jakości itp.

Typowe zadania optymalizacyjne:

• Ustalenie planu produkcyjnego, podczas którego zysk ze sprzedaży wydawanych towarów będzie maksymalny.
• Wyznaczanie map transportowych, podczas których minimalizowane są koszty transportu.
• Poszukaj dystrybucji kilku maszyn do różnego rodzaju prac, tak aby obniżyć koszty produkcji.
• Określenie najkrótszego czasu na wykonanie pracy.

Ważne! Aby sformalizować zadanie, konieczne jest stworzenie modelu, który odzwierciedla główne parametry obszaru tematycznego. W programie Excel model to zestaw formuł korzystających ze zmiennych. Rozważana opcja szuka takich wskaźników, aby funkcja celu była większa (mniejsza) lub równa określonej wartości.

Etap przygotowawczy

Przed umieszczeniem funkcji na wstążce musisz zrozumieć, jak działa ta opcja. Na przykład w tabeli znajdują się informacje o sprzedaży towarów. Zadanie polega na przyznaniu rabatu na każdy przedmiot, który wyniesie 4.5 miliona rubli. Parametr jest wyświetlany wewnątrz komórki o nazwie cel. Na jego podstawie obliczane są inne parametry.

Naszym zadaniem będzie obliczenie rabatu, przez który przemnożone zostaną kwoty za sprzedaż różnych produktów. Te 2 elementy są połączone formułą napisaną w ten sposób: =D13*$G$2. Gdzie w D13 zapisana jest całkowita ilość do realizacji, a $G$2 jest adresem żądanego elementu.

Korzystanie z funkcji i jej konfigurowanie

Gdy formuła jest gotowa, musisz bezpośrednio użyć samej funkcji:

1. Musisz przejść do sekcji „Dane” i kliknąć „Wyszukaj rozwiązanie”.

2. Otworzy się „Opcje”, w którym ustawione są wymagane ustawienia. W wierszu „Optymalizuj funkcję celu:” należy określić komórkę, w której wyświetlana jest suma rabatów. Możliwe jest samodzielne przepisanie współrzędnych lub wybór z dokumentu.

3. Następnie musisz przejść do ustawień innych parametrów. W sekcji „Do:” można ustawić maksymalny i minimalny limit lub dokładną liczbę.

4. Następnie wypełnia się pole „Zmiana wartości zmiennych:”. Tutaj wprowadzane są dane żądanej komórki, która zawiera określoną wartość. Współrzędne są rejestrowane niezależnie lub klika się odpowiednią komórkę w dokumencie.

5. Następnie edytowana jest zakładka „Według ograniczeń:”, w której ustawiane są ograniczenia dotyczące stosowanych danych. Na przykład wykluczone są ułamki dziesiętne lub liczby ujemne.

6. Następnie otworzy się okno, w którym możesz dodać ograniczenia w obliczeniach. Początkowy wiersz zawiera współrzędne komórki lub całego zakresu. Zgodnie z warunkami zadania wskazane są dane żądanej komórki, w której wyświetlany jest wskaźnik rabatu. Następnie określany jest znak porównania. Jest ustawiony na „większe lub równe”, aby ostateczna wartość nie była ze znakiem minus. W tej sytuacji „Limit” ustawiony w wierszu 3 wynosi 0. Możliwe jest również ustawienie limitu za pomocą „Dodaj”. Kolejne kroki są takie same.

7. Po wykonaniu powyższych kroków ustawiony limit pojawi się w największym wierszu. Lista może być duża i będzie zależeć od złożoności obliczeń, jednak w konkretnej sytuacji wystarczy 1 warunek.

8. Dodatkowo istnieje możliwość wyboru innych zaawansowanych ustawień. W prawym dolnym rogu znajduje się opcja „Opcje”, która pozwala to zrobić.

9. W ustawieniach możesz ustawić „Dokładność ograniczenia” i „Granice rozwiązania”. W naszej sytuacji nie ma potrzeby korzystania z tych opcji.

10. Po zakończeniu ustawień uruchamia się sama funkcja – kliknij „Znajdź rozwiązanie”.

11. Po wykonaniu przez program wymaganych obliczeń i wydaniu obliczeń końcowych w wymaganych komórkach. Następnie otwiera się okno z wynikami, w którym wyniki są zapisywane/usuwane lub parametry wyszukiwania są konfigurowane zgodnie z nowymi. Gdy dane spełniają wymagania, znalezione rozwiązanie jest zapisywane. Jeśli wcześniej zaznaczysz pole „Powrót do okna dialogowego opcji wyszukiwania rozwiązań”, otworzy się okno z ustawieniami funkcji.

12. Istnieje możliwość, że obliczenia okazały się błędne lub istnieje potrzeba zmiany danych początkowych w celu uzyskania innych wskaźników. W takiej sytuacji musisz ponownie otworzyć okno ustawień i dwukrotnie sprawdzić informacje.
13. Gdy dane są dokładne, można zastosować alternatywną metodę. W tym celu należy kliknąć aktualną opcję i wybrać najbardziej odpowiednią metodę z wyświetlonej listy:

• Znajdowanie rozwiązania za pomocą uogólnionego gradientu dla problemów nieliniowych. Domyślnie ta opcja jest używana, ale możliwe jest użycie innych.
• Znajdowanie rozwiązań problemów liniowych w oparciu o metodę simplex.
• Korzystanie z wyszukiwania ewolucyjnego do wykonania zadania.

Uwaga! Gdy powyższe opcje nie poradziły sobie z zadaniem, należy ponownie sprawdzić dane w ustawieniach, ponieważ często jest to główny błąd w takich zadaniach.

14. Po otrzymaniu żądanego rabatu pozostaje go zastosować do obliczenia kwoty rabatów dla każdej pozycji. W tym celu podświetlany jest początkowy element kolumny „Kwota rabatu”, zapisywana jest formuła «=D2*$G$2» i naciśnij „Enter”. Znaki dolara są umieszczane tak, że gdy formuła zostanie rozciągnięta do sąsiednich linii, G2 się nie zmieni.

15. Kwota rabatu na pierwszy przedmiot zostanie teraz uzyskana. Następnie należy przesunąć kursor nad róg komórki, gdy pojawi się „plus”, wciśnięty LPM i rozciągnięcie formuły do ​​wymaganych linii.
16. Potem stół będzie w końcu gotowy.

Załaduj/zapisz opcje wyszukiwania

Ta opcja jest przydatna podczas stosowania różnych opcji wiązań.

1. W menu Opcje wyszukiwarki rozwiązań kliknij opcję Załaduj/Zapisz.
2. Wprowadź zakres dla obszaru modelu i kliknij Zapisz lub Wczytaj.

Podczas zapisywania modelu wprowadzane jest odniesienie do 1 komórki pustej kolumny, w której zostanie umieszczony model optymalizacji. Podczas ładowania modelu wprowadzane jest odniesienie do całego zakresu zawierającego model optymalizacji.

Ważne! Aby zapisać ostatnie ustawienia w menu Opcje rozwiązania, zostanie zapisany skoroszyt. Każdy arkusz w nim ma własne opcje dodatku Solver. Dodatkowo istnieje możliwość ustawienia więcej niż 1 zadania dla arkusza poprzez kliknięcie przycisku „Wczytaj lub Zapisz” w celu zapisania poszczególnych zadań.

Prosty przykład użycia dodatku Solver

Konieczne jest załadowanie kontenera kontenerami tak, aby jego masa była maksymalna. Zbiornik ma pojemność 32 metrów sześciennych. m. Wypełnione pudełko waży 20 kg, jego objętość wynosi 0,15 metra sześciennego. m. Pudełko – 80 kg i 0,5 cu. m. Wymagane jest, aby łączna ilość pojemników wynosiła co najmniej 110 szt. Dane są zorganizowane w następujący sposób:

Zmienne modelu są zaznaczone na zielono. Funkcja celu jest podświetlona na czerwono. Ograniczenia: według najmniejszej liczby pojemników (większej lub równej 110) i według wagi (=PROD.SUMA(B8:C8;B6:C6) – całkowita masa tara w pojemniku.

Przez analogię rozważamy całkowitą objętość: =PROD.SUMA(B7:C7;B8:C8). Taka formuła jest niezbędna do ustalenia limitu całkowitej objętości pojemników. Następnie poprzez „Szukaj rozwiązania” wprowadzane są linki do elementów ze zmiennymi, formułami oraz same wskaźniki (lub linki do konkretnych komórek). Oczywiście liczba kontenerów jest liczbą całkowitą (jest to również ograniczenie). Naciskamy „Znajdź rozwiązanie”, w wyniku czego znajdujemy taką ilość pojemników, gdy masa całkowita jest maksymalna i wszystkie ograniczenia są brane pod uwagę.

Wyszukiwanie rozwiązania nie udało się znaleźć rozwiązań

Takie powiadomienie pojawia się, gdy dana funkcja nie znalazła kombinacji wyników zmiennych, które spełniają każde ograniczenie. Podczas korzystania z metody Simplex jest całkiem możliwe, że nie ma rozwiązania.

Gdy stosuje się metodę rozwiązywania problemów nieliniowych, we wszystkich przypadkach zaczynając od początkowych wskaźników zmiennych, oznacza to, że możliwe rozwiązanie jest dalekie od takich parametrów. Jeśli uruchomisz funkcję z innymi początkowymi wskaźnikami zmiennych, prawdopodobnie istnieje rozwiązanie.

Na przykład przy zastosowaniu metody nieliniowej elementy tabeli ze zmiennymi nie zostały wypełnione, a funkcja nie znalazła rozwiązań. Nie oznacza to, że nie ma rozwiązania. Teraz, biorąc pod uwagę wyniki pewnej oceny, do elementów ze zmiennymi zbliżonymi do otrzymanych wprowadzane są inne dane.

W każdej sytuacji należy wstępnie sprawdzić model pod kątem braku konfliktu wiązań. Często wiąże się to z niewłaściwym doborem wskaźnika lub wskaźnika ograniczającego.

W powyższym przykładzie maksymalny wskaźnik objętości wynosi 16 metrów sześciennych. m zamiast 32, ponieważ takie ograniczenie jest sprzeczne ze wskaźnikami minimalnej liczby miejsc, ponieważ będzie odpowiadać liczbie 16,5 metrów sześciennych. m.

Wnioski

Na tej podstawie opcja „Wyszukaj rozwiązanie” w programie Excel pomoże w rozwiązaniu konkretnych problemów, które są raczej trudne lub niemożliwe do rozwiązania w zwykły sposób. Trudność w zastosowaniu tej metody polega na tym, że początkowo ta opcja jest ukryta, dlatego większość użytkowników nie jest świadoma jej obecności. Ponadto funkcja jest dość trudna do nauczenia się i obsługi, ale przy odpowiednim zbadaniu przyniesie ogromne korzyści i ułatwi obliczenia.