Gadzirisa basa muExcel. Gonesa, shandisa kesi ine skrini

"Tsvaga mhinduro" ndeyeExcel-in-in, kuburikidza iyo zvinogoneka kusarudza mhinduro yakanakisa yezvinetso zvinoenderana nezvinorambidzwa. Basa racho rinoita kuti zvikwanise kuronga vashandi, kugovera mari kana mari. Kuziva mashandiro anoita chikamu ichi kuchakuchengetedza nguva nesimba.

Chii chinonzi Search for Solutions

Mukubatana nedzimwe sarudzo dzakasiyana muExcel, pane imwe isinganyanyozivikanwa, asi inonyanya kukosha basa "Tsvaga mhinduro". Pasinei nechokwadi chokuti kuiwana hakusi nyore, kusvika pakuzviziva uye kuishandisa kunobatsira mukugadzirisa matambudziko akawanda. Iyo sarudzo inobata iyo data uye inopa yakakwana mhinduro kubva kune inobvumidzwa. Chinyorwa chinotsanangura kuti Kutsvaga Kwemhinduro kunoshanda sei zvakananga.

Mabatidza sei "Tsvaga mhinduro".

Pasinei nekushanda, sarudzo iri mubvunzo haisi munzvimbo ine mukurumbira pane bhari rekushandisa kana menyu yemamiriro. Vazhinji vashandisi vanoshanda muExcel havazive nezvekuvapo kwayo. Nekumisikidza, basa iri rakavharwa, kuti riratidze, ita zvinotevera zviito:

1. Vhura iyo "Faira" nekudzvanya pazita rakakodzera.
2. Dzvanya pachikamu che "Settings".
3. Zvadaro sarudza chikamu che "Add-ons". Yese ma-add-ons echirongwa acharatidzwa pano, iyo zvakanyorwa "Management" ichaonekwa pazasi. Kurutivi rworudyi rwayo pachava ne-pop-up menyu apo iwe unofanirwa kusarudza "Excel Add-ins". Wobva wadzvanya "Enda".
   

   

4. Imwe hwindo rekuwedzera "Add-ins" richaratidzwa pamonitor. Tarisa bhokisi riri padyo nebasa raunoda uye tinya OK.
5. Basa rinodiwa richaonekwa paribhoni kurudyi rwechikamu che "Data".

About Models

Ruzivo urwu ruchabatsira zvakanyanya kune avo vari kungojairana nepfungwa ye "optimization modhi". Usati washandisa "Tsvaga mhinduro", zvinokurudzirwa kuti udzidze zvishandiso pamitoo yekuvaka mhando:

• iyo sarudzo iri kutariswa ichaita kuti zvikwanise kuona nzira yakanakisa yekugovera mari yekudyara, kurodha nzvimbo, kugovera zvinhu kana zvimwe zviito pazvinenge zvichidikanwa kuwana mhinduro yakanaka.
• Iyo "yakanyanya nzira" mumamiriro ezvinhu akadaro ingareva: kuwedzera mari, kuderedza mutengo, kuvandudza mhando, nezvimwe.

Yakajairika optimization mabasa:

• Kutsunga kwechirongwa chekugadzira, panguva iyo purofiti kubva pakutengeswa kwezvinhu zvakaburitswa ichave yakanyanya.
• Kutemerwa kwemamepu ekufambisa, panguva iyo mari yekufambisa inoderedzwa.
• Tsvaga kugoverwa kwemichina yakawanda yemhando dzakasiyana dzebasa, kuitira kuti mari yekugadzira ideredzwe.
• Kutsunga kwenguva pfupi yekupedza basa.

Zvinokosha! Kuti ugadzirise basa racho, zvinodikanwa kugadzira modhi inoratidza iwo makuru paramita enzvimbo yenyaya. MuExcel, modhi seti yemafomula anoshandisa zvinoshanduka. Iyo yakatariswa sarudzo inotarisa zviratidzo zvakadaro kuti chinangwa basa rakakura (shoma) kana kuenzana nemutengo wakatarwa.

