سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

پیش بینی در اکسل
۱/۵ - (۲ امتیاز)

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

پیش بینی در اکسل

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

مایکروسافت اکسل یک سری امکانات متنوع در اختیارمون قرار میده که شامل، ابزارها، توابع و نمودار هست که بسته به نیاز، باید یکی از روش های زیر رو انتخاب کنیم:

پیش بینی نمایی Exponential Smoothing Forecast: پیش بینی بر اساس سری های زمانی پیشین با چرخه های فصلی یا هر چرخه دیگری

پیش بینی خطیLinear Forecast: پیش بینی آینده با استفاده از رگرسیون خطی

نحوه پیش بینی کردن با روش Exponential Smoothing

این روش در اکسل بر مبنای ورژن AAA از الگوریتم Exponential Triple Smoothing استوار است. این روش با پیدا کردن الگوهای فصلی (بازه ای) و فاصله اطمینان، انحراف در داده های گذشته رو کاهش میدن. این روش پیش بینی بهترین روش برای داد ههای غیر خطی با الگوهای فصلی تکرار شونده هست. این تکنیک در اکسل ۲۰۱۶ و ۲۰۱۹ در دسترس هست و همچنین Office 365.

ایجاد شیت پیش بینی نمایی

Forecast sheet که یکی از امکانات موجود در اکسل ۲۰۱۶ هست، پیش بینی داده ها رو خیلی آسون کرده. کافیه که فقط داده ها رو مرتب کنیم و به عنوان ورودی این ابزار تخصیص بدیم، اکسل بقیه کار رو خودش انجام میده.

مرتب کردن داده ها جهت استفاده در Forecast Sheet

در یک شیت اکسل، داده ها رو در ستون مجزا وارد میکنیم.

  • سری زمانی – تاریخ یا زمان ثبت شده که قاعده مند هستند و در بازه های مشخص تکرار میشن مثلا داده های ثبت شده بصورت ساعتی، روزانه، ماهانه، سالانه و…
  • سری عددی- مقادیر عددی که برای سری های زمانی ثبت شده اند.

خیلی مهمه که سری زمانی ما بازه های برابر داشته باشه. مثلا میتونیم بازه هفته ای در نظر بگیریم و هر دوشنبه داده مورد نظر رو ثبت کنیم. یا بازه ماهانه در نظر بگیریم با ثبت داده در اول هر ماه و …

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

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

ایجاد سری زمانی و میزان فروش در هر ماه

شکل ۱- ایجاد سری زمانی و میزان فروش در هر ماه

ایجاد Forecast sheet

برای استفاده از ابزار پیش بینی در اکل  مراحل زیر رو انجام میدیم:

  1. هر دو سری رو انتخاب میکنیم. اگر یک سلول رو هم انتخاب کنیم کافیه و اکسل خودش محدوده رو میتونه تشخیص بده.
  2. به تب Data رفته و از قسمت Forecast گزینه Forecast Sheet رو انتخاب میکنیم:

انتخاب گزینه Forecast Sheet

شکل ۲- انتخاب گزینه Forecast Sheet

  1. پنجره ایجاد شیت پیش بینی، پیش نمایشی از پیش بینی نشون میده و از ما میخواد تا انتخاب کنیم (شکل ۳):
    • نوع نمودار: خططی یا ستونی باشه
    • تاریخ پایان پیش بینی
  1. بعد از تنظیم این موارد، گزینه Create رو میزنیم.

پیش بینی فروش داده ها تا شش ماهه اول 2020

شکل ۳- پیش بینی فروش داده ها تا شش ماهه اول ۲۰۲۰

با زدن دکمه Create اکسل یک شیت ایجاد میکنه (شکل ۴) که شامل یک جدول با داده های قدیمی و داده های جدید پیش بینی شده و یک نمودار بر اساس این داده ها هست.

شیت ایجاد شده به همراه داده های قدیمی و جدید و نمودار خطی

شکل ۴- شیت ایجاد شده به همراه داده های قدیمی و جدید و نمودار خطی

برای فهمیدن و درک نحوه محاسبه داده های پیش بینی شده میتونیم روی یکی از سلول های مربوط به داده های پیش بینی شده کلیک کنیم و فرمول استفاده شده رو ببینیم (شکل ۵):

مشاهده فرمول استفاده شده در پیش بینی داده ها بصورت نمایی

شکل ۵- مشاهده فرمول استفاده شده در پیش بینی داده ها بصورت نمایی

انجام تنظیمات بیشتر

میتونیم تنظیمات پیش فرض این ابزار رو تغییر بدیم. برای این کار روی گزینه Option کلیک میکنیم و تنظیمات دلخواه رو انجام میدیم:

انجام تنظیمات پیش بینی

شکل ۶- انجام تنظیمات پیش بینی در اکسل

در ادامه به معرفی قسمت های مختلف این تنظیمات می پردازیم:

Forecast Start: تاریخ شروع پیش بینی. هم میشه مستقیم تایپ کرد و هم از Date picker تاریخ رو انتخاب کرد.

  • اگر داده فصلی باشه، بهتره که از آخرین داده پیشین، پیش بینی رو شروع کنیم

Confidence Interval (فاصله اطمینان): محدوده ای که انتظار میره پیش بینی در وان محدوده واقع بشه. در نمودار خطی با دو خط نازک در دو طرف خط پیش بینی نمایش داده شده است. در نمودار ستونی، توسط میله های خطا نمایش داده میشه.

فاصله اطمینان کمک میکنه که دقت پیش بینی رو متوجه بشیم. فاصله پیشفرض میزان ۹۵% هست. این به معنی آن هست که انتظار میره که ۹۵ درصد داده های پیش بینی شده در این محدوده قرار خواهد گرفت.

