انواع ، معادلات و فرمول های Excel Trendline

توسط Svetlana Cheusheva ، به روز شده در 27 سپتامبر 2022

در این آموزش ، توضیحات مفصلی از تمام گزینه های خطوط موجود در اکسل و زمان استفاده از آنها را پیدا خواهید کرد. همچنین یاد خواهید گرفت که چگونه یک معادله خط را در یک نمودار نمایش دهید و شیب خط روند را پیدا کنید.

اضافه کردن یک روند در اکسل بسیار آسان است. تنها چالش واقعی انتخاب نوع Trendline است که به بهترین وجه با نوع داده های مورد نظر شما مطابقت دارد. در این آموزش ، توضیحات مفصلی از تمام گزینه های خطوط موجود در اکسل و زمان استفاده از آنها را پیدا خواهید کرد. اگر به دنبال نحوه وارد کردن یک خط روند در نمودار اکسل هستید ، لطفاً آموزش مرتبط با فوق را بررسی کنید.

انواع Trendline Excel

هنگام افزودن یک خط روند در اکسل ، 6 گزینه مختلف برای انتخاب دارید. علاوه بر این ، Microsoft Excel اجازه می دهد تا یک معادله روند و مقدار R-Squared را در یک نمودار نمایش دهد:

• معادله Trendline فرمولی است که خطی پیدا می کند که به بهترین وجه در نقاط داده باشد.
• ارزش مربع R-Scenture قابلیت اطمینان خط روند - R2 نزدیکتر به 1 است ، هر چه ترند خط بهتر باشد.

   

در زیر ، توضیحات مختصری از هر نوع روند با مثال نمودار پیدا خواهید کرد.

خط خطی

خط روند خطی بهتر است وقتی که نقاط داده در یک نمودار شبیه به یک خط مستقیم هستند ، با مجموعه داده های خطی استفاده شود. به طور معمول ، یک روند خطی یک افزایش مداوم یا سقوط را با گذشت زمان توصیف می کند.

به عنوان مثال ، روند خطی زیر افزایش مداوم فروش در طی 6 ماه را نشان می دهد. و مقدار R 2 از 0. 9855 نشان دهنده تناسب بسیار خوبی از مقادیر تخمین زده شده برای داده های واقعی است.

خط روند نمایی

روند نمایی یک خط خمیده است که نشان دهنده افزایش یا سقوط مقادیر داده با سرعت فزاینده است ، بنابراین خط معمولاً در یک طرف خمیده تر است. این نوع روند اغلب در علوم استفاده می شود ، به عنوان مثال برای تجسم رشد جمعیت انسانی یا کاهش جمعیت حیات وحش.

لطفاً توجه داشته باشید که یک روند نمایی برای داده هایی که حاوی صفر یا مقادیر منفی است ، ایجاد نمی شود.

نمونه خوبی از منحنی نمایی ، پوسیدگی در کل جمعیت ببر وحشی روی زمین است.

خط روند لگاریتمی

خط لگاریتمی بهترین خط به طور کلی برای ترسیم داده هایی که به سرعت افزایش می یابد یا کاهش می یابد و سپس سطح خاموش می شود ، استفاده می شود. این می تواند شامل مقادیر مثبت و منفی باشد.

نمونه ای از یک روند لگاریتمی ممکن است نرخ تورم باشد ، که ابتدا بالاتر می رود اما پس از مدتی تثبیت می شود.

رویه چند جمله ای

خط روند منحنی چند جمله ای برای مجموعه داده های بزرگ با مقادیر نوسان کننده که بیش از یک افزایش و سقوط دارند ، به خوبی کار می کند.

به طور کلی ، یک چند جمله ای با درجه بزرگترین نماینده طبقه بندی می شود. درجه روند چند جمله ای را نیز می توان با تعداد خم های روی یک نمودار نیز تعیین کرد. به طور معمول ، یک روند چند جمله ای درجه دوم دارای یک خم (تپه یا دره) ، یک چند جمله ای مکعب 1 یا 2 خم است و یک چند جمله ای کوارتیک تا 3 خم دارد.

هنگام اضافه کردن یک روند چند جمله ای در نمودار اکسل ، شما با تایپ شماره مربوطه در کادر سفارش در صفحه فرمت فرمت ، که به طور پیش فرض 2 است ، درجه را مشخص می کنید:

به عنوان مثال ، روند چند جمله ای درجه دوم در نمودار زیر مشهود است که رابطه بین سود و تعداد سالهایی که محصول در بازار بوده است نشان می دهد: در ابتدا افزایش می یابد ، اوج در وسط و سقوط نزدیک به پایان.

خط روند برق

خط روند قدرت بسیار شبیه به منحنی نمایی است ، فقط یک قوس متقارن تر دارد. معمولاً برای ترسیم اندازه گیری هایی که با سرعت مشخص افزایش می یابد ، استفاده می شود.

یک روند برق را نمی توان به نمودار اکسل اضافه کرد که حاوی مقادیر صفر یا منفی است.

به عنوان نمونه ، بیایید یک روند قدرت را برای تجسم میزان واکنش شیمیایی ترسیم کنیم. توجه داشته باشید که مقدار مربع R از 0. 9918 ، به این معنی است که روند روند ما تقریباً کاملاً متناسب با داده ها است.

حرکت متوسط روند

هنگامی که نقاط داده در نمودار شما دارای فراز و نشیب های زیادی هستند ، یک روند متوسط حرکت می تواند نوسانات شدید در مقادیر داده را صاف کند تا یک الگوی واضح تر نشان دهد. برای این کار ، اکسل میانگین متحرک تعداد دوره هایی را که مشخص می کنید (2 به طور پیش فرض) محاسبه می کند و آن مقادیر متوسط را به عنوان نقاط در خط قرار می دهد. هرچه مقدار دوره بالاتر باشد ، خط نرم تر است.

یک مثال عملی خوب استفاده از خط متوسط در حال حرکت برای آشکار کردن نوسانات در قیمت سهام است که در غیر این صورت مشاهده آن دشوار خواهد بود.

معادلات و فرمول های Excel Trendline

در این بخش معادله هایی که اکسل برای انواع مختلف روند استفاده می کند ، شرح می دهد. لازم نیست این فرمول ها را به صورت دستی بسازید ، به سادگی به اکسل بگویید تا معادله Trendline را در یک نمودار نمایش دهد.

همچنین ، ما در مورد فرمول برای یافتن شیب یک روند و سایر ضرایب بحث خواهیم کرد. فرمول ها فرض می کنند که شما 2 مجموعه متغیر دارید: متغیر مستقل X و متغیر وابسته y. در برگه های خود ، می توانید از این فرمول ها استفاده کنید تا مقادیر y پیش بینی شده را برای هر مقادیر معین x بدست آورید.

برای قوام ، ما از همان مجموعه داده ها با مقادیر کمی متفاوت برای همه مثالها استفاده خواهیم کرد. با این حال ، لطفاً به خاطر داشته باشید که این فقط برای اهداف تظاهرات است. در برگه های واقعی خود ، باید نوع Trendline را مطابق با نوع داده خود انتخاب کنید.

یادداشت مهم! فرمول های Trendline فقط باید با نمودارهای پراکندگی XY استفاده شوند زیرا فقط این نمودار محورهای X و Y را به عنوان مقادیر عددی ترسیم می کند. برای اطلاعات بیشتر ، لطفاً ببینید که چرا معادله Trendline Excel ممکن است اشتباه باشد.

معادله خطی خطی و فرمول ها

معادله خطی خطی از کمترین روش مربع برای جستجوی شیب و ضرایب رهگیری استفاده می کند به گونه ای:

• B شیب یک روند است.
• A-Intercept y است ، که مقدار مورد انتظار y است که تمام متغیرهای X برابر با 0 باشد. در یک نمودار ، نقطه ای است که خط روند از محور Y عبور می کند.

برای رگرسیون خطی ، Microsoft Excel عملکردهای ویژه ای را برای بدست آوردن ضرایب شیب و رهگیری فراهم می کند.

شیب روند B: = شیب (y ، x)

y-intence a: = رهگیری (y ، x)

با فرض محدوده X B2: B13 و دامنه Y C2: C13 است ، فرمول های زندگی واقعی به شرح زیر است:

با استفاده از عملکرد Linest به عنوان یک فرمول آرایه می توان نتایج مشابهی را بدست آورد. برای این کار ، 2 سلول مجاور را در همان ردیف انتخاب کنید ، فرمول را وارد کرده و Ctrl + Shift + را وارد کنید تا آن را تکمیل کنید:

همانطور که در تصویر زیر نشان داده شده است ، شیب و ضرایب رهگیری برگشتی توسط فرمول ها کاملاً مطابق با ضرایب موجود در معادله خطی خطی است که در نمودار نمایش داده می شود ، فقط دومی به 4 مکان اعشاری گرد می شوند.

معادله و فرمول های روند نمایی

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

در جایی که A و B ضرایب محاسبه شده و E ثابت ریاضی E (پایه لگاریتم طبیعی) است.

ضرایب را می توان با استفاده از این فرمول های عمومی محاسبه کرد:

پاسخ: = exp (شاخص (linest (ln (y) ، x) ، 1 ، 2))

b: = index (linest (ln (y) ، x) ، 1)

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

a: = exp (index (linest (ln (c2: c13) ، b2: b13) ، 1 ، 2)

b: = index (linest (ln (c2: c13) ، b2: b13) ، 1)

معادله و فرمولها لگاریتمی

در اینجا معادله Logarithmic Trendline در اکسل آورده شده است:

جایی که A و B ثابت هستند و LN عملکرد لگاریتم طبیعی است.

برای به دست آوردن ثابت ، از این فرمول های عمومی استفاده کنید ، که فقط در آخرین استدلال متفاوت است:

پاسخ: = فهرست (Linest (y ، ln (x)) ، 1)

b: = index (linest (y ، ln (x)) ، 1 ، 2)

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

پاسخ: = شاخص (Linest (C2: C13 ، LN (B2: B13)) ، 1)

B: = شاخص (Linest (C2: C13 ، LN (B2: B13)) ، 1 ، 2)

معادله و فرمول های چند جمله ای

برای کار کردن از خط چند جمله ای ، اکسل از این معادله استفاده می کند:

بسته به میزان روند چند جمله ای خود ، از یکی از مجموعه های زیر از فرمول ها برای به دست آوردن ثابت استفاده کنید.

خط چند جمله ای درجه دوم (مرتبه دوم)

معادله: y = b₂x 2 + b₁x + a

پاسخ: = فهرست (Linest (y ، x^) ، 1 ، 3)

مکعب (مرتبه 3) روند چند جمله ای

پاسخ: = فهرست (Linest (y ، x^) ، 1 ، 4)

فرمول های مربوط به خطوط چند جملهای درجه بالاتر با استفاده از همان الگوی می توانند ساخته شوند.

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

پاسخ: = فهرست (Linest (C2: C13 ، B2: B13^) ، 1 ، 3)

معادله و فرمول های روند قدرت

یک روند قدرت در اکسل بر اساس این معادله ساده ترسیم شده است:

جایی که A و B ثابت هستند ، که می تواند با این فرمول ها محاسبه شود:

پاسخ: = exp (index (linest (ln (y) ، ln) ،) ، 1 ، 2))

b: = index (linest (ln (y) ، ln (x) ،) ، 1)

در مورد ما ، فرمول های زیر یک درمان کار می کنند:

a: = exp (index (linest (ln (c2: c13) ، ln (b2: b13) ،) ، 1 ، 2)

b: = index (linest (ln (c2: c13) ، ln (b2: b13) ،) ، 1)

معادله Excel Trendline اشتباه است - دلایل و رفع

اگر فکر می کنید که اکسل به طور نادرست یک روند را ترسیم کرده است یا فرمول Trendline که در نمودار شما نمایش داده می شود اشتباه است ، ممکن است دو نکته زیر اوضاع را روشن کند.

معادله Trendline اکسل فقط در نمودارهای پراکنده درست است

فرمول های Trendline Excel فقط باید با نمودارهای XY (پراکندگی) استفاده شود زیرا فقط در این نوع نمودار هم محور y و هم محور x به عنوان مقادیر عددی ترسیم می شوند.

در نمودارهای خط ، نمودارهای ستون و نوار ، مقادیر عددی فقط در محور y ترسیم می شوند. محور x بدون در نظر گرفتن اینکه برچسب های محور اعداد یا متن هستند ، توسط یک سری خطی (1 ، 2 ، 3 ، ...) نشان داده شده است. هنگامی که شما در این نمودارها یک خط روند ایجاد می کنید ، اکسل از آن مقادیر X فرض شده در فرمول Trendline استفاده می کند.

اعداد در معادله Trendline Excel گرد شده اند

برای اشغال فضای کمتری در نمودار ، اکسل در یک معادله خط روند ، رقم های بسیار قابل توجهی را نشان می دهد. از نظر طراحی خوب ، وقتی مقادیر X را به صورت دستی در معادله تهیه می کنید ، دقت فرمول را به میزان قابل توجهی کاهش می دهد.

رفع آسان برای نشان دادن مکان های اعشاری بیشتر در معادله است. از طرف دیگر ، می توانید ضرایب را با استفاده از فرمول متناسب با نوع روند خود محاسبه کنید و سلولهای فرمول را به گونه ای شکل دهید که تعداد کافی از مکان های اعشاری را نشان دهند. برای این کار ، به سادگی روی دکمه افزایش DECIMAL در برگه Home در گروه شماره کلیک کنید.

اینگونه است که می توانید انواع مختلف روند را در اکسل بسازید و معادلات آنها را بدست آورید. من از شما برای خواندن تشکر می کنم و امیدوارم که هفته آینده شما را در وبلاگ ما ببینید!