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

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

خط روند در اکسل

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

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

ساده ترین روش ایجاد یک نمودار کلوچه ای در اکسل

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

نمودار های اکسلی که از خطوط روند پشتیبانی می کنند

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

شما نمی توانید خط روند را به نمودار های سه بعدی یا انباشته، دایره ای، راداری و انواع مشابه اضافه کنید.

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

نحوه اضافه کردن خط روند به اکسل

در اکسل 2019، 2016 و 2013، افزودن خط روند طی فرآیند سریع 3 مرحله ای انجام می شود:

1-روی هر نقطه از نمودار برای انتخاب کلیک کنید.

2- در سمت راست نمودار، روی دکمه Chart Elements (دکمه به اضافه) کلیک کنید و بعد یکی از کار های زیر را انجام دهید:

• برای قرار دادن خط روند پیش فرض، تیک کادر” Trendline” را بزنید.

• روی فلش کنار کادر Trendline کلیک کنید و یکی از چیز هایی که پیشنهاد می شود را انتخاب کنید:

• روی فلش کنار Trendline و بعد روی More Options کلیک کنید. با این کار، قاب Format Trendline باز می شود که از طریق آن می توانید به زبانه Trendline Options بروید تا هر نوع خط روندی که در اکسل وجود دارد را ببینید و هر کدام که خواستید را انتخاب کنید. خط روند Linear پیش ‌فرض به ‌طور خودکار از قبل انتخاب می ‌شود. اگر خواستید ؛ می توانید معادله خط روند را هم در نمودار نمایش دهید.

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

نحوه اضافه کردن خط روند در اکسل 2010

برای افزودن خط روند در اکسل 2010، باید روش دیگری را امتحان کنید:

1-در نمودار، روی دنباله داده ‌ای که می ‌خواهید خط روندش را ترسیم کنید، کلیک کنید.

2- در قسمتِ Chart Tools، به زبانه Layout رفته و بعد وارد گروه Analysis‌ شوید. یا روی Trendline کلیک کنید یا یکی از کار های زیر را انجام بدهید:

• یکی از گزینه های از پیش تعریف شده را انتخاب کنید، یا
• روی قسمتِ More Trendline Options… کلیک کنید و بعد نوع خط روندی که می خواهید را برای نمودارتان انتخاب کنید.

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

نحوه قرار دادن چند خط روند در یک نمودار

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

برای هر سری داده یک خط روند اضافه کنید

برای اضافه کردن خط روند به نموداری که دو یا چند سری داده دارد، این کار را انجام بدهید:

1-روی نقاط داده مورد نظر ( نقاط آبی در این مثال ) کلیک راست کرده و از منوی زمینه گزینه Add Trendline… را انتخاب کنید:

2- با این کار، زبانه Trendline Options در صفحه باز می شود و می توانید نوع خط مورد نظر را انتخاب کنید:

3- مراحل بالا را برای سری داده های دیگر هم تکرار کنید.

در نهایت، هر سری داده خط روند خودش را بر اساس همان رنگ خواهد داشت:

یا می توانید روی دکمه Chart Elements کلیک کنید، بعد روی فلش کنار Trendline کلیک و نوع خط روند مورد نظرتان را انتخاب کنید. اکسل لیستی از سری داده هایی که در نمودار تان رسم شده اند را نشان می دهد. گزینه مورد نیاز را انتخاب و روی OK کلیک کنید.

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

ترسیم انواع خط روند ها برای یک سری داده

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

• روی سری داده ها کلیک راست کنید، گزینه Add Trendline… را در منوی زمینه انتخاب و بعد یک نوع متفاوت از خط روند را انتخاب کنید.
• روی دکمه Chart Elements کلیک کنید، روی فلش کنار Trendline کلیک و نوع مورد نظرتان را انتخاب کنید.

در هر صورت، اکسل چند خط روند که در این جا Linear و Moving average است را در نمودار نشان می ‌دهد. در ضمن می ‌توانید برای این خطوط رنگ انتخاب کنید:

نحوه فرمت دهی به خط روند در اکسل

برای این که نمودار شما قابل درک تر شود و راحت تر بتوان آن را تفسیر کرد؛ شاید بهتر باشد که ظاهر پیش فرض خط روند را تغییر دهید. برای این کار، روی آن کلیک راست و بعد روی قسمتِ Format Trendline… کلیک کنید. یا این که روی خط روند دو بار کلیک کنید تا قاب Format Trendline باز شود.

در صفحه، به زبانه Fill & Line بروید و گزینه Color، Width و Dash type را برای خط روندتان انتخاب کنید. به طور مثال به جای خط چین، از یک خط یکدست استفاده کنید:

حتما بخوانید: آموزش کامل نوشتن فرمول ها در نرم افزار اکسل

نحوه ادامه دار کردن خط روند در اکسل

برای نمایش روند داده ها در آینده یا گذشته ، باید این کار را انجام بدهید:

1-روی خط روند دو بار کلیک کنید تا قاب Format Trendline باز شود.

2- در قسمتِ زبانه Trendline Options (آخرین مورد)، مقادیر مورد نظر را در کادر های Forward و یا Backward در قسمت Forecast تایپ کنید:

در این مثال، ما خط روند را به اندازه 8 نقطه یا Period بالاتر از آخرین نقطه داده ادامه می دهیم :

معادله خط روند اکسل

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

هنگام ترسیم خط یا منحنی برازش در اکسل ، می توانید معادله اش را در نمودار نمایش دهید. علاوه بر این، می توانید مقدار ضریب تعیین یا R-squared را هم نمایش دهید.

• دانلود آموزش صفر تا صد ورد
• دانلود آموزش صفر تا صد پاورپوینت
• دانلود آموزش صفر تا صد اکسس

مقدار ضریب تعیین نشان می دهد که خط روند چه قدر با داده ها مطابقت دارد . هر چه مقدار R^2 به 1 نزدیک تر باشد، تناسب بهتری وجود دارد.

نحوه نمایش معادله خط روند روی نمودار

برای نشان دادن معادله و مقدار R-squared در نمودار ، به صورت زیر عمل کنید:

1-روی خط روند دو بار کلیک کنید تا صفحه مربوط به آن باز شود.

2- در صفحه، به زبانه Trendline Options بروید و تیک این کادر ها را بزنید:

• Display Equation on chart
• Display R-squared value on chart

با این کار فرمول خط روند و مقدار R^2 را در بالای نمودارتان قرار می گیرد و شما آزاد هستید که آن ها را به هر کجا که صلاح می دانید ؛ بکشید یاDrag کنید.

در این مثال، مقدار R-squared برابر با 0.957 است که یعنی خط روند با مقدار حدود 95٪ از داده ها مطابقت دارد.

نکته: معادله نمایش داده شده در نمودار اکسل فقط برای نمودار های پراکندگی XY صدق می کند.

آموزش کدنویسی در اکسل از صفر تا صد با 9 درس رایگان

ارقام بیشتری را در معادله خط روند نشان دهید

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

1-فرمول خط روند را در نمودار انتخاب کنید.

2- در صفحه Format Trendline Label که ظاهر می شود، به قسمتِ زبانه Label Options بروید.

3- در لیست کشوییCategory ، گزینه Number را انتخاب کنید.

4- در کادر Decimal Places، تعداد ارقام اعشاری که می خواهید نشان بدهید را ( که می تواند تا 30 رقم باشد ) را تایپ کنید و Enter را فشار دهید تا معادله در نمودار آپدیت شود.

دانلود کنید: دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

نحوه پیدا کردن شیب خط روند در اکسل

برای به دست آوردن شیب خط روندی که به صورت خطی یا Linear است ، مایکروسافت اکسل یک تابع خاص به همین نام دارد:

SLOPE (known_y’s, known_x’s)

• Known _y’s محدوده ای از نقطه داده های وابسته است که روی محور y رسم شده است.
• Known _x’s محدوده ای از نقطه داده های مستقل است که روی محور x رسم شده است.

اگر مقدارx ، B2 : B13 و مقدارy ، C2:C13 باشد فرمول به این صورت در می آید:

=SLOPE (C2:C13, B2:B13)

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

LINEST (C2:C13,B2:B13)

اما اگر با فشار دادن Ctrl + Shift + Enter از فرمول آرایه استفاده کنید، شیب خط روند و عرض از مبدا در دو سلول مجاور در همان ردیف ظاهر خواهد شد.

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

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

نحوه حذف خط روند در اکسل

برای حذف خط روند از نمودار، روی خط راست کلیک کرده و بعد روی گزینه Delete کلیک کنید:

یا روی دکمه Chart Elements کلیک کنید و تیک کادر Trendline را بردارید:

در هر صورت، اکسل بلافاصله خط روند را از نمودار حذف می کند.

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

ویدیوی آموزش خط روند اکسل

به کار میاد: آموزش ایجاد بارکد در اکسل (و نکات کاربردی)