Microsoft Excel пропонує вбудовані інструменти для візуалізації калібрувальних даних та розрахунку лінії найкращої відповідності. Це особливо корисно при підготовці звітів для хімічної лабораторії або налаштуванні коригувальних коефіцієнтів для різноманітного обладнання.
У цій статті ми розглянемо процес створення діаграми, побудови лінійної калібрувальної кривої, відображення її рівняння, а також застосування функцій SLOPE та INTERCEPT для використання калібрувального рівняння безпосередньо в Excel.
Що таке калібрувальна крива і як Excel допомагає її створювати?
Калібрування передбачає зіставлення показань вимірювального пристрою (наприклад, температури на термометрі) з відомими еталонними значеннями (наприклад, точками замерзання та кипіння води). Це створює набір пар даних, на основі яких будується калібрувальна крива.
Для двоточкового калібрування термометра, використовуючи точки замерзання (0°C або 32°F) та кипіння (100°C або 212°F) води, ви отримаєте дві пари даних. Відобразивши їх на графіку і з’єднавши лінією, ви створите калібрувальну криву. Припускаючи лінійну залежність, будь-яка точка на цій лінії відповідатиме “істинній” температурі, виходячи з показань термометра.
Фактично, лінія заповнює прогалини між двома відомими точками, дозволяючи з більшою впевненістю оцінювати фактичну температуру, навіть якщо ви не проводили вимірювання “стандарту”, що точно відповідає, наприклад, показанню 57.2 градуса.
Excel має інструменти, які дозволяють будувати графіки з парами даних, додавати лінію тренду (калібрувальну криву) та відображати її рівняння. Це зручно для візуалізації, а функції СХИЛ та ПЕРЕРІЗ дозволяють обчислити параметри рівняння. Використовуючи їх у простих формулах, можна автоматично розраховувати “істинне” значення на основі будь-якого вимірювання.
Розглянемо конкретний приклад
У нашому прикладі ми створимо калібрувальну криву на основі десяти пар даних, кожна з яких має значення X та Y. Значення X представляють “стандарти”, такі як концентрація хімічного розчину або вхідна змінна для керування мармуровою пусковою установкою.
Значення Y – це “відповіді”, тобто показання приладу для кожного розчину або виміряна відстань, на яку мармур приземляється, використовуючи кожне вхідне значення.
Після побудови графіка ми використаємо функції SLOPE та INTERCEPT для обчислення рівняння кривої, щоб визначити концентрацію “невідомого” розчину на основі показань приладу, або підібрати вхідні дані для програми, щоб мармур приземлявся на заданій відстані.
Крок 1: Побудова діаграми
Наша таблиця складається з двох стовпців: значення X та значення Y.
Почнемо з вибору даних для графіка.
Спочатку виберіть комірки стовпця “X-Value”.
Тепер, утримуючи клавішу Ctrl, виберіть комірки стовпця “Y-Value”.
Перейдіть на вкладку “Вставка”.
У розділі “Діаграми” виберіть перший варіант “Точковий” (або “Розсіювання”).
З’явиться діаграма з точками даних.
Клацніть на одній з синіх точок, щоб вибрати серію даних. Excel підсвітить вибрані точки.
Клацніть правою кнопкою миші на будь-якій точці і виберіть “Додати лінію тренду”.
На діаграмі з’явиться пряма лінія.
З правого боку з’явиться меню “Формат лінії тренду”. Поставте галочки біля опцій “Показувати рівняння на діаграмі” та “Показувати значення R-квадрат на діаграмі”. R-квадрат є статистикою, яка показує, наскільки точно лінія описує дані. Значення 1,000 означає, що всі точки лежать на лінії. Зі збільшенням розсіювання точок значення зменшується, досягаючи мінімуму 0,000.
Рівняння та значення R-квадрат з’являться на графіку. У нашому прикладі кореляція даних дуже хороша, R-квадрат дорівнює 0,988.
Рівняння має вигляд “Y = Mx + B”, де M – нахил, а B – перетин з віссю Y.
Тепер, коли калібрування завершено, налаштуємо діаграму, змінивши назву та додавши підписи осей.
Щоб змінити назву, клацніть на ній, щоб виділити текст.
Введіть нову назву, яка описує діаграму.
Щоб додати підписи осей, перейдіть в “Інструменти діаграм” > “Конструктор”.
Натисніть на “Додати елемент діаграми”.
Виберіть “Назви осей” > “Основна горизонтальна”.
З’явиться назва осі.
Щоб перейменувати назву осі, виділіть текст та введіть новий заголовок.
Тепер виберіть “Назви осей” > “Основна вертикальна”.
З’явиться назва осі.
Перейменуйте цю назву, виділивши текст та ввівши новий заголовок.
Тепер ваша діаграма готова.
Крок 2: Обчислення рівняння лінії та статистики R-квадрат
Тепер обчислимо рівняння лінії та статистику R-квадрат за допомогою функцій SLOPE, INTERCEPT та CORREL.
До нашої таблиці (рядок 14) додаємо заголовки для цих трьох функцій. Обчислення виконуємо у комірках під цими заголовками.
Спочатку обчислимо НАХИЛ. Виберіть комірку A15.
Перейдіть до “Формули” > “Інші функції” > “Статистичні” > “СХИЛ”.
З’явиться вікно аргументів функції. У полі “Відомі_значення_y” виберіть або введіть комірки стовпця Y-Value.
У полі “Відомі_значення_x” виберіть або введіть комірки стовпця X-Value. Порядок полів “Відомі_значення_y” та “Відомі_значення_x” важливий для функції SLOPE.
Натисніть “OK”. Остаточна формула в рядку формул має виглядати так:
=СХИЛ(C3:C12,B3:B12)
Значення, повернуте функцією SLOPE в комірці A15, відповідає значенню на діаграмі.
Далі виділіть комірку B15, а потім перейдіть до “Формули” > “Інші функції” > “Статистичні” > “ПЕРЕРІЗ”.
З’явиться вікно аргументів функції. Виберіть або введіть комірки стовпця Y-Value для поля “Відомі_значення_y”.
Виберіть або введіть комірки стовпця X-Value для поля “Відомі_значення_x”. Порядок полів “Відомі_значення_y” та “Відомі_значення_x” важливий для функції INTERCEPT.
Натисніть “OK”. Остаточна формула в рядку формул має виглядати так:
=ПЕРЕРІЗ(C3:C12,B3:B12)
Значення, повернене функцією INTERCEPT, відповідає перетину Y, відображеному на діаграмі.
Далі виберіть комірку C15 та перейдіть до “Формули” > “Інші функції” > “Статистичні” > “CORREL”.
З’явиться вікно аргументів функції. Виберіть або введіть будь-який з двох діапазонів комірок для поля “Масив1”. На відміну від SLOPE та INTERCEPT, порядок не впливає на результат функції CORREL.
Виберіть або введіть інший з двох діапазонів комірок для поля “Масив2”.
Натисніть “OK”. Формула в рядку формул має виглядати так:
=CORREL(B3:B12,C3:C12)
Значення, яке повертає функція CORREL, не відповідає значенню “r-квадрат” на діаграмі. Функція CORREL повертає “R”, тому ми повинні звести його в квадрат, щоб обчислити “R-квадрат”.
Клацніть всередині панелі функцій та додайте “^2” в кінці формули, щоб звести значення, повернене функцією CORREL, в квадрат. Повна формула тепер має виглядати так:
=CORREL(B3:B12,C3:C12)^2
Натисніть Enter.
Після зміни формули значення “R-квадрат” тепер відповідає тому, що відображається на діаграмі.
Крок 3: Налаштування формул для швидкого обчислення значень
Тепер ми можемо використовувати ці значення в простих формулах для визначення концентрації “невідомого” розчину або вхідних даних для коду, щоб мармур пролетів на певну відстань.
Ці кроки створять формули, необхідні для того, щоб ви могли ввести значення X або Y та отримати відповідне значення на основі калібрувальної кривої.
Рівняння лінії найкращої відповідності має вигляд “Y-значення = НАХИЛ * X-значення + ПЕРЕРІЗ”. Для розрахунку “значення Y” необхідно помножити X на НАХИЛ, а потім додати ПЕРЕРІЗ.
Для прикладу, введемо нуль як значення X. Повернуте значення Y має дорівнювати ПЕРЕРІЗУ лінії найкращої відповідності. Значення співпадають, отже формула працює правильно.
Для розрахунку значення X на основі значення Y необхідно відняти ПЕРЕРІЗ від Y та розділити результат на НАХИЛ:
X-значення = (Y-значення - ПЕРЕРІЗ) / НАХИЛ
Для прикладу, використаємо ПЕРЕРІЗ як значення Y. Повернуте значення X має дорівнювати нулю, але отримаємо 3.14934E-06. Значення не дорівнює точно нулю, оскільки ми випадково скоротили результат ПЕРЕРІЗ при введенні. Однак формула працює правильно, оскільки результат наближається до нуля.
Ви можете ввести будь-яке значення X в першу комірку з товстою рамкою, і Excel автоматично обчислить відповідне значення Y.
Введення будь-якого значення Y в другу комірку з товстою рамкою дасть відповідне значення X. Цю формулу ви б використали для розрахунку концентрації розчину або вхідних даних, необхідних для запуску мармуру на певну відстань.
У цьому випадку, прилад показує “5”, тоді калібрування передбачає концентрацію 4.94, або ми хочемо, щоб мармур пройшов п’ять одиниць відстані, отже калібрування пропонує нам ввести 4.94 як вхідну змінну для програми, що керує мармуровою пусковою установкою. Ми можемо бути впевнені в цих результатах, через високе значення R-квадрат у цьому прикладі.