Gwiritsani Ntchito SUMPRODUCT ya Excel kuwerengera Zowonjezera Zambiri

Ntchito COUNTIFS, yomwe ingagwiritsidwe ntchito kuwerengetsera nambala ya deta m'mizere iwiri kapena yambiri ya maselo imakumana ndi njira zambiri zomwe zinayambitsidwa ku Excel 2007. Zisanachitike, COUNTIF yokha, yomwe yapangidwa kuti iwerengere maselo Mtundu umene umagwirizana ndi ndondomeko imodzi, inalipo.

Kwa iwo omwe amagwiritsa ntchito Excel 2003 kapena matembenuzidwe oyambirira, kapena omwe akufuna njira yina ku COUNTIFS, m'malo moyesera kupeza njira yowerengera njira zambiri pogwiritsa ntchito COUNTIF, ntchito SUMPRODUCT ingagwiritsidwe ntchito m'malo mwake.

Monga ndi COUNTIFS, mayendedwe ogwiritsidwa ntchito ndi SUMPRODUCT ayenera kukhala ofanana mofanana.

Kuwonjezera apo, ntchitoyi imakhala ndi zochitika pamene mndandanda wa mndandanda uliwonse umakumananso nthawi imodzi - monga mumzere womwewo.

Momwe mungagwiritsire ntchito ntchito SUMPRODUCT

Mawu omasulira omwe amagwiritsidwa ntchito pa SUMPRODUCT ntchito pamene akugwiritsidwa ntchito kuwerengera njira zambiri ndi zosiyana ndi zomwe amagwiritsidwa ntchito ndi ntchito:

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

Criteria_range - gulu la maselo ntchitoyo ndi kufufuza.

Zolinga - zimayesa ngati selo liyenera kuwerengedwa kapena ayi.

Mu chitsanzo pansipa, tidzakhala ndi mizere yokha yomwe ili mu deta chitsanzo E1 mpaka G6 yomwe imakwaniritsa ndondomeko yoyenera pa zigawo zitatu za deta.

Mizere idzawerengedwa ngati idzapeza zotsatirazi:
Pulogalamu E: ngati chiwerengero sichichepera kapena chikufanana ndi 2;
Pulogalamu F: ngati nambala ikufanana ndi 4;
Pulogalamu G: ngati chiwerengero chikuposa kapena chikufanana ndi 5.

Chitsanzo Pogwiritsa Ntchito Excel SUMPRODUCT Ntchito

Zindikirani: Popeza izi sizomwe amagwiritsidwa ntchito pa SUMPRODUCT Function, ntchitoyo silingalowe ntchito pogwiritsa ntchito bokosi , koma iyenera kuyimilidwa mu selo lolunjika.

1. Lowani deta zotsatirazi mu maselo E1 mpaka E6: 1, 2, 1, 2, 2, 8.
2. Lowani deta zotsatirazi mu maselo F1 mpaka F6: 4, 4, 6, 4, 4, 1.
3. Lowani deta zotsatirazi mu maselo G1 mpaka G6: 5, 1, 5, 3, 8, 7.
4. Dinani pa selo I1 - malo omwe zotsatira zotsatira zidzasonyezedwe.
5. Lembani zotsatirazi mu selo I1:
   1. = sumproduct ((E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) ndipo pindikizani fungulo lolowamo lolowamo .
6. Yankho lachiwiri liyenera kuoneka mu selo I1 popeza pali mizere iwiri (mizera 1 ndi 5) yomwe imakwaniritsa zonse zitatu zomwe zili pamwambapa.
7. Ntchito yeniyeni = SUMPRODUCT ((E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) amapezeka mu barolo lazenera pamwamba pa tsambalo pamene mukasindikiza selo I1.