В Excel есть встроенные функции, которые вы можете использовать для отображения ваших данных калибровки и расчета линии наилучшего соответствия. Это может быть полезно, когда вы пишете отчет химической лаборатории или программируете поправочный коэффициент на единицу оборудования.
В этой статье мы рассмотрим, как использовать Excel для создания диаграммы, построить линейную калибровочную кривую, отобразить формулу калибровочной кривой, а затем настроить простые формулы с помощью функций НАКЛОН и ПЕРЕКЛЮЧИТЬ, чтобы использовать уравнение калибровки в Excel.
Что такое калибровочная кривая и как Excel полезен при ее создании?
Чтобы выполнить калибровку, вы сравниваете показания устройства (например, температуру, отображаемую термометром) с известными значениями, называемыми стандартами (например, точки замерзания и кипения воды). Это позволяет вам создать серию пар данных, которые вы затем будете использовать для построения калибровочной кривой.
Двухточечная калибровка термометра с использованием точек замерзания и кипения воды будет иметь две пары данных: одну с момента, когда термометр находится в ледяной воде (32 ° F или 0 ° C) и один в кипящей воде (212 ° F или 100 ° C). Когда вы построите эти две пары данных в виде точек и проведете линию между ними (калибровочную кривую), а затем, предполагая, что реакция термометра является линейной, вы можете выбрать любую точку на линии, которая соответствует значению, которое отображает термометр, и вы мог найти соответствующую «истинную» температуру.
Таким образом, линия, по сути, заполняет информацию между двумя известными для вас точками, так что вы можете быть достаточно уверенными при оценке фактической температуры, когда термометр показывает 57,2 градуса, но когда вы никогда не измеряли «стандарт», который соответствует это чтение.
В Excel есть функции, которые позволяют графически отображать пары данных на графике, добавлять линию тренда (калибровочную кривую) и отображать уравнение калибровочной кривой на графике. Это полезно для визуального отображения, но вы также можете вычислить формулу линии, используя функции SLOPE и INTERCEPT в Excel. Когда вы введете эти значения в простые формулы, вы сможете автоматически рассчитать «истинное» значение на основе любого измерения.
Давайте посмотрим на пример
Для этого примера мы разработаем калибровочную кривую из серии из десяти пар данных, каждая из которых состоит из значения X и значения Y. Значения Х будут нашими «стандартами», и они могут представлять что угодно, от концентрации химического раствора, который мы измеряем с помощью научного прибора, до входной переменной программы, которая управляет пусковой машиной для мрамора.
Значения Y будут «откликами», и они будут представлять собой показания прибора, полученные при измерении каждого химического раствора, или измеренное расстояние, на котором расстояние от пусковой установки, на которую упал мрамор, используя каждое входное значение.
После того, как мы графически изобразим калибровочную кривую, мы будем использовать функции SLOPE и INTERCEPT, чтобы вычислить формулу калибровочной линии и определить концентрацию «неизвестного» химического раствора на основе показаний прибора или решить, какой ввод мы должны дать программе, чтобы мрамор приземляется на определенном расстоянии от пусковой установки.
Шаг первый: создайте свою диаграмму
Наш простой пример электронной таблицы состоит из двух столбцов: X-Value и Y-Value.
Начнем с выбора данных для построения графика.
Сначала выберите ячейки столбца «X-значение».
Теперь нажмите клавишу Ctrl и затем щелкните ячейки столбца Y-значения.
Перейдите на вкладку «Вставить».
Перейдите в меню «Графики» и выберите первый вариант в раскрывающемся меню «Разброс».
scatter “width =” 314 “height =” 250 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “onerror =” this.onerror = null; pagespeed.lazyLoad. loadIfVisibleAndMaybeBeacon (это); “/>
Появится диаграмма, содержащая точки данных из двух столбцов.
Выберите серию, нажав на одну из синих точек. После выбора Excel обрисовывает в общих чертах точки.
Щелкните правой кнопкой мыши одну из точек и выберите опцию «Добавить линию тренда».
На графике появится прямая линия.
В правой части экрана появится меню «Format Trendline». Установите флажки рядом с «Показать уравнение на графике» и «Показать значение R-квадрата на графике».«Значение R-квадрата – это статистика, показывающая, насколько точно линия соответствует данным. Наилучшее значение R-квадрата равно 1.000, что означает, что каждая точка данных касается линии. По мере роста различий между точками данных и линией значение r-квадрата уменьшается, причем 0,000 является наименьшим возможным значением.
Уравнение и R-квадрат статистики трендовой линии появятся на графике. Обратите внимание, что в нашем примере корреляция данных очень хорошая, значение R-квадрата равно 0,988.
Уравнение имеет вид «Y = Mx + B», где M – наклон, а B – пересечение оси y прямой.
Теперь, когда калибровка завершена, давайте приступим к настройке диаграммы, отредактировав заголовок и добавив заголовки осей.
Чтобы изменить заголовок диаграммы, щелкните по нему, чтобы выделить текст.
Теперь введите новый заголовок, который описывает диаграмму.
Чтобы добавить заголовки к осям X и Y, сначала перейдите к «Инструменты диаграммы»> «Дизайн».
design “width =” 650 “height =” 225 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “onerror =” this.onerror = null; pagespeed.lazyLoadImages. loadIfVisibleAndMaybeBeacon (это); “/>
Нажмите «Добавить элемент диаграммы».
Теперь перейдите к Названия осей> Первичная горизонтальная.
основная горизонтальная “ширина =” 650 “высота =” 500 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “onerror =” this.onerror = null; pagespeed.lazyLoadImages .loadIfVisibleAndMaybeBeacon (это); “/>
Появится название оси.
Чтобы переименовать заголовок оси, сначала выделите текст, а затем введите новый заголовок.
Теперь перейдите к Названию осей> Первичная вертикаль.
Появится название оси.
Переименуйте этот заголовок, выделив текст и введя новый заголовок.
Ваша диаграмма теперь завершена.
Шаг второй: Рассчитать линейное уравнение и R-квадрат
Теперь давайте вычислим линейное уравнение и R-квадрат, используя встроенные в Excel функции SLOPE, INTERCEPT и CORREL.
К нашему листу (в строке 14) мы добавили заголовки для этих трех функций. Мы выполним фактические вычисления в ячейках под этими заголовками.
Сначала рассчитаем НАКЛОН. Выберите ячейку A15.
Перейдите к формулам> Дополнительные функции> Статистические> НАКЛОН.
Дополнительные функции> Статистические> НАКЛОН »width =” 650 “height =” 435 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “onerror =” this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (это); “/>
Откроется окно «Аргументы функции». В поле «Known_ys» выберите или введите ячейки столбца Y-значения.
В поле «Known_xs» выберите или введите ячейки столбца X-Value. Порядок полей «Known_ys» и «Known_xs» имеет значение в функции SLOPE.
Нажмите «ОК». Окончательная формула в строке формул должна выглядеть следующим образом:
= СКЛОН (С3: С12, В3: В12)
Обратите внимание, что значение, возвращаемое функцией SLOPE в ячейке A15, соответствует значению, отображенному на графике.
Затем выберите ячейку B15 и перейдите к «Формулы»> «Дополнительные функции»> «Статистические данные»> «ПЕРЕКРЫТЬ».
Дополнительные функции> Статистические> INTERCEPT “width =” 650 “height =” 435 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “onerror =” this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (это); “/>
Откроется окно «Аргументы функции». Выберите или введите в ячейки столбца Y-значение для поля «Known_ys».
Выберите или введите в ячейки столбца X-Value поле «Known_xs». Порядок полей «Known_ys» и «Known_xs» также имеет значение в функции INTERCEPT.
Нажмите «ОК». Окончательная формула в строке формул должна выглядеть следующим образом:
= ОТРЕЗОК (С3: С12, В3: В12)
Обратите внимание, что значение, возвращаемое функцией INTERCEPT, соответствует точке пересечения y, отображаемой на диаграмме.
Затем выберите ячейку C15 и перейдите к «Формулы»> «Дополнительные функции»> «Статистические данные»> «CORREL».
Дополнительные функции> Статистические> CORREL “width =” 650 “height =” 435 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “onerror =” this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (это); “/>
Откроется окно «Аргументы функции». Выберите или введите любой из двух диапазонов ячеек для поля «Массив1». В отличие от SLOPE и INTERCEPT, порядок не влияет на результат функции CORREL.
Выберите или введите другой из двух диапазонов ячеек для поля «Array2».
Нажмите «ОК». Формула должна выглядеть следующим образом на панели формул:
= КОРРЕЛ (В3: В12, С3: С12)
Обратите внимание, что значение, возвращаемое функцией CORREL, не соответствует значению «r-квадрат» на графике. Функция CORREL возвращает «R», поэтому мы должны возвести ее в квадрат, чтобы вычислить «R-квадрат».
Щелкните внутри панели функций и добавьте «^ 2» в конец формулы, чтобы возвести в квадрат значение, возвращаемое функцией CORREL. Заполненная формула теперь должна выглядеть так:
= КОРРЕЛ (В3: В12, С3: С12) ^ 2
Нажмите Ввод.
После изменения формулы значение «R-квадрат» теперь соответствует значению, отображенному на графике.
Шаг третий: настройка формул для быстрого расчета значений
Теперь мы можем использовать эти значения в простых формулах, чтобы определить концентрацию этого «неизвестного» раствора или то, что мы должны ввести в код, чтобы шарик пролетел определенное расстояние.
Эти шаги настроят формулы, необходимые для того, чтобы вы могли ввести значение X или значение Y и получить соответствующее значение на основе калибровочной кривой.
Уравнение линии наилучшего соответствия имеет вид «Y-значение = НАКЛОН * X-значение + INTERCEPT», поэтому решение для «Y-значения» выполняется путем умножения значения X и SLOPE, а затем добавив ИНТЕРЦЕПТ.
В качестве примера мы вводим ноль в качестве значения X. Возвращаемое значение Y должно быть равно ПЕРЕКЛЮЧЕНИЮ линии наилучшего соответствия. Это соответствует, поэтому мы знаем, что формула работает правильно.
Решение для значения X на основе значения Y выполняется путем вычитания INTERCEPT из значения Y и деления результата на НАКЛОН:
X-значение = (Y-значение-ОТРЕЗОК)/СКЛОН
В качестве примера мы использовали INTERCEPT в качестве значения Y. Возвращаемое значение Х должно быть равно нулю, но возвращаемое значение равно 3.14934E-06. Возвращаемое значение не равно нулю, потому что мы непреднамеренно обрезали результат INTERCEPT при вводе значения. Однако формула работает правильно, потому что результат формулы равен 0,00000314934, что по существу равно нулю.
Вы можете ввести любое X-значение в первую ячейку с толстыми границами, и Excel автоматически вычислит соответствующее значение Y.
Ввод любого значения Y во вторую ячейку с толстой рамкой даст соответствующее значение X. Эта формула используется для расчета концентрации этого раствора или того, что необходимо для запуска мрамора на определенном расстоянии.
В этом случае прибор показывает «5», поэтому при калибровке будет предложена концентрация 4,94, или мы хотим, чтобы шарик прошел пять единиц расстояния, поэтому при калибровке предлагается ввести 4,94 в качестве входной переменной для программы, управляющей пусковой установкой мрамора. Мы можем быть достаточно уверены в этих результатах из-за высокого значения R-квадрата в этом примере.
