Jak použít hledání a řešitel Excelu k řešení neznámých proměnných

Excel je velmi schopný, když máte všechna data, která potřebujete pro své výpočty.

Ale nebylo by hezké, kdyby to šlo řešit neznámé proměnné ?

S hledáním cílů a doplňkem Řešitel to může. A my vám ukážeme jak. Přečtěte si úplný návod, jak vyřešit jednu buňku pomocí funkce Hledání cíle nebo složitější rovnici s Řešitelem.

Jak používat hledání cílů v aplikaci Excel

Hledání cílů je již integrováno do Excelu. Je to pod Data na kartě v Analýza What-If Jídelní lístek:

V tomto příkladu použijeme velmi jednoduchou sadu čísel. Máme prodejní čísla v hodnotě tří čtvrtin a roční cíl. Můžeme použít hledání cílů, abychom zjistili, jaká čísla musí být ve 4. čtvrtletí, aby byl cíl.

Jak vidíte, současný celkový prodej je 114 706 kusů. Pokud chceme prodat 250 000 do konce roku, kolik jich potřebujeme prodat ve 4. čtvrtletí? To nám řekne Excel's Goal Seek.

Zde je návod, jak krok za krokem používat vyhledávání cílů:

Klikněte Data> Analýza What-If> Goal Seek . Zobrazí se vám toto okno:
Vložte část své rovnice do pole Nastavit buňku pole. Toto je číslo, které se Excel pokusí optimalizovat. V našem případě jde o průběžný součet našich prodejních čísel v buňce B5.
Zadejte hodnotu cíle do pole Vážit si pole. Hledáme celkem 250 000 prodaných kusů, takže do tohoto pole dáme „250 000“.
Řekněte Excelu, kterou proměnnou v souboru Změnou buňky pole. Chceme vidět, jaké by měly být naše tržby ve 4. čtvrtletí. Řekneme tedy Excelu, aby vyřešil buňku D2. Když to bude připraveno, bude to vypadat takto:
Udeřil OK vyřešit svůj cíl. Když to vypadá dobře, stačí trefit OK . Excel vás bude informovat, až aplikace Goal Seek najde řešení.
Klikněte OK znovu a v buňce, pro kterou jste vybrali, uvidíte hodnotu, která řeší vaši rovnici Změnou buňky .

V našem případě je řešením 135 294 jednotek. Samozřejmě jsme to mohli právě zjistit odečtením průběžného součtu od ročního cíle. Ale hledání cíle může být také použito na buňku, která již v něm jsou data . A to je užitečnější.

Všimněte si, že Excel přepíše naše předchozí data. Je to dobrý nápad spusťte Goal Seek na kopii vašich dat . Je také dobré si na zkopírovaná data poznamenat, že byla vygenerována pomocí funkce Hledání cíle. Nechcete to zaměňovat za aktuální, přesná data.

upozorní instagram, když pořídíte snímek obrazovky dm

Hledání cíle je tedy užitečná funkce Excelu , ale není to tak působivé. Podívejme se na nástroj, který je mnohem zajímavější: doplněk Řešitel.

Co dělá Řešitel aplikace Excel?

Stručně řečeno, Řešitel je jako a vícerozměrná verze Hledání cílů . Trvá jedna proměnná cíle a upravuje řadu dalších proměnných, dokud nedostane požadovanou odpověď.

Může vyřešit maximální hodnotu čísla, minimální hodnotu čísla nebo přesné číslo.

A funguje to v rámci omezení, takže pokud jednu proměnnou nelze změnit nebo se může lišit pouze v rámci zadaného rozsahu, řešitel to vezme v úvahu.

Je to skvělý způsob, jak vyřešit více neznámých proměnných v Excelu. Ale najít a používat to není jednoduché.

Podívejme se na načítání doplňku Řešitel a poté se vrhneme na to, jak používat Řešitel v Excelu 2016.

Jak načíst doplněk Řešitel

Excel ve výchozím nastavení nemá Řešitel. Je to doplněk, takže si jej stejně jako ostatní výkonné funkce Excelu musíte nejprve načíst. Naštěstí už je ve vašem počítači.

Vydejte se na Soubor> Možnosti> Doplňky . Poté klikněte na Jít vedle Spravovat: Doplňky aplikace Excel .

Pokud tato rozbalovací nabídka říká něco jiného než „Doplňky aplikace Excel“, budete ji muset změnit:

Ve výsledném okně uvidíte několik možností. Ujistěte se, že je pole vedle Doplněk řešitele je zaškrtnuto a stisknuto OK .

