در نرم‌افزار اکسل علاوه بر ترسیم نمودارهای زیبا و گرافیکی، می‌توانید رابطه‌ی بین داده‌های خروجی و داده‌های ورودی را با ترسیم خط تمایل یا منحنی کالیبراسیون پیدا کنید. یافتن رابطه‌ی خطی بین متغیر X و Y یکی از موضوعاتی است که در علوم مختلف بسیار کاربرد دارد.

در اکسل برای محاسبه کردن شیب خط تمایل از تابع SLOPE و برای یافتن محل برخورد با محور Y که عرض از مبداء گفته می‌شود، از تابع INTERCEPT استفاده می‌شود.

در این مقاله به روش رسم کردن خط یا منحنی که از میان نقاط داده عبور می‌کند و یافتن معادله‌ی آن در نرم‌افزار Excel مجموعه‌ی آفیس مایکروسافت می‌پردازیم.

آشنایی با منحنی کالیبراسیون و خط تمایل

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

رابطه‌ی طول فنر و وزنه و رابطه‌ی جریان و ولتاژ

با آویزان کردن وزنه‌های مختلف از فنر، طول فنر بیشتر می‌شود یا با افزایش ولتاژ منبع تغذیه، میزان جریان الکتریکی که از سیمی از مدار عبور می‌کند، بیشتر می‌شود.

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

کالیبراسیون ترمومتر دیجیتال با دمای آب

با استفاده از منحنی کالیبراسیون می‌توانید عمل کالیبره کردن را هم انجام بدهید. به عنوان مثال اگر یک دماسنج دیجیتال داشته باشید که دما را با عددی نامفهوم نمایش می‌دهد، می‌توانید جدولی ساده طراحی کنید که دمای مربوط به هر عدد در آن ذکر شده است.

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

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

برای استفاده از فرمول منحنی تمایل و یافتن اعداد، می‌توان از توابعی مثل SLOPE و INTERCEPT در اکسل استفاده کرد.

ترسیم نمودار ورودی و خروجی و Trend Line در اکسل

برای توضیح روش کار، یک مثال ساده را بررسی می‌کنیم.. محور x نمودار یا ورودی و محور y یا خروجی را در یک جدول ساده وارد می‌کنیم.

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

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

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

و سپس کلید Ctrl را نگه دارید و این بار هم با روش کلیک و درگ، سلول‌های سری Y را انتخاب کنید.

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

اکنون روی تب Insert نوار ابزار بالای صفحه، کلیک کنید.

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

در بخش Charts یا نمودارها، روی نمودار نقاط یا Scatter کلیک کنید و سپس روی اولین حالت کلیک کنید.

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

به این ترتیب نمودار نقاط رسم می‌شود.

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

در صورت نیاز می‌توانید موارد مختلف مثل رنگ، فونت، عنوان نمودار و عنوان سری‌ها و غیره را سفارشی کنید که در ادامه مورد بررسی قرار می‌گیرد.

برای انتخاب سری داده، روی یکی از نقاط نمودار کلیک کنید و نتیجه این است که Excel تمام نقاط سری را انتخاب می‌کند.

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

برای اضافه کردن ترند لاین، روی یکی از نقاط نمودار راست‌کلیک کرده و گزینه‌ی Add Trendline را انتخاب کنید.

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

به این ترتیب خط صافی که از میان نقاط عبور کرده، نمایان می‌شود.

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

در سمت راست منوی Format Trendline نیز اضافه می‌شود. برای رویت کردن معادله‌ی خط نمودار که در واقع رابطه‌ی بین خروجی و ورودی مسأله است، تیک گزینه‌ی Display Equation on chart را بزنید. از طرف دیگر برای بررسی میزان خطا که یک کمیت آماری است، Display R-squared value on chart را تیک بزنید.

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

هر چه مقدار R-squared یا R2 به عدد ۱ نزدیک‌تر شود، به این معنی است که خط ترسیم شده، به نقاط نمودار نزدیک‌تر است. طبعاً اگر این عدد به ۰ نزدیک شود، خطا زیاد است و رابطه‌ی بین خروجی و ورودی، چیزی شبیه به معادله‌ی نمایش داده شده نیست.

در اکسل ترسیم منحنی‌های تمایل با معادله‌های مختلف امکان‌پذیر است. همان‌طور که در تصویر فوق مشاهده می‌کنید، منحنی نمایی یا Exponential و حالت لگاریتمی یا Logarithmic و همین‌طور چندجمله‌ای یا Polynomial که درجه‌ی چندجمله‌ای آن در فیلد Order قابل تعریف است، حالت توانی یا Power و پاره خط آزاد یا Moving، همگی قابل انتخاب است.

در این مثال ساده، همان‌طور که مشاهده می‌کنید تقریب رابطه‌ی خطی بین ورودی و خروجی بسیار به واقعیت نزدیک است و لذا مقدار R2 بسیار به ۱ نزدیک است.

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

حال برای تغییر عنوان نمودار، روی بلوک عنوان کلیک کنید و سپس متن درون آن را انتخاب کنید.

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

عنوان جدید و مناسب را وارد کنید.

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

برای اضافه کردن عنوان برای توضیح محور x و y نیز ابتدا روی نمودار کلیک کنید و سپس در نوار ابزار بالای صفحه، روی تب Design در بخش Chart Tools کلیک کنید.

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

حال روی Add a Chart Element کلیک کنید.

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

سپس از Axis Titles و Primary Horizontal برای افزودن بلوک نام و توضیحات محور افقی استفاده کنید.

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

