Усовершенствованная версия градуировочного графика
(описание используемых условных обозначений, а также список других публикаций канала по теме электронных таблиц, находится здесь).
Данную заметку можно рассматривать как продолжение пункта «Построение градуировочных графиков» (Пособие, с. 57). Описанное там можно дополнительно усовершенствовать, причём потребность в этом может быть связана со следующей ситуацией, возникающей в лабораторной практике.
Градуировочная зависимость имеет довольно ограниченный «срок годности» и её регулярно нужно переснимать. На моей памяти никогда не было такого, чтобы при этом все заново измеренные значения аналитического сигнала у стандартных образцов в точности совпали со всеми предыдущими. Это приводит к тому, что новая градуировочная линия отличается от старой – либо слегка меняется угол её наклона, либо вся линия смещается чуть выше или ниже. Поэтому чтобы правильно выполнить расчёт концентрации определяемого вещества в исследуемом образце, нужно в ячейках “G5” и “G6” листа указать обновлённые значения коэффициентов a и b уравнения прямой. При замене данных в ячейках со значениями концентраций определяемого вещества x и соответствующих им величин аналитического сигнала y, табличный процессор отобразит на графике пересчитанные коэффициенты(обновлённый вид уравнения линии) – их-то и надо будет не забыть заново вписать в указанные выше ячейки.
Если же при этом учесть влияние человеческого фактора, то не исключена возможность, что после ввода значений x – y новой градуировки ручная замена коэффициентов можно быть запамятована, в итоге получится неправильное значение концентрации, поскольку она окажется рассчитанной фактически по старой калибровке.
Чтобы этого избежать, удобнее и надёжнее сделать так, чтобы коэффициенты a и b в “G5” и “G6” вычислялись сами. Вот именно этот расчёт и предлагается реализовать при «апгрейде» градуировочного графика.
Уравнение прямой линии табличный процессор вычисляет с использованием метода наименьших квадратов (МНК), поэтому сначала придётся позанудствовать и дать краткое описание этого метода.
Итак, пусть у нас есть n стандартных образцов с концентрациями определяемого компонента x₁, x₂, … , xₙ, а измеренные соответствующие значения аналитического сигнала равны y₁, y₂, … , yₙ . Если зависимость y=f(x) носит линейный характер, то оптимальной аппроксимирующей линией будет такая, коэффициенты которой являются решением следующей системы уравнений (под оптимальной в МНК понимается линия, для которой минимальна сумма квадратов всех отклонений каждого измеренного значения yₖ от «теоретического» значения axₖ + b):
Решение системы «на лету» удобно выполнять с использованием метода Крамера, вскользь упоминавшегося в пункте «Реализация метода Монте-Карло» (Пособие, с. 85). Рассмотрим систему из двух линейных уравнений с двумя неизвестными a и b:
Сначала вычисляется так называемый основной определитель системы Δ (он состоит из коэффициентов, стоящих перед неизвестными переменными в левых частях уравнений):
затем – дополнительные определители:
(они составляются аналогично Δ, только в них коэффициенты перед соответствующей неизвестной переменной заменяются числом из правой части уравнения). После этого можно вычислить сами a и b:
Допустим у нас имеются такие значения концентраций x и аналитического сигнала y:
Прежде чем, начать решать систему уравнений, нужно посчитать значения коэффициентов в них, а для этого необходимо знать суммы по xₖ , yₖ , xₖ·yₖ , xₖ² , а также n – общее количество пар значений x – y. Сделать это можно таким образом:
В диапазонах “I3:I17” и “J3:J17” вычисляются значения xₖ² и xₖ·yₖ по формулам (показано на примере ячеек “I3” и “J3” соответственно):
=C3^2
=C3*D3
В ячейке “G19” подсчитывается n по формуле:
=СЧЁТ(C3:C17)
=COUNT(C3:C17)
При помощи функции
=СУММ( )
=SUM( )
в ячейках “C19”, “D19”, “I19” и “J19” вычисляются необходимые суммы по диапазонам “C3:C17” (xₖ), “D3:D17” (yₖ), “I3:I17” (xₖ²) и “J3:J17” (xₖ·yₖ) соответственно.
Теперь можно переходить к вычислению основного и дополнительных определителей системы. Для этого можно разместить элементы каждого из определителей в отдельном блоке ячеек, введя в них формулы, которые просто дублируют уже вычисленные перед этим значения сумм (а также n):
Следующий шаг – нахождение значений определителей. Для этого сперва в ячейку “C21” нужно ввести формулу:
=МОПРЕД(B22:C23)
=MDETERM(B22:C23)
Использованная в ней функция вычисляет величину определителя, который указан в качестве её аргумента, представляющего собой квадратный массив (диапазон) ячеек.
Чтобы посчитать два остальных определителя, достаточно скопировать формулу из “C21” в “C25” и “C29”. Последний этап – собственно нахождение коэффициентов a и b уравнения линии выполняется вводом формул в ячейки “G5” (a) и “G6” (b) соответственно:
=C25/C21
=C29/C21
Хотелось бы напомнить, что при наличии в ячейке “G10” формулы вида
=(G9-G6)/G5
будет выполняться расчёт концентрации вещества в исследуемом образце на основании значения аналитического сигнала и градуировки. В связи с этим выходит, что в получившейся программе ячейки листа, в которые должны вводится эмпирические данные – это “G10” и “C3:D17”, остальные же ячейки (прежде всего те, которые содержат формулы) трогать не следует, поэтому для большей надёжности при желании можно установить защиту листа (Пособие, с. 55).
В учебных целях приведённые расчёты выполнены достаточно развёрнуто для большей наглядности и понятности последовательности действий при вычислениях. Разумеется, их можно сократить, действуя по одному из следующих вариантов.
В первом варианте в ячейке “I19” можно указать формулу вида:
=СУММКВ(C3:C17)
=SUMSQ(C3:C17)
Использованная в ней функция вычисляет сумму квадратов значений указанного в качестве аргумента диапазона ячеек, в данном случае – сумму каждого xₖ², в связи с чем отпадает необходимость сначала вычислять каждое значение по отдельности в диапазоне “I3:I17”, а затем складывать их. Далее, в ячейке “J19” следует разместить вот такую формулу:
=СУММПРОИЗВ(C3:C17;D3:D17)
=SUMPRODUCT(C3:C17;D3:D17)
Функция в ней может содержать несколько аргументов (в данном случае – два), каждый из которых представляет собой диапазон ячеек. Работает она так: перемножает числа из соответствующих ячеек разных диапазонов (здесь – значения xₖ с соответствующими им значениями yₖ) и складывает их между собой. Таким образом, приведённая формула считает сумму каждого xₖ·yₖ, избавляя от потребности делать эти вычисления раздельно в ячейках “J3:J17”.
Второй вариант сокращения значительно более «экстремальный», поскольку в нём все вычисления сводятся всего к двум формулам. Из МНК и метода Крамера следует, что коэффициенты уравнения аппроксимирующей линии выражаются так:
Эти равенства, записанные в нотации табличного процессора, можно поместить в ячейках“G5” (коэффициент a):
=(СЧЁТ(C3:C17)*СУММПРОИЗВ(C3:C17;D3:D17)-СУММ(C3:C17)*СУММ(D3:D17))/(СЧЁТ(C3:C17)*СУММКВ(C3:C17)-СУММ(C3:C17)^2)
=(COUNT(C3:C17)*SUMPRODUCT(C3:C17;D3:D17)-SUM(C3:C17)*SUM(D3:D17))/(COUNT(C3:C17)*SUMSQ(C3:C17)-SUM(C3:C17)^2)
и “G6” (коэффициент b):
=(СУММКВ(C3:C17)*СУММ(D3:D17)-СУММ(C3:C17)*СУММПРОИЗВ(C3:C17;D3:D17))/(СЧЁТ(C3:C17)*СУММКВ(C3:C17)-СУММ(C3:C17)^2)
=(SUMSQ(C3:C17)*SUM(D3:D17)-SUM(C3:C17)*SUMPRODUCT(C3:C17;D3:D17))/(COUNT(C3:C17)*SUMSQ(C3:C17)-SUM(C3:C17)^2)
Как видно, все необходимые расчёты размещены весьма компактно и не «размазаны» по листу, но вместе с тем подобные формулы в силу своей громоздкости трудны для восприятия, особенно для тех, кто только начал осваивать работу с электронными таблицами.
В качестве финального оформительского штриха рекомендуется добавить на график с градуировочной линией ещё один ряд данных (Пособие, с. 78), состоящий лишь из одной пары значений x и y(то есть из одной точки) – значений ячеек “G10” и “G9”. Это позволит наглядно отмечать место расположения на графике (в серединной области, в районе одного из краёв или за его пределами) измеренной величины аналитического сигнала у исследуемого образца. После настроек параметров маркера такого дополнительного ряда результат может выглядеть примерно следующим образом:
Перечень публикаций на канале