حساب المتوسط المتحرك في Excel (بسيط وأسي ومرجح)
يعني المتوسط المتحرك أننا نحسب متوسط متوسطات مجموعة البيانات التي لدينا ، في Excel لدينا ميزة مضمنة لحساب المتوسط المتحرك والتي تتوفر في علامة تبويب تحليل البيانات في قسم التحليل ، فهي تأخذ نطاق الإدخال والإخراج النطاق مع الفواصل الزمنية كمخرجات ، والحسابات القائمة على الصيغ المجردة في التفوق لحساب المتوسط المتحرك صعبة ولكن لدينا وظيفة مضمنة في التفوق للقيام بذلك.
ما هو المتوسط المتحرك في Excel
المتوسط المتحرك هو تقنية مستخدمة على نطاق واسع في تحليل السلاسل الزمنية التي تستخدم للتنبؤ بالمستقبل. يتم إنشاء المتوسطات المتحركة في سلسلة زمنية بشكل أساسي عن طريق أخذ متوسطات القيم المتسلسلة المختلفة لبيانات السلاسل الزمنية الأخرى.
هناك ثلاثة أنواع من المتوسطات المتحركة وهي المتوسط المتحرك البسيط والمتوسط المتحرك المرجح والمتوسط المتحرك الأسي في التفوق.
# 1 - المتوسط المتحرك البسيط في Excel
يساعد المتوسط المتحرك البسيط في حساب متوسط العدد الأخير من فترات سلسلة البيانات. افترض أن أسعار الفترة س معطاة. ثم يتم إعطاء المتوسط المتحرك البسيط كـ
المتوسط المتحرك البسيط = [P1 + P2 + …………. + Pn] / n
# 2 - المتوسط المتحرك المرجح في Excel
يوفر المتوسط المتحرك المرجح المتوسط المرجح لفترات n الأخيرة. يقل الترجيح مع كل نقطة بيانات للفترة الزمنية السابقة.
المتوسط المتحرك المرجح = (السعر * عامل الترجيح) + (سعر الفترة السابقة * عامل الترجيح -1)
# 3 - المتوسط المتحرك الأسي في Excel
إنه مشابه للمتوسط المتحرك البسيط الذي يقيس الاتجاهات على مدى فترة زمنية. بينما يحسب المتوسط المتحرك البسيط متوسط بيانات معينة ، فإن المتوسط المتحرك الأسي يعلق وزنًا أكبر على البيانات الحالية.
المتوسط المتحرك الأسي = (K x (C - P)) + P.
أين،
- K = ثابت التجانس الأسي
- C = السعر الحالي
- P = المتوسط المتحرك الأسي للفترات السابقة (المتوسط المتحرك البسيط المستخدم لحساب الفترات الأولى)
كيفية حساب المتوسط المتحرك في Excel؟
فيما يلي مثال المتوسط المتحرك في Excel.
يمكنك تنزيل قالب Excel المتوسط المتحرك هذا من هنا - قالب Excel المتوسط المتحركمثال # 1 - المتوسط المتحرك البسيط في Excel
لحساب المتوسط المتحرك البسيط ، أخذنا بيانات مبيعات شركة من يناير إلى ديسمبر لعام 2018. هدفنا هو تسهيل البيانات ومعرفة رقم المبيعات في يناير 2019. سنستخدم المتوسط المتحرك لثلاثة أشهر هنا.
الخطوة 1: يتم حساب المتوسط المتحرك لشهر يناير وفبراير ومارس بأخذ مجموع رقم مبيعات الأشهر ثم تقسيمه على 3.
الخطوة 2: التحديد في زاوية الخلية D5 ثم السحب والإسقاط فقط سيعطي المتوسط المتحرك للفترات المتبقية. هذه هي وظيفة أداة الملء في Excel.
توقعات المبيعات لشهر يناير 2019 هي 10456.66667.
الخطوة 3: الآن نرسم رقم المبيعات والمتوسط المتحرك في الرسم البياني الخطي لفهم الاختلاف في الاتجاه. يمكن القيام بذلك من علامة التبويب "إدراج". أولاً ، اخترنا سلسلة البيانات ثم استخدمنا الرسم البياني الخطي من قسم الرسوم البيانية تحت الإدراج.
بعد إنشاء الرسوم البيانية ، يمكن ملاحظة أن الرسم البياني مع المتوسط المتحرك أكثر تجانسًا من سلسلة البيانات الأصلية.
مثال # 2 - المتوسط المتحرك البسيط من خلال علامة تبويب تحليل البيانات في Excel
- ضمن علامة التبويب البيانات ضمن مجموعة التحليل ، يتعين علينا النقر فوق تحليل البيانات. فيما يلي لقطة الشاشة.
- من تحليل البيانات ، يمكن الوصول إلى المتوسط المتحرك.
- بعد النقر فوق المتوسط المتحرك ، اخترنا رقم المبيعات كنطاق إدخال.
- يتم النقر على الملصقات الموجودة في الصف الأول من أجل جعل برنامج Excel يفهم أن الصف الأول يحمل اسم التصنيف.
- تم تحديد الفاصل الزمني 3 لأننا نريد متوسط متحرك لثلاث سنوات.
- لقد اخترنا نطاق الإخراج مع التقارب مع رقم المبيعات.
- نريد أيضًا أن نرى إخراج الرسم البياني حيث سنكون قادرين على رؤية الفرق بين الفعلي والمتوقع.
يوضح هذا الرسم البياني الفرق بين المتوسط المتحرك الفعلي والمتوقع.
مثال # 3 - المتوسط المتحرك المرجح في Excel
نستخدم المتوسط المتحرك المرجح لثلاث سنوات والمعادلة معطاة في لقطة الشاشة.
بعد استخدام الصيغة ، حصلنا على المتوسط المتحرك لفترة.
حصلنا على المتوسط المتحرك لجميع الفترات الأخرى عن طريق سحب القيم وإفلاتها في الخلايا التالية.
التوقعات لشهر يناير 2019 أي 10718.33
الآن أخذنا الرسم البياني الخطي لنرى تجانس البيانات. لهذا ، اخترنا البيانات المتوقعة لشهرنا ثم أدخلنا رسمًا بيانيًا خطيًا.
الآن سنقارن بياناتنا المتوقعة مع بياناتنا الفعلية. في لقطات الشاشة أدناه ، يمكننا بسهولة رؤية الفرق بين البيانات الفعلية والبيانات المتوقعة. يمثل الرسم البياني في الأعلى البيانات الفعلية والرسم البياني أدناه يمثل المتوسط المتحرك والبيانات المتوقعة. يمكننا أن نرى أن الرسم البياني للمتوسط المتحرك قد أصبح أكثر سلاسة مقارنة بالرسم البياني الذي يحتوي على البيانات الفعلية.
مثال # 4 - المتوسط المتحرك الأسي في Excel
صيغة المتوسط المتحرك الأسي هي S t = α.Y t-1 + (1- α) S t-1 …… (1)
أين،
- Y t-1 = الملاحظة الفعلية في الفترة t-1
- S t-1 = متوسط متحرك بسيط في الفترة t-1
- α = عامل التنعيم ويتراوح بين 0.1 و 0.3. كلما زادت قيمة α الأقرب هو الرسم البياني للقيم الفعلية وتقليل قيمة α ، سيكون المخطط أكثر سلاسة.
أولاً ، نحسب المتوسط المتحرك البسيط كما هو موضح سابقًا. بعد ذلك ، نطبق الصيغة الواردة في المعادلة (1). لتحديد قيمة α لجميع القيم التالية ، قمنا بالضغط على F4.
نحصل على القيم عن طريق السحب والإفلات في الخلايا التالية.
الآن ، نريد أن نرى المقارنة بين القيم الفعلية والمتوسط المتحرك البسيط والمتوسط المتحرك الأسي في التفوق. لقد فعلنا ذلك من خلال عمل مخطط خطي.
من لقطة الشاشة أعلاه ، يمكننا أن نرى الفرق بين رقم المبيعات الفعلي والمتوسط المتحرك البسيط والمتوسط المتحرك الأسي في التفوق.
أشياء يجب تذكرها حول المتوسط المتحرك في Excel
- يمكن حساب المتوسط المتحرك البسيط باستخدام دالة AVERAGE في Excel
- يساعد المتوسط المتحرك في تسهيل البيانات
- غالبًا ما يطلق على المتوسطات الموسمية مؤشر موسمي
- يعطي المتوسط المتحرك الأسي في Excel وزناً أكبر للبيانات الحديثة من المتوسط المتحرك البسيط. لذلك فإن التسوية في حالة المتوسط المتحرك الأسي في التفوق هي أكثر من المتوسط المتحرك البسيط.
- في الأعمال التجارية مثل سوق الأوراق المالية ، يساعد المتوسط المتحرك المتداول على تحديد الاتجاه بسهولة أكبر.
