Lutasin ang function sa Excel. I-enable, use case na may mga screenshot

Ang "Search for a solution" ay isang Excel add-in, kung saan posible na pumili ng pinakamahusay na solusyon sa mga problema batay sa tinukoy na mga paghihigpit. Ginagawang posible ng function na mag-iskedyul ng mga empleyado, ipamahagi ang mga gastos o pamumuhunan. Ang pag-alam kung paano gumagana ang feature na ito ay makakatipid sa iyo ng oras at pagsisikap.

Ano ang Search for Solutions

Sa kumbinasyon ng iba't ibang mga pagpipilian sa Excel, mayroong isang hindi gaanong tanyag, ngunit lubhang kinakailangang function na "Maghanap ng solusyon". Sa kabila ng katotohanan na ang paghahanap nito ay hindi madali, ang pagkilala at paggamit nito ay nakakatulong sa paglutas ng maraming problema. Pinoproseso ng opsyon ang data at nagbibigay ng pinakamainam na solusyon mula sa mga pinapayagan. Inilalarawan ng artikulo kung paano direktang gumagana ang Search for a Solution.

Paano i-on ang feature na "Maghanap ng solusyon."

Sa kabila ng pagiging epektibo, ang pinag-uusapang opsyon ay wala sa isang kilalang lugar sa toolbar o menu ng konteksto. Karamihan sa mga user na nagtatrabaho sa Excel ay hindi alam ang presensya nito. Bilang default, ang function na ito ay hindi pinagana, upang ipakita ito, gawin ang mga sumusunod na aksyon:

1. Buksan ang "File" sa pamamagitan ng pag-click sa naaangkop na pangalan.
2. Mag-click sa seksyong "Mga Setting".
3. Pagkatapos ay piliin ang subsection na "Mga Add-on." Ang lahat ng mga add-on ng programa ay ipapakita dito, ang inskripsyon na "Pamamahala" ay lilitaw sa ibaba. Sa kanang bahagi nito ay magkakaroon ng pop-up menu kung saan dapat mong piliin ang "Excel Add-in". Pagkatapos ay i-click ang "Go".
   

   

4. Ang isang karagdagang window na "Mga Add-in" ay ipapakita sa monitor. Lagyan ng check ang kahon sa tabi ng nais na function at i-click ang OK.
5. Lalabas ang gustong function sa ribbon sa kanan ng seksyong “Data”.

Tungkol sa Mga Modelo

Ang impormasyong ito ay magiging lubhang kapaki-pakinabang para sa mga kakakilala pa lamang sa konsepto ng "modelo ng pag-optimize". Bago gamitin ang "Maghanap ng isang solusyon", inirerekumenda na pag-aralan ang mga materyales sa mga pamamaraan ng pagbuo ng mga modelo:

• ang opsyon na isinasaalang-alang ay magiging posible upang matukoy ang pinakamahusay na paraan upang maglaan ng mga pondo para sa mga pamumuhunan, pagkarga sa mga lugar, pagbibigay ng mga kalakal o iba pang mga aksyon kung saan kinakailangan upang mahanap ang pinakamahusay na solusyon.
• Ang "pinakamainam na paraan" sa ganitong sitwasyon ay mangangahulugan: pagtaas ng kita, pagbawas ng mga gastos, pagpapabuti ng kalidad, atbp.

Mga karaniwang gawain sa pag-optimize:

• Pagpapasiya ng isang plano sa produksyon, kung saan ang kita mula sa pagbebenta ng mga inilabas na kalakal ay magiging maximum.
• Pagpapasiya ng mga mapa ng transportasyon, kung saan ang mga gastos sa transportasyon ay pinaliit.
• Maghanap para sa pamamahagi ng ilang mga makina para sa iba't ibang uri ng trabaho, upang mabawasan ang mga gastos sa produksyon.
• Pagpapasiya ng pinakamaikling oras para sa pagkumpleto ng trabaho.

Mahalaga! Upang gawing pormal ang gawain, kinakailangan upang lumikha ng isang modelo na sumasalamin sa mga pangunahing parameter ng lugar ng paksa. Sa Excel, ang isang modelo ay isang hanay ng mga formula na gumagamit ng mga variable. Ang isinasaalang-alang na opsyon ay naghahanap ng mga naturang indicator na ang layunin ng function ay mas malaki (mas mababa) o katumbas ng tinukoy na halaga.

