Excel में SUBTOTAL() फ़ॉर्मूला: संपूर्ण कैलकुलेशन गाइड

Excel उपयोगकर्ताओं को अक्सर ऐसे लचीले तरीके चाहिए होते हैं जो फ़िल्टर या ग्रुपिंग के अनुसार डेटा का सार प्रस्तुत कर सकें। पारंपरिक फ़ंक्शनों जैसे SUM(), AVERAGE(), या COUNT() किसी रेंज की सभी कोशिकाओं के आधार पर परिणाम निकालते हैं, चाहे कुछ पंक्तियाँ छिपी हों या फ़िल्टर की गई हों। SUBTOTAL() फ़ंक्शन एक डायनेमिक विकल्प देता है जो आपकी वर्कशीट में वर्तमान में जो दिख रहा है उसके अनुसार स्वचालित रूप से अपनी गणना को समायोजित करता है।

यदि आप Excel में नए हैं और SUBTOTAL() जैसे उन्नत फ़ंक्शनों पर जाने से पहले एक मजबूत नींव बनाना चाहते हैं, तो हमारा Introduction to Excel कोर्स वर्कशीट नेविगेशन, बेसिक फ़ॉर्मूले और डेटा फ़ॉर्मैटिंग जैसी आवश्यक स्किल्स कवर करता है, जो आपको और उन्नत Excel कार्य के लिए तैयार करेगा।

Excel में SUBTOTAL() फ़ंक्शन क्या है?

SUBTOTAL() फ़ंक्शन किसी डेटा सेट पर जोड़, औसत, गिनती और अन्य सांख्यिकीय क्रियाओं जैसे समग्र मानों की गणना करता है। इसे मानक Excel फ़ंक्शनों से अलग बनाता है इसका डायनेमिक तरीका—जो दिखाई दे रहा है या फ़िल्टर किया गया है, उसके आधार पर परिणाम बदलता है। यह फ़ंक्शन आपके द्वारा दिए गए फ़ंक्शन नंबर के आधार पर, मैन्युअल रूप से छिपाई गई पंक्तियों और फ़िल्टर से छिपी पंक्तियों—दोनों—को नज़रअंदाज़ कर सकता है।

जब आप अपने डेटा पर फ़िल्टर लगाते हैं या कुछ पंक्तियाँ मैन्युअल रूप से छिपाते हैं, तो SUBTOTAL() स्वचालित रूप से अपनी गणना को समायोजित करके केवल दृश्यमान कोशिकाओं को शामिल करता है। यह व्यवहार फ़िल्टर किए गए डेटा सेट में समरी पंक्तियाँ बनाने, इंटरैक्टिव डैशबोर्ड तैयार करने, या उपयोगकर्ता चयन के आधार पर अपडेट होने वाली रिपोर्ट बनाने के लिए इसे उत्कृष्ट विकल्प बनाता है।

यह फ़ंक्शन Excel के मानक फ़ंक्शनों जैसी ही सांख्यिकीय क्रियाओं का समर्थन करता है—जैसे जोड़, औसत, गिनती, अधिकतम, न्यूनतम, और कई अन्य—लेकिन इन्हें एक ही, संदर्भ-संवेदी फ़ंक्शन में समेट देता है जो आपके डेटा की वर्तमान स्थिति के अनुसार ढल जाता है।

सिंटैक्स और समर्थित क्रियाएँ

SUBTOTAL() फ़ंक्शन एक सरल सिंटैक्स संरचना का पालन करता है:

=SUBTOTAL(function_num, ref1, [ref2], ...)संचालित द्वारा

पहला पैरामीटर function_num गणना के प्रकार और छिपी पंक्तियों के साथ फ़ंक्शन के व्यवहार—दोनों—को निर्धारित करता है। शेष पैरामीटर (ref1, ref2, आदि) वे सेल रेंज हैं जिन्हें आप गणना में शामिल करना चाहते हैं। आप अधिकतम 254 अलग-अलग रेंज निर्दिष्ट कर सकते हैं।

