آموزش تابع FORECAST.ETS.CONFINT در اکسل

معرفی تابع FORECAST.ETS.CONFINT

تابع FORECAST.ETS.CONFINT برای محاسبه عرض بازه اطمینان (Confidence Interval) در پیش‌بینی‌های سری زمانی با روش ETS (Triple Exponential Smoothing) استفاده می‌شود. خروجی این تابع خودش «عدد پیش‌بینی» نیست؛ بلکه مقدار «حاشیه خطا» یا «±» اطراف پیش‌بینی است.

یعنی اگر با تابع FORECAST.ETS مقدار آینده را پیش‌بینی کنید، با FORECAST.ETS.CONFINT می‌توانید بگویید پیش‌بینی شما با سطح اطمینان مشخص (مثلاً ۹۵٪) در چه بازه‌ای قرار می‌گیرد:

حد پایین = Forecast – ConfInt
حد بالا = Forecast + ConfInt

مثال ساده فارسی: فرض کنید فروش ماهانه دارید و می‌خواهید فروش ماه بعد را پیش‌بینی کنید. علاوه بر عدد پیش‌بینی، می‌خواهید بدانید «حداقل و حداکثر محتمل» با اطمینان ۹۵٪ چقدر است.

=FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; 0.95)

کاربردهای اصلی تابع FORECAST.ETS.CONFINT

محاسبه بازه اطمینان برای پیش‌بینی فروش، تقاضا، مصرف، ترافیک سایت و…
گزارش‌دهی مدیریتی: نشان دادن «عدم قطعیت» کنار عدد پیش‌بینی
ساخت سناریو خوش‌بینانه/بدبینانه بر اساس حد بالا و حد پایین
کنترل ریسک موجودی انبار (Stock) با توجه به نوسانات احتمالی
مقایسه کیفیت پیش‌بینی بین چند سری زمانی (هر چه بازه اطمینان کوچک‌تر، عدم قطعیت کمتر)

ساختار (Syntax)

نسخه انگلیسی:

=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

نسخه فارسی (نام آرگومان‌ها به فارسی):

=FORECAST.ETS.CONFINT(تاریخ_هدف; مقادیر; خط_زمان; [سطح_اطمینان]; [فصلی_بودن]; [تکمیل_داده]; [تجمیع])

آرگومان‌ها

target_date (تاریخ_هدف) / نقطه‌ای که می‌خواهید برای آن پیش‌بینی کنید

تاریخ یا عدد زمانی که قصد دارید مقدار آن را پیش‌بینی کنید (مثلاً ماه بعد). باید در ادامه یا در محدوده زمانی timeline قابل تفسیر باشد.

values (مقادیر) / داده‌های تاریخی

رنج داده‌های واقعی (مثلاً فروش ماهانه) که اکسل بر اساس آن الگو را یاد می‌گیرد.

timeline (خط_زمان) / محور زمان

رنج تاریخ‌ها یا نقاط زمانی متناظر با values. باید هم‌طول با values باشد.

[confidence_level] (سطح_اطمینان) / میزان اطمینان بازه

عددی بین 0 و 1. مقدار رایج 0.95 (۹۵٪) است. هرچه بزرگ‌تر باشد، بازه اطمینان بزرگ‌تر می‌شود.

[seasonality] (فصلی_بودن) / طول فصل

تعیین می‌کند الگوی فصلی چند دوره‌ای است:

اگر 0 باشد: اکسل خودش فصل را تشخیص می‌دهد.
اگر 1 باشد: بدون فصل (No Seasonality).
اگر عددی مثل 12 باشد: یعنی فصل 12 دوره‌ای (مثلاً داده ماهانه با فصل سالانه).

[data_completion] (تکمیل_داده) / برخورد با نقاط زمانی گمشده

اگر در timeline فاصله‌ها یا تاریخ‌های جاافتاده داشته باشید:

1: اکسل داده‌های گمشده را به صورت خودکار تکمیل می‌کند (پیش‌فرض)
0: تکمیل انجام نمی‌دهد و ممکن است خطا یا نتیجه نامعتبر بدهد

[aggregation] (تجمیع) / وقتی چند مقدار برای یک زمان دارید

اگر در timeline تکراری داشته باشید (مثلاً یک روز چند رکورد فروش): اکسل باید آن‌ها را به یک مقدار تبدیل کند.

1: AVERAGE (پیش‌فرض)
2: COUNT
3: COUNTA
4: MAX
5: MEDIAN
6: MIN
7: SUM

مثال‌های ساده و پایه

مثال ۱: محاسبه بازه اطمینان ۹۵٪ برای ماه بعد

فرض کنید:

A2:A13 تاریخ ماه‌ها (مثلاً 1403/01 تا 1403/12)
B2:B13 فروش هر ماه
در سلول F2 تاریخ ماه بعد (مثلاً 1404/01)

برای محاسبه مقدار «± بازه اطمینان»:

=FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; 0.95)

خروجی مثلاً 1200 می‌شود؛ یعنی پیش‌بینی شما با اطمینان ۹۵٪ حدوداً ±1200 واحد خطا دارد.

مثال ۲: ساخت حد پایین و حد بالا کنار پیش‌بینی

فرض کنید مقدار پیش‌بینی را در G2 حساب می‌کنید و بازه اطمینان را در H2:

=FORECAST.ETS(F2; B2:B13; A2:A13; 12; 1; 1)

=FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; 0.95; 12; 1; 1)

حالا حد پایین و بالا:

=G2-H2

=G2+H2

این کار برای گزارش مدیریتی بسیار کاربردی است: «پیش‌بینی: 25000، بازه ۹۵٪: از 23800 تا 26200».

مثال‌های کاربردی و واقعی

مثال ۱: سناریو بدبینانه/خوش‌بینانه با AND و OR

فرض کنید می‌خواهید اگر بازه اطمینان خیلی بزرگ است (ریسک بالا)، یک هشدار بدهید. در این مثال، اگر بازه اطمینان بیشتر از ۱۰٪ مقدار پیش‌بینی باشد، وضعیت را «ریسکی» نمایش می‌دهیم.

=FORECAST.ETS(F2; B2:B13; A2:A13; 12; 1; 1)

=FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; 0.95; 12; 1; 1)

=IF(OR(H2>0.1*G2; H2=""); "ریسکی"; "قابل قبول")

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

مثال ۲: استفاده از SUM برای ساخت سری زمانی تجمیعی و سپس بازه اطمینان

فرض کنید فروش چند محصول را در ستون‌های B تا D دارید و می‌خواهید مجموع فروش ماهانه را پیش‌بینی کنید.

=SUM(B2:D2)

این فرمول را تا پایین کپی کنید تا ستون E فروش کل شود. سپس بازه اطمینان روی E:

=FORECAST.ETS.CONFINT(F2; E2:E13; A2:A13; 0.95; 12; 1; 1)

مثال ۳: انتخاب سری زمانی بر اساس نام کالا با XLOOKUP و سپس محاسبه ConfInt

فرض کنید در یک شیت، جدول محصولات دارید و برای هر محصول یک ستون فروش ماهانه وجود دارد. با XLOOKUP می‌توانید ستون مربوط به محصول انتخابی را پیدا کنید (این مثال ایده کلی را نشان می‌دهد؛ ساختار داده‌ها باید طوری باشد که XLOOKUP بتواند رنج درست را برگرداند).

=XLOOKUP(H1; محصولات!B1:Z1; محصولات!B2:Z13)

=FORECAST.ETS.CONFINT(F2; XLOOKUP(H1; محصولات!B1:Z1; محصولات!B2:Z13); محصولات!A2:A13; 0.95; 12; 1; 1)

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

مثال ۴: کنترل کافی بودن داده‌ها با COUNTIF قبل از پیش‌بینی

اگر داده خالی زیاد باشد، بهتر است اصلاً پیش‌بینی نکنید.

=IF(COUNTIF(B2:B13; "")>2; "داده کافی نیست"; FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; 0.95; 12; 1; 1))

ترکیب تابع FORECAST.ETS.CONFINT با فرمول‌های دیگر

ساخت بازه (Lower/Upper) کنار FORECAST.ETS:

=FORECAST.ETS(F2; B2:B13; A2:A13; 12; 1; 1)-FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; 0.95; 12; 1; 1)

=FORECAST.ETS(F2; B2:B13; A2:A13; 12; 1; 1)+FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; 0.95; 12; 1; 1)

تغییر سطح اطمینان بر اساس وضعیت (مثلاً وقتی نوسان زیاد است سطح را بالاتر بگذارید) با IF:
```
=FORECAST.ETS.CONFINT(F2; B2:B13; A2:A13; IF(B14>AVERAGE(B2:B13); 0.95; 0.9); 12; 1; 1)
```

هشداردهی با AND/OR:

=IF(AND(H2>0; H2>0.15*G2); "عدم قطعیت بالا"; "عادی")

کار با چند رکورد در یک تاریخ و جمع‌زدن آن‌ها با aggregation=7 (SUM):
```
=FORECAST.ETS.CONFINT(F2; B2:B200; A2:A200; 0.95; 0; 1; 7)
```

خطاهای رایج و روش رفع آنها

در کار با FORECAST.ETS.CONFINT چند خطای پرتکرار وجود دارد. موارد زیر را بررسی کنید:

1) خطای #N/A به دلیل طول متفاوت values و timeline

اگر تعداد سلول‌های values با timeline برابر نباشد، اکسل نمی‌تواند داده‌ها را هم‌تراز کند. رنج‌ها را دقیقاً هم‌اندازه انتخاب کنید (مثلاً هر دو 12 ردیف).

2) خطای #VALUE! به خاطر تاریخ/زمان نامعتبر در timeline یا target_date

timeline باید واقعاً تاریخ اکسل (Date) یا عدد زمانی معتبر باشد. اگر تاریخ‌ها به صورت متن وارد شده‌اند، آن‌ها را به تاریخ واقعی تبدیل کنید (مثلاً با Text to Columns یا DATEVALUE).

3) خطای #NUM! به دلیل confidence_level خارج از بازه 0 تا 1

سطح اطمینان باید بین 0 و 1 باشد. مقدارهای رایج: 0.9، 0.95، 0.99.

4) نتیجه غیرمنطقی به خاطر seasonality اشتباه

اگر داده ماهانه دارید و seasonality را 4 بگذارید، مدل فصل‌بندی اشتباه یاد می‌گیرد. برای داده ماهانه معمولاً 12، برای داده هفتگی (با فصل سالانه) معمولاً 52 و برای داده روزانه (فصل هفتگی) معمولاً 7 منطقی‌تر است (بسته به الگو).

5) مشکل در داده‌های تکراری در timeline

اگر در timeline یک تاریخ چند بار تکرار شده باشد و aggregation را درست انتخاب نکنید، نتیجه ممکن است عجیب شود. اگر هدف جمع کردن رکوردهای تکراری است، aggregation را 7 (SUM) بگذارید.

6) بازه اطمینان خیلی بزرگ یا خیلی کوچک

این بیشتر «خطا» نیست؛ اما نشانه‌ای از کیفیت داده است. وجود پرت‌ها (Outlier)، تغییر روند ناگهانی، یا تعداد کم داده باعث بازه‌های بزرگ می‌شود. داده را تمیز کنید، فصل درست بدهید، یا تعداد دوره‌های تاریخی را افزایش دهید.

نکات حرفه‌ای و ترفندهای مهم

همیشه کنار پیش‌بینی، بازه اطمینان را گزارش کنید: عدد پیش‌بینی بدون عدم قطعیت، برای تصمیم‌گیری واقعی کافی نیست.
از seasonality=0 برای شروع استفاده کنید: اگر مطمئن نیستید فصل چند است، ابتدا بگذارید اکسل تشخیص دهد، سپس نتایج را با فصل‌های احتمالی مقایسه کنید.
timeline مرتب و با فاصله‌های منظم باشد: سری زمانی نامنظم دقت را پایین می‌آورد. اگر داده جاافتاده دارید، data_completion=1 کمک می‌کند.
داده پرت را بررسی کنید: یک ماه فروش غیرعادی می‌تواند بازه اطمینان را زیاد کند و کل مدل را منحرف کند.
خوانایی گزارش: حد پایین/بالا را در دو ستون جدا، و یک ستون «ریسک» برای هشدار بسازید.
توجه به نسخه اکسل: این تابع در اکسل‌های جدیدتر در دسترس است؛ اگر فایل را برای دیگران می‌فرستید، سازگاری نسخه را چک کنید.

تفاوت تابع FORECAST.ETS.CONFINT با توابع مشابه

FORECAST.ETS:
خودِ مقدار پیش‌بینی را می‌دهد؛ اما بازه اطمینان نمی‌دهد.
FORECAST.ETS.CONFINT:
حاشیه خطا (±) اطراف پیش‌بینی را می‌دهد، نه خود پیش‌بینی را.
FORECAST.LINEAR:
پیش‌بینی بر اساس رگرسیون خطی است و برای داده‌های دارای فصل‌بودن (Seasonality) مناسب نیست. همچنین مفهوم بازه اطمینان را با تابع جداگانه ETS ارائه نمی‌کند.
FORECAST.ETS.STAT:
آمارهای مربوط به مدل ETS را برمی‌گرداند (مثل خطاها و پارامترها) و برای تحلیل کیفیت مدل مفید است، نه تولید بازه اطمینان.

سازگاری با نسخه‌های مختلف اکسل

این تابع در Excel 2016 و نسخه‌های جدیدتر (از جمله Microsoft 365) پشتیبانی می‌شود.
در نسخه‌های قدیمی‌تر مثل Excel 2013 و پایین‌تر معمولاً در دسترس نیست و ممکن است با خطای عدم شناسایی تابع مواجه شوید.
اگر فایل را برای کاربری با نسخه قدیمی می‌فرستید، بهتر است خروجی را به صورت مقدار (Copy → Paste Values) هم ذخیره کنید یا از روش‌های جایگزین استفاده کنید.

سؤالات پرتکرار درباره تابع FORECAST.ETS.CONFINT

آیا خروجی تابع، حد بالا و پایین را مستقیم می‌دهد؟

خیر. خروجی «عرض بازه اطمینان» است. باید آن را از/به مقدار FORECAST.ETS کم یا اضافه کنید.

سطح اطمینان 0.95 یعنی چه؟

یعنی اکسل بازه‌ای می‌سازد که انتظار می‌رود مقدار واقعی با احتمال حدود ۹۵٪ داخل آن قرار بگیرد (بر اساس فرضیات مدل ETS و داده‌های تاریخی).

چه زمانی seasonality را 1 بگذاریم؟

وقتی مطمئن هستید داده شما الگوی تکرارشونده فصلی ندارد (یا نمی‌خواهید فصل در مدل لحاظ شود).

اگر timeline تکراری باشد چه کار کنیم؟

aggregation را مناسب انتخاب کنید (مثلاً SUM برای جمع فروش‌های چند تراکنش در یک روز).

آیا می‌شود این تابع را برای داده‌های غیرتاریخی هم استفاده کرد؟

بله، اگر timeline شما یک توالی عددی منظم باشد (مثلاً 1 تا 36 برای 36 ماه)، می‌تواند کار کند؛ اما بهترین حالت زمانی است که محور زمان واقعی و منظم باشد.

جمع‌بندی و پیشنهاد یادگیری بعدی

تابع FORECAST.ETS.CONFINT ابزار مهمی برای حرفه‌ای‌تر کردن پیش‌بینی‌ها در اکسل است، چون به جای یک عدد قطعی، «دامنه قابل انتظار» را هم نشان می‌دهد. با ترکیب آن با FORECAST.ETS می‌توانید حد پایین و حد بالا بسازید، ریسک پیش‌بینی را بسنجید و گزارش‌های مدیریتی قابل اتکاتری ارائه کنید.

پیشنهاد یادگیری بعدی: برای تکمیل این موضوع، توابع زیر را هم یاد بگیرید:

FORECAST.ETS (پیش‌بینی اصلی)
FORECAST.ETS.SEASONALITY (تشخیص طول فصل)
FORECAST.ETS.STAT (بررسی آمارهای مدل و کیفیت پیش‌بینی)
توابع مدیریت داده مثل XLOOKUP، COUNTIF، IF برای ساخت داشبورد پیش‌بینی