- •Программирование в excel
- •Симферополь 2000
- •Факультет "Механизация сельского хозяйства" Программирование в excel
- •1. Основы программирования на vba
- •1.1. Создание, редактирование и запись программ
- •1.1.1. Запись макроса
- •1.1.2. Как найти макрос в проекте
- •1.1.3. Написание новой процедуры
- •Чем макрос отличается от процедуры
- •1.1.4. Процедуры типа Sub и Function
- •1.1.5. Закрытые и открытые процедуры
- •1.1.6. Использование значения, возвращаемого функцией
- •1.1.7. Выполнение процедуры Sub
- •1.1.8. Передача аргументов в процедуру
- •1.1.9. Именованные аргументы
- •1.1.10. Написание процедур для обработки событий
- •1.1.11. Где хранится код обработки события
- •1.1.12. Средства, ускоряющие написание программ
- •1.1.13. Как написать легкочитаемую программу
- •1.2. Переменные, константы и типы данных
- •1.2.1. Типы данных в Visual Basic
- •1.2.2. Объявление константы, переменной или массива
- •1.2.3. Объявление объектной переменной
- •1.2.4. Встроенные константы
- •1.3. Управляющие конструкции
- •1.3.1. Операторы ветвления
- •1.3.2. Операторы циклов
- •1.3.3. Вложение управляющих конструкций
- •1.3.4. Выход из циклов и процедур
- •1.4. Структура программы
- •2. Сортировка данных
- •2.1. Алгоритм сортировки обменами (алгоритм “пузырька”)
- •2.2. Алгоритм сортировки вставками
- •2.3. Алгоритм сортировки выбором элемента
- •2.4. Алгоритм быстрой сортировки (метод Хоора)
- •2.5. Алгоритм пирамиды (метод Уильямса-Флойда)
- •2.6. Учебные задачи по программированию сортировки данных
- •3. Работа vba с объектами Excel
- •3.1. Как получить справку по Visual Basic для Microsoft Excel
- •3.2. Объекты Microsoft Excel
- •3.3. Работа с объектом Application
- •3.4. Работа с объектом Workbook
- •3.4.1. Открытие рабочей книги
- •3.4.2. Закрытие рабочей книги
- •3.4.3. Создание и сохранение рабочей книги
- •3.5. Работа с объектом Range
- •3.6. Строковые ссылки в стиле а1 или имена диапазонов
- •3.6.1. Числовые индексы строк и колонок
- •3.6.2. Свойство Offset
- •3.6.3. Свойства CurrentRegion и UsedRange
- •3.6.4. Организация циклов для перебора ячеек диапазона
- •3.6.5. Применение свойства Address для отладки кода, работающего с объектом Range
- •3.7. Работа с событиями
- •3.7.1. Включение и отключение обработки событий
- •3.7.2. Использование событий, связанных с рабочими листами
- •3.7.3. События на уровне рабочего листа
- •3.7.4. События на уровне диаграммы
- •3.7.5. События на уровне рабочей книги
- •3.7.6. События на уровне приложения
- •3.7.7. Модули классов и события
- •4. Численные методы математики
- •4.1. Методы решения нелинейных уравнений
- •4.1.2. Метод деления отрезка пополам (метод дихотомии).
- •4.1.3. Метод Ньютона (касательных).
- •4.1.4. Метод хорд (секущих).
- •4.1.5. Метод итераций (метод последовательных приближений).
- •4.2.1. Теоретические сведения
- •4.2.2. Метод Крамера
- •4.2.3. Метод Гаусса
- •4.2.6. Метод Зейделя
- •4.3. Обработка экспериментальных данных
- •4.3.1. Задачи, которые возникают при обработке экспериментальных данных.
- •4.3.2. Интерполяция
- •4.3.2.1. Интерполяция функций
- •4.3.3.2. Определение параметров эмпирической формулы
- •4.4. Методы численного интегрирования
- •4.4.1. Метод трапеций
- •4.4.2. Метод Симпсона
- •4.4.3. Оценка точности формул численного интегрирования. Выбор шага интегриров-ания
- •4.4.3.1. Выбор шага интегрирования по оценке остаточного члена (ошибки)
- •4.4.3.2. Выбор шага интегрирования с помощью двойного пересчета
- •4.5.1. Теоретические сведения
- •4.5.2. Одноступенчатые методы
- •4.5.2.1. Решение с помощью рядов Тейлора
- •4.5.2.2 Метод Эйлера
- •4.5.2.3. Модифицированный метод Эйлера
- •4.5.2.4. Метод Эйлера-Коши
- •4.5.2.5 Метод Рунге-Кутта
- •4.5.3. Многоступенчатые методы
- •4.5.3.1. Методы прогноза и коррекции
- •4.6. Методы решения линейной краевой задачи для обыкновенных дифференциальных уравнений
- •4.6.1. Постановка задачи
- •4.6.2. Метод конечных разностей
- •4.6.3. Метод прогонки
- •4.6.4. Алгоритм решения краевой задачи методом прогонки.
4.2.2. Метод Крамера
Значения неизвестных xi (i=1,2,...,n) могут быть получены по формулам Крамера:
xi=detAi/detA ,
где матрица Аi определяется из матрицы А заменой ее i-того столбца столбцом свободных членов. Такой способ решения линейных систем с n-неизвестными приводит к вычислению n+1 детерминантов порядка n, что очень трудоемко при достаточно большом числе n.
4.2.3. Метод Гаусса
Наиболее распространенным методом решения систем алгебраических уравнений является метод Гаусса, в основе которого лежит идея последовательного исключения неизвестных. Существуют разные вычислительные схемы, которые реализуют этот метод. Рассмотрим одну из них - схему единичного деления.
Пусть задана система линейных уравнений n-го порядка, детерминант которой отличный от нуля. Предположим, что матрица коэффициентов не имеет нулевых диагональных элементов. Если такие имеются, то соответствующей перестановкой строки их всегда можно сделать ненулевыми.
Метод Гаусса заключается в следующем:
1. Из всех уравнений, кроме первого, исключаются члены, которые содержат x1. Для этого из второго, третьего,....., n-го уравнений системы почленно, включая правые части, отнимается первое уравнение, разделенное на а11 и умноженное соответственно на а21, а22, а23,.....,аn1. В результате этой операции порядок всех уравнений, за исключением первого, понижается на единицу.
2. Вновь полученное второе уравнение делится на а22 и аналогичным способом, начиная с третьего уравнения, исключаются все элементы, которые содержат х2.
3. Повторяем эту процедуру n-1 раз, то есть каждый раз исключаем неизвестные из ниже расположенных уравнений. В результате получаем ступенчатую треугольную систему уравнений, эквивалентную начальной, в которой последнее уравнение получает только одну неизвестную.
4. Решение ступенчатой системы уравнений осуществляется путем последовательного расчета неизвестных, начиная с последнего уравнение.
Пример
Найти корни следующей системы уравнений:
2.0*x1+1.0*x2-0.1*x3= 3.7
0.4*x1+0.5*x2-4.0*x3=13.4
0.3*x1-1.0*x2+1.0*x3= 1.3
Для решения системы используем метод Гаусса. Из второго и третьего уравнений исключим члены, которые содержат х1. Для этого сначала поделим первое уравнение на а11=2.0. Получим:
х1+0.5*x2-0.05*x3=1.85
Потом умножим полученное уравнение на а21 и а31, отнимем его из второго и третьего уравнений соответственно. Таким образом, получим систему с двумя неизвестными:
0.3*x2+4.02*x3=12.66
1.15*x2+1.015*x3=0.745
Поделив первое уравнение полученной системы на а22 и умножив его на а32, отнимем его из второго уравнения этой системы.
16.425*x3=49.275
Таким образом, эквивалентная система имеет вид:
x1+0.5*x2-0.05*x3=1.85
x2+13.4*x3=42.2
16.425*x3=49.275
Из полученной эквивалентной системы последовательно найдем:
x3=3.0
x2=42.2-13.4*3.0=2.0
x1=1.85-0.5*2.0+0.05*0.3=1.0.
Процесс построения эквивалентной треугольной системы называется прямым ходом, а процесс нахождения значений неизвестных - обратным ходом.
4.2.4. Блок-схема программы для решения систем линейных уравнений методом Гаусса
Блок-схему программы можно поделить условно на трех части.
Первая - реализует алгоритм исключения неизвестных; вторая - обратную подстановку; третья - определяет наибольший коэффициент при х и осуществляет перестановку уравнений в случае необходимости. В блок-схеме все отношения обозначаются одним символом m. Если вычисления в программе организованы верно, то каждый этап вычислений требует не более одного отношения. При анализе блок-схемы надо четко различать смысл индексов i, j, k: k - определяет номер того уравнения, которое отнимается от последних, а также номер того неизвестного, которое исключается из последних n-k уравнений, i - означает номер уравнения, из которого в данный момент исключается неизвестная, j - означает номер столбца.
Блок-схема последовательного исключения неизвестных показана на рис.4.8. Обратная подстановка для поиска значений неизвестных задается следующими формулами:
xN=bN(N-1)/aNN(N-1)
xN-1=[bN-1(N-2)-aN-1, N(N-2)*xN]/an-1, n-1(N-2)
..................................
xj=(bj(j-1)-ajn(j-1)*xn-...-ajj(j-1)*xj+1]/ajj (j-1)
для j=n-2, n-3, ..., 1.
Блок-схема обратной подстановки показана на рис.4.9. Заметим, что в блок-схеме (рис.4.8) все индексы в процессе вычислений увеличиваются, а в блок-схеме (рис.4.9) один из индексов, а именно i, уменьшается. Третья блок-схема перестановки уравнений (рис.4.10) входит в блок-схему (рис.4.8). Задача нахождения наибольшего коэффициента при xk и перестановки уравнений при необходимости связана с уменьшением ошибки округления, которая возникает при расчетах с числами с плавающей запятой. Во-первых, перестановка может быть нужна, чтобы получить akk0 и уклониться от деления на 0. А также при использовании перестановки возможно уменьшение ошибки округления.
Рис.4.8 Рис.4.9
Сначала рассмотрим числовой пример.
Решим систему методом последовательного исключения:
3.241*100*x1+1.600*102*x2=1.632*102
1.020*104*x1+1.540*103*x2=1.174*104
Точное решение этой системы следующее:
x1=1.000*100;
x2=1.000*100;
Будем решать эти уравнения методом исключение в том порядке, в котором они записаны, используя при вычислении числа с 4 значащими цифрами в мантиссе. Так как а110, то первое и единственное отношение:
m=1.020*104/(3.241*100))=3.147*103
Превращенное второе уравнение имеет вид:
5.730*10(-1)*x1-5.020*105*x2=-5.019*105
Коэффициент при х1 должен был равняться 0, но ошибка округления не разрешает получить точный результат. Так как этот коэффициент не участвует в дальнейших вычислениях, то принимаем его равным 0 и образовываем обратную подстановку:
x 2=-5.019*105/(-5.020*105)=9.998*10(-1)
Из первого получаем: x1=9.873*10(-1)
Подставим уравнение так, чтобы максимальный по модулю элемент попал
на главную диагональ:
1.020*104*x1+1.540*103*x2=1.174*104
3.241*100*x1+1.600*102*x2=1.632*102
Теперь отношение
m=3.241*100/(1.020*104)=3.177*10(-4)
Преобразованное второе уравнение:
0.000*x2+1.595*102*x2=1.595*102
Откуда x2 = 1.000*100 ; x1=1.000*100
В этом примере коэффициенты очень отличаются по величине, но чаще эти различия не так большие. Однако при решении большой системы уравнений ошибки округления могут накапливаться, и
ответы могут не содержать ни одной верной Рис.4.10
цифры. Поэтому при решении СЛР на ЭВМ
методом исключения должно предусмотреть возможность перестановки уравнений.
Итак, задача сводится к перестановке n-k+1 последних уравнений так, чтобы наибольший по модулю коэффициент при xk попал на главную диагональ. Описанный способ решения СЛР называют методом главного элемента. При рассмотрении блок-схемы необходимо помнить, что при переходе к данному этапу расчетов индекс k имеет некоторое определенное значение, а i = k+1.
1. Сначала в программу вводится вспомогательный индекс l, которому предоставляется значение k.
2. Первое сравнение выполняется между аk,l-м элементом, который лежит на главной диагонали, и следующим за ним ai,k. Если |aik|>|alk|, то индексу l дают значение i, и дальнейшее сравнение выполняется со следующим элементом. Поэтому индекс l является номером максимального по модулю элемента. Индекс i пробегает по ходу программы значения от k+1 по n включительно, и в конце этого цикла индекс l определяет номер наибольшего по модулю элемента в k-ом столбце. При перестановке уравнения этот коэффициент должен попасть на главную диагональ. Может случится так, что значение аik уже является наибольшим по модулю элементом. Поэтому проверка выполняется сразу, и в таком случае перестановка не проводится. Фактически процесс состоит в перестановке коэффициентов, один из которых берется в уравнении k, а второй -в уравнении l, каким бы ни было значения l. Перестановку можно провести с помощью трехступенчатого процесса. Эту перестановку необходимо произвести для этих паров коэффициентов двух уравнений:
DO j=k TO N; TEMP=A(k,j); A(k,j)=A(l,j); A(l,j)=TEMP; END;
Аналогично для свободных членов b. Перед возвращением к основному процессу вычислений необходимо восстановить значение индекса i, которое было сначала и использовалось при сравнении. Так как перед началом процесса индекс i имел значения k+1, то в конце блока ему присваивается предшествующее значение i=k+1.
4.2.5 Метод простой итерации
При большом числе неизвестных линейной системы схема метода Гаусса, которая дает верное решение, становится очень громоздкой. В таком случае для поиска корней системы иногда лучше пользоваться приближенными методами вычислений - итерационными. Итерационные методы притягивают своей самонастраиваемостью и простотой реализации на ЭВМ. Пусть дана система линейных алгебраических уравнений (4.7) с неособенной матрицей. Чтобы применить к ней метод итераций, ее необходимо привести к виду:
X=CX+f, |
(4.8) |
где С - некоторая матрица, а f - вектор-столбец.
Для системы уравнений вида:
АX=В |
|
положим, что диагональные элементы матрица А аii0.
В системе выразим Х1 через 1-е уравнение, Х2 - через 2-е и т.д.:
Обозначим bi/aii = f, -aij/aii = Cij, тогда
X1=f+C12X2+C13X3 +C14X4 +…+C1nXn. или:
X=CX+f,
то есть уравнение (4.8).
Итерационный процесс для начала вычислений требует задание начальных условий. Итерационный процесс строится от начального вектора
.
X(k+1) =СХ(k)+f (k=0,1,2,....) |
(4.9) |
Сделав итерацию, получим последовательность векторов x(1), x(2) ,x(3) ,... x(k). Если последовательность приближений имеет границу, то эта граница есть решением системы (4.7):
(4.10)
Итерационный процесс заканчивается, если |x(k+1)-xk|<, где - заданная точность решения. Процесс итераций (4.9) хорошо сходится (то есть количество приближений, необходимых для получения корней системы (4.7) с заданной точностью, небольшое), если модули диагональных коэффициентов системы (4.7) больше суммы модулей оставшихся коэффициентов уравнения (свободные члены не учитываются). Это условие является условием сходимости. Отметим одну из важных особенностей метода итераций. Сходящийся процесс итераций владеет свойствами самоисправления, то есть отдельная ошибка в вычислениях не отражается на окончательном результате, так как ошибочное приближение можно рассматривать как новое начальное приближение. Начальный вектор x0 может быть выбран свободно. Иногда берут x0=f, чаще в качестве компонент вектора x0 берут приближенные значения неизвестных, полученные грубой прикидкой. Чем более близко начальное приближение к значениям корней системы, тем скорее сойдется итерационный процесс. Если матрица А неособенна, то систему (4.7) с помощью совокупности элементарных преобразований всегда можно привести к виду (4.8).
К элементарным преобразованиям матриц относятся:
а) замена строк (столбцов);
б) умножение всех элементов какой-нибудь строки (столбца) на одну и ту же величину, отличную от нуля;
в) прибавление к элементам какой-нибудь строки (столбца), соответствующих элементов второй строки, столбца, умноженных на одну и ту же величину.
Практически действуют следующим образом. Из заданной системы выделяют уравнения с коэффициентами, модули которых больше суммы остальных коэффициентов уравнения. Каждое выделенное уравнение выписывают в такую строку новой системы, чтобы наибольший по модулю коэффициент оказался диагональным. Потом используют элементарные преобразования, составляют оставшиеся уравнения новой системы. При этом необходимо, чтобы были использованы в том или ином соотношении все уравнения начальной системы.
Пример
Для нахождения коэффициентов эмпирической зависимости необходимо решить следующую систему уравнений:
2*x1-0.5*x2+4*x3=22 (A)
3*x1+3.5*x2+3*x3=35 (Б)
x1+0.5*x2-x3=-1 (B)
В уравнении (A) коэффициент при х3 по модулю больший суммы модулей остальных коэффициентов, поэтому можно принять это уравнение как третье уравнение новой системы. Чтобы получить второе уравнение с максимальным по модулю коэффициентом при х2, достаточно составить разность (Б)-(A):
х1+4*x2-x3=13.
При преобразовании начальной системы к виду (4.8) были использованы уравнения (A) и (Б). Тогда, в первое уравнение обязательно должно войти уравнение (В), например, используя следующее преобразование (А)+4*(В):
6*x1+1.5*x2+0*x3=18.
Таким образом, получена эквивалентная система уравнений, которая удовлетворяет условиям сходимости:
6*x1+1.5*x2=18 (А1)
x1+4*x2-x3=13 (Б1)
2*x1-0.5*x2+4*x3=22 (В1)
Решая первое уравнение относительно х1, второе - относительно х2, третье - относительно х3, получим:
x1=3-0.25*x2
x2=3.25-0.25*x1+0.25*x3 (4.11)
x3=5.5-0.5*x1+0.125*x2
В качестве начального вектора x0 возьмем элементы столбца свободных членов, округляя их значения до одного знака после запятой: x10=3; x20=3.3; x30=5.5. Точность вычисления =0.01. Последовательно вычисляем:
при k=1
x1(1)=3-0.25*3.3=2.175
x2(1)=3.25-0.25*3+0.25*5.5=3.875
x3(1)=5.5-0.5*3+0.125*3.3=4.413
при k=2
x1(2)=3-0.25*3.875=2.031
x2(2)=3.25-0.25*2.175+0.25*4.413=3.810
x3(2)=5.5-0.5*2.175+0.125*3.875=4.897 и т.д.
Результаты дальнейших расчетов сведем в таблицу.
K |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
X1 X2 X3 |
3 3,3 5,5 |
2,175 2,875 4,413 |
2,031 3,875 4,413 |
2,031 3,810 4,961 |
2,009 3,978 4,961 |
2,006 3,991 4,993 |
2,002 3,997 4,996 |
Так как модули различий значения x(k) при k=5 и k=6 составляют |x16-x15|=0.004, |x26-x25|=0.006, |x36-x35|=0.003, что меньше заданной точности , то в качестве решения принимаем: х1=2.002 ; х2=3.997 ; х3=4.9967.
Для сравнения точные значения неизвестных: х1=2; х22=4; х3=5.