trendline

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

اساطیر زبان

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

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

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

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

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

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

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

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

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

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

Trendline in Excel

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

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

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

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

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

Add the default linear trendline to Excel chart.

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

Choose the trendline type to insert.

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

Open the pane to see all available trendline types.

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

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

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

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

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

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

Insert a trendline in Excel 2010.

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

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

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

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

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

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

Add a trendline for the first data series.

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

Choose the desired line type.

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

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

Excel chart with multiple trendlines

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

Another way to add multiple trendlines to an Excel chart

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

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

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

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

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

Two different trendlines for the same data series

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

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

Format trendline in Excel.

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

Change the appearance of a trendline.

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

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

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

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

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

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

Extend the trendline to forecast future trends.

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

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

هنگام ترسیم خط یا منحنی برازش در اکسل ، می توانید معادله اش را در نمودار نمایش دهید. علاوه بر این، می توانید مقدار ضریب تعیین یا 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٪ از داده ها مطابقت دارد.

Display the trendline equation and R-squared value on a chart.

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

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

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

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

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

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

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

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

Show more digits in the trendline equation.

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

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

برای به دست آوردن شیب خط روندی که به صورت خطی یا 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 از فرمول آرایه استفاده کنید، شیب خط روند و عرض از مبدا در دو سلول مجاور در همان ردیف ظاهر خواهد شد.

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

Formula to find the slope of a trendline in Excel.

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

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

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

Deleting a trendline from a chart

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

Remove a trendline in Excel.

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

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

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

 

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

 

تفاوت پک های حرفه ای ما با آموزشهای رایگان: بطور خلاصه از زمین تا آسمان! 1-پکها، جدیدترین نسخه نرم افزارها را آموزش می دهند با قابلیت های بسیار بیشتر. 2-پکها توسط متخصص آن نرم افزار، به صورت کاملا پروژه محور و با حل چالش هایی که در مسیر کار عملی و حرفه ای با آن روبرو می شوید تهیه شده اند و بعد از استفاده، کاملا برای بازار کار آماده اید! 3- متد این پکها کاملا کار شده و تا ماهها، در ذهن تان ماندگارند و یادگیری بسیار سریعتر و کاملتری خواهید داشت. آموزشهای رایگان فقط دستورات نرم افزارها را (آنهم ناقص) بیان می کنند و تازه برای ورود به بازار باید ماهها تجربه عملی هم کسب کنید !!

turned_in

چرب زبان

با این اپلیکیشن ساده، هر زبانی رو فقط با 5 دقیقه در روز، توی 80 روز مثل بلبل حرف بزن! بهترین متد روز، تقویت حافظه، آموزش تصویری. یادگیری زبان کلید یادگیری هر مهارتی در قرن 21 !


حتما بخوانید!

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این فیلد را پر کنید
این فیلد را پر کنید
لطفاً یک نشانی ایمیل معتبر بنویسید.

فهرست