Численные методы в физико-химическом эксперименте. Программирование в MS Excel. Часть 1

МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ

РОССИЙСКОЙ ФЕДЕРАЦИИ

Федеральное государственное автономное образовательное

учреждение высшего образования

«ЮЖНЫЙ ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ»

Численные методы

в физико-химическом эксперименте.

Программирование в MS Excel

Учебное пособие

В двух частях

Часть 1

Ростов-на-Дону – Таганрог

Издательство Южного федерального университета

2024

УДК 519.6:544(075.5)
ББК 22.19:24.5я7

Ч-67

Печатается по решению кафедры физической и коллоидной химии 

имени профессора В. А. Когана Южного федерального университета

(протокол № 6 от 12 января 2023 г.)

Рецензенты:

доктор технических наук, заведующая кафедрой химии Донского 

государственного технического университета, профессор В. Э. Бурлакова;

доктор химических наук, заведующий кафедрой аналитической химии 

Южного федерального университета, профессор И. Е. Уфлянд

Ч-67
Численные методы в физико-химическом эксперименте. 

Программирование в MS Excel : учебное пособие : в 2 ч. Часть 1 / 
И. Н. Щербаков, С. Н. Любченко, Ю. П. Туполова, С. А. Бородкин ; 
Южный федеральный университет. – Ростов-на-Дону ; Таганрог : 
Издательство Южного федерального университета, 2024. – 136 с.

ISBN 978-5-9275-4518-6
ISBN 978-5-9275-4519-3 (Ч. 1)

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

Предназначено для студентов специалитета, обучающихся по направлению 04.05.01

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

УДК 519.6:544(075.5)
ББК 22.19:24.5я7

ISBN 978-5-9275-4519-3 (Ч. 1)
ISBN 978-5-9275-4518-6

© Южный федеральный университет, 2024
© Щербаков И. Н., Любченко С. Н.,

Туполова Ю. П., Бородкин С. А., 2024

© Оформление. Макет. Издательство 

Южного федерального университета, 2024

ОГЛАВЛЕНИЕ

СПИСОК УСЛОВНЫХ ОБОЗНАЧЕНИЙ И СОКРАЩЕНИЙ ......................6
ПРЕДИСЛОВИЕ..................................................................................................7

ГЛАВА 1. МАТЕМАТИЧЕСКИЕ МОДЕЛИ И ЧИСЛЕННЫЕ МЕТОДЫ.9

Контрольные вопросы.................................................................................18

ГЛАВА 2. ПОГРЕШНОСТИ РЕЗУЛЬТАТА ЧИСЛЕННЫХ МЕТОДОВ..19

2.1. Источники погрешности......................................................................19
2.2. Погрешность действий ........................................................................20
2.3. Правила записи приближенных чисел ...............................................22

2.3.1. Значащие цифры........................................................................23
2.3.2. Неявная запись приближенных чисел.....................................26
2.3.3. Явная запись приближенных чисел.........................................28

2.4. Погрешность при проведении расчетов.............................................30

2.4.1. Функции одного аргумента......................................................31
2.4.2. Функции нескольких аргументов............................................32

2.5. Итерационные алгоритмы ...................................................................38

2.5.1. Термины и характеристики процессов ...................................39
2.5.2. Порядок сходимости итерационных процессов.....................41
2.5.3. Критерии сходимости ...............................................................41
2.5.4. Блок-схемы алгоритмов............................................................42

Контрольные вопросы.................................................................................44

ГЛАВА 3. КОРНИ УРАВНЕНИЙ...................................................................45

3.1. Решение уравнений одной переменной .............................................45

3.1.1. Процедура локализации корней...............................................45
3.1.2. Критерии сходимости при решении уравнений.....................48

3.2. Метод простой итерации .....................................................................48
3.3. Метод Ньютона – Рафсона ..................................................................51
3.4. Метод дихотомии .................................................................................54
3.5. Метод хорд............................................................................................58
3.6. Оценка скорости сходимости методов...............................................60

