Excel में नॉन-ब्लैंक सेल्स गिनें: 5 मुख्य तरीके

मैं पंक्ति में क्षैतिज रूप से भी गिन सकता/सकती हूँ ताकि पता चल सके कि हर व्यक्ति का उत्तर कितना पूरा है:

=COUNTA(B2:F2)संचालित द्वारा

Sara Ibrahim के लिए, यह 5 लौटाता है क्योंकि उसने हर फ़ील्ड भरी। Nour Mahmoud के लिए, =COUNTA(B6:F6) सिर्फ 1 लौटाता है क्योंकि उसने केवल ईमेल दिया और बाकी छोड़ दिया।

COUNTA() कब पर्याप्त है

जब आपको साधारण काउंट चाहिए और आपका डेटा साफ़-सुथरा है, तब COUNTA() का उपयोग करें। यदि आपकी रेंज में हर ब्लैंक सेल वास्तव में खाली है (कभी छुआ नहीं गया, कोई फ़ॉर्मूला नहीं), COUNTA() बेहतरीन काम करता है।

मुश्किल तब आती है जब फ़ॉर्मूले "" (खाली स्ट्रिंग्स) लौटाते हैं। मैं इस विशेष स्थिति को आगे "Excel ब्लैंक बनाम एम्प्टी सेल्स को कैसे मानता है" सेक्शन में विस्तार से समझाऊँगा।

COUNTIF() से नॉन-ब्लैंक सेल्स गिनें

COUNTIF() नॉन-ब्लैंक सेल्स गिनने का एक और तरीका देता है। हर मौजूद चीज़ को गिनने के बजाय, यह शर्त पूरी करने वाले सेल्स को गिनता है। और वह शर्त "खाली न हो" हो सकती है।

"<>" क्राइटेरिया

फ़ॉर्मूला ऐसा दिखता है:

=COUNTIF(B2:B13,"<>")संचालित द्वारा

यहाँ "<>" का मतलब है "कुछ भी नहीं के बराबर नहीं," जो कि "ब्लैंक नहीं" कहने का ही दूसरा तरीका है। पहले वाले ईमेल कॉलम के लिए, यह भी मुझे 10 देता है, ठीक COUNTA() जैसा।

COUNTIF(range,"*") क्यों न उपयोग करें?

मैं इसे पुराने ट्यूटोरियल्स में देखता/देखती हूँ, और यह एक गलती है। वाइल्डकार्ड "*" सिर्फ टेक्स्ट को गिनता है। यह नंबर, दिनांक, और TRUE/FALSE मानों को पूरी तरह अनदेखा करता है। यदि आपके कॉलम में डेटा प्रकार मिश्रित हैं, तो काउंट गलत आएगा।

इसके बजाय "<>" का ही उपयोग करें। यह किसी भी प्रकार के डेटा पर काम करता है।

COUNTIF() कब COUNTA() से बेहतर है

COUNTIF() का असली फ़ायदा यह है कि यह स्वाभाविक रूप से COUNTIFS() तक ले जाता है। जैसे ही आपको नॉन-ब्लैंक सेल्स गिनने होते हैं जो अन्य शर्तें भी पूरी करते हों (जैसे कोई विशेष विभाग या डेट रेंज), COUNTIFS() तैयार मिलता है। यदि आपको लगता है कि आगे चलकर शर्तें जोड़नी पड़ सकती हैं, तो COUNTIF() से शुरू करें ताकि बदलाव आसान हो।

ज़्यादातर डेटा के लिए, COUNTA() और COUNTIF(range,"<>") एक जैसा परिणाम देते हैं। दोनों वही चीज़ें गिनते हैं, जिनमें एरर और फ़ॉर्मूला-जनित ब्लैंक्स शामिल हैं। मुख्य अंतर लचीलापन है। COUNTIF() आपको बहु-मानदंड काउंटिंग के लिए COUNTIFS() तक जोड़ता है।

शर्तें पूरी करने वाले नॉन-ब्लैंक सेल्स गिनें

यहीं से COUNTIF() वाकई चमकता है। COUNTIFS() आपको नॉन-ब्लैंक सेल्स गिनने देता है जो अन्य शर्तें भी पूरी करते हों।

