فیت کردن دو یا چند نمودار در نرم افزار Excel

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

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

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

در پنجره Format Trendline

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

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

• 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

بخش دوم، بخش TrendLine Name می‌باشد.

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

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

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

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

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

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

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

علاوه بر استفاده گرافیکی از ابزار Trend Line، می‌توان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار به دست آورد.
1- تابع Slope: محاسبه شیب رگرسیون خطی.

=SLOPE(Known Y values, Known X values)

برای مثال زیر شیب خط تقریبا 2.15 می‌باشد.

=SLOPE(B2:B6,A2:A6) = 2.15

2- تابع Intercept: محاسبه عرض از مبدا رگرسیون خطی.که برای مثال بالا تقریبا 0.47- می باشد.

=INTERCEPT(Known Y values, Known X values)

=INTERCEPT(B2:B6,A2:A6) = -0.47

یعنی در واقع معادله رگرسیون خطی این مثال برابر است با:        y = 2.15*x -0.47

3- تابع Forecast: برای پیش‌بینی y متناظر با یک x جدید بر مبنای رگرسیون خطی.

=FORECAST(New X Value, Known Y values, Known X values)
=FORECAST(15,B2:B6,A2:A6) = 31.778

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

=GROWTH(Known Y Values, Known X Values, New X Values, Const)
=GROWTH(B2:B6,A2:A6,15,TRUE) = 48.68

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

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

آموزشی که آماده کردیم راه حل این مشکل است.
برای این کار مطابق دستور العمل زیر عمل می کنیم
1- ایجاد جدول ابتدا جدولی را طراحی کنید
مطابق معمول هدف از ایجاد اینچنین نمودار هایی زیبایی و جلب نظر مخاطب است.پس این بار هم پاره ای از اقدامات برای زیبایی نمودار خود انجام می دهیم.
2- از قسمت INSERT گزینه SHAPES را انتخاب می کنیم و شکل مربع را بر می گزینیم.و رنگ خاکستری را برای ان انتخاب می کنیم
3- از مسیر فوق شکل مثلث را انتخاب کرده (رنگ مشکی)
4- و در نهایت شکل یک مربع جهت دار را انتخاب می کنیم (رنگ سیز)


5- حالا از قسمت DEVELOPERدراکسل(برای اضافه کردن این قسمت در ریبون خود مسیر زیر را دنبال کنید:
FILE – OPTION – CUSTOMIZE RIBBON –
و از کادر سمت راست تیک مربوطه را بزنید)
وارد قسمت INSERT گزینه کامبو باکس را انتخاب می کنیم

روی باکس ایجاد شده کلیک راست کرده و گزینه FORMAT CONTROL را انتخاب و در کادر ظاهر شده در قسمت INPUT RANGE محدوده REGION را انتخاب می کنیم و در قسمت CELL LINK یک سلول خالی اختصاص دهید.در اینجا ما سلول C33 را اختصاص دادیم.سپس OK
حالا شما از کرکره ایجاد شده هر محدوده ای را انتخاب کنید عدد متناظر با آن محدوده در سلول C33 نمایش داده می شود

6-
دقت داشته باشید که در جدول اول عدد 4 مقابل SELECTED REGION همان خانه C33 است که در CELL LINK انتخاب کرده ایم.

در فرمول های فوق به دلیل اینکه هر محدوده از جدول در قسمت DEFINE برای اکسل تعریف شده نام آن نمایش داده شده است تا شما نیز بهتر متوجه محدوده انتخاب شده شوید.شما می توانید محدوده ای که  نام آن نمایش داده شده را به صورت دستی و یا با موس انتخاب کنید.
لازم به ذکر است که فرمولهای index و match از خانوانده lookup بوده و برای جستجو بکار می روند.در اینجا فرمول index به این معنی است که مثلاً از ستون then عدد واقع در ردیف 4 را بخوان..
در شکل های فوق جدول calculations برای معرفی و نمایش، جدول break-up و xy نیز که در ادامه برا آنها نمودار های bar و scatter ترسیم خواهد شد.
7- برای رسم نمودار bar برای جدول break – up محدوده جدول را انتخاب کرده

و برای رسم نمودار scatter برای جدول x,y ابتدا محدوده جدول را انتخاب کرده

حالا نمودار های ایجاد شده را در مربع خاکستری رنگ که اول ایجاد کردیم قرار می دهیم.
8- در نمودار scatter فوق در نقاط ابتدا و انتهای نمودار یک دایره و یک فلش مشاهده می شود.
این نمودار با توجه به جدول x,y از چهار نقطه تشکیل شده است شما لازم است روی نتقه ابتدایی و انتهایی جداگانه کلیک کرده به طوری که یکی از انها فقط انتخاب شود حالا  مسیر را دنبال کنید layout – format selection

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

9-
ما در فرمول ها هر چیز را که بخواهیم به عنوان متن به نمایش در بیاید بین " " مینویسیم.
علامت & برا ی اتصال متن و فرمول استفاده می شود.
فرمول text: اگر بخواهید عددی را به فرمت خاصی نمایش دهید استفاده می شود

• شما برای کسب اطلاعات بیشتر می توانید در قسمت Help نرم افزار text را تایپ کرده سپس text function را انتخاب کرده تا با کارایی بیشتر این فرمول آشنا شوید.
در اینجا ما می خواهیم عدد مورد نظر به شکل درصد نشان داده شود
فرمول ABS: این فرمول قدر مطلق عدد را بر می گرداند
حالا زمان اختصاص این فرمول به کادر سبز رنک است. روی کادر سبز رنک کلیک کرده تا به حالت انتخاب در بیاید سپس در نوار فرمول، فرمول زیر را بنویسید و اینتر را بزنید
=$C$46
حالا نمودار شما اماده است.                                                                 امیدوارم این آموزش اکسل  مورد استفاده شما قرار بگیرد و از آن لذت برده باشید