एक्सेल में COUNTIF और INDIRECT के साथ एक डायनामिक रेंज का उपयोग कैसे करें

2024 लेखक: Abigail Brown | [email protected]. अंतिम बार संशोधित: 2023-12-17 06:48

क्या पता

• अप्रत्यक्ष फ़ंक्शन सूत्र को संपादित किए बिना सूत्र में सेल संदर्भों की श्रेणी को बदल देता है।
• निर्दिष्ट मानदंडों को पूरा करने वाले कक्षों की एक गतिशील श्रेणी बनाने के लिए COUNTIF के तर्क के रूप में INDIRECT का उपयोग करें।
• मानदंड INDIRECT फ़ंक्शन द्वारा स्थापित किए जाते हैं, और केवल उन कक्षों की गणना की जाती है जो मानदंडों को पूरा करते हैं।

यह आलेख बताता है कि सूत्र में उपयोग किए गए सेल संदर्भों की श्रेणी को बदलने के लिए एक्सेल फ़ार्मुलों में अप्रत्यक्ष फ़ंक्शन का उपयोग कैसे करें, बिना सूत्र को संपादित किए। यह सुनिश्चित करता है कि आपकी स्प्रैडशीट में परिवर्तन होने पर भी उन्हीं कक्षों का उपयोग किया जाए।सूचना एक्सेल 2019, एक्सेल 2016, एक्सेल 2013, एक्सेल 2010, मैक के लिए एक्सेल और एक्सेल ऑनलाइन पर लागू होती है।

COUNTIF के साथ एक डायनामिक रेंज का उपयोग करें - अप्रत्यक्ष फॉर्मूला

अप्रत्यक्ष फ़ंक्शन का उपयोग कई फ़ंक्शन के साथ किया जा सकता है जो सेल संदर्भ को तर्क के रूप में स्वीकार करते हैं, जैसे SUM और COUNTIF फ़ंक्शन।

COUNTIF के लिए तर्क के रूप में INDIRECT का उपयोग करना सेल संदर्भों की एक गतिशील श्रेणी बनाता है जिसे फ़ंक्शन द्वारा गिना जा सकता है यदि सेल मान एक मानदंड को पूरा करते हैं। यह टेक्स्ट डेटा, जिसे कभी-कभी टेक्स्ट स्ट्रिंग के रूप में संदर्भित किया जाता है, को सेल संदर्भ में बदलकर ऐसा करता है।

यह उदाहरण ऊपर की छवि में दिखाए गए डेटा पर आधारित है। ट्यूटोरियल में बनाया गया COUNTIF - INDIRECT फॉर्मूला है:

=COUNTIF(अप्रत्यक्ष(E1&":"&E2), ">10")

इस सूत्र में, अप्रत्यक्ष फ़ंक्शन के तर्क में शामिल हैं:

• सेल E1 और E2 को संदर्भित करता है, जिसमें टेक्स्ट डेटा D1 और D6 शामिल हैं।
• रेंज ऑपरेटर, कोलन (:) दोहरे उद्धरण चिह्नों से घिरा हुआ है (" ") जो कोलन को टेक्स्ट में बदल देता है स्ट्रिंग।
• दो एम्परसेंड (&) जिनका उपयोग कोलन को जोड़ने या जोड़ने के लिए किया जाता है, सेल संदर्भ E1 और E2 के साथ।

परिणाम यह है कि INDIRECT टेक्स्ट स्ट्रिंग D1:D6 को एक सेल संदर्भ में परिवर्तित करता है और इसे COUNTIF फ़ंक्शन के साथ पास करता है, यदि संदर्भित सेल 10 से बड़े हैं।

अप्रत्यक्ष फ़ंक्शन किसी भी टेक्स्ट इनपुट को स्वीकार करता है। ये वर्कशीट में सेल हो सकते हैं जिनमें टेक्स्ट या टेक्स्ट सेल संदर्भ होते हैं जो सीधे फ़ंक्शन में दर्ज किए जाते हैं।

फॉर्मूला की रेंज को गतिशील रूप से बदलें

याद रखें, लक्ष्य एक डायनामिक रेंज वाला फॉर्मूला बनाना है। सूत्र को संपादित किए बिना एक गतिशील श्रेणी को बदला जा सकता है।

सेल E1 और E2 में स्थित टेक्स्ट डेटा को D1 और D6 से D3 और D7 में बदलकर, फ़ंक्शन द्वारा कुल रेंज को आसानी से D1:D6 से D3:D7 में बदला जा सकता है। यह कक्ष G1 में सूत्र को सीधे संपादित करने की आवश्यकता को समाप्त करता है।

इस उदाहरण में COUNTIF फ़ंक्शन केवल संख्याओं वाले कक्षों की गणना करता है यदि वे 10 से बड़े हैं। भले ही D1:D6 की श्रेणी में पांच कक्षों में से चार में डेटा होता है, केवल तीन कक्षों में संख्याएं होती हैं। वे कक्ष जो रिक्त हैं या जिनमें टेक्स्ट डेटा है, उन्हें फ़ंक्शन द्वारा अनदेखा कर दिया जाता है।

COUNTIF के साथ टेक्स्ट गिनना

COUNTIF फ़ंक्शन संख्यात्मक डेटा गिनने तक सीमित नहीं है। यह उन कक्षों को भी गिनता है जिनमें टेक्स्ट होता है, यह जांच कर कि क्या वे एक निश्चित टेक्स्ट से मेल खाते हैं।

ऐसा करने के लिए, सेल G2 में निम्न सूत्र दर्ज किया गया है:

=COUNTIF(अप्रत्यक्ष(E1&":"&E2), "दो")

इस फॉर्मूले में, INDIRECT फंक्शन सेल B1 से B6 को रेफर करता है। COUNTIF फ़ंक्शन उन कक्षों की संख्या का योग करता है जिनमें टेक्स्ट मान दो है।

इस मामले में, परिणाम 1 है।

COUNTA, COUNTBLANK, और INDIRECT

दो अन्य एक्सेल काउंट फ़ंक्शन COUNTA हैं, जो केवल रिक्त या खाली कक्षों को अनदेखा करते हुए किसी भी प्रकार के डेटा वाले कक्षों की गणना करता है, और COUNTBLANK, जो किसी श्रेणी में केवल रिक्त या रिक्त कक्षों की गणना करता है।

चूंकि इन दोनों कार्यों में COUNTIF फ़ंक्शन के समान सिंटैक्स है, इसलिए उन्हें निम्न सूत्र बनाने के लिए उपरोक्त उदाहरण में INDIRECT से प्रतिस्थापित किया जा सकता है:

=COUNTA(अप्रत्यक्ष(E1&":"&E2))
=COUNTBLANK(अप्रत्यक्ष(E1&":"&E2)
डी1:डी6 श्रेणी के लिए, COUNTA 4 का उत्तर देता है, क्योंकि पांच में से चार सेल में डेटा होता है। COUNTBLANK 1 का उत्तर देता है क्योंकि इस श्रेणी में केवल एक रिक्त कक्ष है।

अप्रत्यक्ष फ़ंक्शन का उपयोग क्यों करें?
इन सभी फ़ार्मुलों में INDIRECT फ़ंक्शन का उपयोग करने का लाभ यह है कि नए सेल को रेंज में कहीं भी डाला जा सकता है।
विभिन्न कार्यों के अंदर रेंज गतिशील रूप से बदल जाती है, और परिणाम तदनुसार अपडेट होते हैं।





अप्रत्यक्ष फ़ंक्शन के बिना, प्रत्येक फ़ंक्शन को संपादित करने की आवश्यकता होगी, जिसमें सभी 7 सेल शामिल होंगे, जिसमें नया भी शामिल है।
अप्रत्यक्ष फ़ंक्शन के लाभ यह हैं कि टेक्स्ट मानों को सेल संदर्भ के रूप में सम्मिलित किया जा सकता है और जब भी आपकी स्प्रेडशीट बदलती है तो यह गतिशील रूप से श्रेणियों को अपडेट करता है।
यह समग्र स्प्रैडशीट रखरखाव को बहुत आसान बनाता है, विशेष रूप से बहुत बड़ी स्प्रैडशीट के लिए।