यह ट्यूटोरियल दर्शाता है कि कैसे उपयोग करना हैExcएली COUNTIF और COUNTIFS funcमाहौल एक्सेल में कुछ मानदंडों को पूरा करने वाले डेटा की गणना करने के लिए।
COUNTIF फ़ंक्शन अवलोकन
आप एक्सेल में COUNTIF फ़ंक्शन का उपयोग उन कक्षों की गणना करने के लिए कर सकते हैं जिनमें एक विशिष्ट मान होता है, उन कक्षों की गणना करें जो किसी मान से अधिक या उसके बराबर हैं, आदि।
(ध्यान दें कि सूत्र इनपुट कैसे दिखाई देते हैं)
COUNTIF फ़ंक्शन सिंटैक्स और तर्क:
=COUNTIF (रेंज, मानदंड)
श्रेणी - गिनने के लिए कक्षों की श्रेणी.
मानदंड - मानदंड जो नियंत्रित करते हैं कि किन कोशिकाओं की गणना की जानी चाहिए।
COUNTIF फ़ंक्शन क्या है?
COUNTIF फ़ंक्शन स्प्रेडशीट में उपयोग किए जाने वाले पुराने कार्यों में से एक है। सरल शब्दों में, यह किसी श्रेणी को स्कैन करने और आपको यह बताने में बहुत अच्छा है कि कितनी कोशिकाएं उस स्थिति को पूरा करती हैं। हम देखेंगे कि फ़ंक्शन टेक्स्ट, संख्याओं और तिथियों के साथ कैसे काम करता है; साथ ही कुछ अन्य स्थितियां जो उत्पन्न हो सकती हैं।
मूल उदाहरण
आइए यादृच्छिक वस्तुओं की इस सूची को देखकर शुरू करें। हमारे पास कुछ संख्याएं, रिक्त कक्ष और कुछ टेक्स्ट स्ट्रिंग हैं।
यदि आप जानना चाहते हैं कि मानदंड से सटीक मिलान कितने आइटम हैं, तो आप निर्दिष्ट कर सकते हैं कि आप दूसरे तर्क के रूप में क्या देखना चाहते हैं। इस सूत्र का एक उदाहरण इस तरह दिख सकता है
=COUNTIF(A2:A9, "Apple")
यह सूत्र संख्या 3 लौटाएगा, क्योंकि हमारी सीमा में 3 कक्ष हैं जो उस मानदंड को पूरा करते हैं। वैकल्पिक रूप से, हम किसी मान को हार्डकोड करने के बजाय सेल संदर्भ का उपयोग कर सकते हैं। यदि हम सेल G2 में "Apple" लिखते हैं, तो हम सूत्र को बदल सकते हैं
=COUNTIF(A2:A9, G2)
संख्या के साथ काम करते समय, पाठ के रूप में संग्रहीत संख्याओं और संख्याओं के बीच अंतर करना महत्वपूर्ण है। आम तौर पर, आप सूत्र लिखते समय संख्याओं के आसपास उद्धरण चिह्न नहीं लगाते हैं। तो, एक सूत्र लिखने के लिए जो संख्या 5 की जांच करता है, आप लिखेंगे
=COUNTIF(A2:A9, 5)
अंत में, हम शून्य-लंबाई वाले स्ट्रिंग का उपयोग करके रिक्त कक्षों की जांच भी कर सकते हैं। हम उस सूत्र को इस प्रकार लिखेंगे
=COUNTIF(A2:A9, "")
ध्यान दें: यह सूत्र उन दोनों कक्षों की गणना करेगा जो वास्तव में खाली हैं, साथ ही वे जो किसी सूत्र के परिणाम के रूप में रिक्त हैं, जैसे कि IF फ़ंक्शन।
आंशिक मैच
COUNTIF फ़ंक्शन मापदंड में वाइल्डकार्ड, "*" या "?" के उपयोग का समर्थन करता है। आइए स्वादिष्ट बेकरी सामानों की इस सूची को देखें:
ऐप्पल से शुरू होने वाली सभी वस्तुओं को खोजने के लिए, हम "ऐप्पल *" लिख सकते हैं। तो, 3 का उत्तर पाने के लिए, D2 में हमारा सूत्र है
=COUNTIF(A2:A5, "Apple*")
ध्यान दें: COUNTIF फ़ंक्शन केस-संवेदी नहीं है, इसलिए आप चाहें तो "सेब*" भी लिख सकते हैं।
अपने पके हुए माल पर वापस, हम यह भी पता लगाना चाहेंगे कि हमारी सूची में हमारे पास कितने पाई हैं। हम अपने खोज शब्द की शुरुआत में वाइल्डकार्ड रखकर इसे ढूंढ सकते हैं, और लिख सकते हैं
=COUNTIF(A2:A5, "*पाई")
यह सूत्र 2 का परिणाम देता है।
हम टेक्स्ट के साथ किसी भी सेल की जांच के लिए वाइल्डकार्ड का उपयोग भी कर सकते हैं। आइए डेटा की अपनी मूल सूची पर वापस जाएं।
उन कक्षों की संख्या की गणना करने के लिए जिनमें कम से कम कुछ पाठ हैं, इस प्रकार संख्याओं या रिक्त कक्षों की गिनती नहीं करते हुए, हम लिख सकते हैं
=COUNTIF(A2:A9, "*")
आप देख सकते हैं कि हमारा सूत्र सही ढंग से 4 का परिणाम देता है।
COUNTIF में तुलना ऑपरेटर
अब तक मानदंड लिखते समय, हम यह कहते रहे हैं कि हमारा तुलना ऑपरेटर "=" है। वास्तव में, हम यह लिख सकते थे:
=COUNTIF(A2:A9, "=Apple")
हालांकि यह लिखने के लिए एक अतिरिक्त चरित्र है, इसलिए इसे आमतौर पर छोड़ दिया जाता है। हालांकि, इसका मतलब है कि आप अन्य ऑपरेटरों का उपयोग कर सकते हैं जैसे कि इससे बड़ा, इससे कम, या इसके बराबर नहीं। आइए दर्ज उम्र की इस सूची को देखें:
अगर हम जानना चाहते हैं कि कम से कम 5 साल के कितने बच्चे हैं, तो हम इस तरह से "इससे बड़ा या बराबर" लिख सकते हैं:
=COUNTIF(A2:A8, ">=5")
ध्यान दें: तुलना ऑपरेटर को हमेशा टेक्स्ट स्ट्रिंग के रूप में दिया जाता है, और इस प्रकार उद्धरण चिह्नों के अंदर होना चाहिए।
इसी तरह, आप उन वस्तुओं की भी जांच कर सकते हैं जो किसी दिए गए मूल्य से कम हैं। अगर हमें यह पता लगाना है कि 8 से कम कितने हैं, तो हम लिख सकते हैं
=COUNTIF(A2:A8, "<8")
यह हमें 5 का वांछित परिणाम देता है। अब आइए कल्पना करें कि सभी 6 साल के बच्चे सैर पर जा रहे हैं। कितने बच्चे रहेंगे? हम इस तरह की तुलना "बराबर नहीं" का उपयोग करके इसका पता लगा सकते हैं:
=COUNTIF(A2:A8, "6")
अब हम जल्दी से देख सकते हैं कि हमारे 6 बच्चे हैं जो 6 साल के नहीं हैं।
अब तक के इन तुलनात्मक उदाहरणों में, हम अपने इच्छित मूल्यों की कड़ी कोडिंग कर रहे हैं। आप एक सेल संदर्भ का भी उपयोग कर सकते हैं। चाल यह है कि आपको तुलना ऑपरेटर को सेल संदर्भ के साथ जोड़ना होगा। मान लीजिए कि हम संख्या 7 को सेल C2 में रखते हैं, और हम चाहते हैं कि हमारा फॉर्मूला D2 में यह दिखाए कि कितने बच्चे 7 साल से कम उम्र के हैं।
D2 में हमारे सूत्र को इस तरह दिखने की जरूरत है:
=COUNTIF(A2:A8, "<"&C2)
ध्यान दें: इन फ़ार्मुलों को लिखते समय इस बात पर विशेष ध्यान दें कि आपको किसी आइटम को उद्धरण चिह्नों के अंदर रखना है या बाहर। ऑपरेटर हमेशा कोटेशन के अंदर होते हैं, सेल संदर्भ हमेशा कोटेशन के बाहर होते हैं। यदि आप सटीक मिलान कर रहे हैं तो संख्याएं बाहर हैं, लेकिन तुलना ऑपरेटर करते समय अंदर हैं।
तारीखों के साथ काम करना
हमने देखा है कि आप मानदंड के रूप में टेक्स्ट या नंबर कैसे दे सकते हैं, लेकिन जब हमें तारीखों के साथ काम करने की आवश्यकता हो तो क्या होगा? यहां एक त्वरित नमूना सूची है जिसके साथ हम काम कर सकते हैं:
4 मई के बाद कितनी तारीखें हैं यह गिनने के लिए हमें थोड़ी सावधानी बरतने की जरूरत है। कंप्यूटर तिथियों को संख्याओं के रूप में संग्रहीत करते हैं, इसलिए हमें यह सुनिश्चित करने की आवश्यकता है कि कंप्यूटर सही संख्या का उपयोग करता है। यदि हम यह सूत्र लिख दें तो क्या हमें सही परिणाम मिलेगा?
=COUNTIF(A2:A9, "
जवाब "संभवतः" है। चूंकि हमने वर्ष को हमारे मानदंड से हटा दिया है, कंप्यूटर मान लेगा कि हमारा मतलब चालू वर्ष है। यदि हम जिन तिथियों के साथ काम कर रहे हैं, वे चालू वर्ष के लिए हैं, तो हमें सही उत्तर मिलेगा। अगर कुछ तिथियां हैं जो भविष्य में हैं, तो हमें गलत उत्तर मिलेगा। साथ ही, अगला साल शुरू होने के बाद, यह फॉर्मूला एक अलग परिणाम देगा। इस प्रकार, इस वाक्यविन्यास से शायद बचा जाना चाहिए।
चूंकि किसी सूत्र के भीतर तिथियों को सही ढंग से लिखना मुश्किल हो सकता है, इसलिए उस तिथि को लिखना सबसे अच्छा अभ्यास है जिसे आप किसी सेल में उपयोग करना चाहते हैं, और फिर आप उस सेल संदर्भ का उपयोग अपने COUNTIF सूत्र में कर सकते हैं। तो चलिए सेल C2 में 7-May-2020 की तारीख लिखते हैं, और फिर हम अपना फॉर्मूला C4 में डाल सकते हैं।
C4 में सूत्र है
=COUNTIF(A2:A9, "<"&C2)
अब हम जानते हैं कि 7 का परिणाम सही है, और यदि हम भविष्य में कभी इस स्प्रेडशीट को खोलते हैं तो उत्तर अप्रत्याशित रूप से बदलने वाला नहीं है।
इस अनुभाग को छोड़ने से पहले, तिथियों के साथ कार्य करते समय TODAY फ़ंक्शन का उपयोग करना आम बात है। हम इसका उपयोग वैसे ही कर सकते हैं जैसे हम एक सेल संदर्भ करेंगे। उदाहरण के लिए, हम पिछले सूत्र को इस प्रकार बदल सकते हैं:
=COUNTIF(A2:A9, "<"&TODAY ())
अब हमारा फ़ॉर्मूला अभी भी अपडेट होगा क्योंकि वास्तविक समय आगे बढ़ेगा, और हमारे पास उन वस्तुओं की गिनती होगी जो आज की तुलना में कम हैं।
एकाधिक मानदंड और COUNTIFS
मूल COUNTIF फ़ंक्शन में 2007 में सुधार हुआ जब COUNTIFS सामने आया। दोनों के बीच सिंटैक्स बहुत समान है, बाद वाला आपको अतिरिक्त रेंज और मानदंड देने की अनुमति देता है। आप आसानी से COUNTIFS का उपयोग किसी भी स्थिति में कर सकते हैं कि COUNTIF मौजूद है। यह जानना एक अच्छा विचार है कि दोनों कार्य मौजूद हैं।
आइए डेटा की इस तालिका को देखें:
यह पता लगाने के लिए कि 1 से 2 के वेतन स्तर में कितने लोग हैं, आप COUNTIF कार्यों का एक सारांश इस तरह लिख सकते हैं:
=COUNTIF(B2:B7, ">=1")-COUNTIF(B2:B7, ">2")
यह फॉर्मूला काम करेगा, क्योंकि आप 1 से ऊपर की हर चीज ढूंढ रहे हैं, लेकिन फिर आपके कट-ऑफ पॉइंट से परे रिकॉर्ड्स की संख्या घटा रहे हैं। वैकल्पिक रूप से, आप इस तरह COUNTIFS का उपयोग कर सकते हैं:
=COUNTIFS(B2:B7, ">=1", B2:B7, "<=2")
उत्तरार्द्ध पढ़ने के लिए अधिक सहज है, इसलिए आप उस मार्ग का उपयोग करना चाह सकते हैं। साथ ही, जब आपको अनेक स्तंभों पर विचार करने की आवश्यकता होती है, तो COUNTIFS अधिक शक्तिशाली होता है। मान लें कि हम जानना चाहते हैं कि प्रबंधन में और वेतन स्तर 1 में कितने लोग हैं। आप केवल एक COUNTIF के साथ ऐसा नहीं कर सकते; आपको लिखना होगा
=COUNTIFS(A2:A7, "प्रबंधन", B2:B7, 1)
यह सूत्र आपको 2 का सही परिणाम देगा। इस खंड को छोड़ने से पहले, आइए एक या प्रकार के तर्क पर विचार करें। क्या होगा अगर हम यह पता लगाना चाहते हैं कि प्रबंधन में कितने लोग हैं या? आपको कुछ COUNTIFS एक साथ जोड़ने होंगे, लेकिन ऐसा करने के दो तरीके हैं। इसे इस तरह लिखना आसान तरीका है:
=COUNTIF(A2:A7, "HR")+COUNTIF(A2:A7, "Management")
आप एक सरणी का उपयोग भी कर सकते हैं, और इस सरणी सूत्र को लिख सकते हैं:
=SUM(COUNTIF(A2:A7, {"HR", "Management"}))
ध्यान दें: सरणी फ़ार्मुलों की पुष्टि `Ctrl+Shift+Enter` का उपयोग करके की जानी चाहिए, न कि केवल `Enter` से।
यह सूत्र कैसे काम करेगा यह देखेगा कि आपने इनपुट के रूप में एक सरणी दी है। इस प्रकार यह परिणाम को दो अलग-अलग COUNTIF कार्यों के लिए गणना करेगा और उन्हें एक सरणी में संग्रहीत करेगा। एसयूएम फ़ंक्शन तब हमारे एरे में सभी परिणामों को एक साथ जोड़ देगा और एक एकल आउटपुट बना देगा। इस प्रकार, हमारे सूत्र का मूल्यांकन इस प्रकार किया जाएगा:
=SUM(COUNTIF(A2:A7, {"HR", "Management"})) =SUM({2, 3}) =5
अद्वितीय मान गिनें
अब जब हमने देखा है कि COUNTIF फ़ंक्शन के साथ एक सरणी का उपयोग कैसे किया जाता है, तो हम एक कदम आगे बढ़ सकते हैं ताकि हमें यह गिनने में मदद मिल सके कि किसी श्रेणी में कितने अद्वितीय मान हैं। सबसे पहले, आइए हमारे विभागों की सूची को फिर से देखें।
=SUM(1/COUNTIF(A2:A7,A2:A7))
हम देख सकते हैं कि डेटा के लायक ६ सेल हैं, लेकिन केवल ३ अलग-अलग आइटम हैं। गणित को काम करने के लिए, हमें प्रत्येक आइटम की कीमत 1/N की आवश्यकता होगी, जहां N किसी आइटम को बार-बार दोहराने की संख्या है। उदाहरण के लिए, यदि प्रत्येक एचआर केवल 1/2 के बराबर था, तो जब आप उन्हें जोड़ते हैं तो आपको 1 अद्वितीय मूल्य के लिए 1 की गिनती मिलेगी।
हमारे COUNTIF पर वापस जाएं, जिसे यह पता लगाने के लिए डिज़ाइन किया गया है कि कोई आइटम किसी श्रेणी में कितनी बार दिखाई देता है। D2 में, हम सरणी सूत्र लिखेंगे
=SUM(1/COUNTIF(A2:A7, A2:A7))
यह सूत्र कैसे काम करेगा, यह A2:A7 की श्रेणी में प्रत्येक सेल के लिए है, यह देखने के लिए जांच करेगा कि यह कितनी बार दिखाई देता है। हमारे नमूने के साथ, यह एक सरणी का उत्पादन करने जा रहा है
{2, 2, 3, 3, 3, 1}
फिर, हम कुछ भाग करके उन सभी संख्याओं को भिन्नों में बदल देते हैं। अब हमारी सरणी दिखती है
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
जब हम इन सभी को जोड़ते हैं, तो हमें 3 का वांछित परिणाम मिलता है।
दो या एकाधिक शर्तों के साथ काउंटिफ़ - काउंटिफ़ फ़ंक्शन
अभी तक हमने केवल COUNTIF फंक्शन के साथ काम किया है। COUNTIF फ़ंक्शन एक समय में केवल एक मानदंड को संभाल सकता है। कई मानदंडों के साथ COUNTIF करने के लिए आपको COUNTIFS फ़ंक्शन का उपयोग करने की आवश्यकता है। COUNTIFS बिल्कुल COUNTIF की तरह व्यवहार करता है। आप बस अतिरिक्त मानदंड जोड़ें। आइए नीचे दिए गए उदाहरण पर एक नज़र डालें।
=COUNTIFS(B2:B7,"=130")
Google पत्रक में COUNTIF और COUNTIFS
COUNTIF और COUNTIFS फ़ंक्शन Google शीट्स में ठीक उसी तरह काम करता है जैसे एक्सेल में:
