Mawerengedwe Owerengetsa omwe Amatsata Zodalirika Zopadera ndi Excel COUNTIFS Function

Zolemba za COUNTIFIF za Excel zingagwiritsidwe ntchito kuwerengera chiwerengero cha zolemba zadeta muzithunzi zosankhidwa zomwe zikugwirizana ndi zofunikira.

COUNTIFS imapindulitsa ntchito COUNTIF mwa kukulolani kuti mufotokoze kuchokera pa 2 mpaka 127 zofunikira m'malo mofanana ndi COUNTIF.

Kawirikawiri, COUNTIFS imagwira ntchito ndi mizere ya deta yomwe imatchedwa ma rekodi. M'mbuyo, deta iliyonse kapena selo iliyonse mzerewu imagwirizana - monga dzina la kampani, adiresi ndi nambala ya foni.

COUNTIFS imawunika zoyenera pazinthu ziwiri kapena zingapo m'makalata ndipo ngati atapeza machesi pamunda uliwonse womwe wafotokozedwa ndizowerengedwa.

01 ya 09

KHALANI KUGWIRITSANI NTCHITO YOTSATIRA Phunziro

Mu phunziro la COUNTIF ndi phazi lomwe tinalumikizana nalo, tinagwirizana ndi ndondomeko imodzi ya ogulitsa malonda omwe adagulitsa maola 250 pa chaka.

Mu phunziro ili, tidzakhazikitsa chikhalidwe chachiwiri pogwiritsa ntchito COUNTIFS - zomwe zimagulitsa amalonda ku dera la East sales zomwe zinapanga malonda oposa 250 chaka chatha.

Kukhazikitsa zina zowonjezera kumachitika pofotokozera zina Zowonjezera Criteria_range ndi Zotsutsa za COUNTIFS.

Kutsatira ndondomeko m'mitu yophunzitsira pansipa ikukuyenderani popanga ndi kugwiritsa ntchito COUNTIFS ntchito yomwe ikuwonetsedwa mu chithunzi pamwambapa.

Mitu Yophunzitsa

• Kulowa Datorial Data
• Syntax ya COUNTIFS Function
• Kuyambira COUNTIFS Ntchito
• The Criteria_range1 Kutsutsana
• Chotsutsa1 Kukangana
• Mtsutso wa Criteria_range2
• Mtsutso wa Criteria2
• Ndipo Yankho liri ..

02 a 09

Kulowa Datorial Data

Njira yoyamba yogwiritsira ntchito COUNTIFS ntchito ku Excel ndikolowetsa deta.

Phunziro ili lilowetse deta yomwe ikuwonetsedwa mu chithunzi pamwamba pa maselo D1 mpaka F11 pa tsamba la Excel.

Mu mzere 12 pansi pa deta tidzowonjezera COUNTIFS ntchito ndi zofufuzira ziwiri:

• ogulitsa malonda kuchokera ku East East chigawo
• malamulo oposa 250 omwe aperekedwa chaka

Mauthengawa saphatikizapo kupanga mapangidwe a tsamba.

Izi sizidzasokoneza kukwaniritsa maphunziro. Tsamba lanu la ntchito lidzawoneka mosiyana ndi chitsanzo chikuwonetsedwa, koma ntchito COUNTIFS idzakupatsani zotsatira zomwezo.

03 a 09

Syntax ya COUNTIFS Function

Mu Excel, syntax ya ntchito imatanthawuza momwe ntchitoyo ikuyendera ndipo imaphatikizapo dzina la ntchito, mabakita, ndi zifukwa .

Chizindikiro cha COUNTIFS ndi:

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

Kufikira 127 Criteria_range / Pawiri yachiwiri angathe kufotokozedwa mu ntchitoyi.

Maganizo a COUNTIFS Function

Zolinga za ntchitoyo zimapereka COUNTIFS zomwe tikuyesera kuti zifanane ndi deta yamtundu wanji kuti tifufuze kuti tipeze izi.

