उदाहरण कार्यपुस्तिका डाउनलोड करें
यह ट्यूटोरियल प्रदर्शित करेगा कि "सबटोटल if" की गणना कैसे करें, मानदंड के साथ केवल दृश्यमान पंक्तियों की गणना करें.
सबटोटल फंक्शन
SUBTOTAL फ़ंक्शन डेटा की एक श्रेणी (गिनती, योग, औसत, आदि) पर विभिन्न गणना कर सकता है। सबसे महत्वपूर्ण बात यह है कि इसका उपयोग केवल दृश्यमान (फ़िल्टर की गई) पंक्तियों पर गणना करने के लिए किया जा सकता है। इस उदाहरण में, हम SUBTOTAL function_num तर्क को 3 पर सेट करके (COUNTA) दृश्यमान पंक्तियों को गिनने के लिए फ़ंक्शन का उपयोग करेंगे (संभावित कार्यों की एक पूरी सूची यहां पाई जा सकती है।)
= सबटोटल(3,$डी$2:$डी$14)
ध्यान दें कि जब हम मैन्युअल रूप से पंक्तियों को फ़िल्टर करते हैं तो परिणाम कैसे बदलते हैं।
सबटोटल IF
"सबटोटल इफ़" बनाने के लिए, हम एक सरणी सूत्र में SUMPRODUCT, SUBTOTAL, OFFSET, ROW और MIN के संयोजन का उपयोग करेंगे। इस संयोजन का उपयोग करके, हम अनिवार्य रूप से एक सामान्य "SUBTOTAL IF" फ़ंक्शन बना सकते हैं। आइए एक उदाहरण के माध्यम से चलते हैं।
हमारे पास प्रत्येक कार्यक्रम के लिए सदस्यों और उनकी उपस्थिति की स्थिति की एक सूची है:
माना जाता है कि हमें उन सदस्यों की संख्या गिनने के लिए कहा जाता है जो गतिशील रूप से किसी घटना में भाग लेते हैं क्योंकि हम सूची को मैन्युअल रूप से फ़िल्टर करते हैं:
इसे पूरा करने के लिए, हम इस सूत्र का उपयोग कर सकते हैं:
= SUMPRODUCT ((=) * (सबटोटल (3, ऑफसेट (, पंक्ति () - मिन (पंक्ति ()), 0)))))
=SUMPRODUCT((D2:D14="उपस्थित")*(SUBTOTAL(3,OFFSET(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0)))))
एक्सेल 2022 और इससे पहले के संस्करण का उपयोग करते समय, आपको दबाकर सरणी सूत्र दर्ज करना होगा CTRL + SHIFT + ENTER एक्सेल को यह बताने के लिए कि आप एक सरणी सूत्र दर्ज कर रहे हैं। जब सूत्र के चारों ओर घुंघराले कोष्ठक दिखाई देते हैं, तो आपको पता चल जाएगा कि सूत्र को सरणी सूत्र के रूप में ठीक से दर्ज किया गया था (ऊपर चित्र देखें)।
सूत्र कैसे काम करता है?
सूत्र SUMPRODUCT के अंदर दो सरणियों को गुणा करके काम करता है, जहाँ पहली सरणी हमारे मानदंड से संबंधित है और दूसरी सरणी केवल दृश्यमान पंक्तियों को फ़िल्टर करती है:
= SUMPRODUCT (*)
मानदंड सरणी
मानदंड सरणी हमारी मान श्रेणी में प्रत्येक पंक्ति का मूल्यांकन करती है ("इस उदाहरण में "उपस्थित" स्थिति) और इस तरह एक सरणी उत्पन्न करती है:
=(=)
=(D2:D14="उपस्थित")
आउटपुट:
{सच; झूठा; झूठा; सच; झूठा; तुर; तुर; तुर; झूठा; झूठा; सच; झूठा; सच}
ध्यान दें कि हमारे सूत्र में पहली सरणी में आउटपुट इस बात पर ध्यान नहीं देता है कि पंक्ति दिखाई दे रही है या नहीं, जहां हमारी दूसरी सरणी मदद के लिए आती है।
दृश्यता सरणी
हमारी सीमा में गैर-दृश्यमान पंक्तियों को बाहर करने के लिए SUBTOTAL का उपयोग करके, हम अपनी दृश्यता सरणी उत्पन्न कर सकते हैं। हालांकि, अकेले SUBTOTAL एकल मान लौटाएगा, जबकि SUMPRODUCT मानों की एक सरणी की अपेक्षा कर रहा है। इसे हल करने के लिए, हम एक बार में एक पंक्ति पास करने के लिए OFFSET का उपयोग करते हैं। इस तकनीक के लिए OFFSET को एक ऐसे सरणी को फीड करने की आवश्यकता होती है जिसमें एक समय में एक नंबर होता है। दूसरी सरणी इस तरह दिखती है:
= सबटोटल (3, ऑफसेट (, रो (), मिन (पंक्ति ()), 0))
= सबटोटल(3,ऑफसेट(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0))
आउटपुट:
{1;1;0;0;1;1}
दोनों को एक साथ सिलाई करना:
=SUMPRODUCT({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL IF एकाधिक मानदंडों के साथ
कई मानदंड जोड़ने के लिए, SUMPRODUCT के भीतर बस एक साथ कई और मानदंड जैसे:
= SUMPRODUCT((=)*(=)*(सबटोटल(3,ऑफसेट(,ROW()-MIN(ROW()),0))))
=SUMPRODUCT((E2:E14="उपस्थित")*(B2:B14=2019)*(SUBTOTAL(3,OFFSET(E2,ROW(E2:E14)-MIN(ROW(E2:E14)),0)) ))
सबटोटल IF Google पत्रक में
SUBTOTAL IF फ़ंक्शन Google शीट में ठीक उसी तरह काम करता है जैसे Excel में:
