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

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

تابع FORECAST.ETS در اکسل برای پیش‌بینی مقادیر آینده بر اساس داده‌های تاریخیِ زمان‌محور استفاده می‌شود. ETS مخفف روش Exponential Triple Smoothing (هموارسازی نمایی سه‌گانه) است؛ یعنی اکسل به‌صورت هوشمند روند (Trend) و در صورت وجود فصل‌پذیری (Seasonality) را از داده‌ها استخراج می‌کند و مقدار آینده را تخمین می‌زند.

اگر فروش ماهانه، تعداد سفارش‌ها، مصرف انرژی، ورودی سایت یا هر شاخصی را در بازه‌های زمانی منظم ثبت کرده‌اید، FORECAST.ETS یکی از بهترین گزینه‌ها برای پیش‌بینی ساده و سریع داخل اکسل است.

مثال ساده: فرض کنید فروش 12 ماه گذشته را دارید و می‌خواهید فروش ماه 13ام را پیش‌بینی کنید.

=FORECAST.ETS(A14;B2:B13;A2:A13)

در این مثال، اکسل با توجه به تاریخ/ماه‌های ستون A و فروش‌های ستون B، مقدار پیش‌بینی‌شده برای تاریخِ داخل A14 را برمی‌گرداند.

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

• پیش‌بینی فروش روزانه/هفتگی/ماهانه بر اساس داده‌های گذشته
• تخمین تقاضا و موجودی مورد نیاز (Inventory Forecasting)
• پیش‌بینی ترافیک سایت، تعداد لیدها یا نرخ تبدیل در آینده
• برآورد مصرف (برق، آب، گاز) در دوره‌های آینده
• تحلیل روند و فصل‌پذیری در داده‌های سری زمانی
• ساخت داشبوردهای مدیریتی با ستون «پیش‌بینی ماه بعد»

ساختار (Syntax)

فرم کلی (انگلیسی):

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

فرم کلی (با توضیح فارسی آرگومان‌ها):

=FORECAST.ETS(تاریخ/زمان_هدف;مقادیر;خط_زمان;[فصل_پذیری];[تکمیل_داده];[تجمیع])

آرگومان‌ها

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

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

values (مقادیر) / داده‌های تاریخی (مثلاً فروش)

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

timeline (خط زمان) / تاریخ‌ها یا نقاط زمانی متناظر با values

محدوده تاریخ‌ها/زمان‌ها که به همان تعداد values وجود دارد. این زمان‌ها باید به ترتیب زمانی باشند (صعودی).

seasonality (فصل‌پذیری) / طول چرخه تکرار

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

• 0 یعنی بدون فصل‌پذیری
• 1 یعنی اکسل خودش تشخیص دهد (حالت خودکار)
• عدد بزرگ‌تر یعنی طول فصل‌پذیری (مثلاً 7 برای داده‌های روزانه با الگوی هفتگی)

data_completion (تکمیل داده) / برخورد با داده‌های جاافتاده

اختیاری است. اگر خط زمان شما بعضی تاریخ‌ها را ندارد (Missing points)، این گزینه مشخص می‌کند اکسل چگونه برخورد کند.

• 1 (پیش‌فرض): اکسل داده‌های جاافتاده را تکمیل می‌کند
• 0: اکسل داده‌های جاافتاده را تکمیل نمی‌کند

aggregation (تجمیع) / نحوه تجمیع در صورت تکراری بودن تاریخ‌ها

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

• 1: AVERAGE (میانگین)
• 2: COUNT (شمارش)
• 3: COUNTA (شمارش غیرخالی)
• 4: MAX (بیشینه)
• 5: MEDIAN (میانه)
• 6: MIN (کمینه)
• 7: SUM (جمع)

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

مثال 1: پیش‌بینی فروش ماه آینده با داده‌های ماهانه

فرض کنید:

• A2:A13 = تاریخ ماه‌ها (مثلاً 1402/01/01 تا 1402/12/01 یا تاریخ میلادی اکسل)
• B2:B13 = فروش هر ماه
• A14 = تاریخ ماه بعد

فرمول پیش‌بینی فروش ماه بعد:

=FORECAST.ETS(A14;B2:B13;A2:A13)

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

مثال 2: خاموش کردن فصل‌پذیری (Seasonality = 0)

اگر مطمئن هستید داده شما فصل‌پذیری ندارد (مثلاً رشد یکنواخت)، فصل‌پذیری را 0 بگذارید:

=FORECAST.ETS(A14;B2:B13;A2:A13;0)

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

مثال 3: تعیین فصل‌پذیری 12 برای داده‌های ماهانه

برای فروش ماهانه که معمولاً چرخه سالانه دارد:

=FORECAST.ETS(A14;B2:B25;A2:A25;12)

نتیجه: اکسل الگوی تکرار 12 ماهه را دقیق‌تر در مدل لحاظ می‌کند (به‌خصوص وقتی حداقل دو سال داده دارید).

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

مثال 1: پیش‌بینی فروش ماه بعد + جمع فروش 3 ماه آینده (ترکیب با SUM)

