طريقة المتوسط ​​المتحرك هي أداة إحصائية يمكنك من خلالها حل العديد من المهام. على وجه الخصوص ، غالبا ما يستخدم في التنبؤ. في Excel ، يمكنك أيضًا استخدام هذه الأداة لحل عدد من المهام. دعونا نرى كيف يتم استخدام المتوسط ​​المتحرك في Excel.

تطبيق المتوسط ​​المتحرك

معنى هذه الطريقة هو أنه مع مساعدتها هناك تغيير في القيم الديناميكية المطلقة للسلسلة المختارة لمتوسط ​​حسابي خلال فترة معينة عن طريق تجانس البيانات. تُستخدم هذه الأداة في الحسابات الاقتصادية ، والتنبؤ ، وفي عملية التداول في البورصة ، إلخ. من الأفضل القيام بتطبيق أسلوب Moving Average في Excel بمساعدة أداة معالجة بيانات إحصائية قوية تسمى حزمة التحليل . بالإضافة إلى ذلك ، لنفس الغرض ، يمكنك استخدام الدالة المضمنة Excel AVERAGE .

الطريقة 1: حزمة التحليل

حزمة التحليل هي وظيفة إضافية لبرنامج Excel معطلة بشكل افتراضي. لذلك ، أولاً وقبل كل شيء ، فإنه مطلوب لإدراجه.

  1. ننتقل إلى علامة التبويب "ملف" . انقر على البند "خيارات" .
  2. انتقل إلى خيارات في Microsoft Excel

  3. في النافذة التي تفتح ، انتقل إلى قسم "الإضافات" . في الجزء السفلي من الإطار ، يجب تعيين معلمة "وظائف Excel الإضافية" في حقل "الإدارة" . انقر فوق الزر "Go" .
  4. انتقل إلى الوظائف الإضافية في Microsoft Excel

  5. ندخل في نافذة الوظائف الإضافية. حدد المربع المجاور لـ "حزمة التحليل" وانقر على زر "موافق" .

نافذة الوظيفة الإضافية في Microsoft Excel

بعد هذا الإجراء ، يتم تنشيط حزمة "تحليل البيانات" ، ويظهر الزر المقابل على الشريط في علامة التبويب "بيانات" .

والآن دعونا نلقي نظرة على كيفية استخدامك المباشر لقدرات تحليل البيانات للبيانات الخاصة بطريقة المتوسط ​​المتحرك. دعونا نجعل توقعات للشهر الثاني عشر على أساس معلومات حول دخل الشركة للفترات 11 السابقة. للقيام بذلك ، نستخدم جدول معبأ بالبيانات ، بالإضافة إلى أدوات أداة التحليل .

  1. انتقل إلى علامة التبويب "البيانات" وانقر على زر "تحليل البيانات" ، الموجود على حزام الأداة في كتلة "التحليل" .
  2. انتقل إلى أدوات تحليل البيانات في Microsoft Excel

  3. يتم فتح قائمة بالأدوات المتاحة في حزمة التحليل . اختر منهم الاسم "المتوسط ​​المتحرك" وانقر على زر "موافق" .
  4. قائمة أشرطة أدوات التحليل في Microsoft Excel

  5. يتم إطلاق نافذة إدخال البيانات للتنبؤ باستخدام طريقة المتوسط ​​المتحرك.

    في الحقل "فاصل الإدخال" ، حدد عنوان النطاق الذي يتم فيه حساب العائد الشهري بدون الخلية ، ويجب حساب البيانات التي يجب حسابها.

    في حقل "الفاصل الزمني" ، حدد الفاصل الزمني لمعالجة القيم باستخدام أسلوب الصقل. أولاً ، دعنا نضع قيمة الصقل إلى ثلاثة أشهر ، وبالتالي ندخل الرقم "3" .

    في الحقل "Output interval" ، يجب تحديد نطاق فارغ عشوائي على الورقة حيث سيتم إخراج البيانات بعد المعالجة ، والتي يجب أن تكون خلية أكبر من فاصل الإدخال.

    تحقق أيضًا من مربع "الأخطاء القياسية" .

    إذا لزم الأمر ، يمكنك أيضًا تحديد المربع "إخراج الرسم البياني" لعرض توضيحي مرئي ، على الرغم من أن هذا ليس ضروريًا في حالتنا.

    بعد الانتهاء من جميع الإعدادات ، انقر فوق الزر "موافق" .

  6. أداة تحليل البيانات نافذة تحريك المتوسط ​​في مايكروسوفت اكسل

  7. يعرض البرنامج نتيجة للمعالجة.
  8. نتيجة معالجة التنعيم لمدة 3 أشهر في Microsoft Excel

  9. الآن ، قم بإجراء التمهل لفترة شهرين لتحديد النتيجة الأكثر صحة. لهذه الأغراض ، نقوم مرة أخرى بتشغيل أداة "Moving Average" لحزمة التحليل .

    في حقل "فاصل الإدخال" نترك نفس القيم كما في الحالة السابقة.

    في حقل "الفاصل الزمني" نضع الرقم "2" .

    في الحقل "الفترة الزمنية للإخراج" ، نحدد عنوان المدى الفارغ الجديد ، والذي يجب أن يكون مرة أخرى عبارة عن خلية أكبر من فاصل الإدخال.

    بقية الإعدادات هي نفسها. بعد ذلك ، انقر على زر "موافق" .

  10. نافذة أداة تحليل البيانات المتحركة المتوسط ​​في Microsoft Excel

  11. بعد ذلك ، يقوم البرنامج بحساب وعرض النتيجة على الشاشة. من أجل تحديد أي من النموذجين أكثر دقة ، نحتاج إلى مقارنة الأخطاء القياسية. كلما كان المؤشر أصغر ، كلما زادت احتمالية دقة النتيجة التي تم الحصول عليها. كما نرى ، بالنسبة إلى جميع القيم ، يكون الخطأ القياسي في حساب المتداول لمدة شهرين أقل من نفس المؤشر لمدة 3 أشهر. وبالتالي ، يمكن اعتبار القيمة المتوقعة لشهر ديسمبر القيمة المحسوبة بواسطة الطريقة المنزلقة للفترة الأخيرة. في حالتنا ، هذه القيمة هي 990.4 ألف روبل.

نتيجة معالجة التهدئة لمدة شهرين في Microsoft Excel

الطريقة الثانية: استخدم الدالة AVERAGE

في Excel ، هناك طريقة أخرى لتطبيق طريقة المتوسط ​​المتحرك. لاستخدامها ، تحتاج إلى تطبيق عدد من الوظائف القياسية للبرنامج ، والتي يكون هدفها الأساسي هو AVERAGE . على سبيل المثال ، سوف نستخدم نفس جدول دخل الشركة كما في الحالة الأولى.

كما في المرة الأخيرة ، نحتاج إلى إنشاء سلسلة زمنية سلسة. ولكن هذه المرة لن تكون الإجراءات آلية بشكل تلقائي. يجب عليك حساب متوسط ​​القيمة لكل اثنين ، ثم ثلاثة أشهر ، حتى تتمكن من مقارنة النتائج.

أولاً ، نقوم بحساب متوسط ​​القيم للفترتين السابقتين باستخدام الدالة AVERAGE . يمكننا القيام بذلك فقط ابتداء من شهر مارس ، لأنه في تواريخ لاحقة هناك انقطاع في القيم.

  1. حدد الخلية في العمود الفارغ في الصف لشهر مارس. بعد ذلك ، انقر فوق رمز "إدراج الدالة" ، والذي يقع بالقرب من سطر الصيغة.
  2. انتقل إلى معالج الدالة في Microsoft Excel

  3. يتم تنشيط نافذة المعالجات . في الفئة "إحصائي" ، نبحث عن القيمة "AVERAGE" ، ثم حددها وانقر على زر "OK" .
  4. الانتقال إلى الوسائط الدالة AVERAGE في Microsoft Excel

  5. يتم تشغيل نافذة الوسائط لبيان AVERAGE . بناء الجملة كما يلي:

    =СРЗНАЧ(число1;число2;…)

    مطلوب وسيطة واحدة فقط.

    في حالتنا ، في حقل "الرقم 1" ، يجب أن نحدد رابطًا للنطاق حيث يشار إلى إيرادات الفترتين السابقتين (يناير وفبراير). عيّن المؤشر في الحقل وحدد الخلايا المقابلة في الورقة في عمود "الأرباح" . بعد ذلك ، انقر على زر "موافق" .

  6. وسائط الدالة AVERAGE في Microsoft Excel

  7. كما ترى ، تم عرض نتيجة حساب المتوسط ​​للفترتين السابقتين في الخلية. من أجل إجراء مثل هذه الحسابات لجميع الأشهر الأخرى من الفترة ، نحتاج إلى نسخ هذه الصيغة إلى الخلايا الأخرى. للقيام بذلك ، أصبحنا المؤشر في الزاوية اليمنى السفلى للخلية التي تحتوي على الدالة. يتم تحويل المؤشر إلى علامة ملء ، والتي تبدو كالصليب. ثبّت زر الماوس الأيسر واسحبه لأسفل حتى نهاية العمود.
  8. حشو في مايكروسوفت اكسل

  9. نحصل على حساب نتائج المتوسط ​​للشهرين السابقين قبل نهاية العام.
  10. المتوسط ​​خلال الشهرين السابقين في Microsoft Excel

  11. الآن حدد الخلية في العمود الفارغ التالي في الصف لشهر أبريل. نحن نطلق على نافذة الحجج الدالة AVERAGE بالطريقة نفسها كما هو موضح سابقًا. في الحقل "Number1" ، ندخل إحداثيات الخلايا في عمود "الأرباح" من كانون الثاني إلى آذار. ثم انقر على زر "موافق" .
  12. وسيطات دالة AVERAGE لمدة 3 أشهر في Microsoft Excel

  13. باستخدام علامة التعبئة ، انسخ الصيغة إلى خلايا الجدول أدناه.
  14. تطبيق مقبض تعبئة في Microsoft Excel

  15. لذا ، قمنا بحساب القيم. الآن ، كما في المرة السابقة ، سوف نحتاج إلى معرفة أي نوع من التحليل يكون أفضل: مع تمهيد لمدة شهرين أو ثلاثة أشهر. للقيام بذلك ، قم بحساب متوسط ​​الانحراف المربع وبعض المؤشرات الأخرى. أولا ، نحسب الانحراف المطلق باستخدام وظيفة Excel القياسية ABS ، والتي بدلا من الأرقام الإيجابية أو السالبة ترجع لوحدتها. ستكون هذه القيمة مساوية للفرق بين رقم الإيرادات الفعلي للشهر المختار والمتوقع. اضبط المؤشر على العمود الفارغ التالي في صف مايو. استدعاء معالج الدالة .
  16. إدراج وظيفة في مايكروسوفت إكسل

  17. في فئة "رياضيات" نسلط الضوء على اسم الدالة "ABS" . انقر على زر "موافق" .
  18. الانتقال إلى الوسائط الدالة ABS في Microsoft Excel

  19. يتم فتح نافذة وسائط الدالة ABS . في حقل "الرقم" الفردي ، نشير إلى الفرق بين محتويات الخلايا في الأعمدة "الأرباح" و "شهرين" لشهر مايو. ثم انقر على زر "موافق" .
  20. وسيطات الدالة ABS في Microsoft Excel

  21. باستخدام الحشو ، انسخ هذه الصيغة إلى جميع صفوف الجدول إلى نوفمبر شاملة.
  22. انحرافات مطلقة في Microsoft Excel

  23. نحن نحسب متوسط ​​قيمة الانحراف المطلق طوال الفترة بمساعدة الدالة المألوفة AVERAGE .
  24. متوسط ​​قيمة الانحراف المطلق في Microsoft Excel

  25. نقوم بإجراء مماثل من أجل حساب الانحراف المطلق للواحد المتحرك في 3 أشهر. قم أولاً بتطبيق وظيفة ABS . فقط هذه المرة نعتبر الفرق بين محتويات الخلايا مع الدخل الفعلي والمخطط لها ، محسوبة بواسطة طريقة المتوسط ​​المتحرك لمدة 3 أشهر.
  26. انحرافات مطلقة لمدة 3 أشهر في Microsoft Excel

  27. بعد ذلك ، قم بحساب متوسط ​​جميع بيانات الانحراف المطلق باستخدام الدالة AVERAGE .
  28. متوسط ​​قيمة الانحراف المطلق لمدة 3 أشهر في Microsoft Excel

  29. الخطوة التالية هي حساب الانحراف النسبي. وهي تساوي نسبة الانحراف المطلق إلى المؤشر الفعلي. من أجل تجنب القيم السلبية ، نستخدم مرة أخرى الفرص التي يوفرها مشغل ABS . في هذه المرة باستخدام هذه الوظيفة ، نقوم بتقسيم قيمة الانحراف المطلق باستخدام طريقة المتوسط ​​المتحرك لمدة شهرين للدخل الفعلي للشهر المحدد.
  30. الرفض النسبي في Microsoft Excel

  31. ولكن عادة ما يتم عرض الانحراف النسبي كنسبة مئوية. لذلك ، حدد النطاق المناسب على الورقة ، انتقل إلى علامة التبويب "الصفحة الرئيسية" ، حيث في مربع "الرقم" ، في حقل التنسيق الخاص ، اضبط تنسيق النسبة المئوية. بعد ذلك ، يتم عرض نتيجة حساب الانحراف النسبي في المئة.
  32. تغيير التنسيق في Microsoft Excel

  33. يتم إجراء عملية مماثلة لحساب الانحراف النسبي باستخدام البيانات باستخدام تجانس لمدة 3 أشهر. فقط في هذه الحالة لحساب القسمة نستخدم عمود آخر من الجدول ، والذي لدينا اسم "عبس. قبالة (3M) " . ثم نترجم القيم العددية إلى النسبة المئوية.
  34. الانحراف النسبي لخط الانزلاق لمدة شهرين في Microsoft Excel

  35. بعد ذلك ، احسب متوسط ​​القيم لكل من الأعمدة ذات الانحراف النسبي ، كما كان من قبل باستخدام الدالة AVERAGE . نظرًا لأننا نستخدم قيم النسبة المئوية كوسيطة للدالة ، فلا نحتاج إلى تحويلها. يقوم المشغل بإخراج النتيجة بتنسيق النسبة المئوية.
  36. القيم المتوسطة للانحراف النسبي في Microsoft Excel

  37. الآن وصلنا إلى حساب متوسط ​​الانحراف مربع. سيسمح لنا هذا المؤشر بمقارنة نوعية الحساب بشكل مباشر عند استخدام مانع التعرج لمدة شهرين وثلاثة أشهر. في حالتنا ، سيكون الانحراف المعياري مساويًا للجذر التربيعي لمجموع مربعات الاختلافات في الأرباح الفعلية والمتوسط ​​المتحرك مقسومًا على عدد الأشهر. من أجل إجراء عملية حسابية في البرنامج ، يتعين علينا استخدام عدد من الوظائف ، لا سيما ROOT و SUMMKVRAZN و ACCOUNT . على سبيل المثال ، لحساب متوسط ​​الانحراف المربع عند استخدام خط التجانس لمدة شهرين في مايو ، سيتم استخدام الصيغة التالية في حالتنا:

    =КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))

    قم بنسخه إلى خلايا أخرى في العمود مع حساب متوسط ​​الانحراف المربع بواسطة علامة التعبئة.

  38. حساب الانحراف المعياري في Microsoft Excel

  39. يتم إجراء عملية مماثلة لحساب متوسط ​​الانحراف المربعي للمتوسط ​​المتحرك لمدة 3 أشهر.
  40. حساب الانحراف المعياري للمتوسط ​​المتحرك لمدة 3 أشهر في Microsoft Excel

  41. بعد ذلك ، نقوم بحساب متوسط ​​القيمة للفترة بأكملها لكل من هذين المؤشرين ، مع تطبيق الدالة AVERAGE .
  42. متوسط ​​الانحراف المعياري في Microsoft Excel

  43. من خلال مقارنة الحسابات باستخدام طريقة المتوسط ​​المتحرك مع التمهل عند 2 و 3 أشهر لمثل هذه المؤشرات مثل الانحراف المطلق والانحراف النسبي وانحراف الجذر - الوسط المربع ، يمكننا القول على وجه اليقين أن التمهل لمدة شهرين يعطي نتائج أكثر موثوقية من تطبيق التنعيم في ثلاثة أشهر. ويتضح ذلك من خلال حقيقة أن المؤشرات المذكورة أعلاه لمعدل متحرك لمدة شهرين أقل من ثلاثة أشهر.
  44. مقاييس التعيين في Microsoft Excel

  45. وبالتالي ، فإن الدخل المتوقع للشركة لشهر ديسمبر سيكون 990.4 ألف روبل. كما ترون ، تتطابق هذه القيمة تمامًا مع القيمة التي حصلنا عليها عن طريق إجراء العمليات الحسابية باستخدام أدوات حزمة التحليل .

الإيرادات المتوقعة في Microsoft Excel

الدرس: معالج الدالة في Excel

حسبنا التنبؤ باستخدام طريقة المتوسط ​​المتحرك بطريقتين. كما ترى ، هذا الإجراء أسهل بكثير باستخدام أدوات حزمة التحليل . ومع ذلك ، لا يثق بعض المستخدمين دائمًا بالحساب التلقائي ويفضلون استخدام الدالة AVERAGE والمشغلين المرافقين لإجراء عمليات حسابية للتحقق من الإصدار الأكثر موثوقية. على الرغم من ذلك ، إذا كان كل شيء يتم بشكل صحيح ، يجب أن يكون ناتج الحساب نفسه تمامًا عند الإخراج.