Shandisa Kushandisa Excel SUMPRODUCT kuti Uverenge Zvimwe Zvikonzero

Iko COUNTIFS basa , iro rinogona kushandiswa kuverenga nhamba dzenguva dhesi muzvikamu zviviri kana kupfuura zvemasero zvinosangana nemitemo yakawanda yakatanga kuiswa muExcel 2007. Isati yadaro, COUNTIF chete, yakagadzirirwa kuverenga nhamba yemaseli rumwe rutivi runosangana nechirevo chimwe chete, chaivepo.

Kune avo vanoshandisa Excel 2003 kana kuti zvinyorwa zvekare, kana kune avo vanoda imwe nzira kune COUNTIFS, panzvimbo yekuedza kuwana nzira yekuverenga mitemo yakawanda kushandisa COUNTIF, SUMPRODUCT basa rinogona kushandiswa panzvimbo.

Sezvo ne COUNTIFS, mitsara inoshandiswa neSUMPRODUCT inofanira kuva yakakura yakafanana.

Uyezve, basa racho rinongoratidzika mamiriro ezvinhu apo muongorori wegadziriro imwe neimwe inosangana panguva imwe chete-yakadai mumutsara mumwe chete.

Nzira yekushandisa nayo SUMPRODUCT Function

Izwi rinoshandiswa kweSUMPRODUCT basa kana riri kushandiswa kuverenga mitemo yakawanda yakasiyana pane inowanzoshandiswa nemabasa:

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

Criteria_range - boka remasero basa riri kutsvaga.

Zvirongwa - inosarudza kana sero inofanira kuverengwa kana kwete.

Mumuenzaniso uri pasi apa, tichaverenga mitsetse chete mu data sampuli E1 kusvika kuG6 iyo inosangana nematongerwo akajeka ematatu ese e data.

Mitsara ichaongororwa chete kana ichinge ichiita zvinotevera:
Column E: kana nhamba isiri pasi kana yakaenzana ne2;
Column F: kana nhamba yakaenzana ne4;
Chikamu G: kana nhamba yakakura kudarika kana yakaenzana ne5.

Muenzaniso uchishandisa Excel SUMPRODUCT Function

Cherechedza: Sezvo izvi zvisina kushandiswa kweSUMPRODUCT Function, basa harigoni kupinda mukati rebhokisi rebhokisi , asi rinofanira kuiswa kune chinangwa chese.

1. Isa data inotevera inhengo E1 kusvika E6: 1, 2, 1, 2, 2, 8.
2. Isa data inotevera inhengo dzeF1 kusvika F6: 4, 4, 6, 4, 4, 1.
3. Isa data inotevera inhengo dzeG1 kusvika kuG6: 5, 1, 5, 3, 8, 7.
4. Dzvanya pane sero I1 - nzvimbo iyo migumisiro yemabasa icharatidzwa.
5. Tora zvinotevera mumatare I1:
   1. = (E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) uye shandisai Enter key pane keyboard.
6. Mhinduro 2 inofanira kuonekwa muchitokisi I1 sezvo pane mitsetse miviri chete (mitsetse 1 ne5) iyo inosangana nezvose zvitatu zvinotaridzwa pamusoro apa.
7. Basa rakakwana = SUMPRODUCT ((E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) inowanikwa mubhairi yepamusoro pane pepa rekushandura paunenge uchisarudza pane cell I1.