Ntchito yoyendetsa mu Excel. Kupeza njira yabwino yonyamulira kuchokera kwa wogulitsa kupita kwa wogula

Excel ndi pulogalamu yogwira ntchito kwambiri. Itha kugwiritsidwa ntchito kuthana ndi zovuta zazikulu zomwe munthu ayenera kukumana nazo mubizinesi. Chimodzi mwa zofala kwambiri ndi mayendedwe. Tangoganizani kuti tikuyenera kumvetsetsa kuti ndi njira iti yoyendera kuchokera kwa wopanga kupita kwa wogula womaliza yomwe ili yabwino kwambiri potengera nthawi, ndalama ndi zinthu zina. Vutoli ndilodziwika kwambiri, ziribe kanthu momwe bizinesiyo ilili. Choncho, tiyeni tiwone mwatsatanetsatane momwe tingagwiritsire ntchito pogwiritsa ntchito Excel.

Kufotokozera za ntchito ya mayendedwe

Chifukwa chake, tili ndi magawo awiri omwe amalumikizana nthawi zonse. Kwa ife, uyu ndi wogula ndi wogulitsa. Tiyenera kudziwa momwe tinganyamulire katundu m'njira yoti ndalama zikhale zochepa. Kuti muchite izi, muyenera kuwonetsa deta yonse mu mawonekedwe a schematic kapena matrix. Mu Excel, timagwiritsa ntchito njira yomaliza. Kawirikawiri, pali mitundu iwiri ya ntchito zoyendera:

1. Chotsekedwa. Pankhaniyi, kupezeka ndi kufunikira kuli koyenera.
2. Tsegulani. Palibe kufanana pakati pa kupereka ndi kufuna pano. Kuti mupeze yankho la vutoli, choyamba muyenera kulibweretsa ku mtundu woyamba, kufananiza kupezeka ndi kufuna. Kuti muchite izi, muyenera kuyambitsa chizindikiro china - kukhalapo kwa wogula kapena wogulitsa. Komanso, muyenera kusintha zina pa tebulo mtengo.

Momwe mungayambitsire mawonekedwe a Find Solution mu Excel

Kuthetsa mavuto a mayendedwe mu Excel, pali ntchito yapadera yotchedwa "Fufuzani yankho". Sichimathandizidwa mwachisawawa, chifukwa chake muyenera kuchita izi:

1. Tsegulani menyu "Fayilo", yomwe ili pakona yakumanzere kwawindo la pulogalamu.
2. Pambuyo pake, dinani batani ndi magawo.
3. Kenako, timapeza gawo la "Zikhazikiko" ndikupita ku menyu yowongolera zowonjezera. Awa ndi mapulogalamu ang'onoang'ono omwe amayenda mkati mwa Microsoft Excel chilengedwe. Tikuwona kuti poyamba tidadina menyu ya "Add-ins", kenako kumunsi kumanja timayika chinthu cha "Excel Add-ins" ndikudina batani la "Pitani". Zochita zonse zofunika zimawonetsedwa ndi ma rectangles ofiira ndi mivi.
4. Kenako, tsegulani chowonjezera "Sakani yankho", kenako timatsimikizira zochita zathu ndikukanikiza batani la OK. Kutengera kufotokozera kwazomwe zikuchitika, titha kuwona kuti zidapangidwa kuti zizisanthula zinthu zovuta, monga zasayansi ndi zachuma.
5. Pambuyo pake, pitani ku tabu "Data", kumene tikuwona batani latsopano, lomwe limatchedwa chimodzimodzi ndi kuwonjezera. Zitha kupezeka mu gulu la zida za Analysis.

Zimangodinanso batani ili, ndipo timapitilira njira yothetsera vuto lamayendedwe. Koma izi zisanachitike, tiyenera kulankhula zambiri za Solver chida mu Excel. Ichi ndi chowonjezera chapadera cha Excel chomwe chimapangitsa kuti athe kupeza yankho lachangu kwambiri pavuto. A khalidwe mbali ndi kuganizira zoletsa kuti wosuta akhazikitsa pa siteji kukonzekera. Mwachidule, ichi ndi gawo laling'ono lomwe limapangitsa kuti zitheke kudziwa njira yabwino yopezera ntchito inayake. Ntchito zoterezi zingaphatikizepo izi:

1. Kuyika ndalama, kukweza nyumba yosungiramo katundu kapena ntchito zina zofananira. Kuphatikizapo kutumiza katundu.
2. Njira yabwino. Izi zikuphatikiza zolinga monga kupeza phindu lalikulu pamtengo wocheperako, momwe mungakwaniritsire zabwino kwambiri ndi zinthu zomwe zilipo, ndi zina zotero.

Kuphatikiza pa ntchito zoyendera, chowonjezerachi chimagwiritsidwanso ntchito pazifukwa izi:

1. Kupanga dongosolo la kupanga. Ndiye kuti, ndi mayunitsi angati azinthu zomwe ziyenera kupangidwa kuti zitheke kupeza ndalama zambiri.
2. Pezani kugawa kwa ntchito zamitundu yosiyanasiyana ya ntchito kuti mtengo wonse wopangira chinthu kapena ntchito ukhale wocheperako.
3. Khazikitsani nthawi yochepa yomwe ingatenge kuti mumalize ntchito yonse.

Monga mukuonera, ntchito ndi zosiyana kwambiri. Lamulo lapadziko lonse logwiritsira ntchito chowonjezera ichi ndi chakuti musanayambe kuthetsa vutoli, m'pofunika kupanga chitsanzo chomwe chingagwirizane ndi zizindikiro zazikulu za vutolo. Chitsanzo ndi mndandanda wa ntchito zomwe zimagwiritsa ntchito zosinthika monga mfundo zawo. Ndiko kuti, mfundo zomwe zingasinthe.

Ndikofunikira kudziwa kuti kukhathamiritsa kwamagulu azinthu kumachitika kokha pa chizindikiro chimodzi, chomwe chimatchedwa cholinga chantchito.

The Solver add-in imatchula mitundu yosiyanasiyana ya zosinthika zomwe zimaperekedwa ku ntchito ya cholinga m'njira yoti ndizokwera kwambiri, zochepa, kapena zofanana ndi mtengo wina (izi ndiye zoletsa). Palinso ntchito ina yomwe ili yofanana ndi momwe imagwirira ntchito, ndipo nthawi zambiri imasokonezedwa ndi "Fufuzani yankho". Imatchedwa "Zosankha". Koma ngati mumakumba mozama, kusiyana pakati pawo ndi kwakukulu:

1. Ntchito ya Goal Search sikugwira ntchito ndi mitundu ingapo.
2. Sichimapereka mwayi wokhazikitsa malire pazosintha.
3. Zimatha kudziwa kokha kufanana kwa ntchito ya cholinga ku mtengo wina, koma sizimapangitsa kuti zitheke kupeza zambiri komanso zochepa. Choncho, sizoyenera ntchito yathu.
4. Amatha kuwerengera bwino ngati mtundu wa liniya wachitsanzo. Ngati chitsanzocho sichiri mzere, ndiye kuti chimapeza mtengo womwe uli pafupi kwambiri ndi mtengo wapachiyambi.

Ntchito yamayendedwe ndizovuta kwambiri pamapangidwe ake, chifukwa chake "Parameter kusankha" sikokwanira pa izi. Tiyeni tiwone mwatsatanetsatane momwe tingagwiritsire ntchito "Sakani Njira Yothetsera" pogwiritsa ntchito chitsanzo chavuto lamayendedwe.

Chitsanzo cha kuthetsa vuto la mayendedwe mu Excel

Kuti muwonetse bwino momwe mungathetsere zovuta zamagalimoto mu Excel, tiyeni tipereke chitsanzo.

Makhalidwe ntchito

Tiyerekeze kuti tili ndi ogulitsa 6 ndi ogula 7. Kufuna ndi kupereka pakati pawo kumagawidwa motsatira njira iyi: 36, 51, 32, 44, 35 ndi 38 mayunitsi ndi ogulitsa ndi 33, 48, 30, 36, 33, 24 ndi 32 mayunitsi ndi ogula. Mukaphatikiza mfundo zonsezi, mupeza kuti kupezeka ndi kufunidwa zili bwino. Choncho, vutoli ndi la mtundu wotsekedwa, womwe umathetsedwa mosavuta.

Kuphatikiza apo, tili ndi chidziwitso cha kuchuluka kwa zomwe muyenera kugwiritsa ntchito poyendetsa kuchokera ku point A kupita kumalo B (amawonetsedwa mu maselo achikasu pachitsanzo).

Yankho - sitepe ndi sitepe aligorivimu

Tsopano, titadziwa bwino matebulo omwe ali ndi deta yoyambirira, titha kugwiritsa ntchito njira zotsatirazi kuti tithetse vutoli:

1. Choyamba, timapanga tebulo lokhala ndi mizere 6 ndi mizati 7.
2. Pambuyo pake, timapita ku selo iliyonse yomwe ilibe zofunikira ndipo nthawi yomweyo imakhala kunja kwa tebulo lomwe langopangidwa kumene ndikuyika ntchitoyo. Kuti muchite izi, dinani batani la fx, lomwe lili kumanzere kwa mzere wolowera ntchito.
3. Tili ndi zenera momwe tiyenera kusankha gulu "Math". Kodi tikufuna ntchito yanji? Zomwe zawonetsedwa mu skrini iyi. Ntchito SUMPRODUCT achulukitsa milingo kapena magulu pakati pawo ndikuwawerengera. Zomwe tikusowa. Pambuyo pake, dinani OK kiyi.
4. Chotsatira, zenera lidzawonekera pazenera lomwe muyenera kufotokoza magawo a ntchito. Iwo ndi awa:
   1. Gulu 1. Uwu ndiye mkangano woyamba momwe timalembera mndandanda womwe umawonetsedwa mwachikasu. Mutha kukhazikitsa magawo ogwirira ntchito pogwiritsa ntchito kiyibodi kapena posankha malo oyenera ndi batani lakumanzere.
   2. Array 2. Uwu ndi mkangano wachiwiri, womwe ndi tebulo lopangidwa kumene. Zochita zimachitidwa chimodzimodzi.

Tsimikizirani zomwe mwachita podina batani la OK.

1. Pambuyo pake, timadina mbewa yakumanzere pa cell yomwe imakhala kumanzere kwa tebulo lomwe langopangidwa kumene. Tsopano dinani Ikani batani ntchito kachiwiri.
2. Timasankha gulu lomwelo monga momwe zinalili kale. Koma nthawi ino tili ndi chidwi ndi ntchitoyi SUM.
3. Tsopano pakubwera siteji yodzaza mikangano. Monga mkangano woyamba, timalemba mzere wapamwamba wa tebulo lomwe tidapanga pachiyambi. Mofanana ndi kale, izi zikhoza kuchitika mwa kusankha maselowa pa pepala, kapena pamanja. Timatsimikizira zochita zathu mwa kukanikiza batani la OK.
4. Tidzawona zotsatira mu selo ndi ntchito. Pankhaniyi, ndi ziro. Kenako, sunthani cholozera kukona yakumanja yakumanja, pambuyo pake cholembera cha autocomplete chidzawonekera. Zimawoneka ngati zowola pang'ono zakuda. Ngati zikuwoneka, gwirani batani lakumanzere ndikusunthira cholozera kuselo yomaliza patebulo lathu.
5. Izi zimatipatsa mwayi wosamutsa fomula ku ma cell ena onse ndikupeza zotsatira zolondola popanda kuchita mawerengedwe owonjezera.
6. Chotsatira ndikusankha selo lakumanzere lakumanzere ndikuyika ntchitoyo SUM mwa iye. Pambuyo pake, timalowetsa mikangano ndikugwiritsa ntchito cholembera cha autocomplete kuti mudzaze maselo onse otsala.
7. Pambuyo pake, timapitiriza mwachindunji kuthetsa vutoli. Kuti tichite izi, tigwiritsa ntchito zowonjezera zomwe tidaziphatikiza kale. Pitani ku tabu "Data", ndipo pamenepo timapeza "Sakani yankho" chida. Timadina batani ili.
8. Tsopano zenera lawonekera pamaso pathu, momwe mungakhazikitsire magawo owonjezera athu. Tiyeni tiwone chilichonse mwa njira izi:
   1. Konzani cholinga cha ntchito. Apa tiyenera kusankha selo lomwe lili ndi ntchitoyo SUMPRODUCT. Tikuwona kuti njirayi imapangitsa kuti zitheke kusankha ntchito yomwe yankho lidzafufuzidwa.
   2. M'mbuyomu. Apa tikukhazikitsa njira "Minimum".
   3. Mwa kusintha ma cell a zosinthika. Apa tikuwonetsa mndandanda womwe umagwirizana ndi tebulo lomwe tidapanga koyambirira (kupatulapo mzere ndi mzere wofotokozera).
   4. Kutengera zoletsa. Apa tifunika kuwonjezera zolepheretsa podina batani la Add.
   5. Timakumbukira mtundu wanji wazovuta zomwe tifunika kupanga - kuchuluka kwa zomwe ogula amafuna ndi zomwe ogulitsa azipereka ziyenera kukhala zofanana.
9. Ntchito ya zoletsa ikuchitika motere:
   1. Lumikizani ku ma cell. Apa tikulowetsa mndandanda wa tebulo kuti tiwerengere.
   2. Terms. Uku ndi ntchito yamasamu yomwe mulingo womwe watchulidwa m'gawo loyamba lolowera umawunikidwa.
   3. Mtengo wa chikhalidwe kapena zopinga. Apa tikulowetsa gawo loyenera mu tebulo loyambira.
   4. Masitepe onse akamaliza, dinani OK batani, potero kutsimikizira zochita zathu.

Timachita chimodzimodzi ntchito pamizere yapamwamba, ndikuyika zotsatirazi: ziyenera kukhala zofanana.

Chotsatira ndikukhazikitsa zikhalidwe. Tiyenera kukhazikitsa njira zotsatirazi za kuchuluka kwa ma cell omwe ali patebulo - wamkulu kuposa kapena wofanana ndi ziro, chiwerengero chonse. Zotsatira zake, tili ndi mndandanda wazinthu zomwe vutoli limathetsedwa. Apa muyenera kuwonetsetsa kuti bokosi loyang'ana pafupi ndi kusankha "Pangani zosintha popanda malire kuti zikhale zopanda pake" zafufuzidwa. Komanso, m'mikhalidwe yathu, pamafunika kuti njira yothetsera vutoli isankhidwe - "Kufufuza njira yothetsera mavuto osagwirizana ndi njira za OPG". Tsopano tikhoza kunena mosabisa kuti zoikamo zachitika. Chifukwa chake, zimangokhala kuchita mawerengedwe. Kuti muchite izi, dinani batani "Pezani yankho".

Pambuyo pake, deta yonse idzawerengedwa yokha, ndiyeno Excel idzawonetsa zenera ndi zotsatira. Ndikofunikira kuti muyang'ane kawiri kagwiritsidwe ntchito ka kompyuta, chifukwa zolakwika ndizotheka ngati zomwe zidakhazikitsidwa kale molakwika. Ngati zonse zili zolondola, dinani batani "Chabwino" ndikuwona tebulo lomalizidwa.

Ngati zikuwoneka kuti ntchito yathu yakhala mtundu wotseguka, ndiye kuti izi ndizoyipa, chifukwa muyenera kusintha tebulo loyambira kuti ntchitoyo ikhale yotsekedwa. Komabe, izi zikachitika, algorithm yotsalira idzakhala yofanana.

Kutsiliza

Monga mukuonera, Excel itha kugwiritsidwanso ntchito powerengera zovuta kwambiri, zomwe poyamba sizipezeka pakompyuta yosavuta yomwe imayikidwa pafupifupi aliyense. Komabe, zili choncho. Lero taphimba kale mlingo wapamwamba wa ntchito. Mutuwu siwophweka, koma monga akunena, msewu udzakhala wodziwika ndi woyenda. Chinthu chachikulu ndikutsata ndondomeko ya zochita, ndikuchita molondola zonse zomwe zasonyezedwa pamwambapa. Ndiye sipadzakhala zolakwika, ndipo pulogalamuyo idzachita zowerengera zonse zofunika. Sipadzakhala chifukwa choganizira za ntchito yoti mugwiritse ntchito ndi zina zotero.