Yugto ng paghahanda

Bago maglagay ng function sa ribbon, kailangan mong maunawaan kung paano gumagana ang opsyon. Halimbawa, mayroong impormasyon sa pagbebenta ng mga kalakal na ipinahiwatig sa talahanayan. Ang gawain ay magtalaga ng diskwento para sa bawat item, na magiging 4.5 milyong rubles. Ang parameter ay ipinapakita sa loob ng isang cell na tinatawag na target. Batay dito, ang iba pang mga parameter ay kinakalkula.

Ang aming gawain ay upang kalkulahin ang diskwento kung saan ang mga kabuuan para sa pagbebenta ng iba't ibang mga produkto ay pinarami. Ang 2 elementong ito ay konektado sa pamamagitan ng isang formula na nakasulat tulad nito: =D13*$G$2. Kung saan sa D13 ang kabuuang dami para sa pagpapatupad ay nakasulat, at $G$2 ang address ng gustong elemento.

Gamit ang function at i-set up ito

Kapag handa na ang formula, kailangan mong gamitin ang mismong function nang direkta:

1. Kailangan mong lumipat sa seksyong "Data" at i-click ang "Maghanap ng solusyon".

2. Magbubukas ang "Mga Opsyon", kung saan nakatakda ang mga kinakailangang setting. Sa linyang "I-optimize ang layunin ng function:" dapat mong tukuyin ang cell kung saan ipinapakita ang kabuuan ng mga diskwento. Posibleng magreseta ng mga coordinate sa iyong sarili o pumili mula sa dokumento.

3. Susunod, kailangan mong pumunta sa mga setting ng iba pang mga parameter. Sa seksyong “Para kay:,” posibleng itakda ang maximum at minimum na limitasyon o eksaktong numero.

4. Pagkatapos ang patlang na "Pagbabago ng mga halaga ng mga variable:" ay napunan. Dito ipinasok ang data ng nais na cell, na naglalaman ng isang tiyak na halaga. Ang mga coordinate ay nakarehistro nang nakapag-iisa o ang kaukulang cell sa dokumento ay na-click.

5. Pagkatapos ay ang tab na "Ayon sa mga paghihigpit:" ay na-edit, kung saan nakatakda ang mga paghihigpit sa inilapat na data. Halimbawa, ang mga decimal fraction o negatibong numero ay hindi kasama.

6. Pagkatapos nito, bubukas ang isang window na nagbibigay-daan sa iyo upang magdagdag ng mga paghihigpit sa mga kalkulasyon. Ang paunang linya ay naglalaman ng mga coordinate ng isang cell o isang buong hanay. Kasunod ng mga kondisyon ng gawain, ang data ng nais na cell ay ipinahiwatig, kung saan ipinapakita ang tagapagpahiwatig ng diskwento. Pagkatapos ay tinutukoy ang tanda ng paghahambing. Ito ay nakatakda sa "mas malaki kaysa sa o katumbas ng" upang ang panghuling halaga ay hindi may minus sign. Ang "Limit" na itinakda sa linya 3 ay 0 sa sitwasyong ito. Posible ring magtakda ng limitasyon gamit ang "Magdagdag". Ang mga susunod na hakbang ay pareho.

7. Kapag nakumpleto na ang mga hakbang sa itaas, lalabas ang itinakdang limitasyon sa pinakamalaking linya. Ang listahan ay maaaring malaki at depende sa pagiging kumplikado ng mga kalkulasyon, gayunpaman, sa isang partikular na sitwasyon, 1 kundisyon ay sapat.

8. Bilang karagdagan, posible na pumili ng iba pang mga advanced na setting. Sa kanang bahagi sa ibaba ay mayroong opsyon na "Mga Opsyon" na nagpapahintulot sa iyo na gawin ito.

9. Sa mga setting, maaari mong itakda ang "Katumpakan ng limitasyon" at "Mga limitasyon ng solusyon". Sa aming sitwasyon, hindi na kailangang gamitin ang mga opsyong ito.

