विधि 1 - एक्सेल में स्वचालित रूप से सबटोटल डालें
- एक रैंडम सेल चुनें > डेटा टैब पर जाएं > सॉर्ट पर क्लिक करें।
- सॉर्ट डायलॉग बॉक्स विंडो पर खुलेगा।
- सॉर्ट बाय फ़ील्ड में, उस कॉलम को चुनें जिसके आधार पर आप सबटोटल डालना चाहते हैं।
- सॉर्ट ऑन फ़ील्ड में सेल मान रखें।
- क्रम आरोही होगा: A से Z।
- ओके क्लिक करें।
- यह सेल्स रेप्स कॉलम को वर्णानुक्रम में सॉर्ट करेगा।
बिल्ट-इन सुविधा का उपयोग करें, जिसे एक्सेल में सबटोटल शॉर्टकट के रूप में भी जाना जाता है।
- रेंज में एक सेल पर क्लिक करें > डेटा टैब से आउटलाइन समूह के तहत सबटोटल चुनें।
- सबटोटल डायलॉग बॉक्स अब दिखाई देगा। हम SUM फंक्शन का उपयोग करके एक्सेल में सबटोटल डालेंगे।
- प्रत्येक परिवर्तन में फ़ील्ड में वह डेटा होना चाहिए जिस पर आप सबटोटल डालेंगे।
- फंक्शन का उपयोग करें फ़ील्ड में SUM फंक्शन डालें ताकि कुल मान की गणना हो सके।
- ओके क्लिक करें।
आप एक्सेल में सबटोटल डाल पाएंगे। सेल्स रेप्स कॉलम में सबटोटल और ग्रैंड टोटल होंगे।
- ऊपर बाएं कोने पर 1, 2, और 3 नंबरों वाला आइकन आपको बताएगा कि एक्सेल में सबटोटल का उपयोग कैसे करें। यह आपको समूहों को संभालने और एक्सेल में सबटोटल दिखाने और छिपाने की अनुमति देता है।
विधि 2 - एकाधिक सबटोटल जोड़ें
2.1. विभिन्न कॉलम में एकाधिक सबटोटल डालें
पिछले अनुभाग में, हमने दिखाया कि हम सेल्स रेप्स कॉलम पर सबटोटल कैसे लागू कर सकते हैं। श्रेणी के आधार पर एक और सबटोटल जोड़ें।
- डेटा टैब से सॉर्ट डायलॉग बॉक्स खोलें।
- सेल्स रेप्स द्वारा सॉर्ट करें।
- एक और स्तर जोड़ें।
- हमने सेल्स रेप्स द्वारा सॉर्ट किया; अब हम श्रेणी द्वारा सॉर्ट करेंगे।
- ओके क्लिक करें।
- डेटा टैब से सबटोटल डायलॉग बॉक्स खोलें जैसा कि विधि-1 में वर्णित है।
- प्रत्येक परिवर्तन में फ़ील्ड में सेल्स रेप्स चुनें।
- एक्सेल सेल्स रेप्स कॉलम में सबटोटल डालेगा।
- सबटोटल पॉप-अप खोलें।
- इस बार प्रत्येक परिवर्तन में श्रेणी सुनिश्चित करें।
- वर्तमान सबटोटल बदलें फ़ील्ड अनचेक होना चाहिए। यदि नहीं, तो यह पिछले सबटोटल को साफ़ कर देगा और एकल सबटोटल का परिणाम देगा।
- ओके क्लिक करें।
कई बार समूह बनाएं और एक्सेल में सबटोटल डालें।
यदि आप सबटोटल और जोड़ना चाहते हैं तो सबटोटल बॉक्स में एक और परिवर्तन चुनें।
2.2. समान कॉलम में एकाधिक सबटोटल खोजें
एक ही कॉलम में एकाधिक सबटोटल डालें। कुल की गणना के बाद, हम औसत और मानक विचलन की गणना करेंगे। आपको दो और फंक्शन की आवश्यकता होगी। AVERAGE फंक्शन चुनें और फिर StdDev फंक्शन चुनें।
विधि-1 से प्राप्त SUM फंक्शन का उपयोग करके सेल्स रेप्स में सबटोटल डालने के बाद शुरू करें।
- सबटोटल बॉक्स को 2 बार और खोलें।
- AVERAGE फंक्शन चुनें और दूसरी बार StdDev फंक्शन चुनें।
- AVERAGE फंक्शन चयनित कॉलम का औसत निकालेगा।
- StdDev फंक्शन मानक विचलन की गणना करेगा।
इन फंक्शन के आधार पर, सबटोटल आपके डेटा में डाले जाएंगे।
विधि 3 - एक्सेल टेबल में सबटोटल डालें
आप एक्सेल टेबल में सीधे सबटोटल लागू नहीं कर सकते। सबटोटल सुविधा यहां काम नहीं करेगी।
यदि आप सबटोटल डालना चाहते हैं, तो आपको पहले टेबल को रेंज में बदलना होगा।
- अपनी टेबल में एक रैंडम सेल चुनें, और टेबल डिज़ाइन टैब > टूल्स समूह के तहत रेंज में बदलें चुनें।
- माइक्रोसॉफ्ट एक्सेल पूछेगा कि क्या आप टेबल को रेंज में बदलना चाहते हैं। पुष्टि करने के लिए हाँ क्लिक करें।
एक्सेल अब टेबल को सामान्य रेंज में बदल देगा। आप सबटोटल सुविधा लागू करके सबटोटल डाल सकते हैं।
विधि 4 - फ़िल्टर के साथ एक्सेल में SUBTOTAl फॉर्मूला लागू करें
हमने सबटोटल सुविधा का उपयोग किया। एक्सेल में SUBTOTAL फंक्शन भी उपलब्ध है ताकि आप अपने डेटा में सबटोटल जोड़ सकें। फ़िल्टर किए गए डेटा के लिए फंक्शन का उपयोग करें।
SUBTOTAL फंक्शन का सिंटैक्स:
SUBTOTAL(function_num,ref1,[ref2],…)
पहला तर्क: function_num कुछ संख्याओं (1-9 और 101-109) द्वारा वर्णित है जो विभिन्न फंक्शन को दर्शाते हैं।
| तर्क | कार्यक्षमता |
|---|---|
| 1-9 | फ़िल्टर की गई पंक्तियों को अनदेखा करता है लेकिन मैन्युअल रूप से छिपी पंक्तियों को शामिल करता है। |
| 101-109 | फ़िल्टर की गई और मैन्युअल रूप से छिपी दोनों पंक्तियों को अनदेखा करता है। |
4.1. फ़िल्टर की गई पंक्तियों में सबटोटल
हमने सबटोटल डालने के लिए निम्न SUBTOTAL फॉर्मूला लागू किया।
चूंकि हमने पहला तर्क: 9 उपयोग किया है, यह फ़िल्टर किए गए सेल को अनदेखा करेगा।
- सेल्स रेप्स कॉलम पर ड्रॉप-डाउन तीर पर क्लिक करें और एक नाम चुनें (जैसे एडम) ताकि उस नाम के अनुसार फ़िल्टर हो सके।
आप देखेंगे कि फंक्शन केवल फ़िल्टर किए गए सेल के लिए सबटोटल की गणना करता है, बाकी को अनदेखा करता है।
नीचे दी गई छवि SUBTOTAL फंक्शन के आउटपुट को तर्क 9 और 109 दोनों के लिए दिखाती है।
4.2. मैन्युअल रूप से छिपी पंक्तियों में सबटोटल
SUBTOTAL फंक्शन के आउटपुट को मैन्युअल रूप से छिपी पंक्तियों के लिए देखें; पंक्ति संख्या पर राइट-क्लिक करके और संदर्भ मेनू से छिपाएं चुनकर पंक्तियां छिपाएं।
तर्क 9 और 109 के लिए आउटपुट की तुलना करें। तर्क 9 मैन्युअल रूप से छिपी पंक्तियों के सेल को शामिल करेगा, लेकिन तर्क 109 उन्हें अनदेखा करेगा।
विधि 5 - VBA कोड से सबटोटल खोजें
- ALT+F11 दबाकर विजुअल बेसिक एडिटर विंडो लॉन्च करें।
- टूलबार पर इन्सर्ट क्लिक करें > मॉड्यूल चुनें।
- मॉड्यूल विंडो पर अपना VBA कोड डालें।
कोड:
Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
Rows(iValue + 1).Insert
Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
For iColumn = 7 To 8 'कॉलम जिनका योग निकालना है
Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
Next iColumn
Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
iValue = iValue + 2
xValue = iValue
Else
iValue = iValue + 1
End If
Loop
Application.ScreenUpdating = True
End Sub
- रन बटन पर क्लिक करके VBA कोड निष्पादित करें।
एक्सेल अब आपके डेटा में सेल्स रेप्स के आधार पर सबटोटल डालेगा।
विधि 6 - एक्सेल पिवट टेबल में सबटोटल जोड़ें
पिवट टेबल में चार फ़ील्ड हैं, और हमने उन फ़ील्ड में अपने डेटा का उपयोग करके पिवट टेबल फॉर्मेट किया है:
1. फ़िल्टर: सेल्स रेप्स
- कॉलम: रिक्त
- पंक्तियाँ: श्रेणी, उत्पाद
- मान: मात्रा, इकाई मूल्य, कुल मूल्य, और लाभ
- डिज़ाइन टैब पर जाएं > लेआउट समूह के तहत सबटोटल विकल्प के ड्रॉपडाउन पर क्लिक करें।
- ड्रॉपडाउन मेनू से समूह के नीचे सभी सबटोटल दिखाएं चुनें।
पिवट टेबल अब पंक्ति लेबल के आधार पर प्रत्येक समूह के नीचे सबटोटल डालेगा। तालिका के नीचे समूह का ग्रैंड टोटल जोड़ें।
विधि 7 - पावर क्वेरी का उपयोग करके सबटोटल डालें
पावर क्वेरी का उपयोग करके समूहों के लिए सबटोटल डालें।
- रेंज में एक सेल पर क्लिक करें > डेटा टैब पर जाएं > प्राप्त करें और रूपांतरित करें डेटा के तहत टेबल/रेंज से पर क्लिक करें।
- टेबल बनाएं डायलॉग बॉक्स पर ओके क्लिक करें।
- एक्सेल आपको पावर क्वेरी एडिटर विंडो पर ले जाएगा।
- उन कॉलम को चुनें (जैसे सेल्स रेप्स और श्रेणी) जिनसे आप डील करना चाहते हैं > ट्रांसफॉर्म टैब से ग्रुप बाय आइकन पर क्लिक करें।
- ग्रुप बाय डायलॉग बॉक्स में, नया कॉलम नाम दें।
- उस ऑपरेशन का प्रकार चुनें जिसे आप करना चाहते हैं (जैसे SUM)।
- कॉलम चुनें जहां आप गणना करना चाहते हैं (जैसे कुल मूल्य)।
- आगे समूह बनाने के लिए एड एग्रेशन पर क्लिक करें।
- लाभ के सबटोटल की गणना करने के लिए, कॉलम फ़ील्ड में लाभ चुनें और ऑपरेशन फ़ील्ड में योग चुनें। इस एग्रेशन को एक नाम दें (जैसे लाभ)।
- ओके क्लिक करें।
- आप सेल्स रेप्स और श्रेणी के आधार पर डाले गए सबटोटल देखेंगे।
- होम टैब पर जाएं > क्लोज एंड लोड के ड्रॉपडाउन पर क्लिक करें > क्लोज एंड लोड चुनें।
पावर क्वेरी एडिटर आपके समूहीकृत डेटा को सबटोटल के साथ एक्सेल वर्कशीट पर लोड करेगा।
एक्सेल में सबटोटल कैसे हटाएं
- डेटा टैब से सबटोटल डायलॉग बॉक्स खोलें > सभी हटाएं पर क्लिक करें।
- समूहों और सबटोटल को हटाकर अपने डेटा को सामान्य रेंज में बदलें।
नोट: सबटोटल हटाने से डेटा सॉर्टेड रहता है, मूल डेटा की तरह नहीं जैसा सॉर्टिंग से पहले था।
अक्सर पूछे जाने वाले प्रश्न
1. मैं एक्सेल में सबटोटल क्यों नहीं हटा सकता?
सबटोटल कई कारणों से काम नहीं कर सकते।
i) संरक्षित वर्कशीट: यदि यह पासवर्ड से संरक्षित है तो आपको अपनी शीट को अनप्रोटेक्ट करना होगा।
ii) साझा वर्कबुक: आपको वर्कबुक को अनशेयर करना पड़ सकता है या जांचना पड़ सकता है कि क्या वर्कबुक कई उपयोगकर्ताओं के बीच साझा की गई है।
iii) कस्टम फॉर्मूला या मैक्रोज़: यदि सबटोटल कस्टम फॉर्मूला या मैक्रोज़ का उपयोग करके जोड़े गए थे, तो आपको उन्हें हटाने के लिए संशोधित या हटाना पड़ सकता है।
2. एक्सेल में सबटोटल को कैसे संक्षिप्त या विस्तारित करें?
आप एक्सेल में सबटोटल को संक्षिप्त या विस्तारित करने के लिए ग्रुप आउटलाइन सुविधा का उपयोग कर सकते हैं। एक्सेल स्वचालित रूप से आउटलाइन प्रतीक जोड़ता है जो आपको सबटोटल समूहों को संक्षिप्त या विस्तारित करने की अनुमति देता है। समूह पंक्तियों के आगे माइनस (-) या प्लस (+) प्रतीकों पर क्लिक करने से क्रमशः सबटोटल संक्षिप्त या विस्तारित होंगे।
3. क्या डेटा बदलने पर एक्सेल में सबटोटल स्वचालित रूप से अपडेट हो सकते हैं?
हां, आप एक्सेल को डेटा बदलने पर सबटोटल को स्वचालित रूप से अपडेट करने के लिए सेट कर सकते हैं। आप सबटोटल पंक्ति पर राइट-क्लिक करके और संदर्भ मेनू से "रिफ्रेश" चुनकर मैन्युअल रूप से सबटोटल को रिफ्रेश भी कर सकते हैं। यदि वे स्वचालित रूप से अपडेट नहीं होते हैं, तो सुनिश्चित करें कि आपकी वर्कबुक फॉर्मूला ऑटो-कैलकुलेट करती है।
4. क्या AGGREGATE एक्सेल में SUBTOTAL फंक्शन के समान है?
नहीं, AGGREGATE और SUBTOTAL फंक्शन में अंतर हैं हालांकि सिंटैक्स समान है।
लेख से मुख्य निष्कर्ष
- एक्सेल में "सबटोटल" सुविधा आपको निर्दिष्ट कॉलम के आधार पर डेटा के समूहों के लिए सबटोटल डालने की अनुमति देती है।
- सबटोटल डालने के लिए, डेटा की रेंज चुनें और "डेटा" टैब पर जाएं, फिर "आउटलाइन" समूह में "सबटोटल" बटन पर क्लिक करें।
- आप सबटोटल द्वारा कई कॉलम चुनकर सबटोटल के कई स्तर जोड़ सकते हैं।
- सबटोटल को माइनस (-) और प्लस (+) प्रतीकों द्वारा दर्शाए गए ग्रुप आउटलाइन सुविधा का उपयोग करके संक्षिप्त या विस्तारित किया जा सकता है।
- एक्सेल की टेबल या डायनामिक नामित रेंज का उपयोग करके डेटा बदलने पर सबटोटल को स्वचालित रूप से अपडेट किया जा सकता है।
अभ्यास वर्कबुक डाउनलोड करें
आप यहां से अभ्यास वर्कबुक डाउनलोड कर सकते हैं।
<< एक्सेल में सबटोटल | एक्सेल सीखें पर वापस जाएं
मुफ्त उन्नत एक्सेल अभ्यास समाधान के साथ प्राप्त करें!
