تابع FORECAST اکسل-پیش بینی با رگرسیون خطی

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

رگرسیون چیست؟

محاسبه شیب خط

محاسبه عرض از مبدا

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

جدول زیر را در نظر بگیرد.این جدول نشان می دهد در ماه اول فروشنده 20 بسته زعفران را به فروش رسانده است.در ماه دوم  32 بسته زعفران را به فروش رسانده است و…

آموزش تابع FORECASE اکسل-پیش بینی رگرسیون خطی

همان طور که در جدول مشاهده می کنید، شما تا ماه 10 ام، می دانید که چند بسته زعفران فروخته اید، در واقع مقدار واقعی فروش را می دانید.اما اکنون از شما می خواهند با روش رگرسیون خطی،پیش بینی فروش را در ماه های 11ام تا 15ام، انجام دهید.اگر بخواهید معادله این پیش بینی را به دست آورید باید عرض از مبدا و شیب خط را محاسبه کنید.اما ما در اینجا به معادله خط احتیاجی نداریم، بلکه فقط می خواهیم عدد پیش بینی شده را در ماه های آینده به ما بدهد.برنامه اکسل برای محاسبه مقادیر پیش بینی آینده به روش رگرسیون خطی، تابع FORECAST را به ما پیشنهاد می دهد.

ساختار تابع FORECAST:

=FORECAST(x, known_y’s, known_x’s)

=FORECAST(متغییر وابسته, متغییر مستقل, مقادیر(دوره) معلوم که قصد پیش بینی داریم )

[cf-restrict]

x:

مجموعه مقادیری (دوره هایی) که قصد پیش بینی آن را داریم و یا به عبارتی بازه زمانی که در اختیار داریم ولی مقدار پیش بینی در آن بازه را نمی دانیم.با توجه به مثال، ما قصد داریم دوره 11 را پیش بینی کنیم.پس در این قسمت سلول A12 را تایپ می کنیم.

known_y’s:

در این قسمت، متغییر مستقل در دوره های قبل را وارد می کنیم.با توجه به مثال، متغییر مستقل ما “فروش ماهانه” می باشد.مقادیری که از فروش ماهانه داریم در 10 دوره اول می باشد.پس آدرس سلول این دوره ها را وارد می کنیم.یعنی B2:B11 را وارد این قسمت از تابع FORECAST می کنیم.

known_x’s:

در این قسمت، متغییر وابسته در دوره های قبل را وارد می کنیم.با توجه به مثال، متغییر وابسته ما “دوره زمانی(بر حسب ماه)” می باشد.مقادیری که از دوره زمانی داریم در 10 ماه اول می باشد.پس آدرس سلول این ماه ها را وارد می کنیم.یعنی A2:A11 را وارد این قسمت از تابع FORECAST می کنیم.

خلاصه مطالب گفته شده در فرمول زیر بیان می شود:

=FORECAST(A12,B2:B11,A2:A11)

عدد حاصل از این فرمول برابر خواهد بود با 103.53 به عبارتی دیگر تابع FORECAST پیش بینی می کند در ماه 12ام فروشنده 103.53 بسته به فروش برساند.

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

=FORECAST($A12,$B$2:$B$11,$A$2:$A$11)

نتیجه این فرمول را در تصویر زیر مشاهده می کنید.

پیش بینی خطی با تابع FORECAST

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

ویژگی های تابع FORECAST:

1-برازش این تابع رابطه  y=bx+a می باشد (b شیب خط و a عرض از مبدا آن می باشد)  به گونه ای که:

a=y ̅-bx ̅

b=(∑(x-x ̅ )-(y-y ̅))/〖∑(x-x ̅ )〗^2

(x ̅ و y ̅   به ترتیب میانگین مقادیر x  و y می باشند)

2-این تابع پیش بینی ها را بر اساس مقادیر گذشته انجام می دهد.

خطاهای تابع FORECAST:
#N/A
زمانی که خطایی در رابطه با مقادیر موجود y و x( known_y’s ویا ( known_x’s وجود داشته باشد به عنوان مثال اگرهر کدام از این آرایه ها خالی باشند و یا طول آنها یکسان نباشد
#DIV/0!
آگر واریانس محدوده x های موجود(known_x’s) مساوی با صفر باشد
#VALUE!
اگر مقادیر آینده X غیر عددی باشند

[/cf-restrict]

 

3 دیدگاه برای “تابع FORECAST اکسل-پیش بینی با رگرسیون خطی

  1. بهنام گفته:

    سلام. شما اشتباها مقدار y را به عنوان متغیر مستقل معرفی کردید و همچنین برای x. در واقع برعکس گفتید چرا که y متغیر وابسته است

  2. شاکری گفته:

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

دیدگاهتان را بنویسید

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