Konzani ntchito mu Excel. Yambitsani, gwiritsani ntchito ndi zowonera

"Sakani yankho" ndi chowonjezera cha Excel, chomwe chimatha kusankha njira yabwino yothetsera mavuto potengera zoletsa zomwe zatchulidwa. Ntchitoyi imathandizira kukonza antchito, kugawa ndalama kapena ndalama. Kudziwa momwe gawoli limagwirira ntchito kukupulumutsirani nthawi ndi khama.

Kodi Search for Solutions ndi chiyani

Kuphatikiza ndi zosankha zina zosiyanasiyana mu Excel, pali ntchito imodzi yocheperako, koma yofunika kwambiri "Sakani yankho". Ngakhale kuti kuchipeza sikophweka, kuchidziwa ndi kuchigwiritsa ntchito kumathandiza kuthetsa mavuto ambiri. Njirayi imayendetsa deta ndikupereka yankho labwino kwambiri kuchokera kwa omwe amaloledwa. Nkhaniyi ikufotokoza momwe Search for Solution imagwirira ntchito mwachindunji.

Momwe mungayatse gawo la "Sakani yankho".

Ngakhale zili zogwira mtima, njira yomwe ikufunsidwayo siili pamalo otchuka pazida kapena menyu. Ogwiritsa ntchito ambiri omwe amagwira ntchito ku Excel sadziwa za kupezeka kwake. Mwachikhazikitso, ntchitoyi imayimitsidwa, kuti muwonetse, chitani zotsatirazi:

1. Tsegulani "Fayilo" ndikudina dzina loyenera.
2. Dinani pa "Zikhazikiko" gawo.
3. Kenako sankhani kachigawo "Zowonjezera". Zowonjezera zonse za pulogalamuyi zidzawonetsedwa apa, mawu akuti "Management" awonekera pansipa. Kumanja kwa izo padzakhala pop-up menyu kumene muyenera kusankha "Excel Add-ins". Kenako dinani "Pitani".
   

   

4. Zowonjezera zenera "Add-ins" zidzawonetsedwa pa polojekiti. Chongani bokosi pafupi ndi ntchito ankafuna ndi kumadula OK.
5. Ntchito yomwe mukufuna idzawonekera pa riboni kumanja kwa gawo la "Data".

Za Zitsanzo

Chidziwitsochi chidzakhala chothandiza kwambiri kwa iwo omwe akungodziwa lingaliro la "kukhathamiritsa chitsanzo". Musanagwiritse ntchito "Fufuzani yankho", tikulimbikitsidwa kuti tiphunzire zida za njira zomangira:

• njira yomwe ikuganiziridwa idzapangitsa kuti zitheke kuzindikira njira yabwino kwambiri yoperekera ndalama zogulira, kukweza malo, kupereka katundu kapena zina zomwe zimayenera kupeza njira yabwino yothetsera.
• "Njira yabwino" muzochitika zotere ingatanthauze: kuonjezera ndalama, kuchepetsa ndalama, kuwongolera khalidwe, ndi zina zotero.

Zochita zokhazikika bwino:

• Kutsimikiza kwa dongosolo lopangira, pomwe phindu pakugulitsa zinthu zotulutsidwa lidzakhala lalikulu.
• Kutsimikiza kwa mamapu amayendedwe, pomwe ndalama zoyendera zimachepetsedwa.
• Fufuzani kugawa makina angapo amitundu yosiyanasiyana ya ntchito, kuti ndalama zopangira zichepetse.
• Kutsimikiza kwa nthawi yochepa kwambiri yomaliza ntchito.

Zofunika! Kuti ntchitoyi ikhale yokhazikika, ndikofunikira kupanga chitsanzo chomwe chikuwonetsa magawo akulu a phunzirolo. Mu Excel, chitsanzo ndi gulu la mafomu omwe amagwiritsa ntchito zosinthika. Njira yomwe yaganiziridwa imayang'ana zizindikiro zomwe cholinga chake ndi chachikulu (chochepa) kapena chofanana ndi mtengo womwe watchulidwa.

