طريقة نقل المتوسط في مايكروسوفت اكسل
طريقة المتوسط المتحرك هي أداة إحصائية يمكنك من خلالها حل العديد من المهام. على وجه الخصوص ، غالبا ما يستخدم في التنبؤ. في Excel ، يمكنك أيضًا استخدام هذه الأداة لحل عدد من المهام. دعونا نرى كيف يتم استخدام المتوسط المتحرك في Excel.
محتوى
تطبيق المتوسط المتحرك
معنى هذه الطريقة هو أنه مع مساعدتها هناك تغيير في القيم الديناميكية المطلقة للسلسلة المختارة لمتوسط حسابي خلال فترة معينة عن طريق تجانس البيانات. تُستخدم هذه الأداة في الحسابات الاقتصادية ، والتنبؤ ، وفي عملية التداول في البورصة ، إلخ. من الأفضل القيام بتطبيق أسلوب Moving Average في Excel بمساعدة أداة معالجة بيانات إحصائية قوية تسمى حزمة التحليل . بالإضافة إلى ذلك ، لنفس الغرض ، يمكنك استخدام الدالة المضمنة Excel AVERAGE .
الطريقة 1: حزمة التحليل
حزمة التحليل هي وظيفة إضافية لبرنامج Excel معطلة بشكل افتراضي. لذلك ، أولاً وقبل كل شيء ، فإنه مطلوب لإدراجه.
- ننتقل إلى علامة التبويب "ملف" . انقر على البند "خيارات" .
- في النافذة التي تفتح ، انتقل إلى قسم "الإضافات" . في الجزء السفلي من الإطار ، يجب تعيين معلمة "وظائف Excel الإضافية" في حقل "الإدارة" . انقر فوق الزر "Go" .
- ندخل في نافذة الوظائف الإضافية. حدد المربع المجاور لـ "حزمة التحليل" وانقر على زر "موافق" .
بعد هذا الإجراء ، يتم تنشيط حزمة "تحليل البيانات" ، ويظهر الزر المقابل على الشريط في علامة التبويب "بيانات" .
والآن دعونا نلقي نظرة على كيفية استخدامك المباشر لقدرات تحليل البيانات للبيانات الخاصة بطريقة المتوسط المتحرك. دعونا نجعل توقعات للشهر الثاني عشر على أساس معلومات حول دخل الشركة للفترات 11 السابقة. للقيام بذلك ، نستخدم جدول معبأ بالبيانات ، بالإضافة إلى أدوات أداة التحليل .
- انتقل إلى علامة التبويب "البيانات" وانقر على زر "تحليل البيانات" ، الموجود على حزام الأداة في كتلة "التحليل" .
- يتم فتح قائمة بالأدوات المتاحة في حزمة التحليل . اختر منهم الاسم "المتوسط المتحرك" وانقر على زر "موافق" .
- يتم إطلاق نافذة إدخال البيانات للتنبؤ باستخدام طريقة المتوسط المتحرك.
في الحقل "فاصل الإدخال" ، حدد عنوان النطاق الذي يتم فيه حساب العائد الشهري بدون الخلية ، ويجب حساب البيانات التي يجب حسابها.
في حقل "الفاصل الزمني" ، حدد الفاصل الزمني لمعالجة القيم باستخدام أسلوب الصقل. أولاً ، دعنا نضع قيمة الصقل إلى ثلاثة أشهر ، وبالتالي ندخل الرقم "3" .
في الحقل "Output interval" ، يجب تحديد نطاق فارغ عشوائي على الورقة حيث سيتم إخراج البيانات بعد المعالجة ، والتي يجب أن تكون خلية أكبر من فاصل الإدخال.
تحقق أيضًا من مربع "الأخطاء القياسية" .
إذا لزم الأمر ، يمكنك أيضًا تحديد المربع "إخراج الرسم البياني" لعرض توضيحي مرئي ، على الرغم من أن هذا ليس ضروريًا في حالتنا.
بعد الانتهاء من جميع الإعدادات ، انقر فوق الزر "موافق" .
- يعرض البرنامج نتيجة للمعالجة.
- الآن ، قم بإجراء التمهل لفترة شهرين لتحديد النتيجة الأكثر صحة. لهذه الأغراض ، نقوم مرة أخرى بتشغيل أداة "Moving Average" لحزمة التحليل .
في حقل "فاصل الإدخال" نترك نفس القيم كما في الحالة السابقة.
في حقل "الفاصل الزمني" نضع الرقم "2" .
في الحقل "الفترة الزمنية للإخراج" ، نحدد عنوان المدى الفارغ الجديد ، والذي يجب أن يكون مرة أخرى عبارة عن خلية أكبر من فاصل الإدخال.
بقية الإعدادات هي نفسها. بعد ذلك ، انقر على زر "موافق" .
- بعد ذلك ، يقوم البرنامج بحساب وعرض النتيجة على الشاشة. من أجل تحديد أي من النموذجين أكثر دقة ، نحتاج إلى مقارنة الأخطاء القياسية. كلما كان المؤشر أصغر ، كلما زادت احتمالية دقة النتيجة التي تم الحصول عليها. كما نرى ، بالنسبة إلى جميع القيم ، يكون الخطأ القياسي في حساب المتداول لمدة شهرين أقل من نفس المؤشر لمدة 3 أشهر. وبالتالي ، يمكن اعتبار القيمة المتوقعة لشهر ديسمبر القيمة المحسوبة بواسطة الطريقة المنزلقة للفترة الأخيرة. في حالتنا ، هذه القيمة هي 990.4 ألف روبل.
الطريقة الثانية: استخدم الدالة AVERAGE
في Excel ، هناك طريقة أخرى لتطبيق طريقة المتوسط المتحرك. لاستخدامها ، تحتاج إلى تطبيق عدد من الوظائف القياسية للبرنامج ، والتي يكون هدفها الأساسي هو AVERAGE . على سبيل المثال ، سوف نستخدم نفس جدول دخل الشركة كما في الحالة الأولى.
كما في المرة الأخيرة ، نحتاج إلى إنشاء سلسلة زمنية سلسة. ولكن هذه المرة لن تكون الإجراءات آلية بشكل تلقائي. يجب عليك حساب متوسط القيمة لكل اثنين ، ثم ثلاثة أشهر ، حتى تتمكن من مقارنة النتائج.
أولاً ، نقوم بحساب متوسط القيم للفترتين السابقتين باستخدام الدالة AVERAGE . يمكننا القيام بذلك فقط ابتداء من شهر مارس ، لأنه في تواريخ لاحقة هناك انقطاع في القيم.
- حدد الخلية في العمود الفارغ في الصف لشهر مارس. بعد ذلك ، انقر فوق رمز "إدراج الدالة" ، والذي يقع بالقرب من سطر الصيغة.
- يتم تنشيط نافذة المعالجات . في الفئة "إحصائي" ، نبحث عن القيمة "AVERAGE" ، ثم حددها وانقر على زر "OK" .
- يتم تشغيل نافذة الوسائط لبيان AVERAGE . بناء الجملة كما يلي:
=СРЗНАЧ(число1;число2;…)مطلوب وسيطة واحدة فقط.
في حالتنا ، في حقل "الرقم 1" ، يجب أن نحدد رابطًا للنطاق حيث يشار إلى إيرادات الفترتين السابقتين (يناير وفبراير). عيّن المؤشر في الحقل وحدد الخلايا المقابلة في الورقة في عمود "الأرباح" . بعد ذلك ، انقر على زر "موافق" .
- كما ترى ، تم عرض نتيجة حساب المتوسط للفترتين السابقتين في الخلية. من أجل إجراء مثل هذه الحسابات لجميع الأشهر الأخرى من الفترة ، نحتاج إلى نسخ هذه الصيغة إلى الخلايا الأخرى. للقيام بذلك ، أصبحنا المؤشر في الزاوية اليمنى السفلى للخلية التي تحتوي على الدالة. يتم تحويل المؤشر إلى علامة ملء ، والتي تبدو كالصليب. ثبّت زر الماوس الأيسر واسحبه لأسفل حتى نهاية العمود.
- نحصل على حساب نتائج المتوسط للشهرين السابقين قبل نهاية العام.
- الآن حدد الخلية في العمود الفارغ التالي في الصف لشهر أبريل. نحن نطلق على نافذة الحجج الدالة AVERAGE بالطريقة نفسها كما هو موضح سابقًا. في الحقل "Number1" ، ندخل إحداثيات الخلايا في عمود "الأرباح" من كانون الثاني إلى آذار. ثم انقر على زر "موافق" .
- باستخدام علامة التعبئة ، انسخ الصيغة إلى خلايا الجدول أدناه.
- لذا ، قمنا بحساب القيم. الآن ، كما في المرة السابقة ، سوف نحتاج إلى معرفة أي نوع من التحليل يكون أفضل: مع تمهيد لمدة شهرين أو ثلاثة أشهر. للقيام بذلك ، قم بحساب متوسط الانحراف المربع وبعض المؤشرات الأخرى. أولا ، نحسب الانحراف المطلق باستخدام وظيفة Excel القياسية ABS ، والتي بدلا من الأرقام الإيجابية أو السالبة ترجع لوحدتها. ستكون هذه القيمة مساوية للفرق بين رقم الإيرادات الفعلي للشهر المختار والمتوقع. اضبط المؤشر على العمود الفارغ التالي في صف مايو. استدعاء معالج الدالة .
- في فئة "رياضيات" نسلط الضوء على اسم الدالة "ABS" . انقر على زر "موافق" .
- يتم فتح نافذة وسائط الدالة ABS . في حقل "الرقم" الفردي ، نشير إلى الفرق بين محتويات الخلايا في الأعمدة "الأرباح" و "شهرين" لشهر مايو. ثم انقر على زر "موافق" .
- باستخدام الحشو ، انسخ هذه الصيغة إلى جميع صفوف الجدول إلى نوفمبر شاملة.
- نحن نحسب متوسط قيمة الانحراف المطلق طوال الفترة بمساعدة الدالة المألوفة AVERAGE .
- نقوم بإجراء مماثل من أجل حساب الانحراف المطلق للواحد المتحرك في 3 أشهر. قم أولاً بتطبيق وظيفة ABS . فقط هذه المرة نعتبر الفرق بين محتويات الخلايا مع الدخل الفعلي والمخطط لها ، محسوبة بواسطة طريقة المتوسط المتحرك لمدة 3 أشهر.
- بعد ذلك ، قم بحساب متوسط جميع بيانات الانحراف المطلق باستخدام الدالة AVERAGE .
- الخطوة التالية هي حساب الانحراف النسبي. وهي تساوي نسبة الانحراف المطلق إلى المؤشر الفعلي. من أجل تجنب القيم السلبية ، نستخدم مرة أخرى الفرص التي يوفرها مشغل ABS . في هذه المرة باستخدام هذه الوظيفة ، نقوم بتقسيم قيمة الانحراف المطلق باستخدام طريقة المتوسط المتحرك لمدة شهرين للدخل الفعلي للشهر المحدد.
- ولكن عادة ما يتم عرض الانحراف النسبي كنسبة مئوية. لذلك ، حدد النطاق المناسب على الورقة ، انتقل إلى علامة التبويب "الصفحة الرئيسية" ، حيث في مربع "الرقم" ، في حقل التنسيق الخاص ، اضبط تنسيق النسبة المئوية. بعد ذلك ، يتم عرض نتيجة حساب الانحراف النسبي في المئة.
- يتم إجراء عملية مماثلة لحساب الانحراف النسبي باستخدام البيانات باستخدام تجانس لمدة 3 أشهر. فقط في هذه الحالة لحساب القسمة نستخدم عمود آخر من الجدول ، والذي لدينا اسم "عبس. قبالة (3M) " . ثم نترجم القيم العددية إلى النسبة المئوية.
- بعد ذلك ، احسب متوسط القيم لكل من الأعمدة ذات الانحراف النسبي ، كما كان من قبل باستخدام الدالة AVERAGE . نظرًا لأننا نستخدم قيم النسبة المئوية كوسيطة للدالة ، فلا نحتاج إلى تحويلها. يقوم المشغل بإخراج النتيجة بتنسيق النسبة المئوية.
- الآن وصلنا إلى حساب متوسط الانحراف مربع. سيسمح لنا هذا المؤشر بمقارنة نوعية الحساب بشكل مباشر عند استخدام مانع التعرج لمدة شهرين وثلاثة أشهر. في حالتنا ، سيكون الانحراف المعياري مساويًا للجذر التربيعي لمجموع مربعات الاختلافات في الأرباح الفعلية والمتوسط المتحرك مقسومًا على عدد الأشهر. من أجل إجراء عملية حسابية في البرنامج ، يتعين علينا استخدام عدد من الوظائف ، لا سيما ROOT و SUMMKVRAZN و ACCOUNT . على سبيل المثال ، لحساب متوسط الانحراف المربع عند استخدام خط التجانس لمدة شهرين في مايو ، سيتم استخدام الصيغة التالية في حالتنا:
=КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))قم بنسخه إلى خلايا أخرى في العمود مع حساب متوسط الانحراف المربع بواسطة علامة التعبئة.
- يتم إجراء عملية مماثلة لحساب متوسط الانحراف المربعي للمتوسط المتحرك لمدة 3 أشهر.
- بعد ذلك ، نقوم بحساب متوسط القيمة للفترة بأكملها لكل من هذين المؤشرين ، مع تطبيق الدالة AVERAGE .
- من خلال مقارنة الحسابات باستخدام طريقة المتوسط المتحرك مع التمهل عند 2 و 3 أشهر لمثل هذه المؤشرات مثل الانحراف المطلق والانحراف النسبي وانحراف الجذر - الوسط المربع ، يمكننا القول على وجه اليقين أن التمهل لمدة شهرين يعطي نتائج أكثر موثوقية من تطبيق التنعيم في ثلاثة أشهر. ويتضح ذلك من خلال حقيقة أن المؤشرات المذكورة أعلاه لمعدل متحرك لمدة شهرين أقل من ثلاثة أشهر.
- وبالتالي ، فإن الدخل المتوقع للشركة لشهر ديسمبر سيكون 990.4 ألف روبل. كما ترون ، تتطابق هذه القيمة تمامًا مع القيمة التي حصلنا عليها عن طريق إجراء العمليات الحسابية باستخدام أدوات حزمة التحليل .
حسبنا التنبؤ باستخدام طريقة المتوسط المتحرك بطريقتين. كما ترى ، هذا الإجراء أسهل بكثير باستخدام أدوات حزمة التحليل . ومع ذلك ، لا يثق بعض المستخدمين دائمًا بالحساب التلقائي ويفضلون استخدام الدالة AVERAGE والمشغلين المرافقين لإجراء عمليات حسابية للتحقق من الإصدار الأكثر موثوقية. على الرغم من ذلك ، إذا كان كل شيء يتم بشكل صحيح ، يجب أن يكون ناتج الحساب نفسه تمامًا عند الإخراج.