ابزار Forecast Sheet در اکسل

ابزار Forecast Sheet اکسل ویژگی جدیدی است که در اکسل ۲۰۱۶ موجود است. فقط با چند کلیک می‌توانید یک پیش‌بینی انجام‌شده برای خود داشته باشید، از جمله یک نمودار جذاب مانند نمونه‌ی زیر:

این ابزار پیش‌بینی جدید، یک کپی از داده‌های شما را ایجاد کرده و آن را بر روی یک شیت جدید در فایل شما، همراه با فرمول‌های پیش‌بینی برای پیش‌بینی مقادیر آینده ایجاد می‌کند، که همه در یک نمودار ارائه شده‌است.

داده‌های مورد نیاز برای Forecast Sheet اکسل

تمام آن‌چه شما نیاز دارید یک سری داده‌های مبتنی بر زمان تاریخی به صورت جدول است. در واقع اکسل به یک ستون حاوی تاریخ/زمان در فواصل ثابت و یک ستون حاوی مقادیر نیاز دارد.

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

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

درج Forecast Sheet اکسل

برای درج یک نمودار پیش‌بینی، با انتخاب داده‌های خود شروع کنید، یا اگر یک محدوده‌ی پیوسته دارید، کافی است یک سلول را در جدول انتخاب کنید > تب Data > گروه Forecast > سپس Forecast Sheet:

در این قسمت کادر محاوره‌ای Create Forecast Worksheet، که در زیر نشان داده شده‌است، باز می‌شود، جایی که می‌توانید نوع نمودار (1) را انتخاب کنید و زمان پایان پیش‌بینی (2):

کلیک بر روی “Options”، در زیر فیلد “Forecast End” تنظیمات بیشتری را نشان می‌دهد. این موارد به شرح زیر توضیح داده شده‌است (اعداد زیر مطابق تصویر بالا هستند):