Chikamu chekugadzirira

Usati waisa basa pane ribhoni, unofanirwa kunzwisisa kuti sarudzo inoshanda sei. Semuenzaniso, kune ruzivo pamusoro pekutengeswa kwezvinhu zvakaratidzwa mutafura. Basa nderekugovera kuderedzwa kwechinhu chimwe nechimwe, icho chingave 4.5 miriyoni rubles. Iyo parameter inoratidzwa mukati mesero inonzi target. Kubva pairi, mamwe ma parameter anoverengwa.

Basa redu richava rekuverenga kuderedzwa uko mari dzekutengesa kwezvigadzirwa zvakasiyana dzinowedzerwa. Izvi zvinhu zviviri zvakabatanidzwa neformula yakanyorwa seizvi: =D13*$G$2. Iko muD13 huwandu hwese hwekushandiswa hwakanyorwa, uye $ G $ 2 ndiyo kero yechinhu chinodiwa.

Kushandisa basa uye kurimisa

Kana iyo formula yagadzirira, iwe unofanirwa kushandisa iyo pachayo pachayo zvakananga:

1. Iwe unofanirwa kushandura kune "Data" chikamu uye tinya "Tsvaga mhinduro".

2. I "Options" ichazarura, apo zvigadziro zvinodiwa zvakagadzirirwa. Mumutsara we "Optimize basa rechinangwa:" iwe unofanirwa kutsanangura sero panoratidzwa huwandu hwezvakaderedzwa. Zvinogoneka kuti unyore zvinorongeka iwe pachako kana kusarudza kubva mugwaro.

3. Tevere, iwe unofanirwa kuenda kune zvigadziriso zvemamwe ma parameter. Muchikamu che "Kuti:", zvinokwanisika kuseta huwandu uye hudiki miganhu kana nhamba chaiyo.

4. Ipapo iyo munda "Kuchinja kukosha kwezvakasiyana:" inozadzwa. Pano iyo data yesero inodiwa inopindirwa, iyo ine yakakosha kukosha. Coordinates anonyoreswa akazvimirira kana sero rinoenderana mugwaro rinodzvanywa.

5. Zvadaro tebhu "Maererano nezvirambidzo:" inogadziriswa, apo zvinorambidzwa pane data rakashandiswa zvinoiswa. Semuenzaniso, zvikamu zvedesimali kana nhamba dzisina kunaka hazvibatanidzwe.

6. Mushure meizvozvo, hwindo rinovhura iro rinokutendera kuti uwedzere zvirambidzo mukuverenga. Mutsara wekutanga une macoordinates esero kana renji yese. Kutevera mamiriro ebasa racho, iyo data yesero inodiwa inoratidzwa, uko chiratidzo chekuderedzwa chinoratidzwa. Ipapo chiratidzo chekuenzanisa chinotemwa. Yakaiswa ku "zvikuru kupfuura kana kuenzana" kuitira kuti kukosha kwekupedzisira kusava nechiratidzo chekubvisa. Iyo "Limit" yakaiswa mumutsara 3 ndeye 0 mumamiriro ezvinhu aya. Zvinokwanisikawo kuisa muganhu ne "Wedzera". Matanho anotevera akafanana.

7. Kana matanho ari pamusoro apedzwa, muganho wakaiswa unooneka mumutsara mukuru. Rondedzero yacho inogona kuva yakakura uye ichaenderana nekuoma kwekuverenga, zvisinei, mune imwe mamiriro ezvinhu, 1 mamiriro akakwana.

8. Mukuwedzera, zvinokwanisika kusarudza mamwe magadzirirwo epamusoro. Pazasi kurudyi pane sarudzo "Sarudzo" inokubvumira kuita izvi.

9. Mune zvigadziriso, unogona kuseta iyo "Kuganhurirwa kurongeka" uye "Solution miganho". Mumamiriro edu ezvinhu, hapana chikonzero chekushandisa izvi zvingasarudzwa.

10. Kana zvigadziriso zvapedzwa, basa racho pacharo rinotanga - tinya "Tsvaga mhinduro".

