(Применение табличных процессоров в практической деятельности судебных экспертов; Глава 2. Другие примеры использования электронных таблиц)
В химии метод градуировочного графика – один из наиболее применяемых для количественного анализа. Основан он на экспериментальном установлении зависимости т. н. аналитического сигнала y от концентрации определяемого вещества x: y = f(x). Исходя из используемого вида анализа и типа оборудования в роли аналитического сигнала могут выступать самые разные физические величины: оптическая плотность, высота хроматографического пика (выраженная, к примеру, через значение электрического напряжения на детекторе прибора), интенсивность спектральной линии (в том числе – в виде количества квантов определённой энергии, регистрируемых в единицу времени) и т. п. Концентрация также может выражаться в самых разных единицах (моль/л, %, г/мл и т. д.).
Для анализа в первую очередь проводится серия определений величины аналитического сигнала у стандартных образцов с известной концентрацией интересующего вещества. Традиционно получаемые при этом пары значений y – x наносились в виде точек на предварительно размеченную миллиметрованную бумагу. По этим точкам и строилась градуировка, после чего проводилось измерение аналитического сигнала у исследуемых образцов, а далее по готовой градуировке находилось содержание определяемого компонента в них.
Теперь же необходимость в миллиметровке отпала ввиду использования иного подхода. Рассмотрим пошагово, как это делается на примере, когда зависимость y = f(x) имеет линейный характер, то есть
y = a·x + b,
где a и b – постоянные числа.
Для начала нужно создать на листе вот такую табличку:
В ячейку “G10” введите формулу (она нам понадобится позднее):
=(G9-G6)/G5
Нетрудно видеть, что она по сути представляет собой приведённое выше уравнение прямой, решённое относительно x. Так как в ячейках “G5”, “G6” и “G9” выставлены единицы, то в “G10” должен оказаться 0. В столбцы “С” и “D” вводятся результаты измерений стандартных образцов.
Теперь выделите диапазон ячеек "C3:D17".
Вызовите окно «Мастер диаграмм» и на первом шаге укажите тип «Точечная» и вид «Без линий»:
Второй шаг можно пропустить. На третьем шаге выполните следующее:
а) на вкладке «Заголовки» задайте подписи для координатных осей,
б) на вкладке «Легенда» снимите флажок «Добавить легенду»
в) на вкладке «Линии сетки» добавьте отображение основных линий для оси X, установив соответствующий флажок.
На четвертом шаге задайте размещение диаграммы на том же листе, где находятся данные для неё.
Если есть желание, можете полученную диаграмму дополнительно подправить ещё, например, убрать фоновую заливку для области построения (она по умолчанию серая), сделать форму и цвет маркеров соответственно круглой и чёрным (изначально они – тёмно-синие ромбы), а линии сетки сделать серыми и пунктирными (вместо чёрных сплошных).
Выделите диаграмму и щёлкните правой кнопкой мыши по любому маркеру экспериментальной точки. В появившемся контекстном меню выберите пункт «Добавить линию тренда...»:
Появится диалоговое окно «Линия тренда». В нём на вкладке «Тип» выбирается разновидность аппроксимирующей кривой – по умолчанию это «Линейная»:
Переключитесь на вкладку «Параметры» и установите флажок «показывать уравнение на диаграмме», после чего нажмите «ОК»:
На вкладке ленты «Вставка» в секции «Диаграмма» выберите тип «Точечная» и подтип «Точечная с маркерами»:
В лист вставится соответствующая диаграмма. Если есть желание, можете её дополнительно подправить по своему вкусу, например, сделать форму и цвет маркеров соответственно круглой и чёрным.
Выделите диаграмму и щёлкните правой кнопкой мыши по любому маркеру экспериментальной точки. В появившемся контекстном меню выберите пункт «Добавить линию тренда...»:
При этом появится диалоговое окно «Формат линии тренда». В нём в разделе «Параметры линии тренда» выберите тип «Линейная» и установите флажок «показывать уравнение на диаграмме», после чего нажмите кнопку «Закрыть»:
Примечание: В Excel 2016 рассматриваемое диалоговое окно заменено на аналогичную по функционалу боковую панель.
Вызовите диалоговое окно «Мастер диаграмм» и на первом шаге выберите тип «XY (разброс)», подтип «Только точки»:
На втором шаге проверьте, что установлено положение переключателя в положении «Ряды данных в столбцах» и снимите флажки «Первая строка как подпись» и «Первый столбец как подпись», если они там установлены.
На третьем шаге ничего менять не требуется, а на четвёртом можете снять флажок «Показать легенду», указать подписи для оси X и оси Y и включить показ координатной сетки для обеих осей, установив соответствующие флажки.
После нажатия кнопки «Готово» созданная диаграмма будет находиться в режиме правки. Щёлкните правой кнопкой мыши по любому маркеру экспериментальной точки. В появившемся контекстном меню выберите пункт «Вставить линию тренда...»:
Откроется диалоговое окно, в котором нужно поставить переключатель «Тип регрессии» в положение «Линейный» и установить флажок «Показать уравнение»:
В результате этого на диаграмме появятся аппроксимирующая линия (градуировочный график) и её уравнение, рассчитанное по методу наименьших квадратов. Вид получившегося результата может быть примерно таким (показано на примере диаграммы Calc):
Если из полученного уравнения линии переписать значения коэффициентов a и b в ячейки “G5” и “G6” соответственно, то при указании в ячейке “G9” значения измеренного у исследуемого образца аналитического сигнала, формула в ячейке “G10” посчитает концентрацию.
Следует отметить, что программное обеспечение, входящее в комплект поставки современного лабораторного оборудования, часто в своём функционале имеет встроенные возможности для построения градуировочных графиков, однако использование для этой же цели табличных процессоров является не только универсальным способом, но и в ряде случаев может оказаться даже более удобным.
◁◁◁ Линейная интерполяция (защита ячеек) --- Контроль сроков действия права самостоятельного производства экспертиз (способы адресации ячеек) ▷▷▷
См. также (усовершенствованная версия градуировочного графика):