Bayanin Ƙididdiga wanda ke samo ƙayyadaddun matakai tare da Ayyukan KAYA NA Excel

Ayyukan COUNTIFS na Excel za a iya amfani dasu don ƙidaya adadin bayanan bayanan bayanai a cikin zaɓin da aka zaɓa wanda ya dace da ka'idodi.

COUNTIFS ƙaddamar da amfani da aikin COUNTIF ta hanyar ƙyale ka ka ƙayyade daga 2 zuwa 127 sharuddan maimakon ɗaya kamar a COUNTIF.

Kullum, COUNTIFS yana aiki tare da layuka na bayanai da ake kira records. A cikin rikodin, bayanai a kowace tantanin halitta ko filin a jere suna da alaƙa - kamar sunan kamfanin, adireshin da lambar waya.

COUNTIFS suna neman ƙayyadaddun ma'auni a wurare biyu ko fiye a cikin rikodin kuma idan ya samo wasa don kowane filin da aka ƙayyade shi ne kididdigar rikodin.

01 na 09

COUNTIFS Ayyukan Mataki na Mataki na Mataki

A cikin COUNTIF daga kowane mataki na kwalejinmu mun daidaita da ma'auni na masu sayar da tallace-tallace waɗanda suka sayar da fiye da 250 umarni a cikin shekara guda.

A cikin wannan koyo, za mu kafa yanayin ta biyu ta amfani da COUNTIFS - wanda ke cikin tallace-tallace na tallace-tallace a yankin Gabas ta Gabas wanda ya sanya tallace-tallace fiye da 250 a cikin shekara ta gabata.

Ana sanya ƙarin yanayi ta hanyar ƙayyade ƙarin Criteria_range da Criteria abubuwan da aka yi wa COUNTIFS.

Biye matakai a cikin darussan koyawa da ke ƙasa ke tafiya ta hanyar samarwa da yin amfani da aikin COUNTIFS da aka gani a hoton da ke sama.

Tutorial Topics

• Shigar da Bayanan Tutorial
• Hanyoyin Sakamakon COUNTIFS
• Fara aikin COUNTIFS
• A Criteria_range1 Argument
• The Criteria1 Argument
• The Criteria_range2 Argument
• The Criteria2 Argument
• Kuma Amsar ita ce ..

02 na 09

Shigar da Bayanan Tutorial

Mataki na farko da amfani da COUNTIFS aiki a Excel shi ne shigar da bayanai.

Don wannan koyo shigar da bayanan da aka gani a cikin hoton da ke sama cikin sel D1 zuwa F11 na takardar aiki na Excel.

A jere 12 a ƙasa da bayanan da za mu ƙara aikin COUNTIFS da ka'idojin bincike guda biyu:

• wakilan tallace-tallace daga yankin Gabas ta Gabas
• fiye da umurnin 250 da aka sanya a shekara

Umarnin koyawa ba sun haɗa da matakan tsarawa don takardar aiki ba.

Wannan ba zai dame shi ba tare da kammala tutorial. Kayan aikinku zai bambanta da misalin da aka nuna, amma aikin COUNTIFS zai ba ku sakamakon wannan.

03 na 09

Hanyoyin Sakamakon COUNTIFS

A cikin Excel, haɗin aikin yana nufin layout na aikin kuma ya haɗa da sunan aiki, shafuka, da muhawara .

Hadawa don aikin COUNTIFS shine:

= COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Har zuwa 127 Criteria_range / Criteria nau'i-nau'i za a iya ƙayyade a cikin aiki.

COUNTIFS Ayyukan Magana

Ayyukan aikin na gaya wa COUNTIFS waɗanne ma'auni da muke ƙoƙarin daidaitawa da kuma irin jerin bayanai don bincika waɗannan ka'idoji.

Ana buƙatar dukan muhawarar wannan aikin.

Criteria_range - rukuni na kwayoyin halitta shine aikin nema don bincika wasan da aka dace da hujja.

Mahimmanci - darajar da muke ƙoƙarin daidaita a rikodin bayanan. Ana iya shigar da bayanai na ainihi ko tantancewar tantanin halitta zuwa bayanai don wannan hujja.

04 of 09

Fara aikin COUNTIFS

Kodayake yana yiwuwa don kawai rubuta aikin COUNTIFS da kuma muhawarar zuwa cikin tantanin halitta a cikin wani aikin aiki , mutane da yawa sun fi sauƙi don amfani da maganganun maganganun don shigar da aikin.

Tutorial Steps

1. Danna kan tantanin halitta F12 don yin sautin mai aiki . Wannan shi ne inda za mu shiga aikin COUNTIFS.
2. Danna kan shafukan Formulas .
3. Zaɓi Ƙari Ayyuka> Lissafi daga rubutun don buɗe jerin sauƙaƙe aikin.
4. Danna COUNTIFS a cikin jerin don kawo akwatin maganganun aikin.

Bayanan da muka shiga cikin layi a cikin akwatin maganganu zai haifar da muhawarar aikin COUNTIFS.

Kamar yadda aka ambata, wadannan muhawarar sun nuna aikin abin da muke ƙoƙarin daidaitawa da kuma abin da kewayon bayanai don bincika waɗannan ka'idoji.

05 na 09

Shigar da Criteria_range1 Argument

A cikin wannan koyo muna ƙoƙari mu daidaita ma'auni guda biyu a kowane rikodin bayanai:

1. Jami'an sayar da kayayyaki daga yankin Gabas ta Tsakiya.
2. Jami'an sayar da kayayyaki da ke da lambobi fiye da 250 na shekara.