11. Mushure mokunge purogiramu ichiita masvomhu anodiwa uye inopa maverengero ekupedzisira mumasero anodiwa. Ipapo hwindo rine mhedzisiro rinovhura, uko mhedzisiro inochengetwa / yakadzimwa, kana maparamita ekutsvaga anogadziriswa zvinoenderana neitsva. Kana iyo data ikasangana nezvinodiwa, mhinduro inowanikwa inochengetwa. Kana iwe ukatarisa bhokisi re "Dzokera kune mhinduro yekutsvaga dialog box" bhokisi risati rasvika, hwindo rine basa rekugadzirisa richazarura.

12. Pane mukana wekuti maverengero akave akakanganisa kana pane chikonzero chekushandura data rekutanga kuitira kuti uwane zvimwe zviratidzo. Mumamiriro ezvinhu akadaro, unofanirwa kuvhurazve hwindo rezvirongwa uye kaviri-tarisa ruzivo.
13. Kana iyo data iri yakarurama, imwe nzira inogona kushandiswa. Nezvinangwa izvi, iwe unofanirwa kudzvanya pane yazvino sarudzo uye sarudza yakanyatsokodzera nzira kubva pane inooneka:

• Kutsvaga Mhinduro Kushandisa Generalized Gradient yeMatambudziko Asina Mutsara. Nokusingaperi, iyi sarudzo inoshandiswa, asi zvinokwanisika kushandisa vamwe.
• Kutsvaga mhinduro dzematambudziko emutsara zvichienderana neiyo simplex nzira.
• Kushandisa evolutionary search kupedzisa basa.

Chenjerera! Kana izvo zviri pamusoro zvakatadza kutarisana nebasa, unofanirwa kutarisa iyo data muzvirongwa zvakare, sezvo ichi kazhinji ndicho chikanganiso chikuru mumabasa akadaro.

14. Kana iyo yaunoda kuderedzwa yagamuchirwa, inoramba ichiiisa kuti uverenge huwandu hwekuderedzwa kwechinhu chimwe nechimwe. Nechinangwa ichi, chinhu chekutanga chekoramu "Discount Amount" inosimbiswa, iyo formula yakanyorwa «=D2*$G$2» uye tinya "Enter". Zviratidzo zvemadhora zvinoiswa pasi kuitira kuti kana fomula yakatambanudzwa kune mitsetse iri padyo, G2 haishanduki.

15. Mari yekuderedzwa kwechinhu chekutanga ikozvino ichawanikwa. Ipapo iwe unofanirwa kufambisa chitubu pamusoro pekona yesero, kana yava "plus", iyo LMB inomanikidzwa uye fomuro inotambanudzirwa kune mitsara inodiwa.
16. Mushure maizvozvi, tafura ichave yakagadzirira.

Rodha/Chengetedza Sarudzo dzeKutsvaga

Iyi sarudzo inobatsira kana uchishandisa akasiyana constraining sarudzo.

1. Mune Solution Finder Options menyu, tinya Rodha/Chengetedza.
2. Pinda huwandu hwenzvimbo yemuenzaniso uye tinya Sevha kana Rodha.

Paunenge uchichengetedza modhi, referensi inoiswa kune 1 sero yekoramu isina chinhu uko iyo optimization modhi ichaiswa. Panguva yekurodha modhi, referensi inopinzwa kune yese siyana iyo ine optimization modhi.

Zvinokosha! Kuchengeta marongero ekupedzisira muMenu yeSolution Options, bhuku rebasa rinochengetwa. Pepa rega rega mariri rine zvaro Solver yekuwedzera-musarudzo. Pamusoro pezvo, zvinokwanisika kuseta rinopfuura 1 basa repepa nekudzvanya bhatani re "Road or Sevha" kuitira kuchengetedza mabasa ega.

Muenzaniso wakapfava wekushandisa Solver

