Jak používat Excel pro vyhledávání a řešení cílů pro řešení neznámých proměnných

  • Michael Fisher
  • 0
  • 3457
  • 554
reklama

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

Ale nebylo by hezké, kdyby to bylo možné vyřešit neznámé proměnné?

S cílem Goek a doplňkem Řešitele to může. A my vám ukážeme jak. Přečtěte si podrobný průvodce, jak vyřešit jednu buňku pomocí funkce Goal Seek nebo složitější rovnice s řešením Solver.

Jak používat vyhledávání cílů v Excelu

Hledání cílů je již zabudováno do aplikace Excel. Je to pod Data na kartě Analýza co-if Jídelní lístek:

V tomto příkladu použijeme velmi jednoduchou sadu čísel. Máme tři čtvrtiny prodejních čísel a roční cíl. Pomocí funkce Goal Seek můžeme zjistit, jaká čísla musí být ve 4. čtvrtletí, aby bylo dosaženo cíle.

Jak vidíte, současný celkový prodej je 114 706 kusů. Pokud chceme do konce roku prodat 250 000, kolik potřebujeme prodat ve 4. čtvrtletí? Řekněte nám, že se jedná o cíl aplikace Excel.

Postup hledání cíle, krok za krokem:

  1. Klikněte na Data> Analýza What-If> Seek. Zobrazí se toto okno:
  2. Dal “rovná se” část vaší rovnice v Nastavit buňku pole. Toto je číslo, které se Excel pokusí optimalizovat. V našem případě je to celkový počet našich prodejních čísel v buňce B5.
  3. Zadejte svou cílovou hodnotu do Na hodnotu pole. Hledáme celkem 250 000 prodaných kusů, takže dáme “250 000” v tomto poli.
  4. Řekněte Excelu, pro kterou proměnnou se má v Změnou buňky pole. Chceme vidět, jaký musí být náš prodej ve 4. čtvrtletí. Takže řekneme Excelu, aby vyřešil buňku D2. Vypadá to takto, až bude připraven:
  5. Udeřil OK vyřešit pro svůj cíl. Když to vypadá dobře, stačí zasáhnout OK. Excel vám dá vědět, když Goal Seek našel řešení.
  6. Klikněte na OK znovu a uvidíte hodnotu, která řeší vaši rovnici v buňce, pro kterou jste se rozhodli Změnou buňky.

V našem případě je řešením 135 294 jednotek. Samozřejmě jsme to mohli zjistit tak, že odečteme celkovou částku od ročního cíle. Ale Goal Seek lze také použít na buňku, která již obsahuje 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 Goek Seek na kopii vašich dat. Je také dobré si na vaše zkopírovaná data poznamenat, že byla vytvořena pomocí funkce Goal Seek. Nechcete je zaměňovat za aktuální a přesná data.

Hledání cílů je tedy užitečná funkce aplikace Excel 16 vzorců aplikace Excel, které vám pomohou vyřešit problémy v reálném životě 16 vzorců aplikace Excel, které vám pomohou vyřešit problémy v reálném životě Správným nástrojem je polovina práce. Excel dokáže řešit výpočty a zpracovávat data rychleji, než najdete kalkulačku. Ukážeme vám klíčové vzorce aplikace Excel a ukážeme, jak je používat. , ale není to tak působivé. Pojďme se podívat na nástroj, který je mnohem zajímavější: doplněk Řešitele.

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

Stručně řečeno, Solver je jako vícerozměrná verze Goal Seek. Vezme jednu proměnnou cíle a upraví ř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 nelze jednu proměnnou změnit nebo se může lišit pouze v určitém rozsahu, Solver to vezme v úvahu.

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

Pojďme se podívat na načtení doplňku Řešitele a poté skočte na to, jak používat Řešitele v Excelu 2016.

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

Excel ve výchozím nastavení nemá Řešitele. Je to doplněk, tak jako jiné výkonné funkce Excelu Power Up Excel s 10 doplňky pro zpracování, analýzu a vizualizaci dat jako Pro Power Excel Excel s 10 doplňky pro zpracování, analýzu a vizualizaci dat jako Pro Vanilla Excel je úžasné, ale pomocí doplňků jej můžete vylepšit. Ať už potřebujete data zpracovat, je pravděpodobné, že pro ně někdo vytvořil aplikaci Excel. Zde je výběr. , musíte ji nejprve načíst. Naštěstí je to již na vašem počítači.

