I-Excel SUMIF: Ama-Sum Amanani ahlangabezana Nezici Ezicacisiwe

01 ngo-08

Indlela umsebenzi we-SUMIF osebenza ngayo

I-SUMIF Umsebenzi Kafushane

Umsebenzi we-SUMIF uhlanganisa umsebenzi we-IF nomsebenzi we- SUM ku-Excel. Le nhlanganisela ikuvumela ukuthi ungeze lezo zindinganiso ebangeni elikhethiwe ledatha elihlangabezana nezimo ezithile.

Ingxenye ye-IF yomsebenzi inquma ukuthi iyiphi idatha ehlangabezana nezimiso ezicacisiwe futhi ingxenye ye-SUM iyengeza.

Ngokuvamile, i-SUMIF isetshenziswe ngemigqa yedatha ebizwa ngokuthi amarekhodi. Emrekhodini , yonke idatha esitokisini ngasinye emgqeni ihlobene - njengegama lenkampani, ikheli kanye nenombolo yocingo.

I-SUMIF ibheka izinqubo ezithile esitokisini noma insimu eyodwa kumarekhodi futhi, uma ithola umdlalo, ineza ukuthi idatha noma idatha kwenye insimu ecacisiwe kwirekhodi elifanayo.

Umsebenzi we-SUMIF Isinyathelo ngesinyathelo Isifundo

Lesi sihloko sisebenzisa iqoqo lamarekhodi wedatha kanye nomsebenzi we-SUMIF ukuthola ukuthengwa kweminyaka yonke ye-Sales Reps abathengise ama-oda angaphezu kwama-250.

Ukulandela izinyathelo ezihlokweni zokufundisa ngezansi kukuhamba ngokudala nokusebenzisa umsebenzi we-SUMIF obonwe esithombeni ngenhla ukubala ukuthengiswa kweminyaka yonke.

Izihloko zezifundo

• Ukufaka idatha yokufundisa
• I-syntax ye-SUMIF yomsebenzi
• Ukuqala umsebenzi we-SUMIF
• Ukufaka i- Range Argument
• Ukufaka Ukungqubuzana Okubalulekile
• Ukufaka i- Sum_range Argument
• Futhi Impendulo yile ..

02 ngo-08

Ukufaka idatha yokufundisa

Ukufaka idatha yokufundisa

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

Faka idatha kumaseli B1 kuya ku-E11 wephepha lokusebenzela le-Excel njengoba kuboniswa esithombeni ngenhla.

Umsebenzi we-SUMIF kanye nemigomo yokusesha (imiyalo engaphezu kwama-250) izongezwa kulayini 12 ngezansi kwedatha.

Qaphela: Imiyalo yokufundisa ayifaki ukufomatha izinyathelo zephepha lokusebenzela.

Lokhu ngeke kuphazamise ukuqedela ukufundisa. Iphepha lakho lokusebenzela lizobonakala lihlukile kunalesi sibonisi esibonisiwe, kodwa umsebenzi we-SUMIF uzokunika imiphumela efanayo.

03 ngo-08

I-syntax ye-SUMIF yomsebenzi

I-syntax ye-SUMIF yomsebenzi

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

I-syntax yomsebenzi we-SUMIF yile:

= SUMIF (Uhlobo, I-Criteria, i-Sum_range)

I-Arguments ye-Function ye-SUMIF

Izimpendulo zomsebenzi zitshela umsebenzi ukuthi yisiphi isimo esizihlolisayo nokuthi yiyiphi inamba yedatha okumele ifinyelele uma isimo sifinyelelwe.

Ububanzi - iqembu lama cell the function is to search.

Imigomo - leli xabiso lifaniswa nedatha kumaseli weRange . Uma umdlalo utholakala bese idatha ehambelanayo ku- sum_range ingeziwe. Idatha yangempela noma ireferensi yeseli yedatha ingangeniswa kule mpikiswano.

I-Sum_range (okukhethwa kukho) - idatha kulesi sigaba samaseli ifakwe lapho imingcele itholakala phakathi kwempikiswano yobubanzi kanye nemigomo . Uma ngabe leli hlu likhishiwe, ibanga lokuqala lifingqiwe esikhundleni salokho.

04 ngo-08

Ukuqala umsebenzi we-SUMIF

Vula ibhokisi lengxoxo ye-SUMIF Umsebenzi

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

Izinyathelo Zokufundisa

1. Chofoza kuseli E12 ukuze wenze iseli esebenzayo . Yilapho sizofaka khona umsebenzi we-SUMIF.
2. Chofoza kuthebhu yefomula .
3. Chofoza ku- Math & Trig isithonjana ku- riboni ukuvula uhlu lokwehliswa komsebenzi.
4. Chofoza ku- SUMIF ohlwini ukuze ukhiphe ibhokisi lengxoxo ye-SUMIF yomsebenzi.

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

Lezi ziphakamiso zitshela umsebenzi ukuthi yisiphi isimo esizihlolisayo nokuthi yiyiphi inamba yedatha okufanele ifinyelele uma isimo sifinyelelwe.

05 ka-08

Ukufaka i-Range Argument

Ukufaka i- Range Argument

Kulesi sifundo sifuna ukuthola ukuthengiswa okuphelele kwaleyo Sales Reps eyayinezikhulu ezingaphezu kuka-250 ngonyaka.

