رسم نمودار معادله خطی رگرسیونی با 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]

 

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

  1. الهام گفته:

    سلام وقت بخیر
    چطور می تونم شیب نقاط مختلف رو با هم مقایسه کنم
    فرض کنید اعداد افقی ماه ها باشن
    و من بیام مهر تا بهمن و خرداد تا مرداد رو محاسبه کنم

  2. زهره گفته:

    بسيار بسيار عالي بود. ممنون. خدا قوت.
    فقط اينكه polynomial به معني چند جمله اي است كه اينجا قطبي چاپ شده لطفا تصحيح بفرماييد

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

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