Ako použiť hľadanie a riešiteľ cieľov programu Excel na riešenie neznámych premenných

Excel je veľmi schopný, keď máte všetky údaje, ktoré potrebujete na svoje výpočty.

Nebolo by však pekné, keby sa dalo riešenie pre neznáme premenné ?

S hľadaním cieľa a doplnkom Riešiteľ to môže byť. A my vám ukážeme, ako na to. Prečítajte si úplný návod, ako vyriešiť jednu bunku pomocou funkcie Hľadanie cieľa alebo komplikovanejšiu rovnicu s riešením.

Ako používať hľadanie cieľov v programe Excel

Hľadanie cieľa je už zabudované v Exceli. Je to pod Údaje karta, v Analýza Čo-Ak Ponuka:

V tomto prípade použijeme veľmi jednoduchú sadu čísel. Máme predajné čísla za tri štvrtiny a ročný cieľ. Pomocou funkcie Hľadanie cieľa môžeme zistiť, aké čísla musia byť v 4. štvrťroku na dosiahnutie cieľa.

Ako vidíte, súčasný celkový predaj je 114 706 kusov. Ak chceme do konca roka predať 250 000, koľko ich potrebujeme na predaj v 4. štvrťroku? Hľadanie cieľov Excelu nám to povie.

Tu je návod, ako krok za krokom používať funkciu Hľadanie cieľa:

Kliknite Údaje> Analýza Čo-Ak> Hľadanie cieľa . Uvidíte toto okno:
Dajte časť rovnice „rovná sa“ do poľa Nastaviť bunku lúka. Toto je číslo, ktoré sa Excel pokúsi optimalizovať. V našom prípade je to priebežný súčet našich predajných čísel v bunke B5.
Zadajte hodnotu svojho cieľa do Ohodnotiť lúka. Hľadáme celkom 250 000 predaných kusov, takže do tohto poľa vložíme „250 000“.
Povedzte Excelu, pre ktorú premennú máte v súbore Zmenou bunky lúka. Chceme vidieť, aké by mali byť naše tržby v Q4. Povieme teda Excelu, aby vyriešil bunku D2. Keď to bude pripravené, bude to vyzerať takto:
Hit OK vyriešiť svoj cieľ. Keď to vyzerá dobre, stačí trafiť OK . Excel vám oznámi, keď aplikácia Hľadanie cieľov našla riešenie.
Kliknite OK znova a v bunke, pre ktorú ste sa rozhodli, uvidíte hodnotu, ktorá rieši vašu rovnicu Zmenou bunky .

V našom prípade je riešením 135 294 jednotiek. Samozrejme, mohli sme to práve zistiť odpočítaním priebežného súčtu od ročného cieľa. Hľadanie cieľa sa však dá použiť aj na bunku, ktorá už obsahuje údaje . A to je užitočnejšie.

Všimnite si toho, že Excel prepíše naše predchádzajúce údaje. Je to dobrý nápad spustite aplikáciu Goal Seek na kópii svojich údajov . Je tiež dobré si zapísať skopírované údaje, ktoré boli vygenerované pomocou funkcie Hľadanie cieľa. Nechcete si to mýliť s aktuálnymi a presnými údajmi.

notepad ++ správca doplnkov chýba

Hľadanie cieľa je teda a užitočná funkcia Excelu , ale nie je to také pôsobivé. Pozrime sa na nástroj, ktorý je oveľa zaujímavejší: doplnok Riešiteľ.

Čo robí riešiteľ programu Excel?

Stručne povedané, Riešiteľ je ako a viacrozmerná verzia Hľadanie cieľa . Potrebuje jednu cieľovú premennú a upraví niekoľko ďalších premenných, kým nedostane požadovanú odpoveď.

Môže vyriešiť maximálnu hodnotu čísla, minimálnu hodnotu čísla alebo presné číslo.

A funguje to v rámci obmedzení, takže ak jednu premennú nemožno zmeniť alebo sa môže líšiť iba v určenom rozsahu, riešiteľ to vezme do úvahy.

Je to skvelý spôsob, ako vyriešiť niekoľko neznámych premenných v programe Excel. Nájdenie a používanie však nie je jednoduché.

Pozrime sa na načítanie doplnku Riešiteľ a potom si skočte do toho, ako používať Riešiteľ v Exceli 2016.

Ako načítať doplnok Solver

Excel predvolene nemá Riešiteľ. Je to doplnok, takže si ho podobne ako ostatné výkonné funkcie Excelu musíte načítať. Našťastie je už vo vašom počítači.

Vydajte sa na Súbor> Možnosti> Doplnky . Potom kliknite na Choď vedľa Spravovať: Doplnky programu Excel .

Ak tento rozbaľovací zoznam hovorí o niečom inom ako „Doplnky programu Excel“, budete ho musieť zmeniť:

Vo výslednom okne uvidíte niekoľko možností. Uistite sa, že je políčko vedľa Doplnok pre riešenie je začiarknuté a zasiahnuté OK .

Teraz uvidíte Riešiteľ tlačidlo v Analýza skupina Údaje karta:

Ak ste už používali Nástroj na analýzu údajov Zobrazí sa tlačidlo Analýza údajov. Ak nie, Riešiteľ sa zobrazí sám.

Teraz, keď ste si doplnok nainštalovali, poďme sa pozrieť na to, ako ho používať.

Ako používať riešenie v programe Excel

Každá akcia Riešiteľ má tri časti: cieľ, variabilné bunky a obmedzenia. Prejdeme sa všetkými krokmi.

Kliknite Údaje> Riešiteľ . Nasleduje okno Parametre riešiteľa. (Ak sa tlačidlo riešiteľa nezobrazuje, pozrite si predchádzajúcu časť o načítaní doplnku Riešiteľ.)
Nastavte cieľ svojej bunky a povedzte Excelu svoj cieľ. Cieľ je v hornej časti okna Riešiteľ a má dve časti: bunku objektívu a výber maximalizácie, minimalizácie alebo konkrétnej hodnoty. Ak vyberiete Max Excel upraví vaše premenné tak, aby vo vašej cieľovej bunke získal čo najväčší počet. Min je pravý opak: Riešiteľ minimalizuje objektívne číslo. Hodnota umožňuje zadať konkrétne číslo, ktoré má Riešiteľ hľadať.
Vyberte bunky premenných, ktoré môže Excel zmeniť. Variabilné bunky sú nastavené pomocou Zmenou variabilných buniek lúka. Kliknite na šípku vedľa poľa, potom kliknutím a potiahnutím vyberte bunky, s ktorými by mal Riešiteľ pracovať. Všimnite si, že tieto sú všetky bunky to sa môže líšiť. Ak nechcete, aby sa bunka zmenila, nevyberajte ju.
Nastavte obmedzenia pre viac alebo jednotlivé premenné. Nakoniec sa dostávame k obmedzeniam. Tu je Solver skutočne silný. Namiesto zmeny ľubovoľnej bunky premenných na ľubovoľné číslo, ktoré chce, môžete zadať obmedzenia, ktoré je potrebné splniť. Podrobnosti nájdete v nižšie uvedenej časti o nastavení obmedzení.
Keď sú všetky tieto informácie na svojom mieste, kliknite na tlačidlo Vyriešiť dostať vašu odpoveď. Excel aktualizuje vaše údaje tak, aby obsahovali nové premenné (preto vám odporúčame najskôr vytvoriť kópiu údajov).

Môžete tiež generovať prehľady, na ktoré sa stručne pozrieme v našom nižšie uvedenom príklade Riešiteľa.

Ako nastaviť obmedzenia v riešení

Môžete povedať Excelu, že jedna premenná musí byť väčšia ako 200. Keď skúšate rôzne hodnoty premenných, Excel s touto konkrétnou premennou neprejde pod 201.

Ak chcete pridať obmedzenie, kliknite na Pridať tlačidlo vedľa zoznamu obmedzení. Dostanete nové okno. Vyberte bunku (alebo bunky), ktoré chcete obmedziť v priečinku Odkaz na bunku v poli, potom vyberte operátora.

Tu sú dostupní operátori:

<= (menšie alebo rovné)
= (rovná)
=> (väčšie alebo rovné)
int (musí byť celé číslo)
dopoludnia (musí byť buď 1 alebo 0)
Všetko rôzne

Všetko rôzne je trochu mätúce. Špecifikuje, že každá bunka v rozsahu, pre ktorý vyberiete Odkaz na bunku musí byť iné číslo. Ale tiež určuje, že musia byť medzi 1 a počtom buniek. Ak teda máte tri bunky, skončíte s číslami 1, 2 a 3 (nie však nevyhnutne v tomto poradí)

Nakoniec pridajte hodnotu obmedzenia.

Je dôležité mať na pamäti, že môžete vyberte viac buniek pre Cell Reference. Ak chcete, aby napríklad šesť premenných malo hodnoty vyššie ako 10, môžete ich vybrať všetky a povedať Riešiteľovi, že musia byť väčšie alebo rovné 11. Nemusíte pre každú bunku pridávať obmedzenie.

Môžete tiež použiť začiarkavacie políčko v hlavnom okne Riešiteľa, aby ste sa uistili, že všetky hodnoty, pre ktoré ste nezadali obmedzenia, nie sú záporné. Ak chcete, aby boli vaše premenné záporné, zrušte začiarknutie tohto políčka.

Príklad riešiteľa

Aby sme videli, ako to všetko funguje, použijeme na rýchly výpočet doplnok Riešiteľ. Tu sú údaje, s ktorými začíname:

V ňom máme päť rôznych zamestnaní, z ktorých každé platí inú sadzbu. Máme tiež počet hodín, ktoré teoretický pracovník odpracoval na každom z týchto zamestnaní v daný týždeň. Doplnok Solver môžeme použiť na zistenie, ako maximalizovať celkový plat pri zachovaní určitých premenných v rámci určitých obmedzení.

Tu sú obmedzenia, ktoré použijeme:

