Прикладные компьютерные системы. Раздел : решение задач численными методами в среде электронных таблиц Excel

Кафедра обработки металлов давлением 

Галкин С.П., Михайлов В.К. 

Одобрено Методическим  
советом института 

ПРИКЛАДНЫЕ КОМПЬЮТЕРНЫЕ 
СИСТЕМЫ 

Раздел: Решение задач численными методами в среде 
электронных таблиц Excel 

Учебное пособие  
для практических занятий 
студентов специальностей 1106, 1204, 1703 

№ 747 

МОСКВА 2001 

УДК 004.42 

АННОТАЦИЯ 

Учебное пособие предназначено для практических занятий по 
курсам «Информатика», «Прикладные компьютерные системы», 
«Моделирование и оптимизация технологических систем». 

Пособие содержит теоретическое описание численных методов, 
используемых при решении инженерных задач, алгоритмы их 
реализации, 
программное 
обеспечение 
этих 
методов 
на 
алгоритмическом языке VBA (Visual Basic for Application) для Excel 
97, а также примеры и многовариантные задания. 

Пособие состоит из восьми тематических частей по основам 
численных методов и дополнительных сведений, посвященных 
программированию в VBA. Овладение этим материалом позволит 
научиться редактировать и самостоятельно составлять программный 
код макросов на языке VBA для всех приложений Office 97 (Excel, 
Word, Access и др.), а также AutoCad 14, 15, а в дальнейшем перейти 
непосредственно 
к 
визуальному, 
объектно-ориентированному 
программированию 
на 
алгоритмическом 
языке 
Visual 
Basic 
версий 5 и 6. 

© Московский государственный 
институт стали и сплавов 
(Технологический университет) 
(МИСиС) 2001 

СОДЕРЖАНИЕ 

Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
6

1. Основы работы с VBA в Microsoft Excel 97 . . . . . . . . . 
8

 
1.1. Переменные VBA . . . . . . . . . . . . . . . . . . . .. . . . .. . . . .. . .  
8

 
1.2. Функции, процедуры, макросы . . . . . . . . . . . . . . . . . . . . . 
11

 
1.3. Способы создания макросов . . . . . . . . . . . . . . . . . . . . . .  
13

 
1.4. Обмен информацией между ячейками рабочего листа и 
переменными Excel VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  
16

 
1.5. Диалоговые окна  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
18

 
1.6. Отладка и редактирование кода  . . . . . . . . . . . . . . . . . . . . 
19

 
 
1.6.1. Отображение значений . . . . . . . . . . . . . . . . . . . . . . . 
21

 
 
1.6.2. Окна режима отладки . . . . . . . . . . . . . . . . . . . . . . . . 
23

 
 
1.6.3. Закладки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
27

 
1.7. Справочная информация VBA  . . . . . . . . . . . . . . . . . . . . . 
27

1.8. Порядок работы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  
29

2. Решение систем линейных уравнений . . . . . . . . . . . . . . . . . . . . 
31

 
2.1. Текст макроса «Таблица» и процедуры «UserFormat» ... 
32

 
2.2. Методы решения систем линейных уравнений . . . . . . . . 
34

 
 
2.2.1. Методы Гаусса  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
34

 
 
2.2.2. Метод решения по правилу Крамера . . . . . . . . . . .  
49

 
 
2.2.3. Метод обратной матрицы . . . . . . . . . . . . . . . . . . . .  
54

 
2.3. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
58

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

3. Решение задач интерполяции  . . . . . . . . .. . . . . . . . . . . . . . . . . . 
64

 
3.1. Интерполирование функции одной переменной  . . . . . . 
64

 
 
3.1.1. Текст макроса «СозданиеТаблицы»  . . . . . . . . . . . . 
65

 
 
3.1.2. Интерполяция полиномом Лагранжа  . . . . . . . . . . . 
66 

 
 
3.1.3. Интерполирование по методу Эйткена  . . . . . . . . . 
70

 
 
3.1.4. Сплайн-интерполяция  . . . . . . . . . . . . . . . . . . . . . . . 
73

 
3.2. Интерполирование функции двух переменных  . . . . . . . 
77

 
 
3.2.1. Текст макроса «СозданиеТаблицы2»  . . . . . . . . . . . 
77

3.2.2. Квадратичная интерполяция функции  
двух переменных . . . . . . . . . . . . . . . . . . . . . . . . . . .  
79

 
3.3. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
82

3 

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

4. Решение задач аппроксимации . . . . . . . . . . . . . . . . . . . . . . . . . . 
84

 
4.1. Текст программного кода макроса «AppTablica»  . . . . .  
85

 
4.2. Аппроксимация полиномом  . . . . . . . . . . . . . . . . . . . . . . . 
87

 
4.3. Аппроксимация экспоненциальной функцией  . . . . . . . . 
91

 
4.4. Аппроксимация степенной функцией  . . . . . . . . . . . . . . . 
95

 
4.5. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
98

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

5. Решение алгебраических и трансцендентных уравнений  . . . . 
104

 
5.1. Текст функции «Функция»  . . . . . . . . . . . . . . . . . . . . . . . . 
104

 
5.2. Метод простых итераций . . . . . . . . . . . . . . . . . . . . . . . . . . 
105

 
5.3. Метод деления отрезка пополам (метод дихотомии) . . . 
107

 
5.4. Метод Ньютона (касательных) . . . . . . . . . . . . . . . . . . . . . 
109

 
5.5. Метод Рыбакова . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
110

 
5.6. Метод поразрядного приближения . . . . . . . . . . . . . . . . . .
113

 
5.7. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
116

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

6. Определение экстремумов функций  . . . . . . . . . . . . . . . . . . . . . 
119

 
6.1. Методы поиска экстремума одномерной функции . . . . . 
119

 
 
6.1.1. Текст функции «УравнениеОднойПеременной» .  
120

 
 
6.1.2. Метод поразрядного поиска  . . . . . . . . . . . . . . . . . . 
120

 
 
6.1.3. Метод дихотомии  . . . . . . . . . . . . . . . . . . . . . . . . . . . 
122

 
 
6.1.4. Метод золотого сечения . . . . . . . . . . . . . . . . . . . . . . 
124

 
 
6.1.5. Метод квадратичной интерполяции . . . . . . .. . . . . 
126

 
6.2. Методы поиска экстремума многомерной функции . . . .
128

 
 
6.2.1. Текст функции «УравнениеМногихПеременных». 
129

 
 
6.2.2. Метод координатного спуска  . . . . . . . . . . . . . . . . . 
129

 
 
6.2.3. Метод спирального координатного спуска  . . . . . . 
131

 
6.2.4. Метод координатного спуска с применением 
квадратичной интерполяции . . .  . . . . . . . . . . . . . . . . . .  
133

 
6.3. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
135

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

7. Решение систем нелинейных уравнений . . . . . . . . . . . . . . . . . . 
139

 
7.1. Метод простых итераций . . . . . . . . . . . . . . . . . . . . . . . . . . 
139

 
7.2. Метод Зейделя  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
143

 
7.3. Метод Ньютона  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
145

 
7.4. Методы поиска экстремума . . . . . . . . . . . . . . . . . .. . . . . . 
150

4 

 
7.5. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
153

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

8. Вычисление определенных интегралов . . . . . . . . . . . . . . . . . . . 
157

 
8.1. Текст функции  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
160

 
8.2. Метод трапеций  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
160

 
8.3. Метод Симпсона . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
162

 
8.4. Интегрирование по формуле Бодэ  . . . . . . . . . . . . . . . . . . 
163

 
8.5. Метод Уэддля . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
165

 
8.6. Метод Ньютона–Котеса . . . . . . . . . . . . . . . . . . . . . . . . . . . 
167

 
8.7. Метод Чебышева  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
169

 
8.8. Достижение требуемой точности . . . . . . . . . . . . . . . . . . . 
172

 
8.9. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
174

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

9. Численное интегрирование дифференциальных уравнений . . 
181

 
9.1. Метод Эйлера–Коши  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
183

 
9.2. Метод трапеций  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
187

 
9.3. Метод Рунге–Кутта  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
191

 
9.4. Метод Рунге – Кутта – Meрсона  . . . . . . . . . . . . . . . . . . .  
195

 
9.5. Задания  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
201

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

Литература  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
207

5

ВВЕДЕНИЕ 

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

В качестве языка программирования используется один из самых динамичных и современных макроязыков – Visual Basic for 
Application (VBA) в среде Excel 97, который имеет следующие преимущества: 
– существенно расширяет, как возможности Excel 97, так любого 
другого приложения, поддерживаемого этим макроязыком (в настоящее время их более 50, в том числе Word–97, Access–97, 
Power Point 97, AutoCad 14, AutoCad 15); 

– позволяет создавать «мегапрограммы», объединяющие и координирующие усилия мощных прикладных программ, автоматизирующие и облегчающие работу пользователя; 

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

Последнее преимущество языка может быть описано по следующей схеме: создание макросов макрорекордером → правка программного кода макросов и освоение основ VBA → самостоятельное 
создание программ «вручную» с использованием, по мере освоения 
VBA, все большего числа его возможностей. Для облегчения самостоятельного изучения VBA в пособии изложены основы работы с 
VBA и его справочной системой (см. раздел 1). Более полное описание языка VBA можно найти в литературных источниках [7] – [9].  