اگر تیکشو برداریم، فاصله اطمینان در نمودار نمایش داده نمیشه.

Seasonality: طول الگوی فصلی که به طور مرتب تکرار میشه. مثلا در یک الگوی سالیانه که هر داده یک ماه رو نشون میده، عدد Seasonality برابر است با ۱۲.

اکسل خودش بصورت خودکار این عدد رو تشخیص میده اما خودمون هم میتونیم این مقدار رو تغییر بدیم.

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

Include Forecast Statistics: برای دیدن اطلاعات بیشتر راجع به پیش بینی میتونیم این تیک رو بزنیم. جدولی اضافه میشه که ثابت های پیش بینی از قبیل (آلفا، بتا و گاما) و خطا ها مثل (MASE,SMAOE, MAE, RMSE) رو محاسبه میکنه و نمایش میده. همه این مقادیر با تابع Forecast.EST.STAT محاسبه میشن.

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

Values Range: محدوده مقادیر که باید با سری زمانی هم تراز باشه.

Fill missing Points Using: داده هایی که از قلم افتادن و یا حذف شدن در اکسل چطور محاسبه میشن؟ اکسل بصورت پیشفرض از روش درونیابی استفاده میکنه. علاوه بر این روش، میتونیم برای داده های خالی و حذف شده، صفر در نظر بگیریم.

Duplicate Aggregates Using: تعیین میکنه که داده های مختلف با یک تاریخ یکسان چطور باید محاسبه بشن. روش پیشفرض، میانگین هست. اما میتونیم روش های دیگه ای مثل میانه، ماکزیمم یا مینیمم و … رو هم انتخاب کنیم.

فرمول های Exponential Smoothing

ابزار آماده Forecast sheet در اکسل از دو ستون داده تشکیل شده که یکیش سری زمانی و دگری داده های موجود هستن. سه ستون بعدی هم مروط به مقادیر پیش بینی و دو مقدار فاصله اطمینان هست. همونطور که گفتیم این امکان در اکسل ۲۰۱۶ به بعد وجود داره. اما اگه بخوایم در ورژن های قبلی هم با این روش پیش بینی انجام بدیم میتونیم از توابع مربوط به این روش استفاده کنیم که در بالا تابع مربوطه رو دیدیم این توابع عبارتند از: و FORECAST.ETS.CONFINT و FORECAST.ETS.

آرگومان های این توابع عینا همونهایی هستن که در ابزار Forecast sheet توضیح داده شد. با همون منطق میتونیم فرمول نویسی رو انجام بدیم و نتایج پش بینی نمایی رو مشاهده کنیم.

تخمین با استفاده از رگرسیون خطی در اکسل

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

اکسل برای این کار ابزار از قبل آماده مثل Forecast sheet نداره و فقط میشه از توابع برای این کار استفاده کرد. تابع Forecast و تابع Forecast.Linear

هر دو تابع عینا مشابه عمل میکنن و آرگومان ها و خروجی یکسان هم دارند. تنها تفاوت در ورژن نرم افزار هست.

در ورژن ۲۰۱۶ و ۲۰۱۹ هر دو این توابع هستن، ولی پیشنهاد میشه از Forecast.Linear استفاده بشه. ولی در ورژه های قبلی فقط تابع Forecast وجود داره.

این تابع چطور کار میکنه؟

در مثال های قبلی اگر بخوایم از روش خطی استفاده کنیم مطابق شکل ۷ عمل میکنیم:

=FORECAST.LINEAR (A14,$B$2:$B$13,$A$2:$A$13)

پیش بینی با استفاده از روش رگرسیون خطی

شکل ۷- پیش بینی با استفاده از روش رگرسیون خطی

رسم نمودار پیش بینی خطی

برای رسم این نموع نمودار، که در واقع دو نمودار خطی به هم چسبیده هستن، مقاله “نمایش پیش بینی در اکسل” رو مطالعه کنید.

در این مقاله نحوه پیش بینی داده ها رو با دو روش خطی و نمایی مشاهده کردید. جهت تمرین بیشتر میتونید فایل تمرین رو دانلود کنید و موارد آموزش داده شده رو در فایل پیاده کنید.

دانلود فایل اکسل این آموزش

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

133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • 989178710620 ۱۶ مهر ۱۴۰۲ / ۸:۱۵ ب٫ظ

    سلام وقت بخیر:
    ترتیب داده به صورت تاریخ شمسی چطوری هست
    کلا ایا امکانش هست ازش استفاده کنیم

  • مژگان ۲۸ دی ۱۴۰۱ / ۱:۳۷ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۸ بهمن ۱۴۰۱ / ۴:۰۱ ب٫ظ

      درود
      روش پیش بینی رو باید مشخص کنید
      از اونطرف هم با توابع جستجو اول داده های مورد نیاز برای پیش بینی رو استخراج کنید و بعد بذارید توی روش پیش بین مد نظر

      برای استخراج هم بسته به شرایط و چینش داده ها احتمالا میتونید با ndex. offset و … به نتیجه برسید

  • بهرام ۲۷ تیر ۱۳۹۹ / ۳:۰۳ ق٫ظ

    چطور میشه از این روش برای پیش بینی قیمت سهام یا جفت ارزها استفاده کرد؟

  • ?? ۲۲ مهر ۱۳۹۸ / ۱:۵۳ ب٫ظ

    سلام.
    یه سوال داشتم
    با استفاده از اکسل میشه برای پیش بینی بارش ۳ یا ۵ ساله هم کد نویسی انجام داد؟

    • سامان چراغی ۲۲ مهر ۱۳۹۸ / ۸:۲۸ ب٫ظ

      سلام
      اگر فرمول این کار مشخص باشه حتما میشه.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان