Sformułowanie problemu

Załóżmy, że firma, w której pracujesz, ma dwa magazyny, z których towary trafiają do pięciu twoich sklepów rozsianych po całej Moskwie.

Każdy sklep jest w stanie sprzedać określoną ilość znanego nam towaru. Każdy z magazynów ma ograniczoną pojemność. Zadanie polega na racjonalnym wyborze, z którego magazynu, do którego sklepów należy dostarczyć towar, aby zminimalizować całkowite koszty transportu.

Przed rozpoczęciem optymalizacji konieczne będzie skompilowanie prostej tabeli na arkusz excel– nasz model matematyczny opisujący sytuację:

Rozumie się to jako:

• Szara tabela (B3:G5) opisuje koszt wysyłki jednostki z każdego magazynu do każdego sklepu.
• Fioletowe komórki (C14:G14) opisują ilość towarów potrzebną do sprzedaży w każdym sklepie.
• Czerwone komórki (J10:J11) pokazują pojemność każdego magazynu - maksymalną ilość towaru, jaką magazyn może pomieścić.
• Żółte (C12:G12) i niebieskie (H10:H11) komórki to odpowiednio sumy wierszy i kolumn dla komórek zielonych.
• Całkowity koszt wysyłki (E17) jest obliczany jako suma produktów z liczby produktów i odpowiadających im kosztów wysyłki.

Tym samym nasze zadanie sprowadza się do doboru optymalnych wartości zielonych komórek. Ponadto, aby łączna kwota dla linii (niebieskie komórki) nie przekraczała pojemności magazynu (czerwone komórki), a jednocześnie każdy sklep otrzymał taką ilość towaru, jaką musi sprzedać (kwota przypadająca na każdy sklep w żółte komórki powinny być jak najbardziej zbliżone do wymagań - fioletowe komórki).

Rozwiązanie

W matematyce takie problemy wyboru optymalnego rozmieszczenia zasobów były formułowane i opisywane od dawna. I oczywiście od dawna opracowano sposoby ich rozwiązania. Excel udostępnia użytkownikowi jeden z nich - za pomocą potężnego dodatku Znalezienie rozwiązania(rozwiązywanie), dostępny w programie Excel 2003 za pośrednictwem menu Usługa(narzędzia) lub z zakładki Dane w nowszych wersjach programu Excel.

Jeśli menu Usługa lub na karcie Dane Twój Excel nie ma takiej komendy - wszystko w porządku - oznacza to, że dodatek po prostu nie jest jeszcze podłączony. Aby go podłączyć:

• w Excelu 2003 i nowszych - otwórz menu Usługa — dodatki(Narzędzia – Dodać- Ins) , w wyświetlonym oknie zaznacz pole Znalezienie rozwiązania(Solver) i naciśnij OK. Excel aktywuje wybrany dodatek i w menu Usługa (Narzędzia) pojawi się nowe polecenie - Znalezienie rozwiązania (Solver) .
• w Excel 2007 i nowszych - kliknij przycisk Gabinet, a następnie wybierz Opcjeprzewyższać – dodatki – Iść(Opcje Excela - Dodaj Ins - Idź do) .

Uruchommy dodatek. Otworzy się następujące okno:

W tym oknie musisz ustawić następujące ustawienia:

Oprócz oczywistych ograniczeń związanych z czynnikami fizycznymi (pojemność magazynów i środków transportu, limity budżetowe i czasowe itp.), czasami konieczne jest dodanie ograniczeń „specjalnych do Excela”. W naszym przypadku, na przykład, będziemy musieli dodać następujące ograniczenie:

Dodatkowo wyjaśni, że objętość przewożonych towarów (zielonych komórek) nie może być ujemna - dla osoby jest to oczywiste, ale dla komputera musi to być napisane wprost.

Po ustawieniu wszystkich niezbędnych parametrów okno powinno wyglądać tak:

Teraz, gdy dane do obliczeń są wprowadzone, naciśnij przycisk Biegać(rozwiązywać) aby rozpocząć optymalizację. W ciężkich przypadkach z dużą ilością zmieniających się komórek i ograniczeń znalezienie rozwiązania może zająć dużo czasu, ale nasze zadanie dla Excela nie będzie problemem - za kilka chwil otrzymamy następujące wyniki:

Zwróć uwagę na to, jak ciekawie rozłożyły się wielkości dostaw między sklepy, nie przekraczając jednocześnie pojemności naszych magazynów i zaspokajając wszystkie prośby o wymaganą liczbę towarów dla każdego sklepu.

Jeśli znalezione rozwiązanie nam odpowiada, możemy je zapisać, lub cofnąć się do pierwotnych wartości​​i spróbować ponownie z innymi parametrami. Możesz również zapisać wybraną kombinację parametrów jako Scenariusz. Na życzenie użytkownika Excel może zbudować trzy typy Raporty o rozwiązywanym problemie na osobnych arkuszach: raport z wyników, raport o matematycznej stabilności rozwiązania oraz raport o granicach (restrykcji) rozwiązania, jednak w większości przypadków interesują je tylko specjaliści .

Są jednak sytuacje, w których Excel nie może znaleźć odpowiednie rozwiązanie. Można zasymulować taki przypadek, jeśli w naszym przykładzie wskażemy wymagania sklepów w ilości większej niż łączna pojemność magazynów. Następnie podczas przeprowadzania optymalizacji program Excel spróbuje zbliżyć się do rozwiązania jak najbliżej, a następnie wyświetli komunikat, że nie można znaleźć rozwiązania. Jednak nawet w tym przypadku mamy masę przydatna informacja– w szczególności widzimy „słabe ogniwa” naszych procesów biznesowych i rozumiemy obszary wymagające poprawy.

Rozważany przykład jest oczywiście stosunkowo prosty, ale można go łatwo skalować do rozwiązywania znacznie bardziej złożonych problemów nieliniowych. Na przykład:

• Optymalizacja dystrybucji środków finansowych według pozycji wydatków w biznesplanie lub budżecie projektu. Ograniczeniem w tym przypadku będzie kwota finansowania i termin realizacji projektu, a celem optymalizacji jest maksymalizacja zysków i minimalizacja kosztów projektu.
• Optymalizacja harmonogramów pracowników w celu zminimalizowania funduszu wynagrodzenie przedsiębiorstw. Ograniczeniami w tym przypadku będą życzenia każdego pracownika zgodnie z harmonogramem zatrudnienia i wymaganiami tabeli kadrowej.
• Optymalizacja inwestycji inwestycyjnych - konieczność prawidłowego podziału środków pomiędzy kilka banków, papierów wartościowych lub akcji przedsiębiorstw, aby ponownie maksymalizować zyski lub (jeśli jest to ważniejsze) minimalizować ryzyko.

W każdym razie dodatek Znalezienie rozwiązania(rozwiązywanie) to bardzo potężne i piękne narzędzie Excela i zasługuje na Twoją uwagę, ponieważ może pomóc w wielu trudnych sytuacjach, z którymi musisz się zmierzyć we współczesnym biznesie.

Świat zmienia się w zawrotnym tempie, czy nam się to podoba, czy nie. Ta prawda jest szczególnie dobrze znana użytkownikom komputerów PC. W sumie oprogramowanie zmienia się i jest aktualizowany z niesamowitą częstotliwością. Na szczęście pakiety biurowe niewiele z tego powodu cierpią, ale są pewne wyjątki. Jakie jest znaczenie pakietów programy biurowe? Każdy aplikacja biurowa to poręczne narzędzie przeznaczone do pracy z bazami danych. Liczba elementów pomocniczych w tym systemie jest coraz większa.

Dzięki narzędziom do wizualizacji, które pojawiły się dopiero w nowych wersjach takich programów, praca stała się znacznie łatwiejsza. Dzięki obecności nowego filtra wyszukiwania prace zostały znacznie przyspieszone. A sam Microsoft Excel 2010 jest szybszy. Wydaje się jednak, że całkiem niedawno pracownicy biurowi opanowali mądrość pracy z Office 2007. Ale nagle odbyła się prezentacja Office 2010, która tylko przysporzyła jeszcze więcej kłopotów niefortunnym użytkownikom. Przykładem jest „wyszukiwanie rozwiązania” w programie Microsoft Excel 2010.

Ten dodatek jest nie tylko przydatny, ale także umożliwia znacznie wydajniejszą pracę z edytorem arkuszy kalkulacyjnych, umożliwiając tym samym podejmowanie decyzji duża ilość wymagające zadania. Jest to szczególnie istotne z punktu widzenia optymalizacji, która jest obecnie istotna dla wielu firm. Ale dlaczego dokładnie Microsoft Excel 2010? Jeśli mówimy konkretnie o tej wersji Excela, to przeszła ona znaczące zmiany. Poprawiono więc na przykład dużą liczbę błędów w formułach, przez co błędy w obliczeniach dość często występowały w poprzednich wersjach programu. Ale najmniejszy błąd w obliczeniach może czasami prowadzić do raczej nieprzyjemnych konsekwencji.

Korzystając z interfejsu wstążkowego, który ma możliwość wyświetlenia podglądu wykresów i wykresów przed wstawieniem ich do tabeli, użytkownik może łatwiej przygotowywać złożone profesjonalne dokumenty. Również zawarte w Nowa wersja Redaktor wprowadził takie nowe typy formuł, które mogą być bardzo przydatne dla ekonomistów i księgowych. Ta okoliczność podkreśla koncentrację Microsoftu na użytkownikach korporacyjnych. Biorąc pod uwagę, że wszystkie opisane poniżej sytuacje są dla nich typowe, nie ma w tym nic dziwnego.

Jeśli dodatek Solver nie był przez Ciebie używany, możesz go zainstalować osobno. Jak to zainstalować. Robi się to dość łatwo. Jeśli używasz edytora arkuszy kalkulacyjnych Excel 2003 i starszych, aby wykonać tę czynność, musisz przejść do elementu „Narzędzia” i wybrać tam element „Dodatki”. A gdzie szukać „poszukiwania rozwiązań”, jeśli mówimy o bardziej nowoczesnej wersji? Jeśli korzystasz z programu Excel 2007, możesz znaleźć przycisk „Wyszukaj rozwiązanie” w zakładce „Dane”. Jak z nim pracować? Być może wszystkie te wyjaśnienia mogą wydawać się nieco długie, ale ten dodatek działa całkiem logicznie. Nie musisz być geniuszem komputerowym, aby to opanować. Aby w pełni zrozumieć zasadę jego użycia, rozważ najprostszy przykład.

