श्रेणी या समूह के अनुसार योग - एक्सेल और गूगल शीट्स

उदाहरण कार्यपुस्तिका डाउनलोड करें

उदाहरण कार्यपुस्तिका डाउनलोड करें

यह ट्यूटोरियल प्रदर्शित करेगा कि एक्सेल और गूगल शीट्स में SUMIFS फ़ंक्शन का उपयोग करके समूह द्वारा उप-योग की गणना कैसे करें।

श्रेणी या समूह द्वारा उप-योग तालिका

सबसे पहले, हम प्रदर्शित करेंगे कि Excel 365 या Google पत्रक में डेटा श्रेणी से एक गतिशील उप-योग सारांश तालिका कैसे बनाई जाए।

हम स्वचालित रूप से उप-योग करने के लिए अद्वितीय फ़ंक्शन और SUMIFS फ़ंक्शन का उपयोग करते हैं उत्पादों की संख्या द्वारा उत्पाद समूह:

1	=SUMIFS(C3:C11,B3:B11,E3)

इस उप-योग तालिका को बनाने के लिए, हम योग करने के लिए SUMIFS फ़ंक्शन के मानक अनुप्रयोग का उपयोग करते हैं उत्पादों की संख्या जो प्रत्येक से मेल खाता हो उत्पाद समूह। हालांकि, इससे पहले कि यह संभव हो, हमें अद्वितीय की एक सूची बनाने की आवश्यकता है उत्पाद समूह। Microsoft Excel 365 और Google पत्रक उपयोगकर्ताओं के पास एक सेल श्रेणी से अद्वितीय मानों की एक गतिशील सूची बनाने के लिए UNIQUE फ़ंक्शन तक पहुंच है। इस उदाहरण में, हम सेल E3 में निम्न सूत्र जोड़ते हैं:

1	= अद्वितीय (बी 3: बी 11)

जब यह सूत्र दर्ज किया जाता है, तो सेल के नीचे स्वचालित रूप से एक सूची बनाई जाती है, जिसमें सभी अद्वितीय मानों को दिखाया जाता है उत्पाद समूह डेटा रेंज़। इस उदाहरण में, सूची सभी 3 अद्वितीय दिखाने के लिए E3:E5 को कवर करने के लिए खुद को विस्तारित करती है उत्पाद समूह मूल्य।

यह एक गतिशील सरणी फ़ंक्शन है जहां परिणाम सूची के आकार को परिभाषित करने की आवश्यकता नहीं है, और इनपुट डेटा मान बदलते ही यह स्वचालित रूप से कम हो जाएगा और बढ़ेगा।

ध्यान दें कि Excel 365 में, UNIQUE फ़ंक्शन केस संवेदी नहीं है, लेकिन Google पत्रक में यह है। सूची पर विचार करें {"ए"; "ए"; "बी"; "सी"}। UNIQUE फंक्शन आउटपुट प्रोग्राम पर निर्भर करता है:

• {"ए"; "बी"; एक्सेल 365 . में "सी"}
• {"ए"; "ए"; "बी"; "c"} Google पत्रक में

यदि आप Excel 365 से पहले किसी Excel संस्करण का उपयोग कर रहे हैं, तो आपको एक अलग दृष्टिकोण अपनाने की आवश्यकता होगी। इसके बारे में अगले भाग में चर्चा की गयी हैं।

श्रेणी या समूह द्वारा उप-योग तालिका - प्री एक्सेल ३६५

यदि आप Excel 365 से पहले Excel के किसी संस्करण का उपयोग कर रहे हैं, तो UNIQUE फ़ंक्शन उपयोग के लिए उपलब्ध नहीं है। उसी व्यवहार को दोहराने के लिए, आप INDEX फ़ंक्शन और MATCH फ़ंक्शन को COUNTIF फ़ंक्शन के साथ संयोजित कर सकते हैं ताकि सेल की श्रेणी से अद्वितीय मानों की सूची तैयार करने के लिए एक सरणी सूत्र बनाया जा सके:

1	{=INDEX($B$3:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))}

इस सूत्र को कार्य करने के लिए, निश्चित सेल संदर्भों को ध्यान से लिखा जाना चाहिए, COUNTIF फ़ंक्शन रेंज $E$2:E2 को संदर्भित करता है, जो कि E2 से शुरू होने वाली सेल से ऊपर के सेल तक सूत्र है।

सूत्र को लिखने के बाद CTRL + SHIFT + ENTER दबाकर सरणी सूत्र के रूप में भी दर्ज करना होगा। यह सूत्र एक है 1-सेल सरणी सूत्र, जिसे बाद में कोशिकाओं E4, E5 आदि में कॉपी-पेस्ट किया जा सकता है। इसे एक क्रिया में संपूर्ण श्रेणी E3:E5 के लिए सरणी सूत्र के रूप में दर्ज न करें।

उसी तरह जैसे पिछले उदाहरण में, एक SUMIFS फ़ंक्शन का उपयोग तब उप-योग के लिए किया जाता है उत्पादों की संख्या द्वारा उत्पाद समूह:

1	=SUMIFS(C3:C11,B3:B11,E3)

श्रेणी या समूह द्वारा योग - डेटा तालिका में उप-योग

ऊपर दिखाए गए सारांश तालिका पद्धति के विकल्प के रूप में, हम उप-योग सीधे डेटा तालिका में जोड़ सकते हैं। हम इसे जोड़ने के लिए SUMIFS फ़ंक्शन के साथ IF फ़ंक्शंस का उपयोग करके प्रदर्शित करेंगे समूह द्वारा उप-योग मूल डेटा तालिका में।

1	=IF(B3=B2,",SUMIFS(C3:C11,B3:B11,B3))

यह उदाहरण IF फ़ंक्शन के भीतर नेस्टेड SUMIFS फ़ंक्शन का उपयोग करता है। आइए उदाहरण को चरणों में तोड़ें:

डेटा तालिका में सीधे सारांश आँकड़े जोड़ने के लिए, हम SUMIFS फ़ंक्शन का उपयोग कर सकते हैं। हम का योग करके प्रारंभ करते हैं उत्पादों की संख्या जो प्रासंगिक से मेल खाता हो उत्पाद समूह:

1	=SUMIFS(C3:C11,B3:B11,B3)

यह सूत्र प्रत्येक डेटा पंक्ति के लिए एक उप-योग मान उत्पन्न करता है। प्रत्येक की पहली डेटा पंक्ति में केवल उप-योग दिखाने के लिए उत्पाद समूह, हम IF फ़ंक्शन का उपयोग करते हैं। ध्यान दें कि डेटा को पहले से ही सॉर्ट किया जाना चाहिए उत्पाद समूह यह सुनिश्चित करने के लिए कि उप-योग सही ढंग से प्रदर्शित होते हैं।

1	=IF(B3=B2,",SUMIFS(C3:C11,B3:B11,B3))

IF फ़ंक्शन प्रत्येक डेटा पंक्ति की तुलना करता है उत्पाद समूह इसके ऊपर डेटा पंक्ति के साथ मूल्य, और यदि उनके पास समान मूल्य है तो यह एक रिक्त सेल ("") को आउटपुट करता है।

अगर उत्पाद समूह मान अलग हैं, योग प्रदर्शित होता है। इस तरह, प्रत्येक उत्पाद समूह योग केवल एक बार प्रदर्शित होता है (इसके पहले उदाहरण की पंक्ति पर)।

समूह द्वारा डेटासेट को छाँटना

यदि डेटा पहले से सॉर्ट नहीं किया गया है, तो भी हम सबटोटल के लिए उसी फॉर्मूले का उपयोग कर सकते हैं।

ऊपर दिया गया डेटासेट इसके अनुसार क्रमित नहीं है उत्पाद समूह, ऐसा समूह द्वारा उप-योग कॉलम प्रत्येक उप-योग को एक से अधिक बार प्रदर्शित करता है। डेटा को हमारे इच्छित प्रारूप में प्राप्त करने के लिए, हम डेटा तालिका का चयन कर सकते हैं और "सॉर्ट ए टू जेड" पर क्लिक कर सकते हैं।

लॉकिंग सेल संदर्भ

हमारे फ़ार्मुलों को पढ़ने में आसान बनाने के लिए, हमने लॉक किए गए सेल संदर्भों के बिना कुछ फ़ार्मुलों को दिखाया है:

1	=IF(B3=B2,",SUMIFS(C3:C11,B3:B11,B3))

लेकिन आपकी फ़ाइल में कहीं और कॉपी और पेस्ट करने पर ये सूत्र ठीक से काम नहीं करेंगे। इसके बजाय, आपको लॉक किए गए सेल संदर्भों का उपयोग इस तरह करना चाहिए:

1	=IF(B3=B2,"",SUMIFS($C$3:$C$11,$B$3:$B$11,B3))

अधिक जानने के लिए लॉकिंग सेल संदर्भों पर हमारा लेख पढ़ें।

उप-योग दिखाने के लिए पिवट टेबल का उपयोग करना

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

Google पत्रक में श्रेणी या समूह द्वारा योग

ये सूत्र Google शीट्स में एक्सेल की तरह ही काम करते हैं। हालाँकि, Google पत्रक में UNIQUE फ़ंक्शन केस संवेदनशील है।