فرمول متوسط حرکت

برای محاسبه میانگین متحرک یا نورد ، می توانید از یک فرمول ساده بر اساس عملکرد متوسط با منابع نسبی استفاده کنید. در مثال نشان داده شده ، فرمول در E7:

با کپی کردن فرمول ، میانگین حرکت 3 روزه را بر اساس ارزش فروش برای روز جاری و دو روز قبل محاسبه می کند.

در زیر یک گزینه انعطاف پذیر تر بر اساس عملکرد افست که دوره های متغیر را کنترل می کند ، وجود دارد.

در مورد میانگین حرکت

میانگین متحرک (همچنین به عنوان میانگین نورد نیز نامیده می شود) به طور متوسط بر اساس زیر مجموعه های داده ها در فواصل معین است. محاسبه متوسط در فواصل خاص با کاهش تأثیر نوسانات تصادفی ، داده ها را صاف می کند. این امر دیدن روندهای کلی ، به خصوص در یک نمودار را آسان تر می کند. هرچه فاصله مورد استفاده برای محاسبه میانگین متحرک بزرگتر باشد ، هموار سازی بیشتری رخ می دهد ، زیرا نقاط داده بیشتری در هر میانگین محاسبه شده گنجانده می شوند.

توضیح

فرمول های نشان داده شده در مثال همه از عملکرد متوسط با یک مرجع نسبی تنظیم شده برای هر بازه خاص استفاده می کنند. میانگین حرکت 3 روزه در E7 با تغذیه متوسط دامنه ای که شامل روز جاری و دو روز گذشته مانند این است محاسبه می شود:

میانگین 5 روز و 7 روزه به همان روش محاسبه می شود. در هر حالت ، دامنه ارائه شده به میانگین بزرگ شده است تا تعداد مورد نیاز روزها را شامل شود:

تمام فرمول ها از یک مرجع نسبی برای دامنه عرضه شده به عملکرد متوسط استفاده می کنند. از آنجا که فرمول ها در ستون کپی می شوند ، دامنه در هر سطر تغییر می کند تا مقادیر مورد نیاز برای هر میانگین را شامل شود.

هنگامی که مقادیر در یک نمودار خط ترسیم می شوند ، اثر هموار سازی واضح است:

داده های کافی

اگر فرمول ها را در ردیف اول جدول شروع کنید ، چند فرمول اول داده کافی برای محاسبه میانگین کامل ندارند ، زیرا دامنه بالاتر از ردیف اول داده ها است:

این ممکن است بسته به ساختار کاربرگ ، و اینکه آیا مهم است که تمام میانگین ها بر اساس همان تعداد مقادیر باشد ، ممکن است مسئله ای نباشد یا نباشد. عملکرد متوسط به طور خودکار مقادیر متنی و سلولهای خالی را نادیده می گیرد ، بنابراین به طور متوسط با مقادیر کمتری محاسبه می شود. به همین دلیل در E5 و E6 "کار می کند".

یکی از راه های مشخص کردن داده های کافی ، بررسی شماره ردیف فعلی و سقط با #NA در هنگام وجود مقادیر کمتر از n است. به عنوان مثال ، برای میانگین 3 روزه ، می توانید استفاده کنید:

قسمت اول فرمول به سادگی یک شماره ردیف "عادی" ایجاد می کند ، از 1 شروع می شود:

در ردیف 5 ، نتیجه 1 ، در ردیف 6 نتیجه 2 و غیره است.

وقتی شماره ردیف فعلی کمتر از 3 باشد ، فرمول #N/A را برمی گرداند. در غیر این صورت ، فرمول مانند گذشته میانگین متحرک را برمی گرداند. این رفتار نسخه Analysis Toolpak از میانگین متحرک را تقلید می کند ، که تا زمان رسیدن به اولین دوره کامل ، #N/A را تولید می کند.

با این حال ، با افزایش تعداد دوره ها ، در نهایت از ردیف های بالاتر از داده ها خارج خواهید شد و قادر نخواهید بود محدوده مورد نیاز را در حد متوسط وارد کنید. به عنوان مثال ، شما نمی توانید همانطور که نشان داده شده است ، میانگین 7 روزه متحرک را با صفحه کار تنظیم کنید ، زیرا نمی توانید محدوده ای را وارد کنید که 6 ردیف بالاتر از C5 را گسترش دهد.

