پیش‌بینی آینده با تابع FORECAST در اکسل ۲۰۲۱

FORCAST Function in Excel 2021

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

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

مایکروسافت اکسل یک سری امکانات متنوع در اختیارمون قرار میده که شامل، ابزارها، توابع و نمودار هست که بسته به نیاز، باید یکی از روش های زیر رو انتخاب کنیم یکی از پرکاربردترین این توابع تابع Forecast می‌باشد:

پیش بینی نمایی Exponential Smoothing Forecast: پیش بینی بر اساس سری‌های زمانی پیشین با چرخه‌های فصلی یا هر چرخه دیگری

پیش بینی خطیLinear Forecast: پیش بینی آینده با استفاده از رگرسیون خطی

نحوه پیش بینی کردن با روش Exponential Smoothing

این روش در اکسل بر مبنای ورژن AAA از الگوریتم Exponential Triple Smoothing استوار است. این روش با پیدا کردن الگوهای فصلی (بازه‌ای) و فاصله اطمینان، انحراف در داده‌های گذشته رو کاهش میدن. این روش پیش بینی بهترین روش برای داده‌های غیر خطی با الگوهای فصلی تکرار شونده هست. این تکنیک در اکسل ۲۰۱۶ و ۲۰۱۹ در دسترس هست و همچنین Office 365.

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

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

مرتب کردن داده ها جهت استفاده در Forecast Sheet

در یک شیت اکسل، داده ها رو در ستون مجزا وارد می‌کنیم.

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

خیلی مهمه که سری زمانی ما بازه‌های برابر داشته باشه. مثلا می‌تونیم بازه هفته‌ای در نظر بگیریم و هر دوشنبه داده مورد نظر رو ثبت کنیم. یا بازه ماهانه در نظر بگیریم با ثبت داده در اول هر ماه و …

در تئوری، سری زمانی می‌تونه تا سی درصد با داده‌ها همخوانی نداشته باشه یا داده‌های مختلفی با تاریخ و زمان مشابه ثبت شده باشه، اما در عین حال به درستی کار کنه. اما قویا پیشنهاد میشه که داده‌ها رو به درستی و با منطق درست در این تکنیک استفاده کنیم تا نتیجه دقیق‌تری بگیریم.

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

ایجاد Forecast sheet

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

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

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

1- نوع نمودار: خطی یا ستونی باشه

2- تاریخ پایان پیش‌بینی

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 وجود داره.

این تابع چطور کار میکنه؟

در مثال‌های قبلی اگر بخوایم از روش خطی استفاده کنیم مطابق شکل ۷ عمل می‌کنیم:

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

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

در این مقاله نحوه پیش بینی داده‌ها رو با دو روش خطی و نمایی مشاهده کردید. جهت تمرین بیشتر می‌تونید فایل تمرین رو دانلود کنید و موارد آموزش داده شده رو در فایل پیاده کنید.

#Excel_tutorial #Excel_training #Learn_Excel #Excel_course #Excel_classes #Excel_for_beginners #Advanced_Excel #Excel_skills #Excel_tips #Excel_formulas #Excel_functions #Excel_shortcuts #Excel_spreadsheet #Excel_data_analysis #Excel_pivot_tables #Excel_charts #Excel_graphs #Excel_macros #Excel_VBA #Excel_dashboard #Excel_templates #Excel_online_course #Excel_certification #Excel_workshop #Excel_mastery