Bez práce môže klesnúť pod štyri hodiny.
Úloha 2 musí byť viac ako osem hodín .
Úloha 5 musí byť menej ako jedenásť hodín .
Celkový počet odpracovaných hodín musí byť rovná sa 40 .

Pred použitím Riešiteľa môže byť užitočné napísať si svoje obmedzenia týmto spôsobom.

Takto by sme to nastavili v Riešiči:

Najprv si to všimnite Vytvoril som kópiu tabuľky neprepíšeme teda ten pôvodný, ktorý obsahuje náš aktuálny pracovný čas.

A za druhé, zistite, že hodnoty v obmedzeniach viac ako a menej než sú jeden vyšší alebo nižší než som uviedol vyššie. Je to preto, že neexistujú možnosti väčšie alebo menšie. Existuje iba viac ako-alebo-rovnajúci sa a menej ako-alebo-rovný.

Poďme trafiť Vyriešiť a uvidíme, čo sa stane.

Riešiteľ našiel riešenie! Ako vidíte naľavo od okna vyššie, naše zárobky sa zvýšili o 130 dolárov. A všetky obmedzenia boli splnené.

Windows 7 na vykonanie tejto akcie potrebujete povolenie

Ak chcete zachovať nové hodnoty, uistite sa Ponechajte si riešenie Solver je skontrolovaný a zasiahnutý OK .

Ak však chcete viac informácií, môžete vybrať správu z pravej strany okna. Vyberte všetky požadované prehľady, povedzte Excelu, či ich chcete mať načrtnuté (odporúčam) a kliknite na tlačidlo OK .

Správy sa generujú na nové hárky vo vašom zošite a poskytujú vám informácie o procese, ktorým doplnok Doplňovač prešiel, aby získal vašu odpoveď.

V našom prípade správy nie sú veľmi vzrušujúce a nie je tam ani veľa zaujímavých informácií. Ak však spustíte komplikovanejšiu rovnicu Riešiteľa, v týchto nových pracovných listoch nájdete niekoľko užitočných informácií o vykazovaní. Stačí kliknúť na + tlačidlo na boku akejkoľvek správy, aby ste získali ďalšie informácie:

Riešiteľ Rozšírené možnosti

Ak toho o štatistikách veľa neviete, pokročilé možnosti Riešiteľa môžete ignorovať a jednoducho ich spustiť tak, ako sú. Ak však robíte veľké a zložité výpočty, možno by ste sa mali na ne pozrieť.

Najzrejmejšia je metóda riešenia:

Môžete si vybrať medzi GRG Nonlinear, Simplex LP a Evolutionary. Excel poskytuje jednoduché vysvetlenie, kedy by ste mali použiť každý z nich. Lepšie vysvetlenie vyžaduje určité znalosti štatistiky a regresie.

Ak chcete upraviť ďalšie nastavenia, stačí kliknúť na možnosti tlačidlo. Môžete povedať Excelu o celočíselnej optimálnosti, nastaviť časové obmedzenia výpočtu (užitočné pre rozsiahle množiny údajov) a upraviť spôsob, akým metódy GRG a evolučné riešenia postupujú pri výpočtoch.

Opäť platí, že ak neviete, čo to znamená, nebojte sa toho. Ak sa chcete dozvedieť viac o tom, akú metódu riešenia použiť, Engineer Excel má a dobrý článok, ktorý ti to napíše . Ak chcete maximálnu presnosť, Evolutionary je pravdepodobne dobrá cesta. Len si uvedomte, že to bude dlho trvať.

Hľadanie cieľa a riešiteľ: Posunutie Excelu na ďalšiu úroveň

Teraz, keď ste spokojní so základmi riešenia neznámych premenných v programe Excel, je vám otvorený úplne nový svet výpočtu tabuliek.

Hľadanie cieľov vám môže ušetriť čas tým, že niektoré výpočty urobí rýchlejšie, a Riešiteľ do toho pridáva obrovské množstvo energie Výpočtové schopnosti Excelu .

Ide len o to, aby ste sa s nimi cítili príjemne. Čím viac ich budete používať, tým budú užitočnejšie.

Používate vo svojich tabuľkách Hľadanie cieľa alebo Riešiteľ? Aké ďalšie tipy môžete poskytnúť, aby ste z nich získali najlepšie odpovede? Podeľte sa o svoje myšlienky v nižšie uvedených komentároch!

zdieľam zdieľam Tweet E -mail 5 tipov, ako nabiť svoje počítače VirtualBox Linux

Ste unavení z nízkeho výkonu, ktorý ponúkajú virtuálne počítače? Tu je to, čo by ste mali urobiť, aby ste zvýšili výkon svojho VirtualBoxu.

Čítajte ďalej Súvisiace témy

Produktivita
Tabuľka
Microsoft Excel
Tipy pre Microsoft Office

O autorovi Potom Albright(506 publikovaných článkov)

Dann je konzultant pre stratégiu obsahu a marketing, ktorý pomáha spoločnostiam vytvárať dopyt a potenciálnych zákazníkov. Píše tiež blogy o stratégii a obsahovom marketingu na dannalbright.com.

Viac od Danna Albrighta

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber