01 ka 01
I-Excel SUMPRODUCT Umsebenzi
Isisindo esibucayi nesibalo esiphezulu esingenakubalwa
Ngokuvamile, uma kubalwa isilinganiso noma isilinganiso samathemithi, inombolo ngayinye inenani elilinganayo noma isisindo.
Isilinganiso sibalwa ngokungeza izinombolo zezinombolo ndawonye bese uhlukanisa inani lezibalo ngenani lamagugu ebangeni .
Isibonelo singaba (2 + 3 + 4 + 5 + 6) / 5 esinikeza isilinganiso esinganisindo esingaphezu kwesine.
Ku-Excel, izibalo ezinjalo zenziwa kalula usebenzisa umsebenzi WOKUSEBENZA .
Isilinganiso esilinganisiwe, ngakolunye uhlangothi, sibheka inombolo eyodwa noma ngaphezulu ebangeni okufanele lizuze ngaphezulu, noma libe nesisindo esikhulu kunezinye izinombolo.
Isibonelo, amamaki athile esikoleni, njenge-midterm kanye nezivivinyo zokugcina, ngokuvamile avumelekile ngaphezu kokuhlolwa okuvamile noma izabelo.
Uma ukulinganisa kusetshenziselwa ukubala ukugcina kokugcina komfundi phakathi nendawo kanye nokuhlolwa kokugcina kuzonikezwa isisindo esikhulu.
Ku-Excel, isilinganiso esilinganisiwe singabalwa ngokusebenzisa umsebenzi we- SUMPRODUCT .
Indlela okuqala ngayo ukusebenza komsebenzi
Yikuphi okushiwo yi-SUMPRODUCT okwandisa izakhi zamathuluzi amabili noma ngaphezulu bese wengeza noma uhlanganisa imikhiqizo.
Isibonelo, esimweni lapho izingxenye ezimbili ezinezici ezine zifakelwa njengezingxabano zomsebenzi we-SUMPRODUCT:
- isici sokuqala se-array1 sandezwe ngento yokuqala ku-array2;
- isici sesibili se-array1 sinwetshwa yi-element yesibili ye-array2;
- isici sesithathu se-array1 sandezwe nge-third element ye-array2;
- Isici sesine se-array1 sandezwe ngento yesine ye-array2.
Okulandelayo, imikhiqizo yezinhlelo ezine zokubuyabuyelela ihlanganiswa futhi ibuyiselwe ngumsebenzi njengomphumela.
I-Excel SUMPRODUCT Umsebenzi we-Syntax ne-Arguments
I- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, nokuphikisana.
I-syntax yomsebenzi we-SUMPRODUCT yilezi:
= I-SUMPRODUCT (i-array1, i-array2, i-array3, ... i-array255)
Imibono yomsebenzi we-SUMPRODUCT yilezi:
i-array1: (edingekayo) ingxabano yokuqala yokuhlela.
i-array2, i-array3, ... i-array255: (okukhethwa kukho) izigcawu ezengeziwe, kuze kufike ku-255. Ngama-arrays amabili noma ngaphezulu, lo msebenzi uphindaphinda izakhi zohlelo ngalunye ndawonye bese wengeza imiphumela.
- izici ezihlanganisiwe zingabhekiswa esitokisini endaweni yedatha kwiphepha lokusebenzela noma izinombolo ezihlukaniswe opharetha bama- arithmetic - njenge-plus (+) noma amancane ampawu (-). Uma izinombolo zingena ngaphandle kokuhlukaniswa ngabasebenzisi, i-Excel iyabathinta njengedatha yombhalo. Lesi simo sihlanganiswe esibonelweni esingezansi.
Qaphela :
Zonke iziphakamiso ezifanayo kufanele zibe usayizi ofanayo. Noma, ngamanye amazwi, kumele kube nenani elifanayo lezinto ezakhiweni ngasinye. Uma kungenjalo, i-SUMPRODUCT ibuyisela i- #VALUE! inani lephutha.
Uma ngabe ezinye izici ezingezansi azizona izinombolo - njengedatha yombhalo - I-SUMPRODUCT iyabathinta njengama-zero.
Isibonelo: Bala isilinganiso esisindowe ku-Excel
Isibonelo esiboniswe esithombeni ngenhla sinquma isilinganiso esilinganisiwe somaki wokugcina womfundi usebenzisa umsebenzi we-SUMPRODUCT.
Umsebenzi ufeza lokhu ngo:
- ukwandisa amamaki ahlukahlukene ngesisindo somzimba ngamunye;
- engeza imikhiqizo yalokhu imisebenzi yokubuyabuyelela ndawonye;
- wahlukanisa ngenhla ngenani le-factor factor 7 (1 + 1 + 2 + 3) yokuhlolwa okune.
Ukufaka i-Formula Formula
Njengeminye imisebenzi eminingi ku-Excel, i-SUMPRODUCT ivame ukufakwa kwiphepha lokusebenzela usebenzisa ibhokisi lengxoxo yomsebenzi. Kodwa-ke, njengoba ifomula yokulinganisa isebenzisa i-SUMPRODUCT ngendlela engavamile - umphumela womsebenzi uhlukaniswe yisisindo somzimba - ifomula yokulinganisa kumele ifakwe kufaneli leshidi lokusebenza .
Izinyathelo ezilandelayo zisetshenziselwa ukungena ifomula yokulinganisa ibe seli cell C7:
- Chofoza kuseli C7 ukuze wenze iseli elisebenzayo - indawo lapho uphawu lokugcina lomfundi luzoboniswa khona
- Thayipha ifomula elandelayo esitokisini:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Cindezela ukhiye wokungena kwikhibhodi
- Impendulo 78.6 kufanele ivele esitokisini C7 - impendulo yakho ingaba nezindawo ezingaphezu kwedesimali
Isilinganiso esingenakubalwa samamaki amane afanayo singaba ngu- 76.5
Njengoba umfundi abe nemiphumela engcono kakhulu phakathi novivinyo lwakhe lokugcina kanye nokuhlolwa kokugcina, ukulinganisa isilinganiso kusiza ukuthuthukisa uphawu lwakhe jikelele.
Ukuhluka kwefomula
Ukugcizelela ukuthi imiphumela ye-SUMPRODUCT umsebenzi ihlukaniswe ngesamba sezilinganiso zeqembu ngalinye lokuhlola, umshayeli - ingxenye yokwenza ukwahlukana - wangena njengo (1 + 1 + 2 + 3).
I-formula yonke yokulinganisa ingahle ibe lula ngokufaka inombolo 7 (isibalo sezisindo) njenge-divisor. Ifomula yayiyoba:
= SUMPRODUCT (B3: B6, C3: C6) / 7
Lokhu kukhetha kuhle uma inani lezinto ezisezingeni elilinganiselwe lincane futhi zinganezelwa kalula, kodwa liba ngempumelelo kangako njengoba inani lezinto ezisezingeni elilinganisayo landa ukwenza ukufaka kwabo kube nzima nakakhulu.
Enye indlela, futhi mhlawumbe isinqumo esingcono kakhulu - ngoba isebenzisa izinkomba zocingo esikhundleni sezinombolo ezigcwele umshayeli - kungaba ukusebenzisa umsebenzi we- SUM ukuqoqa umshayeli ngefomu:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Ngokuvamile kungcono ukufaka izingqikithi zamangqamuzana kunezinombolo zangempela zibe amafomula ngoba yenza kube lula ukuwabuyekeza uma idatha yefomula iguquka.
Isibonelo, uma izici ezilinganiselwe zeSabelo zishintshiwe zibe ngu-0.5 kusibonelo nakuzo Izivivinyo kuya ku-1.5, izinhlobo ezimbili zokuqala zefomula kuzodingeka zihlelwe ngesandla ukuze zilungise umshayeli.
Ngokwehluka kwesithathu, idatha kuphela kumaseli B3 ne-B4 adinga ukubuyekezwa futhi ifomula izobuyisela kabusha umphumela.