Yuav Paub
- The syntax for AVERAGEIF is: =AVERAGEIF(Range, Criteria, Average_range).
- Tsim, xaiv ib lub xovtooj, mus rau Formulas tab, thiab xaiv Ntau Txoj Haujlwm > Txheeb cais > AVERAGEIF.
- Cia li nkag mus rau Range, Criteria, thiab Average_range hauv Function dialog box thiab xaiv Done.
Kab lus no piav qhia yuav ua li cas siv AVERAGEIF muaj nuj nqi hauv Excel. Cov lus qhia siv rau Excel 2019, 2016, 2013, 2010, thiab Excel rau Microsoft 365.
AVERAGEIF yog dab tsi?
The AVERAGEIF ua haujlwm ua ke IF ua haujlwm thiab AVERAGE ua haujlwm hauv Excel; kev sib xyaw ua ke no tso cai rau koj kom pom qhov nruab nrab lossis lej lej ntawm cov txiaj ntsig ntawm cov ntaub ntawv xaiv uas ua tau raws li cov qauv tshwj xeeb.
Tus IF feem ntawm txoj haujlwm txiav txim siab seb cov ntaub ntawv twg ua tau raws li cov txheej txheem teev tseg, thaum lub AVERAGE ntu suav qhov nruab nrab lossis txhais tau tias. Feem ntau, AVERAGEIF siv kab ntawm cov ntaub ntawv hu ua cov ntaub ntawv, uas tag nrho cov ntaub ntawv hauv txhua kab yog cuam tshuam.
AVERAGEIF Function Syntax
Hauv Excel, ib txoj haujlwm syntax yog hais txog kev teeb tsa ntawm lub luag haujlwm thiab suav nrog cov haujlwm lub npe, cov hlua khi, thiab cov lus sib cav.
The syntax for AVERAGEIF is:
=AVERAGEIF(Range, Criteria, Average_range)
Qhov kev ua haujlwm sib cav qhia nws seb qhov xwm txheej twg los kuaj rau thiab qhov ntau ntawm cov ntaub ntawv kom nruab nrab thaum nws ua tau raws li qhov xwm txheej.
- Range (yuav tsum tau) yog pab pawg ntawm cov hlwb ua haujlwm yuav tshawb rau cov txheej txheem teev tseg.
- Criteria (yuav tsum tau) yog tus nqi piv rau cov ntaub ntawv hauv Range. Koj tuaj yeem nkag mus rau cov ntaub ntawv tiag tiag lossis cov ntaub ntawv ntawm tes rau qhov kev sib cav no.
- Average_range (optional): Lub luag haujlwm nruab nrab ntawm cov ntaub ntawv hauv cov kab ke no thaum nws pom qhov sib tw ntawm Range thiabCriteria arguments. Yog tias koj tshem tawm Average_range kev sib cav, qhov ua haujlwm hloov qhov nruab nrab cov ntaub ntawv sib tw hauv Range sib cav.
Hauv qhov piv txwv no, AVERAGEIFua haujlwm tab tom nrhiav kev muag khoom nruab nrab txhua xyoo rau thaj tsam muag East. Cov qauv yuav suav nrog:
- A Range ntawm cov hlwb C3 rau C9, uas muaj cov npe hauv cheeb tsam.
- The Criteria yog cell D12 (East).
- An Average_range ntawm cells E3 rau E9,uas muaj qhov muag nruab nrab ntawm txhua qhov neeg ua haujlwm.
Yog li yog tias cov ntaub ntawv hauv thaj tsam C3:C12 sib npaug East, ces tag nrho cov muag rau cov ntaub ntawv ntawd yog qhov nruab nrab ntawm kev ua haujlwm.
Nkag mus rau AVERAGEIF Function
Txawm hais tias nws tuaj yeem ntaus tus AVERAGEIF ua haujlwm rau hauv lub xovtooj, ntau tus neeg pom nws yooj yim dua los siv Function Dialog Box rau ntxiv cov haujlwm rau daim ntawv ua haujlwm.
Pib los ntawm kev nkag mus rau cov qauv ntaub ntawv muab rau hauv cells C1 rau E11 ntawm daim ntawv Excel khoob raws li pom hauv daim duab saum toj no.
In Cell D12, nyob rau Sales Region, hom East.
Cov lus qhia no tsis suav nrog cov kauj ruam ua qauv rau daim ntawv ua haujlwm. Koj daim ntawv ua haujlwm yuav zoo li txawv dua li qhov piv txwv qhia, tab sis AVERAGE IF ua haujlwm yuav muab rau koj tib yam.
- Click on cell E12 ua kom nws lub cev muaj zog, uas yog qhov chaw AVERAGEIF ua haujlwm yuav mus.
-
Click on the Formulas tab ntawm ribbon.
Image - Xaiv Ntau Txoj Haujlwm> Tus lej los ntawm ribbon qhib lub luag haujlwm poob.
-
Click on AVERAGEIF hauv daim ntawv txhawm rau qhib lub Function Dialog Box. Cov ntaub ntawv uas nkag mus rau hauv peb kab dawb paug hauv Function Dialog Box ua rau cov lus sib cav ntawm AVERAGEIF muaj nuj nqi.
Image - Click the Range kab.
- Highlight cells C3 rau C9 hauv daim ntawv ua haujlwm kom nkag mus rau cov ntaub ntawv xov tooj ntawm tes raws li qhov ntau yam yuav tsum tau tshawb nrhiav los ntawm kev ua haujlwm.
- Click rau Criteria kab.
- Nias rau cell D12 txhawm rau nkag mus rau qhov kev siv ntawm tes - qhov ua haujlwm yuav tshawb nrhiav ntau yam uas tau xaiv hauv cov kauj ruam dhau los rau cov ntaub ntawv uas phim cov qauv no. Txawm hais tias koj tuaj yeem nkag mus rau cov ntaub ntawv tiag tiag - xws li lo lus East- rau qhov kev sib cav no, feem ntau nws yooj yim dua los ntxiv cov ntaub ntawv rau hauv lub xov tooj ntawm tes hauv daim ntawv ua haujlwm thiab tom qab ntawd nkag mus rau hauv lub xov tooj siv rau hauv dialog box.
- Click rau Average_range kab.
- Highlight cells E3 rau E9 ntawm daim ntawv nthuav qhia. Yog tias cov txheej txheem teev tseg hauv cov kauj ruam dhau los sib phim cov ntaub ntawv hauv thawj ntau yam (C3rau C9), txoj haujlwm yuav nruab nrab cov ntaub ntawv hauv qhov sib thooj. cov hlwb hauv qhov thib ob ntawm cov cell.
- Click Done kom ua tiav AVERAGEIF ua haujlwm.
- Cov lus teb $59, 641 yuav tsum tshwm sim hauv cell E12.
Thaum koj nyem rau cell E12, ua tiav txoj haujlwm tshwm nyob rau hauv daim ntawv qhia zaub mov saum toj ntawm daim ntawv ua haujlwm.
=AVERAGEIF(C3:C9, D12, E3:E9)
Siv lub xovtooj siv rau qhov Kev Ntsuas Kev Ntsuas ua rau nws yooj yim nrhiav kom hloov cov qauv raws li xav tau. Hauv qhov piv txwv no, koj tuaj yeem hloov cov ntsiab lus ntawm tes D12 ntawm East rau North lossisWest Lub luag haujlwm yuav hloov kho thiab tso tawm cov txiaj ntsig tshiab.