Options	توضیحات
Forecast Start (3)	این تاریخی است که پیش‌بینی شروع می‌شود. به طور پیش فرض، پایان داده‌های شماست، اما اگر می‌خواهید داده‌ها را از پیش‌بینی خارج کنید، می‌توانید تاریخی قبل از پایان داده‌های تاریخی انتخاب کنید. این فقط داده‌های قبل از “start date” در پیش‌بینی را در برمی‌گیرد (که گاهی اوقات به عنوان “hindcasting” گفته می‌شود). نکات: شروع پیش‌بینی قبل از آخرین نقطه‌ی تاریخی به شما امکان می‌دهد تا دقت پیش‌بینی پیش‌بینی را احساس کنید زیرا می‌توانید سری پیش‌بینی‌شده را با داده‌های واقعی مقایسه کنید. با این حال، اگر پیش‌بینی را خیلی زود شروع کنید، پیش‌بینی تولیدشده لزوماً پیش‌بینی‌ای نیست که با استفاده از تمام داده‌های تاریخی بدست آورید. استفاده از تمام داده‌های تاریخی پیش‌بینی دقیق‌تری به شما می‌دهد. برای داده‌های فصلی توصیه می‌شود پیش‌بینی را قبل از آخرین نقطه‌ی تاریخی شروع کنید.
Confidence Interval (4)	فاصله‌ی اطمینان محدوده‌ی اطراف هر مقدار پیش‌بینی‌شده است، که در آن 95٪ از نقاط آینده بر اساس پیش‌بینی (با توزیع عادی) توقع می‌رود تنزل کند. این دو خط دقیق در هر دو طرف خط پیش‌بینی هستند (یا اگر گزینه نمودار ستونی را انتخاب کنید، به صورت مقادیر نوار خطا هستند). فاصله‌ی اطمینان می‌تواند به شما کمک کند تا دقت پیش‌بینی را حس کنید. بازه‌ی کوچک‌تر دلالت بر اعتماد به نفس بیشتری برای یک نقطه‌ی خاص دارد. با استفاده از فلش‌های بالا یا پایین، سطح پیش فرض را تغییر داده یا با برداشتن کادر، فواصل اطمینان را خاموش کنید.
Seasonality (5)	Seasonality عددی برای طول (عدد نقاط) الگوی فصلی یک عدد است و به طور خودکار شناسایی می‌شود. به عنوان مثال، در یک چرخه‌ی فروش سالانه، که هر نقطه یک ماه را نشان می‌دهد، Seasonality ما 12 است. شما می‌توانید با انتخاب Set Manually و سپس وارد کردن یک عدد، تشخیص خودکار را نادیده بگیرید. وقتی Seasonality به اندازه‌ی کافی برای الگوریتم قابل تشخیص نباشد، پیش‌بینی به یک روند خطی برمی‌گردد. مقدار پیش‌فرض 1 بدین معنی است که اکسل برای پیش‌بینی به صورت خودکار Seasonality را تشخیص می‌دهد و برای طول الگوی فصلی از اعداد مثبت و صحیح استفاده می‌کند. 0 نشان‌دهند‌ه‌ی عدم وجود Seasonality است، به این معنی که پیش‌بینی خطی خواهد بود. توجه: هنگام تنظیم Seasonality به صورت دستی، از مقداری کمتر از 2 چرخه برای داده‌های تاریخی خودداری کنید. با کمتر از 2 چرخه، اکسل نمی‌تواند اجزای فصلی را مشخص کند.
Include Forecast Statistics (6)	اکسل می‌تواند به طور خودکار جدول آماری را با استفاده از تابع FORECASE.ETS.STAT ایجاد کند، از جمله اقداماتی نظیر ضرایب هموار سازی (آلفا، بتا، گاما) و معیارهای خطا (MASE، SMAPE، MAE، RMSE). اگر این اطلاعات آماری اضافی در مورد پیش‌بینی موجود را در یک ورکشیت جدید می‌خواهید، این کادر را علامت بزنید.
Timeline Range (7)	به طور پیش‌فرض، محدوده‌ی خط زمانی (Timeline Range) شامل تمام تاریخ‌های جدول شما است، اما می‌توانید آن را در اینجا تغییر دهید. توجه داشته باشید؛ دامنه باید با Values Range مطابقت داشته باشد.
Values Range (8)	به طور پیش فرض، Values range تمام مقادیر موجود در جدول شما را شامل می‌شود، اما می‌توانید آن را در این‌جا تغییر دهید. توجه داشته باشید؛ دامنه باید با Timeline Range مطابقت داشته باشد.
Fill Missing Points Using (9)	اکسل برای رسیدگی به نقاط ازدست‌رفته از درون‌یابی استفاده می‌کند، به این معنی که نقطه ازدست‌رفته به صورت میانگین وزنی نقاط همسایه‌ی خود، در جایی که کمتر از 30 درصد نقاط در دسترس نباشند، تکمیل می‌شود. اگر ترجیح می‌دهید با انتخاب Zeros در لیست می‌توانید با نقاط گمشده به صورت صفر رفتار کنید.
Duplicate Aggregates Using (10)	اکسل مقادیر دارای تاریخ و زمان مشابه را میانگین می‌گیرد. می توانید روش محاسبه‌ی دیگری مانند Median را از لیست انتخاب کنید.

Forecast Sheet اکسل

Forecast Sheet جدید شامل یک جدول و نمودار است. در زیر جدول یک سری فرمول‌های FORECAST وجود دارد (به جعبه‌های رنگی در تصویر زیر مراجعه کنید):

این‌ها همه به طور خودکار برای شما ایجاد شده‌است.

آمار پیش‌بینی

اگر هنگام ایجاد شیت پیش‌بینی، Forecast Statistics را در Options انتخاب کرده‌باشیده، اکسل جدولی از آمارهای مختلف را که در زیر نشان داده شده‌است، در کنار جدول پیش‌بینی قرار خواهد داد.

همه‌ی آن‌ها از تابع FORECAST.ETS.STAT همان‌طور که در ستون I در زیر می‌بینید استفاده می‌کنند، که فرمول را از ستون H نشان می‌دهد. توجه کنید که آرگومان سوم همان چیزی است که نوع آمار را مشخص می‌کند:

هم‌رسانی Forecast Sheet اکسل

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

تغییر Forecast Sheet اکسل

