مایکروسافت اکسل روش متوسط حرکت
روش متوسط متحرک یک ابزار آماری است که می توانید انواع مختلف مشکلات را حل کنید. به طور خاص، آن را اغلب در پیش بینی استفاده می شود. در اکسل، این ابزار همچنین می تواند برای حل انواع وظایف مورد استفاده قرار گیرد. بیایید ببینیم که چگونه میانگین متحرک در اکسل استفاده می شود.
محتوا
استفاده از میانگین متحرک
معنای این روش این است که با هماهنگ کردن داده ها، مقادیر پویش مطلق مجموعه های انتخاب شده را به میانگین میانگین محاسباتی برای یک دوره معین تغییر می دهد. این ابزار برای محاسبات اقتصادی، پیش بینی، در روند معاملات در بورس اوراق بهادار و غیره استفاده می شود. بهترین روش استفاده از روش Moving Average در Excel با کمک قوی ترین ابزار برای پردازش اطلاعات آماری است که بسته Analyze نامیده می شود. علاوه بر این، برای این منظور، می توانید از تابع AVERAGE تابع ساخته شده در اکسل استفاده کنید.
روش 1: بسته تجزیه و تحلیل
بسته تجزیه و تحلیل یک افزودنی اکسل است که به طور پیش فرض غیر فعال شده است. بنابراین، اول از همه، لازم است که آن را فعال کنید.
- حرکت به برگه "File" . بر روی گزینه "گزینه ها" کلیک کنید.
- در پنجره پارامترهای راه اندازی، به بخش «افزودنیها» بروید . در قسمت پایین پنجره، پارامتر افزودنیهای اکسل باید در قسمت مدیریت " تنظیم شود. بر روی دکمه "برو" کلیک کنید.
- ما به پنجره افزودنی ها می رویم کادر کنار کادر "تجزیه و تحلیل بسته" را علامت بزنید و روی دکمه OK کلیک کنید.
پس از این عمل، بسته تجزیه و تحلیل داده ها فعال می شود و دکمه مربوطه روی نوار در زبانه داده ظاهر می شود .
و حالا بیایید نگاه کنیم که چگونه می توانید به طور مستقیم از قابلیت های بسته تجزیه و تحلیل داده برای کار بر روی روش میانگین متحرک استفاده کنید. بگذارید براساس اطلاعات مربوط به درآمد شرکت در 11 دوره قبلی، یک پیش بینی برای ماه دوازدهم ایجاد کنیم. برای انجام این کار، ما از جدول پر از داده ها و ابزار بسته تجزیه و تحلیل استفاده می کنیم .
- به زبانه "Data" بروید و روی دکمه "Data Analysis" کلیک کنید که روی نوار ابزار در بلوک "Analysis" قرار می گیرد.
- فهرستی از ابزارهایی که در بسته تجزیه و تحلیل در دسترس هستند، باز می شود . نام "Moving Average" را از آنها انتخاب کرده و روی دکمه "OK" کلیک کنید.
- پنجره ورود اطلاعات برای پیش بینی میانگین متحرک راه اندازی شده است.
در فیلد "فاصله ورودی" ما آدرس محدوده ای را مشخص می کنیم که درآمد ماهانه بدون سلول قرار می گیرد، داده هایی که باید محاسبه شوند.
در فیلد "Interval" باید با استفاده از روش صاف کردن فاصله پردازش ها را مشخص کنید. برای شروع، بگذارید مقدار صاف تا سه ماه تعیین شود، و بنابراین شماره 3 را وارد کنید.
در فیلد "فاصله خروجی" شما باید یک طیف دلخواه خالی روی صفحه مشخص کنید، جایی که داده ها بعد از پردازش نمایش داده می شوند، که باید یک سلول بزرگتر از فاصله ورودی باشد.
همچنین باید کادر کنار «خطاهای استاندارد» را بررسی کنید .
در صورت لزوم، می توانید یک تیک کنار آیتم «گرافیک نمایش» را برای تظاهرات بصری تنظیم کنید، اگرچه در مورد ما لازم نیست.
بعد از اینکه تمام تنظیمات انجام شد، روی دکمه "OK" کلیک کنید.
- این برنامه نتیجه پردازش را نمایش می دهد.
- حالا ما هماهنگ سازی را برای مدت دو ماه انجام خواهیم داد تا نشان دهیم کدام نتیجه صحیح است. برای این منظور، ما دوباره ابزار میانگین متحرک بسته تجزیه و تحلیل را اجرا می کنیم .
در فیلد "فاصله ورودی" ما مقادیر مشابهی را در مورد قبلی می گذاریم .
در قسمت "Interval" شماره "2" را وارد کنید .
در فیلد «فاصله خروجی» آدرس آدرس محدوده خالی جدید را نشان می دهد، که مجددا باید یک سلول بزرگتر از فاصله ورودی باشد.
تنظیمات باقی مانده بدون تغییر باقی می مانند. پس از آن، بر روی دکمه "OK" کلیک کنید.
- پس از این، برنامه محاسبه و نتیجه را بر روی صفحه نمایش می دهد. برای تعیین اینکه کدام یک از دو مدل دقیق تر است، باید مقادیر استاندارد را مقایسه کنیم. این شاخص کوچکتر است، بیشتر احتمال صحت نتیجه است. همانطور که می بینید، برای تمام مقادیر خطای استاندارد در محاسبه کشویی دو ماهه کمتر از همان اندازه برای 3 ماه است. بنابراین، مقادیر پیش بینی شده برای دسامبر می تواند ارزش محاسبه شده توسط روش لغزش برای آخرین دوره باشد. در مورد ما این ارزش 990.4 هزار روبل است.
روش 2: استفاده از عملکرد AVERAGE
در اکسل روش دیگری برای استفاده از روش متوسط متحرک وجود دارد. برای استفاده از آن، شما باید تعدادی از توابع برنامه استاندارد را اعمال کنید، پایه ای که برای هدف ما AVERAGE است . برای مثال، از یک جدول مشابه درآمد شرکت استفاده می کنیم همانطور که در مورد اول است.
همانند گذشته، ما نیاز به ایجاد یک سری زمانی صاف شده داریم. اما این بار اقدامات به صورت خودکار انجام نخواهد شد. محاسبه میانگین برای هر دو و سپس سه ماه برای مقایسه نتایج.
اول از همه، مقادیر میانگین برای دو دوره قبلی را با استفاده از عملکرد AVERAGE محاسبه خواهیم کرد. ما می توانیم این کار را فقط در ماه مارس انجام دهیم، همانطور که برای تاریخ های بعد، شکاف در ارزش ها وجود دارد.
- سلول را در ستون خالی در ردیف مارس انتخاب کنید. بعد، روی نماد "Insert function" کلیک کنید، که در نزدیکی نوار فرمول واقع شده است.
- جادوگر تابع فعال شده است. در رده "آماری"، به دنبال مقدار "AVERAGE" ، آن را انتخاب کنید و بر روی دکمه "OK" کلیک کنید.
- پنجره استدلال AVERAGE ACKNOWLED راه اندازی شده است . نحو او به شرح زیر است:
=СРЗНАЧ(число1;число2;…)فقط یک استدلال لازم است
در مورد ما، در قسمت "شماره 1" باید یک پیوند را به محدوده ای که درآمد برای دو دوره قبلی (ژانویه و فوریه) نشان داده می شود، نشان می دهد. ما مکان را در این زمینه قرار می دهیم و سلول های مربوطه را بر روی صفحه در ستون "درآمد" انتخاب می کنیم. پس از آن، بر روی دکمه "OK" کلیک کنید.
- همانطور که می بینید، نتیجه محاسبه میانگین برای دو دوره قبلی در سلول نمایش داده می شود. برای انجام محاسبات مشابه برای تمام ماه های باقی مانده از دوره، ما باید این فرمول را به سلول های دیگر کپی کنیم. برای انجام این کار، ما در سمت راست پایین سلول حاوی تابع قرار می گیریم. مکان نما به نشانگر پر شده تبدیل می شود که شکل متقابل دارد. پایین دکمه سمت چپ ماوس را نگه دارید و آن را به انتهای ستون بکشید.
- ما محاسبه نتایج میانگین برای دو ماه قبل قبل از پایان سال را دریافت می کنیم.
- اکنون سلول را در ستون خالی بعدی در ردیف برای آوریل انتخاب کنید. پنجره استدلال عملکرد AVERAGE را به همان شیوه ای که قبلا توضیح داده شد، تماس بگیرید. در قسمت "شماره 1" مختصات سلول ها در ستون "درآمد" را از ژانویه تا مارس وارد کنید. سپس بر روی دکمه "OK" کلیک کنید.
- با استفاده از نشانگر پر کنید، فرمول را به سلول جدول در زیر کپی کنید.
- بنابراین، ارزش ها را محاسبه کردیم. در حال حاضر، همانطور که در زمان گذشته، ما نیاز به کشف کردن نوع تجزیه و تحلیل بهتر است: با ضد عرق کردن در 2 یا 3 ماه. برای انجام این کار، انحراف استاندارد و برخی شاخص های دیگر را محاسبه کنید. برای شروع، انحراف مطلق را با استفاده از تابع استاندارد اکسل ABS ، محاسبه می کنیم که به جای تعداد مثبت یا منفی، ماژول خود را باز می گرداند. این مقدار برابر با تفاوت درآمد واقعی ماه انتخاب شده و پیش بینی می شود. مکان را در ماه مه در ستون خالی بعدی در یک ردیف قرار دهید. با کارکرد کارشناسی ارشد تماس بگیرید
- در رده "ریاضی" نام تابع "ABS" را انتخاب کنید . روی دکمه "OK" کلیک کنید.
- پنجره های استدلال عملکرد ABS راه اندازی می شود. در قسمت تک "Number"، اختلاف بین محتوای سلول در ستون "درآمد" و "2 ماه" در ماه مه مشخص می شود. سپس بر روی دکمه "OK" کلیک کنید.
- با استفاده از نشانگر پر کنید، ما این فرمول را به تمام ردیف ها در جدول از طریق نوامبر فراگیر کپی می کنیم.
- ما مقدار میانگین انحراف مطلق برای کل دوره را با کمک عملکرد AVERAGE که برای ما آشناست، محاسبه میکنیم.
- ما یک روش مشابه را برای محاسبه انحراف مطلق برای کشویی برای 3 ماه انجام می دهیم. اول، ما از عملکرد ABS استفاده می کنیم. فقط این بار، ما تفاوت بین محتویات سلول ها با درآمد واقعی و برنامه ریزی شده، با استفاده از روش میانگین متحرک به مدت 3 ماه محاسبه می کنیم.
- بعد، میانگین میانگین داده های انحراف مطلق را با استفاده از عملکرد AVERAGE محاسبه می کنیم.
- گام بعدی محاسبه انحراف نسبی است. این برابر با نسبت انحراف مطلق به شاخص واقعی است. به منظور اجتناب از ارزش منفی، ما دوباره از امکانات ارائه شده توسط اپراتور ABS استفاده می کنیم. این بار با استفاده از این تابع، مقدار انحراف مطلق را با استفاده از روش میانگین متحرک برای 2 ماه با درآمد واقعی ماه انتخاب شده تقسیم می کنیم.
- اما انحراف نسبی معمولا به عنوان یک درصد نمایش داده می شود. بنابراین، محدوده مناسب را در برگه انتخاب کنید، به برگه «صفحه اصلی» بروید ، در آنجا در جعبه ابزار «شماره» در زمینه قالب بندی خاص، فرمت درصد را تعیین می کنیم. پس از آن، نتیجه محاسبه انحراف نسبی در درصد نمایش داده می شود.
- ما یک عملیات مشابه برای محاسبه انحراف نسبی داده ها با استفاده از هموار سازی برای 3 ماه انجام می دهیم. فقط در این مورد، برای محاسبه به عنوان سود سهام، ما از ستون دیگری از جدول استفاده می کنیم که ما آن را Abs. خاموش (3 متر) . " سپس مقادیر عددی را به درصد تبدیل می کنیم.
- پس از آن، مقادیر میانگین برای هر دو ستون را با یک انحراف نسبی محاسبه می کنیم، همانطور که قبل از استفاده از این هدف برای عملکرد AVERAGE . از آنجایی که ما مقدار درصد برای تابع را به عنوان استدلال تابع می گیریم، ما نیاز به تبدیل اضافی نداریم. اپراتور در خروجی نتیجه را از قبل در فرمت درصد ارائه می دهد.
- اکنون ما به محاسبه انحراف استاندارد می رویم. این نشانگر ما را قادر می سازد تا با استفاده از ضد عرق کردن برای دو و سه ماه، کیفیت محاسبات را مستقیما مقایسه کنیم. در مورد ما، انحراف استاندارد برابر با ریشه مربع مجموع مربعات تفاوت در درآمد واقعی و میانگین متحرک تقسیم بر تعداد ماه است. برای محاسبه در برنامه، ما باید از تعدادی از توابع، به ویژه ROOT ، SUMMKVRAZN و ACCOUNT استفاده کنیم . به عنوان مثال، برای محاسبه انحراف استاندارد هنگام استفاده از خط صاف کردن برای دو ماه در ماه مه، در مورد ما، فرمول زیر اعمال خواهد شد:
=КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))ما آن را به سلولهای دیگر ستون با محاسبه انحراف استاندارد با استفاده از نشانگر پر کردن آن کپی می کنیم.
- ما یک عملیات مشابه را برای محاسبه انحراف استاندارد برای میانگین متحرک 3 ماهه انجام می دهیم.
- پس از آن، میانگین مقدار کل برای هر دو شاخص را محاسبه می کنیم، با استفاده از عملکرد AVERAGE .
- با مقایسه مقادیر محاسبات با استفاده از روش میانگین متحرک با صاف کردن در 2 و 3 ماه با استفاده از شاخص هایی نظیر انحراف مطلق، انحراف نسبی و انحراف استاندارد، می توانیم با اطمینان بگوییم که صاف کردن دو ماه نتایج قابل اعتمادتری نسبت به استفاده از سه ماهه صاف کردن می دهد. این نشان دهنده ی این واقعیت است که شاخص های بالا برای میانگین میانگین دو ماهه کمتر از سه ماه است.
- بنابراین درآمد پیش بینی شده شرکت در ماه دسامبر 990.4 هزار روبل خواهد بود. همانطور که می بینید، این مقدار به طور کامل با آنچه دریافت کردیم، محاسبه می شود با استفاده از ابزار بسته تجزیه و تحلیل .
ما با استفاده از روش میانگین متحرک، دو روش پیش بینی را محاسبه کردیم. همانطور که می بینید، این روش بسیار ساده تر از استفاده از ابزار بسته تجزیه و تحلیل است . با این حال، بعضی از کاربران همیشه محاسبه خودکار را انجام نمی دهند و ترجیح می دهند از کارکرد AVERAGE و اپراتورهای مربوطه برای محاسبات استفاده کنند تا گزینه قابل اطمینان را بررسی کنند. اگر چه همه چیز به درستی انجام می شود، در خروجی نتیجه محاسبات باید کاملا یکسان باشد.