- •Программирование в 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.3.3.2. Определение параметров эмпирической формулы
После того как вид эмпирической зависимости выбран, решается задача определения найлучших коэффициентов (параметров), которые находятся в этой формуле. Как правило, поиск параметров осуществляется для эмпирической формулы, которая приведена к линейному виду. В основном применяют три метода: метод избранных точек, метод средних и метод найменьших квадратов. Последний метод наиболее точный, но и наиболее громоздкий. Поэтому его используют при обработке исследований, которые требуют установления очень точных значений параметров.
Метод избранных точек.
Пусть эмпирическая формула имеет вид (4.17). Нужно найти значения коэффициентов А и В. Наносим на координатную плоскость исследовательские точки (xi, yi), как можно ближе к этим точкам проводим прямую (приблизительная прямая). На этой прямой выбираем две произвольных точки N1(x1", y1") и N2(x2", y2"), не обязательно совпадающие с точками (xi, yi) и как можно дальше отдаленные одна от одной. Координаты этих точек подставляем в уравнение (4.17), после чего получаем систему:
y1"=Ax1"+B
y2"=Ax2"+B
Решая систему, находим A и B.
Метод средних
Пусть эмпирическая формула имеет вид (4.17). Подставляем в эту формулу вместо X и Y исследовательские значения x и y. Так как левая часть формулы, как правило, не сравнивается с правой, получаем систему уравнений:
Аx1+B-y1=1; Ax2+B-y2=2; ........................... Axn+B-yn=n. |
(4.18) |
где 1, 2,..., n - несогласования (отклонения), которые могут быть как положительными, так и отрицательными. Согласно методу средних, наилучшей эмпирической зависимостью принимается та, которая обеспечивает нулевое значение суммы отклонений по все экспериментальным точкам, то есть алгебраическая сумма несогласований равняется нулю
. (4.19)
Определение параметров A и B формулы (4.17) выполняют следующим образом:
1. Составляют условные уравнения yi=Axi +B, число которых равняется числу собственных значений xі и yі.
2. Условные уравнения разделяют на приблизительно равные группы, число которых n равняется числу определяемых коэффициентов (в данном случае - 2).
3. Уравнения, которые входят в любую из этих групп, складываются. Для данного случая получаем два уравнения:
(4.20)
4. Из этих уравнений находят неизвестные коэффициенты A и B.
Группирование условных уравнений перед их суммированием можно провести разными способами, причем каждый из них даст несколько разных значений коэффициентов. Рекомендуется группировать уравнения по порядку.
Метод найменьших квадратов.
Эмпирическая формула в общем виде может быть записана так:
Y=F(xi, Aj) ,
где xi - независимые переменные; Aj - коэффициенты эмпирической зависимости.
В соответствии с методом найменьших квадратов наилучшими коэффициентами в смысле приближения к экспериментальным данным (yi=f(xi)), будут коэффициенты, отысканные с
, (4.21)
то есть минимумом суммы квадратов отклонений между экспериментальными и расчетными значениями.
При фиксированных значениях x функция R(Aj) есть положительно определенной (заданной и неперерывной в интервале [x1, xn]).
Пусть эмпирическая формула имеет вид:
F(xi, Aj) = a0+a1.xi+a2.xi2+...+am.xim
Тогда выражение (4.21) можно записать так:
(4.22)
Н езависимыми переменными для квадратичного функционала R(Aj) являются коэффициенты а0….аm. Минимума функционал достигнет в точке, где его частные производные по а0….аm будут равны 0. Действительно, зависимость R(Aj) описывается положительно определенной квадратичной параболой, имеющий экстремальную точку (в которой производная =0) – минимум. Например, при использовании в выражении (4.22) только коэффициентов а0 и а1 (в этом случае аппроксимирующей зависимостью будет прямая линия) функционал R(Aj) представляет собой параболоид вращения
Найдем частные производные функции R(a0, a1,...,am) по a0, a1,...,am и приравняем их к нулю. Получим так называемую нормальную систему m+1 уравнений с m+1 неизвестными a0, a1,...,am:
(4.23)
Решив систему известными методами (формулы Крамера, метод Гаусса и прочие), найдем коэффициенты a0, a1,..., am формулы (4.22), которая будет иметь найменьшее квадратичное отклонение R(Aj). На практике, как правило, при определении коэффициентов с использованием метода найменьших квадратов любую эмпирическую зависимость целесообразно привести к линейному виду. Рассмотрим получение системы нормальных уравнений для этого случая. Нужно определить коэффициенты эмпирической формулы:
F(xi, Aj)=a0+a1xi |
(4.24) |
Тогда выражение (4.22) будет иметь вид:
(4.25)
Нормальная система для определения a0 и a1 будет иметь такой вид:
Сделав простые преобразования, имеем:
(4.26)
Решив систему (4.26), получим значения a0 и a1. Подставим их в формулу (4.24), и получим конкретный вид эмпирической формулы.
Для аппроксимации квадратичной параболой эмпирическая формула имеет вид:
F(xi, Aj) = a0+a1.xi+a2.xi2.
Квадратичный функционал:
Частные производные:
Преобразуем полученную систему уравнений:
.
Преобразованная система – система линейных алгебраических уравнений, решить которую можно методом Гаусса.