Umsebenzi wokuthutha ku-Excel. Ukuthola indlela engcono kakhulu yokuhamba ukusuka kumdayisi ukuya kumthengi

I-Excel wuhlelo olusebenza kakhulu. Ingasetshenziselwa ukuxazulula ungqimba olukhulu lwezinkinga umuntu okufanele abhekane nazo ebhizinisini. Enye yezinto ezivame kakhulu ezokuthutha. Cabanga ukuthi sidinga ukuqonda ukuthi iyiphi indlela yokuthutha esuka kumkhiqizi ukuya kumthengi wokugcina engcono kakhulu ngokwesikhathi, imali kanye nezinye izinsiza. Le nkinga idume kakhulu, noma ngabe ibhizinisi likuyiphi imboni. Ngakho-ke, ake sibhekisise ukuthi singalenza kanjani lisebenzisa i-Excel.

Incazelo yomsebenzi wezokuthutha

Ngakho-ke, sinama-counterparties amabili ahlala exhumana nomunye nomunye. Esimweni sethu, lokhu kungumthengi nomthengisi. Sidinga ukuthola indlela yokuthutha impahla ngendlela yokuthi izindleko zibe zincane. Ukuze wenze lokhu, udinga ukwethula yonke idatha nge-schematic noma ifomu le-matrix. Ku-Excel, sisebenzisa inketho yokugcina. Ngokuvamile, kunezinhlobo ezimbili zemisebenzi yokuthutha:

1. Kuvaliwe. Kulokhu, ukunikezwa kanye nesidingo kuyabhalansi.
2. Vula. Akukho ukulingana phakathi kokunikezwa nokufunwa lapha. Ukuze uthole isixazululo sale nkinga, kufanele uqale ulethe ohlotsheni lokuqala, ukulinganisa ukunikezwa kanye nesidingo. Ukuze wenze lokhu, udinga ukwethula inkomba eyengeziwe - ukuba khona komthengi onemibandela noma umdayisi. Ngaphezu kwalokho, udinga ukwenza izinguquko ezithile etafuleni lezindleko.

Ungasivumela kanjani isici se-Find Solution ku-Excel

Ukuxazulula izinkinga zokuthutha ku-Excel, kunomsebenzi okhethekile obizwa ngokuthi “Sesha isixazululo”. Ayinikwa amandla ngokuzenzakalela, ngakho-ke udinga ukwenza lezi zinyathelo ezilandelayo:

1. Vula imenyu ethi “Ifayela”, esekhoneni eliphezulu kwesokunxele sewindi lohlelo.
2. Ngemuva kwalokho, chofoza inkinobho enamapharamitha.
3. Okulandelayo, sithola isigatshana esithi "Izilungiselelo" bese siya kumenyu yokuphatha izengezo. Lezi izinhlelo ezincane ezisebenza ngaphakathi kwemvelo ye-Microsoft Excel. Siyabona ukuthi ekuqaleni sichofoze imenyu ethi "Engeza", bese engxenyeni engezansi kwesokudla setha into ethi "Excel Add-ins" bese sichofoza inkinobho ethi "Hamba". Zonke izenzo ezidingekayo zigqanyiswa ngonxande ababomvu nemicibisholo.
4. Okulandelayo, vula isengezo esithi "Sesha isixazululo", ngemuva kwalokho siqinisekisa izenzo zethu ngokucindezela inkinobho ethi KULUNGILE. Ngokusekelwe encazelweni yesilungiselelo, singabona ukuthi yakhelwe ukuhlaziya idatha eyinkimbinkimbi, njengesayensi nezezimali.
5. Ngemuva kwalokho, hamba kuthebhu ethi "Idatha", lapho sibona khona inkinobho entsha, ebizwa ngokufana nesengezo. Ingatholakala eqenjini lamathuluzi okuhlaziya.

Kuhlala kuphela ukuchofoza kule nkinobho, futhi siqhubekela kwisixazululo senkinga yezokuthutha. Kepha ngaphambi kwalokho, kufanele sikhulume okwengeziwe ngethuluzi leSolver ku-Excel. Lesi isengezo esikhethekile se-Excel esenza kube lula ukuthola isisombululo esisheshayo senkinga. Isici esiyisici ukucatshangelwa kwemikhawulo emiswa ngumsebenzisi esigabeni sokulungiselela. Ngamagama alula, lena i-subroutine eyenza kube nokwenzeka ukunquma indlela engcono kakhulu yokufeza umsebenzi othile. Imisebenzi enjalo ingase ihlanganise okulandelayo:

