Yenza Ukusetshenziswa kwe-Excel's SUMPRODUCT ukuze Ubale Izinyathelo Eziningi

Umsebenzi we-COUNTIFS, ongasetshenziselwa ukubala inani lezinombolo ezinhlangothini ezimbili noma ngaphezulu zamaseli ahlangabezana nezimiso eziningi eza kuqala ku-Excel 2007. Ngaphambi kwalokho, kuphela COUNTIF, eyenzelwe ukubala inani lamaseli ububanzi obuhlangabezana nesimiso esisodwa, sitholakale.

Kulabo abasebenzisa i-Excel 2003 noma izinguqulo zangaphambilini, noma kulabo abafuna okuhlukile ku-COUNTIFS, kunokuzama ukuthola indlela yokubala izinqubo eziningi usebenzisa i-COUNTIF, umsebenzi we-SUMPRODUCT ungasetshenziswa esikhundleni.

Njengama-COUNTIFS, amarethini asetshenziswa nge-SUMPRODUCT kufanele abe nosayizi ofanayo.

Ngaphezu kwalokho, umsebenzi ubala kuphela izimo lapho umkhawulo ngamunye kuhlanganiselwe khona ngesikhathi esisodwa - njengamarori afanayo.

Indlela yokusebenzisa umsebenzi we-SUMPRODUCT

I- syntax esetshenziselwa umsebenzi we-SUMPRODUCT lapho isetshenziselwa ukubala izindlela eziningi zihlukile kunejwayelekile evame ukusetshenziswa ngumsebenzi:

= I-SUMPRODUCT (I-Criteria_range-1, I-Criteria-1) * (Criteria_range-2, Criteria-2) * ...)

I-Criteria_range - iqembu lamaseli umsebenzi ukucinga.

Imigomo - inquma ukuthi iseli kufanele libale noma cha.

Esikhathini esingezansi, sizobala kuphela imigqa kusampula yedatha E1 kuya ku-G6 ehlangabezana nezilinganiso ezicacisiwe kuzo zonke izintathu zedatha.

Imigqa izobalwa kuphela uma ihlangabezana nalezi zindlela ezilandelayo:
Ikholomu E: uma inombolo ingaphansi noma ilingana no-2;
Ikholomu F: uma inombolo ilingana no-4;
Ikholomu G: uma inombolo ingaphezulu noma ilingana no-5.

Isibonelo Ukusebenzisa i-Excel SUMPRODUCT Function

Qaphela: Njengoba lokhu kungukusetshenziswa okungavamile komsebenzi we-SUMPRODUCT, umsebenzi awukwazi ukungena usebenzisa ibhokisi lebhokisi , kodwa kufanele ifakwe kufilini elitshelwe.

1. Faka idatha elandelayo kumaseli E1 kuya ku-E6: 1, 2, 1, 2, 2, 8.
2. Faka idatha elandelayo kumaseli F1 kuya ku-F6: 4, 4, 6, 4, 4, 1.
3. Faka idatha elandelayo kumaseli G1 kuya ku-G6: 5, 1, 5, 3, 8, 7.
4. Chofoza kuseli I1 - indawo lapho imiphumela yomsebenzi izoboniswa khona.
5. Thayipha lokhu okulandelayo kuseli I1:
   1. = i-sumproduct ((E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) bese ucindezela ukhiye wokungena kwikhibhodi.
6. Impendulo 2 kufanele ivele esitokisini I1 ngoba kunemigqa emibili kuphela (imigqa 1 no-5) ehlangabezana nazo zonke izindlela ezintathu ezibalwe ngenhla.
7. Umsebenzi ophelele = I-SUMPRODUCT ((E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) ivela ebhafini yefomula ngaphezu kwephepha lokusebenzela uma uchofoza esitokisini I1.