Excel AVERAGEIF: Tsvaga Average for Specific Criteria

IAVERAGEIF basa inobatanidza iyo IF function uye AVERAGE basa mu Excel. Uku kusanganiswa kunokubvumira kuti uwane huwandu kana kuti arithmetic zvinoreva zvemashoko aya pane zvakasarudzwa zve data zvinosangana nezvirongwa.

IYO IF chikamu chebasa chinosarudza kuti chii chinosangana nematauriro akafanirwa uye chikamu cheAVERAGE chinotarisa chiyero kana kuti chinoreva.

Kazhinji, AVERAGE IF inoshandiswa nemitsetse ye data inonzi marekhodi. Mune zvinyorwa , yese data iri mune imwe sero iri mumutsara yakabatana - yakadai sezita rekambani, kero uye nhamba yefoni.

AVERAGE IF inotarisa zvinotarisirwa mune imwe sero kana munda munhoroondo uye, kana ikawana mutsara, iyo inowanikwa iyo data kana dhenda mune imwe nzvimbo yakatarwa mune imwe rekodhi.

Kuti AVERAGEIF Function Works sei

Iyi dzidzo inoshandisa AVERAGE IF basa kuti uwane kuenzanisira kwekutengeswa kwemakore kweEast yekutsvaga munharaunda yezvinyorwa zvematare e data.

Kutevera matanho ari mumusoro wekudzidzisa mazano pasi apa anokufambisa kuburikidza nekugadzira uye kushandisa KUTAURA IF basa rinowanikwa mumufananidzo uri pamusoro kuti uverenge mutengo wekutengesa gore rose.

Zvidzidzo Misoro

• Kupinda Tutorial Data
• IAVERAGE IF Syntax yeShoko
• Kutanga IVERVER IF IF Function
• Kupinda muRange Argument
• Kupinda muChiteria Argument
• Kupinda Average-range Argument
• Uye Mhinduro ndeyo ..

Kupinda Tutorial Data

Nhanho yekutanga yekushandisa AVERAGE IF basa mu Excel ndeyokuisa data .

Isa data mumasero C1 kusvika E11 epa Excel worksheet sezvinoonekwa mumufananidzo uri pamusoro.

Cherechedza: Zvidzidzo zvekudzidzisa hazvirevi kutora matanho epa pepaji.

Izvi hazvizovhiringidzi nekupedzisa tutori. Tsamba yako yebasa ichaita yakasiyana nemuenzaniso wakaratidzwa, asi AVERAGE IF basa richakupa mhinduro imwechete.

Mashoko pamusoro pekugadzirisa mafungiro akafanana neaya aonekwa pamusoro apa anowanikwa mune iyi Basic Excel Kuburitsa Tutori .

Syntax yeAVERAGEIF Function

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

Mutsara weAVERAGEIF ndewokuti:

= AVERAGEIF (Range, Criteria, Average_range)

IAVERAGEIF Function Arguments

Nharo dzebasa dzinoudza basa kuti chii chiri kuongororwa uye kuti ndezvipi zvinyorwa zvepakati zvichienzanisa kana chiitiko ichi chasangana.

Range - boka remasero basa racho kutsvaga.

Zvinyorwa - kukosha uku kunoenzaniswa ne data iri muRange. Kana mutsara unowanikwa ipapo iyo inofananidzwa nedzimwe muAverage_range yakaenzana. Dhiyabhorosi chaiyo kana sero rinoshandiswa kune data rinogona kupinda mukati menyaya iyi.

Average_range (kusarudzwa) - data iri mumatare emasero akaenzana kana mitsara inowanikwa pakati peRange neCriteria arguments. Kana Average_range argument haibvumirwi, data inowirirana mumakakatanwa eRange akaenzaniswa.

Kutanga AVERAGEIF Basa

Kunyange zvazvo zvichibvira kungonyorera AVERAGE IF basa iri muchitokisi , vanhu vazhinji vanozviona zviri nyore kushandisa bhokisi rebasa rekushanda kuwedzera basa kune pepa pepaji .

Tutorial Steps

1. Dzvanya pane cell E12 kuti uite iyo inoshandiswa seki . Apa ndipo patichapinda muAVERAGE IF basa.
2. Dzvanya paMafomu tab yebhoni .
3. Sarudza Mamwe Mabasa> Statistical kubva kubhoni kuti uzarure basa rinoderera pasi.
4. Dzvanya paAVERAGE IF mumutsetse kukwidza bhokisi reAVERAGE IF sarudzo.

Dhenda yatinopinda mumitsara mitatu isina kuvharwa mubhokisi rebhokisi ichaita nharo dzeAVERAGE IF basa.

Iyi nharo dzinoudza basa kuti chii chatinenge tichiedza uye kuti ndeapi dhenda yakawanda kuenzanisira apo mamiriro acho aitika.

Kupinda muRange Argument

Muchidzidzo ichi isu tiri kutsvaga kuwana huwandu hwekutengeserana kwegore kune yeEast sales region.

Range nharo inotaurira AVERAGE IF basa iro boka remasero rinotsvaga paunenge uchiedza kutsvaga zvakananga - East.

Tutorial Steps

1. Mubhokisi rebhokisi , tora paRange line.
2. Simbisai masero C3 kusvika C9 mubasa rekushandisa kuti uise izvi zvinyorwa zvinyorwa sezvenzira yekutsvaga nemabasa.

Kupinda muChiteria Argument

Mumuenzaniso uyu kana data iri mumakero C3: C12 akaenzana neEast ipapo kutengeswa kwezvose zvekodhi iyo kunofanira kuenzaniswa nebasa racho .

Kunyange zvazvo data chaiyo - yakadai sezwi East inogona kupinda mubhokisi rebhokisi nekuda kwekukakavadzana iyo kazhinji yakanakisa kuwedzera deta kusero riri mubasa rekushandura uye wozoisa iyo seferensi mubhokisi rebhokisi.

Tutorial Steps

1. Dzvanya pamutsara weCriteria mubhokisi rebhokisi .
2. Dzvanya pane sero D12 kuti upinde iyo seferensi inotarisa. Basa racho richatsvaga mararamiro akasarudzwa mudanho rekare kune dhese inofanidza izvi.
3. Izwi rekutsvaga (Kumabvazuva) richawedzerwa kuseri D12 mumutsara wokupedzisira wekudzidzisa.

Cell References Mawedzera Basa Rinoshandiswa

Kana sero rinotarisa, rakadai seD12, rakapinda seCriteria Argument, basa reAVERAGE IF rinotarisa mitsva kune chero dhesi yakange yakanyorwa muchitokisi ichocho mumushandi webasa.

Saka mushure mokuwana mavhareji ekutengeserana kweEast region inenge iri nyore kuwana mavhareji ekutengeserana kune imwe nzvimbo yekutengesa chete nekushandura East kusvika North kana West. Basa racho richavandudza uye rinoratidza chibereko chitsva.

Kupinda Average-range Argument

The Average_range argument iboka remasero ayo basa rinofanira kuenzanisa kana richinge rakatarisana mumakwikwi eRange akabatanidzwa muchitanhatu chechidzidzo.

Iyi nharo inofadza uye, kana isingabvumirwi, Excel inoshandura masero anotsanangurwa muRange argument.

Kubvira patinoda kuenzanisira kutengesa kweEast yekutsvaga nzvimbo tinoshandisa data mu Total Sales column kana Average_range argument.

Tutorial Steps

1. Dzvanya paAverage_range line mubhokisi rebhokisi.
2. Simbisai masero E3 kusvika E9 pane pepanheti. Kana nzira inotsanangurwa muchitsauko chekutanga ichibatanidza chero ruzivo rwuri pakati pekutanga (C3 kusvika C9), basa racho rinofananidza data mumasero anokwana mumutsetse wechipiri wesero.
3. Dzvanya OK kuti uvhare bhokisi rebhokisi uye upedze AVERAGE IF basa.
4. A # DIV / 0! kukanganisa kuchaonekwa muchitokisi E12 - sero patakapinda mubasa nokuti hatina kuwedzera dhidhi kuCriteria field (D12).

Kuwedzera Mitsva Yokutsvaga

Nhanho yekupedzisira mune zvidzidzo ndeye kuwedzera mvumo yatinoda kuti basa rifanane.

Muchiitiko ichi tinoda kuwana kuenzanirwa kwekutengesera kwegore rekutengesa reps muEast region kuitira kuti tiwedzere izwi rokuti East kusvika D12 - sero rakaratidzwa mubasa seine rine mvumo yenyaya .

Tutorial Steps

1. Muchitokisi D12 rudzi rweEast uye taurai Enter Enter pane keyboard.
2. Mhinduro ye $ 59,641 inofanira kuonekwa muchitokisi E12. Sezvo muongorori wekuenzana neEast unosangana mumasero mana (C3 kusvika C6) nhamba dziri mumakamuri anokwana mukhoma E (E3 kusvika E6) inowanikwa.
3. Paunenge uchinyora pa cell E12, basa rakakwana
   = AVERAGEIF (C3: C9, D12, E3: E9) inowanikwa mubhairi yepamusoro pane pepa rekushanda .
4. Kuti uwane chiyero chekutengesa kune dzimwe nzvimbo dzekutengesa, tora zita remunharaunda, yakadai seNorth muchitokisi E12 uye tapinza muIno yeKuisa pane keyboard.
5. Ivhareji yekuti iyo nzvimbo yekutengesa inofanira kuonekwa muchitokisi E12.