Trendline

خط روند

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

در ادامه این ویدئو، با آموزش اکسل همراه باشید تا علاوه بر جزئیات Trend Line، با توابع کاربردی اکسل برای برازش منحنی نیز آشنا گردید.

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

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

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

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

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

  • Exponential / نمایی؛ با فرمول Y=C.ebx که b و c اعداد ثابت هستند.
  • Linear / خطی؛ با فرمول Y=m.x+b که m شیب خط و b عدد ثابت (عرض از مبدا) است.
  • Logarithmic / لگاریتمی؛ با فرمول Y=c.Lnx+b که c و b اعداد ثابت هستند.
  • Polynomial / چند جمله‌ای؛ با فرمول Y=b+c1x+c2x2+c3x3+…+cnxn که در آن c عدد ثابت است.
  • Power / توانی؛ با فرمول Y=C.xb که b و c اعداد ثابت هستند.
  • Moving Average / میانگین متحرک؛ با فرمول Ft=(At+At-1+…+At-n+1)/n

نکات مهم:

  • زمانی که داده های y شامل اعداد منفی یا صفر باشند، قابل استفاده نیست!
  • زمانی که داده های x شامل اعداد منفی یا صفر باشند، خطا ظاهر می گردد!
  • زمانی که داده های y شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

بخش دوم: بخش TrendLine Name می باشد.

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

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

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

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

۱- معادله ای مناسب است که ضریب رگرسیون آن نزدیک به یک باشد مثلا 0.99.

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

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

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