مثالی از پیاده‌سازی روش پیش‌بینی در اکسل (نمو هموار با تصحیح روند)

تعریف مسأله

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

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

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

گام اول. تعیین ستون‌ها

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

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

در ستون دوم، نتایج پیش‌بینی با روش هموارسازی نمایی ساده (نمو هموار ساده) نمایش داده می‌شود.

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

در ستون چهارم، مقادیر جبران روند داده‌ها (جبران الگوی Trend) نمایش داده می‌شود.

در ستون پنجم، داده‌های مربوط به پیش‌بینی نهایی (پس از تصحیح شدن روند) نشان داده می‌شود.

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

به‌صورت کلّی می‌خواهیم یک بار از روش هموارسازی نمائی ساده برای پیش‌بینی استفاده کنیم (نتایج ستون دوم) و خطای آن را به‌دست بیاوریم، سپس پیش‌بینی نهایی را با تصحیح روندها انجام می‌دهیم و خطای آن را هم محاسبه می‌کنیم تا مشخص شود چه تأثیری روی دقت پیش‌بینی گذاشته است.

گام دوم. ورود داده‌ها

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

گام سوم. محاسبه‌ی رابطه‌ی هموار سازی نمایی ساده

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

زمان فرمول‌نویسی می‌توانیم یک مقدار مشخص برای آلفا (مثلاً ۰٫۸) انتخاب کنیم، اما این کار ما را محدود می‌کند و اگر بعداً تصمیم بگیریم از مقدار دیگری استفاده کنیم (مثلاً آلفای ۰٫۹) به فرمول‌نویسی مجدد نیاز خواهد داشت.

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

اگر از قبل با اکسل آشنا نباشید احتمالاً جدول شما به زیبایی تصویر بالا نیست، ما فونت‌ همه‌ی سلول‌ها را تغییر دادیم و از رنگ‌های مختلف استفاده کردیم اما هنوز از لحاظ ریاضی کاری انجام ندادیم.

برای فرمول نویسی باید به درس هموارسازی نمایی رجوع کنیم. گفته بودیم که امکان پیش‌بینی برای اولین دوره وجود ندارد چون اطلاعات قبل از آن را نداریم و برای دوره‌ی دوم هم باید یک مقدار فرضی وارد کنیم که در تصویر زیر، همان عدد ۱۷۳ را وارد کردیم.

اما از دوره‌ی سوم باید محاسبات شروع شود و فرمول‌نویسی را از همین خانه شروع می‌کنیم.

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

مطابق رابطه‌ی هموارسازی نمائی که بالاتر نوشتیم، دو جمله‌ی ریاضی داریم که در جمله‌ی اول، آلفا در مقدار واقعی دوره‌ی قبلی ضرب می‌شود و در جمله‌ی دوم (۱-a) در مقدار پیش‌بینی شده‌ی دوره‌ی قبلی ضرب می‌شود.

اگر با این رابطه آشنایی ندارید، حتماً به درس هموارسازی نمائی بازگردید و مطالعه‌ی این درس را به زمان دیگری موکول کنید.

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

اکسل به‌طور خودکار نام این خانه را وارد می‌کند، حالا باید کمی با علائم ریاضی در اکسل آشنا شوید:

* برای ضرب استفاده می‌شود.

/ برای تقسیم استفاده می‌شود.

 + برای جمع استفاده می‌شود.

 – برای تفریق استفاده می‌شود.

برای تکمیل جمله‌ی اول،‌ علامت * را قرار داده و روی فروش واقعی دوره‌ی قبل (۹۸) کلیک می‌کنیم.

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

این پرانتز باید در پیش‌بینی فروش دوره‌ی قبل (۱۷۳) ضرب می‌شود:

