ابزارها و توابع پیش بینی در اکسل

در علم آمار روش های مختلفی برای پیش بینی وجود داره و برخی از این روش های پیش بینی در اکسل قابل اجرا و پیاده سازی هستن. در این مقاله میخوایم راجع به پیش بینی در اکسل صحبت کنیم و روش های پیش بینی مبتنی بر داده های گذشته رو شرح بدیم.

پیش بینی در اکسل

پیش بینی یک روش برای تخمین و پیش بینی آینده است که با استفاده از داده های گذشته و تحلیل روی روندها انجام میشه. این روش عموما برای پیش بینی ها و حدس های علمی روی جریان نقدی، برنامه و بودجه، هزینه ها و فروش آینده و … مورد استفاده قرار میگیره. البته این تکنیک آینده رو بصورت قطعی و دقیق پیش بینی نمیکنه و فقط احتمالات رو نشون میده.مایکروسافت اکسل یک سری امکانات متنوع در اختیارمون قرار میده که شامل، ابزارها، توابع و نمودار هست که بسته به نیاز، باید یکی از روش های زیر رو انتخاب کنیم:پیش بینی نمایی Exponential Smoothing Forecast: پیش بینی بر اساس سری های زمانی پیشین با چرخه های فصلی یا هر چرخه دیگریپیش بینی خطیLinear Forecast: پیش بینی آینده با استفاده از رگرسیون خطینحوه پیش بینی کردن با روش Exponential Smoothingاین روش در اکسل بر مبنای ورژن AAA از الگوریتم Exponential Triple Smoothing استوار است. این روش با پیدا کردن الگوهای فصلی (بازه ای) و فاصله اطمینان، انحراف در داده های گذشته رو کاهش میدن. این روش پیش بینی بهترین روش برای داد ههای غیر خطی با الگوهای فصلی تکرار شونده هست. این تکنیک در اکسل ۲۰۱۶ و ۲۰۱۹ در دسترس هست و همچنین Office ۳۶۵.

ایجاد شیت پیش بینی نمایی

Forecast sheet که یکی از امکانات موجود در اکسل ۲۰۱۶ هست، پیش بینی داده ها رو خیلی آسون کرده. کافیه که فقط داده ها رو مرتب کنیم و به عنوان ورودی این ابزار تخصیص بدیم، اکسل بقیه کار رو خودش انجام میده.مرتب کردن داده ها جهت استفاده در Forecast Sheetدر یک شیت اکسل، داده ها رو در ستون مجزا وارد میکنیم.

• سری زمانی – تاریخ یا زمان ثبت شده که قاعده مند هستند و در بازه های مشخص تکرار میشن مثلا داده های ثبت شده بصورت ساعتی، روزانه، ماهانه، سالانه و…
• سری عددی- مقادیر عددی که برای سری های زمانی ثبت شده اند.

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

شکل ۱- ایجاد سری زمانی و میزان فروش در هر ماه

ایجاد Forecast sheet

برای استفاده از ابزار پیش بینی در اکل  مراحل زیر رو انجام میدیم:

1. هر دو سری رو انتخاب میکنیم. اگر یک سلول رو هم انتخاب کنیم کافیه و اکسل خودش محدوده رو میتونه تشخیص بده.
2. به تب Data رفته و از قسمت Forecast گزینه Forecast Sheet رو انتخاب میکنیم:

شکل ۲- انتخاب گزینه Forecast Sheet

3. پنجره ایجاد شیت پیش بینی، پیش نمایشی از پیش بینی نشون میده و از ما میخواد تا انتخاب کنیم (شکل ۳):
4. • نوع نمودار: خططی یا ستونی باشه
   • تاریخ پایان پیش بینی

4. بعد از تنظیم این موارد، گزینه Create رو میزنیم.

شکل ۳- پیش بینی فروش داده ها تا شش ماهه اول ۲۰۲۰

با زدن دکمه Create اکسل یک شیت ایجاد میکنه (شکل ۴) که شامل یک جدول با داده های قدیمی و داده های جدید پیش بینی شده و یک نمودار بر اساس این داده ها هست.

شکل ۴- شیت ایجاد شده به همراه داده های قدیمی و جدید و نمودار خطی

برای فهمیدن و درک نحوه محاسبه داده های پیش بینی شده میتونیم روی یکی از سلول های مربوط به داده های پیش بینی شده کلیک کنیم و فرمول استفاده شده رو ببینیم (شکل ۵):

شکل ۵- مشاهده فرمول استفاده شده در پیش بینی داده ها بصورت نمایی

انجام تنظیمات بیشتر

میتونیم تنظیمات پیش فرض این ابزار رو تغییر بدیم. برای این کار روی گزینه Option کلیک میکنیم و تنظیمات دلخواه رو انجام میدیم:

شکل ۶- انجام تنظیمات پیش بینی در اکسل

در ادامه به معرفی قسمت های مختلف این تنظیمات می پردازیم:Forecast Start: تاریخ شروع پیش بینی. هم میشه مستقیم تایپ کرد و هم از Date picker تاریخ رو انتخاب کرد.

• اگر داده فصلی باشه، بهتره که از آخرین داده پیشین، پیش بینی رو شروع کنیم