3.7. Химические задачи, сводимые к решению уравнений.....................62

3.7.1. Равновесие в произвольной химической реакции .................62
3.7.2. Моделирование состава раствора слабой трехосновной 

кислоты.......................................................................................64

3.7.3. Моделирование состава буферного раствора слабой 

двухосновной кислоты..............................................................65

3.7.4. Моделирование состава буферного раствора слабой 

n-основной кислоты и соответствующих солей.....................68

Контрольные вопросы.................................................................................70

ГЛАВА 4. СИСТЕМЫ ЛИНЕЙНЫХ УРАВНЕНИЙ ....................................71

4.1. Обусловленность линейных алгебраических систем .......................76
4.2. Прямые (точные) методы.....................................................................80

4.2.1. Метод Крамера...........................................................................80
4.2.2. Метод обратной матрицы .........................................................81

4.3. Приближенные (итерационные) методы............................................83

4.3.1. Метод минимальных невязок...................................................83

4.4. Задачи, сводимые к системам линейных уравнений ........................85

4.4.1. Спектрофотометрическое определение состава смеси .........85
4.4.2. Масс-спектрометрическое определение состава смеси ........87
4.4.3. Интерполяция таблично заданной функции...........................88
4.4.4. Интерполирование функций полиномами..............................92

Контрольные вопросы.................................................................................94

ГЛАВА 5. СИСТЕМЫ НЕЛИНЕЙНЫХ УРАВНЕНИЙ...............................95

5.1. Метод простой итерации .....................................................................96
5.2. Метод Зейделя.......................................................................................97
5.3. Метод Ньютона.....................................................................................98
5.4. Задачи, сводящиеся к решению системы нелинейных уравнений103
Контрольные вопросы...............................................................................105

ГЛАВА 6. РЕГРЕССИОННЫЙ АНАЛИЗ ....................................................106

6.1. Метод наименьших квадратов ..........................................................108
6.2. Линейная модель парной регрессии .................................................110

6.3. Обобщенная линейная регрессия......................................................118
6.4. Регрессионные модели, сводимые к линейным ..............................121
Контрольные вопросы...............................................................................127

ГЛОССАРИЙ ...................................................................................................128
ЛИТЕРАТУРА .................................................................................................132
ПРИЛОЖЕНИЕ ...............................................................................................134

СПИСОК УСЛОВНЫХ ОБОЗНАЧЕНИЙ

И СОКРАЩЕНИЙ

c – концентрация вещества в растворе
NA – число Авогадро – 6,022140857(74)⋅1023 моль−1

Ki – константы диссоциации по i-й ступени

R – универсальная газовая постоянная – 8,31446261815324 м3⋅Па⋅К−1⋅моль−1

r – коэффициент корреляции
R2 – величина достоверности аппроксимации в MS Excel
МНК – метод наименьших квадратов
ММ – математическая модель
ПО – программное обеспечение
СЛАУ – система линейных алгебраических уравнений
ЭВМ – электронная вычислительная машина
x – абсолютная погрешность аргумента (переменной)
x – относительная погрешность аргумента (переменной)
y – абсолютная погрешность функции
y – относительная погрешность функции
 – критерий сходимости или молярный коэффициент поглощения

ПРЕДИСЛОВИЕ

Возможность постановки вычислительного эксперимента на ЭВМ су
щественно ускорила процесс математизации науки и техники. Настоящее 
время характеризуется все возрастающей ролью вычислительной математики в связи с бурным развитием компьютерных технологий и средств вычислительной техники. Расширяется круг профессий, для которых математическая грамотность становится необходимостью. Обилие учебного материала, посвященного методам вычислений, в основном ориентировано на
подготовку специалистов, имеющих математический уклон в образовании.
Редкими исключениями являются книги для представителей других, нематематических, специальностей, например для химиков [1; 2].

Современная химия, как и большинство естественных наук, содержит 

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

Решение многих задач, таких как моделирование физико-химических 

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

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

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

Предисловие

8

