Nhamba Dhamba Iyo Inosangana Nemazano Akananga neExcel COUNTIFS Function

Excel ya COUNTIFS basa inogona kushandiswa kuverenga nhamba yezvinyorwa zvepadhesi mumusarudzo yakasarudzwa iyo inofanidza zvidzidzo.

COUNTIFS inowedzera kubatsira kwe COUNTIF basa nekukubvumira kuti utaure kubva pane zvinokwana 2 kusvika ku122 kwete pane imwe chete pane COUNTIF.

Kazhinji, COUNTIFS inoshanda nemitsetse ye data inonzi marekhodi. Mune zvinyorwa, dhesi rimwe nerimwe rega kana munda mumutsara rinobatana - rakadai sezita rekambani, kero uye nhamba yefoni.

COUNTIFS inotarisa mitsara yakananga mumatambudziko maviri kana kupfuura mune rekodhi uye kana ichinge iwana mutambo kumunda umwe neumwe wakatarwa inhoroondo yakaverengwa.

01 ye 09

ONAI Kushanda Nhanho Nhanho Dzidzo

Muchikwata che COUNTIF nhanho chechidzidzo takabatana nechirevo chimwe chete chevatengesi vekutengesa avo vakanga vatengesa zvinopfuura 250 mairi mugore.

Muchidzidzo ichi, tichaisa chipiri chechipiri tichishandisa COUNTIFS - iyo yekutengesa vashandi muEast East sales iyo yakaita zvinodarika makumi maviri nemashanu kutengeswa mugore rapfuura.

Kugadzirisa mimwe mimwe mitemo inogadzirwa nekufungidzira zvimwe Criteria_range uye nharo dzeCriteria dze COUNTIFS.

Kutevera matanho mumusoro wekudzidziswa pasi apa kunokufambisa kuburikidza nekusika uye kushandisa COUNTIFS basa rinoonekwa mumufananidzo pamusoro apa.

Zvidzidzo Misoro

• Kupinda Tutorial Data
• Syntax ya COUNTIFS Function
• Kutanga COUNTIFS Function
• The Criteria_range1 Kurwisana
• ICriteria1 Kupokana
• The Criteria_range2 Kurwisana
• The Criteria2 Argument
• Uye Mhinduro ndeyo ..

02 of 09

Kupinda Tutorial Data

Nhanho yekutanga yekushandisa COUNTIFS basa iri muExcel ndiko kupinda data.

Nokuti izvi zvidzidzo zvinopinda munharaunda inoonekwa mumufananidzo uri pamusoro pemasero D1 kusvika F11 eExpcel worksheet .

Mutsara 12 pasi pe data tichawedzera COUNTIFS basa uye mitsva mbiri yekutsvaga:

• vatengesi vanobva kuEast sales sales
• mirairo inopfuura 250 yakaiswa gore

Dzidzo yemirayiridzo haisanganisi maitiro ekufambisa pepaji.

Izvi hazvizovhiringidzi nekupedzisa tutori. Tsamba yako yebasa ichaita yakasiyana nedzimwe akaratidzwa, asi COUNTIFS basa ichakupa mhinduro imwe chete.

03 of 09

Syntax ya COUNTIFS Function

MuExpcel, syntax yebasa inoreva kurongeka kwebasa uye inosanganisira zita rebasa, mabheketi, uye nharo .

Izwi rechiito che COUNTIFS basa nderekuti:

= COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Kusvika kusvika 127 Criteria_range / Peji dzepakati dzinogona kutsanangurwa mubasa racho.

IAIDSIFS Function's Arguments

Mhinduro dzebasa dzinotaurira COUNTIFS zvipi zvatinenge tichiedza kuenzanisa uye ndezvipi zvinyorwa zvekutsvaga kuti zviwane izvi zvidzidzo.

Zvose zvinopesana mubasa iri zvinodiwa.

Criteria_range - boka remasero basa nderokutsvaga mutsara kune imwecheteyo Criteria argument.

Zvinokonzerwa - kukosha kwatinoedza kuenzanisa mune rekodhi re data. Dhiyabhorosi chaiyo kana sero rinoshandiswa kune data rinogona kupinda mukati menyaya iyi.

04 of 09

Kutanga COUNTIFS Function

Kunyange zvazvo zvichibvira kungonyora COUNTIFS basa uye nharo dzayo muchitokisi mune pepa pepaji , vanhu vazhinji vanozviona zviri nyore kushandisa bhokisi rebasa rebasa kuti vapinde basa.

Tutorial Steps

1. Dzvanya pachitokisi F12 kuti uite sero rinoshanda . Apa ndipo patinopinda maitiro COUNTIFS.
2. Dzvanya paMafomu eTom.
3. Sarudza Mamwe Mabasa> Statistical kubva kubhoni kuti uzarure basa rinoderera pasi.
4. Dzvanya pa COUNTIFS mukati mechirongwa kuti uwedzere bhokisi rebasa rebasa.

Dhenda yatinopinda mumiganhu isina kuvhara mubhokisi yebhokisi ichaita nharo dze COUNTIFS basa.

Sezvambotaurwa, izvi zvinopikisa zvinoreva basa iro zvatiri kuedza kuenzanisa uye ndedzipi dheta rekutsvaga kutsvaga kuwana idzi zvinotarisirwa.

05 of 09

Kupinda Criteria_range1 Kurwisana

Muchidzidzo ichi tiri kuedza kuenzanisa zviyero zviviri munhoroondo ye data imwe neimwe:

1. Sales sales agents kubva kuEast sales sales.
2. Vatengesi vekutengesa vanove nemayero anopfuura 250 ekutengeserana kwegore.

Iko Criteria_range1 nharo inoratidza huwandu hwemasero aya COUNTIFS ndeyekutsvaga paanenge achiedza kufanana nekutanga-inotanga-East East sales.