Nyní uvidíte Řešitel tlačítko v Analýza skupina Data karta:

Pokud jste již používali Nástroj pro analýzu dat , uvidíte tlačítko Analýza dat. Pokud ne, Řešitel se objeví sám.

Nyní, když jste si doplněk načetli, pojďme se podívat, jak jej používat.

Jak používat Solver v Excelu

Každá akce Řešitele má tři části: cíl, proměnné buňky a omezení. Projdeme každý z kroků.

Klikněte Data> Řešitel . Níže uvidíte okno Parametry řešitele. (Pokud tlačítko Řešitel nevidíte, přečtěte si předchozí část o tom, jak načíst doplněk Řešitel.)
Nastavte si cíl buňky a sdělte Excelu svůj cíl. Cíl je v horní části okna Řešitel a má dvě části: buňku objektivu a volbu maximalizace, minimalizace nebo konkrétní hodnoty. Pokud zvolíte Max , Excel upraví vaše proměnné tak, aby ve vaší buňce cíle bylo co největší číslo. Min je opak: Řešitel minimalizuje objektivní číslo. Hodnota umožňuje zadat konkrétní číslo, které má Řešitel hledat.
Vyberte buňky proměnných, které může Excel změnit. Variabilní buňky se nastavují pomocí Změnou variabilních buněk pole. Klikněte na šipku vedle pole, poté kliknutím a přetažením vyberte buňky, se kterými by měl Řešitel pracovat. Všimněte si, že to jsou všechny buňky to se může lišit. Pokud nechcete, aby se buňka měnila, nevybírejte ji.
Nastavit omezení pro více nebo jednotlivé proměnné. Nakonec se dostáváme k omezením. Tady je Solver opravdu silný. Namísto změny libovolné buňky proměnných na libovolné číslo, které chce, můžete zadat omezení, která musí být splněna. Podrobnosti najdete v níže uvedené části o nastavení omezení.
Jakmile jsou všechny tyto informace na svém místě, stiskněte Řešit dostat vaši odpověď. Excel aktualizuje vaše data, aby zahrnovala nové proměnné (proto doporučujeme nejprve vytvořit kopii dat).

Můžete také generovat zprávy, na které se krátce podíváme v našem příkladu Řešitele níže.

Jak nastavit omezení v Řešiči

Můžete Excelu sdělit, že jedna proměnná musí být větší než 200. Při zkoušení různých hodnot proměnných aplikace Excel s konkrétní proměnnou nepůjde pod 201.

Chcete -li přidat omezení, klikněte na Přidat tlačítko vedle seznamu omezení. Získáte nové okno. Vyberte buňku (nebo buňky), která má být omezena v Odkaz na buňku pole a poté vyberte operátora.

Zde jsou k dispozici operátoři:

<= (menší nebo rovno)
= (rovná)
=> (větší nebo rovno)
int (musí být celé číslo)
dopoledne (musí být buď 1 nebo 0)
Vše Různé

Vše Různé je trochu matoucí. Určuje, že každá buňka v rozsahu, pro který vyberete Odkaz na buňku musí být jiné číslo. Ale také určuje, že musí být mezi 1 a počtem buněk. Takže pokud máte tři buňky, skončíte s čísly 1, 2 a 3 (ale ne nutně v tomto pořadí)

Nakonec přidejte hodnotu omezení.

Je důležité si uvědomit, že můžete vyberte více buněk pro odkaz na buňku. Pokud chcete, aby například šest proměnných mělo hodnoty vyšší než 10, můžete je všechny vybrat a sdělit Řešiteli, že musí být větší nebo rovné 11. Není nutné pro každou buňku přidávat omezení.

Zaškrtnutím políčka v hlavním okně Řešitele můžete také zajistit, aby všechny hodnoty, pro které jste nezadali omezení, nebyly záporné. Pokud chcete, aby vaše proměnné byly záporné, zrušte zaškrtnutí tohoto políčka.

Příklad řešitele

Chcete-li zjistit, jak to všechno funguje, použijeme k rychlému výpočtu doplněk Řešitel. Zde začínáme s daty:

V něm máme pět různých zaměstnání, z nichž každé platí jinou sazbu. Máme také počet hodin, které teoretický pracovník odpracoval na každém z těchto zaměstnání v daném týdnu. Pomocí doplňku Řešitel zjistíme, jak maximalizovat celkový plat při zachování určitých proměnných v rámci určitých omezení.

Zde jsou omezení, která použijeme:

Žádná práce může klesnout pod čtyři hodiny.
Úloha 2 musí být více než osm hodin .
Úloha 5 musí být necelých jedenáct hodin .
Celkový počet odpracovaných hodin musí být rovna 40 .

Může být užitečné napsat si svá omezení takto před použitím Řešitele.

Takto bychom to nastavili v Řešiteli:

Nejprve si toho všimněte Vytvořil jsem kopii tabulky nepřepíšeme tedy ten původní, který obsahuje naši aktuální pracovní dobu.

A za druhé, podívejte se, že hodnoty v omezeních větší než a menší než jsou jeden vyšší nebo nižší než to, co jsem zmínil výše. To proto, že neexistují možnosti větší než nebo menší než. Existuje pouze více než nebo rovno a méně než nebo rovno.

Pojďme se trefit Řešit a uvidíme, co se stane.

Řešitel našel řešení! Jak vidíte nalevo od okna výše, naše výdělky se zvýšily o 130 $. A všechna omezení byla splněna.

použijte iphone jako mikrofon pro PC přes usb

Chcete -li zachovat nové hodnoty, ujistěte se Keep Solver Solution je zkontrolováno a zasaženo OK .

Pokud však chcete více informací, můžete vybrat sestavu z pravé strany okna. Vyberte všechny požadované sestavy, řekněte Excelu, zda je chcete mít přehledné (doporučuji), a stiskněte OK .

Sestavy jsou generovány na nové listy ve vašem sešitu a poskytují vám informace o procesu, kterým doplněk Řešitel prošel, aby získal vaši odpověď.

V našem případě zprávy nejsou příliš vzrušující a není tam mnoho zajímavých informací. Pokud ale spustíte složitější řešitelskou rovnici, můžete v těchto nových listech najít nějaké užitečné informace o vykazování. Stačí kliknout na + tlačítko na straně jakékoli zprávy, abyste získali další informace:

Řešení pokročilých možností

Pokud toho o statistikách moc nevíte, můžete pokročilé možnosti Řešitele ignorovat a spustit je tak, jak jsou. Pokud ale provádíte rozsáhlé a složité výpočty, můžete se na ně podívat.

Nejviditelnější je metoda řešení:

Můžete si vybrat mezi GRG Nonlinear, Simplex LP a Evolutionary. Excel poskytuje jednoduché vysvětlení, kdy byste měli použít každý z nich. Lepší vysvětlení vyžaduje určité znalosti statistik a regrese.

Chcete -li upravit další nastavení, stačí kliknout na Možnosti knoflík. Můžete informovat Excel o celočíselné optimálnosti, nastavit časová omezení výpočtu (užitečné pro rozsáhlé datové sady) a upravit způsob, jakým metody GRG a evoluční řešení postupují při výpočtech.

Opět platí, že pokud nevíte, co něco z toho znamená, nedělejte si s tím starosti. Pokud se chcete dozvědět více o tom, kterou metodu řešení použít, má aplikace Engineer Excel a dobrý článek, který vám to stanoví . Pokud chcete maximální přesnost, Evolutionary je pravděpodobně dobrá cesta. Jen si uvědomte, že to bude trvat dlouho.

Hledání cílů a řešitel: Posunutí aplikace Excel na další úroveň

Nyní, když jste spokojeni se základy řešení neznámých proměnných v aplikaci Excel, je vám otevřen zcela nový svět výpočtu tabulek.

Hledání cílů vám může ušetřit čas zrychlením některých výpočtů a Řešitel k tomu přidává obrovské množství energie Výpočtové schopnosti Excelu .

Jde jen o to, dostat se s nimi do pohody. Čím více je budete používat, tím budou užitečnější.

Používáte ve svých tabulkách Goal Seek nebo Solver? Jaké další tipy můžete poskytnout, abyste z nich získali nejlepší odpovědi? Podělte se o své myšlenky v níže uvedených komentářích!

Podíl Podíl tweet E-mailem 5 tipů, jak přeplnit své počítače VirtualBox Linux

Jste unaveni špatným výkonem, který nabízejí virtuální stroje? Zde je to, co byste měli udělat, abyste zvýšili výkon svého VirtualBoxu.

Číst dále Související témata

Produktivita
Tabulkový procesor
Microsoft Excel
Tipy pro Microsoft Office

O autorovi Pak Albright(506 článků zveřejněno)

Dann je poradce pro strategii obsahu a marketing, který pomáhá společnostem vytvářet poptávku a potenciální zákazníky. Píše také o strategickém a obsahovém marketingu na dannalbright.com.

Více od Danna Albrighta

Přihlaste se k odběru našeho zpravodaje

Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!

Kliknutím sem se přihlásíte k odběru