1. Ukutshala imali, ukulayisha inqolobane noma yimuphi omunye umsebenzi ofanayo. Kubandakanya ukulethwa kwezimpahla.
2. Indlela engcono kakhulu. Lokhu kuhlanganisa izinjongo ezifana nokuzuza inzuzo enkulu ngezindleko eziphansi, indlela yokufinyelela ikhwalithi engcono kakhulu ngezinsiza ezitholakalayo, nokunye.

Ngaphezu kwemisebenzi yokuthutha, lesi sengezo siphinde sisetshenziselwe lezi zinhloso ezilandelayo:

1. Ukuthuthukiswa kohlelo lokukhiqiza. Okusho ukuthi, mangaki amayunithi omkhiqizo okudingeka akhiqizwe ukuze kuzuzwe imali engenayo ephezulu.
2. Thola ukusatshalaliswa kwabasebenzi bezinhlobo ezahlukene zomsebenzi ukuze izindleko eziphelele zokukhiqiza umkhiqizo noma isevisi ibe yincane kakhulu.
3. Setha isikhathi esincane esizosithatha ukuze uqedele wonke umsebenzi.

Njengoba ubona, imisebenzi ihluke kakhulu. Umthetho wendawo yonke wokusebenzisa lesi sengezo ukuthi ngaphambi kokuxazulula inkinga, kuyadingeka ukuthi udale imodeli ezohambisana nezici ezibalulekile zenkinga edaliwe. Imodeli iqoqo lemisebenzi esebenzisa okuguquguqukayo njengama-agumenti azo. Okungukuthi, amanani angashintsha.

Kubalulekile ukuqaphela ukuthi ukulungiselelwa kwesethi yamanani kwenziwa kuphela kunkomba eyodwa, ebizwa ngokuthi umsebenzi wenhloso.

Isengezo se-Solver sibala amanani ahlukene okuguquguqukayo adluliselwe kumsebenzi wenjongo ngendlela yokuthi kube umkhawulo, ubuncane, noma olingana nenani elithile (lokhu umkhawulo ngokuqondile). Kukhona omunye umsebenzi ofana nomgomo wawo wokusebenza, futhi ovame ukudideka ngokuthi “Sesha isixazululo”. Kubizwa “Ukukhetha Inketho”. Kodwa uma umba ujule, umehluko phakathi kwabo mkhulu kakhulu:

1. Umsebenzi Wokufuna Umgomo awusebenzi ngokuhlukahluka okungaphezu kokukodwa.
2. Ayinikezi ikhono lokubeka imingcele kokuguquguqukayo.
3. Iyakwazi ukunquma kuphela ukulingana komsebenzi wenhloso enanini elithile, kodwa akwenzi kube nokwenzeka ukuthola ubuningi nobuncane. Ngakho-ke, ayiwufanele umsebenzi wethu.
4. Iyakwazi ukubala kahle kuphela uma imodeli yomugqa wohlobo. Uma imodeli ingelona umugqa, khona-ke ithola inani eliseduze kunani lokuqala.

Umsebenzi wokuthutha uyinkimbinkimbi kakhulu esakhiweni sawo, ngakho-ke isengezo se- "Parameter selection" akwanele kulokhu. Ake sibhekisise ukuthi singawusebenzisa kanjani umsebenzi othi “Sesha Isixazululo” kusetshenziswa isibonelo senkinga yezokuthutha.

Isibonelo sokuxazulula inkinga yezokuthutha ku-Excel

Ukuze ubonise ngokucacile indlela yokuxazulula izinkinga zokuthutha ekusebenzeni ku-Excel, ake sinikeze isibonelo.

Imisebenzi yemibandela

Ake sithi sinabathengisi abayisi-6 nabathengi abayisi-7. Isidingo nokuhlinzekwa phakathi kwazo kusakazwa ngokulandelana ngale ndlela elandelayo: amayunithi angama-36, 51, 32, 44, 35 kanye nama-38 angabathengisi kanti amayunithi angama-33, 48, 30, 36, 33, 24 kanye nama-32 angabathengi. Uma uhlanganisa wonke lawa manani, uzothola ukuthi ukunikezwa kanye nesidingo kuyabhalansi. Ngakho-ke, le nkinga iwuhlobo oluvaliwe, oluxazululwa kalula.