دوره های متغیر با جبران

یک روش انعطاف پذیر تر برای محاسبه میانگین متحرک با عملکرد افست است. Offset می تواند یک دامنه پویا ایجاد کند ، به این معنی که می توانیم فرمولی را تنظیم کنیم که تعداد دوره ها متغیر باشد. شکل کلی:

جایی که n تعداد دوره هایی است که در هر میانگین وجود دارد. همانطور که در بالا گفته شد ، افست دامنه ای را که به عملکرد متوسط منتقل می شود ، باز می گرداند. در زیر می توانید این فرمول را در عمل مشاهده کنید ، جایی که "N" محدوده E2 نامگذاری شده است. با شروع از سلول C5 ، افست دامنه ای را ایجاد می کند که به ردیف های قبلی باز می گردد. این کار با استفاده از ارتفاع برابر با n منفی انجام می شود. هنگامی که E5 به شماره دیگری تغییر می یابد ، میانگین متحرک در همه ردیف ها محاسبه می شود:

فرمول در E5 ، کپی شده ،:

مانند فرمول اصلی بالا ، نسخه با افست نیز بسته به چند دوره در E5 ، مشکل داده های کافی را در چند ردیف اول نخواهد داشت.

در مثال نشان داده شده ، میانگین ها با موفقیت محاسبه می کنند زیرا میانگین عملکرد به طور خودکار مقادیر متن و سلول های خالی را نادیده می گیرد ، و مقادیر عددی دیگری بالاتر از C5 وجود ندارد. بنابراین ، در حالی که دامنه به طور متوسط در E5 C1 است: C5 ، فقط یک مقدار به میانگین 100 است. با این حال ، با افزایش دوره ها ، جبران همچنان به ایجاد دامنه ای می پردازد که بالاتر از شروع داده ها باشد و در نهایت در حال اجرا استبالای صفحه کار و بازگشت خطای #REF.

یک راه حل "کلاه" اندازه دامنه به تعداد نقاط داده موجود است. این کار را می توان با استفاده از عملکرد MIN انجام داد تا تعداد مورد استفاده برای ارتفاع را محدود کنید همانطور که در زیر مشاهده می شود:

این بسیار ترسناک به نظر می رسد ، اما در واقع بسیار ساده است. ما در حال محدود کردن ارتفاع منتقل شده با عملکرد MIN هستیم:

در داخل حداقل ، مقدار اول یک شماره ردیف نسبی است که با: محاسبه شده است:

مقدار دوم داده شده به حداقل تعداد دوره ها ، n. هنگامی که شماره ردیف نسبی کمتر از N باشد ، حداقل شماره ردیف فعلی را به سمت ارتفاع جبران می کند. هنگامی که تعداد ردیف از N بیشتر است ، حداقل حداقل n. به عبارت دیگر ، مین به سادگی کوچکتر از دو مقدار را برمی گرداند.

یکی از ویژگی های خوب گزینه Offset این است که N به راحتی قابل تغییر است. اگر N را به 7 تغییر دهیم و نتایج را ترسیم کنیم ، نمودار مانند این را می گیریم:

توجه: عجیب و غریب با فرمول های جبران فوق این است که آنها در برگه های Google کار نمی کنند ، زیرا عملکرد افست در برگه ها باعث نمی شود مقدار منفی برای ارتفاع یا عرض باشد. صفحه گسترده پیوست دارای فرمول های راه حل برای برگه های Google است.< pan> مقدار دوم داده شده به حداقل تعداد دوره ها ، n. هنگامی که شماره ردیف نسبی کمتر از N باشد ، حداقل شماره ردیف فعلی را به سمت ارتفاع جبران می کند. هنگامی که تعداد ردیف از N بیشتر است ، حداقل حداقل n. به عبارت دیگر ، مین به سادگی کوچکتر از دو مقدار را برمی گرداند.