Izvo zvinodikanwa kutakura mudziyo nemidziyo kuitira kuti huwandu hwayo huve hwakanyanya. Tangi ine huwandu hwe32 cubic metres. m. Bhokisi rakazadzwa rine huremu hwemakirogiramu makumi maviri, huwandu hwayo hunosvika 20 cubic metres. m. Bhokisi - 0,15 kg uye 80 cu. m. Izvo zvinodikanwa kuti iyo yakazara nhamba yemidziyo inenge 0,5 pcs. Iyo data yakarongwa seizvi:

Mienzaniso yakasiyana-siyana inoratidzwa negirini. Basa rechinangwa rinoratidzwa mutsvuku. Zvirambidzo: nenhamba diki yemidziyo (yakakura kupfuura kana yakaenzana ne110) uye nehuremu (=SUMPRODUCT(B8:C8,B6:C6) - huremu hwese hwetare mumudziyo.

Nekufananidza, tinotarisa huwandu hwehuwandu: =SUMPRODUCT(B7:C7,B8:C8). Formula yakadaro inofanirwa kuseta muganho pahuwandu hwehuwandu hwemidziyo. Zvadaro, kuburikidza ne "Tsvaga mhinduro", zvinongedzo zvinopinzwa kune zvinhu zvine zvinoshanduka, mafomula uye zviratidzi pachazvo (kana zvinongedzo kune chaiwo maseru). Ehezve, huwandu hwemidziyo iverengeka (iyo zvakare inogumira). Isu tinodzvanya "Tsvaga mhinduro", semhedzisiro yatinowana yakawanda yakadaro yemidziyo kana huwandu hwese hwakanyanya uye zvirambidzo zvese zvinotariswa.

Kutsvaga mhinduro kwatadza kuwana mhinduro

Chiziviso chakadaro chinobuda kana basa ririmubvunzo risati rawana misanganiswa yezvibodzwa zvakasiyana zvinogutsa chisungo chimwe nechimwe. Paunenge uchishandisa iyo Simplex nzira, zvinogoneka kuti hapana mhinduro.

Kana nzira yekugadzirisa matambudziko asina mutsara inoshandiswa, mumatambudziko ose kutanga kubva kune zviratidzo zvekutanga zvezvakasiyana-siyana, izvi zvinoratidza kuti mhinduro inogoneka iri kure nemiganhu yakadaro. Kana iwe uchimhanyisa basa nezvimwe zviratidzo zvekutanga zvezvakasiyana, saka pane pamwe pane mhinduro.

Semuenzaniso, pakushandisa nzira isina mutsara, zvinhu zvetafura ine zvigadziriswa hazvina kuzadzwa, uye basa racho harina kuwana mhinduro. Izvi hazvirevi kuti hapana mhinduro. Iye zvino, tichifunga nezvemibairo yeimwe ongororo, imwe data inopinzwa muzvinhu zvine machinjiro ari padyo neaya anogamuchirwa.

Chero mamiriro ezvinhu, iwe unofanirwa kutanga waongorora muenzaniso wekushayikwa kwekupokana kwekumanikidza. Kazhinji, izvi zvakabatana nekusarudzwa kwakashata kwereshiyo kana chiratidzo chekumisa.

Mumuenzaniso wepamusoro, iyo yakanyanya vhoriyamu chiratidzo ndeye 16 cubic metres. m panzvimbo ye32, nokuti chirambidzo chakadaro chinopesana nezviratidzo zvehuwandu hushoma hwezvigaro, sezvo huchaenderana nehuwandu hwe16,5 cubic metres. m.

mhedziso

Kubva pane izvi, sarudzo ye "Tsvaga mhinduro" muExcel ichabatsira mukugadzirisa matambudziko akanyanya kuoma kana kusakwanisa kugadzirisa nenzira dzakajairwa. Dambudziko rekushandisa nzira iyi nderekuti pakutanga sarudzo iyi yakavanzwa, ndosaka vashandisi vazhinji vasingazive nezvekuvapo kwayo. Mukuwedzera, basa racho rakaoma chaizvo kudzidza uye kushandisa, asi nekutsvakurudza kwakakodzera, zvinounza zvikomborero zvikuru uye kufambisa kuverenga.