सारांश
COUNTIFS (या COUNTIF) को एक वैरिएबल रेंज से कॉन्फ़िगर करने के लिए, आप OFFSET फ़ंक्शन का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, B11 का सूत्र है:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
यह सूत्र गैर-रिक्त कक्षों को एक श्रेणी में गिनता है जो B5 से शुरू होता है और उस सेल के ऊपर 2 पंक्तियों को समाप्त करता है जहां सूत्र रहता है। दिखाए गए अनुसार डेटा में अंतिम प्रविष्टि के नीचे 2 पंक्तियों को कॉपी और पेस्ट किया गया है।
स्पष्टीकरण
दिखाए गए उदाहरण में, B11 का सूत्र है:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
अंदर से बाहर की ओर काम करते हुए, एक चर रेंज स्थापित करने का काम यहां OFFSET फ़ंक्शन द्वारा किया जाता है:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET के पांच तर्क हैं और इसे इस तरह कॉन्फ़िगर किया गया है:
- संदर्भ = B $ 5, सेल B5 से शुरू करें, पंक्ति लॉक
- पंक्तियाँ = 0, सेल शुरू होने से शून्य पंक्तियों की भरपाई करें
- cols = 0, शून्य कॉलम शुरू सेल
- ऊँचाई = ROW () - ROW (B $ 5) -1 = 5 पंक्तियाँ ऊँची
- चौड़ाई = 1 कॉलम चौड़ा
पंक्तियों में श्रेणी की ऊँचाई का पता लगाने के लिए, हम इस तरह ROW फ़ंक्शन का उपयोग करते हैं:
ROW()-ROW(B$5)-1 // work out height
चूंकि ROW () "वर्तमान" सेल की पंक्ति संख्या देता है (अर्थात वह सूत्र जो कक्ष में रहता है), हम इसे इस प्रकार सरल कर सकते हैं:
=ROW()-ROW(B$5)-1 =11-5-1 =5
उपरोक्त कॉन्फ़िगरेशन के साथ, OFFSET ने सीमा B5: B9 को सीधे COUNTIFS में लौटा दिया:
=COUNTIFS(B5:B9,"") // returns 4
उपरोक्त सूत्र में B $ 5 के संदर्भ को देखें, एक मिश्रित संदर्भ है, जिसमें स्तंभ सापेक्ष और पंक्ति लॉक है। इससे सूत्र को दूसरे कॉलम में कॉपी किया जा सकता है और फिर भी काम किया जा सकता है। उदाहरण के लिए, एक बार C12 में कॉपी किया गया, सूत्र है:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
नोट: OFFSET एक अस्थिर कार्य है और बड़े या जटिल कार्यपत्रकों में प्रदर्शन समस्याओं का कारण बन सकता है।
संकेत और पता के साथ
एक और तरीका यह है कि INDIRECT और ADDRESS फ़ंक्शन के आधार पर किसी सूत्र का उपयोग किया जाए। इस मामले में, हम एक सीमा को पाठ के रूप में इकट्ठा करते हैं, फिर संदर्भ के रूप में पाठ का मूल्यांकन करने के लिए संकेत का उपयोग करते हैं। बी 11 में सूत्र होगा:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
ADDRESS फ़ंक्शन का उपयोग इस तरह की सीमा बनाने के लिए किया जाता है:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
ADDRESS के पहले उदाहरण में, हम पंक्ति संख्या को हार्डकोडेड मान 5 के रूप में आपूर्ति करते हैं, और कॉलम नंबर के साथ कॉलम नंबर प्रदान करते हैं:
=ADDRESS(5,COLUMN()) // returns "$B$5"
दूसरे उदाहरण में, हम "वर्तमान" पंक्ति संख्या माइनस 2 और COLUMN फ़ंक्शन के साथ वर्तमान कॉलम की आपूर्ति करते हैं:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
इन दो मूल्यों को एक साथ जोड़ने के बाद, हमारे पास है:
"$B$5:$B$9" // as text
ध्यान दें कि यह एक टेक्स्ट स्ट्रिंग है। एक वैध संदर्भ में बदलने के लिए, हमें उपयोग करने की आवश्यकता है:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
अंत में, बी 11 में सूत्र बन जाता है:
=COUNTIFS($B$5:$B$9,"") // returns 4
नोट: संकेत एक अस्थिर कार्य है और बड़े या जटिल कार्यपत्रकों में प्रदर्शन समस्याओं का कारण बन सकता है।
