Umsebenzi we-Excel we-COUNTIFS ungasetyenziselwa ukubala inani leedata zedatha kwibala elikhethiweyo elifanisa inkcazo ethile.
I-COUNTIFS idlulisela ukusetyenziswa komsebenzi we-COUNTIF ngokukuvumela ukuba uchaze ukusuka kwi-2 ukuya ku-127 imigaqo kunokuba enye ibe kwi-COUNTIF.
Ngokuqhelekileyo, i-COUNTIFS isebenza ngemigca yedatha ebizwa ngokuba yiirekhodi. Kwirekhodi, idatha kwisisele nganye okanye kwintsimi emgceni inxulumene - njengegama lenkampani, idilesi nenombolo yefowuni.
I-COUNTIFS ikhangele iikhrayitheriya ezithile kwiinkalo ezimbini okanye ngaphezulu kwirekhodi kwaye kuphela xa ithola umdlalo kwintsimi nganye echazwe yirekhodi elibaliweyo.
01 ka 09
COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial
Kwinqanaba elingu - COUNTIF ngesifundo sezinyathelo sasihambelana nesigqibo esisodwa seentengiso zokuthengisa ezithengise ama-oda angaphezu kwama-250 ngonyaka.
Kule tutorial, siza kuseka imeko yesibini isebenzisa i- COUNTIFS- yee-agent zithengisayo kwingingqi yokuthengisa yaseMpuma eyenza ukuthengisa okungaphezulu kwe-250 kunyaka odlulileyo.
Ukubeka iimeko ezongezelelweyo kwenziwa ngokucacisa iCriteria_range eyongezelelweyo kunye neengxabano zeCriteria COUNTIFS.
Ukulandela amanyathelo kwimixholo yokufundisa engezantsi kukuhamba ngokudala kunye nokusebenzisa umsebenzi we-COUNTIFS obonwe kumfanekiso ongentla.
Izihloko zeTutorial
- Ukufaka i-Tutorial Data
- I-Syntax ye-COUNTIFS yeSebenzi
- Ukuqala ukusebenza kwe-COUNTIFS
- I-Criteria_range1 Ukuphikisa
- I-Criteria1 Impikiswano
- I-Criteria_range2 Impikiswano
- I-Criteria2 Impikiswano
- Yaye impendulo i ..
02 we-09
Ukufaka i-Tutorial Data
Isinyathelo sokuqala sokusebenzisa umsebenzi we-COUNTIFS kwi-Excel ukufaka idatha.
Kule khokelo faka idatha ebonwe kumfanekiso ongentla kwiiseli D1 ukuya kwiF11 kwiphepha lokusebenzela lase Excel.
Kwinqanaba eli-12 ngaphantsi kwedatha siza kwongeza umsebenzi we-COUNTIFS kunye neendlela zokukhangela ezimbini:
- abameli bezentengiso evela kwingingqi yokuthengisa eMpuma
- imiyalelo engaphezu kwama-250 abekwe kulo nyaka
Imiyalelo yokufundisa ayifaki ukufomatha amanyathelo kwiphepha lokusebenzela.
Oku akuyi kuphazamisana nokugqiba uqeqesho. Iphepha lokusebenzela liya kubonakala lihlukile kunomzekelo obonisiwe, kodwa umsebenzi we-COUNTIFS uza kunika iziphumo ezifanayo.
03 ka 09
I-Syntax ye-COUNTIFS yeSebenzi
Kwi-Excel, i- syntax yomsebenzi ibhekisela kwisakhiwo somsebenzi kwaye ibandakanya igama lomsebenzi, amabakaki kunye neengxabano .
I-syntax yomsebenzi we-COUNTIFS ngu:
= COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)
Kuze kube ngu-127 I- Criteria_range / iiprayoriti zokubaluleka zingachazwa kumsebenzi.
Iingxoxo ze-COUNTIFS zeMisebenzi
Iziphakamiso zomsebenzi zixelela i-COUNTIFS yiphi imilinganiselo esizama ukuyifanisa kunye naluphi udidi lweedatha ukukhangela ukufumana ezi khrayitheriya.
Zonke iingxabano kulo msebenzi zifunekayo.
I-Criteria_range - iqela leeseli umsebenzi ukukhangela umdlalo kwiingqinamba ezihambelana neCriteria .
Iimpawu - ixabiso esizama ukulifanisa kwingxelo yedatha. Idatha ekhoyo okanye iselferensi yeselfayile ingeniswa kule ngxabano.
04 we-09
Ukuqala ukusebenza kwe-COUNTIFS
Nangona kunokwenzeka ukuba uthayiphe nje umsebenzi we-COUNTIFS kunye neengxabano zayo kwisisele kwifom yokusebenzela, abaninzi abantu bakuthola kulula ukusebenzisa ibhokisi yencoko yencoko yomsebenzi ukuba ufake umsebenzi.
Iimfundiso zeTutorial
- Cofa kwifowuni F12 ukwenzela ukuba yenze iseli esebenzayo . Le yilapho singena khona umsebenzi we-COUNTIFS.
- Cofa kwiThebhu yefomula.
- Khetha Imisebenzi Engakumbi> Isitatisti kwibhebhoni ukuvula uluhlu lokuhla kwehla.
- Cofa kwi- COUNTIFS kuloluhlu ukukhupha ibhokisi yencoko yencoko yomsebenzi.
Idata esifakayo kwimigca engenanto kwibhokisi yencoko iya kubumba iingxoxo ze-COUNTIFS.
Njengoko kubonisiwe, ezi ngxabano zitsho umsebenzi ukuba yiyiphi inqobo esilinga ukuyifanisa kunye naluphi udidi lweedatha ukukhangela ukufumana ezi khrayitheriya.
05 ka 09
Ukungena kwiCriteria_range1 Ukungqubuzana
Kule tutorial sizama ukulinganisa iikhrayitheriya ezimbini kwirekhodi nganye yedatha:
- Iintengiso zentengiso evela kwingingqi yokuthengisa yaseMpuma.
- Amagosa ezithengisayo anemithetho engama-250 yokuthengisa yonyaka.
I-Criteria_range1 ingxabano ibonisa uluhlu lweeseli ii-COUNTIFS kukukhangela xa uzama ukulinganisa i-criteria yokuqala-kwingingqi ye-East yokuthengisa.
Iimfundiso zeTutorial
- Kwibhokisi yencoko , nqakraza kwiCriteria_range1 line.
- Gqamisa iiseli D3 ukuya kwi-D9 kwiphepha lokusebenzela ukuze ufake ezi ngqamaniso zeseli njengoluhlu oluza kuphandwa ngumsebenzi .
06 ka 09
Ukungena kwiCriteria1 Ukungqubuzana
Kule khokelo inkqubo yokuqala esiyifunayo kumdlalo kukuba ukuba idatha kwi-D3: D9 ilingana neMpuma .
Nangona idatha yangempela - njengegama laseMpumalanga - lingeniswa kwibhokisi yencoko yababini kule ngxabano kubhetele kakhulu ukufaka isalathisi yendawo kwindawo yedatha kwibhokisi yencoko.
Iimfundiso zeTutorial
- Cofa kwiCriteria1 umgca kwibhokisi yencoko .
- Cofa kwiseli D12 ukungena kuloo fom yefayile kwibhokisi yencoko.
- Ixesha lokusesha laseMpuma liya kufakwa kwiseli D12 kwisinyathelo sokugqibela sesifundo.
Iingxelo zeeNgcaciso zeeNgcini ziyakwandisa i-COUNTIFS
Ukuba i-cell reference, efana no-D12, ingeniswa njengeCriteria Argument, umsebenzi we-COUNTIFS uza kukhangela imifuno kuyo nayiphi na idatha efakwe kuloo seli kwiphepha lokusebenzela.
Ngoko emva kokubala inani lee-ejenti ezivela kwingingqi yaseMpuma kuya kuba lula ukufumana idatha efanayo enye indawo ekuthengiseni ngokutshintsha ngokusuka kweMpuma ukuya kuMntla okanye eNtshona kwi-cell D12. Umsebenzi uya kuhlaziya ngokuzenzekelayo kwaye ubonise umphumo omtsha.
07 ka 09
Ukungena kwiCriteria_range2 Ukuphikisa
Njengoko kuchazwe ngaphambili, kule khokelo sizama ukulinganisa iikhrayitheriya ezimbini kwirekhodi nganye yedatha
- Iintengiso zentengiso evela kwingingqi yokuthengisa yaseMpuma.
- Iintengiso zentengiso ezenzileyo ezingaphezu kwama-250 ezithengisayo kulo nyaka.
Ingxabano ye- Criteria_range2 ibonisa uluhlu lweeseli ii-COUNTIFS kukukhangela xa uzama ukulinganisa i-criteria ye sibini - abadayisi bokuthengisa abathengise ama-250 angama-oda kulo nyaka.
Iimfundiso zeTutorial
- Kwibhokisi yencoko , nqakraza kwiCriteria_range2 line.
- Gqamisa iiseli E3 ukuya ku-E9 kwiphepha lokusebenzela ukuze ufake ezi ngqamaniso zeselfowuni njengoluhlu lwesibini oluza kusetshenzwa ngumsebenzi .
08 ka 09
Ukungena kwiCriteria2 Ukungqubuzana
Ukufaka iCriteria2 Ukuphikisa nokugqiba umsebenzi we-COUNTIFS
Kule ndlela yokufundisa inqaku lesibini esibheka kumdlalo kukuba ukuba idatha kwinqanaba le-E3: E9 likhulu kune-250 yokuthengisa.
Njengokuba kukho ingxabano yeCriteria1, siya kufaka iselferensi yeseko kwindawo yeCriteria2 kwibhokisi yencoko kunokuba idatha ngokwayo.
Iimfundiso zeTutorial
- Cofa kwi- Criteria2 umgca kwibhokisi yencoko .
- Cofa kwifowuni E12 ukufaka eso saziso seeseli. Umsebenzi uya kukhangela uluhlu olukhethwe kwinqanaba elidlulileyo lwedatha efana nale migaqo.
- Cofa Kulungile ukugqiba umsebenzi we-COUNTIFS uze uvale ibhokisi yencoko.
- Impendulo ye-zero ( 0 ) iya kubonakala kwiseli F12 - iseli apho singene khona umsebenzi - kuba asikazanga ukufaka idatha kwiCriteria1 kunye neCriteria2 (C12 no-D12). Kuze senze, akukho nto ye-COUNTIFS yokubala kwaye ngoko ke inani lihlala kwi-zero.
- Inkqubo yokukhangela izongezwa kwisinyathelo esilandelayo soqeqesho.
09 we 09
Ukongeza iCandelo loPhando kunye nokugqiba i-Tutorial
Isinyathelo sokugqibela kwisifundo siza kufaka iinkcukacha kwiiseli kwiphepha lokusebenzela elichongiweyo njengequlethe iingxabano .
Iimfundiso zeTutorial
- Kwiseli yeD12 yohlobo lwaseMpuma kwaye cinezela ukhiye we- Enter kwibhodibhodi.
- Kwifowuni ye-E12 > 250 kwaye ucinezela ukhiye wokungena kwikhibhodi (">" isimboli esikhulu kunase Excel).
- Impendulo yesi-2 kufuneka ibonakale kwiseli F12.
- Abameli ababini kuphela - uRalph noSam - emsebenzini wendawo yokuthengisa yaseMpuma kwaye benza amaphepha angaphezu kwama-250 omnyaka, ngoko ke, ezi zirekhodi kuphela zibalwa ngumsebenzi.
- Nangona uMarta usebenza kwimimandla yaseMpuma, wayenemiyalelo engaphantsi kwe-250 kwaye ngoko ke, irekhodi lakhe alibalwanga.
- Ngokufanayo, uJoe noTom bobabini babe nemiyalelo engaphezu kwe-250 yonyaka, kodwa abasebenzi kwimimandla yokuthengisa yaseMpuma ukuze iirekhodi zabo zingabalwa.
- Xa ucofa kwiseli F12, umsebenzi opheleleyo
= COUNTIFS (F3: F9, D3: D9, D12, E3: E9, E12) ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela .