Язык программирования VBA
ФИО студента Курсовая работа по Информатике
Министерство образования и науки Российской Федерации
Федеральное государственное автономное образовательное учреждение высшего профессионального образования
«Уральский федеральный университет
имени первого Президента России Б.Н. Ельцина»
Курсовая работа
по дисциплине «Информатика»
Язык программирования VBA
Вариант 10
Студент:
Группа:
Преподаватель:
Екатеринбург
2012 г.
Содержание
Задание на курсовую работу
Теоретическая часть
Задание 1:
1.10 VBA. Объект Range. Свойства.
Задание 2:
Описать метод половинного деления и метод касательных. Провести сравнительный анализ.
Практическая часть
Заданы два уравнения. Необходимо каждое из них решить методом половинного деления и методом касательных согласно своему варианту. Проанализировать и сделать выводы.
Вариант |
Уравнение 1 |
Уравнение 2 |
10 |
ВВЕДЕНИЕ
Microsoft Excel имеет встроенный язык программирования - Visual Basic for Аpplications (VBA). Этот язык позволяет создавать приложения, выполняемые в среде Microsoft Office. Это могут быть разнообразные аналитические программы, финансовые системы, программы учета кадров, системы автоматического создания официальных писем/документов с помощью библиотеки готовых шаблонов и т.п. При этом интерфейс создаваемой программы может быть совершенно непохожим на интерфейс того приложения, в котором она написана.
VBA сочетает в себе практически
неограниченные возможности с
простотой изучения и
Цель курсовой работы.
Рассмотреть объект Range и его свойства. Выполнить сравнительный анализ методов решения уравнений.
Задачи курсовой работы.
Подобрать теоретический материал по свойствам объекта Range и методам решения уравнений (половинного деления и касательных). Выполнить расчеты в MS Excel используя встроенный язык программирования VBA и сделать анализ полученных результатов.
Курсовая работа состоит из двух глав. Первая глава содержит теоретический материал. Во второй главе приводится решение примеров методом половинного деления и методом касательных и выполнен анализ полученных результатов.
Глава 1. Теоретическая часть
1.1 Свойства объекта Range
Объект Range используется в программном коде VBA наиболее часто и он позволяет работать как с отдельными ячейками, так и диапазонами - строками, столбцами и двухмерными областями [1].
В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA.
Особенностью объекта Range является то, что он не является элементом никакого семейства объектов.
Доступ и работу с ячейками объект осуществляет с помощью своих свойств и методов.
Объект Range позволяет сочетать гибкость VBA и мощь рабочего листа Excel. Более 400 встроенных функций рабочего листа существенно упрощают и делают более наглядным программирование на VBA.
Наиболее часто используемые свойства и методы объекта Range.
Перечислим основные свойства объекта Range.
Value Возвращает значение из ячейки или в ячейки диапазона.
В данном примере переменной х присваивается значение из ячейки C1 :
х = Range ("C1") .Value
В следующем примере в диапазон AI : В2 введена 1 :
Range ("A1:B2") .Value = 1
Name Возвращает имя диапазона.
В данном примере диапазону А1:В2 присваивается имя итоги:
Range ( "Al :B2") .Name = "Итоги"
Count Возвращает число объектов в наборе.
В данном примере переменной х присваивается значение, равное числу строк диапазона AI : В2 :
х = Range ( "Al :B2") .Rows . Count
CurrentRegion Возвращает число строк текущего диапазона. Текущим является диапазон, ограниченный пустыми строками и столбцами и содержащий данный элемент.
В следующем примере переменной у присваивается значение, равное числу строк в текущем диапазоне, содержащем ячейку AI :
у = Range ( "Al" ). CurrentRegion. Rows . Count
WrapText Позволяет переносить текст при вводе в диапазон. Допустимые значения True и False.
В следующем примере в ячейку В2 вводится текст длинный текст и в этой ; ячейке устанавливается режим ввода текста с переносом:
With Range ("B2") .Value = "Длинный текст" .WrapText = True End With
EntireColumn, EntireRow Возвращает столбец и строку соответственно.
В данном примере очищается содержимое строки и выделяется столбец с активной ячейкой:
ActiveCell . EntireRow. Clear ActiveCell .EntireColumn. Select
ColumnWidth, RowHeight Возвращает ширину столбцов и высоту строк диапазона соответственно
Comment Возвращает объект comment (примечание), который связан с левым верхним углом диапазона при отображении на экране. Объект comment является элементом семейства comments. Метод AddComment, примененный к диапазону, создает новое примечание. Среди методов объекта comment отметим только метод Text, который задает текст, выводимый в примечании. Синтаксис:
Text (Text, Start, Overwrite)
- Text — строка, выводимая в качестве примечания П start — с какого символа вводится текст в уже существующее примечание. Если аргумент опущен, то из примечания удаляется весь ранее введенный текст П overwrite — допустимые значения: True (вводимый текст записывается поверх уже существующего) и False (вводимый текст вставляется в уже существующий) Среди свойств объекта Comment отметим только свойство visible, устанавливающее отображение примечания при активизации диапазона, имеющего определенное примечание. В качестве примера рассмотрим следующие инструкции, которые создают и отображают примечание ячейки В3, поясняющее запланированное событие:
With Range ("ВЗ")
.AddComment
.Text Text:= "Внимание!"
.Visible = True End With
Font Возвращает объект Font (шрифт). Объект Font имеет следующие свойства:
- Name — строка, указывающая имя шрифта, например "Arial Cyr"
- FontStyle — СТИЛЬ, возможен Regular (обычный), Bold (ПОЛУЖИРНЫЙ), Italic(курсив), Bold italic (полужирный курсив)
- size - размер
- strikethrough — допустимы два значения: True (буквы имеют линию по центру, как будто они перечеркнуты) и False (не имеют линии по центру)
- Superscript — допустимы два значения: True (текст используется как верхний индекс) и False (не используется как верхний индекс)
- Subscript — допустимы два значения: True (текст используется как нижний индекс) и False (не используется как нижний индекс)
- Underline-допустимыми являются значения:
- xlNone (нет подчеркивания)
- xlSingie (одинарное, по значению)
- xlDoubie (двойное, по значению)
- xlsingleAccounting (одинарное, по ячейке)
- Accounting (двойное, но ячейке)
Например, в следующем примере устанавливается для диапазона AI : в2 полужирный шрифт, красного цвета и с высотой символов 1 4 :
With Range ("A1:B2").Font
.Size = 14
.FontStyle = Bold
.Colorlndex = 3
End With
Formula Возвращает формулу в формате Al.
Например, следующая инструкция вводит в ячейку с2 формулу =$А$4+$А$10:
Range ("C2") . Formula = "=$А$4+$А$10"
FormulaArray 1 Возвращает формулу диапазона в формате А1. В отличие от обыкновенной формулы рабочего листа, формула диапазона вводится на рабочем листе не посредством нажатия на клавишу <Enter>, а с помощью комбинации клавиш <Ctrl>+<Shift>+<Enter>. Следующая инструкция вводит в диапазон Е1:ЕЗ формулу {=Sum(Al:A3*Bl:B3) }:
Range ( "El :E3") .FormulaArray = "=Sum(Al:A3*Bl:B3) "
FormulaHidden Допустимые значения: True (формула спрятана, если рабочий лист или книга защищены) и False (в противном случае).
Например, следующая инструкция скрывает формулы в столбце А: Columns ("A") . FormulaHidden = True
FormulaLocal Возвращает неанглоязычные (местные) формулы в формате А1.
Например, следующая инструкция вводит в ячейку В2 формулу =СУММ(С1:С4):
Range("B2"). FormulaLocal = "=СУММ (С1:С4) "
JormulaRlCl Возвращает формулу в формате R1C1.
Например,
Range ("Bl") . FormulaRlCl = "=SQRT (R3C2 ) "
FormulaRlClLocal Возвращает неанглоязычные формулы в формате R1C1
Text Возвращает содержание диапазона в текстовом формате
HorizontalAlignment Горизонтальное выравнивание. Допустимые значения:
- xlceneral (обычное выравнивание, зависящее от типа вводимых значений)
- xlcenter (выравнивание по центру)
- xlRight (выравнивание по правому краю)
- xlLeft (выравнивание по левому краю)
- xUustify (выравнивание по ширине)
- xlCenterAcrossSelection (выравнивание по центру в выделенном диапазоне)
- xlFill (выравнивание по ширине
Vertical Alignment Вертикальное выравнивание. Допустимые значения:
- xlBottom (выравнивание по нижнему краю),
- xlcenter (выравнивание по центру),
- xUustify (выравнивание по высоте),
- xlTop (выравнивание по верхнему краю)
Orientation Ориентация. Допускается либо угол поворота текста в градусах от —90° до 90°, либо одно из допустимых значений:
- xlDownward (выравнивание по левому краю сверху вниз, соответствует углу —90°)
- xlHorizontal(выравнивание по горизонтали, соответствует нулевому углу )
- xlupward (выравнивание по правому краю снизу вверх, соответствует углу 90°)
- xlvertical (выравнивание по вертикали, нет соответствия в градусах)
ShrinkToFit Допустимые значения: True (автоматическое изменение шрифта так, чтобы текст помещался в ячейку) и False (в противном случае)
1.2 Метод половинного деления
1.2.1 Описание метода половинного деления
Метод половинного деления один из методов решения нелинейных уравнений и основан на последовательном сужении интервала (рис.1), содержащего единственный корень уравнения F(x)=0 до того времени, пока не будет достигнута заданная точность е. Метод используется при решении квадратных уравнений и уравнений высших степеней [3,7].
1.2.2 Алгоритм метода половинного деления
Пусть задан отрезок [а,b], содержащий один корень уравнения. Этот отрезок может быть предварительно найден с помощью шагового метода.
- Определить новое приближение корня х в середине отрезка [а,b]: х=(а+b)/2.
- Найти значения функции в точках а и х: F(a) и F(x).
- Проверить условие F(a)*F(x) < 0. Если условие выполнено, то корень расположен на отрезке [а,х]. В этом случае необходимо точку b переместить в точку х (b=х). Если условие не выполнено, то корень расположен на отрезке [х,b]. В этом случае необходимо точку а переместить в точку х (а=х).
- Перейти к пункту 1 и вновь поделить отрезок пополам. Алгоритм продолжить до того времени, пока не будет выполнено условие |F(x)| < e .
Рисунок. 1. Иллюстрация метода половинного деления
Достоинство метода половинного деления: более быстрая сходимость к заданной точности, чем у шагового. Недостаток: если на отрезке [а,b] содержится более одного корня, то метод не работает.
1.3 Метод касательных
1.3.1 Описание метода касательных
Задан отрезок [а,b], содержащий корень уравнения F(x)=0. Уточнение значения корня производится путем использования уравнения касательной. В качестве начального приближения задается тот из концов отрезка [а,b], где значение функции и ее второй производной имеют одинаковые знаки (т.е. выполняется условиеF(x0)*F"(x0) > 0).
В точке F(x0) строится касательная к кривой у = F(x) и ищется ее пересечение с осью х. Точка пересечения принимается за новую итерацию. Метод касательных самый быстрый способ нахождения корней уравнений [3,7].
Итерационная формула имеет вид:
Итерационный процесс проходит до того времени, пока не будет выполнено условие |f(x)|< e , где e - заданная точность.
В данном случае вторая производная функции положительна, поэтому в качестве начального приближения выбрана точка хо = b. Как видно из рисунка 2, метод имеет очень быструю сходимость среди всех методов решения нелинейных уравнений: обычно заданная точность достигается за 2-3 итерации.
Рисунок.2. Иллюстрация метода касательных
1.3.2 Алгоритм метода касательных
- Задается начальное приближение x0.
- Пока не выполнено условие остановки, в качестве которого можно взять или (то есть погрешность в нужных пределах), вычисляют новое приближение: .
Сравнительный анализ методов
К плюсам метода половинного деления конечно стоит отнести его простоту. Им легко вычислять как аналитически, так и программно. К минусам нужно отнести затраты на приведенные итерации, по сравнению с методом хорд и касательных на пример. К плюсам метода касательных относится быстрота нахождения и меньшая затратность на приведенные итерации.
Глава 2. Практическая часть
2.1 Решение уравнений
В приложении 1 приведен листинг модуля на VBA решения примера 1 методом половинного деления (дихотомии). В приложении 2 приведен листинг модуля на VBA решения примера 1 методом касательных. В приложении 3 приведен листинг модуля на VBA решения примера 2 методом половинного деления (дихотомии). В приложении 4 приведен листинг модуля на VBA решения примера 2 методом касательных.
2.2 Анализ полученных решений уравнений
Пример 1
Результаты решения примера приведены в таблице 1.
Таблица 1 – Результаты решения примера 1
Интервалы | ||||||
(-6;-5) |
(-0,5;1) |
(1;2) | ||||
значение корня |
количество итераций |
значение корня |
количество итераций |
значение корня |
количество итераций | |
метод дихотомии |
-5,32 |
11 |
0 |
12 |
1,58 |
11 |
метод касательных |
-5,32 |
3 |
0 |
1 |
1,58 |
4 |
Пример 2
Результаты решения примера приведены в таблице 2.
Таблица 2 – Результаты решения примера 2
Интервалы | ||||
(-1;-0) |
(1;2) | |||
значение корня |
количество итераций |
значение корня |
количество итераций | |
метод дихотомии |
-0,72 |
11 |
1,22 |
11 |
метод касательных |
-0,72 |
4 |
1,22 |
5 |
Вывод: Утверждение, что метод касательных имеет очень быструю сходимость по сравнению с методом половинного деления, подтверждается при решении данных примеров. В таблицах приведены значения корней и количество итерация, необходимых для сходимости методов. Видим, что для метода касательных количество итераций намного меньше потребовалось.
Заключение
Еще не так давно упоминание Basic (Бейсик) в качестве языка программирования вызывало лишь снисходительную усмешку (а порой и негодование) у большинства профессиональных разработчиков ПО. Объяснялось это просто: "Да, есть такой язык, которым пользуются любители (если не сказать "чайники"). Но ставить "Васик" в один ряд с настоящими средствами программирования - это просто смешно".
Сейчас вряд ли кто-то рискнет высказать такое мнение: за прошедшие годы Microsoft Visual Basic (VB) превратился в самый популярный в мире инструмент разработки приложений. Более того, знание его основ сегодня фактически является обязательным для всех программистов, какими бы средствами они не пользовались. Это объясняется двумя взаимосвязанными факторами. Во-первых, VB очень широко распространен и используется не только в качестве самостоятельного средства, но и в виде системы программирования, встроенной в многочисленные прикладные программы (в частности MS Office). Во-вторых, превратившись в серьезный профессиональный инструмент, VB остался очень удобным средством для обучения программирования и решения небольших задач.
В результате выполнения курсовой работы были решены следующие задачи: закрепление и углубление теоретических знаний по разделам информатики; овладение навыками самостоятельной работы (решение примеров с помощью встроенного языка программирования VBA в MS Excel). Выполнен анализ полученных решений и сделан вывод по сравнению методов решения уравнений – метод касательных имеет очень быструю сходимость по сравнению с методом половинного деления.
Список литературы
- VBA в Excel http://rrc.dgu.ru/res/
informat/ - Википедия - Свободная энциклопедия http://ru.wikipedia.org/wiki
- Воробьев Г. Н., Данилова А. Н. “Практикум по численным методам.” - М.:”Высш. шк.”, 2007 г. -184 с.
- Гутер Р.С. , Овчинский Б.В. «Элементы численного анализа и математический обработки результатов опыта». Москва, «Наука», 1979
- Джон Уокенбах. Профессиональное программирование на VBA в Excel. Диалектика. М., С-Пб., Киев, 2003.
- Информатика: Учебник Под ред. проф. Н.В. Макаровой. Москва. Издательство "Финансы и статистика". 2005.
- Калиткин Н.Н. «Численные методы». Москва, «Наука», 1978
- Костомаров Д.П., Корухова Л.С., Манжелей С.Г. Программирование и численные методы. - М.: Издательство МГУ, 2001.
- Крылов В.И., Бабков В.В., Монастырский П.И. «Вычислительные методы». Москва, «Наука», 1976
Приложение 1
Решение примера 1 методом половинного деления
Sub dihotomia()
Dim a As Single: Dim b As Single: Dim c As Single: Dim e As Single: Dim kol As Integer
a = Val(InputBox("Введите левый конец интервала a"))
b = Val(InputBox("Введите правый
e = Val(InputBox("Введите точность e"))
kol = 1: c = (a + b) / 2
Do While Abs(b - a) > e And f(c) <> 0
c = (a + b) / 2
If f(c) * f(a) < 0 Then b = c Else a = c
kol = kol + 1
Loop
MsgBox "корень уравнения x=" & Format(c, "0.##") & "количество итераций-" & kol
End Sub
Function f(x As Single) As Single
f = (x - 1) ^ 2 * 2 ^ x - 1
End Function
Приложение 2
Решение примера 1 методом касательных
Sub kasatelnaya()
Dim a As Single: Dim b As Single: Dim c As Single: Dim e As Single: Dim x0 As Single: Dim x1 As Single
Dim kol As Integer
a = Val(InputBox("Введите левый
b = Val(InputBox("Введите правый конец интервала b"))
e = Val(InputBox("Введите точность e"))
If f(a) * f3(a) >= 0 Then x0 = a Else If f(b) * f3(b) >= 0 Then x0 = b Else MsgBox "не верный ввод данных"
kol = 0
10:
kol = kol + 1
x1 = x0 - f(x0) / f2(x0)
If Abs(x1 - x0) > e Then x0 = x0 - f(x0) / f2(x0): GoTo 10 Else MsgBox "корень уравнения x=" & Format(x1, "0.##") & "количество итераций - " & kol
End Sub
Function f(x As Single) As Single
f = (x - 1) ^ 2 * 2 ^ x - 1
End Function
Function f2(x As Single) As Single
f2 = 2 ^ x * (2 * x - 2 + (x - 1) ^ 2 * Log(2))
End Function
Function f3(x As Single) As Single
f3 = (2 + 2 * Log(2) * (x - 1)) * 2 ^ x + 2 ^ (2 * x) * Log(2) * (2 * x - 2 + (x - 1) ^ 2 * Log(2))
End Function
Приложение 3
Решение примера 2 методом половинного деления
Sub dihotomia2()
Dim a As Single: Dim b As Single: Dim c As Single: Dim e As Single
Dim kol As Integer
a = Val(InputBox("Введите левый
b = Val(InputBox("Введите правый конец интервала b"))
e = Val(InputBox("Введите точность e"))
kol = 1
c = (a + b) / 2
Do While Abs(b - a) > e And f2(c) <> 0
c = (a + b) / 2
If f2(c) * f2(a) < 0 Then b = c Else a = c
kol = kol + 1
Loop
MsgBox "корень уравнения x=" & Format(c, "0.##") & "количество итераций-" & kol
End Sub
Function f2(x As Single) As Single
f2 = x ^ 4 - x - 1
End Function
Приложение 4
Решение примера 2 методом касательных
Sub kasatelnaya2()
Dim a As Single: Dim b As Single: Dim c As Single: Dim e As Single: Dim x0 As Single: Dim x1 As Single
Dim kol As Integer
a = Val(InputBox("Введите левый
b = Val(InputBox("Введите правый конец интервала b"))
e = Val(InputBox("Введите точность e"))
If f(a) * f3(a) >= 0 Then x0 = a Else If f(b) * f3(b) >= 0 Then x0 = b Else MsgBox "не верный ввод данных"
kol = 0
10:
kol = kol + 1
x1 = x0 - f(x0) / f2(x0)
If Abs(x1 - x0) > e Then x0 = x0 - f(x0) / f2(x0): GoTo 10 Else MsgBox "корень уравнения x=" & Format(x1, "0.##") & "количество итераций - " & kol
End Sub
Function f(x As Single) As Single
f = x ^ 4 - x - 1
End Function
Function f2(x As Single) As Single
f2 = 4 * x ^ 3 - 1
End Function
Function f3(x As Single) As Single
f3 = 12 * x ^ 2
End Function