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

در این مطلب نحوه‌ی استفاده از FORECAST اکسل و سایر تابع‌های پیش‌بینی در اکسل همراه با مثال‌های فرمول توضیح داده شده‌است.

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

تمرکز اصلی این آموزش بر روی دو تابع اصلی پیش‌بینی خواهد بود، اما به طور خلاصه در مورد سایر توابع اشاراتی خواهیم داشت تا به شما در درک هدف و کاربردهای اصلی آن‌ها کمک کنیم.

• توابع پیش‌بینی اکسل
• FORECAST
• FORECAST.LINEAR
• FORECAST.ETS
• FORECAST.ETS.CONFINT
• FORECAST.ETS.SEASONALITY
• FORECAST.ETS.STAT

توابع پیش‌بینی اکسل

در نسخه‌های اخیر اکسل، شش تابع پیش‌بینی متفاوت وجود دارد.

دو تابع کار پیش‌بینی‌های خطی (linear forecasts) را انجام می‌دهند:

• FORECAST – مقادیر آینده را با استفاده از رگرسیون خطی پیش‌بینی می‌کند. یک تابع به‌ارث‌رسیده برای سازگاری با اکسل ۲۰۱۳ و قبل از آن.
• FORECAST.LINEAR – با تابع FORECAST یکسان است. بخشی از مجموعه جدید توابع پیش‌بینی در اکس ۲۰۱۶ و اکسل ۲۰۱۹.

چهار تابع ETS برای پیش‌بینی‌های هموارسازی نمایی (exponential smoothing) در نظر گرفته شده‌است. این توابع فقط در اکسل برای آفیس ۳۶۵، اکسل ۲۰۱۹ و اکسل ۲۰۱۶ موجود است.

• FORECAST.ETS – مقادیر آینده را براساس الگوریتم هموارسازی نمایی پیش‌بینی می‌کند.
• FORECAST.ETS.CONFINT – فاصله‌ی اطمینان را محاسبه می‌کند.
• FORECAST.ETS.SEASONALITY – طول یک الگوی تکراری فصلی یا دیگر را محاسبه می‌کند.
• FORECAST.ETS.STAT – مقادیر آماری را برای پیش‌بینی سری‌های زمانی برمی‌گرداند.

تابع FORECAST اکسل

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

نحو تابع FORECAST به شرح زیر است:

FORECAST(x, known_y’s, known_x’s)

که در آن:

• X (ضروری) – یک مقدار x عددی که می‌خواهید مقدار y جدید را پیش‌بینی کنید.
• Known_y’s (ضروری) – مجموعه‌ای از مقادیر y وابسته‌ی شناخته‌شده .
• Known_x’s (ضروری) – مجموعه‌ای از مقادیر x مستقل شناخته‌شده.

تابع FORECAST در کلیه نسخه‌های اکسل برای آفیس ۳۶۵، اکسل ۲۰۱۹، اکسل ۲۰۱۶، اکسل ۲۰۱۳، اکسل ۲۰۱۰، اکسل ۲۰۰۷، اکسل ۲۰۰۳، اکسل XP و اکسل ۲۰۰۰ کار می‌کند.
توجه: در اکسل ۲۰۱۶ و ۲۰۱۹، این تابع با FORECAST.LINEAR جایگزین شده‌است، اما هنوز هم برای سازگاری به عقب در دسترس است.

تابع FORECAST.LINEAR اکسل

تابع FORECAST.LINEAR همتای مدرن تابع FORECAST است. همان نتیجه و سینتکس را دارد:

FORECAST.LINEAR(x, known_y’s, known_x’s)

این تابع در اکسل برای آفیس ۳۶۵ ، اکسل ۲۰۱۹ و اکسل ۲۰۱۶ موجود است.

چگونه FORECAST و FORECAST.LINEAR مقادیر آینده را محاسبه می‌کنند

هر دو تابع با استفاده از معادله‌ی رگرسیون خطی مقدار-y آینده را محاسبه می‌کنند:

y = a + bx

جایی که در آن ثابت a (رهگیری) عبارت است است:

و ضریب b (شیب خط) عبارت است از:

مقادیر x̄ و ȳ میانگین ساده‌ی مقادیر known_y’s و known_x’s هستند.

تابع FORECAST اکسل کار نمی‌کند:

اگر فرمول FORECAST شما خطایی را برگرداند، این به احتمال زیاد به دلایل زیر است:

1. اگر دامنه‌های known_y’s و known_x’s دارای طول‌های مختلف یا خالی باشد، خطای #N/A! رخ می‌دهد.
2. اگر مقدار x غیر عددی باشد، فرمول خطای #VALUE! را برمی‌گرداند!
3. اگر واریانس known_x’s صفر باشد، خطای #DIV/0! رخ می‌دهد

نحوه‌ی استفاده از تابع FORECAST در Excel – مثال فرمول

همان‌طور که قبلاً ذکر شد، توابع Excel FORECAST و FORECAST.LINEAR برای پیش‌بینی روند خطی تعیین شده‌اند. آن‌ها برای مجموعه داده‌های خطی بهتر کار می‌کنند و در شرایطی که می‌خواهید یک روند کلی را پیش‌بینی کنید و از نوسانات داده‌های ناچیز چشم‌پوشی کنید.

به عنوان نمونه، می‌خواهیم که براساس داده‌های 3 هفته‌ی قبل، ترافیک وب سایت خود را برای 7 روز آینده پیش‌بینی کنیم.

با وجود مقادیر-y شناخته‌شده (تعداد بازدیدکنندگان) در B2:B22 و مقادیر-x شناخته‌شده (تاریخ‌ها) در A2:A22، فرمول پیش‌بینی به شرح زیر است.

اکسل ۲۰۱۹ – اکسل ۲۰۰۰:

=FORECAST(A23, $B$2:$B$22, $A$2:$A$22)

اکسل ۲۰۱۶ و اکسل ۲۰۱۹:

=FORECAST.LINEAR(A23, $B$2:$B$22, $A$2:$A$22)

که در آن A23 مقدار-x جدیدی است که می‌خواهید مقدار-y آینده‌ی آن را پیش‌بینی کنید.

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

لطفاً توجه داشته باشید که ما محدوده‌ها را با استفاده از ارجاع سلول‌های مطلق قفل می‌کنیم (مثال $A$2:$A$2) برای جلوگیری از تغییر آن‌ها هنگام کپی کردن فرمول در سلول‌های دیگر.

پیش‌بینی خطی ما با ترسیم بر روی نمودار به شرح زیر است:

مراحل تفصیلی برای تهیه‌ی چنین نموداری در نمودار پیش‌بینی رگرسیون خطی شرح داده شده‌است.

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

تابع FORECAST.ETS اکسل

تابع FORECAST.ETS برای انجام پیش‌بینی‌های هموارسازی نمایی بر اساس یک سری مقادیر موجود استفاده می‌شود.

به طور دقیق‌تر، این تابع یک مقدار آینده را بر اساس نسخه AAA الگوریتم هموارسازی سه‌گانه‌ی نمایی (Exponential Triple Smoothing ETS) پیش‌بینی می‌کند، و نام تابع هم همین را نشان می‌دهد. این الگوریتم با تشخیص الگوهای فصلی و فواصل اطمینان، انحرافات ناچیز در روند داده را کاهش می‌دهد. “AAA” مخفف additive error (خطای افزودنی)، additive trend (روند افزودنی) و additive seasonality (فصلی افزودنی) است.

تابع FORECAST.ETS در اکسل برای آفیس ۳۶۵، اکسل ۲۰۱۹ و اکسل ۲۰۱۶ موجود است.

سینتکس اکسل FORECAST.ETS به شرح زیر است:

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

که درآن:

• Target_date (ضروری) – نقطه‌ای از داده‌ها برای پیش‌بینی یک مقدار. می‌توان آن را با تاریخ/زمان یا عدد نشان داد.
• Values (ضروری) – مجموعه‌ای از داده‌های تاریخی که می‌خواهید مقادیر آتی را برای آن‌ها پیش‌بینی کنید.
• Timeline (ضروری) – مجموعه‌ای از تاریخ‌ها/زمان‌ها یا داده‌های عددی مستقل با یک گام ثابت بین آن‌ها.
• 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

5 چیز که باید درباره‌ی FORECAST.ETS بدانید

1. برای کارکرد صحیح تابع FORECAST.ETS، جدول زمانی باید دارای یک بازه‌ی منظم – ساعتی، روزانه، ماهانه، سه ماهه، سالانه و غیره باشد.
2. این تابع برای مجموعه داده‌های غیرخطی با الگوی تکراری فصلی یا سایر مدل‌ها مناسب است.
3. هنگامی که اکسل نمی‌تواند الگویی را تشخیص دهد، تابع به پیش‌بینی خطی برمی‌گردد.
4. این تابع می‌تواند با مجموعه داده‌های ناقص که در آن 30% نقاط داده‌ها وجود ندارد، کار کند. نقاط گمشده مطابق مقدار آرگومان data completion مورد بررسی قرار می‌گیرند.
5. اگرچه یک جدول زمانی با یک گام پیوسته مورد نیاز است، اما ممکن است مقادیر تکراری در سری تاریخ/زمان وجود داشته باشد. مقادیر با همان مهر زمانی همان‌طور که توسط آرگومان aggregation تعریف می‌شوند، محاسبه می‌شوند.

تابع FORECAST.ETS کار نمی‌کند:

اگر فرمول شما خطایی ایجاد کند، احتمالاً یکی از موارد زیر است:

1. #N/A در صورتی رخ می‌دهد که value و آرایه‌های timeline دارای طول‌های مختلف باشند.
2. #VALUE! در صورتی رخ می‌دهد که آرگومان‌های seasonality، data completion یا aggregation غیر عددی باشند
3. #NUM! خطا ممکن است به دلایل زیر برگردانده شود:
   • اندازه‌ی گام پیوسته در timeline قابل تشخیص نیست.
   • مقدار seasonality خارج از محدوده‌ی پشتیبانی‌شده است (0 – 8,760).
   • مقدار data completion غیر از 0 یا 1 است.
   • مقدار aggregation خارج از محدوده‌ی معتبر است (1 – 7).

نحوه‌ی استفاده از تابع FORECAST.ETS در Excel – مثال فرمول

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

=FORECAST.ETS(A23, $B$2:$B$22, $A$2:$A$22)

که در آن:

• A23 تاریخ هدف است
• $B$2:$B$22 داده‌های تاریخی (value) هستند
• $A$2:$A$22 تاریخ‌ها هستند (timeline)

با حذف سه آرگومان اخیر (seasonality، data completion یا aggregation) ما به پیش‌فرض‌های اکسل اعتماد می‌کنیم؛ و اکسل روند را کاملاً پیش‌بینی می‌کند:

تابع FORECAST.ETS.CONFINT اکسل

تابع FORECAST.ETS.CONFINT برای محاسبه‌ی فاصله اطمینان برای یک مقدار پیش‌بینی‌شده استفاده می‌شود.

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

FORECAST.ETS.CONFINT در اکسل برای آفیس ۳۶۵، اکسل ۲۰۱۹ و اکسل ۲۰۱۶ موجود است.

این تابع آرگومان‌های زیر را دارد:

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

همان‌طور که می‌بینید، سینتکس FORECAST.ETS.CONFINT بسیار شبیه به تابع FORECAST.ETS است، به جز این آرگومان اضافی:

Confidence_level (اختیاری) – عددی بین 0 تا 1 است که یک سطح اطمینان برای فاصله‌ی محاسبه‌شده را مشخص می‌کند. به طور معمول، به صورت عدد اعشاری عرضه می‌شود، اگرچه درصدها نیز پذیرفته می‌شوند. به عنوان مثال، برای تعیین سطح اطمینان 90%، یا 0.9 یا 90% را وارد کنید.

• در صورت حذف، مقدار پیش فرض 95% استفاده می‌شود، به این معنی که 95% از زمان نقطه‌ داده‌ی پیش‌بینی‌شده در شعاع مقدار برگشتی توسط FORECAST.ETS قرار می‌گیرد.
• اگر سطح اطمینان خارج از محدوده‌ی پشتیبانی‌شده (0 – 1) باشد، فرمول خطای #NUM! را برمی‌گرداند.

مثال فرمول FORECAST.ETS.CONFINT

4 آرگومان بعدی حذف شده‌اند و به اکسل می‌گویند از گزینه‌های پیش‌فرض استفاده کند:

=FORECAST.ETS.CONFINT(A23, $B$2:$B$22, $A$2:$A$22)

که در آن:

• A23 تاریخ هدف است
• $B$2:$B$22 داده‌های تاریخی (value) هستند
• $A$2:$A$22 تاریخ‌ها هستند (timeline)

4 آرگومان بعدی حذف شده‌اند و به اکسل می‌گویند از گزینه‌های پیش‌فرض استفاده کند:

• سطح اطمینان را تا 95٪ تنظیم کند.
• seasonality را بطور خودکار تشخیص دهید.
• نقاط کامل ازدست‌رفته به صورت میانگین امتیازات همسایه تکمیل کند.
• با استفاده از تابع AVERAGE مقادیر داده‌های چندگانه را با همان مهر زمانی مشابه محاسبه کنید.

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

فرمول موجود در D23 نتیجه‌ی 6441.22 (گردشده به 2 رقم اعشار) را می‌دهد. منظور این است که 95٪ از زمان، پیش‌بینی 11-Mar در 6441.22 از ارزش پیش‌بینی‌شده‌ی 61.075 (C3) قرار می‌گیرد. این همان 61,075 ± 6441.22 است.

برای پیدا کردن دامنه‌ای که مقادیر پیش‌بینی شده در آن واقع می‌شود، می‌توانید محدوده‌ی بازه اطمینان برای هر نقطه داده را محاسبه کنید.

برای به دست آوردن حد پایین، فاصله‌ی اطمینان را از مقدار پیش‌بینی‌شده کم کنید:

=C23-D23

برای به دست آوردن حد بالا، فاصله‌ی اطمینان را به مقدار پیش‌بینی‌شده اضافه کنید:

=C23+D23

که در آن C23 مقدار پیش‌بینی‌شده توسط FORECAST.ETS است و D23 فاصله‌ی اطمینان است که توسط FORECAST.ETS.CONFINT برگردانده شده‌است.

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

تابع FORECAST.ETS.SEASONALITY اکسل

از تابع FORECAST.ETS.SEASONALITY برای محاسبه طول یک الگوی عود در خط زمانی (timeline) معین استفاده می‌شود. این تابع با FORECAST.ETS به طور تنگاتنگی گره خورده‌است زیرا هر دو تابع از الگوریتم یکسانی برای تشخیص فصلی (seasonality) استفاده می‌کنند.

این تابع در اکسل برای آفیس ۳۶۵، اکسل ۲۰۱۹ و اکسل ۲۰۱۶ موجود است.

سینتکس FORECAST.ETS.SEASONALITY به شرح زیر است:

برای مجموعه داده‌های ما، فرمول شکل زیر را به خود می‌گیرد:

=FORECAST.ETS.SEASONALITY(B2:B22, A2:A22)

و فصلی (seasonality) 7 را که کاملاً با الگوی هفتگی اطلاعات تاریخی ما منطبق است، برمی‌گرداند:

تابع FORECAST.ETS.STAT اکسل

تابع FORECAST.ETS.STAT یک مقدار آماری معین مرتبط به یک پیش‌بینی هموارسازی نمایی سری زمانی را برمی‌گرداند.

مانند سایر توابع ETS، در اکسل برای Office ۳۶۵، اکسل ۲۰۱۹ و اکسل ۲۰۱۶ نیز موجود است.

این تابع دارای سینتکس زیر است:

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

آرگومان‌های 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).

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

=FORECAST.ETS.STAT(B2:B22, A2:A22, 1)

تصویر زیر فرمول سایر مقادیر آماری را نشان می‌دهد:

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

دانلود

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