اکنون می‌توانید روی این بلوک جدید کلیک کنید.

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

و با انتخاب کردن متن و تایپ عنوان و توضیح جدید، محور ایکس را توصیف کنید.

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

برای افزودن عنوان محور عمودی نیز از منوی Add a Chart Element و گزینه‌ی Axis Titles و سپس Primary Vertical استفاده کنید. 

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

به این ترتیب عنوان محور عمودی نیز نمایان می‌شود.

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

نام محور عمودی را نیز تغییر بدهید.

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

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

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

محاسبه‌ی معادله خط Trend Line با توابع SLOPE و INTERCEPT و CORREL

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

ابتدا سلولی را با کلیک کردن، انتخاب کنید که در این مثال از A15 استفاده می‌کنیم.

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

سپس در منوی Formulas بالای صفحه، روی More Functions و سپس Statistical کلیک کنید که حاوی توابع آماری است. در لیست توابع اسکرول کنید و روی SLOPE کلیک کنید.

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

پنجره‌ی تابع موردبحث نمایان می‌شود. در فیلد Known_ys می‌بایست داده‌های خروجی و در فیلد Known_xs می‌بایست داده‌های ورودی را تایپ یا انتخاب کنید. لذا روی اولین فیلد کلیک کنید و سپس با روش ساده‌ی کلیک و درگ، سلول‌های ستون Y را انتخاب کنید.

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

برای فیلد بعدی هم همین کار را انجام بدهید با این تفاوت که سری X یا سلول‌های وروردی می‌بایست انتخاب شود.

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

در نهایت روی OK کلیک کنید.

روش دیگر برای فرمول‌نویسی و استفاده از SLOPE، نوشتن فرمول به صورت مستقیم است. کافی است روی سلول موردنظر کلیک کنید و فرمول زیر را تایپ کنید. اولین آرگومان سلول‌های خروجی و دومین آرگومان سلول‌های ورودی است.

=SLOPE(C3:C12,B3:B12)

به این ترتیب شیب خط محاسبه می‌شود.

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

تابع بعدی برای محاسبه‌ی معادله‌ی خط، تابع INTERCEPT است که نقطه‌ی برخورد خط با محور Y را مشخص می‌کند. این تابع هم دو آرگومان مشابه تابع SLOPE دارد.

روش فرمول‌نویسی نیز کاملاً مشابه است: ابتدا روی سلول موردنظر کلیک کنید و سپس از مسیر Formulas > More Functions > Statistical > INTERCEPT در نوار ابزار بالای صفحه، تابع INTERCEPT را انتخاب کنید.

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

در پنجره‌ی نمایش داده شده، روی فیلد محور y و سپس x کلیک کنید و برای هر یک سری داده‌ی مربوطه را انتخاب کنید.

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

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

و در نهایت روی OK کلیک کنید.

در مثال مال فرمول زیر در سلول B15 قرار می‌گیرد:

=INTERCEPT(C3:C12,B3:B12)

و نتیجه نقطه‌ی برخورد با محور Y است، که در معادله‌ی خط y = 1.0584x - 0.2313 روی نمودار نیز دیده می‌شود.

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

آخرین تابع آماری، تابع CORREL است که میزان همبستگی را محاسبه می‌کند.

برای استفاده از این تابع هم می‌توانید روی سلولی کلیک کرده و سپس از مسیر Formulas > More Functions > Statistical > CORREL، پنجره‌ی تعریف آرگومان‌های تابع را باز کنید.

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

در ادامه در فیلد Array1 یکی از سری‌ها را انتخاب کنید. تابع همبستگی مثل دو تابع SLOPE و INTERCEPT، تفاوتی بین سری y و x قائل نمی‌شود بلکه صرفاً میزان همبستگی این دو سری داده را محاسبه می‌کند. لذا می‌توانید جای سری‌ها را جابجا کنید.

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

در ادامه در فیلد Array2 کلیک کنید و سری داده‌ی دیگر را انتخاب کنید.

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

روی OK کلیک کنید. فرمول نهایی به این صورت خواهد بود که در این مثال آن را در سلول C15 قرار داده‌ایم:

=CORREL(B3:B12,C3:C12)

خروجی تابع همبستگی می‌بایست به توان ۲ برسد تا عددی مشابه R2 به دست آید. لذا اگر می‌بایست فرمول فوق را کمی تغییر بدهید.

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

بنابراین روی سلولی که فرمول در آن تایپ شده کلیک کنید و سپس در نوار فرمول بالای صفحه یا داخل سلول، در انتهای فرمول، ^2 را اضافه کنید.

=CORREL(B3:B12,C3:C12)^2

و سپس Enter را فشار دهید.

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

به این ترتیب آنچه روی نمودار درج شده با محاسبه از طریق تابع CORREL یکسان می‌شود.

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

محاسبه‌ی خروجی یا Y با وارد کردن ورودی یا X و برعکس

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

برای این کار دو جدول ساده ایجاد کنید:

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

خروجی با رابطه‌ی زیر محاسبه می‌شود:

Y-result = SLOPE * X-entry + INTERCEPT

لذا در سلول Y Result از فرمول فوق استفاده شده است:

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

و اگر بخواهید X را بر حسب Y محاسبه کنید، فرمول به این صورت خواهد بود:

X-result = (Y-entry-INTERCEPT)/SLOPE

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

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

لذا به راحتی می‌توان ورودی و خروجی موردنظر را محاسبه کرد.