Построить градуировочный график в excel. Градуировочные графики в Excel

Как сделать линейную калибровочную кривую в Excel

В Excel есть интегрированные функции, которые вы сможете применять для отображения ваших данных калибровки и расчета полосы наилучшего соответствия. Это может быть полезно, когда вы пишете отчет хим лаборатории либо программируете поправочный коэффициент на единицу оборудования.

В данной нам статье мы разглядим, как применять Excel для сотворения диаграммы, выстроить линейную калибровочную кривую, показать формулу калибровочной кривой, а потом настроить обыкновенные формулы с помощью функций НАКЛОН и ПЕРЕКЛЮЧИТЬ, чтоб употреблять уравнение калибровки в Excel.

Что такое калибровочная кривая и как Excel полезен при ее создании?

Чтоб выполнить калибровку, вы сравниваете показания устройства (например, температуру, отображаемую термометром) с известными значениями, именуемыми эталонами (например, точки замерзания и кипения воды). Это дозволяет для вас сделать серию пар данных, которые вы потом будете применять для построения калибровочной кривой.

Двухточечная калибровка указателя температуры с внедрением точек замерзания и кипения воды будет иметь две пары данных: одну с момента, когда указатель температуры находится в ледяной воде (32 ° F либо 0 ° C) и один в кипящей воде (212 ° F либо 100 ° C). Когда вы построите эти две пары данных в виде точек и проведете линию меж ними (калибровочную кривую), а потом, предполагая, что реакция указателя температуры является линейной, вы сможете выбрать всякую точку на полосы, которая соответствует значению, которое показывает указатель температуры, и вы мог отыскать подобающую «истинную» температуру.

Таковым образом, линия, по сущности, заполняет информацию меж 2-мя известными для вас точками, так что вы сможете быть довольно уверенными при оценке фактической температуры, когда указатель температуры указывает 57,2 градуса, но когда вы никогда не измеряли «стандарт», который соответствует это чтение.

В Excel есть функции, которые разрешают графически показывать пары данных на графике, добавлять линию тренда (калибровочную кривую) и показывать уравнение калибровочной кривой на графике. Это полезно для зрительного отображения, но вы также сможете вычислить формулу полосы, используя функции SLOPE и INTERCEPT в Excel. Когда вы введете эти значения в обыкновенные формулы, вы можете автоматом рассчитать «истинное» значение на базе хоть какого измерения.

Давайте поглядим на пример

Для этого примера мы разработаем калибровочную кривую из серии из 10 пар данных, любая из которых состоит из значения X и значения Y. Значения Х будут нашими «стандартами», и они могут представлять что угодно, от концентрации хим раствора, который мы измеряем с помощью научного устройства, до входной переменной программы, которая заведует пусковой машинкой для мрамора.

Значения Y будут «откликами», и они будут представлять собой показания устройства, приобретенные при измерении каждого хим раствора, либо измеренное расстояние, на котором расстояние от пусковой установки, на которую свалился мрамор, используя каждое входное значение.

Опосля того, как мы графически изобразим калибровочную кривую, мы будем применять функции SLOPE и INTERCEPT, чтоб вычислить формулу калибровочной полосы и найти концентрацию «неизвестного» хим раствора на базе показаний устройства либо решить, какой ввод мы должны отдать програмке, чтоб мрамор приземляется на определенном расстоянии от пусковой установки.

Шаг первый: сделайте свою диаграмму

Наш обычный пример электронной таблицы состоит из 2-ух столбцов: X-Value и Y-Value.

Начнем с выбора данных для построения графика.

Поначалу выберите ячейки столбца «X-значение».

Сейчас нажмите кнопку Ctrl и потом щелкните ячейки столбца Y-значения.

Перейдите на вкладку «Вставить».

Перейдите в меню «Графики» и выберите 1-ый вариант в раскрывающемся меню «Разброс».

scatter «width =» 314 «height =» 250 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null; pagespeed.lazyLoad. loadIfVisibleAndMaybeBeacon (это); «/>

Покажется диаграмма, содержащая точки данных из 2-ух столбцов.

Выберите серию, нажав на одну из голубых точек. Опосля выбора Excel обрисовывает в общих чертах точки.

Щелкните правой клавишей мыши одну из точек и выберите опцию «Добавить линию тренда».

На графике покажется ровная линия.

В правой части экрана покажется меню «Format Trendline». Установите флажки рядом с «Показать уравнение на графике» и «Показать значение R-квадрата на графике».«Значение R-квадрата — это статистика, показывающая, как точно линия соответствует данным. Наилучшее значение R-квадрата равно 1.000, что значит, что любая точка данных касается полосы. По мере роста различий меж точками данных и линией значение r-квадрата миниатюризируется, при этом 0,000 является минимальным вероятным значением.

Читайте также  Как подключить iptv к телевизору philips. Настройка IPTV на телевизорах Philips SmartTV

Уравнение и 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) мы добавили заглавия для этих 3-х функций. Мы выполним фактические вычисления в ячейках под этими заголовками.

Поначалу рассчитаем НАКЛОН. Выберите ячейку 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.

Нажмите «ОК». Окончательная формула в строке формул обязана смотреться последующим образом:

Обратите внимание, что значение, возвращаемое функцией 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.

Нажмите «ОК». Окончательная формула в строке формул обязана смотреться последующим образом:

Обратите внимание, что значение, возвращаемое функцией INTERCEPT, соответствует точке пересечения y, отображаемой на диаграмме.

Потом выберите ячейку C15 и перейдите к «Формулы»> «Дополнительные функции»> «Статистические данные»> «CORREL».

Доп функции> Статистические> CORREL «width =» 650 «height =» 435 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (это); «/>

Раскроется окно «Аргументы функции». Выберите либо введите хоть какой из 2-ух диапазонов ячеек для поля «Массив1». В отличие от SLOPE и INTERCEPT, порядок не влияет на итог функции CORREL.

Выберите либо введите иной из 2-ух диапазонов ячеек для поля «Array2».

Нажмите «ОК». Формула обязана смотреться последующим образом на панели формул:

Обратите внимание, что значение, возвращаемое функцией CORREL, не соответствует значению «r-квадрат» на графике. Функция CORREL возвращает «R», потому мы должны возвести ее в квадрат, чтоб вычислить «R-квадрат».

Щелкните снутри панели функций и добавьте «^ 2» в конец формулы, чтоб возвести в квадрат значение, возвращаемое функцией CORREL. Заполненная формула сейчас обязана смотреться так:

Нажмите Ввод.

Опосля конфигурации формулы значение «R-квадрат» сейчас соответствует значению, отображенному на графике.

Шаг третий: настройка формул для скорого расчета значений

Сейчас мы можем применять эти значения в обычных формулах, чтоб найти концентрацию этого «неизвестного» раствора либо то, что мы должны ввести в код, чтоб шарик пролетел определенное расстояние.

Эти шаги настроят формулы, нужные для того, чтоб вы могли ввести значение X либо значение Y и получить соответственное значение на базе калибровочной кривой.

Читайте также  Можно смотреть на ps2 фильмы с флешки на. PS-2 как видеоплеер AVI, MPEG-2, MKV, FLV

Уравнение полосы наилучшего соответствия имеет вид «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, либо мы желаем, чтоб шарик прошел 5 единиц расстояния, потому при калибровке предлагается ввести 4,94 в качестве входной переменной для программы, управляющей пусковой установкой мрамора. Мы можем быть довольно убеждены в этих результатах из-за высочайшего значения R-квадрата в этом примере.

Градуировочные графики в Excel

Фотометрический анализ при контроле гальванических электролитов применяется для определения малых концентраций добавок и примесей, титриметрический анализ которых затруднён либо нецелесообразен.

Распространёнными фотометрическими измерениями являются определения: • солей хрома III в электролите хромирования и растворе хромитирования,

• общего кобальта и никеля в электролитах твёрдого золочения, серебрения и нанесения сплава «Никель-Кобальт»,

• висмута в электролите нанесения сплава «Олово-Висмут»,

• определение палладия в активаторе для металлизации диэлектриков,

• определение примесей меди, железа и никеля в различных электролитах,

• а также турбидиметрическое определение сульфатов в электролите хромирования и в сульфаматных электролитах для определения степени гидролиза сульфаматов и пр. Физико-химические способы анализа основаны на взаимосвязи меж составом системы и ее физическими и физико-химическими качествами. Многофункциональная зависимость меж численным значением данного физического либо физико-химического характеристики и содержанием анализируемого вещества может быть выражена графиком либо формулой.

Основой для построения градуировочного графика является приготовление обычных растворов.

Обычные растворы нужно готовить из аттестованных муниципальных образцов (ГСО). Ежели таких не имеется, нужно верно придерживаться главных требований к точности приготовления обычных растворов:

• следует использовать соединения квалификации не ниже ч.д.а;

• внедрение реактивов с просроченным сроком годности недопустимо;

• для приготовления обычных растворов применять лишь гостированную мерную посуду.

Особенное внимание направить на условия и сроки хранения растворов с содержанием вещества 1 мг/см3 хранят 1 год, 0,1 мг/см3 — 3 месяца (если нет остальных указаний, а также ежели нет помутнения, хлопьев, осадка), растворы с меньшим содержанием используются свежеприготовленными. Обычные растворы фильтровать не допускается.

Для определения содержания вещества способом градуировочного графика готовят 3 серии шкал стандартов. Указания по приготовлению шкалы стандартов непосредственно оговорены в каждой методике.

При приготовлении параллельных шкал рабочие растворы готовят подходящим разбавлением основного обычного раствора, который готовится из одной взятой навески либо ГСО.

Градуировочный график строят на миллиметровой бумаге либо в электронной форме при помощи Excel, откладывая на оси абсцисс, указанную в методике определения концентрацию, а по оси ординат — измеренные значения оптической плотности.

Количественное значение оптической плотности для каждой точки градуировочного графика определяется как среднее арифметическое результатов параллельных измерений 3-х шкал.

С целью уменьшения погрешности графического измерения, нужно подобрать таковой масштаб графика, чтоб угол его наклона приблизительно приравнивался 45º.

Градуировочный график должен нести последующую информацию:

• заглавие определения;

• НД на метод проведения исследования;

• способ определения;

• марка КФК, его заводской номер;

• длина волны;

• длина рабочей грани кюветы;

• раствор сопоставления (растворитель, нулевой раствор и т.д.), т.е. относительно чего же снимались показания испытуемого раствора;

• дата построения;

• даты поверки;

• на графике должны находиться данные 3-х параллельных измерений и среднее арифметическое значение (в виде таблицы).

Градуировочный график строится один раз в год и после ремонта устройства. Поверка графика обязана проводиться 1 раз в квартал (если нет остальных указаний в методике определения), а также опосля приготовления реактивов из новой партии, поверки устройства. Поверка проводится по 3-м точкам графика более нередко встречающихся в работе концентраций. Данные поверки заносятся в журнал поверок градуировочных графиков либо наносятся на обратную сторону графика в виде таблицы.

Читайте также  Как в ворде сделать в фигуре надпись. Как в файле ворд вставить произвольный текст в фигуру

При построении градуировочного графика обязана соблюдаться ровная зависимость меж оптической плотностью и концентрацией. Прямолинейность графика сохраняется лишь в интервале концентраций, указанных в методике. Потому продлевать градуировочную прямую выше крайней указанной в методике точки не допустимо. Отыскивать значение концентраций испытуемого раствора по градуировочному графику ниже первой точки графика не рекомендуется, т.к. это определение несет огромную погрешность. В таком случае итог исследования следует записывать в виде «менее … мг/дм3».

Невзирая на простоту и удобство, практическое внедрение градуировочных графиков в ряде случаев вносит доп погрешность при определении концентрации растворов как за счет субъективного построения графической зависимости, так и за счет несоответствия графичеких (масштабных) погрешностей и погрешностей измерения оптических плотностей. Потому для получения наиболее объективных результатов в фотометрическом анализе целенаправлено построение градуировочного графика способом регрессивного анализа (уравнение регрессии).

Описание способа меньших квадратов и пример построение уточнённого градуировочного графика на примере фотометрического определения железа — тут.

Дополнение для устройств, имеющих стрелочное регистрирующее устройство:

На многих предприятиях до сих пор в эксплуатации находятся приборы, при работе на которых значение измеренного пропускания отсчитывают по стрелочному устройству. Эти устройства являются обыденным источником погрешности конкретно при отсчитывании показаний. Для верно сконструированного стрелочного устройства погрешность при отсчёте будет постоянной и, может быть, равной толщине стрелки, которая соответствует определённому отсчёту по шкале устройства. При допущении, что отсчёты пропускания, приобретенные каждым таковым устройством, имеют постоянную недостоверность (обусловленную конфигурацией мощности источника света, чертами сенсора, электрическими шумами, положением кюветы, и обычно приводящую к суммарной погрешности от 0,2 до 1%), можно рассчитать погрешность, которую вызовет ненадёжность при регистрации концентрации определяемых частиц. Для расчёта концентрационной погрешности нужно изучить влияние недостоверности измерения пропускания на вычисленное значение концентрации. Для этого запишем закон Бера в форме, которая указывает, что зависимость от концентрации является экспоненциальной: T = P/P0 = 10-abC, где (1)

b— толщина всасывающего слоя раствора в кювете,

С — концентрация оптически активного вещества,

а — удельная поглощательная способность, зависящая от природы вещества.

Из графика данной для нас зависимости на рисунке можно найти, какое влияние на рассчитанное значение С окажет неизменная погрешность в пропускании.

Поначалу разглядим спектрофотометрическое измерение раствора с низкой концентрацией (С1) определяемых частиц. Согласно рисунку этот раствор будет иметь высочайшее значение пропускание (Т1). Допуская, что недостоверность при отсчёте стрелочного устройства соответствует неизменной абсолютной погрешности в Т, можно найти из рисунка результирующую погрешность в определение С. Из экстраполирования полосы видно, что абсолютная погрешность в концентрации (ΔС1) мала, когда раствор имеет высочайшее пропускание. Но так как сама концентрация (С1) мала, относительная погрешность в концентрации (ΔС1/С1) достаточно велика.

Сейчас разглядим вариант, когда имеется высочайшая концентрация (С2) определяемого вещества. Этот раствор будет иметь относительно низкое значение пропускания (Т2). Ежели опять допустить наличие неизменной погрешности в Т, то получим огромную абсолютную погрешность (ΔС2) в концентрации, и хотя концентрация велика, относительная погрешность (ΔС2/С2) также будет велика.

Эти результаты свидетельствуют, что кое-где меж экстремальными значениями концентрации в пробах, имеющих высочайшие и низкие пропускания, обязано существовать значение пропускания, для которого относительная погрешность в концентрации (ΔС/С) мала. В действительности относительная погрешность мала при 36,8%-ном пропускании. К этому выводу можно придти и расчётным путём, вспомнив определение понятия поглощения:

А = lg (1/T) = 1/2,303 ln(1/T) = 0,434 ln (1/T) (2)

Ежели это уравнение продифференцировать относительно Т, то получим

dA/dT = −0,434/T либо dA = (-0,434/T) dT (3)

Для того чтоб оценить относительную погрешность в значении поглощения и тем самым в концентрации, нужно поделить каждую часть уравнения (3) на поглощение (А). Тогда

dA/А = (-0,434/TА) dT= (0,434/TlgT) dT (4)

Чтоб отыскать относительную погрешность в концентрации, заменим в этом уравнении А соответственной величиной согласно закону Ламберта-Бера (1):

d(abC)/abC = (ab)dC/abC = dC/C = (0,434/T lgT) dT (5)

Уравнение (5) указывает, что относительная погрешность в концентрации (dC/C) конкретно зависит от абсолютной недостоверности в пропускании (dT), а также от обратной величины произведения (TlgT), включающего значение самого пропускания. Графическая зависимость этого уравнения, показывающая относительную погрешность в концентрации (dC/C) как функцию пропускания (в %) при неизменной погрешности в отсчёте (dT), равной 1%, показана на рисунке:

Из рисунка видно, что относительная погрешность в концентрации довольно мала при пропусканиях меж 20 и 70%, но при переходе к очень низким либо высочайшим значениям пропускания погрешность резко увеличивается. Можно отыскать минимальную относительную погрешность в концентрации путём дифференцирования уравнения (5) и приравнивая выражения к нулю. Как указано ранее, малая погрешность имеет месть при 36,8%-ном пропускании либо при поглощении, равном 0,434. Потому для спектрофотометров, имеющих стрелочное регистрирующее устройство, можно проводить измерение с наименьшей погрешностью, ежели отсчёты по шкале ограничены пределом от 20% до 70% пропускания (или от 0,7 до 0,2 единиц поглощения), а в оптимальном случае эти расчёты приближаются к значениям 36,8% пропускания либо к 0,434 единиц поглощения.

Оставьте комментарий