Count Cells of Data neExcel's SUMPRODUCT Function

Iko SUMPRODUCT basa iri muExcel inoshanda zvakanyanyisa iyo ichapa migumisiro yakasiyana zvichienderana nenharo dzakapinda.

Icho SUMPRODUCT basa inowanzoita inowedzera zvikamu zveimwechete kana mamwe mabhii uye wobva wawedzera kana kuvhara mishonga pamwe chete.

Asi nekugadzirisa maitiro emakakatanwa, SUMPRODUCT ichaverenga nhamba yemasero mune imwe nzvimbo yakatarwa ine data inogadzirisa zviyero zvakananga.

01 ye 04

SUMPRODUCT vs. COUNTIF uye COUNTIFS

Kubva pane Excel 2007, purogiramu inewo COUNTIF uye COUNTIFS mabasa ayo achakubvumira kuti uverenge masero anosangana neimwe kana zvinongedzo zvinongororwa.

Dzimwe nguva, kunyange zvakadaro, SUMPRODUCT iri nyore kushanda nayo kana zvasvika pakutsvaga mitemo yakawanda inoratidzika kune rumwe rutivi rwunoratidzwa pane muenzaniso uri mumufananidzo uri pamusoro.

02 of 04

SUMPRODUCT Function Syntax uye Arguments kuverenga MaCD

Mutsara webasa unoreva kurongeka kwebasa racho uye rinosanganisira zita rebasa, mabheji, vatengesi vemavara, uye nharo .

Kuti uwane basa rekuverenga masero pane kuti uite chinangwa chazvo, aya anotevera asingaenzaniswi mashoko ese anofanira kushandiswa neSUMPRODUCT:

= SUMPRODUCT ([chimiro1] * [condition2])

Tsanangudzo yekuti iyi syntax inoshanda yakataurwa sei pasi apa muenzaniso unotevera.

Muenzaniso: Kuverenga Makero Anosangana Nezviitiko Zvimwe

Sezvinoratidzwa mumuenzaniso uri mumufananidzo uri pamusoro, SUMPRODUCT inoshandiswa kuwana nhamba yose yemasero mu data range A2 kusvika B6 ine dhi pakati pehutano hwe25 ne75.

03 of 04

Kupinda muSUMPRODUCT Function

Kazhinji, nzira yakanakisisa yokupinda nayo mabasa muExcel ndeyekushandisa ibhokisi ravo rekukurukurirana , izvo zvinoita kuti zvive nyore kupinda mhirizhonga imwechete panguva imwe chete pasina kuisa mabheji kana makasita anoita separarator pakati pemakakatanwa.

Zvisinei, nokuti muenzaniso uyu unoshandisa nzira isina kukodzera yeSUMPRODUCT basa, nzira yebhokisi yebhokisi haigone kushandiswa. Pane kudaro, basa racho rinofanirwa kuiswa muchengeti yepasiti .

Mumufananidzo uri pamusoro, matanho anotevera akashandiswa kupinda muSUMPRODUCT muchikamu B7:

1. Dzvanya pane sero B7 mubasa rekushanda - nzvimbo iyo migumisiro yemabasa icharatidzwa
2. Nyora iyi inotevera fomu mu cell E6 yebasa rekushanda:

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

3. Mhinduro 5 inofanira kuonekwa muchitokisi B7 sezvo pane mazano mashanu chete muhupamhi-40, 45, 50, 55, uye 60 - iyo iri pakati pe25 ne75
4. Paunenge uchivhara pane cell B7 yakagadziriswa formula = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75)) inowanikwa mubhairi yepamusoro pane pepa rekushanda

04 we 04

Kuputsa pasi SUMPRODUCT Basa

Apo mamiriro ezvinhu anogadziriswa kuti apikisane, SUMPRODUCT inoratidzira chimwe nechimwe chezvinhu zvinopesana nemamiriro ezvinhu uye inodzorera kukosha kweBoolean (TRUE kana FALSE).

Nokuda kwezvinangwa zvekuverenga, Excel inopa kukosha kwe1 kune izvozvi zvinyorwa zviri CHOKWADI uye nhamba ye 0 yekugadzirisa zvinhu zviri FALSE.

Izvo zvakaenzana uye zeros mumhando imwe neimwe yakawanda pamwe chete:

• uye 1 x 1 kudzorera kukosha kwe1
• uye 1 x 0 kudzorera kukosha kwe 0

Izvo uye zeros zvino zvinopfupikiswa nebasa kutipa isu nhamba yehutano hunoenderana nemamiriro ose maviri.

Kana, funga nezvazvo nenzira iyi ...

Imwe nzira yekufunga nezveiyo SUMPRODUCT iri kuita ndeyekufunga nezvekuwedzera kwechiratidzo sechiitiko.

Nezvozvi mupfungwa, zvinongoitika kana zvose zviri zviviri zvasangana - nhamba dzakakura kudarika makumi maviri nemashanu uye pasi pe 75 - kuti kukosha kwe TRUE (iyo yakaenzana neyeyeuke) inodzorerwa.

Basa racho rinosanganisira zvose zvinodiwa kuti zvive nemugumisiro we 5.