10. Kapag nakumpleto na ang mga setting, magsisimula ang function mismo - i-click ang "Maghanap ng solusyon".

11. Matapos maisagawa ng programa ang mga kinakailangang kalkulasyon at mag-isyu ng mga huling kalkulasyon sa mga kinakailangang cell. Pagkatapos ay isang window na may mga resulta ay bubukas, kung saan ang mga resulta ay nai-save / kinansela, o ang mga parameter ng paghahanap ay na-configure ayon sa isang bago. Kapag natugunan ng data ang mga kinakailangan, ang nahanap na solusyon ay nai-save. Kung lagyan mo ng check ang kahon ng "Bumalik sa dialog box ng mga pagpipilian sa paghahanap ng solusyon," magbubukas ang isang window na may mga setting ng function.

12. May posibilidad na ang mga kalkulasyon ay naging mali o may pangangailangan na baguhin ang paunang data upang makakuha ng iba pang mga tagapagpahiwatig. Sa ganoong sitwasyon, kailangan mong buksang muli ang window ng mga setting at i-double check ang impormasyon.
13. Kapag tumpak ang data, maaaring gumamit ng alternatibong paraan. Para sa mga layuning ito, kailangan mong mag-click sa kasalukuyang opsyon at piliin ang pinaka-angkop na paraan mula sa lilitaw na listahan:

• Paghahanap ng Solusyon Gamit ang Generalized Gradient para sa mga Nonlinear na Problema. Bilang default, ginagamit ang opsyong ito, ngunit posibleng gumamit ng iba.
• Paghahanap ng mga solusyon para sa mga linear na problema batay sa simplex na pamamaraan.
• Paggamit ng ebolusyonaryong paghahanap upang makumpleto ang isang gawain.

Atensyon! Kapag ang mga pagpipilian sa itaas ay nabigo upang makayanan ang gawain, dapat mong suriin muli ang data sa mga setting, dahil ito ang madalas na pangunahing pagkakamali sa mga naturang gawain.

14. Kapag natanggap ang nais na diskwento, nananatili itong ilapat upang kalkulahin ang halaga ng mga diskwento para sa bawat item. Para sa layuning ito, ang paunang elemento ng hanay na "Halaga ng Diskwento" ay naka-highlight, ang formula ay nakasulat "=D2*$G$2" at pindutin ang "Enter". Ang mga palatandaan ng dolyar ay inilalagay upang kapag ang formula ay naunat sa mga katabing linya, ang G2 ay hindi nagbabago.

15. Ang halaga ng diskwento para sa paunang item ay makukuha na ngayon. Pagkatapos ay dapat mong ilipat ang cursor sa sulok ng cell, kapag ito ay naging isang "plus", ang LMB ay pinindot at ang formula ay nakaunat sa mga kinakailangang linya.
16. Pagkatapos nito, sa wakas ay handa na ang mesa.

I-load/I-save ang Mga Opsyon sa Paghahanap

Ang pagpipiliang ito ay kapaki-pakinabang kapag nag-aaplay ng iba't ibang mga pagpipilian sa pagpilit.

1. Sa menu ng Solution Finder Options, i-click ang I-load/I-save.
2. Ilagay ang hanay para sa lugar ng modelo at i-click ang I-save o I-load.

Kapag nagse-save ng modelo, may ipinapasok na reference sa 1 cell ng isang walang laman na column kung saan ilalagay ang modelo ng pag-optimize. Sa panahon ng paglo-load ng modelo, isang reference ang ipinasok sa buong hanay na naglalaman ng modelo ng pag-optimize.

Mahalaga! Upang i-save ang mga huling setting sa menu ng Mga Opsyon sa Solusyon, isang workbook ang ise-save. Ang bawat sheet sa loob nito ay may sariling Solver add-in na mga opsyon. Bilang karagdagan, posibleng magtakda ng higit sa 1 gawain para sa isang sheet sa pamamagitan ng pag-click sa pindutang "I-load o I-save" upang i-save ang mga indibidwal na gawain.

Isang simpleng halimbawa ng paggamit ng Solver

Kinakailangan na i-load ang lalagyan na may mga lalagyan upang ang masa nito ay maximum. Ang tangke ay may dami na 32 metro kubiko. m. Ang isang puno na kahon ay may timbang na 20 kg, ang dami nito ay 0,15 metro kubiko. m. Kahon – 80 kg at 0,5 cu. m. Kinakailangan na ang kabuuang bilang ng mga lalagyan ay hindi bababa sa 110 mga PC. Ang data ay nakaayos tulad nito:

Ang mga variable ng modelo ay minarkahan ng berde. Ang layunin ng function ay naka-highlight sa pula. Mga Paghihigpit: ayon sa pinakamaliit na bilang ng mga lalagyan (mas malaki sa o katumbas ng 110) at ayon sa timbang (=SUMPRODUCT(B8:C8,B6:C6) – kabuuang bigat ng tare sa lalagyan.

Sa pamamagitan ng pagkakatulad, isinasaalang-alang namin ang kabuuang dami: =SUMPRODUCT(B7:C7,B8:C8). Ang ganitong formula ay kinakailangan upang magtakda ng limitasyon sa kabuuang dami ng mga lalagyan. Pagkatapos, sa pamamagitan ng "Search for a solution", ang mga link ay ipinapasok sa mga elemento na may mga variable, formula at ang mga indicator mismo (o mga link sa mga partikular na cell). Siyempre, ang bilang ng mga lalagyan ay isang integer (isa rin itong limitasyon). Pinindot namin ang "Maghanap ng solusyon", bilang isang resulta kung saan nahanap namin ang isang bilang ng mga lalagyan kapag ang kabuuang masa ay maximum at ang lahat ng mga paghihigpit ay isinasaalang-alang.

Nabigo ang paghahanap para sa solusyon upang makahanap ng mga solusyon

Lumalabas ang naturang notification kapag ang pinag-uusapang function ay hindi nakahanap ng mga kumbinasyon ng mga variable na marka na nakakatugon sa bawat hadlang. Kapag gumagamit ng Simplex na paraan, ito ay lubos na posible na walang solusyon.

Kapag ang isang paraan para sa paglutas ng mga problemang hindi linear ay ginamit, sa lahat ng mga kaso simula sa mga paunang tagapagpahiwatig ng mga variable, ito ay nagpapahiwatig na ang posibleng solusyon ay malayo sa naturang mga parameter. Kung patakbuhin mo ang function sa iba pang mga paunang tagapagpahiwatig ng mga variable, malamang na mayroong isang solusyon.

Halimbawa, kapag gumagamit ng non-linear na pamamaraan, ang mga elemento ng talahanayan na may mga variable ay hindi napuno, at ang function ay hindi nakahanap ng mga solusyon. Hindi ito nangangahulugan na walang solusyon. Ngayon, isinasaalang-alang ang mga resulta ng isang tiyak na pagtatasa, ang iba pang data ay ipinasok sa mga elemento na may mga variable na malapit sa mga natanggap.

Sa anumang sitwasyon, dapat mo munang suriin ang modelo para sa kawalan ng salungatan sa pagpilit. Kadalasan, ito ay magkakaugnay sa hindi tamang pagpili ng ratio o ang tagapagpahiwatig ng paglilimita.

Sa halimbawa sa itaas, ang maximum na volume indicator ay 16 cubic meters. m sa halip na 32, dahil ang gayong paghihigpit ay sumasalungat sa mga tagapagpahiwatig para sa pinakamababang bilang ng mga upuan, dahil ito ay tumutugma sa bilang ng 16,5 metro kubiko. m.

Konklusyon

Batay dito, ang opsyon na "Maghanap ng solusyon" sa Excel ay makakatulong sa paglutas ng mga partikular na problema na medyo mahirap o imposibleng lutasin sa mga karaniwang paraan. Ang kahirapan sa paglalapat ng pamamaraang ito ay sa simula ay nakatago ang opsyong ito, kaya naman hindi alam ng karamihan sa mga user ang presensya nito. Bilang karagdagan, ang pag-andar ay medyo mahirap matutunan at gamitin, ngunit sa wastong pananaliksik, ito ay magdadala ng mahusay na mga benepisyo at mapadali ang mga kalkulasyon.