I-Excel I-SUMIFS: Imikhawulo Yezindinganiso Zodwa Ezilinganiselwe Izidingo Eziningi

Umsebenzi we-SUMIFS udlulisela ukusebenza komsebenzi we-SUMIF ngokukuvumela ukuba ucacise kusuka ku-2 kuya ku-127 criteria kunokuba nje ku-SUMIF.

Ngokujwayelekile, i-SUMIFS isebenza ngemigqa yedatha ebizwa ngamarekhodi. Emrekhodini , yonke idatha esitokisini ngalinye noma enkundleni emgqeni ihlobene - njengegama lenkampani, ikheli kanye nenombolo yocingo.

I-SUMIFS ihlola izindlela eziqondile ezinkambu ezimbili noma ngaphezulu kwirekhodi futhi kuphela uma ithola umdlalo kwenkambu ngayinye ecacisiwe idatha yedokhumenti efingqa.

01 kwezingu-10

Indlela i-SUMIFS Umsebenzi esebenza ngayo

Esikhathini se- SUMIF isinyathelo ngesifundo sesinyathelo sasihambisana nesilinganiso esisodwa se-agent edayisayo esithengise ama-oda angaphezu kuka-250 ngonyaka.

Kulesi sifundo, sizobeka izimo ezimbili besebenzisa i- SUMIFS - yilabo abadayisi bezokuthengisa esifundeni se- East sokuthengisa ababenokungaphansi kwezingu-275 zokuthengisa ngonyaka odlule.

Ukubeka izimo ezingaphezu kwezimbili kungenziwa ngokucacisa okunye okubhaliwe kwe-Criteria_range kanye neziphakamiso ze-SUMIFS.

Ukulandela izinyathelo ezihlokweni zokufundisa ngezansi kukuhamba ngokudala nokusebenzisa umsebenzi we-SUMIFS obonwe kusithombe esingenhla.

Izihloko zezifundo

• Ukufaka idatha yokufundisa
• I-Syntax ye-SUMIFS Yomsebenzi
• Ukuqala ukusebenza kwe-SUMIFS
• I-Sum_range Argument
• I-Criteria_range1 Ukuphikisana
• I-Criteria1 Ukuphikisana
• I-Criteria_range2 Ukuphikisana
• I-Criteria2 Impikiswano

02 kwezingu-10

Ukufaka idatha yokufundisa

Isinyathelo sokuqala sokusebenzisa umsebenzi we-SUMIFS ku-Excel ukufaka idatha.

Faka idatha kumaseli D1 kuya ku-F11 wephepha lokusebenzela le-Excel njengoba kuboniswa esithombeni ngenhla.

I-SUMIFS isebenzayo kanye nemigomo yokusesha (imiyalo engaphansi kuka-275 kanye nama-ejenti okuthengisa avela esifundeni se-East sales) izongezwa kulayini 12 ngezansi kwedatha.

Imiyalo yokufundisa ayifaki ukufomatha izinyathelo zephepha lokusebenzela.

Lokhu ngeke kuphazamise ukuqedela ukufundisa. Iphepha lakho lokusebenzela lizobonakala lihlukile kunalokhu kubonisiwe, kodwa umsebenzi we-SUMIFS uzokunika imiphumela efanayo.

03 ka-10

I-Syntax ye-SUMIFS Yomsebenzi

Ku-Excel, i- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, nokuphikisana .

I-syntax yomsebenzi we-SUMIFS yilezi:

= I-SUMIFS (i-Sum_yange, iCriteria_range1, iCriteria1, iCriteria_range2, iCriteria2, ...)

Qaphela: Kuze kube ngu-127 Criteria_range / Criteria pairs kungacaciswa emsebenzini.

I-SUMIFS I-Arguments Function

Iziphakamiso zomsebenzi zitshela umsebenzi ukuthi yiziphi izimo ezivivinywayo nokuthi yikuphi uhlaka lwemininingwane okufanele lufinyelele uma lezo zimiso zihlangene.

Zonke izingxabano kulo msebenzi zidingeka.

I-Sum_yange - idatha kulolu hlu lwamaseli ifingqa lapho umdlalo utholakala phakathi kweNqunyiwe ecacisiwe kanye neziphakamiso zabo ezihambelana neCriteria_range .

I-Criteria_range - iqembu lamaseli umsebenzi ukucinga umdlalo ku-argument yokuHlola ehambelanayo.

Imigomo - leli xabiso lifaniswa nedatha ku- Criteria_range ehambelanayo. Idatha yangempela noma ireferensi yeseli yedatha ingangeniswa kule mpikiswano.

04 kwezingu-10

Ukuqala ukusebenza kwe-SUMIFS

Nakuba kungenzeka nje ukuthayipha umsebenzi we-SUMIFS esitokisini kushidi lokusebenzela , abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo lomsebenzi ukufaka umsebenzi.

Izinyathelo Zokufundisa

1. Chofoza kuseli F12 ukuze wenze iseli esebenzayo . Yilapho sizofaka khona umsebenzi we-SUMIFS.
2. Chofoza kuthebhu yefomula .
3. Chofoza ku- Math & Trig isithonjana ku- riboni ukuvula uhlu lokwehliswa komsebenzi.
4. Chofoza ku- SUMIFS ohlwini ukuze ulethe ibhokisi lengxoxo lomsebenzi we-SUMIFS.

Idatha esiyifaka emigqeni engenalutho ebhokisini lengxoxo izokwenza izingxabano zomsebenzi we-SUMIFS.

Lezi zimpikiswano zitshela umsebenzi ukuthi yiziphi izimo esizihlolisayo nokuthi yikuphi uhla lwemininingwane okufanele lufinyelele uma lezo zimiso zihlangana.

05 ka-10

Ukufaka i-Sum_range Argument

Ingxabano ye- Sum_range iqukethe izinkomba zeseli kudatha esifuna ukuyifaka .

Uma umsebenzi uthola umdlalo phakathi kwazo zonke izinkomba ezicacisiwe kanye neziphakamiso ze-Criteria_range zokurekhoda insimu ye-Sum_range yalokho irekhodi ifakiwe kuphelele.

Kulesi sifundo, idatha ye-argument ye-Sum_range itholakala kukholomu Yokuthengisa Yonke .

Izinyathelo Zokufundisa

1. Chofoza ku- Sum_range line ebhokisini lengxoxo .
2. Qinisekisa amaseli F3 kuya ku-F9 ekhasini lokusebenzela ukuze ungeze lezi zikhombo zeseli kumugqa we- Sum_range .

06 kwangu-10

Ukufaka i-Criteria_range1 Ukuphikisana

Kulesi sifundo sizama ukufanisa imigomo emibili kwirekhodi ngalinye lemininingwane:

1. Ama-agent okuthengisa avela esifundeni se-East sokuthengisa.
2. Ama-agent okuthengisa abenze okungenani kuka-275 ukuthengisa kulo nyaka.

I- Criteria_range1 ingxabano ibonisa ububanzi bamaseli ama-SUMIFS ukuthi afune uma ezama ukulinganisa indlela yokuqala - isifunda sokuthengisa saseMpumalanga.

Izinyathelo Zokufundisa

1. Ebhokisini lengxoxo , chofoza ku- Criteria_range1 line.
2. Qinisekisa amaseli D3 kuya ku-D9 kushidi lokusebenzela ukuze ufake lezi zikhombisi zeseli njengebanga lokucwaningwa ngumsebenzi .

07 kwangu-10

Ukufaka i-Criteria1 Ukungqubuzana

Kulesi sifundo, inqubo yokuqala esiyifuna ukuyifana nayo uma idatha ebangeni D3: D9 lilingana ne- East .

Nakuba idatha yangempela - njengegama elithi East - lingangeniswa ebhokisini lebhokisi le ngxabano ngokuvamile kungcono ukwengeza idatha esitokisini ekhasini lokusebenzela bese ufaka leyo nkomba yeseli ebhokisini lengxoxo.

Izinyathelo Zokufundisa

1. Chofoza ku- Criteria1 line ebhokisini lengxoxo .
2. Chofoza kuseli D12 ukuze ufake leyo reference reference. Umsebenzi uzosesha uhla olukhethiwe kwisinyathelo sangaphambilini sedatha efana nale nqubo.
3. Igama lokusesha (EMpumalanga) lizofakwa kwiseli D12 esiteji sokugcina sesifundo.