Zonse zokhudzana ndi ntchitoyi zimafunika.

Criteria_range - gulu la maselo ntchitoyo ndi kufufuza machesi ku mgwirizano wovomerezeka woyenera.

Zolinga - mtengo umene tikuyesera kuti ufanane ndi mbiri ya data. Dongosolo lenileni kapena mawonekedwe a selo pa deta angalowetsedwe pazitsutsano izi.

04 a 09

Kuyambira COUNTIFS Ntchito

Ngakhale kuti n'zotheka kungolemba zochitika za COUNTIFS ndi zifukwa zake mu selolo, anthu ambiri amavutika kuti agwiritse ntchito bokosi la polojekitiyi kuti alowe ntchitoyo.

Maphunziro Otsogolera

1. Dinani pa selo F12 kuti mupange selo yogwira ntchito . Apa ndi pamene tidzalowa ntchito COUNTIFS.
2. Dinani pa Fomu ya Fomu.
3. Sankhani Ntchito Zambiri> Chiwerengero chochokera ku riboni kuti mutsegule ntchitoyi.
4. Dinani pa COUNTIFI mu mndandanda kuti mubweretse bokosi lazokambirana.

Deta yomwe tifika mu mizere yopanda kanthu mu bokosi la zokambirana idzakhazikitsa zifukwa za COUNTIFS.

Monga tafotokozera, zifukwa izi zimapereka ntchito zomwe tikuyesera kuti zifanane ndi deta yamtundu wanji kuti tipeze izi.

05 ya 09

Kulowa mu Criteria_range1 Kukangana

Mu phunziro ili tikuyesera kufanana ndi zofunikira ziwiri pa chiwerengero chilichonse cha data:

1. Ogulitsa malonda kuchokera ku East East dera.
2. Ogulitsira malonda omwe ali ndi zoposa 250 malonda a chaka.

Mtsutso wa Criteria_range1 umasonyeza maselo osiyanasiyana COUNTIFS ndikofunafuna pamene akuyesera kufanana ndi zoyambazo - dera la East East.

Maphunziro Otsogolera

1. Mu bokosi la bokosi , dinani pa Criteria_range1 mzere.
2. Onetsetsani maselo D3 mpaka D9 mu tsamba lolemba kuti mulowetse maumboni awa ngati malo omwe angasaka ndi ntchitoyo .

06 ya 09

Kulowetsa Zotsutsana1 Kukangana

Mu phunziroli zoyambirira zomwe tikuyang'ana kuti zifanane ndi ngati deta iliyonse D3: D9 ikufanana ndi East .

Ngakhale chidziwitso chenicheni - monga mawu a East - chingalowe mu bokosi lazokambirana pazokambirana izi ndi bwino kuti mulowetse selolo pa malo a deta muzokambirana.

Maphunziro Otsogolera

1. Dinani pa Criteria1 mzere mu bokosi la dialog .
2. Dinani pa selo D12 kuti mulowetse selolo mu bokosi la dialog.
3. Kufufuza kolowera East kudzawonjezeredwa ku selo D12 mu sitepe yotsiriza ya phunzirolo.

Momwe Makambirano a Zilili Amachezera COUNTIFS Zophatikizapo

Ngati mafotokozedwe a selo, monga D12, alowetsedwa ngati Chigwirizano Chotsutsa, COUNTIFS ntchitoyi idzayang'ana machesi kwa chilichonse chimene chidaikidwa mu selolo.

Choncho mutatha kuwerenga chiwerengero cha antchito ochokera kummawa kwa Africa, zidzakhala zosavuta kupeza deta yofanana ndi dera lina la malonda pokhapokha mutasintha East mpaka North kapena West ku cell D12. Ntchitoyi idzangosintha ndi kusonyeza zotsatira zatsopano.

07 cha 09

Kulowa mu Criteria_range2 Kukangana

Monga tanenera kale, mu phunziro ili tikuyesera kufanana ndi zigawo ziwiri pazomwe deta iliyonse

1. Ogulitsa malonda kuchokera ku East East dera.
2. Ogulitsa ogulitsa omwe apanga zoposa 250 malonda chaka chino.

Mtsutso wa Criteria_range2 ukuwonetsa maselo osiyanasiyana COUNTIFS ndiwafunafuna pamene akuyesera kufanana ndi chiwerengero chachiwiri - ogulitsa malonda omwe agulitsa maola 250 chaka chino.

Maphunziro Otsogolera

1. Mu bokosi la bokosi , dinani pa Criteria_range2 mzere.
2. Onetsetsani maselo E3 kuti E9 awonongeke kuti mulowetse mafotokozedwe a maselowa ngati gawo lachiwiri lofufuzidwa ndi ntchitoyi .

08 ya 09

Kulowa pa Zotsutsa2 Kutsutsana

Kulowetsa Zotsutsa2 Kutsutsana ndikukwaniritsa COUNTIFS Function

Mu phunziroli, njira yachiwiri yomwe tikuyang'ana kuti tiyifanana ndi ngati deta iliyonse E3: E9 ndi yaikulu kuposa maulamuliro 250.

Mofanana ndi ndondomeko ya Criteria1, tidzalowa mu selo loyang'ana pa malo a Criteria2 mu bokosi la zokambirana m'malo mwa deta.

Maphunziro Otsogolera

1. Dinani pa Criteria2 mzere mu bokosi la bokosi .
2. Dinani pa selo E12 kuti mulowetse selolo. Ntchitoyi idzafufuza zosankhidwa zomwe zasankhidwa kumbuyo kwa deta yomwe ikugwirizana ndi izi.
3. Dinani OK kuti mutsirize ntchito COUNTIFS ndi kutseka bokosi la bokosi.
4. Yankho la zero ( 0 ) lidzawoneka mu selo F12 - selo limene talowa mu ntchito - chifukwa sitinayambe kuwonjezera deta ku minda ya Criteria1 ndi Criteria2 (C12 ndi D12). Mpaka titachita, palibe chirichonse cha COUNTIFS kuti chiwerengedwe ndipo kotero chiwerengerocho chikhalapo pa zero.
5. Zotsatira zofufuzira zidzawonjezedwa mu sitepe yotsatira ya phunziroli.

09 ya 09

Kuwonjezera Zofuna Zosaka ndi Kumaliza Tutorial

Gawo lomaliza la phunzirolo ndi kuwonjezera deta ku maselo omwe ali pa tsamba lolembedwa kuti ali ndi zifukwa zotsutsa .

Maphunziro Otsogolera

1. Mu selo la D12 mtundu wa East ndipo dinani fungulo lolowani mukibokosi.
2. Mu selo la E12 mtundu > 250 ndi kukanikiza Mphindi lolowamo mu kibokosi (the ">" ndi chizindikiro chachikulu kuposa mu Excel).
3. Yankho 2 liyenera kuoneka mu selo F12.
4. Azimayi awiri okha - Ralph ndi Sam - amagwira ntchito kumadera a East East ndipo amalemba maulendo oposa 250 a chaka, choncho, zolemba ziwirizi ndizolembedwa.
5. Ngakhale kuti Martha amagwira ntchito kumadera akummawa, anali ndi malamulo osakwana 250 ndipo, chifukwa chake, mbiri yake siwerengedwa.
6. Mofananamo, Joe ndi Tom onse anali ndi malamulo oposa 250 a chaka, koma palibe ntchito kumadera a East East malonda kotero zolemba zawo siziwerengedwanso.
7. Mukasindikiza pa selo F12, ntchito yonse
   = COUNTIFI (F3: F9, D3: D9, D12, E3: E9, E12) ikuwonekera muzenera zapamwamba pamwamba pa tsamba .