При написании материала настоящего пособия в основу положены литературные источники [1]–[6], в которых приемы и методы 
программирования изложены более основательно, но на базе других 
алгоритмических языков. 

В восьми разделах пособия (со 2-го по 9-й) описаны численные методы, используемые для: 

6 

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

В каждом разделе рассмотрено от 3 до 7 различных численных методов. Порядок рассмотрения практически любого метода следующий: 
– теоретическое изложение метода; 
– описание алгоритма этого метода; 
– программный код, реализующий алгоритм; 
– пример. 

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

7

1. ОСНОВЫ РАБОТЫ С VBA В 
MICROSOFT EXCEL 97 

Компанией Microsoft разработана технология, которую называют ActiveX Automation. Эта технология позволяет обращаться к 
объектам приложения средствами любого макроязыка, если приложение и макроязык поддерживают эту технологию. С помощью 
ActiveX Automation можно автоматизировать работу за компьютером, 
разрабатывая макросы и специальные приложения. Для этого можно 
использовать современные стандартные дружественные к пользователю макроязыки типа: Visual Basic, Microsoft Visual C++, Delphi и 
др. Кроме перечисленных выше, к дружественным макроязыкам относится Visual Basic for Application (VBA), главная особенность которого заключается в том, что компоненты Microsoft Office 97 включают этот язык, и нет необходимости приобретать отдельно любой из 
перечисленных, к тому же дорогостоящих, макроязыков. На сегодняшний момент VBA является единым языком, который используют 
уже более 50 приложений, и их число неуклонно продолжает расти. К 
этим приложениям относятся: Word–97, Access–97, Excel–97, Power 
Point 97, AutoCad 14, AutoCad 15 и др. 

Макроязык VBA является современным языком визуального и 
объектно-ориентированного программирования. Он является объектным языком по двум причинам. Во-первых, он позволяет создавать 
собственные классы объектов, а, во-вторых, что более важно, позволяет работать с огромным числом объектов, содержащихся в библиотеках. Все приложения, входящие в Office 97: Excel, Word, Access и 
другие, представляют собой совокупность объектов со своими свойствами, методами и событиями. 

Рассмотрим основы работы с VBA в Excel-97. 

1.1. Переменные VBA 

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

Галкин С.П., Михайлов В.К. 

Самый простой способ – использование оператора Dim, например, для описания обычной переменной целого типа – 

 
Dim x As Integer, 

где x – переменная, а Integer – ее тип; 

для описания переменной одномерного массива – 

 
Dim m(25) As Integer; 

для динамически объявляемого массива: 

Dim k As Integer 
Dim z() As Integer 
k = 125 
ReDim z(k) 

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

Характеристики типов данных 

Название 
Размер, 
байт 

Диапазон значений 

Byte (байт) 
1 
от 0 до 255 

Boolean (логический) 

2 
true или False 

Integer  (целое) 
2 
от –32768 до 32767 

Long (длинное  
целое) 

4 
от –2147483648 до 2147483647 

Single (с плавающей точкой обычной точности) 

4 
от –3,402823 E+38 до –1,401298 E–45 
для отрицательных значений; 
от 1,401298 E-45 до 3,402823 E+38 
для положительных значений 

Double (с плавающей точкой 
двойной точности) 

8 
от –1,79769313486232 E+308  
до –4,94065645841247 E-324  
для отрицательных значений; 
от 4,94065645841247 E–324  
до 1,79769313486232 E+308  
для положительных значений 

9

1. Основы работы с VBA в Microsoft Excel 97 

Продолжение таблицы 

Название 
Размер, 
байт 

Диапазон значений 

Currency  
(денежный) 

8 
от –922337203685477,5808  
до 922337203685477,5807 

Decimal  
(масштабируемое 
целое) 

14 
+/–79228162514264337593543950335 
без дробной части;  
+/–7,9228162514264337593543950335 
с 28 знаками справа от запятой; минимальное ненулевое значение:  
+/–0,0000000000000000000000000001 

Date (даты и  
время) 

8 
от 1 января 100 г. до  
31 декабря 9999 г. 

Object (объект) 
4 
любой указатель объекта. 

String (строка переменной длины) 

10 + 
длина 
строки 

от 0 до приблизительно 2 миллиардов. 

String (строка постоянной длины) 

Длина 
строки 

от 1 до приблизительно 65400. 

Variant (числовые 
подтипы) 

16 
любое числовое значение вплоть до 
границ диапазона для типа Double. 

Variant (строковые 
подтипы) 

22 + 
длина 
строки 

как для строки (String) переменной 
длины. 

Тип данных, определяемый пользователем (с помощью ключевого 
слова Type) 

Объем 
определяется 
элементами 

диапазон каждого элемента определяется его типом данных. 

10