फ़ंक्शन नंबर मैन्युअल रूप से छिपी पंक्तियों के साथ उनके व्यवहार के आधार पर दो अलग श्रेणियों में बँटे होते हैं:

फ़ंक्शन नंबर 1-11 अपनी गणना में मैन्युअल रूप से छिपी पंक्तियों की कोशिकाएँ शामिल करते हैं, जबकि 101-111 उन्हें बाहर कर देते हैं। हालाँकि, दोनों श्रेणियाँ फ़िल्टर से छिपी पंक्तियों को हमेशा नज़रअंदाज़ करती हैं।

उदाहरण के लिए, SUBTOTAL(9, A2:A10) A2:A10 का जोड़ मैन्युअल रूप से छिपी पंक्तियों सहित निकालता है, जबकि SUBTOTAL(109, A2:A10) जोड़ की गणना से मैन्युअल रूप से छिपी पंक्तियों को बाहर कर देता है। दोनों ही मामलों में, फ़िल्टर की गई पंक्तियाँ स्वतः परिणाम से बाहर रहती हैं।

SUBTOTAL() फ़ंक्शन का व्यावहारिक उदाहरण

आइए इलेक्ट्रॉनिक्स और फ़र्नीचर स्टोर के बिक्री डेटा के एक व्यावहारिक उदाहरण के साथ देखें कि SUBTOTAL() कैसे काम करता है।

बिना फ़िल्टर वाला डेटासेट। चित्र: लेखक द्वारा। 

हमारे नमूना डेटा में दो श्रेणियों में सात उत्पाद हैं—चार इलेक्ट्रॉनिक्स आइटम (Laptop, Phone, Tablet, Monitor) और तीन फ़र्नीचर आइटम (Desk, Chair, Lamp)। बिना किसी फ़िल्टर के, SUBTOTAL(109,C2:C8) और सामान्य SUM(C2:C8) दोनों ही फ़ॉर्मूले एक जैसा परिणाम दिखाते हैं: 3525 (कुल बिक्री)।

फ़िल्टर किया हुआ डेटासेट। चित्र: लेखक द्वारा। 

अब हमने केवल इलेक्ट्रॉनिक्स उत्पाद दिखाने के लिए फ़िल्टर लगाया है, जिससे तीन फ़र्नीचर पंक्तियाँ छिप गई हैं। हमारी गणना के परिणामों में मुख्य अंतर देखें:

• SUBTOTAL फ़ॉर्मूला 3000 दिखाता है—स्वतः केवल दिख रही इलेक्ट्रॉनिक्स पंक्तियों का जोड़ निकालते हुए
• साधारण SUM फ़ॉर्मूला अभी भी 3525 दिखाता है—छिपी हुई फ़र्नीचर आइटम्स को शामिल करते हुए

यह आमने-सामने की तुलना SUBTOTAL() की मुख्य विशेषता दिखाती है: यह फ़िल्टर और दृश्यता में बदलावों के अनुसार डायनेमिक रूप से प्रतिक्रिया करता है, जबकि SUM() जैसे पारंपरिक फ़ंक्शन स्थिर रहते हैं। जब आप डेटा फ़िल्टर करते हैं या पंक्तियाँ छिपाते हैं, तो SUBTOTAL() तुरंत केवल वर्तमान में दिखाई दे रही चीज़ों को दर्शाने के लिए पुनर्गणना करता है, जिससे यह उत्तरदायी डैशबोर्ड और इंटरैक्टिव रिपोर्ट के लिए आवश्यक बनता है।

छिपे और फ़िल्टर किए गए डेटा के साथ SUBTOTAL() कैसे काम करता है

SUBTOTAL() फ़ंक्शन विभिन्न प्रकार के छिपे डेटा को विशिष्ट तरीकों से संभालता है, इसलिए फ़ॉर्मूले बनाते समय इन व्यवहारों को समझना आवश्यक है।

फ़िल्टर की गई पंक्तियों के साथ SUBTOTAL()