цепочке исследований: постановка физико-химической задачи – математическая формулировка задачи – выбор методов решения – реализация решения с помощью ПО – интерпретация, в том числе и графическая, полученных результатов. Соединение знаний химических законов и численных методов решения, возникающих при моделировании химических задач, дает 
возможность существенно улучшить готовность студентов к профессиональной деятельности в современных условиях.

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

Учебное пособие разработано на базе курса лекций и практических за
нятий «Вычислительные методы в химии» [3], читаемого студентам химического факультета Южного федерального университета (ЮФУ) в рамках 
образовательной программы «Фундаментальная и прикладная химия».
Кроме основных классических разделов численных методов оно содержит
дополнения практического характера, позволяющие визуализировать 
программирование и вычисления с помощью не специализированного ПО
(MathCad, MathLab, Mathematica, Maple, Statistica и др.), часто требующего
подключения к Интернету, а с применением MS Excel, входящего в стандартный пакет MS Office.

Книга может быть полезна для студентов естественных факультетов ву
зов, в подготовке которых отсутствует или только затрагивается раздел математики – вычислительные (численные) методы.

ГЛАВА 1

МАТЕМАТИЧЕСКИЕ МОДЕЛИ И ЧИСЛЕННЫЕ МЕТОДЫ

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

Получить ответы на возникающие вопросы можно с помощью проведе
ния эксперимента. Существует два подхода – экспериментальный и теоретический.

В рамках первого подхода, допустим, поставлена задача выпуска како
го-либо нового соединения в промышленных масштабах. Для этого сначала 
необходимо провести лабораторный синтез данного соединения и изучить его 
свойства. Далее – собрать экспериментальную установку для производства 
искомого вещества и на основании серии опытов получить данные о ее
производительности и качестве продукта. И наконец, построить завод, запустить его. Очевидно, что данный путь является, скорее всего, не только 
дорогим, но и затратным по количеству времени, необходимого для его 
осуществления. На каждом этапе возможны неудачи. Например, метод синтеза, использованный в лаборатории, не удается воспроизвести в заводских 
условиях. Тогда цепочка «синтез – экспериментальная установка – завод»
должна быть неоднократно повторена до полного удовлетворения условий
по промышленному выпуску продукции.

Другой подход заключается в математическом анализе объектов иссле
дования или явлений в рамках теоретических (физических, физикохимических, технологических и других) представлений о закономерностях
их поведения.

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

уже не к реальным явлениям, а к некоторым их моделям, сформулированным

Глава 1. Математические модели и численные методы

10

в виде математических соотношений, – математическим моделям, которые более или менее точно их отображают. Любая модель не может быть 
тождественна объекту-оригиналу и поэтому является всегда неполной. ММ –
математическое представление реальности, один из вариантов модели как 
системы, исследование которой позволяет получать информацию о некоторой другой системе. Другими словами, это некоторое образное представление одного или множества объектов, создаваемое исследователем при помощи определенной формальной (математической) системы с целью изучения и оценки определенных свойств или его поведения [4].

Ни одно из предлагаемых в настоящее время определений не может

в полном объеме реально охватить всю человеческую деятельность, связанную с математическим моделированием. Что же такое математическое 
моделирование? Лучший ответ на этот вопрос, по мнению авторов, дает 
В. П. Ильин: «…математическое моделирование – это совокупность фундаментальных и прикладных научных направлений по изучению процессов
и явлений с помощью математических методов, вычислительных алгоритмов и информационных технологий, реализуемых в программных комплексах, с помощью которых проводится машинный эксперимент и принимается решение по результатам исследований» [5]. Рассмотрим, из чего состоит 
данный процесс.

Первый этап такой работы – это постановка задачи – формулировка

математической модели.

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

Например, при моделировании процесса адсорбции из раствора на гра
нице с газом И. Ленгмюр (Irving Langmuir) использовал выражения для 
скоростей адсорбции (Vадс.) и десорбции (Vдес.):

)
1(
0
адс.
адс.
− 
=
с
k
V
,
(1)

0
дес.
дес.

= k
V
,