डेटाच्या गतिशील श्रेणीसाठी एकूण शोधण्यासाठी SUM आणि OFFSET वापरा
आपल्या एक्सेल वर्कशीटमध्ये एसएएम आणि OFFSET फंक्शन एकत्र वापरुन सेलच्या बदलत्या श्रेणीवर आधारित गणिते समाविष्ट होतात तर SUM OFFSET फॉर्मूला एकत्रितपणे गणित ठेवणे अद्ययावत ठेवण्याचे कार्य सोपे करते.
SUM आणि OFFSET कार्यांसह एक डायनॅमिक रेंज तयार करा
सतत बदलते अशा काळासाठी आपण गणना वापरत असल्यास - जसे महिन्यातल्या एकूण विक्री - ऑफसेट फंक्शन आपल्याला एक गतिमान श्रेणी सेट करण्याची परवानगी देते जी प्रत्येक दिवसाच्या विक्री आकडेवारी जोडल्या जात आहे म्हणून बदलत असते
स्वत: हून, SUM फंक्शन सामान्यत: श्रेणीत समाविष्ट केल्या जाणार्या डेटाच्या नवीन सेलची पूर्तता करते.
एक अपवाद तब घडतो जेव्हा डेटा सेलमध्ये घातला जातो जिथे फंक्शन सध्या अस्तित्वात आहे.
या लेखासह असलेल्या उदाहरण प्रतिमेमध्ये प्रत्येक दिवसाच्या नवीन विक्रय आकडेवारी सूचीच्या तळाशी जोडल्या जातात, ज्यामुळे प्रत्येक वेळी नवीन डेटा जोडला जाताना प्रत्येक पेशी सतत सतत बदलत राहतो.
जर SUM फंक्शनचा वापर संपूर्ण डेटावर केला गेला असेल तर प्रत्येक वेळी नवीन डेटा समाविष्ट केला जाणारा फंक्शनच्या वितर्क म्हणून वापरल्या जाणार्या सेलची श्रेणी सुधारणे आवश्यक आहे.
एकत्र SUM आणि OFFSET फंक्शन्स वापरून, तथापि, एकूण भरलेली श्रेणी गतिमान होते. दुसऱ्या शब्दांत, डेटाच्या नवीन पेशींना सामावून घेण्यासाठी हे बदलते. डेटाच्या नवीन पेशी वाढवण्यामुळे अडचणी उद्भवत नाहीत कारण श्रेणी प्रत्येक नवीन सेल जोडल्याप्रमाणे समायोजित होत चालली आहेत.
वाक्यरचना आणि आर्ग्युमेंटस
या ट्युटोरियलसह अनुसरण करण्यासाठी या लेखासह असलेल्या प्रतिमा पहा.
या सूत्रामध्ये, SUM फंक्शनचा वापर त्याच्या वितर्काप्रमाणे प्रदान केलेल्या डेटाच्या श्रेणीसाठी केला जातो. या श्रेणीसाठी प्रारंभ बिंदू स्थिर आहे आणि सूत्रानुसार एकूण प्रथम क्रमांकाचा सेल संदर्भ म्हणून ओळखला जातो.
OFFSET फंक्शन SUM फंक्शनमध्ये नेस्ट केले आहे आणि सूत्राद्वारे भरलेल्या डेटाच्या श्रेणीसाठी एक गतिमान अंत्यबिंदू तयार करण्यासाठी वापरला जातो. हे सूत्राच्या स्थानाच्या वरच्या कक्षापर्यंतच्या एका कक्षापर्यंत पोहोचल्यावर हे साधले जाते.
सूत्र च्या वाक्यरचना :
= SUM (श्रेणी प्रारंभ: OFFSET (संदर्भ, पंक्ती, स्तंभ))श्रेणी प्रारंभ - (आवश्यक) SUM फंक्शनद्वारे भरलेल्या सेलची श्रेणीसाठी प्रारंभ बिंदू. उदाहरणार्थ प्रतिमा मध्ये, हा सेल B2 आहे.
संदर्भ - (आवश्यक) श्रेणीच्या शेवटच्या पॉइण्टची गणना करण्यासाठी वापरलेल्या सेल संदर्भामध्ये अनेक पंक्ती आणि स्तंभ दूर आहेत. उदाहरणार्थ प्रतिमा मध्ये, सूत्र नेहमीच सूत्रापेक्षा एका सेलने समाप्त करण्याची आमची इच्छा आहे कारण संदर्भ आर्ग्युमेंट हा सूत्रांकरिता सेल संदर्भ आहे.
पंक्ती - (आवश्यक) ऑफसेटची गणना करण्यासाठी वापरलेल्या संदर्भ वितणाच्या वरील किंवा खालील पंक्तीची संख्या. हे मूल्य धन, ऋण किंवा शून्यावर सेट होऊ शकते.
ऑफसेटचे स्थान संदर्भ आर्ग्युमेंटच्या वर असल्यास, हे मूल्य नकारात्मक आहे. जर तो खाली असेल तर, पंक्ती वाद दर्शविला जातो. ऑफसेट एकाच ओळीत असल्यास, हा वितर्क शून्य आहे. या उदाहरणात, ऑफसेट आरंभीक वितर्कच्या वर एक पंक्ती पासून सुरू होते, म्हणून या वितर्कचे मूल्य नकारात्मक एक आहे (-1).
स्तंभ - (आवश्यक) ओफ़्सेटची गणना करण्यात वापरलेल्या संदर्भ वितर्कांच्या डाव्या किंवा उजव्या स्तंभाची संख्या. हे मूल्य धन, ऋण किंवा शून्यावर सेट होऊ शकते
ऑफसेटचे स्थान संदर्भ आर्ग्युमेंटच्या डाव्या बाजूस असल्यास, हे मूल्य नकारात्मक आहे उजवीकडे असल्यास, कॉल्स दंड सकारात्मक आहे या उदाहरणामध्ये, भरलेला डेटा सूत्रांप्रमाणे समान स्तंभामध्ये आहे त्यामुळे या वितर्कचे मूल्य शून्य आहे.
एकूण विक्री डेटावर SUM OFFSET फॉर्म्युला वापरणे
कार्यपत्रकाच्या स्तंभ B मध्ये सूचीबद्ध दैनिक विक्री आकडेवारी साठी एकूण परत करण्यासाठी हे उदाहरण SUM OFFSET सूत्र वापरते
प्रारंभी, सूत्र सेल बी 6 मध्ये प्रविष्ट केला आणि चार दिवसांसाठी विक्री डेटा भरला.
पुढील चरण म्हणजे पाचव्या दिवसाच्या एकूण विक्रीसाठी जागा बनविण्यासाठी सॅम ऑफसेट फॉर्मुला एका ओळीत हलवावी.
हे नवीन पंक्ती 6 घालून साधले आहे, जे सूत्र 1 पंक्तीपर्यंत खाली हलवते.
हलण्याच्या परिणामांप्रमाणे, एक्सेल आपोआप सूत्र B7 सेलच्या संदर्भ वितरणास अद्ययावत करतो आणि सेल B6 ला सूत्राद्वारे नमूद केलेल्या श्रेणीस जोडतो.
SUM OFFSET फॉर्म्युला प्रविष्ट करणे
- सेल B6 वर क्लिक करा, जे स्थान आहे जेथे सूत्र परिणाम सुरूवातीला प्रदर्शित केले जाईल.
- रिबन मेनूच्या फॉर्मुला टॅबवर क्लिक करा
- फंक्शन ड्रॉप-डाउन सूची उघडण्यासाठी रिबनमधून मठ आणि त्रिग निवडा.
- फंक्शनच्या डायलॉग बॉक्सची सुरूवात करण्यासाठी SUM वर क्लिक करा.
- डायलॉग बॉक्स मध्ये, Number1 line वर क्लिक करा.
- डायलॉग बॉक्समधील हा कक्ष संदर्भ प्रविष्ट करण्यासाठी सेल B2 वर क्लिक करा. हे स्थान सूत्राच्या स्थिर अंत्यबिंदू आहे;
- डायलॉग बॉक्स मध्ये, Number2 line वर क्लिक करा.
- सूत्रासाठी डायनॅमिक अंत्यबिंदू लावण्यासाठी खालील OFFSET फंक्शन: ऑफसेट (बी 6, -1 0) प्रविष्ट करा.
- फंक्शन पूर्ण करण्यासाठी ओके क्लिक करा आणि डायलॉग बॉक्स बंद करा.
एकूण $ 5679.15 सेल B7 मध्ये दिसते
जेव्हा आपण सेल B3 वर क्लिक करता तेव्हा पूर्ण कार्य = SUM (B2: OFFSET (B6, -1.0)) कार्यपत्रकाच्या वरील सूत्र बारमध्ये दिसते.
पुढील दिवस विक्री डेटा जमा करणे
पुढील दिवसाचा विक्री डेटा जोडण्यासाठी:
- संदर्भ मेनू उघडण्यासाठी पंक्ति 6 साठी पंक्ति शीर्षकावर उजवे-क्लिक करा.
- मेनूमध्ये, कार्यपत्रकात एक नवीन पंक्ती घालण्यासाठी निविष्ट करा वर क्लिक करा
- परिणामी, SUM OFFSET फॉर्म्युला सेल B7 वर खाली सरकतो आणि पंक्ती 6 आता रिक्त आहे
- सेल A6 वर क्लिक करा.
- पाचव्या दिवसासाठी विक्री एकूण नोंदवली जात आहे हे सूचित करण्यासाठी संख्या 5 प्रविष्ट करा.
- सेल B6 वर क्लिक करा.
- संख्या टाइप करा $ 1458.25 आणि कीबोर्डवरील एंटर की दाबा.
सेल B7 अद्यतने $ 7137.40 नवीन एकूण .
जेव्हा आपण सेल B7 वर क्लिक करता, तेव्हा अद्ययावत सूत्र = SUM (B2: OFFSET (B7, -1.0)) सूत्र बारमध्ये दिसते.
टीप : OFFSET फंक्शनमध्ये दोन पर्यायी आर्ग्यूमेंट्स आहेत: उंची आणि रुंदी, जी या उदाहरणात वगळली गेली होती.
हे आर्ग्यूमेंट्स ऑफफिएसटीला हे सांगण्यासाठी वापरला जाऊ शकतो की आउटपुटचे आकार आणि इतके पंक्ति अधिक असल्याने आणि कित्येक स्तंभ रुंद आहेत.
हे आर्ग्यूमेंट काढून टाकून, फंक्शन डीफॉल्टनुसार, संदर्भ वितर्काच्या उंची आणि रुंदीचा वापर करते, जे या उदाहरणामध्ये एक पंक्ती उच्च आणि एक स्तंभ रुंद आहे.