जब आप अपने डेटासेट पर फ़िल्टर लागू करते हैं, तो आप चाहे कोई भी फ़ंक्शन नंबर इस्तेमाल करें, SUBTOTAL() हमेशा फ़िल्टर की गई पंक्तियों को अपनी गणना से बाहर रखता है। यह व्यवहार सभी फ़ंक्शन नंबरों (1-11 और 101-111 दोनों रेंज) में स्वतः और सुसंगत रहता है।

उदाहरण के लिए, यदि आपके पास जनवरी से दिसंबर तक की बिक्री है और आप केवल Q1 महीने (जनवरी, फ़रवरी, मार्च) दिखाने के लिए फ़िल्टर लगाते हैं, तो SUBTOTAL(9, B2:B13) केवल उन्हीं तीन दृश्यमान महीनों का जोड़ निकालेगा, Q2–Q4 के फ़िल्टर किए गए डेटा को नज़रअंदाज़ करते हुए।

मैन्युअल रूप से छिपी पंक्तियों के साथ SUBTOTAL()

मैन्युअल रूप से छिपाई गई पंक्तियाँ आपके फ़ंक्शन नंबर की पसंद के आधार पर अलग तरह से संभाली जाती हैं। जब आप पंक्ति नंबरों पर राइट-क्लिक करके "Hide" चुनते हैं, तो आप पंक्तियों को मैन्युअल रूप से छिपा रहे होते हैं।

फ़ंक्शन नंबर 1-11 का उपयोग करने पर ये मैन्युअल रूप से छिपी पंक्तियाँ गणना में शामिल होती हैं। फ़ंक्शन नंबर 101-111 का उपयोग करने पर ये बाहर कर दी जाती हैं। इससे आपको इस बात पर नियंत्रण मिलता है कि छिपा डेटा आपके परिणामों को प्रभावित करे या नहीं।

क्षैतिज और ऊर्ध्वाधर रेंज के साथ SUBTOTAL()

SUBTOTAL() फ़ंक्शन ऊर्ध्वाधर डेटा रेंज (कॉलम-आधारित डेटा) के साथ सबसे अच्छा काम करता है। यद्यपि यह क्षैतिज रेंज को संभाल सकता है, इसका हाइडिंग व्यवहार पंक्ति-आधारित कार्यों के लिए अनुकूलित है क्योंकि Excel के फ़िल्टरिंग और रो-हाइडिंग फ़ीचर ऊर्ध्वाधर दिशा में काम करते हैं।

जब आप समान रेंज के भीतर SUBTOTAL() फ़ॉर्मूलों को नेस्ट करते हैं, तो यह फ़ंक्शन स्वतः अन्य SUBTOTAL() परिणामों को स्किप कर देता है ताकि डबल-काउंटिंग न हो। यह विशेष रूप से ग्रुपों के भीतर पदानुक्रमित समरी या सबटोटल बनाते समय उपयोगी है, जिससे आपके कुल अलग-अलग ग्रुपिंग को बिना दोहराव के सटीक रूप से दर्शाएँ।

SUBTOTAL() फ़ंक्शन के उन्नत उपयोग

SUBTOTAL() के साथ डायनेमिक ड्रॉपडाउन समरी बनाना

आप SUBTOTAL() को IF() फ़ंक्शन के साथ मिलाकर इंटरैक्टिव समरी बना सकते हैं जो उपयोगकर्ता चयन के आधार पर बदलती हैं। इस तरीके में ड्रॉपडाउन सूची का उपयोग किया जाता है ताकि उपयोगकर्ता अलग-अलग गणना प्रकारों के बीच स्विच कर सकें।

डायनेमिक ड्रॉपडाउन समरी बनाने के लिए, पहले सेल D1 में "Sum", "Average", "Count", और "Max" जैसे विकल्पों के साथ एक ड्रॉपडाउन सेट करें।

फिर, उपयोगकर्ता के चयन के आधार पर गणना को डायनेमिक रूप से समायोजित करने के लिए किसी अन्य सेल (जैसे, D2) में निम्न फ़ॉर्मूला का उपयोग करें:

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))संचालित द्वारा

यह फ़ॉर्मूला ड्रॉपडाउन चयन का मूल्यांकन करता है और संबंधित SUBTOTAL() फ़ंक्शन लागू करता है। उपयोगकर्ता बिना फ़ॉर्मूले संपादित किए गणना प्रकार बदल सकते हैं, जिससे रिपोर्ट अधिक इंटरैक्टिव और उपयोगकर्ता-अनुकूल बनती हैं।

Excel टेबल्स और आउटलाइन में SUBTOTAL()

Excel Tables की Total Row सुविधा में SUBTOTAL() स्वतः उपयोग होता है। जब आप अपने डेटा रेंज को टेबल (Ctrl+T) में बदलते हैं और Total Row सक्षम करते हैं, तो Excel जोड़ की गणना के लिए डिफ़ॉल्ट रूप से SUBTOTAL(109, …) डालता है। यह Total Row जब भी पंक्तियाँ फ़िल्टर की जाती हैं, डायनेमिक रूप से अपडेट होती है, और तुरंत केवल दृश्यमान डेटा को दर्शाने के लिए परिणाम समायोजित करती है।

आप किसी भी Total Row सेल में ड्रॉपडाउन तीर पर क्लिक करके गणना प्रकार बदल सकते हैं और Count, Average, Max, या Min जैसे अलग विकल्प चुन सकते हैं। Excel SUBTOTAL() संरचना को बनाए रखते हुए फ़ंक्शन नंबर को स्वतः अपडेट करता है।

Outline सुविधा (Data > Subtotal) भी SUBTOTAL() फ़ॉर्मूलों पर निर्भर करती है। जब आप डेटा को समूहित करते हैं और सबटोटल सम्मिलित करते हैं, तो Excel प्रत्येक ग्रुप ब्रेक पर स्वतः SUBTOTAL() फ़ंक्शन रखता है। यह पदानुक्रमित समरी बनाता है जो तब भी सही प्रतिक्रिया देते हैं जब आप आउटलाइन स्तरों का विस्तार या संकुचन करते हैं, क्योंकि फ़ंक्शन नेस्टेड सबटोटल परिणामों को नज़रअंदाज़ करता है।

सामान्य गलतियाँ और सीमाएँ

SUBTOTAL() की सामान्य त्रुटियों और सीमाओं को समझना आपको गणना संबंधी गलतियों से बचने और अपनी डेटा विश्लेषण आवश्यकताओं के लिए सही तरीका चुनने में मदद करता है।

• अमान्य फ़ंक्शन नंबर पर #VALUE! त्रुटि: सबसे आम त्रुटि अमान्य फ़ंक्शन नंबर उपयोग करने पर होती है। SUBTOTAL() केवल 1-11 और 101-111 फ़ंक्शन नंबर स्वीकार करता है। 12, 50, या 200 जैसे नंबर #VALUE! त्रुटि लौटाते हैं। हमेशा सुनिश्चित करें कि आपका फ़ंक्शन नंबर समर्थित रेंज में किसी मान्य क्रिया से मेल खाता है।

• छिपे कॉलम बनाम छिपी पंक्तियों का व्यवहार: जहाँ SUBTOTAL() छिपी पंक्तियों पर आपके फ़ंक्शन नंबर की पसंद के अनुसार प्रतिक्रिया देता है, वहीं यह छिपे कॉलम के साथ वैसा व्यवहार नहीं करता। यह फ़ंक्शन छिपे कॉलम के डेटा को सभी गणनाओं में शामिल करता है, भले ही आप 1-11 या 101-111 फ़ंक्शन नंबरों का उपयोग करें। इस सीमा का अर्थ है कि जब कॉलम दृश्यता आपके विश्लेषण को प्रभावित करती है, तो आपको वैकल्पिक तरीकों की आवश्यकता होगी।

• 3D रेफ़रेंस समर्थित नहीं हैं: कई Excel फ़ंक्शनों के विपरीत, SUBTOTAL() कई वर्कशीट्स में फैली रेंज को संदर्भित नहीं कर सकता। SUBTOTAL(109, Sheet1:Sheet3!A1:A10) जैसी फ़ॉर्मूलाएँ त्रुटि देती हैं। एक समाधान के रूप में, आप पहले प्रत्येक शीट पर मानक फ़ंक्शनों जैसे SUM() से जोड़ या अन्य समग्र मान अलग-अलग निकाल सकते हैं, फिर एक समरी शीट पर इन मध्यवर्ती परिणामों को एकल SUBTOTAL से समेकित कर सकते हैं।

• त्रुटि मान गणनाओं में बने रहते हैं: SUBTOTAL() #N/A, #DIV/0!, या #VALUE! जैसे त्रुटि मानों वाली कोशिकाओं को नज़रअंदाज़ नहीं करता। ये त्रुटियाँ आपके सबटोटल की गणनाओं में फैल सकती हैं और पूरे परिणाम को अमान्य कर सकती हैं। SUBTOTAL() लागू करने से पहले अपने डेटा की त्रुटियाँ साफ़ करें, या AGGREGATE() फ़ंक्शन का उपयोग करने पर विचार करें जो त्रुटि मानों को स्किप कर सकता है।

• ऊर्ध्वाधर डेटा उन्मुखीकरण सर्वोत्तम रहता है: यद्यपि SUBTOTAL() क्षैतिज रेंज को संभाल सकता है, इसका डिज़ाइन ऊर्ध्वाधर डेटा संरचनाओं के लिए अनुकूलित है। Excel के फ़िल्टरिंग, सॉर्टिंग और हाइडिंग फ़ीचर पंक्ति-दर-पंक्ति काम करते हैं, जिससे ऊर्ध्वाधर क्रम इस फ़ंक्शन के इच्छित व्यवहार के अधिक अनुरूप होता है।

Subtotal() बनाम AGGREGATE()

AGGREGATE() फ़ंक्शन SUBTOTAL() का उन्नत विकल्प है, जिसमें त्रुटियों को संभालने और अधिक सांख्यिकीय क्रियाएँ करने की अतिरिक्त क्षमताएँ हैं। जहाँ SUBTOTAL() 11 बुनियादी क्रियाएँ देता है, वहीं AGGREGATE() 19 अलग-अलग फ़ंक्शन प्रदान करता है, जिनमें परसेंटाइल, क्वार्टाइल और माध्यिका की गणनाएँ शामिल हैं।

AGGREGATE() का मुख्य लाभ इसकी त्रुटि-हैंडलिंग क्षमता है। SUBTOTAL() के विपरीत, जो गणनाओं में त्रुटि मानों को शामिल करता है, AGGREGATE() #N/A, #DIV/0!, या #VALUE! जैसी त्रुटियों वाली कोशिकाओं को स्वतः स्किप कर सकता है। यह उन डेटासेट के लिए विशेष रूप से उपयोगी है जिनमें अधूरा या समस्याग्रस्त डेटा होता है।

AGGREGATE() इस बात पर भी अधिक सूक्ष्म नियंत्रण देता है कि क्या-क्या नज़रअंदाज़ किया जाए। आप इसके विकल्प पैरामीटर का उपयोग करके छिपी पंक्तियों, नेस्टेड सबटोटल, त्रुटि मानों, या इन तत्वों के किसी भी संयोजन को स्किप करने के लिए इसे कॉन्फ़िगर कर सकते हैं।

SUBTOTAL() उपयोग करते समय प्रो टिप्स

हमेशा पहले सॉर्ट करें

Excel के बिल्ट-इन Data > Subtotal फ़ीचर का उपयोग करते समय, पहले अपने डेटा को ग्रुपिंग कॉलम के अनुसार सॉर्ट करें। इससे संबंधित सभी रिकॉर्ड एक साथ दिखाई देते हैं और आपकी सबटोटल गणनाओं के लिए साफ़ ग्रुप ब्रेक बनते हैं। बिना सॉर्ट किए हुए डेटा में बिखरे हुए सबटोटल बनते हैं जो सार्थक समरी नहीं देते।

कॉलम की पहली पंक्ति में लेबल का उपयोग करें

सबटोटल लागू करने से पहले अपने डेटा रेंज की पहली पंक्ति में वर्णनात्मक हेडर रखें। Excel इन लेबल्स का उपयोग सार्थक सबटोटल विवरण बनाने में करता है और आपकी रिपोर्ट को समझना आसान बनाता है। स्पष्ट कॉलम हेडर मैन्युअल SUBTOTAL() फ़ॉर्मूलों के लिए रेंज चुनते समय भी मदद करते हैं।

समरी का स्थान (ऊपर या नीचे) ध्यान में रखें

Excel का Subtotal टूल डिफ़ॉल्ट रूप से प्रत्येक समूह के नीचे समरी पंक्तियाँ रखता है, लेकिन आप चाहें तो उन्हें ऊपर भी रख सकते हैं। यह चुनाव करते समय अपनी रिपोर्ट के अपेक्षित उपयोग पर विचार करें। समूहों के ऊपर समरी पंक्तियाँ एग्ज़िक्युटिव डैशबोर्ड के लिए उपयुक्त हैं, जबकि नीचे समरी पंक्तियाँ पारंपरिक लेखा प्रारूपों के अनुकूल हैं।

सटीक नियंत्रण हेतु पंक्तियाँ Hide बनाम Filter करें

अपेक्षित परिणाम पाने के लिए फ़िल्टर करने और मैन्युअल रूप से पंक्तियाँ छिपाने के अंतर को समझें। जब आप चाहते हैं कि SUBTOTAL() कुछ डेटा को अस्थायी रूप से नज़रअंदाज़ करे, तो फ़िल्टर का उपयोग करें। जब आप अधिक स्थायी बहिष्करण चाहते हैं, तो मैन्युअल हाइड (राइट-क्लिक > Hide) करें, फिर 101-111 फ़ंक्शन नंबर चुनें ताकि छिपी स्थिति का सम्मान हो।

यह तरीका आपको परतदार नियंत्रण देता है: अस्थायी दृश्य के लिए फ़िल्टर करें, अर्द्ध-स्थायी बहिष्करण के लिए मैन्युअल रूप से छिपाएँ, और अपनी हाइडिंग मंशा का सम्मान कराने के लिए उपयुक्त फ़ंक्शन नंबर चुनें।

निष्कर्ष

SUBTOTAL() का प्रभावी उपयोग सीखना Excel में डेटा विश्लेषण के प्रति आपका दृष्टिकोण बदल देता है। स्थिर गणनाएँ बनाने के बजाय जो डेटा बदलने पर टूट जाती हैं, आप ऐसे मजबूत फ़ॉर्मूले बना सकते हैं जो उपयोगकर्ता क्रियाओं और डेटा संशोधनों के अनुसार स्वचालित रूप से अनुकूलित हों। यह कौशल विशेष रूप से तब मूल्यवान होता है जब आप बड़े डेटासेट्स पर काम कर रहे हों या ऐसी रिपोर्ट बना रहे हों जिन्हें समय के साथ कई उपयोगकर्ता फ़िल्टर और संशोधित करेंगे।

व्यक्तिगत फ़ंक्शन जैसे SUBTOTAL() से आगे व्यापक Excel विशेषज्ञता बनाने के लिए, हमारा Excel Fundamentals स्किल ट्रैक 16 घंटे का संरचित लर्निंग पथ प्रदान करता है जो डेटा तैयारी से लेकर उन्नत विश्लेषण और विज़ुअलाइज़ेशन तकनीकों तक आपको ले जाता है। जो पाठक तुरंत विश्लेषणात्मक अनुप्रयोगों को खंगालना चाहते हैं, उनके लिए हमारा Data Analysis in Excel कोर्स PivotTable में महारत और गहरी अंतर्दृष्टि के लिए उन्नत लॉजिकल फ़ंक्शनों को सिखाता है।