(Применение табличных процессоров в практической деятельности судебных экспертов; Глава 2. Другие примеры использования электронных таблиц)
При производстве экспертиз спиртосодержащих жидкостей (водно-спиртовых смесей) одним из этапов исследования является определение содержания этилового спирта в исследуемом объекте, результат которого принято приводить как объёмную долю этанола (в %) при температуре 20°С. Определение крепости проводится ареометрическим методом, причём делать это допускается и при температуре, отличной от указанной, так как есть специальная пересчётная таблица, по которой можно установить истинную крепость (при 20 °С), если известны температура проведения измерения и показываемое при этом ареометром содержание спирта (т. н. видимая крепость). Вот фрагмент такой таблицы:
Как видно, она составлена с шагом по температуре в 1 °С и с шагом 0,5% – по видимой крепости. Главная трудность её использования заключается в том, что на практике редко измеренные термометром и ареометром значения точно попадают в табличные величины – чаще всего результат бывает примерно таким: 39,7% об. при 23,3 °С. В таких случаях и проводится процедура линейной интерполяции табличных значений. В рассматриваемом примере для этого нужна такая выборка данных:
С математической точки зрения линейная интерполяция сводится к тому, что если известны два значения некой функции y(x): (x₁; y₁) и (x₂; y₂), то возможно найти приближённое значение y₀ = y(x₀), если x₁ < x₀ < x₂ , а функцию в указанном интервале (x₁, x₂) можно считать линейной. Так как в аналитической геометрии существует несколько способов записи уравнения прямой линии, то y(x) при x в интервале (x₁, x₂) может быть описана как прямая с угловым коэффициентом k, проходящая через точку (x₀, y₀):
y – y₀ = = k·(x – x₀) , где k = (y₂ – y₁)/(x₂ – x₁)
Программа для расчёта истинной крепости выглядит следующим образом:
Экспериментально измеренные величины заносятся в закрашенные тёмно-серым цветом и выделенные жирными границами ячейки “С7” (температура) и “Е5” (видимая крепость). После этого в ячейки с тёмно-серой заливкой вводятся соответствующие значения из пересчётной таблицы. Конечный результат (истинная крепость) показывается красным шрифтом в ячейке “Е7” – она имеет светло-зелёную заливку. В рассматриваемом примере интерполяция проводится трижды. Сначала получаются два промежуточных значения истинной крепости при 23 °С и 24 °С для видимой крепости 39,7% (ячейки “Е6” и “Е8” соответственно), а уже на основании их определяется крепость при 23,3 °С. Расчётные формулы в ячейках следующие:
“Е6”: =(F6-D6)/0,5*(E5-D5)+D6
“Е7”: =(F8-D8)/0,5*(E5-D5)+D8
“Е8”: =E6-(E6-E8)*(C7-C6)
Указанные формулы основаны на уравнении прямой, приведённом выше, и в них также учтено, что численно разница между табличными значениями температур равна 1, а разница между табличными значениями видимой крепости – 0,5.
Если измеренная (видимая) крепость жидкости будет точно равна целому или полуцелому числу %, то в этом случае будет достаточно одной интерполяции по температуре – для этого в ячейке “D7” имеется такая формула:
=D6-(C7-C6)*(D6-D8)
По аналогии, если при проведении измерений температура исследуемой жидкости окажется равной целому числу градусов, то также будет достаточно однократной интерполяции, результат которой (разумеется, после ввода всех необходимых табличных и эмпирических данных) отобразится в ячейке “Е6”.
Как видно, приведённая программа для расчётов берёт на себя заботу о правильности выполнения вычислений – пользователю в этом случае остаётся только аккуратно заполнять ячейки необходимыми для этого числами. При этом ячейки с формулами непосредственно соседствуют с ячейками, в которые вводятся данные. Это чревато тем, что можно по ошибке ввести число не в то место, затерев при этом формулу. Для избежания этого в табличных процессорах есть возможность защитить ячейки от нежелательного изменения.
Щёлкните правой кнопкой мыши по какой-нибудь ячейке и с помощью появившегося контекстного меню откройте диалоговое окно «Формат ячеек».
Переключитесь в нём на вкладку «Защита»:
и обратите внимание на флажок «Защищаемая ячейка».
Переключитесь в нём на вкладку «Защита ячейки»:
и обратите внимание на флажок «Защищено».
Такую отметку по умолчанию имеют все ячейки листа. В связи с этим обычно поступают так. Сначала выделяют весь лист и в окне «Формат ячеек» снимают указанный флажок. После этого выделяют ячейки, которые действительно не должны подвергаться редактированию (для рассматриваемой программы по линейной интерполяции это “Е6”, “Е7”, “Е8”, “D7”) и для них снова посредством вызова окна «Формат ячеек» ставят отметку о защите. После этого:
В программном меню нужно выбрать пункт «Сервис» → «Защита» → «Защитить лист...».
На вкладке «Рецензирование» в секции «Изменения» нужно нажать кнопку «Защитить лист»:
В появившемся диалоговом окне «Защита листа» нужно нажать «ОК». Здесь же можно установить пароль – его понадобится ввести, чтобы снять впоследствии при необходимости защиту листа. Если устанавливать пароль нет нужды, то следует просто оставить текстовое поле для него пустым:
В программном меню нужно выбрать пункт «Сервис» → «Защитить документ» → «Лист...». Появится диалоговое окно:
В нём нужно нажать «ОК». Здесь же можно установить пароль для снятия впоследствии защиты листа. В случае, если установка пароля не требуется, то нужно соответствующие текстовые поля оставить незаполненными.
Примечание: в Calc из LibreOffice 7.5 обсуждаемое здесь диалоговое окно вызывается в программном меню чуть иначе: «Сервис» → «Защитить лист...»
После этого предварительно указанные ячейки окажутся заблокированными от случайных изменений, а остальные, на которые установленная защита не распространяется, можно будет редактировать, при этом формулы в защищённых ячейках будут исправно работать.
◁◁◁ Статистическая обработка результатов анализа (опция «специальная вставка») --- Построение градуировочных графиков (аппроксимация данных) ▷▷▷
