filter - COUNTIF with table filtering, or how does the SUMPRODUCT/SUBTOTAL/OFFSET combination actually work? -

निम्न एक्सेल तालिका को देखते हुए:

  + --- + -------- + ---- + ---- + | | ए | बी | सी | + --- + -------- + ---- + ---- + | 1 | फ़िल्टर | V1 | वी 2 | & Lt; - तालिका के शीर्ष लेख पंक्ति, फ़िल्टरिंग विकल्प के साथ + --- + -------- + ---- + ---- + | 2 | एफ 1 | एक्स | एक्स | | 3 | F2 | एक्स | वाई | | 4 | एफ 1 | एक्स | वाई | + + --- + -------- + ---- + ---- +  

मुझे x की संख्या की गणना करने की आवश्यकता है V1 और V2 में कॉलम:

  = COUNTIF (बी 2: सी 4, "x" )  

यह काम करता है और 4 देता है। अब, यदि मैं तालिका को फ़िल्टर करता हूं तो स्तंभ फ़िल्टर में केवल F1 मान होता है:

  + --- + -------- + ---- + ---- + | | ए | बी | सी | + --- + -------- + ---- + ---- + | 1 | फ़िल्टर | V1 | वी 2 | & Lt; - तालिका के शीर्ष लेख पंक्ति, फ़िल्टरिंग विकल्प के साथ + --- + -------- + ---- + ---- + | 2 | एफ 1 | एक्स | एक्स | | 4 | एफ 1 | एक्स | वाई | + --- + -------- + ---- + ---- +।  

पहला सूत्र अभी भी 4 देता है मैं पहले से ही पाया कि यह कैसे सुधारना है जिससे संभव फिल्टर को ध्यान में रखा जा सकता है (यह समाधान आसानी से इंटरनेट पर पाया जा सकता है):

  = SUMPRODUCT ((बी 2: सी 4 = "एक्स") * उपखंड (3, OFFSET (बी 2: सी 4, आरओयू (बी 2: सी 4) - MIN (आरओओ (बी 2: सी 4)), 0, 1, 1)))  

यह दूसरे मामले के लिए 3 देता है, जैसा कि अपेक्षित है प्रश्न यह है कि यह कैसे काम करता है? क्या कोई मुझे दूसरे फॉर्मूला के बारे में विस्तृत जानकारी देने में सक्षम है?

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

= SUMPRODUCT ((B2: C4 = "x"))

एक सरणी को लेता है {TRUE, TRUE; TRUE, FALSE ;), (बी 2: सी 4 = "x") ।

= SUMPRODUCT ((बी 2: सी 4 = "x" पर निर्भर करता है; TRUE, FALSE} ) * 1)

बुलियन मूल्यों को सांख्यिक संदर्भ में {1,1; 1,0; 1,0} के रूप में प्राप्त होगा। अब SUMPRODUCT इस सरणी को SUM और 4 मिलेगा।

= SUBTOTAL (3, बी 2: सी 4) केवल तभी गणना करेगा अगर कोई भी कक्ष B2: C4 अदृश्य नहीं है क्योंकि इसे फ़िल्टर्ड किया गया है। उदाहरण के लिए, यदि F2 को फ़िल्टर्ड किया गया है, तो यह 6 हो जाता है, उदाहरण के लिए, अगर 4। ), 0, 1, 1)} सरणी संदर्भ में प्रयुक्त होता है {= OFFSET (बी 2: सी 4, {2; 3; 4} - 2, 0, 1, 1}} = {= OFFSET (बी 2: सी 4, {0; 1; 2}, 0, 1, 1)} जो B2: C4 को ले जाता है {0; 1; 2} नीचे की ओर, कभी भी 0 कॉलम साइडवेयर, कभी ऊंचाई 1 और चौड़ाई 1 में जिसके परिणामस्वरूप {b2; b3; b4}

तो हमारे पास है {B2; B3; B4} के साथ

{= SUBTOTAL (3, {B2; बी 3; बी 4}) फ़िल्टर्ड नहीं किया जाता है ..

तो SUBTOTAL का नतीजा है जब F2 (B3) को फ़िल्टर किया जाता है: {1; 0; 1} ।

SUMPRODUCT और आउट फ़िल्टर्ड पंक्ति 3 में हमारे पास है:

= SUMPRODUCT ((B2: C4 = "x") * {1; 0; 1})

जो {TRUE, TRUE; TRUE, FALSE; TRUE, FALSE} * {1; 0; 1} = {1,1; 0,0; 1.0 } जो कि 3 से संबंधित है।

---

इसे सरल बनाने के लिए मैं

= SUMPRODUCT (B 2: सी 4 = "एक्स") * उपखंड (3, अप्रत्यक्ष ("ए" और आरओओ (2: 4))))

जो कि अप्रत्यक्ष परिणाम {A2; A3; A4} और, क्योंकि "ए" एक निश्चित टेक्स्ट स्ट्रिंग है, आपको सूत्र बदलने की ज़रूरत है यदि आप स्तंभ ए से पहले कॉलम डालेंगे।

यह OFFSET संस्करण के साथ जरूरी नहीं है क्योंकि सभी तर्क सेल संदर्भ हैं जो स्वचालित रूप से अपडेट होते हैं जब कोई कॉलम डाला जाएगा।