नॉन-ब्लैंक लॉजिक के साथ COUNTIFS()

मान लीजिए मैं कर्मचारी प्रशिक्षण ट्रैक कर रहा/रही हूँ और जानना चाहता/चाहती हूँ कि Sales में कितने लोगों ने स्कोर जमा किया है। फ़ॉर्मूला यह है:

=COUNTIFS(B2:B11,"Sales",E2:E11,"<>")संचालित द्वारा

अर्थ: "वे पंक्तियाँ गिनें जहाँ Department 'Sales' है और Score ब्लैंक नहीं है।" मेरे ट्रैकर में, यह 3 देता है क्योंकि Mona Sayed Sales में है लेकिन उसने अभी तक स्कोर जमा नहीं किया।

एकाधिक शर्तें

आप ज़रूरत होने पर 127 तक क्राइटेरिया पेयर्स जोड़ सकते हैं। यह फ़ॉर्मूला "Complete" मार्क किए कर्मचारियों को गिनता है जिनके पास स्कोर भी है:

=COUNTIFS(C2:C11,"Complete",E2:E11,"<>")संचालित द्वारा

यह मुझे 6 देता है क्योंकि Mona Sayed ने ट्रेनिंग पूरी की, लेकिन स्कोर कभी जमा नहीं किया।

एक और उदाहरण। Engineering वालों को गिनना जिनकी completion dates हैं:

=COUNTIFS(B2:B11,"Engineering",D2:D11,"<>")संचालित द्वारा

यह 3 लौटाता है क्योंकि Omar Mostafa की ट्रेनिंग अभी पेंडिंग है और तारीख नहीं है।

पैटर्न सरल है: जिस कॉलम में नॉन-ब्लैंक जाँचना हो, उसके लिए "<>" का उपयोग करें, और अपने विशिष्ट फ़िल्टर्स बाकी कॉलमों में रखें।

Excel "Blank" बनाम "Empty" सेल्स को कैसे मानता है

ज़्यादातर गिनने की गलतियाँ यहीं होती हैं। Excel "खाली" के अलग-अलग प्रकार देखता है जो स्क्रीन पर एक जैसे दिखते हैं।

वास्तव में खाली सेल्स

वास्तव में खाली सेल कभी छुआ नहीं गया होता या पूरी तरह क्लियर किया गया होता है। ISBLANK() TRUE लौटाता है, COUNTA() इसे छोड़ देता है, और COUNTBLANK() इसे गिनता है। यह एकमात्र सेल प्रकार है जिसे सभी फ़ंक्शन ब्लैंक मानते हैं।

फ़ॉर्मूला-जनित खाली स्ट्रिंग्स

यह अक्सर लोगों को फँसाता है। जब =IF(A1>60,"Pass","") जैसा फ़ॉर्मूला "" लौटाता है, तो सेल खाली दिखता है पर वास्तव में उसमें फ़ॉर्मूला का परिणाम होता है। अलग-अलग फ़ंक्शन इसे ऐसे देखते हैं:

• COUNTA() → इसे गिनता है (नॉन-ब्लैंक)
• COUNTIF(range,"<>") → इसे गिनता है (नॉन-ब्लैंक)
• COUNTBLANK() → इसे गिनता है (ब्लैंक)
• ISBLANK() → FALSE लौटाता है

समस्या समझे? COUNTBLANK() इसे ब्लैंक कहता है, लेकिन ISBLANK() कहता है कि नहीं। यह उन लोगों को भी उलझा देता है जो सालों से Excel इस्तेमाल कर रहे हैं।

SUMPRODUCT() + LEN() वर्कअराउंड

सिर्फ उन सेल्स को गिनने के लिए जिनमें वास्तविक दृश्य सामग्री हो (कोई फ़ॉर्मूला ब्लैंक, कोई स्पेस नहीं), यह उपयोग करें:

=SUMPRODUCT(--(LEN(B5:B10)>0))संचालित द्वारा