Shaidar Criteria_range1 yana nuna iyakar kwayoyin halitta COUNTIFS shine bincika yayin ƙoƙarin daidaita ka'idojin farko - yankin yankin gabas.

Tutorial Steps

1. A cikin akwatin maganganu , danna kan Criteria_range1 line.
2. Sanya sassa D3 zuwa D9 a cikin takardun aiki don shigar da waɗannan maƙallan siginar kamar layin da za a bincike ta wurin aikin .

06 na 09

Shigar da Criteria1 Argument

A cikin wannan koyo na farko ma'auni da muke kallon wasan shine idan bayanai a cikin kewayon D3: D9 yana daidaita gabas .

Kodayake ainihin bayanai - irin su kalmar Gabas - za a iya shigar da akwatin maganganun wannan hujja mafi yawa shine shigar da tantanin halitta zuwa wurin wurin data a cikin takardun aiki a cikin akwatin maganganu.

Tutorial Steps

1. Danna kan layi na Criteria1 a cikin akwatin maganganu .
2. Danna kan tantanin halitta D12 don shigar da wannan tantanin halitta a cikin akwatin maganganu.
3. Za a kara ma'anar gabas ta Gabas zuwa cell D12 a mataki na karshe na koyawa.

Ta yaya Siffofin Sanya Ƙara COUNTIFS Versatility?

Idan an yi la'akari da tantanin halitta, irin su D12, a matsayin Maɗaukakin Magana, aikin COUNTIFS zai nemo matakan zuwa duk wani bayanai da aka shigar a cikin tantanin ɗin a cikin takardun aiki.

Don haka bayan da aka ƙidaya adadin jami'ai daga yankin Gabas zai zama sauƙin samun bayanai guda don wani yanki na tallace-tallace ta wurin canza Gabas zuwa Arewa ko Yamma a cell D12. Aikin zai sabunta ta atomatik da kuma nuna sabon sakamakon.

07 na 09

Shigar da Criteria_range2 Argument

Kamar yadda aka ambata a baya, a cikin wannan koyo muna ƙoƙarin daidaita ma'auni guda biyu a kowane rikodin bayanan

1. Jami'an sayar da kayayyaki daga yankin Gabas ta Tsakiya.
2. Jami'an sayar da kayayyaki wadanda suka sanya tallace-tallace fiye da 250 a wannan shekara.

Sha'idar Criteria_range2 ta nuna iyakar ɗakunan Kwayoyin suna COUNTIFS don bincika lokacin ƙoƙarin daidaita ka'idodi na biyu - wakilan tallace-tallace da suka sayar da fiye da 250 a wannan shekara.

Tutorial Steps

1. A cikin akwatin maganganu , danna kan jerin Criteria_range2 .
2. Fassara sel E3 zuwa E9 a cikin takardun aiki don shigar da waɗannan maƙallan siginar kamar layi na biyu don bincike ta wurin aiki .

08 na 09

Shigar da Criteria2 Argument

Shigar da Shawarar Criteria2 da Cika Ayyukan COUNTIFS

A cikin wannan koyo na biyu da muke kallo don daidaitawa shine idan bayanai a cikin kewayon E3: E9 ya fi lambobin tallace-tallace 250.

Kamar yadda yake da hujja na Criteria1, za mu shiga cikin tantanin salula zuwa wurin Criteria2 a cikin akwatin maganganu maimakon bayanai kanta.

Tutorial Steps

1. Danna kan layi na Criteria2 a cikin akwatin maganganu .
2. Danna kan tantanin halitta E12 don shigar da wannan tantanin salula. Ayyukan za su bincika tashar da aka zaba a mataki na baya don bayanai da suka dace da wannan sharudda.
3. Danna Ya yi don kammala aikin COUNTIFS kuma rufe akwatin maganganu.
4. Amsar da zero ( 0 ) zai bayyana a tantanin halitta F12 - tantanin halitta inda muka shiga aikin - saboda ba mu riga muka ƙara bayanai zuwa ga Criteria1 da kuma Criteria2 (C12 da D12) ba. Har sai mun yi haka, babu wani abu ga masu ba da izini su ƙidaya kuma don haka jimillar za ta zauna a kome.
5. Za a kara matakan bincike a mataki na gaba na koyawa.

09 na 09

Ƙara Masarrafan Bincike da Ƙarshen Tutorial

Ƙarshen mataki a cikin koyawa shine don ƙara bayanai zuwa sel a cikin takardun aikin da aka gano kamar yadda ke dauke da hujjoji na Criteria .

Tutorial Steps

1. A cikin sel D12 irin ta gabas kuma latsa maɓallin shigarwa akan keyboard.
2. A cikin na'urar E12 type > 250 kuma danna maɓallin Shigar da ke keyboard (">" alama ce ta fi girma a Excel).
3. Amsar 2 ya kamata ya bayyana a cell F12.
4. Sai kawai jami'ai biyu - Ralph da Sam - aiki a yankin Gabas ta Tsakiya da kuma sanya fiye da 250 umarni na shekara, sabili da haka, kawai wadannan biyu records an kidaya ta hanyar aiki.
5. Ko da yake Martha yana aiki a yankin gabas, tana da kasa da umarni 250, don haka, ba a kidaya rikodin sa.
6. Hakazalika, Joe da Tom duka suna da umarni fiye da 250 a shekara, amma ba su aiki a yankin gabas ta Tsakiya don haka ba a kididdige su ba.
7. Lokacin da ka danna kan tantanin halitta F12, cikakken aikin
   = COUNTIFS (F3: F9, D3: D9, D12, E3: E9, E12) ya bayyana a cikin maɓallin tsari a sama da takardun aiki .