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

از Trend Line در منحنی‌های Area – Bar – Column – Line و XY استفاده می‌شود. برای اضافه‌کردن Trend Line به نمودار مورد نظرتان پس از راست کلیک کردن روی منحنی دادها، مطابق شکل زیر، گزینه‌ی Add Trendline را انتخاب کنید. اما به خاطر داشته باشید که در نمودارهای ۳d – Radar – Pie – Doughnut و Buuble نمی‌توانید از Trend Line استفاده کنید.

^[1]

پس از باز شدن پنجره Format Trendline (مطابق شکل زیر)، قسمت راست (Trendline Options)، بخش اول Trend/Regression Type انواع Trendline را نشان می‌دهد (در ادامه توضیح داده می‌شود). بخش دوم TrendLine Name می‌باشد. بخش سوم Forecast می‌باشد که بر اساس نوع معادلات انتخابی در بخش اول، yهای قبل و یا بعد متناظر با xهای داده شده را پیش بینی می‌کند. Set Intercept هم برای تعیین عرض از مبداء دلخواه می‌باشد. دو قسمت آخر، معادله و R^۲ (ضریب رگرسیون) متناظر با نوع Trendline انتخاب شده، را روی نمودار نمایش می‌دهند.

^[2]

انواع Trend Line:

• Exponential / نمایی؛ با فرمول Y=C.e^bx که 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^۳+…+c_nxⁿ که در آن c عدد ثابت است.

• Power / توانی؛ با فرمول Y=C.x^b که b و c اعداد ثابت هستند.
  – نکته: هنگامی که داده‌ها شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

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

^[3]

علاوه بر استفاده گرافیکی از ابزار Trend Line، می‌توان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار بدست آورد.

۱- تابع Slope: محاسبه شیب رگرسیون خطی. که برای مثال زیر تقریبا ۲٫۱۵ می باشد.

^[4]

۲- تابع 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) = ۳۱.۷۷۸

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

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

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

منبع: http://tarfandha.blog.ir