एक्सेल फॉर्मूला: मूविंग एवरेज फॉर्मूला -

सारांश

मूविंग या रोलिंग औसत की गणना करने के लिए, आप सापेक्ष संदर्भों के साथ AVERAGE फ़ंक्शन के आधार पर एक सरल सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, E7 में सूत्र है:

=AVERAGE(C5:C7)

जैसा कि सूत्र नीचे कॉपी किया गया है, यह वर्तमान दिन और पिछले दो दिनों के लिए बिक्री मूल्य के आधार पर 3-दिवसीय चलती औसत की गणना करता है।

नीचे OFFSET फ़ंक्शन के आधार पर अधिक लचीला विकल्प है जो चर अवधि को संभालता है।

चलती औसत के बारे में

एक चलती औसत (जिसे रोलिंग औसत भी कहा जाता है) दिए गए अंतराल पर डेटा के सबसेट के आधार पर एक औसत है। विशिष्ट अंतराल पर औसत की गणना यादृच्छिक उतार-चढ़ाव के प्रभाव को कम करके डेटा को सुचारू करती है। इससे समग्र रुझानों को देखना आसान हो जाता है, खासकर एक चार्ट में। एक बढ़ते औसत की गणना करने के लिए जितना बड़ा अंतराल होता है, उतना अधिक चौरसाई होता है, क्योंकि प्रत्येक गणना औसत में अधिक डेटा बिंदु शामिल होते हैं।

स्पष्टीकरण

उदाहरण में दिखाए गए सूत्र सभी एवरेज फ़ंक्शन का उपयोग प्रत्येक विशिष्ट अंतराल के लिए एक सापेक्ष संदर्भ के साथ करते हैं। E7 में 3-दिवसीय मूविंग एवरेज की गणना AVERAGE रेंज को खिलाकर की जाती है जिसमें वर्तमान दिन और पिछले दो दिन जैसे शामिल हैं:

=AVERAGE(C5:C7) // 3-day average

5-दिन और 7-दिवसीय औसत की गणना उसी तरह से की जाती है। प्रत्येक मामले में, AVERAGE को प्रदान की गई सीमा को आवश्यक दिनों की संख्या शामिल करने के लिए बड़ा किया गया है:

=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average

सभी सूत्र AVERAGE फ़ंक्शन को दी गई सीमा के लिए एक सापेक्ष संदर्भ का उपयोग करते हैं। जैसा कि सूत्र कॉलम के नीचे कॉपी किए गए हैं, प्रत्येक औसत के लिए आवश्यक मानों को शामिल करने के लिए रेंज प्रत्येक पंक्ति में बदलती है।

जब मानों को एक लाइन चार्ट में प्लॉट किया जाता है, तो चौरसाई प्रभाव स्पष्ट होता है:

अपर्याप्त डेटा

यदि आप तालिका की पहली पंक्ति में सूत्र शुरू करते हैं, तो पहले कुछ सूत्रों में एक पूर्ण औसत की गणना करने के लिए पर्याप्त डेटा नहीं होगा, क्योंकि सीमा डेटा की पहली पंक्ति से ऊपर का विस्तार करेगी:

वर्कशीट की संरचना के आधार पर यह एक मुद्दा हो सकता है या नहीं भी हो सकता है, और क्या यह महत्वपूर्ण है कि सभी औसत समान मानों पर आधारित हों। AVERAGE फ़ंक्शन स्वचालित रूप से पाठ मानों और रिक्त कक्षों की उपेक्षा करेगा, इसलिए यह कम मूल्यों के साथ एक औसत की गणना करना जारी रखेगा। यही कारण है कि यह E5 और E6 में "काम करता है"।

अपर्याप्त डेटा को स्पष्ट रूप से इंगित करने का एक तरीका यह है कि वर्तमान पंक्ति संख्या की जांच करें और # एन के साथ गर्भपात करें जब एन मान से कम हो। उदाहरण के लिए, 3-दिन के औसत के लिए, आप उपयोग कर सकते हैं:

=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))

सूत्र का पहला भाग केवल "सामान्यीकृत" पंक्ति संख्या उत्पन्न करता है, जिसकी शुरुआत 1 से होती है:

ROW()-ROW($C$5)+1 // relative row number

पंक्ति 5 में, परिणाम 1 है, पंक्ति 6 ​​में परिणाम 2 है, और इसी तरह।

जब वर्तमान पंक्ति संख्या 3 से कम होती है, तो सूत्र # N / A देता है। अन्यथा, सूत्र पहले की तरह एक चलती औसत देता है। यह मूविंग एवरेज के विश्लेषण टूलपैक संस्करण के व्यवहार की नकल करता है, जो पहली पूर्ण अवधि तक पहुंचने तक # एन / ए आउटपुट करता है।

हालाँकि, जैसे-जैसे समय की संख्या बढ़ती है, आप अंततः डेटा से ऊपर की पंक्तियों से बाहर निकलेंगे और एवरेज के अंदर आवश्यक सीमा में प्रवेश नहीं कर पाएंगे। उदाहरण के लिए, आप दिखाए गए वर्कशीट के साथ 7-दिन की चलती औसत सेट नहीं कर सकते हैं, क्योंकि आप C5 के ऊपर 6 पंक्तियों का विस्तार करने वाली सीमा में प्रवेश नहीं कर सकते हैं।

OFFSET के साथ परिवर्तनीय अवधि

चलती औसत की गणना करने के लिए एक अधिक लचीला तरीका OFFSET फ़ंक्शन के साथ है। OFFSET एक डायनामिक रेंज बना सकता है, जिसका मतलब है कि हम एक ऐसा फॉर्मूला सेट कर सकते हैं, जहाँ पीरियड्स की संख्या परिवर्तनशील हो। सामान्य रूप है:

=AVERAGE(OFFSET(A1,0,0,-n,1))

जहां n प्रत्येक औसत में शामिल करने के लिए अवधियों की संख्या है। ऊपर के रूप में, OFFSET एक सीमा देता है जो AVERAGE फ़ंक्शन में पारित हो जाता है। नीचे आप इस फॉर्मूले को एक्शन में देख सकते हैं, जहाँ "n" नाम की रेंज E2 है। सेल C5 से शुरू होकर, OFFSET एक ऐसी सीमा का निर्माण करता है जो पिछली पंक्तियों में वापस फैली होती है। यह नकारात्मक n के बराबर ऊंचाई का उपयोग करके पूरा किया गया है। जब E5 को दूसरी संख्या में बदल दिया जाता है, तो सभी पंक्तियों पर चलती औसत पुनरावृत्ति होती है:

E5 में सूत्र, नीचे कॉपी किया गया है:

=AVERAGE(OFFSET(C5,0,0,-n,1))

ऊपर दिए गए मूल सूत्र की तरह, OFFSET वाले संस्करण में पहले कुछ पंक्तियों में अपर्याप्त डेटा की समस्या भी होगी, यह इस बात पर निर्भर करता है कि E5 में कितनी अवधि दी गई है।

दिखाए गए उदाहरण में, औसत सफलतापूर्वक गणना करता है क्योंकि AVERAGE फ़ंक्शन स्वचालित रूप से पाठ मानों और रिक्त कक्षों की उपेक्षा करता है, और C5 के ऊपर कोई अन्य संख्यात्मक मान नहीं हैं। इसलिए, जबकि E5 में AVERAGE में दी गई सीमा C1: C5 है, औसतन केवल एक मान है, 100। हालांकि, जैसे-जैसे अवधि बढ़ती है, OFFSET एक ऐसी सीमा बनाना जारी रखेगा जो डेटा की शुरुआत से ऊपर फैली हुई है, अंततः चल रही है।) वर्कशीट का शीर्ष और #REF त्रुटि लौटाता है।

एक समाधान रेंज का आकार उपलब्ध डेटा बिंदुओं की संख्या को "कैप" करना है। नीचे दिए गए अनुसार ऊंचाई के लिए उपयोग की जाने वाली संख्या को प्रतिबंधित करने के लिए MIN फ़ंक्शन का उपयोग करके यह किया जा सकता है:

=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))

यह बहुत डरावना लग रहा है, लेकिन वास्तव में काफी सरल है। हम MIN फ़ंक्शन के साथ OFFSET में पारित ऊंचाई को सीमित कर रहे हैं:

MIN(ROW()-ROW($C$5)+1,n)

MIN के अंदर, पहला मान एक सापेक्ष पंक्ति संख्या है, जिसकी गणना:

ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.

MIN को दिया गया दूसरा मान अवधियों की संख्या है, n। जब सापेक्ष पंक्ति संख्या n से कम होती है, तो MIN वर्तमान पंक्ति संख्या OFFSET को ऊँचाई पर लौटाता है। जब पंक्ति संख्या n से अधिक हो, तो MIN n वापस करता है। दूसरे शब्दों में, MIN केवल दो मानों को छोटा करता है।

OFFSET विकल्प की एक अच्छी विशेषता यह है कि n को आसानी से बदला जा सकता है। यदि हम n को 7 में बदलते हैं और परिणामों को प्लॉट करते हैं, तो हमें एक चार्ट मिलता है:

नोट: उपरोक्त OFFSET फॉर्मूले के साथ एक प्रश्न यह है कि वे Google शीट में काम नहीं करेंगे, क्योंकि शीट में OFFSET फ़ंक्शन ऊंचाई या चौड़ाई के लिए एक नकारात्मक मान नहीं होने देगा। संलग्न स्प्रेडशीट में Google शीट के लिए वर्कअराउंड सूत्र हैं।