Gawo lokonzekera

Musanayike ntchito pa riboni, muyenera kumvetsetsa momwe njirayo imagwirira ntchito. Mwachitsanzo, pali zambiri zokhudza kugulitsa katundu zomwe zasonyezedwa patebulo. Ntchito yake ndikupereka kuchotsera pa chinthu chilichonse, chomwe chingakhale ma ruble 4.5 miliyoni. Parameter imawonetsedwa mkati mwa cell yotchedwa target. Malingana ndi izo, magawo ena amawerengedwa.

Ntchito yathu ikhala kuwerengera kuchotsera komwe ndalama zogulitsa zinthu zosiyanasiyana zimachulukitsidwa. Zinthu ziwirizi zimalumikizidwa ndi fomula yolembedwa motere: =D13*$G$2. Komwe mu D13 kuchuluka kwathunthu kwa kukhazikitsa kwalembedwa, ndipo $G $2 ndi adilesi ya chinthu chomwe mukufuna.

Kugwiritsa ntchito ndi kukhazikitsa

Fomula ikakonzeka, muyenera kugwiritsa ntchito ntchitoyo mwachindunji:

1. Muyenera kusinthana ndi gawo la "Data" ndikudina "Fufuzani yankho".

2. "Zosankha" zidzatsegulidwa, pomwe zosintha zofunika zimayikidwa. Pamzere wa "Optimize the goal function:" muyenera kutchula foni yomwe kuchuluka kwa kuchotsera kumawonetsedwa. Ndizotheka kudzipangira nokha ma coordinates kapena kusankha kuchokera pachikalatacho.

3. Kenako, muyenera kupita ku zoikamo magawo ena. Mu gawo la "Kuti:", ndizotheka kukhazikitsa malire apamwamba komanso ochepera kapena nambala yeniyeni.

4. Kenako gawo "Kusintha kwamitundu yosiyanasiyana" ladzazidwa. Apa deta ya selo yofunidwa imalowetsedwa, yomwe ili ndi mtengo wapadera. Ma coordinates amalembetsedwa paokha kapena cell yofananira mu chikalatacho yadina.

5. Kenako tabu "Malinga ndi zoletsa" imasinthidwa, pomwe zoletsa zomwe zimagwiritsidwa ntchito zimayikidwa. Mwachitsanzo, zigawo za decimal kapena manambala olakwika saphatikizidwa.

6. Pambuyo pake, zenera limatsegulidwa lomwe limakupatsani mwayi wowonjezera zoletsa pakuwerengera. Mzere woyamba uli ndi zolumikizira za selo kapena gulu lonse. Kutsatira zikhalidwe za ntchitoyi, deta ya cell yomwe mukufuna ikuwonetsedwa, pomwe chizindikiro chochotsera chikuwonetsedwa. Ndiye chizindikiro chofananitsa chimatsimikiziridwa. Aikidwa kukhala “wamkulu kuposa kapena wofanana ndi” kotero kuti mtengo womalizira usakhale ndi chizindikiro chochotsera. "Malire" omwe ali mu mzere 3 ndi 0 muzochitika izi. N'zothekanso kukhazikitsa malire ndi "Add". Masitepe otsatirawa ndi ofanana.

7. Pamene masitepe omwe ali pamwambawa atsirizidwa, malire oikidwa amawonekera pamzere waukulu kwambiri. Mndandanda ukhoza kukhala waukulu ndipo udzadalira zovuta za mawerengedwe, komabe, muzochitika zinazake, chikhalidwe cha 1 ndi chokwanira.

8. Komanso, n'zotheka kusankha zina zapamwamba. Pansi kumanja pali njira "Zosankha" zomwe zimakulolani kuchita izi.

9. Muzokonda, mutha kukhazikitsa "Kulondola kwa malire" ndi "Malire a yankho". M'mikhalidwe yathu, palibe chifukwa chogwiritsa ntchito zosankhazi.

10. Zokonda zikamalizidwa, ntchitoyo imayamba - dinani "Pezani yankho".

