Основы численных методов и их реализация в MS Excel

Министерство науки и высшего образования Российской Федерации

Казанский национальный исследовательский

технологический университет

В. Е. Воробьева, Ф. И. Воробьева

ОСНОВЫ ЧИСЛЕННЫХ 

МЕТОДОВ И ИХ РЕАЛИЗАЦИЯ 

В MS EXCEL

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

Казань

Издательство КНИТУ

2022

УДК 004.67(075)
ББК 32.97я7

В75

Печатается по решению редакционно-издательского совета 

Казанского национального исследовательского технологического университета

Рецензенты:

канд. физ.-мат. наук А. Р. Юльметов

канд. физ.-мат. наук, доц. Л. И. Савостина

В75

Воробьева В. Е.
Основы численных методов и их реализация в MS Excel : учебное пособие / 
В. Е. Воробьева, Ф. И. Воробьева; Минобрнауки России, Казан. нац. исслед. 
технол. ун-т. – Казань : Изд-во КНИТУ, 2022. – 124 с.
ISBN 978-5-7882-3138-9

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

Рекомендовано магистрам направлений 09.04.01 «Информационные техноло
гии», 15.04.04 «Автоматизация технологических процессов и производств» и 18.04.02 
«Энерго- и ресурсосберегающие процессы в химической технологии, нефтехимии 
и биотехнологии» и бакалаврам направления 18.03.02 «Энерго- и ресурсосберегающие 
процессы в химической технологии, нефтехимии и биотехнологии», а также студентам
и аспирантам для самостоятельного изучения.

Подготовлено на кафедре автоматизированных систем сбора и обработки ин
формации.

Валерия Евгеньевна Воробьева
Фарида Ильгизовна Воробьева

Редактор Л. Ш. Киямова

Подписано в печать 16.05.2022
Формат 6084 1/16

Бумага офсетная
Печать цифровая
7,21 усл. печ. л.

7,75 уч.-изд. л.
Тираж 400 экз.
Заказ 79/22

Издательство Казанского национального исследовательского 

технологического университета

Отпечатано в офсетной лаборатории Казанского национального

исследовательского технологического университета

420015, Казань, К. Маркса, 68

ISBN 978-5-7882-3138-9
© Воробьева В. Е., Воробьева Ф. И., 2022
© Казанский национальный исследовательский 

технологический университет, 2022

УДК 004.67(075)
ББК 32.97я7

2 

В В Е Д Е Н И Е

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

Реализация таких решений обычно не может быть выполнена

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

В настоящее время стремительное развитие вычислительной тех
ники расширяет области применения численных методов для самых 
различных задач во всех областях как науки, так и техники. Этим вопросам и посвящено данное пособие.

3 

Ч И С Л Е Н Н Ы Е  М Е Т О Д Ы  Р Е Ш Е Н И Я

М А Т Е М А Т И Ч Е С К И Х  З А Д А Ч

Обычно для решения большинства задач прикладной математики 

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

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

При переходе к дискретным моделям появляются погрешности

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

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

4 

Для многих классов задач разработаны разнообразные численные 

методы решения. По способу дискретизации численные методы делятся на проекционные и конечно-разностные, по способу решения –
на прямые и итерационные. 

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

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

Многие наши задачи требуют нахождения корней уравнений или 

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

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

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

5 

без ограничений. Если есть желание попытаться создать их самостоятельно, то в пособии даны подробные описания этих процедур.

Для удобства в тексте приняты следующие форматы:
– текст, набранный курсивом – общепринятый термин, курсивом

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

– [Enter] – названия клавиш на клавиатуре компьютера, которые

заключены в квадратные скобки;

– Книга1, А1, Лист1 – названия объектов MS Excel;

– Главная – Заполнить (
) – команды MS Excel на ленте;

– Подчеркнутый текст – требует ввода с клавиатуры;
– Public Function My_fun() – код программы в VBA.
Прежде чем перейти к основным задачам, познакомимся с осно
вами численных методов. Все они строятся на итерационных процедурах, которые повторяют простые вычисления с подбором какого-либо 
из параметров, пока не будет достигнуто решение с заданной точностью. Не надо считать, что эти решения приближенные, мы вправе задать любую точность (даже очень высокую), но это потребует большего 
числа итераций и, как следствие, больших затрат машинного времени.

Что же такое точности решения? Обычно требования по точно
сти наших результатов выбираются из требований ГОСТ [1] в зависимости и типа наших исследований (лабораторные исследования, макетные, полупромышленные и промышленные установки и т. п.). Например, для наших научных исследований это 95 %, как и требуется по 
ГОСТ для обычных научно-исследовательских работ. Однако при выполнении расчетов мы накапливаем погрешности как за счет ошибок 
в исходных данных, так и за счет их (ошибок) накопления во время расчетов и округления результатов вычислений. Реально мы можем разделить наши ошибки на несколько групп:

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

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

Ошибки метода – применяемые для решения задач методы 

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

6 

Ошибки вычислений и округления – при вводе исходных данных 

в ЭВМ, выполнении арифметических операций и выводе результатов 
на печать производятся округления.

Поэтому полная погрешность при решении задачи на ЭВМ скла
дывается из трех составляющих:

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

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

Вычислительная погрешность определяется характеристиками 

используемой ЭВМ, ее разрядной сеткой действительных чисел. Желательно всегда использовать переменные двойной точности.

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


+
= X
X
, 
(1)

где X – реальное значение; ̅X – записанное значение; ε – ошибка.

Теперь можно построить все погрешности для основных арифме
тических операций. Сложение и вычитание имеют погрешности: 

(
) (
)
(
)
2
1
*
2
2
1
1




+
+
=
+

+
=


X
X
X
X
, 
(2)

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

Умножение. Если построить показанное в предыдущем абзаце 

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

1
2
1






+
+


(3)

Деление при подобных расчетах дает зависимость точности 

2

2
1
1 



−
+
=

.
(4)

Как видим, любые вычисления приводят к потере точности, но 

самые опасные возникают при вычитании.

Для оценки погрешностей в расчетах используются абсолютная 

и относительная погрешности.

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

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

В расчетах удобнее использовать относительные погрешности, 

что обеспечивает получение одинаковых погрешностей по всей вещественной оси чисел.

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

1. Цели и задачи дисциплины.
2. Понятие численных методов, основные пути их реализации.
3. Область применения численных методов.
4. Ошибки, возникающие при решении данных задач.
5. Погрешности вычислений, основные понятия.
6. Ошибки косвенных вычислений.

8 

О С Н О В Ы Р А Б О Т Ы  В  E X C E L

В в е д е н и е  в  р а б о т у

Все численные методы предполагают определенный алгоритм 

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

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

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

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

– нулевой – необходимо вычислять только саму функцию и ме
тоды являются линейными;

– первый и выше – необходимо вычисление функции и первой

производной и т. д. Решения становятся нелинейными.

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

Рассмотрим два эти подхода для решения простейшей математи
ческой задачи – нахождение корней уравнения и его экстремумов. Все 
решения будем выполнять в электронной таблице MS Excel. Вспомнить 
основные приемы работы в пакете, можно обратившись к материалам 
прил. 1–2 данного пособия или к учебным пособиям [2, 3].

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

9 

Р е а л и з а ц и я  р е ш е н и я  в  M S  E x c e l

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

Поэтому на первом этапе выполнения лабораторных работ надо 

создать в среде VBA функции по этим трем уравнениям и две вспомогательные функции для нахождения первой и второй производных.

Заходим в среду VBA командой c ленты Разработчик – Visual Basic

или через горячие клавиши [Alt + F11]. В открывшемся окне (рис. 1) видим оболочку VBA, которая является неотъемлемой частью MS Office. 

Рис. 1. Окно редактора VBA

Подробно с приемами работы в среде VBA можно ознакомиться

в пособии [3]. С левой стороны окна видим дерево проекта, которое 
пока не содержит элементов с программным кодом, а содержит только 

10