Численные методы в физико-химическом эксперименте. Программирование в 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
,