طریقه برازش منحنی (Trend Line) یا رگرسیون در اکسل به همراه توابع کاربردی

برازش منحنی (Trend Line) یا رگرسیون در اکسل به همراه توابع کاربردی [بروز شد]

اکثر کاربران نرم‌افزار اکسل برای یک بار هم که شده با برازش منحنی برای داده‌های x و y یا Trend Line برخورد داشته‌اند. در واقع Trend Line به شما کمک می‌کند تا علاوه بر تشخیص روند تغییر داده‌ها، بتوانید تا حدودی وضعیت داده‌ها را پیش‌بینی (Forecasting) کنید.

از Trend Line فقط می‌توان در منحنی‌های Area ،Bar ،Column ،Line و XY استفاده کرد. به خاطر داشته باشید که نمی‌توانید در نمودارهای ۳D ،Radar ،Pie ،Doughnut و Bubble از Trend Line استفاده کنید.

برای اضافه‌ کردن Trend Line، مطابق شکل زیر، پس از راست کلیک کردن روی منحنی داده‌ها، گزینه‌ی Add Trendline را انتخاب کنید تا پنجره Format Trendline باز شود.

در پنجره Format Trendline (مطابق شکل زیر)،

در قسمت راست پنجره Format Trendline یعنی قسمت Trendline Options، بخش‌های زیر وجود دارد:

بخش اول: بخش Trend/Regression Type انواع Trendline‌ها را نشان می‌دهد که به شرح زیر است:

– Exponential / نمایی؛ با فرمول Y=C.e^bx که b و c اعداد ثابت هستند.

* نکته: هنگامی که داده‌های y شامل اعداد منفی یا صفر باشند، قابل استفاده نیست!

– Linear / خطی؛ با فرمول Y=m.x+b که m شیب خط و b عدد ثابت (عرض از مبدا) است.

– Logarithmic / لگاریتمی؛ با فرمول Y=c.Lnx+b که c و b اعداد ثابت هستند.

* نکته: هنگامی که داده‌های x شامل اعداد منفی یا صفر باشند، خطا ظاهر می‌شود!

– Polynomial / چند جمله‌ای؛ با فرمول Y=b+c₁x+c₂x²+c₃x³+…+c_nxⁿ که در آن c عدد ثابت است.

– Power / توانی؛ با فرمول Y=C.x^b که b و c اعداد ثابت هستند.

* نکته: هنگامی که داده‌های y شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

– Moving Average / میانگین متحرک؛ با فرمول F_t=(A_t+A_t-1+…+A_t-n+1)/n

بخش دوم، بخش TrendLine Name می‌باشد.

بخش سوم، بخش Forecast یا پیش‌بینی می‌باشد که بر اساس نوع معادلات انتخابی در بخش اول، yهای قبل و یا بعد متناظر با xهای داده شده را پیش بینی می‌کند.

Set Intercept هم برای تعیین عرض از مبداء دلخواه می‌باشد.

با تیک زدن دو گزینه آخر یعنی Display Equation on chart و Display R-squared value on chart، به ترتیب معادله و ضریب رگرسیون (R²) متناظر با نوع Trendline انتخاب شده، روی نمودار نمایش داده می‌شود. در رگرسیون خطی ضریب رگرسیون مجذور ضریب همبستگی (R) است.

* نکته: چنانچه می‌خواهید از معادله‌ی پیشنهادی اکسل جهت درون‌یابی یا برون‌یابی استفاده کنید باید به دو نکته زیر توجه کنید:

۱- معادله‌ای مناسب است که ضریب رگرسیون آن نزدیک به یک باشد مثلا ۰٫۹۹٫

۲- اکسل بصورت پیش فرض، ضرایب معادله‌ را تا ۲ رقم اعشار نمایش می‌دهد. برای اینکه بتوانید با استفاده از معادله، y متناظر با یک x را محاسبه کنید برای دقت بیشتر باید از معادله‌ای استفاده کنید که تعداد ارقام اعشاری بیشتری داشته باشد. برای این کار مطابق شکل زیر روی معادله خط، راست کلیک کنید و گزینه Format trendline label را انتخاب کنید.

در پنجره باز شده زیر در قسمت Category گزینه Number را انتخاب و در قسمت Decimal places تعداد ارقام بعد از ممیز را افزایش دهید. دکمه Close را بزنید و از معادله جدید استفاده کنید.

علاوه بر استفاده گرافیکی از ابزار Trend Line، می‌توان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار به دست آورد.
۱- تابع Slope: محاسبه شیب رگرسیون خطی.

=SLOPE(Known Y values, Known X values)

برای مثال زیر شیب خط تقریبا ۲٫۱۵ می‌باشد.

=SLOPE(B2:B6,A2:A6) = ۲٫۱۵

۲- تابع Intercept: محاسبه عرض از مبدا رگرسیون خطی. که برای مثال بالا تقریبا ۰٫۴۷- می باشد.

=INTERCEPT(Known Y values, Known X values)

=INTERCEPT(B2:B6,A2:A6) = -0.47

یعنی در واقع معادله رگرسیون خطی این مثال برابر است با: y = 2.15*x -0.47

۳- تابع Forecast: برای پیش‌بینی y متناظر با یک x جدید بر مبنای رگرسیون خطی.

=FORECAST(New X Value, Known Y values, Known X values)
=FORECAST(15,B2:B6,A2:A6) = ۳۱٫۷۷۸

۴- تابع GROWTH: برای پیش بینی y متناظر با یک x جدید بر مبنای رگرسیون نمائی.

=GROWTH(Known Y Values, Known X Values, New X Values, Const)
=GROWTH(B2:B6,A2:A6,15,TRUE) = ۴۸٫۶۸

عبارت Const در تابع GROWTH، دارای دو حالت True (محاسبه b) و False (مقدار ۱ برای b) می‌باشد.

نسخه کامل‌تر معرفی توابع را می‌توانید در این مطلب مشاهده کنید که در آن توابع کاربردی دیگری نظیر LINEST ،Trend، RSQ و CORREL و … ارائه شده است.