رسم منحنی کالیبراسیون خطی در اکسل (+ دانلود فیلم آموزش گام به گام)

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

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

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

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

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

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

فیلم نمودار منحنی چیست؟ + راهنمای رسم در اکسل (رایگان) در فرادرس

کلیک کنید

بررسی یک مثال

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

کلیک کنید

فرمول SLOPE

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

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

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

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

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

=SLOPE(C3:C12,B3:B12)

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

فرمول INTERCEPT

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

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

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

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

=INTERCEPT(C3:C12,B3:B12)

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

فرمول CORREL

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

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

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

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

=CORREL(B3:B12,C3:C12)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

سخن پایانی

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

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

• مجموعه آموزش‌های نرم‌‌افزار مایکروسافت اکسل
• آموزش‌های مجموعه نرم‌‌افزاری آفیس
• گنجینه آموزش های اکسل (Microsoft Excel)
• مدیریت نوار (Ribbon) اکسل با چهار ترفند کاربردی — راهنمای جامع
• گرد کردن اعداد اعشاری در اکسل – آموزش گام به گام
• فرمول نویسی در اکسل – آموزش مقدماتی

==