I-Excel AVERAGEIF: Thola Isilinganiso Semibuzo Ecacile

Umsebenzi we-AVERAGEIF uhlanganisa umsebenzi we-IF no- AVERAGE umsebenzi ku-Excel. Le nhlanganisela ikuvumela ukuba uthole isilinganiso noma isilinganiso se-arithmetic yalezo zindinganiso ebangeni elikhethiwe ledatha elihlangabezana nezimiso ezithile.

Ingxenye ye-IF yomsebenzi inquma ukuthi iyiphi idatha ehlangabezana nezilinganiso ezicacisiwe futhi ingxenye ye-AVERAGE ibala isilinganiso noma isho.

Ngokuvamile, i-AVERAGE IF isetshenziswe ngemigqa yedatha ebizwa ngamarekhodi. Emrekhodini , yonke idatha esitokisini ngasinye emgqeni ihlobene - njengegama lenkampani, ikheli kanye nenombolo yocingo.

I-AVERAGE IF ibuka izinqubo ezithile kwelinye iseli noma insimu kwirekhodi futhi, uma ithola umdlalo, ihlanganisa leyo datha noma idatha kwenye insimu ecacisiwe kwirekhodi elifanayo.

Indlela umsebenzi we-AVERAGEIF osebenza ngayo

Le tutorial isebenzisa umsebenzi we-AVERAGE IF ukuthola ukuthengwa kweminyaka ngonyaka wesifunda sezokuthengisa sase-East kuqoqo lamarekhodi wedatha.

Ukulandela izinyathelo ezihlokweni zokufundisa ngezansi kukuhamba ngokudala nokusebenzisa umsebenzi we-AVERAGE IF obonwe esithombeni ngenhla ukubala isilinganiso sokuthengisa ngonyaka.

Izihloko zezifundo

• Ukufaka idatha yokufundisa
• I-ANVERAGE IF i-Syntax Yomsebenzi
• Ukuqala umsebenzi we-AVERAGE IF
• Ukufaka i-Range Argument
• Ukufaka Ukungqubuzana Okubalulekile
• Ukufaka i-Average_range Argument
• Futhi Impendulo yile ..

Ukufaka idatha yokufundisa

Isinyathelo sokuqala sokusebenzisa umsebenzi we-AVERAGE IF ku-Excel ukufaka idatha .

Faka idatha kumaseli C1 kuya ku-E11 wephepha lokusebenzela le-Excel njengoba kuboniswa esithombeni ngenhla.

Qaphela: Imiyalo yokufundisa ayifaki ukufomatha izinyathelo zephepha lokusebenzela.

Lokhu ngeke kuphazamise ukuqedela ukufundisa. Iphepha lakho lokusebenzela lizobonakala lihlukile kunalokhu kubonisiwe, kodwa umsebenzi we-AVERAGE IF uzokunika imiphumela efanayo.

Ulwazi olukhethwa kukho lokufometha olufana nalabo abonwe ngenhla luyatholakala kule Tifundvo Eyisisekelo Yokufometha I-Excel .

I-Syntax ye-AVERAGEIF Yomsebenzi

Ku-Excel, i- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, nokuphikisana .

I-syntax ye-AVERAGEIF yile:

= AVERAGEIF (I-Range, i-Criteria, i-average_range)

Iziphakamiso ze-AVERAGEIF

Iziphakamiso zomsebenzi zitshela umsebenzi ukuthi yisiphi isimo esivivinywayo nokuthi yiluphi uhla lwedatha olulinganiselwe uma leso sifo sifinyelelwe.

Ububanzi - iqembu lama cell the function is to search.

Imigomo - leli xabiso lifaniswa nedatha kuRange. Uma umdlalo utholakala bese idatha ehambisanayo ku-Isilinganiso se-average isilinganiso. Idatha yangempela noma ireferensi yeseli yedatha ingangeniswa kule mpikiswano.

Isilinganiso_imingcele (okuzikhethela) - idatha kulezi zinhlobo zamaseli isilinganiso uma imingcele itholakala phakathi kwezingqinamba zeRange neCriteria . Uma ngabe ukuphikisana okuphakathi kwe-Medium kungavumelekile, idatha ehambelana nengxabano yeRange isilinganiso esithile.

Iqala ukusebenza kwe-AVERAGEIF

Nakuba kungenzeka nje ukuthayipha umsebenzi we-AVERAGE IF esitokisini , abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo lomsebenzi ukuze wengeze umsebenzi kushidi lokusebenzela .

Izinyathelo Zokufundisa

1. Chofoza kuseli E12 ukuze wenze iseli esebenzayo . Yilapho sizofaka khona umsebenzi we-AVERAGE IF.
2. Chofoza kuthebhu yefomathi yebhande .
3. Khetha Imisebenzi Engaphezulu> Isitatimende kusuka kubhebhoni ukuvula uhlu lwehla lokusebenza phansi.
4. Chofoza ku-AVERAGE IF kuluhlu ukuze akhiphe ibhokisi lengxoxo ye-AVERAGE IF.

Idatha esiyifaka emigqeni emithathu engenalutho ebhokisini lebhokisi izokwenza izingxabano zomsebenzi we-AVERAGE IF.

Lezi zimpikiswano zitshela umsebenzi ukuthi yisiphi isimo esizihlolisayo nokuthi yiluphi uhla lwedatha olulinganiselwe uma isimo sifinyelelwe.

Ukufaka i-Range Argument

Kulesi sifundo sibheka ukuthola ukuthengwa kweminyaka ngonyaka wesifunda sokuthengisa seMpumalanga.

Ingxabano ye- Range ithela umsebenzi we-AVERAGE IF yiliphi iqembu lamaseli ukuthi lizame uma lizama ukuthola izindlela ezicacisiwe - EMpumalanga.

Izinyathelo Zokufundisa

1. Ebhokisini lebhokisi , chofoza ku- Range line.
2. Qinisekisa amaseli C3 kuya ku-C9 kuphepha lokusebenzela ukuze ufake lezi zikhombisi zeseli njengebanga lokufundwa ngumsebenzi.

Ukufaka Ukungqubuzana Okubalulekile

Kulesi sibonelo uma idatha ebangeni C3: C12 lilingana neMpumalanga bese ukuthengiswa okuphelele kwalelo rekhodi kuzolinganiswa nomsebenzi .

Nakuba idatha yangempela - njengegama elithi East lingangena ebhokisini lengxoxo kule mpikiswano ngokuvamile kungcono ukwengeza idatha esitokisini ekhasini lokusebenzela bese ufaka leyo nkomba yeselula ebhokisini lengxoxo.

Izinyathelo Zokufundisa

1. Chofoza kulayini Wokuqondisa ebhokisini lengxoxo .
2. Chofoza kuseli D12 ukuze ufake leyo reference reference. Umsebenzi uzosesha uhla olukhethiwe kwisinyathelo sangaphambilini sedatha efana nale nqubo.
3. Igama lokusesha (EMpumalanga) lizofakwa kwiseli D12 esiteji sokugcina sesifundo.

Izinkomba Zezinhlamvu Khulisa Ukusebenza Okuhambisanayo

Uma ireferensi yeseli, njenge-D12, ingeniswa njenge-Criteria Argument, umsebenzi we-AVERAGE IF uzobukeka ngokufana nanoma iyiphi idatha efakwe kulowo lensi ekhasini lokusebenzela.

Ngakho emva kokuthola ukuthengiswa okujwayelekile kwesifunda saseMpumalanga kuyoba lula ukuthola ukuthengiswa okujwayelekile kwesinye isifunda sokuthengisa ngokumane uguqule i-East kuya eNyakatho noma eNtshonalanga. Umsebenzi uzovuselela ngokuzenzakalelayo futhi ubonise umphumela omusha.

Ukufaka i-Average_range Argument

Iphikithi ye-Average_range yiqembu lamaseli ukuthi umsebenzi kufanele ulinganise uma uthola umdlalo ku-Range impikiswano ekhonjiswe esiteji sesi-5 sesifundo.

Le mpikiswano iyakhethwa futhi, uma ingashiywe, i-Excel iphakamisa amaseli ashiwo ku-Range argument.

Njengoba sifuna ukuthengiswa okujwayelekile kwesifunda se-East sales sisebenzisa idatha ku-Total Sales ikholomu njenge-Average_range argument.

Izinyathelo Zokufundisa

1. Chofoza kumugqa we- Average_range ebhokisini lengxoxo.
2. Gqamisa amaseli E3 ku-E9 kuspredishithi. Uma i-criteria echazwe kwisinyathelo sangaphambilini ihambisana nanoma iyiphi idatha ebangeni lokuqala (C3 kuya ku-C9), umsebenzi uzodlulisa idatha kumaseli ahambelana naloluhlu lwamaseli wesibili.
3. Chofoza OK ukuvala ibhokisi lengxoxo bese uqedela umsebenzi we-AVERAGE IF.
4. A # DIV / 0! iphutha lizovela esitokisini E12 - iseli lapho singene khona umsebenzi ngoba asikazanga ukufaka idatha emkhakheni Wokunqunywa (D12).

Ukungeza i-Criteria Yokusesha

Isinyathelo sokugcina esifundweni ukufaka izindlela esizifuna ukuthi umsebenzi ufanane nazo.

Kulesi simo sifuna ukuthola ukuthengwa kweminyaka yonke yokuthengiswa kwempahla esifundeni saseMpumalanga ngakho-ke sizofaka igama elithi East kuya ku-D12 - iseli elikhonjisiwe emsebenzini njengoba liqukethe impikiswano yokunquma.

Izinyathelo Zokufundisa

1. Ngeselula D12 hlobo East bese ucindezela ukhiye we- Enter kukhibhodi.
2. Impendulo engu-$ 59,641 kufanele ibonakale esitokisini E12. Njengoba umgomo wokulinganisa eMpumalanga uhlangene ngamaseli amane (C3 kuya ku-C6) izinombolo kumaseli ahambisanayo kukholomu E (E3 kuya E6) zilinganiselwe.
3. Uma uchofoza ku-cell E12, umsebenzi ophelele
   = AVERAGEIF (C3: C9, D12, E3: E9) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela .
4. Ukuthola isilinganiso sokuthengisa kwezinye izifunda zokuthengisa, thayipha igama lesifunda, njengeNyakatho kuseli-E12 bese ucindezela ukhiye we- Enter ekhibhodi.
5. Isilinganiso saleso sifunda sokuthengisa kufanele sivele esitokisini E12.