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

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

رسم نمودار رگرسیون خطی در اکسل

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

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

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

آموزش رسم نمودار رگرسیون خطی در اکسل با ابزار Trendline

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

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

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

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

نمودار scatter

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

آموزش Add Trend Line در اکسل

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

Format Trendline اکسل

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

Display Equation on chart- قرار گرفتن معادله در کنار نمودار

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

آموزش رسم نمودار رگرسیون خطی در اکسل با ابزار Trendline

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

[cf-restrict]

انواع 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 را فعال کنید.

فعال کردن ضریب تعیین در trendline

قرار گرفتن ضریب تعیین بر روی نمودار

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

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

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

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

تعیین عرض از مبدا در trendline-set interceptتغییر عرض از مبدا

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

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

تغییر دوره ها در مدلforward backward in trend line

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

[/cf-restrict]

 

11 دیدگاه‌ها

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

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

ارسال یک پاسخ

لطفا دیدگاه خود را وارد کنید!
لطفا نام خود را در اینجا وارد کنید