यह जाँचता है कि हर सेल में कितने कैरेक्टर्स हैं। अगर LEN() 0 लौटाता है, तो सेल हर हाल में खाली है। -- भाग TRUE/FALSE को 1/0 में बदलता है, और SUMPRODUCT()` उन्हें जोड़ देता है।

और भी सख्त काउंट जिसके तहत स्पेस वाले सेल्स भी अनदेखे हों:

=SUMPRODUCT(--(LEN(TRIM(B5:B10))>0))संचालित द्वारा

छिपे हुए कैरेक्टर्स वाले सेल्स

कभी-कभी सेल खाली दिखता है, पर उसमें एक स्पेस (CHAR(32)) या नॉन-ब्रेकिंग स्पेस (CHAR(160)) होता है—वेब डेटा या PDF से आया हुआ। COUNTA() इसे गिनता है, LEN() 1 या अधिक लौटाता है, और ISBLANK() FALSE बताता है।

इन्हें अपने काउंट में पकड़ने के लिए, पहले दिखाया सख्त फ़ॉर्मूला उपयोग करें: =SUMPRODUCT(--(LEN(TRIM(range))>0))। TRIM() आम स्पेस हटाता है। नॉन-ब्रेकिंग स्पेस के लिए, पहले डेटा को =SUBSTITUTE(A1,CHAR(160),"") से साफ करें या CLEAN() फ़ंक्शन का उपयोग करें।

फ़िल्टर्ड डेटा में नॉन-ब्लैंक सेल्स गिनें

यह चीज़ अक्सर लोगों को चौंका देती है: आप डेटा फ़िल्टर करते हैं, COUNTA() लिखते हैं, और परिणाम में हिडन रो भी शामिल रहती हैं। क्योंकि COUNTA() फ़िल्टर्स की परवाह नहीं करता। यह रेंज में सब कुछ गिनता है—चाहे आप देख सकें या नहीं।

SUBTOTAL(): फ़िल्टर-समझदार विकल्प

SUBTOTAL() इसे ठीक करता है। यह फ़िल्टर्स का सम्मान करते हुए गिनता है।

=SUBTOTAL(3,D2:D11)संचालित द्वारा

यहाँ 3 SUBTOTAL() को COUNTA()-स्टाइल काउंट करने को कहता है। फर्क: यह फ़िल्टर से छिपी पंक्तियाँ छोड़ देता है।

फ़ंक्शन नंबर: 3 बनाम 103

SUBTOTAL() के दो मोड होते हैं:

• फ़ंक्शन नंबर 3: AutoFilter से छिपी पंक्तियाँ छोड़ता है, लेकिन मैन्युअली हिडन पंक्तियों को फिर भी गिनता है।
• फ़ंक्शन नंबर 103: सभी हिडन पंक्तियाँ (फ़िल्टर और मैन्युअल दोनों) छोड़ता है।

=SUBTOTAL(3,D2:D11)    -- Skips AutoFilter rows
=SUBTOTAL(103,D2:D11)  -- Skips ALL hidden rowsसंचालित द्वारा

अधिकांश समय, 3 ही चाहिए। 103 का उपयोग तभी करें जब आपने पंक्तियाँ मैन्युअली छिपाई हों (right-click > Hide) और आप उन्हें भी बाहर रखना चाहते हों।

AGGREGATE(): गंदे डेटा के लिए विकल्प

यदि आपके फ़िल्टर्ड डेटा में एरर वैल्यूज़ हैं, तो SUBTOTAL() एरर देगा। AGGREGATE() इसे बेहतर संभालता है:

=AGGREGATE(3,7,D2:D11)संचालित द्वारा

यहाँ 3 फ़ंक्शन नंबर है (COUNTA()), और 7 उसे हिडन पंक्तियाँ और एरर—दोनों—नज़रअंदाज़ करने को कहता है। यह गंदे डेटा के लिए सबसे सुरक्षित विकल्प है, हालांकि अधिकांश लोगों को इसकी ज़रूरत नहीं पड़ती।

Excel टेबल्स में नॉन-ब्लैंक सेल्स गिनना

यदि आपका डेटा Excel Table में है (Insert > Table दबाएँ या Ctrl+T), तो आप सेल एड्रेस की बजाय स्ट्रक्चर्ड रेफरेंसेज़ का उपयोग कर सकते हैं। इससे फ़ॉर्मूले स्पष्ट होते हैं और टेबल बढ़ने पर वे स्वतः अपडेट होते हैं।

COUNTA() के साथ स्ट्रक्चर्ड रेफरेंसेज़

यह लिखने की बजाय =COUNTA(C2:C9), आप यह लिखते हैं:

=COUNTA(TaskTracker[Assigned To])संचालित द्वारा

यह मेरे TaskTracker टेबल के Assigned To कॉलम में नॉन-ब्लैंक सेल्स गिनता है। यह रेफरेंस हेडर रो को अपने आप छोड़ देता है और नए रो जोड़ने पर फैल जाता है।

स्ट्रक्चर्ड रेफरेंस के साथ COUNTIFS() मिलाना

COUNTIFS() टेबल्स के साथ बेहतरीन काम करता है:

=COUNTIFS(TaskTracker[Status],"Done",TaskTracker[Completion Date],"<>")संचालित द्वारा

यह उन टास्क्स को गिनता है जो "Done" मार्क हैं और जिनकी completion date है। मेरे ट्रैकर में, यह 4 देता है क्योंकि सभी पूरे किए गए टास्क्स की तिथियाँ हैं।

काउंटिंग के लिए टेबल्स क्यों बेहतर हैं

दो बातें टेबल्स को उपयोगी बनाती हैं। पहला, स्ट्रक्चर्ड रेफरेंसेज़ अपने आप हेडर रो को छोड़ देते हैं, इसलिए आपको रो 2 से शुरू करने की चिंता नहीं रहती। दूसरा, जब आप टेबल में नया रो जोड़ते हैं, तो स्ट्रक्चर्ड रेफरेंस वाले सभी फ़ॉर्मूले नया डेटा पकड़ लेते हैं। रेंजेज़ को मैन्युअली अपडेट करने की ज़रूरत नहीं।

यह समय के साथ बढ़ते डेटा—जैसे लॉग्स, ट्रैकर्स, या नियमित रूप से इम्पोर्ट किया गया डेटा—के लिए बेहतरीन है।

नॉन-ब्लैंक सेल्स गिनते समय आम समस्याएँ

काउंट अपेक्षा से ज़्यादा है

क्या होता है: COUNTA() 6 बताता है, पर आपको सिर्फ 4 सेल्स में डेटा दिखता है।

क्यों: दो सेल्स में ऐसे फ़ॉर्मूले हैं जो "" लौटाते हैं। जैसा पहले बताया, ये खाली दिखते हैं पर तकनीकी रूप से खाली नहीं होते।

समाधान: =SUMPRODUCT(--(LEN(range)>0)) का उपयोग करें COUNTA() की जगह। यह केवल दृश्य सामग्री वाले सेल्स को गिनता है, फ़ॉर्मूला-जनित ब्लैंक्स को नज़रअंदाज़ करता है।

छिपे स्पेस काउंट बिगाड़ देते हैं

क्या होता है: सेल खाली दिखता है, लेकिन COUNTA() उसे गिनता है और ISBLANK() FALSE बताता है।

क्यों: सेल में स्पेसेज़ हैं, शायद किसी वेबसाइट या दूसरी फ़ाइल से कॉपी किया गया डेटा।

समाधान: =LEN(A1) से जाँचें। यदि सेल खाली दिखते हुए भी 0 से अधिक लौटाता है, तो छिपे कैरेक्टर्स हैं। पहले डेटा साफ़ करें =TRIM(A1) से, या पहले दिखाया सख्त फ़ॉर्मूला उपयोग करें: =SUMPRODUCT(--(LEN(TRIM(range))>0))।

एरर वैल्यूज़ आपकी इच्छा के विरुद्ध गिनी जाती हैं

क्या होता है: आप नॉन-ब्लैंक सेल्स गिनना चाहते हैं, पर #DIV/0! या #N/A जैसी त्रुटियाँ भी शामिल हो जाती हैं।

क्यों: COUNTA() और COUNTIF(range,"<>") दोनों एरर को नॉन-ब्लैंक मानते हैं।

समाधान: यदि आपके डेटा में फ़िल्टर्स भी हैं, तो =AGGREGATE(3,7,range) उपयोग करें जैसा फ़िल्टर्ड डेटा सेक्शन में बताया। 7 हिडन पंक्तियाँ और एरर—दोनों—स्किप करता है। बिना फ़िल्टर वाले डेटा के लिए, =SUMPRODUCT(--(LEN(IFERROR(range,""))>0)) उपयोग करें।

फ़िल्टरिंग के बाद काउंट अपडेट नहीं होता

क्या होता है: आप डेटा फ़िल्टर करते हैं और COUNTA() अब भी अनफ़िल्टर्ड काउंट दिखाता है।

क्यों: COUNTA() फ़िल्टर्स को पूरी तरह अनदेखा करता है।

समाधान: =SUBTOTAL(3,range) उपयोग करें जैसा फ़िल्टर्ड डेटा सेक्शन में कवर किया गया। यह स्वचालित रूप से दृश्य के आधार पर समायोजित होता है।

नॉन-ब्लैंक सेल्स गिनने की सर्वोत्तम प्रक्रियाएँ

इन मुद्दों से कई बार निपटने के बाद, अब मैं यह करता/करती हूँ:

• साफ़, सरल डेटासेट्स के लिए COUNTA() से शुरू करें। यह तेज़ और सीधा है।

• यदि आगे शर्तें जोड़नी पड़ सकती हैं, तो COUNTIF(range,"<>") का उपयोग करें। यह COUNTIFS() से स्वाभाविक रूप से जुड़ता है।

• काउंट पर भरोसा करने से पहले फ़ॉर्मूला ब्लैंक्स का परीक्षण करें। कुछ "खाली" सेल्स को =LEN(cell) से स्पॉट-चेक करें। यदि 0 के अलावा कुछ मिलता है, तो SUMPRODUCT()+LEN() तरीका अपनाएँ।

• फ़िल्टर्ड डेटा के लिए SUBTOTAL(3,range) का उपयोग करें। यह एकमात्र काउंटिंग फ़ंक्शन है जो फ़िल्टर्स का सम्मान करता है।

• इम्पोर्ट किया डेटा पहले साफ़ करें। वेबसाइटों या अन्य स्रोतों से आए डेटा पर TRIM() चलाएँ और CHAR(160) की जाँच करें।

• बढ़ते डेटासेट्स के लिए Excel टेबल्स का उपयोग करें। स्ट्रक्चर्ड रेफरेंसेज़ रो जोड़ने पर अपने आप अपडेट होते हैं।

• जटिल फ़ॉर्मूलों का दस्तावेज़ीकरण करें। यदि आप SUMPRODUCT(--(LEN(TRIM(range))>0)) इस्तेमाल कर रहे हैं, तो टिप्पणी जोड़ें कि क्यों। भविष्य का आप आपको धन्यवाद देगा।

निष्कर्ष

Excel नॉन-ब्लैंक सेल्स गिनने के कई तरीके देता है, और सही तरीका आपकी स्थिति पर निर्भर करता है। COUNTA() अधिकांश मामलों में काम करता है। COUNTIF() और COUNTIFS() आपको शर्तें जोड़ने देते हैं। SUBTOTAL() फ़िल्टर्स का सम्मान करता है। और SUMPRODUCT() के साथ LEN() तब बैकअप है जब फ़ॉर्मूले अदृश्य ब्लैंक्स बनाते हैं।

चाल फ़ॉर्मूले याद करना नहीं है। बात यह समझने की है कि Excel "ब्लैंक" से क्या समझता है। "" लौटाने वाला फ़ॉर्मूला खाली दिखता है पर खाली नहीं होता। स्पेस कैरेक्टर अदृश्य होता है पर गिना जाता है। ये फर्क समझ गए, तो गलत काउंट अब चौंकाएँगे नहीं।

यदि आप अपनी Excel स्किल्स और बढ़ाना चाहते हैं, तो हमारा Data Analysis in Excel कोर्स देखें। इसमें फ़ॉर्मूले, पिवट टेबल्स, और डेटा क्लीनिंग शामिल हैं—जो यहाँ कवर की गई बातों के साथ अच्छी तरह जाते हैं।