Jak działa „wyszukiwanie rozwiązania” w programie Excel 2010?

Przykład: otrzymujesz zadanie dystrybucji premii w organizacji. Aby uprościć rozwiązanie, załóżmy, że musisz rozdzielić premię na wszystkich pracowników oddziału. Budżet premium to 100 000 rubli. Możesz rozdzielić premię proporcjonalnie do wysokości wynagrodzenia każdego pracownika. Od czego zacząć pracę? Przede wszystkim należy opracować tabelę, wprowadzić do niej wszystkie niezbędne informacje i wyrażenia formuł. Jako wynik będzie brana pod uwagę łączna wartość kwoty premii. Warto wziąć pod uwagę, że komórka docelowa (np. C8) jest powiązana z sekcją, która zostanie zmieniona (np. E2).

W zakresie C2-C7 mogą znajdować się dodatkowe formuły, za pomocą których można obliczyć wysokość wypłaty premii dla każdego pracownika. Następnie musisz uruchomić dodatek „wyszukaj rozwiązanie”. Następnie wymagane wartości są ustawiane w oknie, które się otworzy. Szczególną uwagę należy zwrócić na fakt, że wygląd zewnętrzny okna mogą się znacznie różnić w różne wersje apartament biurowy. Więc w tej sytuacji będziesz musiał sam sobie z tym poradzić. Ale nie ma kardynalnych różnic, więc badanie nie zajmie dużo czasu.

Jakie opcje znajdują się w oknie dialogowym?

Aby ułatwić sobie pracę, należy zdawać sobie sprawę z tych wartości, które na ogół istnieją w określonym zakresie roboczym. Przede wszystkim komórka docelowa. Zauważ, że w przeciwieństwie do innych operacji, które mogą używać wielu pól wejściowych, tylko jedno może być tutaj obecne. Ponadto należy pamiętać, że może istnieć kilka opcji optymalizacji. Szczególną uwagę należy zwrócić na minimalną i maksymalną możliwą wartość końcową. Zwróć także uwagę na konkretny wynik. Jeśli potrzebujesz ostatnia opcja, preferowany wynik musi być dokładnie określony w polu wejściowym. Należy również pamiętać, że zarówno poszczególne pola, jak i zakres mogą pełnić rolę zmiennych komórek. To dla zakresu program przyjmuje wartość końcową porównując ją z danymi oryginalnymi.

Jak dodawane są ograniczenia?

Jeśli chcesz dodać pewne ograniczenia do programu, musisz użyć przycisku „Dodaj”. Tutaj ważne jest, aby wziąć pod uwagę następujący punkt: ustawiając takie wartości, musisz być bardzo ostrożny. Ponieważ dodatek Excela „szukaj rozwiązania” jest używany w dość odpowiedzialnych operacjach, ważne jest, aby w rezultacie uzyskać najbardziej poprawne wartości. Same wyniki będą zależeć od ograniczeń. Możesz ustawić limity dla poszczególnych komórek, a także dla całych zakresów.

Jakie warianty formuł i znaków można w tym przypadku zastosować? Można używać następujących znaków: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».

Gdzie znajdę tę opcję w starszych wersjach programu? Jeśli korzystasz z programu Excel 2007 lub nowszego, dostęp do tej opcji można uzyskać, klikając przycisk „Opcje”. Tutaj będziesz mógł zobaczyć pozycję "Opcje rozwiązań wyszukiwania".

Znalezienie wyniku końcowego

Aby wyszukać gotowe rozwiązanie, musisz kliknąć przycisk „Uruchom”. W rezultacie pojawi się okno dialogowe "Solution Results". Jeśli ostateczna odpowiedź Ci odpowiada, wystarczy kliknąć przycisk „OK”. W rezultacie odpowiedź, którą lubisz, zostanie zapisana w tabeli. W przypadku, gdy otrzymana wartość różni się z Twojego punktu widzenia, musisz kliknąć przycisk „Anuluj”. Tabela w końcu wróci do swojej pierwotnej wartości, możesz dalej szukać optymalnego rozwiązania. Jeśli zmieniłeś oryginalne dane, to rozwiązanie będzie musiało zostać wykonane ponownie.

Gdzie można użyć dodatku „wyszukaj rozwiązanie” w programie Excel?

Rozważ inny przykład - minimalizację kosztów. Jak wspomniano powyżej, funkcję tę można wykorzystać do optymalizacji procesów produkcyjnych. Zobaczmy, jak można obniżyć koszty firmy zajmującej się budownictwem niskim. Załóżmy, że mamy samą organizację i trzech dostawców, którzy dostarczają materiały budowlane. Koszty budowy zostaną wliczone w koszt obiektu, więc w interesie firmy jest wybór dostawcy, którego praca będzie kosztować mniej.

Jakie informacje należy wpisać w polu „szukaj rozwiązania” w MS Excel? Konieczne jest wskazanie kosztu materiałów budowlanych, zapotrzebowania na nie na placu budowy oraz kosztu transportu materiałów budowlanych. Musisz wziąć pod uwagę każdą parę „Dostawca-kupujący”. Komórka docelowa musi zawierać sumę wszystkich kosztów transportu. Jeśli wszystko zostanie wykonane poprawnie, funkcja „szukaj rozwiązania” da możliwość stworzenia najbardziej opłacalnej strategii, która przyniesie najwyższy możliwy dochód.

Znajdź rozwiązania w programie Excel 2007 nie jest standardowym dodatkiem. Jest to konieczne do skomplikowanych obliczeń w obecności więcej niż jednej nieznanej. W związku z tym nie jest zawarty w zwykłym zestawie opcji programu. Jednak gdy zajdzie taka potrzeba, dodatek oferuje użytkownikowi wydajną pracę, a także wysoką produktywność.

Co to jest wyszukiwanie rozwiązań?

To jest dodatek do programu. W zwykłej konfiguracji produkowanej przez producenta ten pakiet nie jest dostarczany. Musi być pobrany i skonfigurowany osobno. Często użytkownicy obchodzą się bez niego. Ponadto dodatek jest często określany jako „Solver”, ponieważ wykonuje dokładne i szybkie obliczenia, bez względu na to, jak trudne jest zadanie. W przypadku oryginalnej wersji pakietu Microsoft Office nie ma problemów z instalacją. Użytkownik musi wykonać następujące przejścia: Opcje - Narzędzia - Dodatki - Zarządzaj - Dodatki programu Excel. W rezultacie na ekranie pojawi się okno, w którym będzie obecny przycisk przejścia. Jeśli go klikniesz, pojawi się lista wszystkich dodatków oferowanych użytkownikowi, zarówno zainstalowanych, jak i nieodebranych. Następnie musisz znaleźć „Wyszukaj rozwiązania”, a następnie ustawić obok niego znacznik. Narzędzie stanie się aktywne i będzie można z niego korzystać w dowolnym momencie.

Do czego służy Solver? Dlaczego warto korzystać z Find Solutions w programie Excel 2007 i czy trzeba go zainstalować? Jeśli użytkownik ma funkcję celu, która zależy od kilku parametrów, dodatek wybierze rozwiązania problemu, odpowiadające danym źródłowym. Może to być zmienna, nieznana lub, powiedzmy, wartość końcowa. Innymi słowy, użytkownik będzie miał początkowe cechy, a także odpowiedź. Jeśli chodzi o sam program, wybierze przebieg rozwiązania, poda formułę. Warto zauważyć, że korzystając z dodatku można znaleźć:

Udana dystrybucja zasobów roboczych w celu osiągnięcia maksymalnego zysku, ponieważ działa firma lub jej osobny dział, oddział;
dystrybucja inwestycji w warunkach zminimalizowanego ryzyka;
rozwiązywanie problemów, w których jest więcej niż jedna niewiadoma;
zapisanie i wczytanie modelu rozwiązania to najlepsza opcja dla pracowników, którzy zmuszeni są do ciągłej zmiany komputera lub laptopa;
rozwiązywanie kilku problemów jednocześnie z różnymi zmiennymi, niewiadomymi, wzorami i całkami.

W ten sposób program otwiera ogromne możliwości, ale musisz nauczyć się z niego poprawnie korzystać.

Jak działa solver? Oprócz Solvera Excel posiada taką funkcję jak wybór parametrów. Należy go używać, gdy istnieje tylko jedna nieznana wartość. Ta funkcja wymaga mniej zasobów, więc wynik jest publikowany szybciej. Znajdowanie rozwiązań w Excelu 2007 służy do problemów o największej złożoności, w których występuje kilka niewiadomych, często obserwuje się zmienne. Tak więc wszystko można przedstawić w następujący sposób: Znajdź niewiadome - kilka "x". Pod warunkiem, że jest to formuła lub funkcja. Z ograniczeniami w tym przypadku zwykle wskazuje się nierówność lub wartości minimalne / maksymalne. Dodatkowo należy wskazać komórki, za pomocą których chcemy wykonać obliczenia. Możliwe jest rozwiązywanie kilku różnych zadań na raz, pod warunkiem ustawienia w programie odpowiednich parametrów.

Ustawianie opcji dodatku Solver Aby dodatek Solver programu Excel 2007 działał poprawnie, należy wprowadzić prawidłowe parametry. Z reguły ograniczają się one do kilku (1-3) cech, jednak przy zadaniach o większej złożoności potrzebne jest ustawienie globalne. Opcje w Znajdź rozwiązania w programie Office Excel 2007 są następujące:

1. Maksymalny czas - liczba sekund przeznaczana przez użytkownika na rozwiązanie programu, zależy od stopnia złożoności zadania.
2. Maksymalna liczba integracji. W tym przypadku mówimy o liczbie ruchów wykonywanych przez program podczas rozwiązywania problemu. Gdy parametr zostanie zwiększony, odpowiedź nie dociera.
3. Błąd lub dokładność, często używane w trakcie rozwiązywania ułamków dziesiętnych (na przykład do 0,0001).
4. Tolerancja. Jest używany w procesie pracy z procentami.
5. Wartości nieujemne. Używane przy rozwiązywaniu funkcji, która ma dwie poprawne odpowiedzi (powiedzmy +/-X).
6. Wyświetlanie wyników integracji. To ustawienie jest zapewniane, gdy ważny jest zarówno wynik decyzji, jak i ich przebieg.
7. Metoda przeszukiwania - dobór algorytmu optymalizacji. Z reguły stosuje się „metodę Newtona”. Po wybraniu wszystkich ustawień należy kliknąć przycisk Zapisz.

Parametry zadania w funkcji „Szukaj rozwiązań”

Praca tego dodatku jest wykonywana zgodnie z określoną charakterystyką obliczeń. Najważniejszą z nich jest metoda. Istnieją dwie opcje. Domyślnym ustawieniem jest „metoda Newtona”. Działa z większą ilością pamięci, ale mniejszą liczbą integracji. Dzięki temu nadaje się do standardowych i prostych równań. Ponadto istnieje „metoda gradientu sprzężonego”. W takim przypadku wymagana jest mniejsza ilość pamięci, ale potrzeba więcej integracji. Tak więc, jeśli jest używany, możliwe jest rozwiązywanie nawet najbardziej złożonych równań, stosowanie wzorów i funkcji skalujących.

Formuła w Excelu

Warto zwrócić uwagę na obowiązkowy element, bez którego dodatek „Wyszukaj rozwiązania” w Excelu 2007 nie może działać.W tym przypadku mówimy o formułach. Są wyrażeniem wykonującym określone obliczenia. Formuły nie mogą działać bez równości. W związku z tym program nie jest w stanie rozpoznać jednego, jeśli nie ma odpowiadającego znaku. Formuła składa się z następujących składników:

1. Funkcja. Standardowa formuła, w której istnieje pewna i określona sekwencja działań, nie może zostać zmieniona.
2. Link. Wskazuje liczbę komórek do rozwiązania. Jednocześnie komórki można umieszczać losowo lub w określonej kolejności.
3. Operator. Reprezentuje symbol określający typ obliczenia (+ - dodawanie, * - mnożenie itp.).
4. Stała. Jest stałą wartością, która zawsze pozostaje taka sama. Nie musisz wykonywać żadnych obliczeń, aby to uzyskać.

Formuły są rozwiązywane od lewej do prawej, ważne jest przestrzeganie wszystkich zasad matematycznych.

Utwórz formułę

Wzory to równania potrzebne do wykonania obliczeń programu. Gdy nie zostaną wprowadzone, dodatek Excel Solver nie będzie działać. Zadania również nie zostaną rozwiązane. Dlatego do poprawnej pracy musisz poprawnie wprowadzić formułę. Obliczenie zaczyna się od równości. Jeśli komórka zawiera „=ROOT(numer komórki)”, używana jest odpowiednia funkcja. Gdy główna formuła jest drukowana ze znakiem „=”, konieczne jest wskazanie danych, z którymi oddziałuje. Użyj funkcji wyszukiwania, aby znaleźć wymagane informacje.

Wyszukiwarka rozwiązań to dodatek do programu Microsoft Excel, którego można użyć do znalezienia najlepszego rozwiązania problemu, biorąc pod uwagę ograniczenia określone przez użytkownika.

Rozważymy wyszukiwanie rozwiązania w (ten dodatek przeszedł pewne zmiany w porównaniu z poprzednią wersją w .
W tym artykule przyjrzymy się:

• tworzenie modelu optymalizacyjnego na arkuszu MS EXCEL
• ustawienie Znalezienie rozwiązania;
• prosty przykład (model liniowy).

Instalowanie dodatku Solver

Zespół Znalezienie rozwiązania jest w grupie Analiza patka Dane.

Jeśli zespół Znalezienie rozwiązania w grupie Analiza nie jest dostępna, musisz włączyć dodatek o tej samej nazwie.
Dla tego:

• Na karcie Plik wybierz drużynę Opcje a potem kategoria dodatki;
• W terenie Kontrola Wybierz wartość Dodatki do Excela i naciśnij przycisk Skok;
• W terenie Dostępne dodatki zaznacz pole obok Znalezienie rozwiązania i kliknij OK.

Notatka. Okno dodatki dostępne również w zakładce Deweloper. Jak włączyć tę kartę.

Po naciśnięciu przycisku Znalezienie rozwiązania w grupie Analiza, otworzy się jego okno dialogowe .

Przy częstym użytkowaniu Znalezienie rozwiązania wygodniej jest uruchomić go z paska narzędzi szybkiego dostępu, a nie z karty Dane. Aby umieścić przycisk na Panelu, kliknij go prawym przyciskiem myszy i wybierz Dodaj do paska narzędzi szybkiego dostępu.

O modelach

Ta sekcja jest przeznaczona dla tych, którzy dopiero zapoznają się z koncepcją modelu optymalizacyjnego.

Rada. Przed użyciem Znalezienie rozwiązania Zdecydowanie zalecamy zapoznanie się z literaturą dotyczącą rozwiązywania problemów optymalizacji i budowania modeli.

Poniżej mały program edukacyjny na ten temat.

nadbudowa Znalezienie rozwiązania pomaga określić Najlepszym sposobem robić coś:

• „Coś” może obejmować alokację pieniędzy na inwestycje, załadunek magazynu, dostarczenie towaru lub inną działalność merytoryczną, gdzie wymagane jest optymalne rozwiązanie.
• „Najlepszy sposób” lub optymalne rozwiązanie w tym przypadku oznacza: maksymalizację zysków, minimalizację kosztów, osiągnięcie najlepszej jakości itp.

Oto kilka typowych przykładów problemów z optymalizacją:

• Określ, przy którym dochód ze sprzedaży wytworzonych produktów jest maksymalny;
• Określ, przy którym całkowity koszt transportu byłby minimalny;
• Znajdź tak, aby całkowity koszt produkcji był minimalny;
• Określ minimalny czas na zakończenie wszystkich działań projektowych (ścieżka krytyczna).

Aby sformalizować zadanie, wymagane jest stworzenie modelu, który odzwierciedlałby istotne cechy obszaru tematycznego (i nie zawierałby drobnych szczegółów). Zwróć uwagę, że model jest optymalizowany Szukasz rozwiązania tylko jeden wskaźnik(ta zoptymalizowana metryka nazywa się funkcja celu).
W programie MS EXCEL model jest zbiorem połączonych ze sobą formuł, które wykorzystują zmienne jako argumenty. Zazwyczaj te zmienne mogą przyjmować tylko prawidłowe wartości, z zastrzeżeniem ograniczeń określonych przez użytkownika.
Znalezienie rozwiązania dobiera takie wartości tych zmiennych (z uwzględnieniem podanych ograniczeń), aby funkcja celu była maksymalna (minimalna) lub równa podanej wartości liczbowej.

Notatka. W najprostszym przypadku model można opisać za pomocą jednego wzoru. Niektóre z tych modeli można zoptymalizować za pomocą . Przed pierwszym spotkaniem Szukasz rozwiązania warto najpierw szczegółowo zająć się powiązanym instrumentem.
Główne różnice Wybór parametrów z Znalezienie rozwiązania:

• Wybór parametrów działa tylko z modelami z jedną zmienną;
• nie można ustawić w nim ograniczeń dla zmiennych;
• określa się nie maksimum lub minimum funkcji celu, ale jej równość z określoną wartością;
• działa skutecznie tylko w przypadku modeli liniowych, w przypadku nieliniowym znajduje optimum lokalne (najbliższe wartości początkowej zmiennej).

Przygotowanie modelu optymalizacyjnego w MS EXCEL

Znalezienie rozwiązania optymalizuje wartość funkcji celu. Funkcja celu to formuła zwracająca pojedynczą wartość w komórce. Wynik formuły musi zależeć od zmiennych modelu (niekoniecznie bezpośrednio, jest to możliwe poprzez wynik obliczenia innych formuł).
Ograniczenia modelu mogą być nałożone zarówno na zakres zmienności samych zmiennych, jak i na wyniki obliczeń innych formuł modelu, które zależą od tych zmiennych.
Wszystkie komórki zawierające zmienne modelu i ograniczenia muszą znajdować się tylko w jednym arkuszu skoroszytu. Wprowadzanie parametrów w oknie dialogowym Znalezienie rozwiązania możliwe tylko z tego arkusza.
Na tym arkuszu musi również znajdować się funkcja celu (komórka). Jednak obliczenia pośrednie (wzory) można umieścić na innych arkuszach.

Rada. Uporządkuj dane modelu tak, aby tylko jeden model znajdował się na jednym arkuszu MS EXCEL. W przeciwnym razie, aby wykonać obliczenia, będziesz musiał stale zapisywać i ładować ustawienia. Znalezienie rozwiązania(patrz poniżej).

Przedstawiamy algorytm pracy z Szukasz rozwiązania, który jest zalecany przez samych programistów (www.solver.com):

• Zdefiniuj komórki ze zmiennymi modelu (zmienne decyzyjne);
• Utwórz formułę w komórce, która obliczy funkcję celu Twojego modelu (funkcja celu);
• Twórz formuły w komórkach, które obliczą wartości do porównania z limitami (lewa strona wyrażenia);
• Korzystanie z okna dialogowego Znalezienie rozwiązania wprowadzić linki do komórek zawierających zmienne, do funkcji celu, do formuł ograniczeń i samych wartości ograniczeń;
• Biegać Znalezienie rozwiązania znaleźć optymalne rozwiązanie.

Zróbmy wszystkie te kroki na prostym przykładzie.

Prosty przypadek użycia Znalezienie rozwiązania

Konieczne jest załadowanie kontenera towarem tak, aby waga kontenera była maksymalna. Kontener ma objętość 32 metrów sześciennych. Towary pakowane są w pudełka i skrzynie. Każda skrzynia z towarem waży 20 kg, jej objętość wynosi 0,15 m3. Pudełko - odpowiednio 80kg i 0,5m3. Konieczne jest, aby łączna liczba pojemników nie była mniejsza niż 110 sztuk.

Dane modelu organizujemy w następujący sposób (patrz przykładowy plik).

Zmienne modelu (liczba każdego typu kontenera) są podświetlone na zielono.
Funkcja celu (całkowita waga wszystkich pudeł i skrzynek) jest zaznaczona na czerwono.
Ograniczenia modelu: minimalną liczbą pojemników (>=110) i całkowitą objętością (<=32) – синим.
Funkcja celu jest obliczana ze wzoru =PROD.SUMA(B8:C8;B6:C6) to całkowita waga wszystkich pudeł i skrzynek załadowanych do kontenera.
Podobnie obliczamy całkowitą objętość - =PROD.SUMA(B7:C7;B8:C8). Ta formuła jest potrzebna do ustalenia limitu całkowitej objętości pudełek i pudełek (<=32).
Ponadto, aby ustawić ograniczenie modelu, obliczamy całkowitą liczbę kontenerów = SUMA (B8: C8) .
Teraz z oknem dialogowym Znalezienie rozwiązania wprowadźmy odwołania do komórek zawierających zmienne, funkcję celu, formuły ograniczeń oraz same wartości ograniczeń (lub linki do odpowiednich komórek).
Oczywiste jest, że liczba pudełek i pudełek musi być liczbą całkowitą - to kolejne ograniczenie modelu.

Po naciśnięciu przycisku Znaleźć rozwiązanie zostanie znaleziona taka liczba pudełek i pudełek, przy której ich łączna waga (funkcja celu) jest maksymalna, a jednocześnie spełnione są wszystkie określone ograniczenia.

Streszczenie

W rzeczywistości głównym problemem w rozwiązywaniu problemów optymalizacyjnych przy użyciu Znalezienie rozwiązania bynajmniej nie subtelności tworzenia tego narzędzia analitycznego, ale poprawność zbudowania modelu, który jest adekwatny do zadania. Dlatego w innych artykułach skupimy się na budowaniu modeli, ponieważ „zakrzywiony” model jest często powodem niemożności znalezienia rozwiązania za pomocą Znalezienie rozwiązania.
Często łatwiej przyjrzeć się kilku typowym zadaniom, znaleźć wśród nich podobne, a następnie dostosować ten model do swojego zadania.
Rozwiązywanie klasycznych problemów optymalizacyjnych za pomocą Znalezienie rozwiązania uważane .

Solver nie mógł znaleźć wykonalnego rozwiązania

Ten komunikat pojawia się, gdy Znalezienie rozwiązania nie udało się znaleźć kombinacji wartości zmiennych, które jednocześnie spełniają wszystkie ograniczenia.
Jeśli używasz Metoda simpleksowa do rozwiązywania problemów liniowych, wtedy możesz być pewien, że rozwiązanie tak naprawdę nie istnieje.
Jeśli używasz nieliniowej metody rozwiązywania problemów, która zawsze zaczyna się od początkowych wartości zmiennych, może to również oznaczać, że możliwe rozwiązanie jest dalekie od tych początkowych wartości. Jeśli uciekniesz Znalezienie rozwiązania przy innych początkowych wartościach zmiennych, być może zostanie znalezione rozwiązanie.
Wyobraź sobie, że przy rozwiązywaniu problemu metodą nieliniową komórki ze zmiennymi pozostały puste (tj. początkowe wartości wynoszą 0), a Znalezienie rozwiązania nie znalazłem rozwiązania. Nie oznacza to, że tak naprawdę nie ma rozwiązania (chociaż może być). Teraz, na podstawie wyników pewnej oceny eksperckiej, wprowadźmy do komórek ze zmiennymi kolejny zestaw wartości, który Twoim zdaniem jest zbliżony do optymalnego (pożądanego). W tym przypadku, Znalezienie rozwiązania może znaleźć rozwiązanie (jeśli naprawdę istnieje).

Notatka. O wpływie nieliniowości modelu na wyniki obliczeń można przeczytać w ostatniej części artykułu.

W każdym przypadku (liniowym lub nieliniowym) należy najpierw przeanalizować model pod kątem ograniczeń spójności, czyli warunków, które nie mogą być spełnione jednocześnie. Najczęściej jest to spowodowane niewłaściwym doborem proporcji (np.<= вместо >=) lub wartość graniczna.
Jeżeli na przykład w omawianym powyżej przykładzie wartość maksymalnej kubatury jest ustawiona na 16 m3 zamiast na 32 m3, to ograniczenie to będzie sprzeczne z ograniczeniem minimalnej liczby miejsc (110), ponieważ minimalna liczba miejsc odpowiada kubaturze równej 16,5 m3 (110 * 0,15, gdzie 0,15 to objętość skrzyni, czyli najmniejszego kontenera). Ustawiając maksymalny limit objętości na 16 m3, Znalezienie rozwiązania nie znajdzie rozwiązania.

Z limitem 17 m3 Znalezienie rozwiązania znajdzie rozwiązanie.

Niektóre ustawienia Znalezienie rozwiązania

Metoda rozwiązania
Rozważany powyżej model jest liniowy, tj. funkcja celu (M to całkowita waga, która może być maksymalna) jest wyrażona następującym równaniem M=a1*x1+a2*x2, gdzie x1 i x2 to zmienne modelu (liczba pudełek i pudełek), a1 i a2 są ich wagi. W modelu liniowym ograniczenia muszą być również liniowymi funkcjami zmiennych. W naszym przypadku ograniczenie objętości V=b1*x1+b2*x2 jest również wyrażone jako zależność liniowa. Oczywiście inne ograniczenie – maksymalna liczba kontenerów (n) – również jest liniowe x1+x2 Problemy liniowe są zwykle rozwiązywane przy użyciu metody Simplex. Wybierając tę ​​metodę rozwiązania w oknie Znalezienie rozwiązania można również przetestować sam model pod kątem liniowości. W przypadku modelu nieliniowego otrzymasz następujący komunikat:

W takim przypadku konieczne jest wybranie metody rozwiązania problemu nieliniowego. Przykłady zależności nieliniowych: V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2, gdzie x jest zmienną, a V funkcją celu.

Przyciski Dodaj, Edytuj, Usuń
Te przyciski umożliwiają dodawanie, modyfikowanie i usuwanie wiązań modelu.

Przycisk reset
Aby usunąć wszystkie ustawienia Znalezienie rozwiązania naciśnij przycisk Resetowanie– okno dialogowe zostanie wyczyszczone.

Ta opcja jest przydatna podczas korzystania z różnych opcji ograniczeń. Podczas zapisywania parametrów modelu (przycisk Załadować bezpiecznie, następnie kliknij przycisk Ratować) proponuje się wybrać górną komórkę zakresu (kolumny), w której będą umieszczone: link do funkcji celu, linki do komórek ze zmiennymi, ograniczeniami i parametrami metod rozwiązania (dostępne poprzez przycisk Opcje). Przed zapisaniem upewnij się, że ten zakres nie zawiera danych modelu.
Aby załadować zapisane parametry, najpierw naciśnij przycisk Załadować bezpiecznie, a następnie w wyświetlonym oknie dialogowym przycisk Ściągnij, a następnie ustaw zakres komórek zawierających wcześniej zapisane ustawienia (nie można określić tylko jednej górnej komórki). Kliknij przycisk OK. Potwierdź zresetowanie bieżących ustawień zadania i zastąpienie ich nowymi.

Precyzja
Tworząc model, badacz początkowo ma pewne oszacowanie zakresów zmienności funkcji celu i zmiennych. Biorąc pod uwagę obliczenia w MS EXCEL, zaleca się, aby te zakresy zmienności były znacznie wyższe niż dokładność obliczeń (zwykle jest ona ustawiona od 0,001 do 0,000001). Z reguły dane w modelu są znormalizowane tak, aby zakresy zmienności funkcji celu i zmiennych mieściły się w granicach 0,1 - 100 000. Oczywiście wszystko zależy od konkretnego modelu, ale jeśli twoje zmienne zmienią się o więcej niż 5- 6 rzędów wielkości, to może powinieneś "zgrubić" model, na przykład za pomocą operacji logarytmicznej.

Dodatek Excel „Szukaj rozwiązania” to narzędzie analityczne, które pozwala nam szybko i łatwo określić, kiedy i jaki wynik uzyskamy w określonych warunkach. Możliwości narzędzia do wyszukiwania rozwiązań są znacznie wyższe niż „wybór parametrów” w programie Excel.

Główne różnice między znalezieniem rozwiązania a wyborem parametru:

1. Wybieranie wielu opcji w programie Excel.
2. Narzucanie warunków ograniczających zmiany w komórkach zawierających wartości zmiennych.
3. Możliwość zastosowania w przypadkach, w których może być wiele rozwiązań jednego problemu.

Przykłady i zadania do znalezienia rozwiązania w Excelu

Rozważ możliwości analityczne dodatku. Na przykład musisz zaoszczędzić 14 000 USD przez 10 lat. Przez 10 lat chcesz odkładać 1000 $ rocznie po 5% rocznie na rachunku depozytowym w banku. Poniższy rysunek to tabela w Excelu, która wyraźnie pokazuje saldo zgromadzonych środków za każdy rok. Jak widać, w takich warunkach rachunku depozytowego i akumulacji składek cel nie zostanie osiągnięty nawet po 10 latach. Istnieją dwa sposoby rozwiązania tego problemu:

1. Znajdź bank, który oferuje wyższe oprocentowanie depozytów.
2. Zwiększ kwotę rocznych wpłat na konto bankowe.

Możemy zmieniać wartości zmiennych w komórkach B1 i B2 tak, aby wybrać warunki niezbędne do zgromadzenia wymaganej kwoty pieniędzy.

Dodatek „Wyszukaj rozwiązanie” – pozwala na jednoczesne korzystanie z 2 z tych opcji w celu szybkiego zasymulowania najbardziej optymalnych warunków do osiągnięcia naszego celu. Dla tego:

Jak widać, program nieznacznie podniósł oprocentowanie i wysokość rocznych składek.



Ograniczenie parametrów przy wyszukiwaniu rozwiązań

Załóżmy, że poszedłeś do banku z tym stolikiem, ale bank odmawia podwyższenia stopy procentowej. W takich przypadkach musimy dowiedzieć się, o ile będziemy musieli zwiększyć kwotę rocznych inwestycji. Musimy ustawić limit komórek za pomocą jednej wartości zmiennej. Ale przed rozpoczęciem zmień wartości w komórkach zmiennych na oryginalne: w B1 o 5%, a w B2 o -1000$. A teraz robimy co następuje.