Confidence Interval (فاصله اطمینان): محدوده ای که انتظار میره پیش بینی در وان محدوده واقع بشه. در نمودار خطی با دو خط نازک در دو طرف خط پیش بینی نمایش داده شده است. در نمودار ستونی، توسط میله های خطا نمایش داده میشه.فاصله اطمینان کمک میکنه که دقت پیش بینی رو متوجه بشیم. فاصله پیشفرض میزان ۹۵% هست. این به معنی آن هست که انتظار میره که ۹۵ درصد داده های پیش بینی شده در این محدوده قرار خواهد گرفت.اگر تیکشو برداریم، فاصله اطمینان در نمودار نمایش داده نمیشه.Seasonality: طول الگوی فصلی که به طور مرتب تکرار میشه. مثلا در یک الگوی سالیانه که هر داده یک ماه رو نشون میده، عدد Seasonality برابر است با ۱۲.اکسل خودش بصورت خودکار این عدد رو تشخیص میده اما خودمون هم میتونیم این مقدار رو تغییر بدیم.وقتی اکسل نمیتونه این مقدار رو تشخصی بده (عموما وقتی اتفاق میفته که کمتر از ۲ سیکل داشته باشیم)، پیش بینی بر اساس جریان خطی انجام خواهد شد.Include Forecast Statistics: برای دیدن اطلاعات بیشتر راجع به پیش بینی میتونیم این تیک رو بزنیم. جدولی اضافه میشه که ثابت های پیش بینی از قبیل (آلفا، بتا و گاما) و خطا ها مثل (MASE,SMAOE, MAE, RMSE) رو محاسبه میکنه و نمایش میده. همه این مقادیر با تابع Forecast.EST.STAT محاسبه میشن.Timeline Range: محدوده ای شامل تاریخ هست و به عنوان سری زمانی به اکسل معرفی میکنیم. بصورت خودکار تشخیص داده میشه ولی خودمون هم میتونیم محدوده رو ویرایش کنیم.Values Range: محدوده مقادیر که باید با سری زمانی هم تراز باشه.Fill missing Points Using: داده هایی که از قلم افتادن و یا حذف شدن در اکسل چطور محاسبه میشن؟ اکسل بصورت پیشفرض از روش درونیابی استفاده میکنه. علاوه بر این روش، میتونیم برای داده های خالی و حذف شده، صفر در نظر بگیریم.Duplicate Aggregates Using: تعیین میکنه که داده های مختلف با یک تاریخ یکسان چطور باید محاسبه بشن. روش پیشفرض، میانگین هست. اما میتونیم روش های دیگه ای مثل میانه، ماکزیمم یا مینیمم و … رو هم انتخاب کنیم.

فرمول های Exponential Smoothing

ابزار آماده Forecast sheet در اکسل از دو ستون داده تشکیل شده که یکیش سری زمانی و دگری داده های موجود هستن. سه ستون بعدی هم مروط به مقادیر پیش بینی و دو مقدار فاصله اطمینان هست. همونطور که گفتیم این امکان در اکسل ۲۰۱۶ به بعد وجود داره. اما اگه بخوایم در ورژن های قبلی هم با این روش پیش بینی انجام بدیم میتونیم از توابع مربوط به این روش استفاده کنیم که در بالا تابع مربوطه رو دیدیم این توابع عبارتند از: و FORECAST.ETS.CONFINT و FORECAST.ETS.آرگومان های این توابع عینا همونهایی هستن که در ابزار Forecast sheet توضیح داده شد. با همون منطق میتونیم فرمول نویسی رو انجام بدیم و نتایج پش بینی نمایی رو مشاهده کنیم.

تخمین با استفاده از رگرسیون خطی در اکسل

برای داده هایی که چرخه تکرار ندارن، میتونیم از روش رگرسیون خطی برای پیش بینی استفاده کنیم. این روش برای داده هایی که سابقه چندانی ندارن و امکان تعیین سیکل براشون وجود نداره، هم مناسبه.اکسل برای این کار ابزار از قبل آماده مثل Forecast sheet نداره و فقط میشه از توابع برای این کار استفاده کرد. تابع Forecast و تابع Forecast.Linearهر دو تابع عینا مشابه عمل میکنن و آرگومان ها و خروجی یکسان هم دارند. تنها تفاوت در ورژن نرم افزار هست.در ورژن ۲۰۱۶ و ۲۰۱۹ هر دو این توابع هستن، ولی پیشنهاد میشه از Forecast.Linear استفاده بشه. ولی در ورژه های قبلی فقط تابع Forecast وجود داره.این تابع چطور کار میکنه؟در مثال های قبلی اگر بخوایم از روش خطی استفاده کنیم مطابق شکل ۷ عمل میکنیم:

=FORECAST.LINEAR (A14,$B$2:$B$13,$A$2:$A$13)

شکل ۷- پیش بینی با استفاده از روش رگرسیون خطی

رسم نمودار پیش بینی خطی

برای رسم این نموع نمودار، که در واقع دو نمودار خطی به هم چسبیده هستن، مقاله “نمایش پیش بینی در اکسل” رو مطالعه کنید.در این مقاله نحوه پیش بینی داده ها رو با دو روش خطی و نمایی مشاهده کردید. جهت تمرین بیشتر میتونید فایل تمرین رو دانلود کنید و موارد آموزش داده شده رو در فایل پیاده کنید.دانلود فایل اکسل این آموزشبرای دانلود فایل اکسل پیش بینی در اکسل روی دکمه زیر کلیک کنید: