यदि एक से अधिक शीट में योग हो - एक्सेल और गूगल शीट्स

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

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

यह ट्यूटोरियल प्रदर्शित करेगा कि एक्सेल और गूगल शीट्स में कई शीट्स में कुछ मानदंडों को पूरा करने वाले डेटा को समेटने के लिए SUMPRODUCT और SUMIFS फ़ंक्शंस का उपयोग कैसे करें.

एकाधिक शीट्स में नियमित योग

कभी-कभी आपका डेटा एक्सेल फ़ाइल में कई कार्यपत्रकों तक फैल सकता है। समय-समय पर एकत्र किए जाने वाले डेटा के लिए यह सामान्य है। कार्यपुस्तिका में प्रत्येक शीट में एक निर्धारित समय अवधि के लिए डेटा हो सकता है। हम एक ऐसा फॉर्मूला चाहते हैं जो दो या दो से अधिक शीट में निहित डेटा को समेटे।

SUM फ़ंक्शन आपको a . का उपयोग करके कई शीट में डेटा को आसानी से समेटने की अनुमति देता है 3डी संदर्भ:

1	= एसयूएम (शीट 1: शीट 2! ए 1)

हालाँकि, यह SUMIFS फ़ंक्शन के साथ संभव नहीं है। इसके बजाय हमें एक अधिक जटिल सूत्र का उपयोग करना चाहिए।

योग यदि एक से अधिक शीटों में है

यह उदाहरण योग करेगा नियोजित डिलीवरी की संख्या प्रत्येक के लिए ग्राहक SUMIFS, SUMPRODUCT, और INDIRECT फ़ंक्शंस का उपयोग करके कई कार्यपत्रकों पर, प्रत्येक एक अलग महीने से संबंधित डेटा धारण करता है:

1	= SUMPRODUCT (SUMIFS (अप्रत्यक्ष ("'" और F3: F6 और "'!" और "D3: D7"), अप्रत्यक्ष ("'" और F3: F6 और "'!" और "C3: C7"), H3))

आइए इस सूत्र के माध्यम से चलते हैं।

चरण 1: केवल 1 इनपुट शीट के लिए SUMIFS फॉर्मूला बनाएं:

हम योग करने के लिए SUMIFS फ़ंक्शन का उपयोग करते हैं नियोजित डिलीवरी की संख्या द्वारा ग्राहक एकल इनपुट डेटा शीट के लिए:

1	=SUMIFS(D3:D7,C3:C7,H3)

चरण 2: सूत्र में एक पत्रक संदर्भ जोड़ें

हम सूत्र परिणाम को समान रखते हैं, लेकिन हम निर्दिष्ट करते हैं कि इनपुट डेटा शीट में है जिसे कहा जाता है 'चरण 2'

1	=SUMIFS('चरण 2'!D3:D7,'चरण 2'!C3:C7,H3)

चरण 3: एक SUMPRODUCT फ़ंक्शन के अंदर घोंसला बनाएं

कई शीटों पर SUMIFS गणना करने के लिए सूत्र तैयार करने के लिए और फिर परिणामों को एक साथ जोड़ने के लिए, हम सूत्र के चारों ओर एक SUMPRODUCT फ़ंक्शन जोड़ते हैं

1	= SUMPRODUCT (SUMIFS ('चरण 3'! D3: D7, 'चरण 3'! C3: C7, H3))

एक शीट पर SUMIFS फ़ंक्शन का उपयोग करने से एकल मान प्राप्त होता है। कई शीटों में, SUMIFS फ़ंक्शन मानों की एक सरणी (प्रत्येक कार्यपत्रक के लिए एक) आउटपुट करता है। हम इस सरणी में मानों को कुल करने के लिए SUMPRODUCT फ़ंक्शन का उपयोग करते हैं।

चरण 4: शीट संदर्भ को शीट नामों की सूची से बदलें

हम को बदलना चाहते हैं शीट का नाम मान वाली डेटा सूची के साथ सूत्र का हिस्सा: जनवरी, फ़रवरी, मार्च, तथा अप्रैल. यह सूची कोशिकाओं F3:F6 में संग्रहीत है।

अप्रत्यक्ष कार्य यह सुनिश्चित करने के लिए कि पाठ सूची दिख रही है शीट के नाम SUMIFS फ़ंक्शन में एक मान्य सेल संदर्भ के भाग के रूप में माना जाता है।

1	= SUMPRODUCT (SUMIFS (अप्रत्यक्ष ("'" और F3: F6 और "'!" और "D3: D7"), अप्रत्यक्ष ("'" और F3: F6 और "'!" और "C3: C7"), H3))

इस सूत्र में, पहले लिखित श्रेणी संदर्भ:

1	'चरण 3'!D3:D7

द्वारा प्रतिस्थापित किया जाता है:

1	अप्रत्यक्ष("'"&F3:F6&"'!"&"D3:D7")

उद्धरण चिह्न सूत्र को पढ़ने में कठिन बनाते हैं, इसलिए यहां इसे अतिरिक्त रिक्त स्थान के साथ दिखाया गया है:

1	अप्रत्यक्ष ("'" और F3:F6 और "'!" और "D3:D7")

कोशिकाओं की सूची को संदर्भित करने के इस तरीके का उपयोग करने से हम कई शीटों से डेटा को सारांशित कर सकते हैं जो संख्यात्मक सूची शैली का पालन नहीं करते हैं। एक मानक 3D संदर्भ के लिए शीट नामों की शैली में होना आवश्यक है: इनपुट 1, इनपुट 2, इनपुट 3, आदि, लेकिन ऊपर दिया गया उदाहरण आपको किसी की सूची का उपयोग करने की अनुमति देता है शीट के नाम और उन्हें एक अलग सेल में संदर्भित करने के लिए।

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

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

1	= SUMPRODUCT (SUMIFS (अप्रत्यक्ष ("'" और F3: F6 और "'!" और "D3: D7"), अप्रत्यक्ष ("'" और F3: F6 और "'!" और "C3: C7"), H3))

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

1	=SUMPRODUCT(SUMIFS(अप्रत्यक्ष("'"&$F$3:$F$6&"'!"&"D3:D7"),INDIRECT("'"&$F$3:$F$6&"'!"& "C3:C7"), H3))

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

योग यदि Google पत्रक में एकाधिक पत्रक में है

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

इसके बजाय, प्रत्येक इनपुट शीट के लिए अलग SUMIFS गणना की जा सकती है और परिणाम एक साथ जोड़े जा सकते हैं:

1234	=SUMIFS(जनवरी!D3:D7,जनवरी!C3:C7,H3)+SUMIFS(फरवरी!D3:D7,फरवरी!C3:C7,H3)+SUMIFS(मार्च!D3:D7,Mar!C3:C7,H3)+SUMIFS(अप्रैल!D3:D7,अप्रैल!C3:C7,H3)