11. Pambuyo pa pulogalamuyo imachita mawerengedwe ofunikira ndikupereka mawerengedwe omaliza m'maselo ofunikira. Kenako zenera lokhala ndi zotsatira limatsegulidwa, pomwe zotsatira zimasungidwa / kuthetsedwa, kapena magawo osakira amakonzedwa molingana ndi watsopano. Deta ikakwaniritsa zofunikira, yankho lopezeka limasungidwa. Ngati muyang'ana bokosi la "Bwererani ku bokosi lofufuzira njira zothetsera" pasadakhale, zenera lokhala ndi ntchito lidzatsegulidwa.

12. Pali kuthekera kuti mawerengedwewo adakhala olakwika kapena pakufunika kusintha deta yoyambirira kuti mupeze zizindikiro zina. Zikatero, muyenera kutsegulanso zenera la zoikamo ndikuwunikanso zambiri.
13. Deta ikakhala yolondola, njira ina ingagwiritsidwe ntchito. Pazifukwa izi, muyenera dinani pazomwe zilipo ndikusankha njira yoyenera kwambiri pamndandanda womwe ukuwoneka:

• Kupeza Yankho Pogwiritsa Ntchito Gradient Yokhazikika Pamavuto Osagwirizana. Mwachikhazikitso, njirayi imagwiritsidwa ntchito, koma ndizotheka kugwiritsa ntchito zina.
• Kupeza mayankho amavuto amzere kutengera njira ya simplex.
• Kugwiritsa ntchito kusaka kwachisinthiko kumaliza ntchito.

Chenjerani! Zosankha zomwe zili pamwambazi zikalephera kuthana ndi ntchitoyi, muyenera kuyang'ananso zomwe zili muzokonda, chifukwa nthawi zambiri ndiye cholakwika chachikulu pantchito zotere.

14. Pamene kuchotsera komwe mukufuna kulandilidwa, kumatsalira kuti mugwiritse ntchito kuwerengera kuchuluka kwa kuchotsera kwa chinthu chilichonse. Pachifukwa ichi, gawo loyambirira la gawo la "Kuchotsera Kuchotsera" likuwunikira, ndondomekoyo imalembedwa «=D2*$G$2» ndipo dinani "Enter". Zizindikiro za dollar zimayikidwa pansi kotero kuti fomuyo ikatambasulidwa ku mizere yoyandikana nayo, G2 sisintha.

15. Kuchotsera kwa chinthu choyambirira tsopano kupezedwa. Ndiye muyenera kusuntha cholozera pa ngodya ya selo, pamene imakhala "kuphatikiza", LMB imakanizidwa ndipo ndondomekoyi imatambasulidwa ku mizere yofunikira.
16. Pambuyo pake, tebulo lidzakhala lokonzeka.

Katundu/Sungani Zosaka Zosaka

Izi ndizothandiza mukamagwiritsa ntchito zoletsa zosiyanasiyana.

1. Pa menyu ya Solution Finder Options, dinani Lowani / Sungani.
2. Lowetsani mndandanda wagawo lachitsanzo ndikudina Sungani kapena Kwezani.

Mukasunga choyimira, cholozera chimalowetsedwa ku selo imodzi yagawo lopanda kanthu pomwe mtundu wokhathamiritsa udzayikidwa. Pakutsitsa kwachitsanzo, zolozera zimalowetsedwa kumtundu wonse womwe uli ndi mtundu wokhathamiritsa.

Zofunika! Kusunga makonda omaliza mu menyu ya Solution Options, buku lantchito limasungidwa. Tsamba lililonse mkati mwake lili ndi zosankha zake zowonjezera Solver. Kuphatikiza apo, ndizotheka kukhazikitsa ntchito yopitilira 1 papepala podina batani la "Katundu kapena Sungani" kuti musunge ntchito iliyonse.

Chitsanzo chosavuta chogwiritsa ntchito Solver