Indlela AmaReferensi Ezingqamuzana Ukwandisa I-SUMIFS I-Versatility

Uma ireferensi yeseli, njenge-D12, ingeniswa njenge- Criteria Argument, umsebenzi we-SUMIFS uzobuka ukufaniswa kwanoma yiluphi ulwazi olufakwe kulolo seli kusefeshini lokusebenzela .

Ngakho emva kokuthola imali yokuthengisa yesifunda saseMpumalanga kuzoba lula ukuthola idatha efanayo yesinye isifunda sokuthengisa ngokushintsha i- East ukuya eNyakatho noma eNtshonalanga esitokisini D12. Umsebenzi uzovuselela ngokuzenzakalelayo futhi ubonise umphumela omusha.

08 kwezingu-10

Ukufaka i-Criteria_range2 Ukuphikisana

Njengoba kushiwo ngaphambilini, kule tutorial sizama ukufanisa imigomo emibili kwirekhodi ngayinye yedatha:

1. Ama-agent okuthengisa avela esifundeni se-East sokuthengisa.
2. Ama-agent okuthengisa abenze okungenani kuka-275 ukuthengisa kulo nyaka.

I- argument ye- Criteria_range2 ibonisa ububanzi bamaseli ama-SUMIFS aseshayo uma ezama ukufanisa ne-criteria yesibili - ama-agent edayisayo abathengise amakholi angaphansi kuka-275 kulo nyaka.

Izinyathelo Zokufundisa

1. Ebhokisini lengxoxo , chofoza ku- Criteria_range2 line.
2. Gxilisa amaseli e-E3 kuya ku-E9 kushidi lokusebenzela ukuze ufake lezi zikhombo zeseli njengebanga lesibili okuzoseshwa ngumsebenzi .

09 kwezingu-10

Ukufaka i-Criteria2 Ukungqubuzana

Kulesi sifundo, inqubo yesibili esibhekene nayo ukufanisa ukuthi ngabe idatha ebangeni E3: E9 ingaphansi kwama-oda okuthengisa angu-275.

Njengengxabano yeCriteria1 , sizofaka ireferensi yeseli endaweni kaCriteria2 ebhokisini lezingxoxo kunokuba idatha ngokwayo.

Izinyathelo Zokufundisa

1. Chofoza ku- Criteria2 line ebhokisini lengxoxo .
2. Chofoza kuseli E12 ukuze ufake leyo reference reference. Umsebenzi uzosesha uhla olukhethiwe kwisinyathelo sangaphambilini sedatha efana nale nqubo.
3. Chofoza OK ukuze uqedele umsebenzi we-SUMIFS bese uvala ibhokisi lengxoxo.
4. Impendulo ye-zero (0) izovela esitokisini F12 - iseli lapho singene khona umsebenzi - ngoba asikazanga ukufaka idatha emasimini e-Criteria1 noCriteria2 (C12 no-D12). Kuze kube yilapho senza, akukho lutho lomsebenzi ozokwengeza futhi ngakho-ke inani lihlala ku-zero.
5. I-criteria yokusesha izongezwa kwisinyathelo esilandelayo sokufundisa.

10 kwangu-10

Ukungeza I-Criteria Yokusesha nokuqedela Isifundo

Isinyathelo sokugcina esifundweni ukufaka idatha kumaseli ekhredithi lokusebenzela elikhonjiswe njengequkethe izingxabano zokunquma .

Izinyathelo Zokufundisa

Ukuze uthole usizo ngalesi sibone ubone isithombe ngenhla.

1. Ngeselula D12 hlobo East bese ucindezela ukhiye we-Enter kukhibhodi.
2. Ngesisindo se-E12 <275 bese ucindezela ukhiye we-Enter ekhibhodi (i- "<" uphawu oluphansi kune- Excel).
3. Impendulo engu-$ 119,719.00 kufanele ivele kuseli F12.
4. Amarekhodi amabili kuphela ama-3 no-4 ahambisana nemigomo yokubili, ngakho-ke, kuphela inani lokuthengisa lala marekhodi amabili lifingqiwe ngumsebenzi.
5. Inani lemali engu- $ 49,017 ne $ 70,702 lingu-$ 119,719.
6. Uma uchofoza ku-cell F12, umsebenzi ophelele
   = I-SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela .