Книжная полка Сохранить
Размер шрифта:
А
А
А
|  Шрифт:
Arial
Times
|  Интервал:
Стандартный
Средний
Большой
|  Цвет сайта:
Ц
Ц
Ц
Ц
Ц

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

Покупка
Артикул: 752996.01.99
Доступ онлайн
2 000 ₽
В корзину
Учебное пособие предназначено для практических занятий по курсам «Информатика», «Прикладные компьютерные системы», «Моделирование и оптимизация технологических систем». Пособие содержит теоретическое описание численных методов, используемых при решении инженерных задач, алгоритмы их реализации, программное обеспечение этих методов на алгоритмическом языке VBA (Visual Basic for Application) для Excel 97, а также примеры и многовариантные задания. Пособие состоит из восьми тематических частей по основам численных методов и дополнительных сведений, посвященных программированию в VBA. Овладение этим материалом позволит научиться редактировать и самостоятельно составлять программный код макросов на языке VBA для всех приложений Office 97 (Excel, Word, Access и др.), а также AutoCad 14, 15, а в дальнейшем перейти непосредственно к визуальному, объектно-ориентированному программированию на алгоритмическом языке Visual Basic версий 5 и 6.
Галкин, С. П. Прикладные компьютерные системы. Раздел : решение задач численными методами в среде электронных таблиц Excel : учебное пособие / С. П. Галкин, В. К. Михайлов. - Москва : ИД МИСиС, 2001. - 208 с. - Текст : электронный. - URL: https://znanium.com/catalog/product/1232271 (дата обращения: 29.03.2024). – Режим доступа: по подписке.
Фрагмент текстового слоя документа размещен для индексирующих роботов. Для полноценной работы с документом, пожалуйста, перейдите в ридер.
Кафедра обработки металлов давлением 

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

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

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

Раздел: Решение задач численными методами в среде 
электронных таблиц 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 

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

Под массив любого типа данных необходимо выделить оперативную память, исходя из суммы следующих слагаемых: 
– 
20 байт на любой массив; 

– 
4 байта на каждую размерность массива; 

– 
число байт для хранения данных. 

Объем памяти, требуемый для сохранения данных, рассчитывается как 
произведение числа элементов на размер элемента. Например, данные в одномерном 
массиве, который содержит четыре элемента типа Integer, требующих по 2 байта на 
элемент, занимают 8 байт. Вместе с 20 байтами на массив и 4 байтами на размерность общий требуемый объем составляет 32 байта. 

Значение типа Variant, содержащее массив, требует 12 байт в дополнение к 
объему, требуемому массивом. 

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

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

1.2.1. Макрос 

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

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

Sub Макрос1() 

текст макроса 

End Sub 

11

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

Наличие пустого списка параметров позволяет VBA классифицировать эту программную единицу как макрос и представлять его 
в диалоговом окне Макрос (меню Сервис\Макрос\Макросы…). 

В тексте программы макрос может быть вызван двумя способами: 

1. Call Макрос1() 

или 

2. Макрос1 

1.2.2. Процедура 

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

Sub Фигура(Длина As Single, Ширина As Single,_ 
 Площадь As Single) 
   'Вычисление площади прямоугольника 
    Площадь = Длина * Ширина 
End Sub 

В данном примере за инструкцией Sub идет имя процедуры  
«Фигура», затем параметры: Длина, Ширина, Площадь с описанием 
типа этих параметров. 

В тексте программы процедура может быть вызвана двумя 
способами: 

1. Call Фигура(a, b, s) 

или 

2. Фигура a, b, s 

Параметры при вызове: a, b, s должны иметь тип, соответствующий параметрам передачи: Длина, Ширина, Площадь. 

Следует отметить, что процедуру нельзя запустить из диалогового окна Макрос, она может вызываться только в строках программного кода VBA. 

12 

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

1.2.3. Функция 

Программная единица функция возвращает при вызове единственное значение. Этим она отличается от процедур, которые могут 
возвращать одно и более значений. Функции могут быть встроенными в Excel или созданы пользователем. 

Следующая пользовательская функция принимает три аргумента, а возвращает результат, являющийся произведением первых 
двух параметров и суммой третьего: 

 Function ТриАргумента(x, y, z) 
  
ТриАргумента = x * y + z 

 End Function 

Вызов такой функции с помощью программного кода происходит следующим образом: 

 
Результат = ТриАргумента(x, y, z), 

где x, y, z – числа или переменные, имеющие числовые значения. 

В общем случае первая строка программного кода имеет вид: 

 
Function ТриАргумента(x As Тип1, y As Тип2, 
z As Тип3) As Тип4, 

где ТипN, N = 1, 2, 3 – это типы переменных и самой функции.  

При совместной работе VBA и Excel типы, как правило, не 
описывают. Они по умолчанию принимаются как Variant. Это связано 
с тем, что в VBA в качестве разделителя целой и дробной части числа 
используется точка, а в Excel – запятая. 

В программном коде можно использовать функцию Excel 
AVERAGE следующим образом: 

 
СреднееЗначение = Application.Average(12, 24, 8). 

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

Существует два способа создания макросов: 

– автоматический, при котором записывают последовательность 
своих действий с использованием макрорекордера; 

13

Доступ онлайн
2 000 ₽
В корзину