Ndikofunikira kukweza chidebecho ndi zotengera kuti misa yake ikhale yayikulu. Thanki ili ndi mphamvu ya 32 cubic metres. m. Bokosi lodzaza ndi kulemera kwa 20 kg, voliyumu yake ndi 0,15 cubic metres. m. Bokosi - 80 kg ndi 0,5 cu. m. M'pofunika kuti chiwerengero chonse cha muli osachepera 110 ma PC. Deta imakonzedwa motere:

Zosintha zachitsanzo zimayikidwa zobiriwira. Ntchito ya cholinga imawonetsedwa mofiira. Zoletsa: ndi ziwiya zazing'ono kwambiri (zokulirapo kapena zofanana ndi 110) komanso kulemera kwake (=SUMPRODUCT(B8:C8,B6:C6) - kulemera kwathunthu kwa tare mu chidebe.

Mwa fanizo, timaganizira kuchuluka kwa voliyumu: =SUMPRODUCT(B7:C7,B8:C8). Njira yotereyi ndiyofunikira kuti muyike malire pa kuchuluka kwa zotengera zonse. Kenako, kudzera mu "Fufuzani yankho", maulalo amalowetsedwa kuzinthu zosinthika, ma formula ndi zizindikiritso zokha (kapena zolumikizira ku maselo enaake). Zowonadi, kuchuluka kwa zotengera ndizokwanira (ndizochepa). Timakanikiza "Pezani yankho", chifukwa chake timapeza zotengera zochulukirapo pamene kuchuluka kwake kuli kokwanira ndipo zoletsa zonse zimaganiziridwa.

Kusaka njira kwalephera kupeza mayankho

Zidziwitso zotere zimawonekera pomwe ntchito yomwe ikufunsidwayo sinapeze zophatikizira zamagulu osiyanasiyana omwe amakwaniritsa cholepheretsa chilichonse. Mukamagwiritsa ntchito njira ya Simplex, ndizotheka kuti palibe yankho.

Pamene njira yothetsera mavuto osagwirizana ikugwiritsidwa ntchito, nthawi zonse kuyambira pa zizindikiro zoyamba za zosinthika, izi zikusonyeza kuti njira yothetsera vuto ili kutali ndi magawo oterowo. Ngati muyendetsa ntchitoyi ndi zizindikiro zina zoyambira zosinthika, ndiye kuti pali yankho.

Mwachitsanzo, pogwiritsira ntchito njira yopanda mzere, zinthu za tebulo ndi zosinthika sizinadzazidwe, ndipo ntchitoyi sinapeze mayankho. Izi sizikutanthauza kuti palibe yankho. Tsopano, poganizira zotsatira za kafukufuku wina, deta ina imalowetsedwa muzinthu zomwe zili ndi zosinthika zomwe zili pafupi ndi zomwe zimalandiridwa.

Mulimonse momwe zingakhalire, choyamba muyenera kuyang'ana chitsanzo cha kusakhalapo kwa mikangano yoletsa. Nthawi zambiri, izi zimalumikizidwa ndi kusankha kosayenera kwa chiŵerengero kapena chizindikiro cholepheretsa.

Mu chitsanzo pamwamba, pazipita voliyumu chizindikiro ndi 16 kiyubiki mamita. m m'malo mwa 32, chifukwa kuletsa koteroko kumatsutsana ndi zizindikiro za chiwerengero chochepa cha mipando, chifukwa chidzafanana ndi chiwerengero cha 16,5 cubic metres. m.

Kutsiliza

Kutengera izi, njira ya "Sakani yankho" mu Excel ithandizira kuthetsa mavuto ena omwe ndi ovuta kapena osatheka kuwathetsa mwachizolowezi. Kuvuta kugwiritsa ntchito njirayi ndikuti poyamba njirayi imabisika, chifukwa chake ogwiritsa ntchito ambiri sadziwa za kukhalapo kwake. Kuphatikiza apo, ntchitoyi ndizovuta kuphunzira ndikugwiritsa ntchito, koma ndi kafukufuku woyenera, zimabweretsa phindu lalikulu ndikuwongolera kuwerengera.