Ingxabano ye-Range isitshela umsebenzi we-SUMIF ukuthi iyiphi iqembu lamaseli elingahlola uma uzama ukuthola imigomo ecacisiwe ye- > 250 .

Izinyathelo Zokufundisa

1. Ebhokisini lebhokisi, chofoza ku- Range line.
2. Amaseli Ekhanyayo D3 kuya ku-D9 ekhasini lokusebenzela lokufaka lezi zikhombisi zeseli njengebanga lokufundwa ngumsebenzi.

06 ka-08

Ukufaka Ukungqubuzana Okubalulekile

Ukufaka Ukungqubuzana Okubalulekile

Kulesi sibonelo uma idatha ebubanzi buka-D3: D12 ingaphezulu kuka- 250 ke ukudayiswa okuphelele kwalowo rekhodi kuzongeza umsebenzi we-SUMIF.

Nakuba idatha yangempela - njengombhalo noma izinombolo ezinjenge- "> 250" zingangenwa ebhokisini lengxoxo kule mpikiswano ngokuvamile kungcono ukwengeza idatha esitokisini ekhasini lokusebenzela bese ufaka leyo nkomba yeseli ebhokisini lengxoxo.

Izinyathelo Zokufundisa

1. Chofoza kulayini Wokuqondisa ebhokisini lengxoxo.
2. Chofoza kuseli e-E13 ukuze ufake leyo reference yeselula. Umsebenzi uzosesha uhla olukhethiwe kwisinyathelo sangaphambilini sedatha efana nalezi zindlela (eNyakatho).

Izinkomba Zezinhlamvu Khulisa Ukusebenza Okuhambisanayo

Uma ireferensi yeseli, efana ne-E12, ingenwe njenge- Criteria Argument, umsebenzi we-SUMIF uzobukeka kumdlalo kunoma yiluphi ulwazi olufakwe kulolo seli kusefeshini lokusebenzela.

Ngakho emva kokuthola ukuthengiswa okuphelele kwe-Sales Reps ngama-oda angaphezu kwama-250 kuzoba lula ukuthola ukuthengiswa okuphelele kwamanye izinamba zokuhleleka - njengokungaphansi kuka-100 - ngokushintsha nje
"> 250" kuya ". Umsebenzi uzovuselela ngokuzenzakalelayo futhi ubonise umphumela omusha.

07 ngo-08

Ukufaka i-Sum_range Argument

Ukufaka i- Sum_range Argument

I- Sum_range argument iyiqoqo lamaseli ukuthi umsebenzi kufanele uqokelele lapho uthola umdlalo ku- Range ingxabano ekhonjiswe esiteji sesi-5 sesifundo.

Le mpikiswano iyakhethwa futhi, uma ingashiywe, i-Excel inezela amangqamuzana acaciswe ku- Range argument.

Njengoba sifuna ukuthengiswa okuphelele kwe-Sales Reps ngama-oda angaphezu kwama-250 sisebenzisa idatha kukholomu Yonke Yokuthengisa njenge- Sum_range argument.

Izinyathelo Zokufundisa

1. Chofoza ku- Sum_range line ebhokisini lengxoxo.
2. Qinisekisa amaseli E3 ku-E12 kuspredishithi ukufaka lezi zikhombisi zeseli njenge-Sum_range argument.
3. Chofoza OK ukuvala ibhokisi lengxoxo bese uqedela umsebenzi we-SUMIF.
4. Impendulo ye- zero kufanele ivele esitokisini E12 - iseli lapho singene khona umsebenzi - ngoba asikazanga ukufaka idatha emkhakheni Wokunqunywa (D12).

Uma idatha ingene esitokisini se-D12 esiteji esilandelayo, uma inkundla yeRange yerekhodi iqukethe umdlalo we-criteria ku-D12 - idatha ku- Total Sales field ngoba lelo rekhodi lizofakwa kulo lonke inani lomsebenzi.

08 ngo-08

Ukungeza i-Criteria Yokusesha

Ukungeza i-Criteria Yokusesha

Isinyathelo sokugcina esifundweni ukufaka izindlela esizifuna ukuthi umsebenzi ufanane nazo.

Kulesi simo sifuna ukuthengiswa okuphelele kwe-Sales Reps ngemilayezo engaphezu kwe-250 ukuze sizongeze isikhathi > 250 kuya ku-D12 - iseli elikhonjisiwe emsebenzini njengoba liqukethe impikiswano yokunquma.

Izinyathelo Zokufundisa

1. Ngeselula D12 uhlobo > 250 bese ucindezela ukhiye wokungena kwikhibhodi.
2. Impendulo engu-$ 290,643.00 kufanele ivele esitokisini E12. Umgomo we->> 250 " uhlangene emaseli amane kukholomu D: D4, D5, D8, D9. Ngenxa yalokho izinombolo ezingqamuzaneni ezihambelana kukholomu E: E4, E5, E8, E9 zihlanganisiwe.
3. Uma uchofoza ku-cell E12, umsebenzi ophelele
   = I-SUMIF (D3: D9, D12, E3: E9) ivela kubha yefomula ngaphezulu kwephepha lokusebenzela.
4. Ukuthola inani lokuthengisa ngezinombolo ezahlukene ze-oda, thayipha inani, njenge- cell E12 bese ucindezela ukhiye we- Enter ekhibhodi.
5. Ukuthengisa okuphelele kwenani elifanele lamaseli kufanele livele esitokisini E12.