روش میانگین متحرک ابزاری آماری است که با استفاده از آن می توانید انواع مختلفی از مشکلات را برطرف کنید. به طور خاص ، اغلب در پیش بینی استفاده می شود. در اکسل نیز می توانید از این ابزار برای حل تعدادی از مشکلات استفاده کنید. بیایید ببینیم که چگونه از میانگین متحرک در اکسل استفاده می شود.
حرکت برنامه متوسط
معنای این روش این است که با کمک آن ، مقادیر دینامیکی مطلق سری انتخاب شده برای یک دوره خاص با هموار سازی داده ها به مقادیر میانگین حسابی تغییر می کنند. این ابزار برای محاسبات اقتصادی ، پیش بینی ، در روند معاملات بورس و غیره استفاده می شود. استفاده از روش میانگین متحرک Excel بهتر است با استفاده از یک ابزار قدرتمند پردازش داده های آماری به نام انجام شود بسته تحلیل. شما همچنین می توانید از تابع اکسل داخلی برای همین منظور استفاده کنید. دسترسی.
روش 1: بسته تحلیلی
بسته تحلیل یک افزونه اکسل است که به طور پیش فرض غیرفعال است. بنابراین ، اول از همه ، باید آن را فعال کنید.
- به برگه بروید پرونده. روی یک مورد کلیک کنید "گزینه ها".
- در پنجره پارامترهایی که باز می شود ، به بخش بروید "افزودنیها". در پایین پنجره در جعبه "مدیریت" پارامتر باید تنظیم شود افزودنیهای اکسل. روی دکمه کلیک کنید برو به.
- وارد پنجره افزودنیها می شویم. کادر کنار را علامت بزنید بسته تحلیل و روی دکمه کلیک کنید "خوب".
پس از این عمل ، بسته "تجزیه و تحلیل داده ها" فعال شد و دکمه مربوطه روی روبان در برگه ظاهر شد "داده".
حال بیایید ببینیم که چگونه می توانید به طور مستقیم از ویژگی های بسته استفاده کنید. تجزیه و تحلیل داده ها برای روش میانگین متحرک بیایید برای ماه دوازدهم بر اساس اطلاعات مربوط به درآمد شرکت برای 11 دوره قبلی پیش بینی کنیم. برای این کار از جدولی پر از داده و همچنین ابزار استفاده خواهیم کرد بسته تحلیل.
- برو به برگه "داده" و روی دکمه کلیک کنید "تجزیه و تحلیل داده ها"، که بر روی روبان ابزار در بلوک قرار می گیرد "تحلیل".
- لیستی از ابزارهایی که در آنها موجود است بسته تحلیل. یک نام از آنها انتخاب کنید میانگین متحرک و روی دکمه کلیک کنید "خوب".
- پنجره ورود داده ها برای میانگین پیش بینی حرکت می کند.
در زمینه فاصله ورودی آدرس محدوده ای که مبلغ درآمد ماهانه بدون سلولی که داده ها باید در آن محاسبه شود را نشان دهید.
در زمینه فاصله شما باید با استفاده از روش هموار سازی ، فاصله پردازش مقادیر را مشخص کنید. ابتدا بیایید مقدار صاف کردن را به سه ماه تنظیم کنیم و بنابراین عدد را وارد کنید "3".
در زمینه "فاصله خروجی" شما باید یک برگه خالی دلخواه را روی برگه مشخص کنید که داده ها پس از پردازش نمایش داده می شود ، که باید یک سلول بزرگتر از فاصله ورودی باشد.
همچنین کادر کنار پارامتر را علامت بزنید. "خطاهای استاندارد".
در صورت لزوم می توانید کادر کنار آن را نیز بررسی کنید "خروجی نمودار" برای نمایش نمایشی ، اگرچه در مورد ما این کار ضروری نیست.
پس از تمام تنظیمات ، روی دکمه کلیک کنید "خوب".
- این برنامه نتیجه پردازش را نشان می دهد.
- اکنون ما برای مدت زمان دو ماه صافکاری را انجام خواهیم داد تا نشان دهیم کدام نتیجه صحیح تر است. برای این اهداف ، ابزار را دوباره اجرا کنید. میانگین متحرک بسته تحلیل.
در زمینه فاصله ورودی ما همان مقادیر را در مورد قبلی می گذاریم.
در زمینه فاصله شماره را بگذارید "2".
در زمینه "فاصله خروجی" آدرس دامنه خالی جدید را مشخص کنید ، که مجدداً باید یک سلول بزرگتر از فاصله ورودی باشد.
تنظیمات باقی مانده بدون تغییر باقی می ماند. پس از آن بر روی دکمه کلیک کنید "خوب".
- پس از این ، برنامه نتیجه را روی صفحه محاسبه و نمایش می دهد. برای تعیین دقیق تر کدام یک از دو مدل ، باید خطاهای استاندارد را با هم مقایسه کنیم. هرچه این شاخص کوچکتر باشد ، احتمال صحت نتیجه بیشتر می شود. همانطور که مشاهده می کنید برای کلیه مقادیر خطای استاندارد در محاسبه نورد دو ماهه کمتر از همان شاخص برای 3 ماه است. بنابراین ، ارزش پیش بینی شده برای ماه دسامبر می تواند مقدار محاسبه شده با استفاده از روش لغزش برای آخرین دوره باشد. در مورد ما ، این مقدار 990.4 هزار روبل است.
روش 2: با استفاده از عملکرد AVERAGE
در اکسل روش دیگری برای استفاده از روش میانگین متحرک وجود دارد. برای استفاده از آن ، شما باید تعدادی از کارکردهای برنامه استاندارد را اعمال کنید ، که اصلی ترین آنها برای اهداف ماست دسترسی. به عنوان نمونه ، ما از همان جدول درآمدهای شرکت مانند موارد اول استفاده خواهیم کرد.
مانند آخرین بار ، ما نیاز به ایجاد یک سری زمان هموار داریم. اما این بار ، اقدامات چندان خودکار نخواهد بود. برای اینکه بتوانید نتایج را مقایسه کنید ، باید برای هر دو و سپس سه ماه میانگین را محاسبه کنید.
اول از همه ، ما با استفاده از عملکرد ، مقادیر متوسط دو دوره قبلی را محاسبه می کنیم دسترسی. ما می توانیم این کار را فقط از ماه مارس انجام دهیم ، زیرا برای تاریخ های بعدی شکاف ارزش ها وجود دارد.
- برای ماه مارس سلول را در یک ستون خالی در یک ردیف انتخاب کنید. بعد روی آیکن کلیک کنید "درج عملکرد"که در نزدیکی نوار فرمول قرار داده شده است.
- پنجره فعال می شود جادوگران عملکرد. در دسته "آماری" به دنبال معنی SRZNACHآن را انتخاب کرده و بر روی دکمه کلیک کنید "خوب".
- پنجره Argument Operator راه اندازی شد دسترسی. نحو به شرح زیر است:
= AVERAGE (number1؛ number2؛ ...)فقط یک استدلال لازم است.
در مورد ما ، در زمینه "شماره 1" ما باید پیوندی به محدوده ای که درآمد دو دوره قبلی (ژانویه و فوریه) نشان داده شده است ، ارائه دهیم. مکان نما را در قسمت تنظیم کرده و سلول های مربوطه را روی ورق موجود در ستون انتخاب کنید درآمد. پس از آن بر روی دکمه کلیک کنید "خوب".
- همانطور که مشاهده می کنید ، نتیجه محاسبه مقدار متوسط برای دو دوره قبلی در سلول نمایش داده شد. برای انجام محاسبات مشابه برای تمام ماههای دیگر دوره ، باید این فرمول را در سلولهای دیگر کپی کنیم. برای این کار ، ماوس را در گوشه سمت راست پایین سلول که حاوی عملکرد است ، تبدیل می کنیم. مکان نما به یک نشانگر پر شده تبدیل می شود ، که مانند یک صلیب است. دکمه سمت چپ ماوس را نگه دارید و آن را تا انتهای ستون بکشید.
- ما محاسبه نتایج میانگین متوسط برای دو ماه قبل از پایان سال را دریافت می کنیم.
- اکنون سلول را در ستون خالی بعدی در ردیف برای آوریل انتخاب کنید. با پنجره آرگومان عملکرد تماس بگیرید دسترسی به همان روشی که قبلاً توضیح داده شد. در زمینه "شماره 1" مختصات سلول ها را در ستون وارد کنید درآمد ژانویه تا مارس. سپس بر روی دکمه کلیک کنید "خوب".
- با استفاده از نشانگر پر ، فرمول را در سلولهای جدول زیر کپی کنید.
- بنابراین ، ما مقادیر را محاسبه کردیم. حال ، مانند زمان قبل ، باید دریابیم که کدام نوع تحلیل بهتر است: با صاف کردن در 2 یا 3 ماه. برای این کار ، انحراف استاندارد و برخی از شاخص های دیگر را محاسبه کنید. ابتدا انحراف مطلق را با استفاده از عملکرد استاندارد اکسل محاسبه می کنیم ABS، که به جای اعداد مثبت یا منفی مدول خود را برمی گرداند. این مقدار با تفاوت بین شاخص درآمد واقعی برای ماه منتخب و پیش بینی برابر خواهد بود. مکان نما را روی ستون خالی بعدی در ردیف برای ماه مه تنظیم کنید. ما تماس می گیریم جادوگر ویژگی.
- در دسته "ریاضی" نام عملکرد را انتخاب کنید "ABS". روی دکمه کلیک کنید "خوب".
- پنجره آرگومان عملکرد شروع می شود ABS. در یک زمینه واحد "شماره" تفاوت بین محتویات سلول در ستون ها را نشان می دهد درآمد و 2 ماه برای ماه مه سپس بر روی دکمه کلیک کنید "خوب".
- با استفاده از نشانگر پر کردن ، این فرمول را در تمام سطرهای جدول تا نوامبر کپی کنید.
- ما مقدار متوسط انحراف مطلق را برای کل دوره با استفاده از عملکردی که قبلاً می شناسیم محاسبه می کنیم دسترسی.
- ما یک روش مشابه را برای محاسبه انحراف مطلق برای یک حرکت در 3 ماه انجام می دهیم. ابتدا تابع را اعمال کنید ABS. فقط این بار تفاوت بین محتویات سلولها با درآمد واقعی و برنامه ریزی شده را در نظر می گیریم که با استفاده از روش میانگین متحرک به مدت 3 ماه محاسبه می شود.
- در مرحله بعد ، ما با استفاده از تابع ، مقدار میانگین کلیه داده های انحراف مطلق را محاسبه می کنیم دسترسی.
- مرحله بعدی محاسبه انحراف نسبی است. برابر است با نسبت انحراف مطلق به شاخص واقعی. برای جلوگیری از مقادیر منفی ، ما دوباره از امکاناتی که اپراتور ارائه می دهد استفاده خواهیم کرد ABS. این بار با استفاده از این عملکرد ، مقدار انحراف مطلق را هنگام استفاده از روش میانگین متحرک به مدت 2 ماه با درآمد واقعی برای ماه منتخب تقسیم می کنیم.
- اما انحراف نسبی معمولاً به صورت درصد نشان داده می شود. بنابراین محدوده مناسب روی ورق را انتخاب کنید ، به برگه بروید "خانه"جایی که در جعبه ابزار است "شماره" در قسمت قالب بندی ویژه قالب درصد را تنظیم می کنیم. پس از آن ، نتیجه محاسبه انحراف نسبی در درصد نمایش داده می شود.
- ما یک عمل مشابه را برای محاسبه انحراف نسبی با داده ها با استفاده از صاف کردن به مدت 3 ماه انجام می دهیم. فقط در این حالت ، برای محاسبه به عنوان سود سهام ، از ستون دیگری از جدول استفاده می کنیم که نام آن را داریم "خاموش (3 متر)". سپس مقادیر عددی را به صورت درصد ترجمه می کنیم.
- پس از آن ، مقادیر متوسط برای هر دو ستون با یک انحراف نسبی ، مانند قبل از استفاده از عملکرد ، محاسبه می کنیم دسترسی. از آنجا که مقادیر درصد را به عنوان آرگومان برای عملکرد استفاده می کنیم ، دیگر نیازی به تبدیل اضافی نداریم. اپراتور خروجی نتیجه را در قالب درصد ارائه می دهد.
- اکنون به محاسبه انحراف استاندارد رسیده ایم. این شاخص به ما امکان می دهد تا هنگام استفاده از صاف کردن به مدت دو و سه ماه ، کیفیت محاسبه را به طور مستقیم مقایسه کنیم. در مورد ما ، انحراف استاندارد برابر با ریشه مربع از کل مربعهای اختلاف در درآمد واقعی و میانگین متحرک تقسیم بر تعداد ماهها خواهد بود. برای ایجاد محاسبات در برنامه ، به ویژه باید از تعدادی توابع استفاده کنیم ریشه, خلاصه و حساب. به عنوان مثال ، برای محاسبه میانگین انحراف مربع هنگام استفاده از خط صاف کننده به مدت دو ماه در ماه مه ، در مورد ما از فرمول زیر استفاده می شود:
= ROOT (خلاصه (B6: B12؛ C6: C12) / COUNT (B6: B12))با محاسبه انحراف استاندارد با استفاده از نشانگر پر ، آن را در سلولهای دیگر در ستون کپی کنید.
- یک عمل مشابه برای محاسبه انحراف استاندارد برای میانگین متحرک به مدت 3 ماه انجام می شود.
- پس از آن ، ما برای هر دو این شاخص با استفاده از عملکرد ، مقدار متوسط را برای کل دوره محاسبه می کنیم دسترسی.
- با مقایسه محاسبات با استفاده از روش میانگین متحرک با صاف کردن در 2 و 3 ماه برای شاخص هایی از قبیل انحراف مطلق ، انحراف نسبی و انحراف استاندارد می توان با اطمینان گفت که هموار سازی به مدت دو ماه نتایج معتبرتر از استفاده از صاف کردن به مدت سه ماه می دهد. این امر با این واقعیت نشان می دهد که شاخص های فوق برای میانگین حرکت دو ماهه نسبت به نمونه سه ماهه کمتر است.
- بنابراین ، شاخص پیش بینی شده درآمد شرکت برای ماه دسامبر 990.4 هزار روبل خواهد بود. همانطور که مشاهده می کنید ، این مقدار کاملاً مطابق با مقداری است که با محاسبه با استفاده از ابزار به دست آوردیم بسته تحلیل.
درس: جادوگر ویژگی های اکسل
ما پیش بینی را با استفاده از روش میانگین متحرک به دو روش محاسبه کردیم. همانطور که مشاهده می کنید انجام این روش با استفاده از ابزار بسیار ساده تر است. بسته تحلیل. با این حال ، برخی از کاربران همیشه به محاسبه خودکار اعتماد ندارند و ترجیح می دهند از این عملکرد برای محاسبات استفاده کنند. دسترسی و اپراتورهای مرتبط برای تأیید مناسب ترین گزینه. اگرچه ، اگر همه چیز به درستی انجام شود ، بازده محاسبه باید کاملاً یکسان باشد.
