Umsebenzi we-SUMPRODUCT ku-Excel yinto ehambisana nemisebenzi eminingi eyokwenza imiphumela ehlukene ngokuya ngezimpikiswano ezifakiwe.
Okushiwo yi-SUMPRODUCT umsebenzi okwenziwe ngokuphindaphindiwe kwandisa izakhi ezithile noma ngaphezulu bese ufaka noma ukuhlanganisa imikhiqizo ndawonye.
Kodwa ngokulungisa isimo sezingxabano, i-SUMPRODUCT izobala inani lamaseli ebangeni elinikeziwe equkethe idatha ehlangabezana nezidingo ezithile.
01 ngo-04
I-SUMPRODUCT vs. COUNTIF ne-COUNTIFS
Kusukela ku-Excel 2007, lolu hlelo lunezinhlelo ezingu- COUNTIF ne- COUNTIFS ezizokuvumela ukuthi ubale amaseli ahlangabezana nesilinganiso esisodwa noma ngaphezulu.
Ngezinye izikhathi, i-SUMPRODUCT ilula ukusebenza uma kuziwa ekutholeni izimo eziningi eziphathelene nobubanzi obufanayo njengoba kuboniswa isibonelo esibhalwe esithombeni ngenhla.
02 ka 04
SUMPRODUCT Function Syntax and Arguments Ukubala Amaseli
I- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, ukwahlukanisa kwama-comma, nokuphikisana .
Ukuze uthole umsebenzi wokubala amaseli kunokuba wenze inhloso yayo ejwayelekile, le syntax engezona ejwayelekile kufanele isetshenziswe nge-SUMPRODUCT:
= SUMPRODUCT ([isimo1] * [isimo2])
Incazelo yokuthi le syntax isebenza kanjani ichazwe ngezansi kwesibonelo esilandelayo.
Isibonelo: Ukubala Amaseli ahlangabezana Nezimo Eziningi
Njengoba kuboniswe kusibonelo esithombeni esingenhla, i-SUMPRODUCT isetshenziselwa ukuthola inani lenamba lamaseli ku-range data A2 kuya ku-B6 equkethe idatha phakathi kwamanani angu-25 no-75.
03 ka 04
Ukufaka umsebenzi we-SUMPRODUCT
Ngokuvamile, indlela engcono kakhulu yokufaka imisebenzi ku-Excel ukusebenzisa ibhokisi labo lengxoxo , okwenza kube lula ukufaka izingxabano ngesikhathi esisodwa ngaphandle kokufaka amabakaki noma ama-commas asebenza njengabahlukanisi phakathi kwezimpikiswano.
Noma kunjalo, ngoba lesi sibonelo sisebenzisa uhlobo olungavamile lomsebenzi we-SUMPRODUCT, indlela yebhokisi lengxoxo ayikwazi ukusetshenziswa. Esikhundleni salokhu, umsebenzi kumele ufakwe kufilda lokusebenzela .
Esikhathini esingenhla, izinyathelo ezilandelayo zisetshenziselwa ukungenisa i-SUMPRODUCT esitokisini B7:
- Chofoza kuseli B7 kushidi lokusebenzela - indawo lapho imiphumela yomsebenzi izoboniswa khona
- Thayipha ifomula elandelayo kwiselula E6 yephepha lokusebenzela:
= SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75))
- Impendulo 5 kufanele ivele esitokisini B7 njengoba kunamanani amahlanu kuphela ebangeni - 40, 45, 50, 55, no-60 - phakathi kuka-25 no-75
- Uma uchofoza ku-cell B7 ifomula eqediwe = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75)) ivela kubha yefomula ngaphezulu kwephepha lokusebenzela
04 ka 04
Ukuphulukisa umsebenzi we-SUMPRODUCT
Uma izimo zibekwe ukuphikisana, i-SUMPRODUCT ihlola isici ngasinye sendawo ngokumelene nesimo bese ibuyisela inani le-Boolean (TRUE noma FALSE).
Ngenhloso yezibalo, i-Excel inikezela inani le- 1 kulawo macebo ayingxenye eyi-TRUE kanye nenani le- 0 yokuhlela izinto ezi-FALSE.
Okuhambisana nabo kanye ne-zeros ngayinye kuhlanganiswa ndawonye:
- nge-1 x 1 kubuyisa inani le-1
- nge-1 x 0 ukubuyisa inani le-0
Lezi kanye ne-zeros zifingqa ngomsebenzi ukusinika inani lamanani ahlangabezana nezimo zombili zombili.
Noma, cabanga ngakho ngale ndlela ...
Enye indlela yokucabanga ukuthi yikuphi okushiwo yi-SUMPRODUCT ukucabanga ngesibonakaliso sokuphindaphinda njenge- AND AND condition.
Ngalokhu engqondweni, kungukuthi kuphela lapho izimo zombili zifinyelelwa - izinombolo ezinkulu kunama-25 nangaphansi kuka-75 - ukuthi inani le-TRUE (elingana nelinye elikhunjulwayo) libuyiselwa.
Umsebenzi uhlanganisa wonke amanani weqiniso ukufika emphumela we-5.