यदि आपकी एक्सेल वर्कशीट में सेल की बदलती रेंज पर आधारित गणनाएं शामिल हैं, तो गणनाओं को अद्यतित रखने के कार्य को सरल बनाने के लिए SUM OFFSET फॉर्मूला में SUM और OFFSET फ़ंक्शंस का एक साथ उपयोग करें।
इस आलेख में निर्देश Microsoft 365, Excel 2019, Excel 2016, Excel 2013 और Excel 2010 के लिए Excel पर लागू होते हैं।
SUM और OFFSET फंक्शन के साथ एक डायनामिक रेंज बनाएं
यदि आप उस अवधि के लिए गणनाओं का उपयोग करते हैं जो लगातार बदलती रहती है - जैसे कि महीने के लिए बिक्री का निर्धारण - एक्सेल में ऑफ़सेट फ़ंक्शन का उपयोग एक गतिशील रेंज सेट करने के लिए करें जो प्रत्येक दिन के बिक्री के आंकड़े जोड़े जाने पर बदल जाती है।
अपने आप में, एसयूएम फ़ंक्शन आमतौर पर डेटा की नई कोशिकाओं को सम्मिलित करने की श्रेणी में सम्मिलित कर सकता है। एक अपवाद तब होता है जब डेटा उस सेल में डाला जाता है जहां फ़ंक्शन वर्तमान में स्थित है।
नीचे दिए गए उदाहरण में, प्रत्येक दिन के लिए नए बिक्री के आंकड़े सूची के निचले भाग में जोड़े जाते हैं, जिससे हर बार नया डेटा जोड़े जाने पर कुल एक सेल को लगातार नीचे शिफ्ट करने के लिए मजबूर किया जाता है।
इस ट्यूटोरियल का अनुसरण करने के लिए, एक खाली एक्सेल वर्कशीट खोलें और नमूना डेटा दर्ज करें। आपकी कार्यपत्रक को उदाहरण की तरह स्वरूपित करने की आवश्यकता नहीं है, लेकिन डेटा को उसी कक्ष में दर्ज करना सुनिश्चित करें।
यदि डेटा को कुल करने के लिए केवल SUM फ़ंक्शन का उपयोग किया जाता है, तो फ़ंक्शन तर्क के रूप में उपयोग किए जाने वाले कक्षों की श्रेणी को हर बार नया डेटा जोड़े जाने पर संशोधित करने की आवश्यकता होगी।
SUM और OFFSET फ़ंक्शंस का एक साथ उपयोग करने से, जो रेंज कुल होती है वह गतिशील हो जाती है और डेटा की नई कोशिकाओं को समायोजित करने के लिए बदल जाती है। डेटा की नई कोशिकाओं को जोड़ने से कोई समस्या नहीं होती है क्योंकि जैसे-जैसे प्रत्येक नए सेल को जोड़ा जाता है, सीमा समायोजित होती रहती है।
वाक्यविन्यास और तर्क
इस सूत्र में, SUM फ़ंक्शन का उपयोग तर्क के रूप में आपूर्ति किए गए डेटा की श्रेणी को कुल करने के लिए किया जाता है। इस श्रेणी के लिए प्रारंभ बिंदु स्थिर है और सूत्र द्वारा कुल की जाने वाली पहली संख्या के सेल संदर्भ के रूप में पहचाना जाता है।
ऑफ़सेट फ़ंक्शन SUM फ़ंक्शन के अंदर नेस्टेड है और सूत्र द्वारा कुल डेटा की सीमा के लिए एक गतिशील समापन बिंदु बनाता है। यह सूत्र के स्थान के ऊपर एक कक्ष में श्रेणी के समापन बिंदु को सेट करके पूरा किया जाता है।
सूत्र वाक्यविन्यास है:
=SUM(रेंज प्रारंभ:ऑफसेट(संदर्भ, पंक्तियाँ, कॉलम))
तर्क हैं:
- रेंज स्टार्ट: सेल्स की रेंज के लिए शुरुआती बिंदु जिसे SUM फंक्शन द्वारा टोटल किया जाएगा। इस उदाहरण में, प्रारंभिक बिंदु सेल B2 है।
- संदर्भ: रेंज एंडपॉइंट की गणना के लिए आवश्यक सेल संदर्भ का उपयोग किया जाता है। उदाहरण में, संदर्भ तर्क सूत्र के लिए कक्ष संदर्भ है क्योंकि श्रेणी सूत्र के ऊपर एक कक्ष को समाप्त करती है।
- पंक्तियां: ऑफसेट की गणना में प्रयुक्त संदर्भ तर्क के ऊपर या नीचे पंक्तियों की संख्या आवश्यक है। यह मान धनात्मक, ऋणात्मक या शून्य पर सेट हो सकता है। यदि ऑफ़सेट स्थान संदर्भ तर्क से ऊपर है, तो मान ऋणात्मक है। यदि ऑफ़सेट नीचे है, तो Rows तर्क सकारात्मक है। यदि ऑफ़सेट एक ही पंक्ति में स्थित है, तो तर्क शून्य है। इस उदाहरण में, ऑफ़सेट संदर्भ तर्क के ऊपर एक पंक्ति शुरू करता है, इसलिए तर्क के लिए मान ऋणात्मक (-1) है।
- Cols: ऑफ़सेट की गणना के लिए संदर्भ तर्क के बाएँ या दाएँ कॉलम की संख्या। यह मान धनात्मक, ऋणात्मक या शून्य पर सेट हो सकता है। यदि ऑफ़सेट स्थान संदर्भ तर्क के बाईं ओर है, तो यह मान ऋणात्मक है। यदि ऑफ़सेट दाईं ओर है, तो Cols तर्क सकारात्मक है। इस उदाहरण में, डेटा का योग सूत्र के समान कॉलम में है, इसलिए इस तर्क का मान शून्य है।
कुल बिक्री डेटा के लिए योग ऑफसेट फॉर्मूला का उपयोग करें
यह उदाहरण कार्यपत्रक के कॉलम B में सूचीबद्ध दैनिक बिक्री के आंकड़ों का कुल योग लौटाने के लिए SUM OFFSET सूत्र का उपयोग करता है। प्रारंभ में, सूत्र को सेल B6 में दर्ज किया गया था और चार दिनों के लिए बिक्री डेटा का कुल योग किया गया था।
अगला कदम पांचवे दिन की कुल बिक्री के लिए जगह बनाने के लिए SUM OFFSET फॉर्मूला को एक पंक्ति में नीचे ले जाना है। यह एक नई पंक्ति 6 को सम्मिलित करके पूरा किया जाता है, जो सूत्र को पंक्ति 7 में ले जाता है।
चाल के परिणामस्वरूप, एक्सेल स्वचालित रूप से संदर्भ तर्क को सेल B7 में अपडेट करता है और सेल B6 को सूत्र द्वारा सारांशित श्रेणी में जोड़ता है।
- सेल का चयन करें B6, जो वह स्थान है जहां सूत्र परिणाम प्रारंभ में प्रदर्शित होंगे।
-
रिबन के सूत्र टैब चुनें।
Image -
चुनें गणित और त्रिकोण।
Image -
चुनें योग.
Image - फंक्शन आर्ग्युमेंट्स डायलॉग बॉक्स में, कर्सर को Number1 टेक्स्ट बॉक्स में रखें।
-
वर्कशीट में, डायलॉग बॉक्स में इस सेल संदर्भ को दर्ज करने के लिए सेल B2 चुनें। यह स्थान सूत्र के लिए स्थिर समापन बिंदु है।
Image - फंक्शन आर्ग्युमेंट्स डायलॉग बॉक्स में, कर्सर को Number2 टेक्स्ट बॉक्स में रखें।
-
ऑफसेट(B6, -1, 0) दर्ज करें। यह ऑफ़सेट फ़ंक्शन सूत्र के लिए गतिशील समापन बिंदु बनाता है।
Image -
फ़ंक्शन को पूरा करने के लिए ठीक चुनें और डायलॉग बॉक्स बंद करें। कुल सेल B6 में दिखाई देता है।
Image
अगले दिन का बिक्री डेटा जोड़ें
अगले दिन का बिक्री डेटा जोड़ने के लिए:
- पंक्ति 6 के लिए पंक्ति शीर्षलेख पर राइट-क्लिक करें।
-
कार्यपत्रक में एक नई पंक्ति सम्मिलित करने के लिए सम्मिलित करें चुनें। SUM OFFSET सूत्र एक पंक्ति को नीचे ले जाकर सेल B7 में ले जाता है और पंक्ति 6 अब खाली है।
Image - सेल A6 का चयन करें और नंबर 5 दर्ज करें यह इंगित करने के लिए कि पांचवें दिन की कुल बिक्री दर्ज की जा रही है।
-
सेल चुनें B6, $1458.25 दर्ज करें, फिर Enter दबाएं।
Image - सेल B7 $7137.40 के नए कुल अपडेट के लिए।
जब आप सेल B7 का चयन करते हैं, तो अद्यतन सूत्र सूत्र पट्टी में प्रकट होता है।
=SUM(B2:OFFSET(B7, -1, 0))
ऑफ़सेट फ़ंक्शन के दो वैकल्पिक तर्क हैं: ऊँचाई और चौड़ाई, जिनका इस उदाहरण में उपयोग नहीं किया गया था। ये तर्क ऑफ़सेट फ़ंक्शन को पंक्तियों और स्तंभों की संख्या के संदर्भ में आउटपुट के आकार को बताते हैं।
इन तर्कों को छोड़कर, फ़ंक्शन इसके बजाय संदर्भ तर्क की ऊंचाई और चौड़ाई का उपयोग करता है, जो इस उदाहरण में एक पंक्ति ऊंची और एक स्तंभ चौड़ी है।
