Sebenzisa i-AVERAGEIF ye-Excel ukuLawula iMilinganiselo yeZero xa ufumana uMyinge

Umsebenzi we-AVERAGEIF wongezwa kwi-Excel 2007 ukwenza kube lula ukufumana ixabiso eliqhelekileyo kwimiba yedatha ehlangabezana nekhrayithi echaziweyo.

Enye into enjalo yomsebenzi kukuba kukungayinaki imilinganiselo ye-zero kwidatha elahla umyinge okanye i-arithmetic ithetha xa usebenzisa umsebenzi oqhelekileyo WENKQUBO .

Ukongezelela kwidatha endiweyo kwiphepha lokusebenzela, ixabiso lezora lingaba ngumphumo wokubala kwefomula - ingakumbi kwiiphetshana zokusebenzela ezingaphelelanga.

Ungayihoyi iZeros xa Ufumana Umyinge

Umfanekiso ongasentla uqulethe ifomula isebenzisa i-AVERAGEIF engayinaki ixabiso lexabiso. Umlinganiselo kwi-formula eyenza le " <> 0".

Umtsalane we "<>" ngulo uphawu olungalinganiyo kwi-Excel kwaye luyadalwa ngokuthayipha amabakaki angama-angle - ekhoneni elingezantsi lasebhobhodi yebhodibhodi - buyela emuva;

Imimiselo emfanekisweni yonke isebenzise ifomula efanayo esisiseko - kuphela uhla lweenguqu. Iziphumo ezahlukileyo zifunyenwe ngenxa yeenkcukacha ezahlukeneyo ezisetyenziswa kwifom.

I-AVERAGEIF yeSebenza yeSintax kunye ne-Augments

I- syntax yomsebenzi ibhekisela kwisakhiwo somsebenzi kwaye iquka igama lomsebenzi , amabakethi kunye neengxabano .

I-syntax yomsebenzi we-AVERAGEIF ngu:

= I-AVERAGEIF (I-Range, iCriteria, Umyinge-umda)

Iingxoxo zomsebenzi we-AVERAGEIF zi:

Uhlobo - (olufunekayo) iqela leeseli umsebenzi uya kukhangela ukufumana umxube weengxoxo zeCriteria apha ngezantsi.

Iimfuno - (ezifunekayo) zichonga ukuba ngaba idatha kwiselingi iya kuhlawulwa okanye ayikho

Umyinge_ekhefu - (okukhethiweyo) uluhlu lweedatha olulinganiselwe ukuba uluhlu lokuqala luhlangabezana neendlela ezichaziweyo. Ukuba le ngxabano ishiywe, idatha kwi- Range argument isithuba endaweni - njengoko kuboniswe kwimimiselo engasentla umfanekiso.

Umsebenzi we-AVERAGEIF uyayigcina:

• Iiselingi kwi- Average_range argument equkethe ixabiso le-Boolean (TRUE okanye FALSE) - umgca 5 ngaphezulu;
• Iiseli kwi- Average_range ezingenanto-umgca 4 ngaphezulu;

Phawula:

• Ukuba akukho zisele kwiRange zidibana neendlela ezichongiweyo, u-AVERAGEIF ubuyisela i-# DIV / 0! Ixabiso lephutha - umgca wesi-7 kumzekelo, apho zonke iiseli kwiRange zilingana no-zero.
• Ukuba ingxabano yeRange ayinanto ngokupheleleyo okanye iqulethe ixabiso lombhalo kuphela, i-AVERAGEIF ibuyisela i- # DIV0! Ixabiso lesiphene - imigqa 6 no-8 ngasentla;

Uthobe umzekelo weZeros

Izinketho zokwenza umsebenzi we-AVERAGEIF kunye neengxabano zayo ziquka:

1. Ukuthayipha umsebenzi opheleleyo, njenge: = AVERAGEIF (A3: C3, "<> 0") kwisitoreji somsebenzi ;
2. Ukukhetha umsebenzi kunye neengxabano zayo usebenzisa ibhokisi yencoko yencoko ye -AVERAGEIF .

Nangona kunokwenzeka ukufaka umsebenzi opheleleyo ngesandla, abantu abaninzi bakuthola kulula ukuyisebenzisa ibhokisi yencoko njengoko kuthatha ukunakekelwa kokungena kwi-syntax yomsebenzi - njengabakaki kunye ne-comma separators efunekayo phakathi kweengxabano.

Ukongeza, ukuba umsebenzi kunye neziphakamiso zayo zingenwe ngesandla, ingxabano yeCriteria kufuneka ijikelezwe ngamanqaku okucaphuna: "<> 0" . Ukuba ibhokisi yencoko isetyenziselwa ukungena kumsebenzi, iya kufaka amanqaku okucaphuna.

Uluhlu olungezantsi lusetyenziswe ukufaka i-AVERAGEIF kwiseli D3 yomzekelo ongentla usebenzisa ibhokisi yencoko yencoko yomsebenzi.

Ukuvula ibhokisi le-AVERAGEIF

1. Cofa kwiseli D3 ukwenzela ukuba yenze iseli esebenzayo -indawo apho iziphumo zomsebenzi ziza kuboniswa khona;
2. Cofa kwiThebhu yefomula yerbhoni ;
3. Khetha Imisebenzi Engakumbi> Isitatisti kwi-ribbon ukuvula uluhlu lwehla lokuhla;
4. Cofa kwi- AVERAGEIF kuloluhlu ukukhupha ibhokisi yencoko yencoko yomsebenzi;
5. Kwibhokisi yencoko, nqakraza kwi- Range line;
6. Gqamisa iiseli ze-A3 ukuya ku-C3 kwiphepha lokusebenzela ukuze ufake olu luhlu kwibhokisi yencoko;
7. Kulo mgca woqwalaselo kwibhokisi yencoko, thayipha: <> 0 ;
8. Qaphela: Umyinge-umda-umda ushiywe ungabonakali njengoko sithola ixabiso eliqhelekileyo kwiiseli ezifanayo zangena kwi- Range argument;
9. Cofa OK ukuvala ibhokisi yencoko kwaye ubuyele kwiphepha lokusebenzela;
10. Impendulo 5 kufuneka ibonakale kwiseli D3;
11. Ekubeni umsebenzi ungayinaki ixabiso lexabiso kwi-cell B3, umlinganiselo weeseli ezimbini ezisele zi-5: (4 + 6) / 2 = 10;
12. Ukuba ucofa kwiseli D8 umsebenzi opheleleyo = AVERAGEIF (A3: C3, "<> 0") ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela.