برازش منحنی (Trend Line) یا رگرسیون در اکسل به همراه توابع کاربردی
اکثر کاربران نرم‌افزار Excel برای یک بار هم که شده با برازش منحنی برای داده‌های x و y یا Trend Line برخورد داشته‌اند. در واقع Trend Line به شما کمک می‌کند تا علاوه بر تشخیص روند تغییر داده‌ها، بتوانید تا حدودی وضعیت داده‌ها را پیش‌بینی (Forecasting) کنید.
با ادامه مطلب همراه شوید تا علاوه بر جزئیات Trend Line، با توابع کاربردی اکسل برای برازش منحنی نیز آشنا گردید.
با ادامه مطلب همراه شوید تا علاوه بر جزئیات Trend Line، با توابع کاربردی اکسل برای برازش منحنی نیز آشنا گردید.
از Trend Line در منحنی‌های Area – Bar – Column – Line و XY استفاده میشود. برای اضافه‌کردن Trend Line به نمودار مورد نظرتان پس از راست کلیک کردن روی منحنی دادها، مطابق شکل زیر، گزینه‌ی Add Trendline را انتخاب کنید. اما به خاطر داشته باشید که در نمودارهای ۳d – Radar – Pie – Doughnut و Buuble نمی‌توانید از Trend Line استفاده کنید.
پس از باز شدن پنجره Format Trendline (مطابق شکل زیر)، قسمت راست (Trendline Options)، بخش اول Trend/Regression Type انواع Trendline را نشان میدهد (در ادامه توضیح داده میشود). بخش دوم TrendLine Name می‌باشد. بخش سوم Forecast می‌باشد که بر اساس نوع معادلات انتخابی در بخش اول، yهای قبل و یا بعد متناظر با xهای داده شده را پیش بینی می‌کند. Set Intercept هم برای تعیین عرض از مبداء دلخواه می‌باشد. دو قسمت آخر، معادله و R۲ (ضریب رگرسیون) متناظر با نوع Trendline انتخاب شده، را روی نمودار نمایش می‌دهند.
انواع Trend Line:
- Exponential / نمایی؛ با فرمول Y=C.ebx که b و c اعداد ثابت هستند.
– نکته: هنگامی که داده‌ها شامل اعداد منفی یا صفر باشند قابل استفاده نیست!
-
Linear / خطی؛ با فرمول Y=m.x+b که m شیب خط و b عدد ثابت (عرض از مبدا) است.
- Logarithmic / لگاریتمی؛ با فرمول Y=c.Lnx+b که c و b اعداد ثابت هستند.
- Polynomial / چند جمله‌ای؛ با فرمول Y=b+c۱x+c۲x۲+c۳x۳+…+cnxn که در آن c عدد ثابت است.
- Power / توانی؛ با فرمول Y=C.xb که b و c اعداد ثابت هستند.
– نکته: هنگامی که داده‌ها شامل اعداد منفی یا صفر باشند قابل استفاده نیست!
- Moving Average / میانگین متحرک؛ با فرمول Ft=(At+At-1+…+At-n+1)/n
علاوه بر استفاده گرافیکی از ابزار Trend Line، میتوان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار بدست آورد.
۱- تابع Slope: محاسبه شیب رگرسیون خطی. که برای مثال زیر تقریبا ۲٫۱۵ می باشد.
=SLOPE(Known Y values, Known X values)
۲- تابع Intercept: محاسبه عرض از مبدا رگرسیون خطی. که برای مثال بالا تقریبا ۰٫۴۷- می باشد.
=INTERCEPT(Known Y values, Known X values)
یعنی در واقع معادله رگرسیون خطی این مثال برابر است با:
y = 2.15*x -0.47
۳- تابع Forrecast: برای پیش بینی y متناظر با یک x جدید بر مبنای رگرسیون خطی.
= FORECAST (New X Value, Known Y values, Known X values)
= FORECAST(۱۵,B2:B6,A2:A6) = ۳۱.۷۷۸
= FORECAST(۱۵,B2:B6,A2:A6) = ۳۱.۷۷۸
۴- تابع GROWTH: برای پیش بینی y متناظر با یک x جدید بر مبنای رگرسیون نمائی.
= GROWTH (Known Y Values, Known X Values, New X Values, Const)
=GROWTH (B2:B6,A2:A6,۱۵,TRUE) = ۴۸.۶۸
=GROWTH (B2:B6,A2:A6,۱۵,TRUE) = ۴۸.۶۸
عبارت Const در تابع GROWTH، دارای دو حالت True (محاسبه b) و False (مقدار ۱ برای b) می باشد.
منبع: http://tarfandha.blog.ir
سید اسعد حسینی
علیرضا کربلائی
نادیا بابائی
عباسعلی داداشی رودباری
با سلام و سپاس از مطالب خوبتان
در مورد مطلبی که ذکر کردید یک سوال داشتم.
آیا این خطی که به کمک Linearرسم میکنیم میتواند روند را به ما نشان دهد؟
وقتی بر روی Trend Line کلیک میکنیم دو گزینه در قسمت انتهایی آن وجود دارد
۱- display equation on chart
۲- display r- squared value on chart
با کلیک برروی گزینه اول یک فرمول و با کلیک بر روی گزینه دوم r2 را به ما میدهد.
آیا این همان فرمول رگرسیون است؟ و از روی آن میتوان روند مثبت یا منفی بطور مثال یک پارامتر اقلیمی را مورد بررسی قرار داد؟
با تشکر
با سلام
بله روند رو بهتون نشان می دهد و به همان معنی روند خطی (Trend Line) است.گزینه هایی که فرمودین بله همان فرمول رگرسیون و ضریب تعیین رو به شما نشان می دهد مثبت یا منفی بودن رو میتونید با مثبت و منفی بودن شیب خط (m) و مقدار آن رو نیز بیان کنید.
موفق باشید
سپاس از پاسختان
اگر گزینه اول فرمول رگرسیون هست کدام یک بعنوان شیب خط در نظر گرفته میشود؟ بطور مثال در یک تحلیل این فرمول را اکسل محاسبه کرده
y = 0.0271x + 12.473
کدامیک شیب خط است؟
در spss بعد از محاسبه رگرسیون از کدام جدول میتوان شیب خط و روند مثبت یامنفی بودن را یافت؟
با تشکر
با سلام
در این رابطه ۰٫۰۲۷۱ شیب خط هست یا همان رابطه ی y=mx+b که m شیب خط است در spss نیز از جدول ضرایب یا همان Coefficients است که در اونجا Constant ضریب ثابت یا عرض از مبداء خواهد بود عدد پایین تر از اون ضریب رگرسیون یا شیب خط خواهد بود یعنی رابطه ی y=b+b1x که b ضریب ثابت b1 شیب خط یا ضریب رگرسیون خواهد بود.
موفق باشید
با سلام
در خصوص رابطه بین رگراسیون و معادله خط در زمانی که به طور مثال y مشخص باشد با توجه به رگراسیون ۰٫۹۸ چطور x محاسبه میشود. به فرض مثال من عدد مربوط به Y را درمعادله خط جایگذاری می کنم اما مقدار x با توجه به معادله درست به دست نمی آید.
با تشکر
با سلام
احتمالاً منظورتون از ۰٫۹۸ ضریب تعیین باشد.با فرض داشتن y این را در نظر بگیرید که y تنها تابعی از تغییرات X نیست که به راحتی محاسبه شود و x یک متغیر مستقل است و نیاز به ضرایب a و b نیز خواهد بود در صورت پیدا کردن راه حل جدید ماهم استقبال می کنیم
با سپاس
با سلام و آرزوی قبولی طاعات و عبادادتون در این ماه
من چگونه می توان داده های یک ستون رو روی ستن دیگه رگرس کنم به عبارتی چگونه می توان تاثیر یک متغیر را رو یمتغیر دیگه با نمودار نشون داد؟
با سلام
از نمودار پراکنش استفاده کنید در یک محور یکی از متغیرها و در محور دوم نیز متغیر دیگر رو قرار دهید و در پایان خط رگرسیون را ترسیم کنید یا از نمودارهای خطی استفاده کنید و هر متغییر را بصورت یک سری معرفی نمایید.
موفق باشید
سلام
نمی دونم چرا وقتی روی نمودار کلیک می کنم و به قسمت add chart element میرم گزینه ی trend line غیر فعاله
لطفا راهنمائی کنید
با سلام
بر روی منحنی ترسیم شده کلیک راست کنید و گزینه Add trend line را انتخاب کنید.
موفق باشید
نویسنده: سید اسعد حسینی
دکتری اقلیم شناسی از دانشگاه محقق اردبیلی