Umsebenzi we-SUMIF ku-Excel kanye nesamba ngezimo eziningi

I-Excel wuhlelo olusebenza ngendlela emangalisayo. Ngisho nesethi yesici esakhelwe ngaphakathi yanele ukuqedela cishe noma yimuphi umsebenzi. Futhi ngaphandle kwalezo ezijwayelekile, ezijwayelekile kwabaningi, kukhona nalezo abantu abambalwa abake bezwe ngazo. Kodwa ngesikhathi esifanayo, aziyeki ukuba usizo. Banobuchwepheshe obuncane, futhi asikho isidingo sabo ngaso sonke isikhathi. Kodwa uma wazi ngabo, khona-ke ngesikhathi esibucayi bangaba usizo kakhulu.

Namuhla sizokhuluma ngomunye wemisebenzi enjalo − I-SUMMESLIMN.

Uma umsebenzisi ebhekene nomsebenzi wokufingqa amanani amaningana, agxile kumibandela ethile, khona-ke kuyadingeka ukusebenzisa umsebenzi. I-SUMMESLIMN. Ifomula esebenzisa lo msebenzi ithatha lezi zimo njengezimpikiswano, bese ihlanganisa amanani ahlangabezana nazo, bese inani elitholiwe lifakwa kuseli okubhalwe kulo. 

Incazelo Eningiliziwe Yomsebenzi We-SUMIFS

Ngaphambi kokucabangela umsebenzi I-SUMMESLIMN, kufanele uqale uqonde ukuthi inguqulo yayo elula - I-SUMMESLI, njengoba kuwumsebenzi esiwucabangelayo usekelwe kuwo. Cishe ngamunye wethu usejwayelene nemisebenzi emibili evame ukusetshenziswa - ISUM (Yenza ukufinyezwa kwamanani) kanye IF (ihlola inani ngokumelene nesimo esithile).

Uma uzihlanganisa, uthola omunye umsebenzi - I-SUMMESLI, ehlola idatha ngokuqhathanisa nemibandela eshiwo umsebenzisi futhi ibale lezo zinombolo kuphela ezihlangabezana nalezo zindlela zokunquma. Uma sikhuluma ngenguqulo yesiNgisi ye-Excel, lo msebenzi ubizwa nge-SUMIF. Ngamagama alula, igama elithi -language liwukuhumusha okuqondile kolimi lwesiNgisi. Lo msebenzi ungasetshenziswa ngezinjongo ezihlukahlukene. Ikakhulukazi, ingasetshenziswa njengenye indlela VPR, okungukuthi, bhala phansi

Umehluko omkhulu phakathi komsebenzi I-SUMMESLIMN  kusukela emsebenzini ojwayelekile I-SUMMESLI ukuthi kusetshenziswa izindlela ezimbalwa. I-syntax yayo iyinkimbinkimbi ekuqaleni, kodwa lapho ihlolisisa, kuvela ukuthi umqondo walo msebenzi ulula kakhulu. Okokuqala udinga ukukhetha ububanzi lapho idatha izohlolwa khona, bese usetha izimo zokuhambisana nokuhlaziywa okuzokwenziwa ngazo. Futhi ukuhlinzwa okunjalo kungenziwa ngenani elikhulu lezimo.

I-syntax ngokwayo ithi:

SUMIFS(sum_range, condition_range1, condition1, [condition_range2, condition2], …)

Ezindaweni ezifanele kudingekile ukubeka amaseli ahlukahlukene afanele esimweni esithile. 

Ake sibheke izimpikiswano ngokuningiliziwe:

1. Isamba_sobubanzi. Le mpikiswano, kanye nobubanzi besimo 1 kanye nesimo 1, kuyadingeka. Iqoqo lamaseli adinga ukufingqwa.
2. Condition_range1. Lobu ububanzi lapho isimo sizohlolwa khona. Imataniswe nempikiswano elandelayo - Condition1. Ukufinyezwa kwamanani ahambisana nombandela kwenziwa ngaphakathi kwamaseli ashiwo ku-agumenti yangaphambilini.
3. Isimo1. Le mpikiswano icacisa umbandela okufanele kubhekwe ngawo. Ingasethwa, isibonelo, ngale ndlela: "> 32".
4. Ububanzi besimo 2, Isimo 2… Lapha, izimo ezilandelayo zisethwe ngendlela efanayo. Uma imibandela engaphezu kwembalwa idinga ukucaciswa, kuzobe sekwengezwa ama-agumenti Obubanzi Besimo 3 kanye Nombandela 3. I-syntax iyafana kulawa ma-agumenti alandelayo.

Umsebenzi uvumela ukucutshungulwa okuphezulu okufika kumapheya angu-127 ezimo nobubanzi. 

Ungayisebenzisa ezindaweni ezimbalwa ngesikhathi esisodwa (sizonikeza ezimbalwa, uhlu empeleni lude kakhulu):

1. Ukubalwa kwezimali. Isibonelo, kuhle ukusebenzisa umsebenzi I-SUMMESLIMN ukudala imibiko efingqiwe, ngekota yokusetshenziswa kwenani elithile, isibonelo. Noma dala umbiko ngomkhiqizo owodwa ovela esigabeni esithile samanani.
2. Ukuphathwa kokuthengisa. Lapha futhi umsebenzi ungaba usizo kakhulu. Isibonelo, sibhekene nomsebenzi wokufingqa kuphela izindleko zezimpahla ezadayiselwa ikhasimende elithile ngesikhathi esithile. Futhi esimweni esinjalo, umsebenzi I-SUMMESLIMN kungaba usizo kakhulu.
3. Imfundo. Sizonikeza izibonelo ezengeziwe ezisebenzayo kule ndawo namuhla. Ikakhulukazi, ungayisebenzisa ukuze uthole isifinyezo samabanga abafundi. Ungakhetha isifundo esisodwa noma amabanga ngamanye. Umuntu angakwazi ukusetha ngokushesha izinqubo ezimbalwa lapho ukuhlola kuzokhethwa khona, okuyinto elula kakhulu futhi engonga isikhathi esiningi.

Njengoba ubona, ububanzi bezinhlelo zokusebenza zalo msebenzi bubanzi kakhulu. Kodwa lokhu akukhona kuphela ukufaneleka kwayo. Ake sibheke ezinye izinzuzo ezimbalwa lesi sici esinazo:

1. Ikhono lokusetha imibandela eminingi. Kungani lokhu kuyinzuzo? Ungasebenzisa umsebenzi ojwayelekile I-SUMMESLI! Futhi konke ngoba kulula. Asikho isidingo sokwenza izibalo ezihlukene zenqubo ngayinye. Zonke izenzo zingahlelwa kusengaphambili, ngisho nangaphambili. ukuthi ithebula ledatha lizokwakhiwa kanjani. Lesi yisilondolozi sesikhathi esihle.
2. Okuzenzakalelayo. Inkathi yesimanje isikhathi sokuzenzakalela. Umuntu owazi ukwenza umsebenzi wakhe ngokuzenzakalelayo kuphela ongathola imali eningi. Kungakho ikhono lokwazi kahle i-Excel nomsebenzi I-SUMMESLIMN ikakhulukazi, ibaluleke kakhulu kunoma yimuphi umuntu ofuna ukwakha umsebenzi. Ukwazi umsebenzi owodwa kukuvumela ukuthi wenze izenzo ezimbalwa ngesikhathi esisodwa, njengento eyodwa. Futhi lapha siqhubekela enzuzweni elandelayo yalesi sici.
3. Ukonga isikhathi. Ngenxa nje yokuthi umsebenzi owodwa wenza imisebenzi eminingana ngesikhathi esisodwa.
4. Ubulula. Naphezu kweqiniso lokuthi i-syntax inzima kakhulu uma uqala ukuyibuka ngenxa yobukhulu bayo, empeleni, i-logic yalo msebenzi ilula kakhulu. Okokuqala, kukhethwa uhla lwedatha, bese kuba uhla lwamanani, oluzohlolelwa ukuhambisana nombandela othile. Futhi-ke, isimo ngokwaso kufanele futhi sicaciswe. Futhi ngakho izikhathi eziningana. Eqinisweni, lo msebenzi usekelwe ekwakhiweni okukodwa okunengqondo, okwenza kube lula kunokwaziwayo VPR naphezu kweqiniso lokuthi ingasetshenziswa ngezinhloso ezifanayo, futhi kucatshangelwa inani elikhulu lemibandela. 

Izici zokusebenzisa umsebenzi we-SUMIFS

Kunezici ezimbalwa zokusebenzisa lo msebenzi okufanele uzinake. Okokuqala nje, lo msebenzi uziba ububanzi obunamayunithi ezinhlamvu ombhalo noma ama-null, njengoba lezi zinhlobo zedatha azikwazi ukungezwa ndawonye ngephethini ye-arithmetic, ehlanganiswe kuphela njengeyunithi yezinhlamvu. Lo msebenzi awukwazi ukwenza lokhu. Udinga futhi ukunaka lezi zimo ezilandelayo:

1. Ungasebenzisa lezi zinhlobo zamanani njengemibandela yokukhetha amaseli ukuze uqhubeke ungeze amanani aqukethwe kuwo: amanani ezinombolo, izinkulumo ze-boolean, ireferensi yamaseli, nokunye. 
2. Uma umbhalo, izinkulumo eziphusile noma izimpawu zezibalo zihlolwa, lokho kukhethwa kucaciswa ngokucaphuna.
3. Ayikwazi ukusebenzisa amagama amade kunezinhlamvu ezingama-255.
4. Kungenzeka ukusebenzisa imibandela elinganiselwe yokukhetha amanani usebenzisa amakhadi asendle. Uphawu lombuzo lusetshenziswa esikhundleni sohlamvu olulodwa, futhi uphawu lokuphindaphinda (inkanyezi) luyadingeka ukuze kungene izinhlamvu eziningi. 
5. Amanani aphusile asebangeni lokuhlanganisa aguqulwa ngokuzenzakalelayo abe amanani ezinombolo ngokuya ngohlobo lwawo. Ngakho, inani elithi "TRUE" liphenduka libe linye, futhi "FALSE" - libe nguziro. 
6. Uma i-#VALUE! iphutha livela kuseli, kusho ukuthi inani lamaseli asesimweni kanye nobubanzi bokulinganisa lihlukile. Udinga ukwenza isiqiniseko sokuthi osayizi balawa ma-agumenti bayefana. 

Izibonelo zokusebenzisa umsebenzi we-SUMIFS

Umsebenzi I-SUMMESLIMN hhayi njengoba inkimbinkimbi njengoba kubonakala efika kuqala, kuvela. Kodwa ukuze uthole ukucaca okwengeziwe, ake sibheke ezinye izibonelo ezingokoqobo zokuthi ungawusebenzisa kanjani umsebenzi I-SUMMESLIMN. Lokhu kuzokwenza kube lula kakhulu ukujula esihlokweni.

Ububanzi obuguquguqukayo bemibandela

Ngakho ake siqale ngesibonelo sokuqala. Ake sithi sinethebula eliqukethe ulwazi mayelana nendlela abafundi ababhekana ngayo nekharikhulamu esifundweni esithile. Kunesethi yamabanga, ukusebenza kuhlolwa esikalini samaphuzu ayi-10. Umsebenzi uwukuthola ibanga lokuhlolwa kwalabo bafundi isibongo sabo esiqala ngohlamvu A, futhi amaphuzu abo amancane ngu-5.

Itafula libukeka kanje.

Ukuze sibale amaphuzu aphelele ngokusekelwe kumbandela wokunquma ochazwe ngenhla, sidinga ukusebenzisa ifomula elandelayo.

Ake sichaze izimpikiswano ngokuningiliziwe:

1. I-C3:C14 uhla lwethu lokuhlanganisa. Esimweni sethu, kuhambisana nobubanzi besimo. Kuyo kuzokhethwa amaphuzu asetshenziselwa ukubala inani, kodwa kuphela lawo awela ngaphansi kwemibandela yethu.
2. "> 5" yisimo sethu sokuqala.
3. I-B3:B14 iwububanzi besibili bokuhlanganisa obucutshungulwayo ukuze bufane nombandela wesibili. Siyabona ukuthi akukho ukuqondana nebanga lokuhlanganisa. Kulokhu siphetha ngokuthi ububanzi bokulinganisa kanye nobubanzi besimo kungase kufane noma kungafani. 
4. “A*” uhla lwesibili, olucacisa ukukhethwa kwamamaki kulabo bafundi isibongo sabo esiqala ngo-A. Esimeni sethu, inkanyezi isho noma iyiphi inombolo yezinhlamvu. 

Ngemva kokubala, sithola ithebula elilandelayo.

Njengoba ubona, ifomula ifingqa amanani asuselwa kububanzi obuguqukayo nangokususelwa kuzimo ezishiwo umsebenzisi.

I-summary ekhethiwe ngokwemibandela ku-Excel

Manje ake sithi sifuna ukuthola ulwazi mayelana nokuthi yiziphi izimpahla ezithunyelwe kumaphi amazwe ngekota yokugcina. Ngemuva kwalokho, thola inani lemali engenayo kusukela ekuthunyelweni kukaJulayi no-Agasti.

Itafula ngokwalo libukeka kanje. 

Ukuze sinqume umphumela wokugcina, sidinga ifomula enjalo.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Njengomphumela wezibalo ezenziwa yile fomula, sithola umphumela olandelayo.

Qaphela! Le fomula ibukeka inkulu kakhulu yize sisebenzise izindlela ezimbili kuphela. Uma ububanzi bedatha bufana, unganciphisa kakhulu ubude befomula, njengoba kukhonjisiwe ngezansi.

Umsebenzi we-SUMIFS ukuze wenze isamba samanani kuzo zonke izimo eziningi

Manje ake sinikeze esinye isibonelo ukuze sisibonise. Kulokhu, ithebula lihlala lifana necala langaphambilini. 

Sisebenzisa ifomula elandelayo (kodwa siyibhala njengefomula yohlu, okungukuthi, siyifaka ngenhlanganisela yokhiye CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Ngemva komcimbi I-SUMMESLIMN izohlanganisa amanani afanayo asekelwe kumbandela ocaciswe kufomula (okungukuthi, amazwe aseShayina naseGeorgia), uhlu oluwumphumela lufingqwa ngomsebenzi ojwayelekile. SUM, ebhalwe njengefomula yohlu.

Uma izimo ziphasiswe njengokungaguquki kwamalungu afanayo kumapheya angaphezu kwelilodwa, ifomula izonikeza umphumela ongalungile.

Manje ake sibheke ithebula eliqukethe amathothali.

Njengoba ubona, siphumelele. Nawe uzophumelela nakanjani. Impumelelo enkulu kulo mkhakha. Lona umsebenzi olula ongawuqonda umuntu osanda kubeka unyawo endleleni yokufunda i-Excel. Futhi sesiyazi ukuthi umsebenzi I-SUMMESLIMN ikuvumela ukuthi uphumelele kunoma yimuphi umkhakha womsebenzi, kusukela ku-accounting kuya ngisho nasemfundweni. Ngisho noma wakha umsebenzi kunoma iyiphi enye indawo engachazwanga ngenhla, lesi sici sisazokusiza ukuzuza imali. Yingakho eyigugu.

Okubaluleke kakhulu, ikuvumela ukuthi ulondoloze isikhathi, okungukuthi, ngeshwa, insiza elinganiselwe. Kubukeka sengathi kunemizuzwana embalwa yokusebenzisa imisebenzi emibili, kodwa uma kufanele wenze inani elikhulu lemisebenzi ephindaphindwayo, le mizuzwana ihlanganisa amahora angachithwa kokunye. Ngakho-ke sincoma ukuthi uzijwayeze ukusebenzisa lesi sici. Ngaphezu kwalokho, ilula ngendlela emangalisayo.