رسم نمودار معادله خطی رگرسیونی با Trendline اکسل

رسم نمودار معادله خطی رگرسیونی با Trendline اکسل

ابزار Trendline اکسل معادله خط ، نمودار داده ها و نمودار خط را برای ما رسم می کند.

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

از شما می خواهند معادله رگرسیون خطی آن را ارائه دهید و هم چنین نمودار آن را رسم کنید.

شاید در ابتدا نیاز باشد که مطلب “رگرسیون چیست” را مطالعه کنید و سپس به کمک دو تابع SLOPE و INTERCEPT شیب و عرض از مبدا آن را به دست آورده و بر مبنای آن نمودار مورد نظر را رسم کنید.

اما اکسل به کمک ابزار Trend Line در نمودارها، این امکان را به ما می دهد که نیاز به هیچ کدام از موارد گفته شده نداشته باشیم.یعنی هم معادله خط را به ما می دهد و هم نمودار داده ها و نمودار خط را برای ما رسم می کند.یعنی تصویر زیر:

Trend Line (مسیر رشد یا روند خط) روند نموداری از داده های شما را برایتان ترسیم می کند.به کمک این ابزار روند داده ها را بررسی می کنید و همچنین آینده را به کمک آن پیش بینی خواهید کرد.

برای این کار ابتدا داده های خود را انتخاب کنید.و بر روی مربع کوچکی که در کنار جدول ایجاد می شود،کلیک کنید.نام این مربع ابزار Quick Analysis می باشد.سپس از منوی باز شده بر روی Chart و سپس بر روی نمودار Scatter  کلیک کنید.

اکنون باید نمودار زیر را مشاهده نمایید.

بر روی خود نمودار(خط آبی رنگ) راست کلیک کنید و گزینه Add Trend Line را انتخاب کنید.

اکنون باید نمودار معادله خط را ببینید و علاوه بر آن Format Trendline برای شما ظاهر خواهد شد.

دز این صفحه گزینه Linear را انتخاب کنید. و علامت گزینه Display Equation on Chart را فعال کنید تا معادله نمودار برای شما نمایش داده شود.

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

تا الان شما نمودار(بر حسب X و Y) و معادله نمودار و نمودار معادله رگرسیونی را به دست آوردید. اما در ادامه با ما همراه باشید تا با تنظیمات این بخش آشنا شوید و اینکه چرا گزینه Linear را انتخاب کردیم؟

انواع Trendline:

Linear(خطی):

با فرمول Y=m.x+b  محاسبه می شود.كه m شيب خط و b عدد ثابت (عرض از مبدا) می باشد.

Logarithmic(لگاريتمی):

با فرمول Y=c.Lnx+b محاسبه می شود كه c و b اعداد ثابت  و LN لگاریتم طبیعی می باشد

Polynomial(قطبی):

با فرمول Y=b+c1x+c2x2+c3x3+…+cnxnمحاسبه می شود كه در آن c عدد ثابت است.

Powre(توانی):

با فرمول Y=C.xb محاسبه می شود كه b و c اعداد ثابت هستند.
نكته: هنگامی كه داده‌ها شامل اعداد منفی يا صفر باشند قابل استفاده نيست!

Emponential(توصيفی):

با فرمول Y=C.ebx كه b و c اعداد ثابت و  e(همان 2.71) مبنای لگاريتم نپر می باشد.
نكته: هنگامی كه داده‌ها شامل اعداد منفی يا صفر باشند قابل استفاده نيست!

Moving Average(ميانگين متحرك):

با فرمول Ft=(At+At-1+…+At-n+1)/n محاسبه می شود.
نكته: تعداد نقاط در اين سير روند برابر است با تعداد داده‌ها منهای نقطه‌ای كه معين می‌كنيد.

R-squared value یا R^2 چیست؟

R^2 را ضریب تعیین می گویند.ضریب تعیین شاخصی برای خوبی برازش می باشد.در مقاله ای جداگانه این مبحث را توضیح خواهیم داد ولی این را فعلا بدانید که این شاخص تعیین می کند که تا چه حد خط، به نقاط مشاهده شده نزدیک می باشد یا به عبارتی تا چه حد خط رگرسیون به داده ها برازش شده است.این شاخص عددی بین 0 و 1 به ما می دهد و هرچه این شاخص به 1 نزدیکتر باشد شما مدل خوبی برازش کرده اید.

چگونه ضریب R^2 را بر روی نمودار نمایش دهیم؟

کافیست گزینه Display R-squared value on chart را فعال کنید.

چرا گزینه Linear را انتخاب کردیم؟

انتخاب نوع این روند (trendline) بستگی به نوع داده های شما و ضریب R2 دارد.زمانی از گزینه Linear استفاده می کنیم، که الگوی داده های ما شبیه به یک خط عمل می کنند.خط روند Linear معمولا برای داده هایی به کار می رود که با یک نرخ ثابت،کاهش یا افزایش می یابد.

چطور عرض از مبدا را تغییر دهیم؟

از طریق گزینه Set Intercept این کار را انجام می دهیم.کافیست آن را فعال کنید و عدد دلخواه خود را وارد کنید.اگر به تصویر قبلی دقت کنید،عرض از مبدا ما برابر با 3.9893  می باشد که با فعال کردن این گزینه در ابتدا روی 0 قرار می گیرد.

چگونه دوره های آینده و دوره های قبلی را بر روی نمودار مدل اعمال کنیم؟

از طریق دو گزینه Forward و Backward این کار را انجام می دهیم.

چگونه مقدار دوره های قبلی و بعدی (نه بر روی نمودار) را بدست آوریم؟ تابع forecast