उदाहरण कार्यपुस्तिका डाउनलोड करें
यह ट्यूटोरियल दर्शाता है कि कैसे उपयोग करना है एक्सेल सबटोटल फंक्शन एक्सेल में सारांश आँकड़ों की गणना करने के लिए।
सबटोटल फंक्शन अवलोकन
सबटोटल फ़ंक्शन डेटा की एक श्रृंखला के लिए एक सारांश आंकड़े की गणना करता है। उपलब्ध आंकड़ों में औसत, मानक विचलन, गणना, न्यूनतम और अधिकतम शामिल हैं, लेकिन इन्हीं तक सीमित नहीं हैं। फ़ंक्शन इनपुट अनुभाग में नीचे पूरी सूची देखें:
सबटोटल एक्सेल वर्कशीट फ़ंक्शन का उपयोग करने के लिए, एक सेल चुनें और टाइप करें:
(ध्यान दें कि सूत्र इनपुट कैसे दिखाई देते हैं)
सबटोटल फ़ंक्शन सिंटैक्स और इनपुट:
| 1 | = सबटोटल (function_num, REF1) |
function_num - एक संख्या जो दर्शाती है कि कौन सा ऑपरेशन करना है।
आरईएफ1 - गणना करने के लिए डेटा युक्त रेंज या संदर्भ।
सबटोटल फ़ंक्शन क्या है?
SUBTOTAL स्प्रेडशीट के भीतर अद्वितीय कार्यों में से एक है क्योंकि यह छिपी हुई कोशिकाओं और गैर-छिपी कोशिकाओं के बीच अंतर बता सकता है। फ़िल्टर की गई श्रेणियों के साथ काम करते समय या जब आपको विभिन्न उपयोगकर्ता चयनों के आधार पर गणना सेट करने की आवश्यकता होती है, तो यह काफी मददगार साबित हो सकता है। चूंकि यह अपनी गणनाओं से अन्य SUBTOTAL कार्यों को अनदेखा करना भी जानता है, इसलिए हम इसे डबल-काउंटिंग के डर के बिना बड़े सारांशित डेटा के भीतर भी उपयोग कर सकते हैं।
सबटोटल के साथ मूल सारांश
मान लें कि आपके पास क्रमबद्ध उत्पाद बिक्री की एक तालिका थी, और आप प्रत्येक उत्पाद के लिए योग बनाना चाहते थे, साथ ही कुल योग बनाना चाहते थे। आप PivotTable का उपयोग कर सकते हैं, या आप कुछ सूत्र सम्मिलित कर सकते हैं। इस लेआउट पर विचार करें:
मैंने कोशिकाओं B5 और B8 में कुछ SUBTOTAL फ़ंक्शन रखे हैं जो इस तरह दिखते हैं
| 1 | = सबटोटल (९, बी२:बी४) |
वाक्य रचना से, आप पहले तर्क के लिए विभिन्न संख्याओं का उपयोग कर सकते हैं। हमारे विशिष्ट मामले में, हम यह इंगित करने के लिए 9 का उपयोग कर रहे हैं कि हम एक योग करना चाहते हैं।
आइए सेल B9 पर ध्यान दें। इसमें यह सूत्र है, जिसमें संपूर्ण स्तंभ B डेटा श्रेणी शामिल है, लेकिन इसमें अन्य उप-योग शामिल नहीं हैं।
| 1 | = सबटोटल (९, बी२:बी८) |
नोट: यदि आप सभी सारांश फ़ार्मुलों को स्वयं नहीं लिखना चाहते हैं, तो आप डेटा रिबन पर जा सकते हैं और आउटलाइन - सबटोटल विज़ार्ड का उपयोग कर सकते हैं। यह स्वचालित रूप से पंक्तियों को सम्मिलित करेगा और आपके लिए सूत्र रखेगा।
पहले तर्कों में अंतर
पहले उदाहरण में, हमने यह दर्शाने के लिए 9 का उपयोग किया था कि हम एक योग करना चाहते हैं। 9 और 109 का उपयोग करने के बीच का अंतर यह होगा कि हम कैसे चाहते हैं कि फ़ंक्शन छिपी हुई पंक्तियों को संभाले। यदि आप 1XX पदनामों का उपयोग करते हैं, तो फ़ंक्शन में उन पंक्तियों को शामिल नहीं किया जाएगा जिन्हें मैन्युअल रूप से छिपाया या फ़िल्टर किया गया है।
यहाँ पहले से हमारी तालिका है। हमने फ़ंक्शन को स्थानांतरित कर दिया है ताकि हम 9 और 109 तर्कों के बीच अंतर देख सकें। सभी दृश्यमान के साथ, परिणाम समान हैं।
यदि हम कॉलम बी में 6 के मान को फ़िल्टर करने के लिए फ़िल्टर लागू करते हैं, तो दो कार्य समान रहते हैं।
यदि हम मैन्युअल रूप से पंक्तियों को छिपाते हैं, तो हमें अंतर दिखाई देता है। 109 फ़ंक्शन छिपी हुई पंक्ति को अनदेखा करने में सक्षम था जबकि 9 फ़ंक्शन नहीं था।
SUBTOTAL . के साथ गणित संचालन बदलें
आप कभी-कभी अपने उपयोगकर्ता को यह बदलने की क्षमता देने में सक्षम हो सकते हैं कि किस प्रकार की गणना की जाती है। उदाहरण के लिए, क्या वे योग या औसत प्राप्त करना चाहते हैं। चूंकि SUBTOTAL एक तर्क संख्या द्वारा गणित के संचालन को नियंत्रित करता है, आप इसे एक सूत्र में लिख सकते हैं। यहाँ हमारा सेटअप है:
हमने D2 में एक ड्रॉपडाउन बनाया है जहां उपयोगकर्ता "योग" या "औसत" का चयन कर सकता है। E2 में सूत्र है:
| 1 | = सबटोटल (IF(D2="औसत",1,IF(D2="Sum",9)),B2:B4) |
यहां, IF फ़ंक्शन यह निर्धारित करने जा रहा है कि SUBTOTAL को कौन सा संख्यात्मक तर्क देना है। यदि A5 "औसत" है, तो यह 1 का उत्पादन करेगा और SUBTOTAL B2: B4 का औसत देगा। या, यदि A5 "योग" के बराबर है, तो IF एक 9 आउटपुट करता है, और हमें एक अलग परिणाम मिलता है।
आप लुकअप तालिका का उपयोग करके और भी अधिक प्रकार के संचालनों को सूचीबद्ध करने के लिए इस क्षमता का विस्तार कर सकते हैं जिन्हें आप करना चाहते हैं। आपकी लुकअप तालिका इस तरह दिख सकती है
फिर, आप E2 के सूत्र को be . में बदल सकते हैं
| 1 | = सबटोटल (वीलुकअप (ए५, लुकअपटेबल, २,०), बी२:बी४) |
SUBTOTAL के साथ सशर्त सूत्र
जबकि SUBTOTAL के पास कई ऑपरेशन हैं जो वह कर सकता है, यह अपने आप मापदंड की जाँच नहीं कर सकता है। हालाँकि, हम इस ऑपरेशन को करने के लिए एक सहायक कॉलम में इसका उपयोग कर सकते हैं। जब आपके पास डेटा का एक कॉलम होगा जिसे आप जानते हैं हमेशा इसमें डेटा का एक टुकड़ा है, आप छिपी हुई पंक्तियों का पता लगाने के लिए SUBTOTALs क्षमता का उपयोग कर सकते हैं।
यहां वह तालिका है जिसके साथ हम इस उदाहरण में काम करेंगे। आखिरकार, हम "Apple" के लिए मानों का योग करने में सक्षम होना चाहते हैं, लेकिन साथ ही उपयोगकर्ता को मात्रा कॉलम को फ़िल्टर करने देना चाहते हैं।
सबसे पहले, एक हेल्पर कॉलम बनाएं जिसमें SUBTOTAL फंक्शन होगा। C2 में, सूत्र है:
| 1 | = सबटोटल(103, ए2) |
याद रखें कि 103 का मतलब है कि हम एक COUNTA करना चाहते हैं। मैं COUNTA का उपयोग करने की सलाह देता हूं क्योंकि तब आप अपने A2 के संदर्भ कक्ष को भर सकते हैं दोनों में से एक संख्या या पाठ। अब आपके पास इस तरह दिखने वाली एक टेबल होगी:
यह पहली बार में मददगार नहीं लगता क्योंकि सभी मान सिर्फ 1 हैं। हालाँकि, यदि हम पंक्ति 3 को छिपाते हैं, तो C3 में "1" 0 में बदल जाएगा क्योंकि यह एक छिपी हुई पंक्ति की ओर इशारा कर रहा है। जबकि विशिष्ट छिपे हुए सेल के मान को दिखाने वाली छवि होना असंभव है, आप इसे पंक्ति को छुपाकर और फिर जाँच करने के लिए इस तरह एक मूल सूत्र लिखकर देख सकते हैं।
| 1 | =सी3 |
अब जब हमारे पास एक कॉलम है जो छिपा हुआ है या नहीं, इसके आधार पर मूल्य में परिवर्तन होगा, हम अंतिम समीकरण लिखने के लिए तैयार हैं। हमारा SUMIFS इस तरह दिखेगा
इस सूत्र में, हम केवल कॉलम B के मानों का योग करने जा रहे हैं, जब कॉलम A "Apple" के बराबर होगा, तथा कॉलम सी में मान 1 है (उर्फ, पंक्ति छिपी नहीं है)। मान लें कि हमारा उपयोगकर्ता 600 को फ़िल्टर करना चाहता है, क्योंकि यह असामान्य रूप से उच्च लगता है। हम देख सकते हैं कि हमारा सूत्र सही परिणाम देता है।
इस क्षमता के साथ, आप किसी COUNTIFS, SUMIFS, या यहां तक कि किसी SUMPRODUCT पर चेक लागू कर सकते हैं। आप अपने उपयोगकर्ताओं को कुछ टेबल स्लाइसर को नियंत्रित करने की क्षमता में जोड़ते हैं, और आप एक शानदार डैशबोर्ड बनाने के लिए तैयार हैं।
Google पत्रक में SUBTOTAL
सबटोटल फ़ंक्शन Google शीट्स में ठीक उसी तरह काम करता है जैसे एक्सेल में:
VBA . में सबटोटल उदाहरण
आप वीबीए में सबटोटल फ़ंक्शन का भी उपयोग कर सकते हैं। प्रकार:application.worksheetfunction.subtotal(function_num,reh1)
निम्नलिखित VBA कथनों को क्रियान्वित करना
| 1234567891011121314151617 | रेंज ("सी 7") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (1, रेंज ("सी 2: सी 5"))रेंज ("सी 8") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (2, रेंज ("सी 2: सी 5"))रेंज ("सी 9") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (4, रेंज ("सी 2: सी 5"))रेंज ("सी १०") = एप्लीकेशन। वर्कशीट फंक्शन। सबटोटल (५, रेंज ("सी २: सी ५"))रेंज ("सी 11") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (9, रेंज ("सी 2: सीई 5"))रेंज ("डी 7") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (1, रेंज ("डी 2: डी 5"))रेंज ("डी 8") = एप्लीकेशन। वर्कशीटफंक्शन। सबटोटल (2, रेंज ("डी 2: डी 5"))रेंज ("डी 9") = एप्लीकेशन। वर्कशीटफंक्शन। सबटोटल (4, रेंज ("डी 2: डी 5"))रेंज ("डी 10") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (5, रेंज ("डी 2: डी 5"))रेंज ("डी 11") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (9, रेंज ("डी 2: डी 5"))रेंज ("ई 7") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (1, रेंज ("ई 2: ई 5"))रेंज ("ई 8") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (2, रेंज ("ई 2: ई 5"))रेंज ("ई 9") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (4, रेंज ("ई 2: ई 5"))रेंज ("ई १०") = एप्लीकेशन। वर्कशीट फंक्शन। सबटोटल (५, रेंज ("ई २: ई ५"))रेंज ("ई 11") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सबटोटल (9, रेंज ("ई 2: ई 5")) |
निम्नलिखित परिणाम देगा
फ़ंक्शन तर्कों (function_num, आदि) के लिए, आप या तो उन्हें सीधे फ़ंक्शन में दर्ज कर सकते हैं, या इसके बजाय उपयोग करने के लिए चर परिभाषित कर सकते हैं।
एक्सेल में सभी फंक्शन की सूची पर लौटें
