डेटा की गतिशील श्रेणियों के लिए कुल खोजने के लिए SUM और OFFSET का उपयोग करें
यदि आपके एक्सेल वर्कशीट में कोशिकाओं की बदलती रेंज के आधार पर गणना शामिल है, तो एसयूएम ऑफसेट फॉर्मूला में एसयूएम और ऑफ़सेट कार्यों का उपयोग करके गणना गणना को अद्यतित रखने का कार्य सरल बनाती है।
एसयूएम और ऑफसेट कार्यों के साथ एक गतिशील रेंज बनाएँ
यदि आप लगातार समय के लिए गणना का उपयोग करते हैं जो लगातार बदलते हैं - जैसे कि महीने के लिए कुल बिक्री - ऑफ़सेट फ़ंक्शन आपको एक गतिशील रेंज स्थापित करने की अनुमति देता है जो प्रत्येक दिन के विक्रय आंकड़े जोड़े जाने के साथ बदलता रहता है।
अपने आप में, एसयूएम फ़ंक्शन आमतौर पर समेकित सीमा में डाले जा रहे डेटा की नई कोशिकाओं को समायोजित कर सकता है।
एक अपवाद तब होता है जब डेटा उस सेल में डाला जाता है जहां फ़ंक्शन वर्तमान में स्थित है।
इस आलेख के साथ उदाहरण छवि में, प्रत्येक दिन के लिए नए विक्रय आंकड़े सूची के नीचे जोड़े जाते हैं, जो प्रत्येक बार नए डेटा को जोड़े जाने पर कुल मिलाकर एक सेल को स्थानांतरित करने के लिए मजबूर करता है।
यदि डेटा को कुल करने के लिए स्वयं SUM फ़ंक्शन का उपयोग किया जाता है, तो प्रत्येक बार नया डेटा जोड़े जाने पर फ़ंक्शन के तर्क के रूप में उपयोग की जाने वाली कोशिकाओं की श्रेणी को संशोधित करना आवश्यक होगा।
एसयूएम और ऑफ़सेट कार्यों का एक साथ उपयोग करके, हालांकि, कुल सीमा जो गतिशील हो जाती है। दूसरे शब्दों में, यह डेटा की नई कोशिकाओं को समायोजित करने के लिए बदलता है। डेटा की नई कोशिकाओं के अतिरिक्त समस्याएं उत्पन्न नहीं होती हैं क्योंकि प्रत्येक नए सेल को जोड़ा जाने पर सीमा समायोजित होती रहती है।
सिंटेक्स और तर्क
इस ट्यूटोरियल के साथ-साथ इस आलेख के साथ आने वाली छवि का संदर्भ लें।
इस सूत्र में, एसयूएम फ़ंक्शन का उपयोग इसके तर्क के रूप में प्रदान किए गए डेटा की सीमा के कुल योग के लिए किया जाता है। इस श्रेणी के लिए प्रारंभ बिंदु स्थैतिक है और सूत्र द्वारा कुल किए जाने वाले पहले नंबर के सेल संदर्भ के रूप में पहचाना जाता है।
OFFSET फ़ंक्शन को SUM फ़ंक्शन के अंदर घोंसला दिया जाता है और सूत्र द्वारा कुल डेटा की सीमा के लिए गतिशील अंतराल बनाने के लिए उपयोग किया जाता है। यह सूत्र के स्थान के ऊपर सीमा के अंत बिंदु को एक सेल में सेट करके पूरा किया जाता है।
सूत्र का वाक्यविन्यास :
= एसयूएम (रेंज स्टार्ट: ऑफसेट (संदर्भ, पंक्तियां, कर्नल))रेंज स्टार्ट - (आवश्यक) कोशिकाओं की श्रेणी के लिए प्रारंभिक बिंदु जो एसयूएम फ़ंक्शन द्वारा कुल किया जाएगा। उदाहरण छवि में, यह सेल बी 2 है।
संदर्भ - (आवश्यक) श्रेणी संदर्भ के अंतराल की गणना के लिए उपयोग किया जाने वाला सेल संदर्भ कई पंक्तियों और स्तंभों को दूर करता है। उदाहरण छवि में, संदर्भ तर्क सूत्र के लिए सेल संदर्भ है क्योंकि हम हमेशा सूत्र को सूत्र के ऊपर एक सेल को समाप्त करना चाहते हैं।
पंक्तियां - (आवश्यक) ऑफ़सेट की गणना में उपयोग की जाने वाली संदर्भ तर्क के ऊपर या नीचे पंक्तियों की संख्या। यह मान सकारात्मक, नकारात्मक, या शून्य पर सेट हो सकता है।
यदि ऑफ़सेट का स्थान संदर्भ तर्क से ऊपर है, तो यह मान नकारात्मक है। यदि यह नीचे है, तो पंक्ति तर्क सकारात्मक है। अगर ऑफ़सेट एक ही पंक्ति में स्थित है, तो यह तर्क शून्य है। इस उदाहरण में, ऑफसेट संदर्भ तर्क से ऊपर एक पंक्ति शुरू होता है, इसलिए इस तर्क का मान ऋणात्मक एक (-1) है।
Cols - (आवश्यक) ऑफ़सेट की गणना में उपयोग किए गए संदर्भ तर्क के बाएं या दाएं कॉलम की संख्या। यह मान सकारात्मक, नकारात्मक, या शून्य पर सेट हो सकता है
यदि ऑफसेट का स्थान संदर्भ तर्क के बाईं ओर है, तो यह मान नकारात्मक है। यदि दाहिने ओर, कोल्स तर्क सकारात्मक है। इस उदाहरण में, कुल मिलाकर डेटा सूत्र के समान कॉलम में है, इसलिए इस तर्क के लिए मान शून्य है।
कुल बिक्री डेटा के लिए एसयूएम ऑफसेट फॉर्मूला का उपयोग करना
यह उदाहरण वर्कशीट के कॉलम बी में सूचीबद्ध दैनिक बिक्री आंकड़ों के लिए कुल लौटने के लिए एसयूएम ऑफ़सेट फॉर्मूला का उपयोग करता है।
प्रारंभ में, सूत्र सेल बी 6 में दर्ज किया गया था और चार दिनों के लिए बिक्री डेटा कुल मिला।
अगला कदम पांचवें दिन की बिक्री कुल के लिए जगह बनाने के लिए एसयूएम ऑफ़सेट फॉर्मूला को एक पंक्ति में स्थानांतरित करना है।
यह एक नई पंक्ति 6 डालने से पूरा किया जाता है, जो सूत्र को पंक्ति 7 पर ले जाता है।
चाल के परिणामस्वरूप, एक्सेल स्वचालित रूप से सेल बी 7 के संदर्भ तर्क को अद्यतन करता है और सूत्र बी द्वारा वर्णित सीमा में सेल बी 6 जोड़ता है।
एसयूएम ऑफसेट फॉर्मूला दर्ज करना
- सेल बी 6 पर क्लिक करें, यह वह स्थान है जहां सूत्र का परिणाम प्रारंभ में प्रदर्शित किया जाएगा।
- रिबन मेनू के सूत्र टैब पर क्लिक करें।
- फ़ंक्शन ड्रॉप-डाउन सूची खोलने के लिए रिबन से गणित और ट्रिग चुनें।
- फ़ंक्शन के संवाद बॉक्स को लाने के लिए सूची में SUM पर क्लिक करें।
- संवाद बॉक्स में, नंबर 1 लाइन पर क्लिक करें।
- संवाद बॉक्स में इस सेल संदर्भ को दर्ज करने के लिए सेल बी 2 पर क्लिक करें। यह स्थान सूत्र के लिए स्थिर अंत बिंदु है;
- संवाद बॉक्स में, संख्या 2 लाइन पर क्लिक करें।
- निम्नलिखित ऑफ़सेट फ़ंक्शन दर्ज करें: फॉर्मूला के लिए डायनामिक एंडपॉइंट बनाने के लिए ऑफ़सेट (बी 6, -1,0) ।
- फ़ंक्शन को पूरा करने के लिए ठीक क्लिक करें और संवाद बॉक्स बंद करें।
कुल $ 5679.15 सेल बी 7 में प्रकट होता है।
जब आप सेल बी 3 पर क्लिक करते हैं, तो पूर्ण फ़ंक्शन = SUM (B2: OFFSET (B6, -1,0)) वर्कशीट के ऊपर सूत्र बार में दिखाई देता है।
अगली दिन के बिक्री डेटा को जोड़ना
अगले दिन के बिक्री डेटा को जोड़ने के लिए:
- संदर्भ मेनू खोलने के लिए पंक्ति 6 के लिए पंक्ति शीर्षलेख पर राइट-क्लिक करें।
- मेनू में, वर्कशीट में एक नई पंक्ति डालने के लिए सम्मिलित करें पर क्लिक करें ।
- नतीजतन, एसयूएम ऑफसेट फॉर्मूला सेल बी 7 पर चला जाता है और पंक्ति 6 अब खाली है।
- सेल ए 6 पर क्लिक करें।
- यह इंगित करने के लिए संख्या 5 दर्ज करें कि पांचवें दिन की बिक्री कुल दर्ज की जा रही है।
- सेल बी 6 पर क्लिक करें।
- $ 1458.25 नंबर टाइप करें और कीबोर्ड पर एंटर कुंजी दबाएं।
सेल बी 7 नए $ 7137.40 के लिए अपडेट करता है।
जब आप सेल बी 7 पर क्लिक करते हैं, तो अपडेटेड फॉर्मूला = एसयूएम (बी 2: ऑफसेट (बी 7, -1,0)) फॉर्मूला बार में दिखाई देता है।
नोट : ऑफ़सेट फ़ंक्शन में दो वैकल्पिक तर्क हैं: ऊंचाई और चौड़ाई, जो इस उदाहरण में छोड़ी गई थीं।
इन तर्कों का उपयोग ऑफ़सेट के आकार को आउटपुट के आकार को बताने के लिए किया जा सकता है, क्योंकि यह बहुत अधिक पंक्तियों और इतने सारे कॉलम चौड़े हैं।
इन तर्कों को छोड़कर, डिफ़ॉल्ट रूप से फ़ंक्शन, संदर्भ तर्क की ऊंचाई और चौड़ाई का उपयोग करता है, जो इस उदाहरण में एक पंक्ति उच्च और एक स्तंभ चौड़ा है।