Idatha yokubala edibanisa iCritical Criteria ne-Excel ye-COUNTIFS Umsebenzi

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

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

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

02 we-09

Ukufaka i-Tutorial Data

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

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:

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

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

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

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

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

  1. Cofa kwifowuni F12 ukwenzela ukuba yenze iseli esebenzayo . Le yilapho singena khona umsebenzi we-COUNTIFS.
  2. Cofa kwiThebhu yefomula.
  3. Khetha Imisebenzi Engakumbi> Isitatisti kwibhebhoni ukuvula uluhlu lokuhla kwehla.
  4. 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

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

Kule tutorial sizama ukulinganisa iikhrayitheriya ezimbini kwirekhodi nganye yedatha:

  1. Iintengiso zentengiso evela kwingingqi yokuthengisa yaseMpuma.
  2. 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

  1. Kwibhokisi yencoko , nqakraza kwiCriteria_range1 line.
  2. Gqamisa iiseli D3 ukuya kwi-D9 kwiphepha lokusebenzela ukuze ufake ezi ngqamaniso zeseli njengoluhlu oluza kuphandwa ngumsebenzi .

06 ka 09

Ukungena kwiCriteria1 Ukungqubuzana

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

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

  1. Cofa kwiCriteria1 umgca kwibhokisi yencoko .
  2. Cofa kwiseli D12 ukungena kuloo fom yefayile kwibhokisi yencoko.
  3. 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

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

Njengoko kuchazwe ngaphambili, kule khokelo sizama ukulinganisa iikhrayitheriya ezimbini kwirekhodi nganye yedatha

  1. Iintengiso zentengiso evela kwingingqi yokuthengisa yaseMpuma.
  2. 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

  1. Kwibhokisi yencoko , nqakraza kwiCriteria_range2 line.
  2. Gqamisa iiseli E3 ukuya ku-E9 kwiphepha lokusebenzela ukuze ufake ezi ngqamaniso zeselfowuni njengoluhlu lwesibini oluza kusetshenzwa ngumsebenzi .

08 ka 09

Ukungena kwiCriteria2 Ukungqubuzana

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

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

  1. Cofa kwi- Criteria2 umgca kwibhokisi yencoko .
  2. Cofa kwifowuni E12 ukufaka eso saziso seeseli. Umsebenzi uya kukhangela uluhlu olukhethwe kwinqanaba elidlulileyo lwedatha efana nale migaqo.
  3. Cofa Kulungile ukugqiba umsebenzi we-COUNTIFS uze uvale ibhokisi yencoko.
  4. 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.
  5. Inkqubo yokukhangela izongezwa kwisinyathelo esilandelayo soqeqesho.

09 we 09

Ukongeza iCandelo loPhando kunye nokugqiba i-Tutorial

Excel COUNTIFS Umsebenzi weNyathelo ngeSigaba seTutorial. © Ted French

Isinyathelo sokugqibela kwisifundo siza kufaka iinkcukacha kwiiseli kwiphepha lokusebenzela elichongiweyo njengequlethe iingxabano .

Iimfundiso zeTutorial

  1. Kwiseli yeD12 yohlobo lwaseMpuma kwaye cinezela ukhiye we- Enter kwibhodibhodi.
  2. Kwifowuni ye-E12 > 250 kwaye ucinezela ukhiye wokungena kwikhibhodi (">" isimboli esikhulu kunase Excel).
  3. Impendulo yesi-2 kufuneka ibonakale kwiseli F12.
  4. Abameli ababini kuphela - uRalph noSam - emsebenzini wendawo yokuthengisa yaseMpuma kwaye benza amaphepha angaphezu kwama-250 omnyaka, ngoko ke, ezi zirekhodi kuphela zibalwa ngumsebenzi.
  5. Nangona uMarta usebenza kwimimandla yaseMpuma, wayenemiyalelo engaphantsi kwe-250 kwaye ngoko ke, irekhodi lakhe alibalwanga.
  6. Ngokufanayo, uJoe noTom bobabini babe nemiyalelo engaphezu kwe-250 yonyaka, kodwa abasebenzi kwimimandla yokuthengisa yaseMpuma ukuze iirekhodi zabo zingabalwa.
  7. Xa ucofa kwiseli F12, umsebenzi opheleleyo
    = COUNTIFS (F3: F9, D3: D9, D12, E3: E9, E12) ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela .