پایگاه علمی سعید سان دانلود رایگان مقالات علمی کنفرانس و ژورنال
مدل سری زمانی در اکسل — به زبان ساده
پیش‌بینی آینده کاری بسیار دشوار است ولی خوشبختانه بسیاری از پدیده‌های مبتنی بر زمان، دارای الگوهای مشخصی هستند که با استفاده از مطابقت دادن آن‌ها با این الگو‌ها، امکان پیش‌بینی آینده میسر می‌شود. البته همیشه این پیش‌بینی‌ها با خطا همراه هستند ولی طبق گفته «گذشته چراغ راه آینده است» می‌توانیم با تکیه بر رفتار گذشته یک پدیده (حتی پدیده تصادفی) آینده را تصویر کنیم و خطا را به حداقل برسانیم. در این نوشتار یاد می‌گیریم که چگونه براساس داده‌ها، مدل سری زمانی در اکسل ایجاد کنیم و از آن برای پیش‌بینی مقادیر آینده کمک بگیریم.
مدل سری زمانی در اکسل
اکسل یک ابزار مناسب برای تحلیل داده‌ها محسوب می‌شود و در بین کاربران، بسیار محبوب بوده و به یک برنامه همه کاره، بخصوص برای متخصصین داده (Data Scientists) تبدیل شده است. در این نوشتار قصد داریم از ابزارها و توابعی که در اکسل گنجانده شده برای پیش‌بینی داده‌های مرتبط با زمان یا سری زمانی (Time Series) بهره ببریم. مدل سری زمانی در اکسل را به دو شیوه می‌توان ایجاد کرد. یکی از این روش‌ها، استفاده از توابع و دیگری استفاده از راهنما و ابزار مدل سری زمانی در اکسل است که به واسطه یک کاربرگ پیش‌بین، پارامترهای مدل سری زمانی را از کاربر دریافت و محاسبات را انجام می‌دهد.
همانطور که می‌دانید، یک دنباله یا سری زمانی می‌تواند دارای مولفه‌های مختلفی مانند روند (Trend)، تغییرات فصلی (Seasonality) و … باشد. در نسخه‌های اکسل ۲۰۱۶ به بعد ابزاری قرار گرفته است که شما را قادر می‌سازد، پیش‌بینی و مدل‌سازی داده‌های سری زمانی را به سرعت و البته دقت انجام دهید و بتوانید مقادیر آینده فرآیند سری زمانی مورد نظرتان را پیش‌بینی کنید. همچنین توابعی نیز به این منظور در نسخه‌های جدید اکسل وجود دارد که برای مدل‌سازی سری‌های زمانی مفید است.
پیش‌بینی به کمک تابع Forecast در اکسل
در نسخه‌های مختلف اکسل، تابع Forecast به امر پیش‌بینی داده‌های سری زمانی می‌پردازد. این تابع در نسخه‌های قدیمی اکسل وجود داشته و به علت سازگاری در نسخه‌های جدید اکسل نیز گنجانده شده است. ولی در نسخه‌های جدیدتر اکسل توابع دیگری نیز به منظور ایجاد مدل‌های پیچیده‌تر وجود دارد. به یاد داشته باشید که شیوه پیش‌بینی در تابع Forecast، به صورت خطی است و بوسیله تعیین روند داده‌ها، آینده و داده‌های مربوط آن را پیش‌بینی می‌کند. به علت سادگی مدل به کار رفته در این تابع، ممکن است استفاده از آن همیشه کارساز نباشد و فقط برای مواقعی که روند سری زمانی خطی باشد، این تابع می‌تواند پاسخ‌های مناسب ایجاد کند.
مدل سری زمانی در اکسل با تابع Forecast به همراه پارامترهای آن به صورت زیر است.
FORECAST(x, known_y’s, known_x’s)
به این ترتیب مشخص است که برای مدل‌سازی به سه پارامتر احتیاج داریم:
- پارامتر اول یا x، مقدار متغیر مستقلی است که در آن نقطه می‌خواهید مقدار سری زمانی را مشخص کنید و در حقیقت مقدار y را پیش‌بینی کنید.
- پارامتر known_y’s، برداری از مشاهدات وابسته به زمان است که در مدل‌سازی به کار می‌روند.
- پارامتر known_x’s نیز برداری از مشاهدات متغیر مستقل است که به کمک رابطه بین آن‌ها و پارامتر known_y’s، مدل سری زمانی شکل گرفته و مقدار x، پیش‌بینی می‌شود.
نحوه محاسبه پارامترهای این مدل سری زمانی درست به مانند مدل رگرسیون ساده (OLS) است. به این ترتیب خواهیم داشت:
$$large a = bar{y}-bbar{x}$$
$$large b = dfrac{sum_{i=1}^n(x_i-bar{x})(y_i-bar{y})}{sum_{i=1}^n(x_i-bar{x})}$$
در نتیجه مدل سری زمانی در اکسل به صورت زیر نوشته خواهد شد.
$$large y = a +bx$$
با برآورد پارامترهای $$a$$ و $$b$$، کار پیش‌بینی انجام شده و نتیجه برای نقطه دلخواه بدست می‌آید.
مثال ۱
سری زمانی زیر را در نظر بگیرید. مشخص است که این داده‌ها در ۵ نوبت زمانی اندازه‌گیری شده‌اند و می‌خواهیم برای مقدار مشاهده ۳۰ (یا زمان ۶) برای متغیر x، مقدار y را پیش‌بینی کنیم.
| زمان | x | y |
| 1 | 20 | 6 |
| 2 | 28 | 7 |
| 3 | 31 | 9 |
| 4 | 38 | 15 |
| 5 | 40 | 21 |
| 6 |
در تصویر زیر که مربوط به یک کاربرگ اکسل است، این کار را به کمک تابع FORECAST انجام داده‌‌ایم.
همانطور که مشخص است براساس مدل رگرسیونی، مقدار سری زمانی در زمان ۶، براساس مشاهدات قبلی، ۱۰٫607 برآورد شده است.
در ادامه از توابع دیگری که در ابزار سری‌زمانی اکسل وجود دارد، کمک می‌گیریم و مدل سری زمانی را براساس هموارسازی نمایی ایجاد می‌کنیم، بطوری که به این ترتیب قادر به تشخیص تغییرات فصلی و تاثیر آن‌ها در مدل سری زمانی می‌شویم.
استفاده از هموارسازی نمایی برای پیش‌بینی با کاربرگ پیش‌بین
روش‌های مختلفی برای پیش‌بینی داده‌های وابسته به زمان وجود دارد. یکی از این شیوه‌ها، هموارسازی (Smoothing) است که به طور خاص هموارسازی نمایی (Exponential Smoothing)، در این بین از اهمیت بیشتری برخوردار است زیرا وزن مشاهداتی که به زمان حال نزدیک‌ترند در امر پیش‌بینی نسبت به داده‌های دور بیشتر است.
در بسیاری از مواقع، داده‌ها و اطلاعات مربوط به کسب و کار، دارای الگوی سری زمانی با تغییرات فصلی (Seasonality) هستند. به عنوان مثال، فروش بستنی در ماه‌های گرم سال بیشتر از ماه‌های دیگر است و این چرخه بطور سالانه تکرار می‌شود.
در ادامه با استفاده از یک مثال و با ابزار کاربرگ پیش‌بین اکسل (Forecasting Sheet)، داده‌های مربوط به آینده را پیش‌بینی کرده و براساس نمودارهای ترسیم شده، نتیجه پیش‌بینی را مطابق با مدل سری زمانی در اکسل بررسی می‌کنیم.
ممکن است طول دوره تغییرات فصلی برای داده‌ها مشخص باشد ولی در اغلب موارد این مقدار مشخص نیست. ابزار Forecasting اکسل، دوره تغییرات فصلی را بطور خودکار اندازه‌گیری کرده و در مدل به کار می‌برد. بهتر است برای گرفتن نتایج بهتر توسط ابزار Forecasting اکسل، داده‌های سری زمانی شامل حدود ۲ تا ۳ دوره تغییرات فصلی باشند تا نتایج بدست آمده با دقت بیشتری محاسبه شوند.
مثال ۲
مجموعه داده‌های سری زمانی که در کاربرگ اکسل به نام Forecast-ets_example.xlsx موجود است، موضوع این مثال است. همچنین برای دسترسی به ابزار پیش‌بین اکسل از برگه Data و در قسمت Forecast دستور Forecast sheet را انتخاب کنید. صفحه‌ای به صورت زیر باز می‌شود.
نکته: توجه داشته باشید که هنگام اجرای این دستور، یکی از خانه‌های کاربرگ اطلاعاتی را انتخاب کرده باشید. به این موضوع نیز توجه داشته باشید که حتما یکی از ستون‌های اطلاعاتی (ترجیحا ستون اول) شامل مقادیر تاریخی باشد تا اکسل توالی داده‌های زمانی را درک کند و قادر به تشخیص تغییرات فصلی بشود.
اگر می‌خواهید روی مجموعه داده‌ها و نتایج خروجی در مدل سری زمانی، تسلط بیشتری داشته باشید بهتر است دکمه Options را انتخاب کنید تا ستون‌ها یا نواحی مربوط به زمان (Timeline Range)، مقادیر متغیر وابسته به زمان (Values Range) و همچنین نوع تشخیص تغییرات فصلی (Seasonality) را مشخص کنید. نتیجه انتخاب دکمه Options در تصویر زیر دیده می‌شود.
خوشبختانه اکسل نواحی که مربوط به مقادیر پیش‌نیاز بوده را در قسمت Timeline Range و Value Range، تشخیص داده و با توجه به این نواحی، در دوره‌ای زمانی که توسط Forecasting Start و Forecasting End مشخص شده، پیش‌بینی سری زمانی را انجام می‌دهد. به منظور تشخیص خودکار تغییرات فصلی توسط اکسل کافی است در قسمت Seasonality گزینه Detect Automatically را فعال کنید. همچنین امکان تعیین مقدار پارامتر تغییرات فصلی توسط گزینه Set Manually نیز وجود دارد.
اگر می‌خواهید در اکسل علاوه بر مقادیر پیش‌بینی، گزارشی هم در مورد پارامترها و برآوردهای حاصل از مدل سری زمانی، دریافت کنید، گزینه Include forecasting statistics را فعال کنید. اگر در بازه‌هایی از زمان، مقادیر گمشده (Missing Point) دارید بوسیله درون‌یابی (Interpolation) می‌توانید مقادیر آن‌ها را برآورد کنید و در سری زمانی به کار برید. همچنین در صورت وجود مقادیر تکراری در زمان‌های یکسان، میانگین (Average) مقادیر برای برآورد به کار می‌رود.
نکته: اگر می‌خواهید به جای نمودار خطی (Line Chart)، سری زمانی و مقادیر پیش‌بینی شده، توسط یک نمودار ستونی (Bar Chart)، نشان داده شوند در کنار نمودار، دکمه مربوطه را انتخاب کنید.
با فشردن دکمه Create، یک کاربرگ جدید تشکیل شده که شامل مدل سری زمانی در اکسل، مقادیر و توابعی است که برای پیش‌بینی سری زمانی به کار رفته است.
در این بین، نموداری که شامل مقادیر واقعی و پیش‌بینی شده از سری زمانی نیز هست در میان کاربرگ قرار می‌گیرد. در این نمودار مقادیر پیش‌بینی شده توسط خطوط نارنجی رنگ به نمایش آمدهاند و مقادیر اصلی به رنگ آبی هستند. همچنین برای خط مربوط به مقادیر پیش‌بینی یک فاصله اطمینان (Confidence Intervals) نشان داده شده است که معمولا سطح اطمینان برایش ۹۵٪ تنظیم شده. اگر می‌خواهید سطح اطمینان را تغییر دهید کافی است در گزینه Confident Interval، سطح اطمینان را به دلخواه تغییر دهید. توجه داشته باشید که هر چه سطح اطمینان را بیشتر کنید، فاصله خطوط اطمینان از خط نارنجی اصلی بیشتر می‌شود تا پیش‌بینی با اطمینان بیشتری صورت گیرد. واضح است که در این حالت، دقت برآورد نیز کاهش خواهد یافت. در نتیجه، افزایش اطمینان به قیمت کاهش یافتن دقت خواهد بود.
نکته: اگر سلول‌هایی را که حاوی مقادیر پیش‌بینی هستند، انتخاب کنید، فرمول‌های محاسباتی و توابع به کار رفته در آن‌ها را مشاهده می‌کنید. پس به این ترتیب ابزار پیش‌بینی سری زمانی در اکسل، فقط یک راهنما برای درج این توابع است و محاسبات توسط تابع FORECAST.ETS بدست آمده و فواصل اطمینان نیز با تابع FORECAST.ETS.CONFINT حاصل شده‌اند.
برای مثال برای نقطه اول از پیش‌بینی که مربوط به تاریخ 10/1/2013 (یا ۱۳-oct) است، توابع به کار رفته به همراه پارامترهایشان به صورت زیر است.
$$large =FORECAST.ETS(A59,$B$2:$B$58,$A$2:$A$58,1,1)$$
همچنین ناحیه اطمینان پایین و بالایی به ترتیب به مانند زیر نوشته شده و مقادیر مربوطه بدست آمدهاند.
$$large =C59-FORECAST.ETS.CONFINT(A59,$B$2:$B$58,$A$2:$A$58,0.95,1,1)$$
$$large =C59+FORECAST.ETS.CONFINT(A59,$B$2:$B$58,$A$2:$A$58,0.95,1,1)$$
برای نمایش پارامترهای مدل سری زمانی که در اینجا معمولا مدل هولت وینترز (Holt-Winters) است، گزینه Include Forecasting Statistics را انتخاب کنید. نتایج در کاربرگ نیز در سمت راست بالای صفحه نمایش داده می‌شود. همانطور که در دیگر نوشتارهای فرادرس خوانده‌اید، پارامترهای مدل هولت وینترز، $$alpha$$، $$beta$$ و $$gamma$$ هستند که در این قسمت نیز با همین اسامی دیده می‌شوند.
ارزیابی نتایج پیش‌بینی سری زمانی
دقت و صحت نتایج پیش‌بینی قبل از به کارگیری مدل ایجاد شده، باید مورد بررسی قرار گیرد. روش‌های مختلفی برای ارزیابی مدل‌های سری زمانی وجود دارد. بررسی نتایج پیش‌بینی برای داده‌های گذشته یکی دیگر از کارهایی است که می‌توانید برای تعیین کارایی یک مدل انجام دهید.
برای انجام این کار می‌توانید در قسمت آغاز پیش‌بینی سری زمانی در فرم Forecasting Form، تغییراتی بوجود آورید و مثلا پیش‌بینی را از تاریخی که مقادیر آن در سری زمانی موجود است، آغاز کنید. در تصویر زیر این کار صورت گرفته است.
همانطور که مشاهده می‌کنید، مقادیر واقعی با نتایج پیش‌بینی بسیار نزدیک هستند و می‌توان گفت که مدل سری زمانی در اکسل برای داده‌های گذشته، به خوبی عمل کرده است و دارای کارایی مناسب است.
البته اگر می‌خواهید دو مدل سری زمانی را با یکدیگر مقایسه کنید، بهتر است به طول فاصله اطمینان برای نقاط پیش‌بینی توجه کنید. هر چه طول بازه اطمینان کوچک‌تر باشد، دقت برآورد در آن مدل بیشتر است.
خلاصه و جمع‌بندی
در این نوشتار نحوه محاسبات پیش‌بینی سری زمانی در اکسل را آموختیم. در این میان به کمک توابع و همچنین ابزار مفیدی که اکسل برای پیش‌بینی سری زمانی در اختیار کاربران قرار داده، آشنا شدیم و به حل مسئله‌هایی با داده‌های واقعی پرداختیم.
در انتها نیز روش‌های ارزیابی مدل سری زمانی حاصل را مورد بررسی قرار دادیم. از آنجایی که شیوه کار با اکسل برای تحلیل سری زمانی بسیار ساده و در عین حال دقیق است، کاربران زیادی را به خود جلب کرده است. همچنین استفاده از روش‌های نوین مانند تکنیک هموارسازی هولت وینترز و هموارسازی نمایی در کاربرگ پیش‌بین اکسل به قابلیت‌های آن افزوده است.منبع:فرادرس