Co je Excel Solver?

2024 Autor: Abigail Brown | [email protected]. Naposledy změněno: 2024-01-07 19:01

Doplněk Excel Solver provádí matematickou optimalizaci. To se obvykle používá k přizpůsobení složitých modelů datům nebo hledání iterativních řešení problémů. Můžete například chtít proložit křivku některými datovými body pomocí rovnice. Řešitel může v rovnici najít konstanty, které nejlépe odpovídají datům. Další aplikace je tam, kde je obtížné přeskupit model tak, aby se požadovaný výstup stal předmětem rovnice.

Kde je Solver v Excelu?

Doplněk Řešitel je součástí Excelu, ale není vždy načten jako součást výchozí instalace. Chcete-li zkontrolovat, zda je načten, vyberte kartu DATA a vyhledejte ikonu Solver v sekci Analýza.

Pokud nemůžete najít Řešitel na kartě DATA, budete muset načíst doplněk:

1. Vyberte kartu FILE a poté vyberte Options.

   

2. V dialogovém okně Options vyberte z karet na levé straně Add-Ins.

   

3. Ve spodní části okna vyberte Doplňky Excelu z rozbalovací nabídky Manage a vyberte Přejít…

   

4. Zaškrtněte políčko vedle Doplněk Řešitel a vyberte OK.

   

5. Příkaz Solver by se nyní měl objevit na kartě DATA. Jste připraveni použít Solver.

   

Použití Solveru v Excelu

Začněme jednoduchým příkladem, abychom pochopili, co Řešitel dělá. Představte si, že chceme vědět, jaký poloměr dá kružnici o ploše 50 čtverečních jednotek. Známe rovnici pro obsah kruhu (A=pi r²). Tuto rovnici bychom samozřejmě mohli přeskupit, abychom získali poloměr požadovaný pro danou oblast, ale pro příklad předstírejme, že nevíme, jak to udělat.

Vytvořte tabulku s poloměrem v B1 a vypočítejte plochu v B2 pomocí rovnice =pi()B1^2.

Hodnotu v B1 bychom mohli ručně upravit, dokud B2 neukáže hodnotu dostatečně blízkou 50. V závislosti na tom, jak přesné být, může to být praktický přístup. Pokud však potřebujeme být velmi přesní, provedení požadovaných úprav bude trvat dlouho. Ve skutečnosti to je v podstatě to, co Řešitel dělá. Provádí úpravy hodnot v určitých buňkách a kontroluje hodnotu v cílové buňce:

1. Vyberte kartu DATA a Solver pro načtení dialogového okna Parametry řešitele

2. Nastavit Cíl buňku jako Oblast, B2. Toto je hodnota, která bude kontrolována a upravovat ostatní buňky, dokud tato nedosáhne správné hodnoty.

   

3. Vyberte tlačítko pro Value of: a nastavte hodnotu 50. To je hodnota, které by měl B2 dosáhnout.

   

4. V poli s názvem Změnou proměnných buněk: zadejte buňku obsahující poloměr, B1.

   

5. Ponechte ostatní možnosti tak, jak jsou ve výchozím nastavení, a vyberte Solve. Provede se optimalizace, hodnota B1 se upraví, dokud B2 nebude 50 a zobrazí se dialog Výsledky řešení.

   

6. Vyberte OK pro zachování řešení.

   

Tento jednoduchý příklad ukazuje, jak funguje řešitel. V tomto případě bychom mohli snadněji získat řešení jinými způsoby. Dále se podíváme na několik příkladů, kde Solver poskytuje řešení, která by bylo obtížné najít jiným způsobem.

Přizpůsobení složitého modelu pomocí doplňku Řešitel Excel

Excel má vestavěnou funkci pro provádění lineární regrese, prokládání přímky přes sadu dat. Mnoho běžných nelineárních funkcí lze linearizovat, což znamená, že lineární regresi lze použít k přizpůsobení funkcí, jako jsou exponenciály. Pro složitější funkce lze Solver použít k provedení „minimalizace nejmenších čtverců“. V tomto příkladu budeme zvažovat přizpůsobení rovnice ve tvaru ax^b+cx^d níže uvedeným datům.

To zahrnuje následující kroky:

1. Uspořádejte datovou sadu s hodnotami x ve sloupci A a hodnotami y ve sloupci B.
2. Vytvořte 4 hodnoty koeficientů (a, b, c a d) někde v tabulce, kterým lze zadat libovolné počáteční hodnoty.

3. Vytvořte sloupec proložených hodnot Y pomocí rovnice ve tvaru ax^b+cx^d, která odkazuje na koeficienty vytvořené v kroku 2 a na hodnoty x ve sloupci A. Všimněte si, že za účelem zkopírování vzorce dolů ve sloupci, odkazy na koeficienty musí být absolutní, zatímco odkazy na hodnoty x musí být relativní.

   

4. Ačkoli to není nezbytné, můžete získat vizuální indikaci toho, jak dobře rovnice odpovídá, vynesením obou sloupců y proti hodnotám x na jediném bodovém grafu XY. Má smysl používat značky pro původní datové body, protože se jedná o diskrétní hodnoty se šumem, a používat čáru pro proloženou rovnici.

   

5. Dále potřebujeme způsob, jak kvantifikovat rozdíl mezi daty a naší proloženou rovnicí. Standardním způsobem, jak toho dosáhnout, je vypočítat součet čtverců rozdílů. Ve třetím sloupci je pro každý řádek původní hodnota dat pro Y odečtena od hodnoty proložené rovnice a výsledek je umocněn na druhou. Takže v D2 je hodnota dána =(C2-B2)^2 Potom se vypočítá součet všech těchto umocněných hodnot. Protože jsou hodnoty umocněny, mohou být pouze kladné.

   

6. Nyní jste připraveni provést optimalizaci pomocí Solveru. Existují čtyři koeficienty, které je třeba upravit (a, b, c a d). Máte také jedinou cílovou hodnotu, kterou chcete minimalizovat, součet čtverců rozdílů. Spusťte řešič, jak je uvedeno výše, a nastavte parametry řešiče tak, aby odkazovaly na tyto hodnoty, jak je znázorněno níže.

   

7. Zrušte zaškrtnutí možnosti Udělat neomezené proměnné jako nezáporné, tím by všechny koeficienty nabyly kladných hodnot.

   

8. Vyberte Solve a zkontrolujte výsledky. Tabulka se aktualizuje a poskytuje dobrou představu o dobré vhodnosti. Pokud se řešič na první pokus nepodaří správně uložit, můžete jej zkusit spustit znovu. Pokud se přizpůsobení zlepšilo, zkuste to vyřešit z aktuálních hodnot. V opačném případě můžete před vyřešením zkusit ručně vylepšit přizpůsobení.

   

9. Jakmile dosáhnete správného přizpůsobení, můžete řešitel opustit.

Řešení modelu iterativně

Někdy existuje relativně jednoduchá rovnice, která dává výstup ve smyslu nějakého vstupu. Když se však pokusíme problém obrátit, není možné najít jednoduché řešení. Například výkon spotřebovaný vozidlem je přibližně dán P=av + bv^3 kde v je rychlost, a je součinitel valivého odporu a b je součinitel pro aerodynamický odpor. I když se jedná o poměrně jednoduchou rovnici, není snadné ji přeskupit tak, aby poskytla rovnici rychlosti, kterou vozidlo dosáhne při daném příkonu. Můžeme však použít Solver k opakovanému nalezení této rychlosti. Zjistěte například rychlost dosaženou s příkonem 740 W.

1. Vytvořte jednoduchou tabulku s rychlostí, koeficienty aab a z nich vypočítaným výkonem.

   

2. Spusťte Řešitel a zadejte sílu, B5, jako cíl. Nastavte cílovou hodnotu 740 a vyberte rychlost, B2, jako proměnné buňky, které chcete změnit. Vyberte solve pro zahájení řešení.

   

3. Řešitel upravuje hodnotu rychlosti, dokud se výkon velmi neblíží 740, čímž poskytuje požadovanou rychlost.

   

4. Řešení modelů tímto způsobem může být často rychlejší a méně náchylné k chybám než invertování složitých modelů.

Porozumět různým možnostem dostupným v řešiteli může být docela obtížné. Pokud máte potíže se získáním rozumného řešení, pak je často užitečné použít na proměnlivé buňky okrajové podmínky. To jsou mezní hodnoty, za které by se neměly upravovat. Například v předchozím příkladu by rychlost neměla být menší než nula a bylo by také možné nastavit horní hranici. To by byla rychlost, o které jste si jisti, že vozidlo nemůže jet rychleji. Pokud jste schopni nastavit meze pro měnitelné proměnné buňky, pak také lépe fungují další pokročilejší možnosti, jako je multistart. Tím spustíte řadu různých řešení, počínaje různými počátečními hodnotami proměnných.

Výběr metody řešení může být také obtížný. Simplex LP je vhodný pouze pro lineární modely, pokud problém není lineární, selže se zprávou, že tato podmínka nebyla splněna. Další dvě metody jsou obě vhodné pro nelineární metody. GRG Nonlinear je nejrychlejší, ale jeho řešení může být vysoce závislé na počátečních počátečních podmínkách. Má flexibilitu, že nevyžaduje, aby proměnné měly nastaveny hranice. Evoluční řešitel je často nejspolehlivější, ale vyžaduje, aby všechny proměnné měly horní i dolní mez, což může být obtížné předem zjistit.

Doplněk Excel Solver je velmi výkonný nástroj, který lze použít na mnoho praktických problémů. Chcete-li plně využít možnosti Excelu, zkuste zkombinovat Řešitel s makry Excel.