Ngaphezu kwalokho, sinolwazi mayelana nokuthi udinga ukuchitha malini kwezokuthutha ukusuka endaweni A ukuya endaweni B (zigqanyiswe kumaseli aphuzi esibonelweni).

Isixazululo - isinyathelo ngesinyathelo algorithm

Manje, ngemva kokuzijwayeza amathebula anedatha yokuqala, singasebenzisa i-algorithm elandelayo ukuxazulula le nkinga:

1. Okokuqala, senza itafula elinemigqa engu-6 namakholomu angu-7.
2. Ngemuva kwalokho, siya kunoma yiliphi iseli elingenawo amanani futhi ngesikhathi esifanayo lingaphandle kwetafula elisanda kwakhiwa bese ufaka umsebenzi. Ukuze wenze lokhu, chofoza inkinobho ye-fx, etholakala kwesokunxele somugqa wokufaka umsebenzi.
3. Sinewindi lapho sidinga ukukhetha khona isigaba "Izibalo". Imuphi umsebenzi esinentshisekelo kuwo? Leyo egqanyisiwe kulesi sithombe-skrini. Umsebenzi SUMPRODUCT iphindaphinda ububanzi noma izinhlu phakathi kwayo futhi ihlanganise. Esikudingayo nje. Ngemva kwalokho, cindezela ukhiye OK.
4. Okulandelayo, iwindi lizovela esikrinini lapho udinga ukucacisa khona amapharamitha omsebenzi. Lawa alandelayo:
   1. Uhlu 1. Lena impikiswano yokuqala lapho sibhala khona ububanzi obugqanyisiwe ngokuphuzi. Ungasetha amapharamitha omsebenzi ngokusebenzisa ikhibhodi noma ngokukhetha indawo efanele ngenkinobho yegundane engakwesokunxele.
   2. Uhlu 2. Lena impikiswano yesibili, okuyithebula elisanda kwakhiwa. Izenzo zenziwa ngendlela efanayo.

Qinisekisa isenzo sakho ngokucindezela inkinobho ethi KULUNGILE.

1. Ngemva kwalokho, senza ukuchofoza kwegundane kwesokunxele kuseli elisebenza njengenhla kwesokunxele kuthebula elisanda kwakhiwa. Manje chofoza inkinobho yokufaka umsebenzi futhi.
2. Sikhetha isigaba esifanayo naso endabeni edlule. Kodwa kulokhu sinentshisekelo kulo msebenzi ISUM.
3. Manje kufika isigaba sokugcwalisa izimpikiswano. Njengengxabano yokuqala, sibhala umugqa ophezulu wethebula esalidalile ekuqaleni. Ngendlela efanayo nangaphambili, lokhu kungenziwa ngokukhetha lawa maseli eshidini, noma ngokwenza. Siqinisekisa izenzo zethu ngokucindezela inkinobho ethi KULUNGILE.
4. Sizobona imiphumela kuseli ngomsebenzi. Kulokhu, kunguziro. Okulandelayo, hambisa ikhesa ekhoneni elingezansi kwesokudla, ngemva kwalokho kuzovela umaka wokuqedela ngokuzenzakalela. Kubukeka sengathi i-plush encane emnyama. Uma ivela, cindezela inkinobho yegundane kwesokunxele bese uhambisa ikhesa kuseli yokugcina kuthebula lethu.
5. Lokhu kusinika ithuba lokudlulisela ifomula kuwo wonke amanye amaseli futhi sithole imiphumela efanele ngaphandle kokwenza izibalo ezengeziwe.
6. Isinyathelo esilandelayo ukukhetha iseli engaphezulu kwesokunxele bese unamathisela umsebenzi ISUM angene kuye. Ngemva kwalokho, sifaka ama-agumenti futhi sisebenzise umaka wokuqedela ngokuzenzakalela ukuze sigcwalise wonke amaseli asele.
7. Ngemva kwalokho, siqhubeka ngqo ekuxazululeni inkinga. Ukwenza lokhu, sizosebenzisa isengezo esisifake ekuqaleni. Iya kuthebhu "Idatha", futhi lapho sithola ithuluzi elithi "Sesha isixazululo". Sichofoza le nkinobho.
8. Manje sekuvele iwindi phambi kwamehlo ethu, lapho ungakwazi ukumisa imingcele yesengezo sethu. Ake sibheke ngayinye yalezi zinketho:
   1. Lungiselela umsebenzi wezinjongo. Lapha sidinga ukukhetha iseli eliqukethe umsebenzi SUMPRODUCT. Siyabona ukuthi le nketho yenza kube nokwenzeka ukukhetha umsebenzi lapho isisombululo sizoseshwa khona.
   2. Ngaphambili. Lapha sibeka inketho "Okungenani".
   3. Ngokushintsha amaseli eziguquguqukayo. Lapha sibonisa ububanzi obuhambisana nethebula esalidalile ekuqaleni (ngaphandle komugqa nekholomu yokufingqa).
   4. Kungaphansi kwemikhawulo. Lapha sidinga ukungeza izithiyo ngokuchofoza inkinobho ethi Engeza.
   5. Sikhumbula ukuthi hlobo luni lwesivimbelo esidinga ukukudala - isamba samanani wezidingo zabathengi kanye neminikelo yabathengisi kumele ifane.
9. Umsebenzi wemikhawulo wenziwa kanje:
   1. Xhuma kumaseli. Lapha sifaka ububanzi bethebula ukuze kube nezibalo.
   2. Imigomo. Lona umsebenzi wezibalo lapho ububanzi obushiwo endaweni yokuqala yokufaka kuhlolwa khona.
   3. Inani lesimo noma isithiyo. Lapha sifaka ikholomu efanelekile kuthebula lomthombo.
   4. Ngemuva kokuthi zonke izinyathelo seziqediwe, chofoza inkinobho ethi KULUNGILE, ngaleyo ndlela uqinisekise izenzo zethu.

Senza imisebenzi efanayo ncamashi emigqeni ephezulu, sibeka umbandela olandelayo: kufanele alingane.

Isinyathelo esilandelayo ukubeka izimo. Sidinga ukusetha imibandela elandelayo yesamba samaseli kuthebula - elikhulu noma elilingana noziro, inombolo ephelele. Ngenxa yalokho, sinohlu olunjalo lwezimo lapho inkinga ixazululwa khona. Lapha udinga ukwenza isiqiniseko sokuthi ibhokisi lokuhlola eduze kwenketho ethi "Yenza okuguquguqukayo ngaphandle kwemikhawulo okungeyona inegethivu" lithikhiwe. Futhi, esimweni sethu, kuyadingeka ukuthi indlela yokuxazulula inkinga ikhethwe - "Isesha isixazululo sezinkinga ezingezona ze-OPG zezindlela". Manje singasho ngokuphepha ukuthi isilungiselelo senziwe. Ngakho-ke, kuhlala kuphela ukwenza izibalo. Ukuze wenze lokhu, chofoza inkinobho ethi "Thola isixazululo".

Ngemva kwalokho, yonke idatha izobalwa ngokuzenzakalelayo, bese i-Excel izobonisa iwindi elinemiphumela. Kuyadingeka ukuze uhlole kabili ukusebenza kwekhompiyutha, ngoba amaphutha angenzeka uma izimo bezisethwe ngokungalungile. Uma konke kulungile, chofoza inkinobho ethi "KULUNGILE" futhi ubone ithebula eliqediwe.

Uma kuvela ukuthi umsebenzi wethu usuwuhlobo oluvulekile, khona-ke lokhu kubi, ngoba udinga ukuhlela ithebula lomthombo ukuze umsebenzi uphenduke ube ovaliwe. Kodwa-ke, uma lokhu sekwenziwe, i-algorithm esele izofana.

Isiphetho

Njengoba ubona, i-Excel ingasetshenziswa futhi ekubaleni okuyinkimbinkimbi kakhulu, okuthi uma uthi nhla, ingatholakali ohlelweni olulula lwekhompyutha olufakwe cishe kuwo wonke umuntu. Nokho, kunjalo. Namuhla sesivele sihlanganise izinga eliphezulu lokusetshenziswa. Lesi sihloko asilula kangako, kodwa njengoba besho, umgwaqo uzophathwa ngohambayo. Into esemqoka ukulandela uhlelo lokusebenza, futhi wenze ngokunembile zonke izenzo ezikhonjiswe ngenhla. Khona-ke ngeke kube namaphutha, futhi uhlelo luzokwenza ngokuzimela zonke izibalo ezidingekayo. Ngeke kube nesidingo sokucabanga ukuthi yimuphi umsebenzi okufanele usetshenziswe nokunye.