Vydejte se Soubor> Možnosti> Doplňky. Poté klikněte na Jít vedle Správa: 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 pole vedle Doplněk řešitele je zaškrtnuto a zasaženo OK.

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

Pokud již používáte Nástroj pro analýzu dat Jak udělat základní analýzu dat v Excelu Jak udělat základní analýzu dat v Excelu Excel není určen pro analýzu dat, ale stále dokáže zpracovávat statistiky. Ukážeme vám, jak pomocí doplňku nástroje pro analýzu dat spustit statistiku aplikace Excel. , zobrazí se tlačítko Analýza dat. Pokud ne, Solver se objeví sám.

Nyní, když jste doplněk načetli, se podívejme, jak jej použít.

Jak používat řešič v Excelu

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

  1. Klikněte na Data> Řešitel. Níže uvidíte okno Parametry řešiče. (Pokud nevidíte tlačítko řešiče, přečtěte si předchozí část o tom, jak načíst doplněk Řešitele.)
  2. Nastavte svůj cíl buňky a sdělte Excelu svůj cíl. Cíl je v horní části okna Řešitele a má dvě části: buňku cíle a výběr maximalizace, minimalizace nebo konkrétní hodnoty.

    Pokud vyberete Max, Excel upraví vaše proměnné tak, aby v cílové buňce bylo co největší počet. Min je opak: Řešitel minimalizuje objektivní číslo. Hodnota umožňuje určit konkrétní číslo, které má řešitel hledat.
  3. Vyberte proměnné buňky, které Excel může změnit. Proměnné buňky jsou nastaveny pomocí Změnou variabilních buněk pole. Klepněte na šipku vedle pole a poté klepnutím a tažením vyberte buňky, se kterými má Řešitel pracovat. Všimněte si, že jsou všechny buňky to se může lišit. Pokud nechcete, aby se buňka změnila, nevybírejte ji.
  4. Nastavení omezení pro více nebo jednotlivé proměnné. Nakonec se dostáváme k omezením. To je místo, kde je Solver opravdu silný. Místo změny libovolné z proměnných buněk na libovolné číslo můžete určit omezení, která musí být splněna. Podrobnosti naleznete v části o tom, jak nastavit omezení níže.
  5. Jakmile jsou všechny tyto informace na místě, stiskněte Řešit získat odpověď. Excel aktualizuje vaše data tak, 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 říct Excel, že jedna proměnná musí být větší 200. Při pokusu o různé hodnoty proměnné Excel nepřejde pod 201 s touto konkrétní proměnnou.

Chcete-li přidat omezení, klikněte na ikonu Přidat tlačítko vedle seznamu omezení. Získáte nové okno. Vyberte buňku (nebo buňky), které mají být omezeny v Reference buněk vyberte operátora.

Zde jsou dostupní operátoři:

  • <= (menší nebo rovno)
  • = (rovná)
  • => (větší nebo rovno)
  • int (musí být celé číslo)
  • zásobník (musí být 1 nebo 0)
  • AllDifferent

AllDifferent je trochu matoucí. Určuje, že každá buňka v rozsahu, který vyberete Reference buněk musí být jiné číslo. Určuje však také, ž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 Cell Reference. Pokud chcete, aby například šest proměnných mělo hodnoty vyšší než 10, můžete je vybrat všechny a říct Řešiteli, že musí být větší nebo rovno 11. Nemusíte přidávat omezení pro každou buňku..

Můžete také použít zaškrtávací políčko v hlavním okně Řešitele a ujistit se, že všechny hodnoty, pro které jste neurčili omezení, nejsou negativní. Pokud chcete, aby byly vaše proměnné negativní, zrušte zaškrtnutí tohoto políčka.

Příklad řešitele

Abychom zjistili, jak to všechno funguje, použijeme doplněk Řešitele k rychlému výpočtu. Zde jsou data, která začínáme:

V tom máme pět různých pracovních míst, z nichž každá platí jinou sazbu. Máme také počet hodin, které teoretický pracovník pracoval na každém z těchto pracovních míst v daném týdnu. Pomocí doplňku Řešitel můžeme zjistit, jak maximalizovat celkovou odměnu 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.
  • Úkol 2 musí být více než osm hodin.
  • Úkol 5 musí být méně než jedenáct hodin.
  • Celkový počet odpracovaných hodin musí být rovná se 40.

Před použitím Řešitele může být užitečné zapsat vaše omezení.

Takto jsme to nastavili v Solveru:

Nejprve si to všimněte Vytvořil jsem kopii tabulky takže nepřepisujeme původní, který obsahuje naši aktuální pracovní dobu.

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

Pojďme zasáhnout Řešit a uvidíme, co se stane.

Řešitel našel řešení! Jak vidíte vlevo od výše uvedeného okna, naše příjmy se zvýšily o 130 USD. A všechna omezení byla splněna.

Chcete-li zachovat nové hodnoty, ujistěte se, že Zachovat řešení řešení je zaškrtnuto a zasaženo OK.

Pokud však chcete získat více informací, můžete vybrat sestavu z pravé strany okna. Vyberte všechny přehledy, které chcete, sdělte Excelu, zda je chcete nastínit (doporučuji) a stiskněte OK.

Přehledy jsou generovány na nových listech v 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ě nejsou zprávy příliš vzrušující a není tam spousta zajímavých informací. Pokud však spustíte složitější Řešitelovu rovnici, můžete v těchto nových listech najít nějaké užitečné informace o hlášení. Stačí kliknout na ikonu + tlačítko na boku kterékoli zprávy, abyste získali více informací:

Rozšířené možnosti řešení

Pokud nevíte mnoho o statistikách, můžete ignorovat pokročilé možnosti Solvera a jednoduše je spustit tak, jak jsou. Ale pokud provádíte velké a složité výpočty, možná byste se měli podívat do nich.

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

Můžete si vybrat mezi GRG Nelineární, Simplex LP a Evoluční. Excel poskytuje jednoduché vysvětlení, kdy byste měli použít každý z nich. Lepší vysvětlení vyžaduje určité znalosti statistik Naučte se statistiku zdarma s těmito 6 zdroji Naučte se statistiku zdarma s těmito 6 zdroji Statistika má pověst předmětu, kterému je obtížné porozumět. Poučení ze správného zdroje vám však pomůže porozumět výsledkům průzkumu, zprávám o volbách a úkolům ve třídě statistik v žádném okamžiku. a regrese.

Chcete-li upravit další nastavení, stačí kliknout na ikonu Možnosti tlačítko. Můžete říct Excelu o celočíselné optimálnosti, nastavit časová omezení výpočtu (užitečná pro rozsáhlé datové sady) a nastavit, jak metody GRG a Evoluční řešení řeší své výpočty.

Opět, pokud nevíte, co to znamená, nedělejte si s tím starosti. Pokud se chcete dozvědět více o tom, jakou metodu řešení použít, aplikace Engineer Excel má dobrý článek, který vám ji stanoví. Pokud chcete maximální přesnost, Evolutionary je pravděpodobně dobrý způsob, jak jít. Jen mějte na paměti, že to bude trvat dlouho.

Hledání a řešení cílů: Převádění Excelu na další úroveň

Nyní, když vám vyhovuje základy řešení neznámých proměnných v Excelu, je vám k dispozici zcela nový svět výpočtu tabulky.

Program Goek Seek vám pomůže ušetřit čas tím, že některé výpočty zrychlí a program Solver přidává obrovské množství energie do výpočtových schopností aplikace Excel Jak vypočítat základní statistiku v Excelu: Příručka pro začátečníky Jak vypočítat základní statistiku v Excelu: Příručka pro začátečníky Program Microsoft Excel může statistiky! Můžete vypočítat procenta, průměry, směrodatné odchylky, standardní chyby a studentovy T-testy. .

Je to jen otázka, jak se s nimi cítit dobře. Čím více je použijete, tím užitečnější budou.

Používáte v tabulkách vyhledávání cílů nebo řešitelů? Jaké další tipy můžete poskytnout, abyste z nich dostali ty nejlepší odpovědi? Podělte se o své myšlenky v komentářích níže!




Bisher hat noch niemand einen Kommentar zu diesem Artikel abgegeben.

O moderní technologii, jednoduché a cenově dostupné.
Váš průvodce ve světě moderních technologií. Naučte se, jak používat technologie a pomůcky, které nás každý den obklopují, a naučte se, jak objevovat na internetu zajímavé věci.