Gwiritsani ntchito AVERAGEIF ya Excel kulemekeza Zero Makhalidwe Pamene Mukupeza Average

Ntchito ya AVERAGEIF yowonjezedwa mu Excel 2007 kuti zikhale zosavuta kuti mupeze mosavuta mtengo wa deta yomwe imakomana ndi ndondomeko inayake.

Ntchito imodzi yogwiritsira ntchitoyi ndi kukhala nayo kunyalanyaza zero mu data zomwe zimachokera pafupipafupi kapena masamu zimatanthauza pokhapokha kugwiritsa ntchito NTCHITO YOPHUNZITSIRA ntchito .

Kuphatikizana ndi deta yomwe yawonjezeredwa pa tsamba, ziwerengero za zero zikhoza kukhala chifukwa cha mawerengero apangidwe - makamaka m'mapepala osakwanira.

Onyalani Zeros pakupeza Average

Chithunzichi pamwamba chili ndi ndondomeko yogwiritsa ntchito AVERAGEIF yomwe imanyalanyaza zero zamtengo. Chotsatira mu njira yomwe ili ndi " <> 0".

Khalidwe la "<>" ndilo losafanana ndilo mu Excel ndipo limapangidwa polemba mabotolo olowera - ali kumbali ya kumanja kwa makina - kubwerera;

Zitsanzo zomwe zili m'chithunzichi zimagwiritsa ntchito njira yofanana - zokhazokha zimasintha. Zotsatira zosiyana zopezeka zimachokera ku deta zosiyana siyana zomwe zimagwiritsidwa ntchito mu ndondomekoyi.

AVERAGEIF Function Syntax ndi Augments

Syntax ya ntchito imatanthawuza momwe ntchitoyo ikuyendera ndipo imaphatikizapo dzina la ntchito , mabaki, ndi zifukwa .

Chidule cha ntchito ya AVERAGEIF ndi:

= AVERAGEIF (Range, Criteria, Average_range)

Zolinga za ntchito ya AVERAGEIF ndi:

Mtundu - (wofunikira) gulu la maselo ntchitoyi idzasaka kuti ipeze machesi a mtsutso wotsutsa pansipa.

Zolinga - (zofunikira) zimatsimikizira ngati deta yomwe ili mu selo iyenera kuwerengedwa kapena ayi

Average_range - (mwachangu) deta yomwe ilipo ngati mtengo woyamba ukukwaniritsa zofunikira. Ngati kutsutsana uku sikulephereka, deta yomwe ikugwirizana ndi Range imakhala m'malo mwake - monga momwe zisonyezera mu zitsanzo za chithunzi pamwambapa.

Ntchito ya AVERAGEIF imanyalanyaza:

• Maselo mu Average -range argument omwe ali ndi chikhalidwe cha Boolean (TRUE kapena FALSE) - mzere 5 pamwamba;
• Maselo mu Average_range omwe alibe - mzera 4 pamwamba;

Zindikirani:

• Ngati palibe maselo mu Range akwaniritsa zofunikira, AVERAGEIF imabwezera # DIV / 0! Cholakwika - mzere 7 mu chitsanzo, pamene maselo onse a Range ali ofanana ndi zero.
• Ngati mkangano wa Range ulibe kanthu kapena uli ndi malemba okha, AVERAGEIF amabwezera # DIV0! zopanda pake.- mizere 6 ndi 8 pamwambapa;

Pezani Chitsanzo cha Zeros

Zosankha zolowera ntchito ya AVERAGEIF ndi zifukwa zake zikuphatikizapo:

1. Kujambula ntchito yonse, monga: = AVERAGEIF (A3: C3, "<> 0") mu selo lamasewera;
2. Kusankha ntchito ndi zifukwa zake pogwiritsa ntchito AVERAGEIF ntchito dialog box.

Ngakhale kuti n'zotheka kungolowera polojekitiyi, anthu ambiri amavutika kuti agwiritse ntchito bokosi la nkhaniyi pamene zimasamala kuti alowe m'ma syntax - monga mabakita komanso opatukana okhudzana pakati pa zokambirana.

Kuwonjezera apo, ngati ntchitoyo ndi zifukwa zake zalowa mwadongosolo, kutsutsana kwachitsulo kuyenera kuzunguliridwa ndi zizindikiro za quotation: "<> 0" . Ngati bokosi la bokosi likugwiritsidwa ntchito kulowa ntchito, liziwonjezera malemba a quotation kwa inu.

Mndandanda womwe uli pansipa ndi njira zomwe mungagwiritsire ntchito AVERAGEIF mu chipinda D3 chachitsanzo pamwambapa pogwiritsa ntchito bokosi lazokosi.

Kutsegula Bokosi la Dialog AVERAGEIF

1. Dinani pa selo D3 kuti mupange selo yogwira ntchito - malo omwe zotsatira zotsatira zidzasonyezedwe;
2. Dinani pa Fomu tab ya riboni ;
3. Sankhani Ntchito Zambiri> Zotsatira kuchokera ku riboni kuti mutsegule ntchito yolemba pansi;
4. Dinani pa AVERAGEIF m'ndandanda kuti mubweretse bokosi la bokosi la ntchitoyo;
5. Mu bokosi la bokosi, dinani pa Range line;
6. Onetsetsani maselo A3 mpaka C3 mu tsamba lothandizira kuti mulowetse mndandanda uwu mu bokosi la dialog;
7. Pa ndondomeko yoyenera mu bokosi la bokosi, yesani: <> 0 ;
8. Zindikirani: Average_range yasalalika opanda kanthu popeza ife tikupeza mtengo wapatali wa maselo omwewo omwe alowekera mu ndondomeko ya Range ;
9. Dinani OK kuti mutseke bokosi la bokosi ndikubweranso ku tsamba la ntchito;
10. Yankho lachisanu liyenera kuoneka mu selo D3;
11. Popeza ntchitoyi imanyalanyaza zero mu selo B3, pafupifupi maselo awiri otsalawo ndi 5: (4 + 6) / 2 = 10;
12. Ngati inu mutsegula pa selo D8 ntchito yonse = AVERAGEIF (A3: C3, "<> 0") ikuwoneka pa bar lamulo pamwamba pa tsamba.