مقدمه:
ابزار Forecast Sheet در اکسل یکی از قدرتمندترین امکانات برای پیشبینی روندهای آینده بر اساس دادههای تاریخی است. این ابزار با چند کلیک ساده، نمودارهای بصری و تحلیلهای آماری ارائه میدهد که تصمیمگیری را برای کاربران آسانتر میکند.
ابزار Forecast Sheet اکسل ویژگی جدیدی است که در اکسل ۲۰۱۶ موجود است. فقط با چند کلیک میتوانید یک پیشبینی انجامشده برای خود داشته باشید، از جمله یک نمودار جذاب مانند نمونهی زیر:
این ابزار پیشبینی جدید، یک کپی از دادههای شما را ایجاد کرده و آن را بر روی یک شیت جدید در فایل شما، همراه با فرمولهای پیشبینی برای پیشبینی مقادیر آینده ایجاد میکند، که همه در یک نمودار ارائه شدهاست.
آشنایی با ابزار Forecast Sheet در اکسل
در دنیای امروز که تصمیمگیری مبتنی بر داده اهمیت بالایی دارد، اکسل با ابزار Forecast Sheet به کاربران این امکان را میدهد که روندهای آینده را بر اساس دادههای گذشته پیشبینی کنند. این ابزار با استفاده از الگوریتمهای آماری مانند Exponential Smoothing، نمودارهای پیشبینی تولید میکند که میتوانند در برنامهریزی فروش، بودجهبندی، تحلیل بازار و مدیریت موجودی بسیار مفید باشند.
کلمات کلیدی:
Forecast Sheet در اکسل، پیشبینی در اکسل، آموزش Forecast Sheet، نمودار پیشبینی، تحلیل روند، Exponential Smoothing، ابزارهای آماری اکسل، پیشبینی فروش، آموزش اکسل، تحلیل داده در اکسل
ابزار Forecast Sheet در اکسل برای پیشبینی مقادیر آینده بر اساس دادههای زمانی گذشته طراحی شده است. این ابزار از نسخه 2016 به بعد در تب Data در دسترس است و با انتخاب یک سری زمانی (مثلاً تاریخ و فروش روزانه)، میتوان با چند کلیک ساده یک نمودار پیشبینی تولید کرد.
برای استفاده از Forecast Sheet مراحل زیر را دنبال کنید:
- دادههای خود را بهصورت ستونی وارد کنید (مثلاً ستون تاریخ و ستون مقدار).
- محدوده دادهها را انتخاب کنید.
- از تب Data گزینه Forecast Sheet را انتخاب کنید.
- نوع نمودار (خطی یا ستونی) را انتخاب کرده و تنظیمات مربوط به بازه پیشبینی، سطح اطمینان، و فواصل زمانی را انجام دهید.
- روی Create کلیک کنید تا شیت جدیدی با نمودار و جدول پیشبینی ایجاد شود.
این ابزار از الگوریتم Exponential Triple Smoothing (ETS) استفاده میکند که برای دادههای دارای روند و فصلبندی بسیار مناسب است. همچنین میتوان میزان اطمینان پیشبینی را تنظیم کرد (مثلاً 95٪) تا بازههای بالا و پایین پیشبینی نیز نمایش داده شوند.
دادههای مورد نیاز برای 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],[datacompletion],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 است.
سناریوی کاربردی
فرض کنید مدیر فروش یک فروشگاه آنلاین هستید و دادههای فروش روزانه ۶ ماه گذشته را در اختیار دارید. میخواهید بدانید فروش در ماه آینده چگونه خواهد بود تا موجودی انبار را بهدرستی مدیریت کنید.
مراحل کار:
- ستون A: تاریخ فروش (مثلاً از 1 فروردین تا 31 شهریور)
- ستون B: تعداد فروش روزانه
با انتخاب این دو ستون و کلیک روی Forecast Sheet، اکسل نموداری تولید میکند که روند فروش را تا مثلاً ۳۰ روز آینده پیشبینی میکند. شما میتوانید با تحلیل این نمودار، تصمیم بگیرید چه میزان کالا باید سفارش دهید یا چه زمانی کمپین تبلیغاتی اجرا کنید.
پرسشهای متداول
- سطح / ناشرEXCEL، مقاله تخصصی
- بازدید820