فرض کنید تاریخ‌های سه ماه آینده در A14:A16 هستند و می‌خواهید مجموع پیش‌بینی سه ماه آینده را داشته باشید:

=SUM(FORECAST.ETS(A14;B2:B13;A2:A13);FORECAST.ETS(A15;B2:B13;A2:A13);FORECAST.ETS(A16;B2:B13;A2:A13))

نتیجه: مجموع پیش‌بینی‌شده برای سه ماه آینده را برمی‌گرداند. این روش برای بودجه‌بندی فصلی ساده کاربردی است.

مثال 2: اگر تعداد داده‌ها کم بود، پیام بده (ترکیب با COUNT و IF)

برای اینکه اگر داده کافی ندارید (مثلاً کمتر از 8 نقطه)، به جای پیش‌بینی پیام نشان دهید:

=IF(COUNT(B2:B100)<8;"داده کافی نیست";FORECAST.ETS(A101;B2:B100;A2:A100))

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

مثال 3: انتخاب خودکار رنج داده‌ها با XLOOKUP (سناریوی واقعی)

فرض کنید در E2 نام محصول را انتخاب می‌کنید و در جدول شما ستون‌های جداگانه برای هر محصول وجود دارد. با XLOOKUP می‌توانید ستون مقادیر آن محصول را پیدا کنید و سپس پیش‌بینی کنید (سناریوی نمونه):

=FORECAST.ETS($A$14;XLOOKUP($E$2;$B$1:$D$1;$B$2:$D$13);$A$2:$A$13)

نتیجه: با تغییر نام محصول در E2، پیش‌بینی برای همان محصول محاسبه می‌شود. (این ساختار زمانی بهترین نتیجه را می‌دهد که داده‌ها به‌صورت ستونی و تمیز باشند.)

مثال 4: شرط‌گذاری با AND و OR برای کنترل تاریخ هدف

اگر می‌خواهید فقط وقتی تاریخ هدف بعد از آخرین تاریخ باشد پیش‌بینی انجام شود:

=IF(AND(A14>MAX(A2:A13);COUNT(B2:B13)=COUNT(A2:A13));FORECAST.ETS(A14;B2:B13;A2:A13);"تاریخ/داده نامعتبر")

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

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

• FORECAST.ETS + IF برای کنترل داده کافی یا نمایش پیام

  =IF(COUNT(B2:B13)<12;"حداقل 12 ماه داده وارد کنید";FORECAST.ETS(A14;B2:B13;A2:A13;12))

• FORECAST.ETS + SUM برای جمع چند پیش‌بینی آینده

  =SUM(FORECAST.ETS(A14;B2:B13;A2:A13);FORECAST.ETS(A15;B2:B13;A2:A13))

• FORECAST.ETS + COUNTIF برای فیلتر کیفیت داده (مثلاً حذف صفرها به‌عنوان داده نامعتبر)

  =IF(COUNTIF(B2:B13;0)>0;"در داده‌ها مقدار صفر وجود دارد";FORECAST.ETS(A14;B2:B13;A2:A13))

• FORECAST.ETS + XLOOKUP برای پیش‌بینی بر اساس انتخاب کاربر (محصول/شعبه)

  =FORECAST.ETS($A$14;XLOOKUP($E$2;$B$1:$D$1;$B$2:$D$13);$A$2:$A$13)

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

1) خطای #N/A

علت‌های رایج: طول محدوده values و timeline برابر نیست، یا target_date خارج از منطق زمانی داده‌هاست (مثلاً timeline درست تشخیص داده نشده یا تاریخ‌ها متن هستند).

راه‌حل: مطمئن شوید تعداد سلول‌های values و timeline دقیقاً برابر است و تاریخ‌ها «واقعی» هستند (نه متن). در صورت نیاز، فرمت تاریخ را اصلاح کنید یا داده را مجدد وارد کنید.

2) خطای #VALUE!

علت‌های رایج: وجود متن/کاراکتر غیرعددی در values، یا تاریخ‌های timeline به شکل متن ذخیره شده‌اند، یا target_date عددی/تاریخی معتبر نیست.

راه‌حل: ستون values را از نظر متن، فاصله اضافی، خط تیره، یا مقادیر غیرعددی پاکسازی کنید. تاریخ‌ها را با فرمت Date واقعی اکسل ذخیره کنید.

3) خطای #NUM!

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

راه‌حل: seasonality را 1 (خودکار) یا 0 یا یک عدد منطقی مثل 12 قرار دهید. همچنین حداقل به اندازه کافی داده (ترجیحاً بیش از یک چرخه کامل) داشته باشید.

4) خروجی غیرمنطقی (خیلی بزرگ/خیلی کوچک)

علت‌های رایج: وجود outlier (نقطه پرت)، تغییر ناگهانی فرآیند (مثلاً تغییر قیمت/کمپین)، یا داده‌های نامنظم/ناپاک.

راه‌حل: داده را پاکسازی کنید، نقاط پرت را بررسی کنید، و اگر کسب‌وکار تغییر ساختاری داشته، داده‌های خیلی قدیمی را جداگانه تحلیل کنید. در صورت نیاز seasonality را دستی تنظیم کنید.

5) نتیجه دقیق نیست چون فاصله زمانی‌ها منظم نیست

علت‌های رایج: timeline فاصله‌های نامنظم دارد (مثلاً برخی ماه‌ها حذف شده یا تاریخ‌ها جابه‌جا هستند).

راه‌حل: timeline را مرتب (Sort) کنید و در صورت نبودن برخی تاریخ‌ها، از data_completion=1 استفاده کنید یا داده را کامل کنید.

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

• از تاریخ واقعی اکسل استفاده کنید: اگر ماه‌ها را به صورت متن (مثل “فروردین”) نوشته‌اید، بهتر است یک ستون تاریخ واقعی داشته باشید.
• حداقل یک تا دو چرخه کامل داده داشته باشید: برای داده ماهانه با seasonality=12، داشتن حداقل 24 ماه داده معمولاً نتایج بهتری می‌دهد.
• timeline را حتماً صعودی و مرتب نگه دارید: پیش‌بینی سری زمانی روی ترتیب داده حساس است.
• اگر تاریخ‌های تکراری دارید، aggregation را مشخص کنید: برای فروش روزانه با چند تراکنش در روز، معمولاً SUM انتخاب خوبی است.
• در داشبوردها، کنار پیش‌بینی حتماً مقدار واقعی را هم نگه دارید: تا بتوانید خطا و کیفیت پیش‌بینی را ماه‌به‌ماه بسنجید.
• برای تحلیل کامل‌تر از توابع خانواده ETS کمک بگیرید: مثل FORECAST.ETS.SEASONALITY یا FORECAST.ETS.CONFINT (اگر نسخه اکسل شما پشتیبانی کند).

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

• FORECAST.ETS در برابر FORECAST.LINEAR

  FORECAST.LINEAR فقط یک خط روند خطی را مدل می‌کند و فصل‌پذیری را نمی‌فهمد. اما FORECAST.ETS می‌تواند روند + فصل‌پذیری را مدل کند و معمولاً برای داده‌های واقعی (که الگوی تکرارشونده دارند) بهتر است.

• FORECAST.ETS در برابر TREND

  TREND هم بر پایه رگرسیون خطی است و بیشتر برای برازش خط و پیش‌بینی خطی استفاده می‌شود. FORECAST.ETS برای سری زمانی و الگوهای تکرارشونده مناسب‌تر است.

• FORECAST.ETS در برابر ابزار Forecast Sheet

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

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

تابع FORECAST.ETS در نسخه‌های جدید اکسل ارائه شده و معمولاً در این موارد در دسترس است:

• Excel 2016 و بالاتر (ویندوز)
• Microsoft 365 (همیشه به‌روز)
• در بسیاری از نسخه‌های Excel برای Mac جدید نیز وجود دارد (بسته به نسخه و به‌روزرسانی)

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

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

آیا FORECAST.ETS فقط برای تاریخ (Date) کار می‌کند؟

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

آیا می‌توانم چند قدم جلوتر را پیش‌بینی کنم؟

بله. کافی است target_date را برای تاریخ‌های آینده مختلف بگذارید (مثلاً ماه‌های بعدی) و برای هرکدام FORECAST.ETS بزنید.

اگر داده‌هایم نامنظم باشد چه؟

بهتر است داده را منظم کنید. اگر برخی نقاط زمانی جا افتاده‌اند، data_completion=1 کمک می‌کند، اما کیفیت پیش‌بینی به کیفیت و نظم داده وابسته است.

فصل‌پذیری را خودکار بگذارم یا دستی تعیین کنم؟

برای شروع خودکار (seasonality=1) خوب است. اگر می‌دانید چرخه دقیقاً چیست (مثل 12 ماه)، تعیین دستی معمولاً پایدارتر و قابل کنترل‌تر است.

آیا FORECAST.ETS مقدار دقیق آینده را تضمین می‌کند؟

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

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

تابع FORECAST.ETS یک ابزار قدرتمند و در عین حال ساده برای پیش‌بینی سری زمانی در اکسل است؛ به‌خصوص وقتی داده‌های شما روند و/یا فصل‌پذیری دارند. با تنظیم درست timeline، پاکسازی داده‌ها، و انتخاب مناسب seasonality و aggregation می‌توانید خروجی‌های بسیار کاربردی برای فروش، تقاضا و برنامه‌ریزی بسازید.

پیشنهاد یادگیری بعدی: بعد از تسلط بر FORECAST.ETS، سراغ این موارد بروید:

• آشنایی با Forecast Sheet و تنظیمات آن برای گزارش‌های مدیریتی
• یادگیری توابع تکمیلی خانواده ETS مثل تشخیص فصل‌پذیری و محدوده اطمینان (در صورت پشتیبانی نسخه اکسل)
• پاکسازی داده‌ها با Power Query برای بهبود کیفیت پیش‌بینی
• ساخت داشبورد پیش‌بینی با ترکیب XLOOKUP، جدول‌ها (Excel Table) و نمودارها