عمومی 571 بازدید

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

منحنی کالیبراسیون خطی در اکسل چیست و چگونه ایجاد می‌شود؟

برای اجرای کالیبراسیون باید قرائت‌های مختلف از یک دستگاه – مانند دمای یک دماسنج – را با مقادیر مشخص که استاندارد نامیده می‌شوند، مانند دمای جوش یا یخ زدن آب، مقایسه کنید. بدین ترتیب یک سری از نقاط داده ایجاد می‌شود که می‌توان از آن‌ها برای ایجاد منحنی کالیبراسیون بهره گرفت. دو کالیبراسیون دونقطه‌ای از یک دماسنج با استفاده از نقاط یخ زدن و جوش آب می‌تواند دو جفت داده داشته باشد که یک از آن‌ها زمانی است که دماسنج در آب یخ (32 درجه فارنهایت یا صفر درجه سلسیوس) قرار گرفته و دیگری هنگامی است که دماسنج در آب جوش (212 درجه فارنهایت یا 100 درجه سلسیوس) قرار گرفته باشد.

هنگامی که این دو جفت داده را به عنوان نقاط داده رسم می‌کنید و خطی بین آن‌ها می‌کشید منحنی کالیبراسیون به دست می‌آید. با فرض این که واکنش دماسنج خطی باشد، می‌توانید هر نقطه‌ای را روی خطی که مقادیر را به هم مرتبط ساخته انتخاب کنید و بدین ترتیب می‌توانید دمای متناظر «صحیح» دماسنج را مشاهده کنید. بدین ترتیب این خط اساساً اطلاعاتی را که بین دونقطه معلوم وجود داشت برای ما پر می‌کند و از این رو می‌توانیم به طور معقولی مطمئن باشیم که وقتی دمای دماسنج را 57.2 درجه قرائت می‌کنیم، دمای واقعی را نمایش می‌دهد؛ این در حالی است که هرگز یک استاندارد مرتبط با قرائت را اندازه‌گیری نکرده‌ایم.

رسم نمودار منحنی کالیبراسیون

اکسل امکاناتی دارد که امکان ترسیم جفت‌های داده را به صورت گرافیکی در یک نمودار ارائه می‌کند. همچنین می‌توان یک خط روند (منحنی کالیبراسیون) ترسیم کرده و معادله منحنی کالیبراسیون را روی نمودار نمایش داد. این وضعیت برای یک ارائه بصری مفید است، اما می‌توانید فرمول خط را با استفاده از تابع‌های SLOPE و INTERCEPT اکسل نیز محاسبه کنید. زمانی که این مقادیر را در فرمول‌های ساده‌ای وارد می‌کنید، می‌توانید به صورت خودکار مقدار صحیح را بر مبنای هر اندازه‌گیری به دست بیاورید.

بررسی یک مثال

در این مثال، یک منحنی کالیبراسیون را بر مبنای یک سری از نقاط داده‌ای ترسیم می‌کنیم که هر یک متناظر با مقدار X و مقدار Y است. مقادیر X همان استانداردهای ما هستند و می‌توانند نمایش‌دهنده هر چیزی از غلظت یک محلول شیمیایی که با استفاده از ابزار علمی اندازه‌گیری کرده‌ایم تا مقدار ورودی یک برنامه که یک دستگاه صنعتی را کنترل می‌کند باشد.

مقادیر Y همان پاسخ‌ها خواهند بود که نشان‌دهنده قرائت‌های ابزار هستند که در زمان اندازه‌گیری محلول شیمیایی یا میزان عملکرد دستگاه صنعتی آن‌ها را به دست می‌آوریم.

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

گام اول: ایجاد نمودار

اسپردشیت مثال ساده ما دو ستون دارد که شامل مقادیر X و مقادیر Y است.

کالیبراسیون خطی در Excel

کار خود را با انتخاب کردن داده‌ها و ترسیم آن‌ها روی نمودار آغاز می‌کنیم. ابتدا سلول‌های ستون X-Value را انتخاب می‌کنیم.

کالیبراسیون خطی در Excel

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

کالیبراسیون خطی در Excel

در ادامه به برگه Insert می‌رویم.

کالیبراسیون خطی در Excel

به منوی Charts می‌رویم و گزینه نخست را در منوی بازشدنی Scatter انتخاب می‌کنیم.

کالیبراسیون خطی در Excel

یک نمودار ظاهر خواهد شد که شامل نقاط داده‌ای از دو ستون است.

کالیبراسیون خطی در Excel

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

کالیبراسیون خطی در Excel

روی یکی از نقاط راست کلیک کرده و سپس گزینه «Add Trendline» را انتخاب کنید.

کالیبراسیون خطی در Excel

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

کالیبراسیون خطی در Excel

در سمت راست صفحه منوی «Format Trendline» ظاهر خواهد شد. کادرهای کنار گزینه‌های Display Equation on chart و Display R-squared value on chart را انتخاب کنید. مقدار مربع R یک آماره است که نشان می‌دهد خط مربوطه چه مقدار به داده‌ها نزدیک است. بهترین مقدار مربع R برابر با 1.000 است که به این معنی است که همه نقاط داده‌ای با خط در تماس هستند. زمانی که اختلاف‌های بین نقاط داده‌ای و خط افزایش می‌یابند، مقدار مربع R کاهش می‌یابد و وقتی به 0.000 برسد، کمترین مقدار ممکن را دارد.

کالیبراسیون خطی در Excel

این معادله و آماره مربع R از خط روند روی نمودار نمایش خواهد یافت. دقت کنید که همبستگی داده‌ها در مثال ما بسیار مناسب است و مقدار مربع R برابر با 0.988 است.

این معادله به شکل Y = Mx + B است که M شیب و V محل تقاطع محور y خط مستقیم است.

کالیبراسیون خطی در Excel

سفارشی‌سازی نمودار

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

کالیبراسیون خطی در Excel

اینک عنوان جدید را که نمودار را توصیف می‌کند وارد کنید:

کالیبراسیون خطی در Excel

برای افزودن عناوین برای محورهای X و Y ابتدا به مسیر Chart Tools > Design بروید.

کالیبراسیون خطی در Excel

روی منوی بازشدنی Add a Chart Element کلیک کنید.

کالیبراسیون خطی در Excel

اینک به مسیر Axis Titles > Primary Horizontal بروید.

کالیبراسیون خطی در Excel

یک عنوان محور نمایش می‌یابد.

کالیبراسیون خطی در Excel

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

کالیبراسیون خطی در Excel

سپس به مسیر Axis Titles > Primary Vertical بروید.

کالیبراسیون خطی در Excel

یک عنوان محور نمایش می‌یابد.

کالیبراسیون خطی در Excel

این عنوان را با انتخاب کردن متن و وارد کردن عنوان جدید به‌روزرسانی کنید.

کالیبراسیون خطی در Excel

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

کالیبراسیون خطی در Excel

گام دوم: محاسبه معادله خط و آماره مربع R

اینک نوبت به محاسبه معادله خط با استفاده از تابع‌های داخلی SLOPE ،INTERCEPT و CORREL اکسل رسیده است. در شیت ما (در ردیف 14) عناوینی برای این تابع‌ها اضافه کرده‌ایم. محاسبات واقعی را در سلول‌های زیر این عناوین اجرا خواهیم کرد.

فرمول SLOPE

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

کالیبراسیون خطی در Excel

به مسیر Formulas > More Functions > Statistical > SLOPE بروید.

کالیبراسیون خطی در Excel

در این مرحله پنجره آرگومان‌های سفارش باز می‌شود. در فیلد «Known_ys» سلول‌های ستون مقدار Y را انتخاب یا وارد کنید.

کالیبراسیون خطی در Excel

در فیلد «Known_xs» سلول‌های ستون X-Value را انتخاب یا وارد کنید. ترتیب فیلدهای Known_ys و Known_xs در تابع SLOPE حائز اهمیت است.

کالیبراسیون خطی در Excel

روی OK کلیک کنید. فرمول نهایی در نوار فرمول باید مانند زیر باشد:

=SLOPE(C3:C12,B3:B12)

دقت کنید که مقدار بازگشتی از تابع SLOPE در سلول A15 با مقدار نمایش یافته روی نمودار مطابقت داشته باشد.

کالیبراسیون خطی در Excel

فرمول INTERCEPT

سپس سلول B15 را انتخاب کنید. و به مسیر Formulas > More Functions > Statistical > INTERCEPT بروید.

کالیبراسیون خطی در Excel

پنجره آرگومان‌های تابع باز خواهد شد. سلول‌های ستون Y-Value را برای فیلد Known_ys انتخاب یا وارد کنید.

کالیبراسیون خطی در Excel

سلول‌های ستون X-Value را برای فیلد Known-xs انتخاب یا وارد کنید. ترتیب فیلدهای Known_ys و Known_xs نیز برای تابع INTERCEPT حائز اهمیت است.

کالیبراسیون خطی در Excel

روی OK کلیک کنید. فرمول نهایی در نوار فرمول باید مانند زیر باشد:

=INTERCEPT(C3:C12,B3:B12)

دقت کنید که مقدار بازگشتی از سوی تابع INTERCEPT با مقدار y-intercept نمایش یافته در نمودار مطابقت داشته باشد.

کالیبراسیون خطی در Excel

فرمول CORREL

سپس سلول C15 را انتخاب کرده و به مسیر Formulas > More Functions > Statistical > CORREL بروید.

کالیبراسیون خطی در Excel

در این مرحله پنجره آرگومان‌های تابع باز می‌شود. یکی از دو بازه سلول برای فیلد Array1 را انتخاب یا وارد کنید. برخلاف SLOPE و INTERCEPT در مورد تابع CORREL ترتیب داده‌ها تأثیری روی نتیجه ندارد.

کالیبراسیون خطی در Excel

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

کالیبراسیون خطی در Excel

بر روی OK کلیک کنید. این فرمول باید اینک در نوار فرمول اکسل ظاهر شود:

=CORREL(B3:B12,C3:C12)

دقت کنید که مقدار بازگشتی از سوی تابع CORREL با مقدار r –squared روی نمودار مطابقت ندارد. تابع CORREL مقدار R را بازگشت می‌دهد و از این رو باید آن را به توان 2 برسانید تا به صورت مربع R دربیاید.

کالیبراسیون خطی در Excel

درون نوار فرمول کلیک کرده و عبارت 2^ را به انتهای فرمول اضافه کنید تا مقدار بازگشتی از سوی تابع CORREL به توان 2 برسد. اکنون فرمول کامل باید به صورت زیر باشد:

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

دکمه enter را بزنید.

کالیبراسیون خطی در Excel

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

کالیبراسیون خطی در Excel

گام سوم: تنظیم فرمول‌ها برای محاسبه سریع مقادیر

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

این گام‌ها برای تنظیم فرمول‌های مورد نیاز جهت وارد کردن X-Value و یا Y-Value برای گرفتن مقادیر متناظر بر مبنای منحنی کالیبراسیون استفاده می‌شوند.

کالیبراسیون خطی در Excel

خط بهترین برازش

معادله خط با بهترین برازش با شکل Y-value = SLOPE * X-value + INTERCEPT است، بنابراین برای Y-Value این کار از طریق ضرب کردن X-Value و SLOPE و سپس جمع کردن با INTERCEPT به دست می‌آید.

کالیبراسیون خطی در Excel

به عنوان مثال ما مقدار صفر را در X-Value قرار می‌دهیم. مقدار Y بازگشتی باید برابر با INTERCEPT خط بهترین برازش باشد. این دو مورد مطابقت دارند و از این رو می‌دانیم فرمول به درستی کار می‌کند.

کالیبراسیون خطی در Excel

حل کردن X-Value بر مبنای Y-Value از طریق کسر کردن INTERCEPT از Y-Value و تقسیم نتیجه بر SLOPE به دست می‌آید:

X-value=(Y-value-INTERCEPT)/SLOPE

کالیبراسیون خطی در Excel

به عنوان نمونه، ما از INTERCEPT به عنوان Y-Value استفاده کرده‌ایم. X-Value بازگشتی باید برابر با صفر باشد؛ اما مقدار بازگشتی 3.14934E-06 است. مقدار بازگشتی صفر نیست زیرا هنگام وارد کردن مقدار ناخواسته INTERCEPT را گرد کرده‌ایم. فرمول به طرز صحیحی کار می‌کند نتیجه فرمول برابر با 0.00000314934 است که درواقع صفر محسوب می‌شود.

کالیبراسیون خطی در Excel

شما می‌توانید هر X-Value که می‌خواهید در سلول با حاشیه ضخیم نخست وارد کنید تا اکسل Y-Value متناظر را به صورت خودکار محاسبه کند.

کالیبراسیون خطی در Excel

وارد کردن Y-Value در سلول با حاشیه ضخیم دوم باعث می‌شود X-Value متناظر به دست آید. این فرمول آن چیزی که است که باید برای محاسبه میزان صحت غلظت محلول و تنظیمات دستگاه صنعتی مورد استفاده قرار دهیم.

کالیبراسیون خطی در Excel

سخن پایانی

در این مثال دستگاه ما مقدار 5 را قرائت کرده است و از این رو کالیبراسیون پیشنهاد می‌کند که غلظت محلول 4.94 است و اگر می‌خواهیم تنظیم دستگاه خود را روی 5 قرار بدهیم، اکسل پیشنهاد می‌کند که مقدار 4.94 را در برنامه وارد کنیم. این نتایج به طرز معقولی قابل اطمینان هستند، زیرا مقدار مربع R در این مثال بالا بوده است.

اگر این مطلب برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

==

بر اساس رای 1 نفر

آیا این مطلب برای شما مفید بود؟

نظر شما چیست؟

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