حال، شما شیت پیش‌بینی خود را ایجاد کرده‌اید و سپس پی می‌برید که می‌خواهید چیزی را تغییر دهید.

در ابتدا، هیچ پیوندی به داده‌های منبع اصلی وجود ندارد، بنابراین اگر در داده‌های منبع خود تغییراتی ایجاد می‌کنید، باید شیت پیش‌بینی را از نو بسازید یا در جدول شیت پیش‌بینی، داده‌ها را ویرایش کنید.

هم‌چنین، هیچ راهی برای باز کردن کادر محاوره ای “Create Forecast Worksheet” و ایجاد تغییر در Options و تنظیمات پیش‌بینی وجود ندارد. با این وجود، می‌توانید فرمول‌های FORECAST را مستقیماً در جدول تغییر دهید.

به عنوان مثال، اگر می‌خواهید seasonality را تغییر دهید، آرگومان “seasonality ” را در فرمول‌های پیش‌بینی ویرایش کنید. با استفاده از مثال زیر، ما 12 را تغییر می‌دهیم:

هم‌چنین می‌توانید دامنه‌هایی را که با ارجاع به آن‌ها پیش‌بینی انجام می‌شود، ویرایش کنید. به عنوان مثال، داده‌های زیر روند صعودی مداوم دارند، اما می‌بینید که در اولین دوره پیش‌بینی کم می‌کند:

شاید ترجیح می‌دهید پیش‌بینی به مسیر صعودی خود ادامه دهد، در این صورت می‌توانید محدوده‌های خط زمانی (timeline) و مقادیر را برای پیش‌بینی بر اساس داده‌های جدیدتر تغییر دهید:

فراموش نکنید که فرمول‌های کران اطمینان را نیز در ستون‌های D و E تغییر دهید.

دقت پیش‌بینی

می‌توانید دقت پیش‌بینی را با شروع آن قبل از پایان داده‌های واقعی ارزیابی کنید. در مثال زیر فرمول پیش‌بینی را به ردیف 18 کپی کرده‌ایم. شما می‌توانید خط پیش‌بینی زرد را در خط مقدماتی نمودار از ماه May تا July مشاهده کنید.

توابع مورد استفاده در Forecast Sheet

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

مقادیر پیش‌بینی‌شده – تابع FORECAST.ETS:

FORECAST.ETS.CONFINT(target_date, values, timeline, [seasonality], [data completion], [aggregation])

فاصله‌ی اطمینان – تابع FORECAST.ETS.CONFINT:

FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data completion], [aggregation])

آمارهای پیش‌بینی – تابع FORECAST.ETS.STAT:

FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])

همه‌ی آن‌ها حاوی آرگومان‌های مشابه هستند، که در زیر شرح داده شده‌است:

• Target_date (ضروری) – نقطه‌ای از داده‌ها برای پیش‌بینی یک مقدار. می‌توان آن را با تاریخ/زمان یا عدد نشان داد.
• Values (ضروری) – مجموعه‌ای از داده‌های تاریخی که می‌خواهید مقادیر آتی را برای آن‌ها پیش‌بینی کنید.
• Timeline (ضروری) – مجموعه‌ای از تاریخ‌ها/زمان‌ها یا داده‌های عددی مستقل با یک گام ثابت بین آن‌ها.
• Confidence_level (اختیاری) – عددی بین 0 تا 1 است که یک سطح اطمینان برای فاصله‌ی محاسبه‌شده را مشخص می‌کند. به طور معمول، به صورت عدد اعشاری عرضه می‌شود، اگرچه درصدها نیز پذیرفته می‌شوند. به عنوان مثال، برای تعیین سطح اطمینان 90%، یا 0.9 یا 90% را وارد کنید.
• Seasonality (اختیاری) – عددی که طول الگوی فصلی را نشان می‌دهد:
  • 1 یا حذف‌شده (پیش‌فرض) – اکسل با استفاده از اعداد مثبت و کامل، مقدار را به طور خودکار تشخیص می‌دهد.
  • 0 – بدون Seasonality، یعنی یک پیش‌بینی خطی.

  حداکثر Seasonality مجاز 8,760 است که تعداد ساعات در سال است. تعداد فصلی بالاتر منجر به خطای #NUM! خواهد شد!

• Data completion (اختیاری) – نقاط ازدست‌رفته را حساب می‌کند.
  • 1 یا حذف شده (پیش‌فرض) – نقاط ازدست‌رفته را به صورت میانگین امتیازات همسایه (عدم تلاطم خطی) پر کند.
  • 0 – نقاط ازدست‌رفته را مانند صفر در نظر بگیرد.
• Aggregation (اختیاری) – نحوه‌ی جمع‌آوری چندین مقدار داده با همان مهر زمان را مشخص می‌کند.
  • 1 یا حذف شده (پیش‌فرض) – تابع AVERAGE برای جمع‌آوری استفاده می‌شود.
  • گزینه‌های دیگر شما عبارتند از: 2 – COUNT, 3 – COUNTA, 4 – MAX, 5 – MEDIAN, 6 – MIN و 7 – SUM
• Statistics_type (ضروری) – این مقدار عددی بین 1 و 8 است و نشان می‌دهد کدام آمار برای پیش‌بینی محاسبه‌شده برگردانده شود. برای توضیحات بیشتر به جدول زیر مراجعه کنید:

statistic_type	توضیحات
1	Alpha (مقدار پایه) -مقدار هموارسازی که بین 0 تا 1 است که وزن‌گیری نقاط داده را کنترل می‌کند. هرچه مقدار بیشتر باشد، وزن بیشتری به داده‌های اخیر داده می‌شود.
2	Beta (مقدار روند) – مقداری بین 0 و 1 که محاسبه‌ی روند (trend) را تعیین می‌کند. هرچه ارزش بیشتر باشد، وزن بیشتری به روندهای اخیر داده می‌شود.
3	Gamma (مقدار فصلی) – مقدار بین 0 تا 1 که فصلی بودن پیش‌بینی ETS را کنترل می‌کند. هرچه مقدار بیشتر باشد، وزن بیشتری به دوره‌ی فصلی اخیر داده می‌شود.
4	MASE (مخفف mean absolute scaled error) – اندازه‌گیری دقت پیش‌بینی.
5	SMAPE (مخفف symmetric mean absolute percentage error) – اندازه‌ی دقت بر اساس خطاهای درصد یا نسبی.
6	MAE (مخفف mean absolute error) – اندازه‌گیری بزرگی میانگین خطاهای پیش‌بینی، بدون در نظر گرفتن جهت آن‌ها.
7	RMSE (مخفف root mean square error) – اندازه‌ی تفاوت بین مقادیر پیش‌بینی‌شده و مشاهده‌شده.
8	Step size detected – اندازه‌ی گام شناسایی‌شده در خط زمانی (timeline).

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

پیش‌بینی در اکسل قبل از 2016

اگر از اکسل 2013 یا قبل از آن استفاده می‌کنید، می‌توانید Forecast sheet خود را به صورت دستی با استفاده از تابع FORECAST (با محدودیت‌هایی) ایجاد کنید.

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

 

محدودیت دیگر این است که هیچ تابعی معادلی برای محاسبه‌ی فواصل اطمینان با استفاده از هموارسازی سه‌گانه‌ی نمایی (ETS) که تابع جدید FORECAST.ETS.CONFINT از آن استفاده می‌کند، وجود ندارد. نزدیک‌ترین چیزی که می‌توانید استفاده از تابع CONFIDENCE.NORM است.

ورک‌بوک را دانلود کنید و نگاهی بیندازید

برای بررسی کلیه‌ی فرمول‌های مورد بحث در این آموزش، می‌توانید که نمونه ورک‌بوک پیش‌بینی اکسل ما را دانلود کنید. توجه داشته باشید، این ورک‌بوک شامل توابع فقط موجود در اکسل 2016 یا بعد از آن است. اگر اکسل 2016 را ندارید، نمی‌توانید فرمول‌های FORECAST.ETS را ویرایش کنید.

دانلود

از شما متشکریم که این مطلب را خواندید و امیدوارم دوباره شما را در وب‌سایت‌مان ببینیم!