Izibalo zeeDatha zeDatha kunye ne-Excel's SUMPRODUCT Function

Umsebenzi we-SUMPRODUCT kwi-Excel ngumsebenzi onobuchule obuninzi oza kunika iziphumo ezahlukeneyo kuxhomekeke kwiingxoxo ezifakiwe.

Into eyenziwa ngu-SUMPRODUCT umsebenzi ngokuphindaphindiweyo inokwandisa iimeko zenye okanye ngaphezulu kwaye zize zongeze okanye zibale iziveliso ndawonye.

Kodwa ngokulungiswa kwefom yeengxoxo, i-SUMPRODUCT iya kubala inani lamaseli kwinqanaba elinikezelweyo eliqukethe iinkcukacha ezihambelana nemigqaliselo ethile.

01 ngo 04

I-SUMPRODUCT vs COUNTIF ne-COUNTIFS

Ukususela kwi-Excel 2007, le nkqubo inemisebenzi ye- COUNTIF ne- COUNTIFS eya kukuvumela ukuba ubale iiseli ezidibanisa imigaqo ethile okanye ngaphezulu.

Ngamanye amaxesha, i-SUMPRODUCT ilula ukusebenza kunye nelokufika ekufumaneni iimeko ezininzi ezimalunga nelohlu olufanayo njengoko kuboniswa kumzekelo osemfanekisweni ongentla.

02 we 04

SUMPRODUCT Umsebenzi we-Syntax kunye neMigomo yokubala iiseli

I- syntax yomsebenzi ibhekisela kwisakhiwo somsebenzi kwaye ibandakanya igama lomsebenzi, iibakaki, abahluli be-comma kunye neengxabano .

Ukuze ufumane umsebenzi wokubala iiseli kunokuba wenze intsebenzo efanelekileyo, le syntax engekho-standard kufuneka isetyenziswe nge-SUMPRODUCT:

= I-SUMPRODUCT ([imeko1] * [imeko2])

Inkcazo yendlela le syntax esebenza ngayo ichazwe ngezantsi kule mzekelo olandelayo.

Umzekelo: Ukubala iiseli ezidibanisa iimeko ezininzi

Njengoko kuboniswe kumzekelo kumfanekiso ongentla, i-SUMPRODUCT isetyenziswe ukufumana inani elipheleleyo leeseli kwi-A2 ukuya kwi-B6 elineenkcukacha eziqulethwe phakathi kwedatha ye-25 ne-75.

03 we 04

Ukufaka iSUMPRODUCT Umsebenzi

Ngokuqhelekileyo, indlela engcono yokufaka imisebenzi kwi-Excel kukuba isebenzise ibhokisi yencoko yababini , eyenza kube lula ukufaka iingxabano ngeyodwa ngaphandle kokungena kwiibakaki okanye ii-commas ezenza njengabahluli phakathi kweengxoxo.

Nangona kunjalo, ngenxa yokuba lo mzekelo usebenzisa uhlobo olungagqithanga lomsebenzi we-SUMPRODUCT, indlela yebhokisi yencoko ayinakusetyenziswa. Endaweni yoko, umsebenzi kufuneka ufakwe kwifayile yeshidi .

Kulo mfanekiso ungentla, amanyathelo alandelayo asetyenziswa ukufaka i-SUMPRODUCT kwiseli B7:

1. Cofa kwiseli B7 kwiphepha lokusebenzela - indawo apho iziphumo zomsebenzi ziza kuboniswa
2. Thayipha ifom elandelayo kwiseli E6 kwiphepha lokusebenzela:

   = I-SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75))

3. Impendulo yesi-5 kufuneka ibonakale kwiseli ye-B7 njengoko kukho ixabiso elincinane ezintlanu - 40, 45, 50, 55, no-60 - eziphakathi kwama-25 no-75
4. Xa ucofa kwi-cell B7 ifom egcwalisiweyo = ISUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75)) ibonakala kwifom yefom ephezulu kwiphepha lokusebenzela

04 we 04

Ukuphulwa kweSUMPRODUCT Umsebenzi

Xa izimo zibekwe kwiingxabano, i-SUMPRODUCT ivavanya isicwangciso ngasinye sendawo ngokuchasene nesimo kwaye ibuyisela ixabiso leBoolean (TRUE okanye FALSE).

Ngeenjongo zokubala, i-Excel inikezela ixabiso le- 1 kulawo maqela adibeneyo aYI-TRUE kunye nexabiso le- 0 kwizinto ezilandelelanayo ezi-FALSE.

Ezihambelanayo kunye neefere kwiqela ngalinye ziphindwe ndawonye:

• kunye no-1 x 1 ubuyisela ixabiso le-1
• kunye no-1 x 0 ubuyisela ixabiso le-0

Ezi zinto kunye ne-zeros zidibaniswa ngumsebenzi ukusinika isibalo senani elijongene neemeko zombini.

Okanye, cinga ngako ngale ndlela ...

Enye indlela yokucinga ukuba yiyiphi i-SUMPRODUCT eyenzayo ukucinga ngophawu lokuphindaphinda njenge- AND condition.

Ngale nto engqondweni, kuba kuphela xa iimeko zidibeneyo - iinombolo ezinkulu kunama-25 nangaphantsi kwama-75 - ukuba inani le-TRUE (elilingana nelinye liyakhumbula) libuyiselwa.

Umsebenzi ubandakanya yonke imilinganiselo yinyaniso ukufika kwisiphumo se-5.