Tutorial Steps

1. Mubhokisi rebhokisi , tora paCriteria_range1 mutsara.
2. Simbisai masero D3 kusvika kuD9 mubasa rekushandisa kuti uise izvi zvinyorwa zvinyorwa sezvenzira yekutsvaga nemabasa .

06 of 09

Kupinda Criteria1 Kurwisana

Muchidzidzo ichi mitoo yekutanga yatinotarisa kuti tifananidze ndeyo kana dhesi iri muhara D3: D9 yakaenzana neEast .

Kunyange zvazvo data chaiyo - yakadai sezwi East - inogona kupinda mubhokisi rebhokisi nekuda kwekukakavadzana kunowanzova nani kupinda muchitsauko chesaferensi panzvimbo ye data panzvimbo yebhokisi mubhokisi rebhokisi.

Tutorial Steps

1. Dzvanya paCriteria1 mutsara mubhokisi rebhokisi .
2. Dzvanya pane sero D12 kuti uise iyo sero rinenge riri mubhokisi rebhokisi.
3. Izwi rekutsvaga East richawedzerwa kune cell D12 muchikamu chekupedzisira chekudzidzisa.

Kushandiswa Kwemafoni Sei Kuwedzera COUNTIFS Kusiyanisa

Kana sero rinoshandiswa, rakadai saD12, rakapinda seCriteria Argument, iyo COUNTIFS basa inotarisa mitsva kune chero dhesi yakanyorerwa mune iyo seki mumushandi webasa.

Saka mushure mekuverenga nhamba yevashumiri vanobva kuMabvazuva kunzvimbo iri nyore kuwana dhechero imwechete kune imwe nzvimbo yekutengesa kuburikidza nekushandura East kusvika kuchamhembe kana kumavirazuva muchikamu D12. Basa racho richavandudza uye rinoratidza chibereko chitsva.

07 of 09

Kupinda Criteria_range2 Kurwisana

Sezvambotaurwa kare, mune iyi tutori isu tiri kuedza kuenzanisa zviyero zviviri mumabhuku ose e data

1. Sales sales agents kubva kuEast sales sales.
2. Sales agents vanoita zvinodarika makumi maviri zvekutengesa gore rino.

Iko Criteria_range2 nharo inoratidza huwandu hwemasero aya COUNTIFS ndeyekutsvaga paanenge achiedza kufanana neyechipiri mitemo - vatengesi vanotengesa avo vakatengesa zvinopfuura 250 mairi gore rino.

Tutorial Steps

1. Mubhokisi rebhokisi , tora paCriteria_range2 mutsara.
2. Simbisai masero E3 kusvika E9 mubasa rekushandisa kuti uise izvi zvinyorwa zvese seyeri yepiri kuti iongorwe nebasa racho .

08 of 09

Kupinda Criteria2 Kurwisana

Kupinda Criteria2 Kurwisana uye Kuzadzisa COUNTIFS Function

Muchidzidzo ichi chechipiri nzira yatinotarisa kuti tifananidze ndeye kana dhesi iri muhutatu E3: E9 inopfuura 250 mabhizimisi ekutengesa.

Sezvo neCriteria1 nharo , tichapinda sero rinoreva nzvimbo yeCriteria2 mubhokisi rebhokisi panzvimbo ye data pachayo.

Tutorial Steps

1. Dzvanya paCriteria2 mutsara mubhokisi rebhokisi .
2. Dzvanya pane cell E12 kuti uise iyo cell reference. Basa racho richatsvaga mararamiro akasarudzwa mudanho rekare kune dhese inofanidza izvi.
3. Dzvanya OK kuti upedze COUNTIFS basa uye uvhare bhokisi rebhokisi.
4. Mhinduro ye zero ( 0 ) ichaonekwa muchitokisi F12 - sero patakapinda mubasa - nokuti hatina kuwedzera tsanangudzo kuCriteria1 uye Criteria2 minda (C12 naD12). Kusvikira taita, hapana chinhu che COUNTIFS kuverenga uye saka iyo yose inogara pa zero.
5. Mitsva yekutsvaga ichawedzerwa mu danho rinotevera rekudzidzisa.

09 of 09

Kuwedzera Mitsva yekutsvaga uye Kuzadzisa Tutori

Nhanho yekupedzisira mune zvidzidzo ndeyekuwedzera deta kumasero ari mubhuku rebasa rinotsanangurwa seine nharo dzeCriteria .

Tutorial Steps

1. Muchitokisi D12 rudzi rweEast uye taurai Enter Enter pane keyboard.
2. Muchitokisi E12 rudzi > 250 uye pindai muIno yeKuisa pane keyboard (i ">" ndiyo chiratidzo chekukuru kupfuura in Excel).
3. Mhinduro 2 inofanira kuonekwa muchitoro F12.
4. Vatori vaviri chete - Ralph naSam - vanoshanda munzvimbo yeEast sales uye vakaita mirairo inopfuura 250 yegore, naizvozvo, zvinyorwa izvi zviviri chete zvinoverengwa nemabasa.
5. Kunyange zvazvo Marita ari kushanda kumabvazuva, aive nemirairo inopfuura 250 uye, naizvozvo, zvinyorwa zvake hazviverengwi.
6. Saizvozvowo, Joe naTom vose vaiva nemirairo inopfuura 250 yegore, asiwo haishandisi muEast East yekutsvaga kuitira kuti zvinyorwa zvavo zvirege kuverengerwa.
7. Paunenge uchitsvaga pane cell F12, basa rakakwana
   = COUNTIFS (F3: F9, D3: D9, D12, E3: E9, E12) inowanikwa mubharani yepamusoro pane pepa pepaji .