(Применение табличных процессоров в практической деятельности судебных экспертов; Глава 2. Другие примеры использования электронных таблиц)
При проведении сравнительных исследований довольно удобным способом сопоставления объектов является построение кривых распределения, характеризующих те или иные свойства этих объектов. Ниже будут рассмотрены два случая использования такого приёма.
Первый из них относится к экспертизе нефтепродуктов и горюче-смазочных материалов. Допустим, было проведено исследование трёх образцов дизельного топлива методом газовой хроматографии и по результатам анализа хроматограмм для пиков, соответствующих углеводородам парафинового ряда С10 – С26 (подобная запись используется для сокращённого обозначения алканов (парафинов) – в ней указывается только число атомов углерода в молекуле. Например ундекан, имеющий формулу C₁₁H₂₄, обозначается как C11), получены значения площадей. Для построения кривых распределения требуется сначала пронормировать эти площади.
В общем случае, для совокупности n количественных значений однотипных свойств нормировка проводится по формуле:
Здесь: Sₖ – количественное значение k-го свойства (в рассматриваемом примере это площадь хроматографического пика компонента), а wₖ – его нормированное значение (доля).
С помощью электронных таблиц подобные расчёты выполняются очень легко. Взгляните на рисунок ниже – там приведены их результаты:
В ячейках 21-й строки считается сумма площадей пиков по формуле (показано на примере содержимого ячейки “С21”):
=СУММ(C3:C19)
=SUM(C3:C19)
Использованная в формуле функция
=СУММ( )
=SUM( )
вычисляет в указанном ей в качестве аргумента диапазоне ячеек сумму их численных значений.
В ячейке “D3” содержится формула для вычисления w₁₀ (в процентах):
=C3/C$21*100
Обратите внимание на то, что в формуле ссылка на ячейку, содержащую значение делителя (сумма площадей всех пиков), имеет у строковой части адреса абсолютную адресацию, в то время как у обозначения столбца она относительная. Это позволяет после ввода формулы в “D3”скопировать её через буфер обмена в “F3”и в “H3”, а маркером заполнения – размножить в каждом из столбцов “D”, “F”и “H”до 19-й строки, сразу получив в указанных столбцах готовые значения w.
Теперь можно переходить к визуализации результатов расчётов. Выделите ячейки "В3:В19", а затем, зажав [Ctrl], добавьте к выделению блок ячеек"D3:D19".
Запустите «Мастер диаграмм» и на первом шаге выберите тип «График» и вид «С маркерами»:
Остальные шаги выполните как обычно.
На вкладке ленты «Вставка» в секции «Диаграммы» выберите тип «График» и подтип «С маркерами»:
Вызовите диалоговое окно «Мастер диаграмм» и на первом шаге выберите тип «Линии» и подтип «Линии и точки»:
На втором шаге при положении переключателя в значении «Ряды данных в столбцах» установите флажок «Подписи в первом столбце», а дальше действуйте как обычно.
В итоге получится диаграмма, построенная на основе только одного ряда данных – теперь нужно добавить ещё два.
Выделите диаграмму и щёлкните в свободном её месте правой кнопкой мыши.
В появившемся контекстном меню выберите «Исходные данные...»:
Откроется соответствующее диалоговое окно. В нём сначала поменяйте имя первому ряду данных. В качестве имени можно указать ссылку на ячейку, установив курсор в соответствующее текстовое поле и щёлкнув кнопкой мыши по ячейке в листе (на рисунке ниже была выбрана ячейка “С1”):
или просто ввести вручную некий текст. В первом случае при изменении информации в ячейке “С1” имя ряда также будет изменяться.
Нажмите кнопку «Добавить» и укажите в качестве имени нового ряда содержимое ячейки “Е1”. Обратите внимание, что в нижней части окна в текстовом поле указан диапазон ячеек, которые будут использоваться для подписей оси Х (оси абсцисс):
Теперь нужно указать данные для значений второго ряда. Поставьте курсор в текстовое поле «Значения», удалите оттуда символы "={1}" и нажмите кнопку
в правой части поля. При этом окно минимизируется и потому не так сильно загораживает собой ячейки листа:
Необходимый диапазон (ячейки "F3:F19") указывается аналогично, как и при выделении, только при этом вокруг него возникает анимированная пунктирная рамка. Текст ссылки на указываемый диапазон при этом автоматически вписывается в поле. Возврат к обычному виду окна осуществляется при помощи кнопки
В появившемся контекстном меню выберите «Исходные данные...»:
Откроется диалоговое окно «Выбор источника данных»:
В нём сначала поменяйте имя первому ряду данных. Для этого в секции «Элементы легенды (ряды)» нажмите кнопку «Изменить». Появится небольшое окошко, в котором установите курсор в текстовое поле «Имя ряда», после чего щелкните по ячейке “С1” листа и ссылка на неё сама поставится в поле :
В поле «Имя ряда» не обязательно указывать ссылку на ячейку – туда можно просто ввести вручную некий текст, но в первом случае при изменении информации в ячейке “С1” имя ряда также будет изменяться.
Закройте окошко нажатием кнопки «ОК». После этого в секции «Элементы легенды (ряды)» нажмите кнопку «Добавить». Появится ещё одно такое же окно:
Укажите в качестве имени ряда содержимое ячейки “Е1”, после этого нужно будет указать данные для значений второго ряда. Поставьте курсор в текстовое поле «Значения», удалите оттуда символы "={1}" и нажмите кнопку
в правой части поля. При этом окно минимизируется и потому не так сильно загораживает собой ячейки листа:
Необходимый диапазон (ячейки "F3:F19") указывается аналогично, как и при выделении, только при этом вокруг него возникает анимированная пунктирная рамка. Текст ссылки на указываемый диапазон при этом автоматически вписывается в поле. Возврат к обычному виду окна осуществляется при помощи кнопки
Перейдите в режим правки диаграммы и щёлкните в свободном её месте правой кнопкой мыши. В появившемся контекстном меню выберите пункт «Диапазоны данных...»:
Откроется диалоговое окно – переключитесь в нём на вкладку «Ряды данных»:
Сначала зададим имя уже имеющемуся ряду данных. Для этого в списке «Диапазоны данных» выберите элемент «Название», после чего в текстовое поле «Диапазон для: Название» установите курсор. Имя задаётся в следующем виде (начинается со знака «равно» и заключается в кавычки):
="Название_ряда"
Кроме этого, можно в качестве имени ряда указать ссылку на какую-нибудь ячейку в листе. Для этого нужно нажать кнопку
справа от рассматриваемого поля, при этом окно минимизируется и приобретает вот такой вид:
Щёлкните по ячейке “С1”– в поле автоматически впишется текст ссылки на неё, а диалоговое окно вернётся в исходное состояние.
Нажмите кнопку «Добавить» и укажите для нового создаваемого ряда (данных по второму образцу) название и диапазон значений, выбрав соответствующий элемент в списке «Диапазоны данных» и указав для него ячейку (диапазон ячеек), аналогично тому, как это описано выше.
Действуя сходным образом, добавьте ещё один ряд данных со значениями w для третьего образца, после чего закройте диалоговое окно, нажав «ОК». Полученный результат будет выглядеть приблизительно так:
Второй случай необходимости построения кривых распределения имел место в экспертной практике автора при исследовании порошка поливинилхлорида (ПВХ) – тогда возникла необходимость сравнения по размерам частиц трёх образцов ПВХ.
Для каждого образца было произведено не менее 2100 замеров. Частицы в каждом образце обладали неправильной, шарообразной формой и за их размер принималось значение максимального диаметра. Обработка полученных результатов осуществлялась средствами табличного процессора Calc. Ниже описано, как выполнять такую обработку в среде всех трёх рассматриваемых в пособии программ.
Пусть имеются исходные данные по размерам частиц (в мкм) образцов 1, 2, 3, размещённые соответственно в столбцах “A”, “B”, “C”:
Для удобства обзора каждый из столбцов нужно отсортировать. Сначала выделите столбец “А”.
Нажмите кнопку сортировки по возрастанию
на панели инструментов «Стандартная».
Нажмите кнопку сортировки по возрастанию на вкладке ленты «Данные» в секции «Сортировка и фильтр»:
При этом появится небольшое окно – установите в нём переключатель в положение «сортировать в пределах указанного диапазона» и нажмите кнопку «Сортировка»:
Нажмите кнопку сортировки по возрастанию
а панели инструментов «Стандартная». При этом появится небольшое окно – нажмите в нём кнопку «Текущее выделение»:
Примечание: Вместо кнопки на панели задач можно воспользоваться командой программного меню «Данные» → «Сортировать по возрастанию...».
В LibreOffice 7.5 кнопки «Расширить выделение» и «Текущее выделение» подписаны иначе – как «Расширить выбор» и «Выбранное» соответственно.
Аналогичным образом отсортируйте данные в столбцах “В” и “С”. Сортировка позволяет быстро выяснить весь диапазон значений размеров частиц. Теперь предстоит разбить этот диапазон на интервалы и посчитать, сколько частиц имеют размеры, соответствующие каждому интервалу. Результаты этого выглядят следующим образом:
Как видно, диапазон разбит на интервалы по 20 мкм. В ячейке “H3” содержится такая формула:
=СЧЁТЕСЛИ(A$1:A$2500;"<="&$G3)-СЧЁТЕСЛИ(A$1:A$2500;"<="&$F3)
=COUNTIF(A$1:A$2500;"<="&$G3)-COUNTIF(A$1:A$2500;"<="&$F3)
Использованная в ней функция
СЧЁТЕСЛИ( ; )
COUNTIF( ; )
подсчитывает, сколько в указанном ей диапазоне ячеек (первый аргумент) удовлетворяют условию, фигурирующему в качестве второго аргумента. Таким образом, в приведённой формуле сначала подсчитывается количество ячеек в диапазоне "A1:A2500", числа в которых меньше 40, из него вычитается количество ячеек, числа в которых меньше 20, и таким образом подсчитываются ячейки из указанного диапазона, значения чисел в которых лежат в пределах от 20 до 40.
Обратите внимание на синтаксис условий с неравенствами: знаки берутся в кавычки и через амперсанд "&" соединяются с адресом ячейки. Кроме этого, в формуле в ссылках используется абсолютная адресация для столбцов и строк, благодаря чему эту формулу маркером заполнения можно копировать как вниз, до строки 16, так и вправо – до столбца “J”, при этом она в каждом случае срабатывает соответствующим образом, производя необходимые подсчёты.
Дальнейшие вычисления выполняются аналогично тому, как это было описано в случае обработки результатов хроматографического анализа. Так, в ячейке “Н19” находится формула (в “I19” и “J19” содержимое аналогичное)
=СУММ(H3:H16)
=SUM(H3:H16)
В ячейке “K3” же находится формула
=H3/H$19*100 ,
которая вычисляет долю частиц с определёнными размерными характеристиками и её также можно копировать маркером заполнения, как вниз, так и вправо.
Итоговый результат – кривые распределения частиц ПВХ по размерам – имеет вид, приведённый на рисунке ниже:
Файлы с примерами: а) парафины в дизельном топливе – xls / ods;
б) размеры частиц ПВХ – xls / ods
◁◁◁ Построение спектров (операция «текст по столбцам») --- Реализация метода Монте-Карло ▷▷▷