حالا اگر دکمه‌ی Enter را فشار دهیم همان مقدار ۱۷۳ نمایش داده می‌شود چون هنوز مقدار آلفا را وارد نکردیم و اکسل آن را صفر در نظر گرفته است، پس آلفا را ۰٫۸ انتخاب می‌کنیم تا نتیجه‌ی پیش‌بینی دوره‌ی سوم نمایش داده شود. (۱۱۳)

حالا نوبت خانه‌های مربوط به ردیف‌های چهارم و پنجم و … است اما خوشبختانه لازم نیست این فرمول را برای آن‌ها بنویسیم و اکسل می‌تواند به‌صورت خودکار کدنویسی کند، اما برای این‌کار، اکسل باید بداند که مقدار آلفا در تمام خانه‌های بعدی همان ۰٫۸ (یا هر مقداری که وارد کردیم) می‌ماند و تغییر نمی‌کند. (وگرنه بلاتکلیف می‌شود که چه مقداری برای آن در نظر بگیرد)

برای اصلاح رابطه‌ای که نوشته بودیم، دوباره روی همان خانه‌ی ردیف سوم کلیک می‌کنیم تا رابطه در نوار بالای صفحه ظاهر شود:

در رابطه‌ی که می‌بینید، خانه‌ی H4 همان ورودی است که برای آلفا در نظر گرفته بودیم و اگر آن را به شکل $H$4 بنویسیم اکسل متوجه می‌شود که این یک ورودی است و مقدارش در طول پیش‌بینی خانه‌های بعدی ثابت می‌ماند.

توجه کنید که در این رابطه دو بار از H4 استفاده شده است و هر دوی آن‌ها باید اصلاح شود:

وقتی کارمان تمام شد Enter را می‌زنیم تا تغییرات ثبت شود و برای این‌که خانه‌های بعدی به‌صورت خودکار کدنویسی شوند، دوباره روی همان خانه کلیک می‌کنیم. ابتدا تصویر زیر را ببینید تا کارمان را ادامه دهیم:

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

در این لحظه تمام این خانه‌ها کدنویسی شده‌اند و مقدار پیش‌بینی را برای دوره‌های بعدی نشان می‌دهند.

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

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

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

برای این‌کار روی دومین خانه‌ کلیک می‌کنیم (برای دوره‌ی اول مقداری پیش‌بینی نکرده بودیم) و فرمول زیر را می‌نویسیم:

تابع () ABS همان قدر مطلق است که مقدار داخل پرانتز را مثبت می‌کند، پس داخل پرانتز باید تفاضل مقدار پیش‌بینی با فروش واقعی وارد شود. خانه‌ی G4 فروش واقعی دوره‌ی دوم و خانه‌ی F4 پیش‌بینی دوره‌ی دوم را نشان می‌دهد.

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

خوشبختانه در این مرحله ورودی خاصی نداشتیم (آلفا یا بتا) و نیازی به اطلاح نبود.

گام پنجم. تنظیم مقدار جبرانی روند

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

جمله‌ی اول این رابطه همان پیش‌بینی اولیه است که در گام‌ سوم فرمول آن را نوشتیم و حالا باید جمله‌ی دوم را کدنویسی کنیم تا مقادیر جبرانی (برای تصحیح روند) حساب شود. مقدار جبرانی T از رابطه‌ی زیر محاسبه می‌شود:

در حقیقت مقدار جبرانی را هم با روش هموارسازی نمایی محاسبه می‌کنیم.

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

برای دوره‌ی دوم، چون پیش‌بینی برای دوره‌ی اول انجام نشده است باید یک مقدار فرضی انتخاب کنیم که آن را صفر قرار می‌دهیم. (وقتی خانه‌ای را خالی می‌گذاریم، اکسل خودش آن را صفر در نظر می‌گیرد)

کدنویسی را از ردیف سوم و با قرار دادن علامت = و تعیین ورودی بتا شروع می‌کنیم.

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

سپس مقدار پیش‌بینی شده برای دوره‌ی قبلی را از آن کسر می‌کنیم:

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

برای این مثال مقدار بتا را ۰٫۵ در نظر می‌گیریم، آن را وارد می‌کنیم تا روند برای دوره‌ی سوم پیش‌بینی شود:

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

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

گام ششم. پیش‌بینی نهایی (هموارسازی نمایی با تصحیح روند)

اگر نتایج محاسبه شده به روش نمو هموار ساده را با مقادیر ستون تنظیم Trend جمع کنیم، مقادیر پیش‌بینی نهایی محاسبه می‌شود.

پس روی اولین خانه‌ی ردیف سوم کلیک می‌کنیم و فرمول ساده‌ی زیر را می‌نویسیم:

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

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

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

در این مرحله دقیقاً گام چهارم را تکرار می‌کنیم، با این تفاوت که به‌جای مقادیر ستون دوم، مقادیر ستون پنجم را از ستون اول کسر می‌کنیم.

گام هشتم. محاسبه‌ی مجموع خطا

تا این لحظه دو ستون برای قدر مطلق خطا داریم که یکی اشتباهات پیش‌بینی اولیه و دیگری اشتباهات پیش‌بینی نهایی را نشان می‌دهد.

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

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

از پنجره‌ی باز شده، وارد تب Total می‌شویم و گزینه‌ی Average  را انتخاب می‌کنیم.

همین کار را برای ستون دیگر تکرار می‌کنیم تا میانگین خطای هر دو پیش‌بینی (اولیه و نهایی) محاسبه شود.

همان‌طور که می‌بینید با تصحیح روند، میانگین قدر مطلق خطا حدوداً سه واحد کاهش یافته و از ۴۳٫۹۱۸ به ۴۰٫۶۹۳ رسیده است.

گام نهم. تنظیم تعداد اعشارها

همان‌طور که می‌بینید تعداد ارقام اعشار زیادی نشان داده می‌شود و این موضوع جدول را شلوغ و نامرتب کرده است، در گام نهم قصد داریم که تعداد ارقام اعشار را به دو رقم کاهش دهیم.

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

در پنجره‌ی باز شده روی Number کلیک کرده و مقدار Decimal Places را روی دو قرار می‌دهیم.

حالا داده‌ها فقط تا دو رقم اعشار نمایش داده می‌شوند و جدول تمیزتری داریم.

اگر کمی جدول را مرتب‌تر کنیم، نهایتاً به تصویر زیر می‌رسیم.

گام دهم. تعیین مقادیر آلفا و بتا و پیش‌بینی ماه آینده

در این مرحله می‌توانیم مقادیر آلفا و بتا را تغییر دهیم تا با توجه به تغییرات میانگین قدر مطلق خطا،‌ بهترین ضرایب را پیدا کنیم.

مثلاً در تصویر زیر آلفا را ۰٫۶ و بتا را ۰٫۸ قرار دادیم، تغییر نتایج را ببینید:

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

می‌توانیم آن‌قدر مقادیر آلفا و بتا را عوض کنیم تا نهایتاً به کمترین میانگین قدر مطلق خطا برسیم.

برای پیش‌بینی مقادیر دوره‌ی بعدی چه کار کنیم؟

در این مثال فروش دوره‌هایی را پیش‌بینی کردیم که مربوط به گذشته بودند و می‌خواستیم مقدار خطا و بهترین ضرایب آلفا و بتا را حدس بزنیم، اما اگر بخواهیم دوره‌ی بعدی را پیش‌بینی کنیم راه‌حل چیست؟

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

حالا آخرین خانه‌ی ستون پیش‌بینی با نمو هموار ساده را انتخاب می‌کنیم و تا خانه‌ی پایین می‌کشیم تا ردیف بعدی کدنویسی شود:

همین کار را برای ستون تنظیم Trend و پیش‌بینی با نمو هموار تنظیم شده انجام می‌دهیم و خواهیم داشت:

پس پیش‌بینی کردیم که در دوره‌ی بعدی ۴۴۶ واحد فروش خواهیم داشت.

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