zaeto.ru

Методические указания по применению

Другое
Экономика
Финансы
Маркетинг
Астрономия
География
Туризм
Биология
История
Информатика
Культура
Математика
Физика
Философия
Химия
Банк
Право
Военное дело
Бухгалтерия
Журналистика
Спорт
Психология
Литература
Музыка
Медицина
добавить свой файл
 

 
страница 1




Кемеровский Государственный Университет
Кафедра вычислительной математики

О.Н. Гавришина, М.Р. Екимова



Методические указания по применению

MS EXCEL и VBA в численных методах

Учебное пособие



Кемерово 2002
УДК 681.3.6

Методические указания по применению MS EXCEL и VBA в численных методах.

Учебное пособие




Утверждено методической комиссией математического факультета




Утверждено на заседании кафедры вычислительной математики

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

Пособие подготовлено старшим преподавателем кафедры вычислительной математики О.Н. Гавришиной и ассистентом кафедры вычислительной математики М.Р. Екимовой.
Кемеровский Государственный Университет

Содержание




Тема 1. Программирование в Excel. 4

Тема 2. Работа с массивами 13

Тема 3. Подбор и поиск решения 18

Тема 4. Построение форм 19

Тема 5. Задания по численным методам 24




Тема 1.Программирование в Excel.


Функциия пользователя. Одним из способов ввода функции является объявление функции на языке VBA (Visual Basic for Application). Для этого откроем окно редактора VBA через меню Сервис, Макрос, Редактор Visual Basic (или комбинацией клавиш +).

Вся книга Excel вместе с макросами и листами представляет собой проект (VBAProject). Рабочая книга- это ThisWorkBook, текущий лист – это WorkSheet, все процедуры и функции проекта содержатся в модулях.

Макрос в VBA - это процедура без параметров со структурой:
Sub Макрос1()

операторы

End Sub
Функция в VBA представляет собой следующую структуру:
Function Имя(параметры)

операторы

End Function
Чтобы написать эти процедуры и функции, нужно вставить новый модуль (Вставка, Модуль), Затем вставляем процедуру (Вставка, Процедура), указав в диалоговом окне «Вставка процедуры» имя, тип процедуры и область определения. После этого в окне Модуль1 появится готовая структура.

Запишем функцию F(x) и процедуру Create(), которая будет выводить в ячейки на текущем листе значения функции и ее аргументы по заданным значениям a, b, h.


Function F(x)

F = sin(2*x^2+4*x-2*exp(1/x))

End Function
Sub Create()

a=InputBox(“Введите начало отрезка”)

b=InputBox(“Введите конец отрезка”)

h=InputBox(“Введите шаг на отрезке”)

Range(“A:A”).Clear

Range(“B:B”).Clear

Range(“A1”)=”x”

Range(“B1”)=”y”

i=2

For x=a To b Step h



Cells(i,1)=x

Cells(i,2)=f(x)

i=i+1

Next x


End Sub
Значения переменных а, в и n можно считать из ячеек рабочего листа с соответствующими именами а, в и n следующим образом. Чтобы присвоить имя ячейки используйте команду Вставка/имя/присвоить

a = Worksheets("имя_листа").Range("a").Value

b = Worksheets("имя_листа").Range("b").Value

n = Worksheets("имя_листа").Range("a").Value

В задаче интерполирования можно описать функцию многочлена Лагранжа по формуле l(t)=
Function l(t)

ReDim x(N), y(N)' N, a, b -глобальные переменные

h = (b - a) / N

For i = 0 To N

x(i) = a + i * h

y(i) = f(x(i))

Next i

l = 0


For i = 0 To N

p = 1


For j = 0 To N

If i <> j Then p = p * (t - x(j)) / (x(i) - x(j))

Next j

l = l + y(i) * p



Next i

End Function

Здесь применены свойства и методы объектов в VBA.
Объекты, их свойства и методы в VBA. Объект- это то, чем мы управляем с помощью программ (лист, ячейка, диаграмма). Например:


Range(“A:A”)

объект «диапазон ячеек», а именно: весь столбец А

Range(“1:1”)

объект «диапазон ячеек», а именно: вся первая строка

Range(“A1”)

объект «диапазон ячейки» с указанием имени ячейки

Cells(1,1)

объект «ячейка первой строки и первого столбца»

Sheet

объект «лист»

WorkSheet

объект «рабочий лист»

DialogSheet

объект «диалоговое окно»

Каждый объект обладает некоторыми характеристиками или свойствами (например, шрифт в ячейке). Установка или получение свойств объекта производиться с помощью оператора присваивания.



Установка значения свойств:
объект.свойство = выражение
Получение свойств объекта:
переменная = объект.свойство
Методы – это то, что вы можете делать с объектом. Например, показать объект (метод Show) или скрыть его (метод Hide). Вызов метода производится также как вызов процедуры:
объект.метод
Перечислим некоторые свойства и методы объектов:


a = Cells(1,1).Value

получение значения объекта «ячейка»

Range(“A2”).formula = ”=СУММ(A1:C1)”

задание формулы для объекта «ячейка»

Range(“A:A”).Clear

очистка объекта «диапазон» (столбец)

Range(“A10:B12”).Select

выбор объекта «диапазон ячеек»

При наборе метода или свойства объекта выходит окно подсказки по всем методам и свойствам данного объекта. Чтобы воспользоваться ею наберите первые символы нужного метода и нажмите клавишу пробела или ввода. После ввода строки сразу идет проверка на возможные ошибки операторов VBA или объявлений объектов, их свойств и методов. Если все в порядке, то в строке первые символы ключевых имен переводятся в заглавные и расставляются необходимые пробелы.

Приведем некоторые команды (инструкции или операторы) VBA:


Оператор

Описание оператора

Dim имяПеременной as тип

описание переменных с указанием типа

Const имяКонстанты=значение

описание констант

Type имяТипа

ИмяЭлемента1 as тип

ИмяЭлемента2 as тип

End Type



описание типа записи с описанием типов для элементов (полей записи)

[Let/Set]переменная=значение

оператор присваивания Set используется для объектных переменных, Let - для всех остальных, но обычно он опускается

With объект

[операторы]

End With


оператор связи с объектом

If условие Then…[Else…]

условный оператор

If условие Then

[операторы]

[ElseIf условие Then

[операторы_elseif]

[Else

[операторы_else]



End If

условный оператор с возможностью организации одной или двух ветвлений elseif /else.

Select Case выражение

[Case списокВыражений-n

[операторы-n]] ...

[Case Else

[операторы_else]]

End Select



оператор выбора

For счетчик=нач. To кон.[Step шаг]

[операторы]

[Exit For]

[операторы]

Next [счетчик]


оператор цикла со счетчиком

For Each элемент In группа

[операторы]

[Exit For]

[операторы]

Next [элемент]


оператор цикла для каждого элемента из введенной группы

Do [{While | Until} условие]

[операторы]

[Exit Do]

[операторы]

Loop


оператор цикла с предварительной проверкой условия продолжения цикла

While- пока условие истинно; Until- пока условие ложно



Do

[операторы]

[Exit Do]

[операторы]

Loop [{While | Until} условие]


оператор цикла с последующей проверкой условия окончания цикла:

While- пока условие ложно; Until- пока условие истинно



While условие

[операторы]

Wend


оператор цикла с предварительной проверкой условия продолжения цикла (пока условие истинно цикл продолжается)

Приведем в таблице используемые типы данных и функции преобразования в эти типы:



1.1.Тип


Функция преобразования

Значение данных

Byte

Cbyte()

байт

Boolean

Cbool()

логический

Integer

CInt()

целый

Long

CLng()

длинное целое

Single

CSng()

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

Double

CDbl()

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

String

CStr()

строка

Currency

CCur()

денежный

Decimal

CDec()

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

Date

Cdate()

дата и время

Variant

CVar()

любой тип чисел или строк

Object




объект

Функция MsgBox осуществляет вывод информации в окне сообщений:


MsgBox(Сообщение, Кнопка, Заголовок)
Функция InputBox - осуществляет ввод значений с помощью окна ввода:
InputBox(Сообщение, Заголовок)


Сообщение

Строка, которая выводится как приглашение к диалогу

Кнопки

Числовая константа, которая определяет какие кнопки будут задействованы в диалоге

Заголовок

Определяет строку заголовка в верхней части окна диалога

Возможные значения аргумента кнопки.




Кнопки

Номер

Отображение

VbOkonly

0

Показывать только ОК

VbOkCancel

1

Показывать ОК и Cancel

VbAbortRetryIgnore

2

Показывает Abort, Retry, Ignore

VbYesNoCancel

3

Показывает Yes, No, Cancel

VbYesNo

4

Показывает Yes, No

VbRetryCancel

5

Показывает Retry, Cancel

VbCritical

16

Показывать значок критической ошибки

VbQuestion

32

Показывать знак “?”

VbExclamation

48

Показывать знак “!”

VbInformation

64

Информационный значок

VbDefaultbutton1

0

Назначить первую кнопку

VbDefaultbutton2

256

Вторую кнопку

VbDefaultbutton3

512

Третью кнопку по умолчанию

Возвращение значения для функции MsgBox.




Значение

Номер

Кнопка

VbOK

1

Ok

VbCancel

2

Cancel

VbAbort

3

Abort

VbRetry

4

Retry

VbIgnore

5

Ignore

VbYes

6

Yes

VbNo

7

No

Например:


function MyString() as String

Msg =”Вы хотите продолжить?”

Style =VbYesNo+VbCritical+VbDefaultButton2

Title =”Заголовок”

Response =MsgBox (Msg, Style, Title)

If Response =VbYes Then MyString =”Yes” else _

Mystring =”No”

End Sub
‘Изменение размеров окна приложения с помощью изменения

‘его свойств: высоты и ширины
Sub New_Width_Size( )

Wsize = InputBox (“Введите ширину окна”)

Hsize = InputBox (“Введите высоту окна”)

ActiveWindow.Height = Hsize

ActiveWindow.Width = Wsize

End Sub
Функция Format осуществляет форматирование значения вычисляемого выражения по указанному формату:


Format( выражение, формат)
Встроенные форматы VBA:

1.2.Формат

1.3.Отображение


Fixed

#.## ( в программе можно записать Format(r,"Fixed"))

General Number

Отображает число, как оно есть.

Currency

Денежный формат с 2 цифрами после десятичной точки.

Standard

Отображает число с разделителем тысяч и двумя цифрами после десятичной точки.

Percent

Отображает число в формате процентов с двумя цифрами после десятичной точки.

Scientific

Отображает число в стандартном экспоненциальном формате.

Yes/No

Отображает No, если 0, и Yes, если 1

True/False

Отображает False, если 0, и True, если 1

On/Off

Отображает Off, если 0, и On, если 1

General Date

Отображает дату или время.

Long Date

Отображает длинный формат даты.

Medium Date

Отображает средний формат даты.

Short Date

Отображает короткий формат даты.

Long Time

Отображает длинный формат времени.

Medium Time

Отображает средний формат времени.

Short Time

Отображает короткий формат времени.


При редактировании можно воспользоваться через меню Правка следующими командами для работы с выделенным блоком: Вырезать (+X), Копировать (+C), Вставить (+V), Очистить (Del). Выделить строку можно комбинациями клавиш + [клавиши управления курсором] или движением мыши с нажатой левой клавишей.

Запуск макроса. До запуска макроса следует откомпилировать проект (Отладка, Компилировать VBAProject). При возникновении ошибок компилятор показывает ту строку, где эта ошибка возникла. Для исправления можно воспользоваться справочной системой помощи. Для выполнения макроса нужно встать на нужную подпрограмму и выбрать из меню Запуск: Запуск подпрограммы (). Если программа долго не выполняется, то прервите ее (Запуск, Прервать (+)) или сбросьте (Запуск, Сброс).

Отладка программ: Ошибки в программах - это объективная неизбежность. Ошибки в программах можно условно разделить на ошибки компиляции, ошибки выполнения и логические ошибки.

Ошибки компиляции возникают, если VBA не может интерпретировать введенный код. Строка, которая содержит ошибку, выделяется красным цветом - VBA обнаруживает эти ошибки при вводе. Другие ошибки обнаруживаются перед выполнением программы. VBA каждый раз автоматически компилирует программу при запуске на выполнение. Предусмотрена также возможность компилировать программу без запуска командой Отладка, Компилировать. Место ошибки выделяется синим цветом, и выдается сообщение о ее причине.

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

Логические ошибки труднее всего обнаружить и устранить. Программа при таких ошибках работает, но выдает неверные результаты. Необходимо анализировать алгоритм программы с привлечением средств отладки VBA.

Простейшим средством предотвращения случайных ошибок является использование инструкции Option Explicit, которая предписывает явное описание всех переменных в программе.



Пошаговое выполнение программ можно выбрать при помощи панели инструментов Отладка, либо меню Отладка, которое включает команды и соответствующие комбинации клавиш.

  1. Отладка, Шаг с заходом (в процедуры): программа выполняется шаг за шагом

  2. Отладка, Шаг с обходом: процедуры выполняются целиком.

  3. Отладка, Шаг с выходом: завершается выполнение текущей процедуры и останавливается процесс пошаговой отладки на следующей после вызвавшей ее инструкции программы.

  4. Отладка, Выполнить до текущей позиции: выполняется программа до инструкции, на которой установлен курсор.

Точка останова устанавливается или снимается с помощью команды Отладка, Точка останова. Точки останова выделяются полосой кирпичного цвета и кругом того же цвета. В одном проекте может быть несколько точек останова. Они предназначены для приостановления выполнения программы. Все инструкции, расположенные выше, между и ниже точек останова выполняются в обычном режиме.

Вывод значений свойств и переменных можно произвести следующим образом: первый путь - режим вывода всплывающей подсказки с текущими значениями описывался выше. Другим способом является использование диалогового окна «Контрольные значения», отображаемого на экране с помощью команды Вид, Окно контрольного значения, либо команды Отладка, Контрольное значение.

Диалоговое окно «Контрольное значение» применяется для одновременного отображения текущих значений нескольких данных. Команда Отладка, Добавить контрольное значение позволяет добавить новые контрольные значения. Для того, чтобы удалить контрольные значения, необходимо их выделить и нажать на <Delete>.

Существует также программный способ вывода значений свойств и переменных в диалоговом окне Контрольные значения при помощи метода Print объекта Debug.Например:
Переменная="Вывод текста"

Debug.Print переменая


Окно «Локальные переменные» отображаемое на экране командой Вид, Окно отладки предоставляет пользователю возможность:

1.Набирать и вычислять отдельные инструкции VBA. Для этого достаточно в окно «Проверка» ввести соответствующую инструкцию и нажать . Единственным ограничением на пользование инструкции является то, что она должна быть набрана в одну строку, например:


S=0: for i=1 to 5: S=S+i^2: Next i: MsgBox S
2. Определять текущее значение переменных и свойств. Для этого в окне «Проверка» надо набрать вопросительный знак, имя переменной или свойства и , например:
? x
3. Устанавливать новые текущие значения переменных. Для этого в Окне «Проверка» надо набрать имя переменной, знак равенства и новое значение переменной.
х=15

Тема 2.Работа с массивами


Диапазон ячеек в n строк и m столбцов можно ограничить (выделить) и объявить как двумерный массив (вектор или матрица), предварительно задав имя в строке «Имя» (например, массив_А или вектор_X). Имя можно записывать на русском и на латинском языках, но без пробелов, и обязательно нажать клавишу после окончания ввода имени. Далее при обращении к этому диапазону достаточно ввести имя, а не весь диапазон.

Например, в диапазоне A1:D4, объявленном под именем массив_А, заданы целые числа, тогда в модуле для ввода массивов А, B, X, Y из нужного диапазона наберем процедуру:


Sub vvod()

B=Range(“A1:D4”) ‘матрица В(1 to 4, 1 to 4)

A=Range(“массив_А”)‘матрица A(1 to 4, 1 to 4)

X=Range(“A1:A4”) ‘1-ый столбец/вектор X(1 to 4, 1 to 1)

Y=Range(“A2:D2”)‘2-ая строка/вектор Y(1 to 1, 1 to 4)

C=Application.Mmult(a,x)‘вектор-столбец С(1 to 4, 1 to 1)

range(“G1:G4”)=C

End Sub
В результате получим вектор c=A*x.

Можно считывать каждую ячейку и присваивать это значение в заданный элемент массива. При этом сам массив должен быть описан в операторе DIM одним из способов:
‘Описание матриц

Dim A(1 to 4, 1 to 4) as single

Dim B(3,3) as integer

‘Описание векторов

Dim X(1 to 4, 1 to 1) as double

Dim Y(5) as string

‘Задание динамического массива

Dim Z() as integer

n=5:m=5

ReDim Z(n,m)



‘Явное задание элементов массива

C=Array(”a”,”b”,”c”,”d”,4,5,6)


Если в начале модуля записать оператор
Option Base 1
то нижняя граница массива всегда будет начинаться с 1. Если нет, то, по умолчанию, будет начинаться с 0. Эта опция нужна при передачи данных из массива в диапазон (при не совпадении нумерации в диапазонах и в массивах присваиваться будет сначала нулевой элемент массива) или при вызове функции для работы с массивами.

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





Функция в Excel

Функция в VBA

Назначение

СЧЕТ

Count

возвращает количество чисел в списке аргументов

СЧЕТЗ

CountA

возвращает количество непустых значений в списке аргументов

ЧИСЛОСТОЛБ

Columns

возвращает количество столбцов в ссылке

ЧСТРОК

Rows

возвращает количество строк в ссылке

ТРАНСП

Transpose

возвращает транспонированный массив

МОБР

MDeterm

возвращает обратную матрицу

МОПРЕД

MInverse

возвращает определитель матрицы

МУМНОЖ

MMult

возвращает произведение матриц

СУММКВ

SumSq

возвращает сумму квадратов аргументов

СУММКВРАЗН

SumXMY2

возвращает сумму квадратов разностей соответствующих значений в двух массивах

СУММПРОИЗВ

SumProduct

возвращает сумму произведений соответствующих элементов массивов

СУММРАЗНКВ

SumX2MY2

возвращает сумму разностей квадратов соответствующих значений в двух массивах

СУММСУММКВ

SumX2PY2

возвращает сумму сумм квадратов соответствующих элементов двух массивов

Например, для решения системы линейных алгебраических уравнений достаточно написать функцию с одним оператором присваивания:


Function Реш_лин_сис(A as Variant, B as variant)_

as Variant

Реш_лин_сис=Application.Mmult _

(Application.Minverse(A),B)

End Function
Здесь знак «_» необходим для разрыва строки, т.к. оператор должен вмещаться на всю строку.

Для вычисления выражения по следующей формуле



напишем следующую функцию:


Function S(X as Variant, Y as Variant) as Double

Dim n as Integer, Sx, Sy, Sxy, Sx2 as Double

With Application do

n = .Count(X)

Sx = .Sum(X)

Sy = .Sum(Y)

Sxy = .SumProduct(X, Y)

Sx2 = .SumSq(X)

End With

S = (n*Sxy-Sx*Sy)/(n*Sx2-Sy^2)

End Function
Для ввода и вывода массива в ячейки, если размер заранее не известен или меняется от задачи к задаче, можно воспользоваться процедурами Vvod и Vivod. Параметрами процедур будут массив, размер массива и координаты вывода (номер строки и столбца верхнего левого угла диапазона вывода). Покажем это на примере вычисления произведения матрицы А на вектор В. Значения этих массивов должны быть занесены в диапазон ячеек, начиная с A1 для матрицы А и через столбец для вектора В. Запуск макроса mass производиться через меню Сервис, Макрос, Макросы (или +). Из диалогового окна «Макрос» выбираем нужный макрос и нажимаем кнопку «Выполнить» (или ).

Option Base 1


Sub Vvod(a, b, n, m, X)

For i = 1 To n

For j = 1 To m

X(i, j) = Cells(a + i - 1, b + j - 1)

Next j

Next i


End Sub
Sub Vivod(a, b, n, m, X)

For i = 1 To n

For j = 1 To m

Cells(a + i - 1, b + j - 1) = X(i, j)

Next j

Next i


End Sub
Sub mass()

Dim A(), B() As Integer

n = InputBox("Введите кол-во строк")

m = InputBox("Введите кол-во столбцов")

x = InputBox("Укажите координату строки")

y = InputBox("Укажите координату столбца")

ReDim A(n, m), B(m, 1)

Vvod x, y, n, m, A

b=y + m + 1

Vvod x, b, m, 1, B

c = Application.MMult(A, B)

b=y + m + 3

Vivod x, b, n, 1, c

End Sub


При решении систем линейных алгебраических уравнений чтобы определить число обусловленности матрицы А по формуле || A|| ||A-1 || можно использовать функцию для вычисления нормы матрицы

function norma(a)

norm = 0

For i = 1 To n

Sum = 0

For j = 1 To n



Sum = Sum + Abs(a(i, j))

Next j


If Sum >= norm Then norm = Sum

Next i


End function

При вызове этой функции следует написать оператор n1=norma(A)

Можно эту же задачу решить с помощью процедуры

Sub norma(a, norm)

norm = 0

For i = 1 To n

Sum = 0

For j = 1 To n



Sum = Sum + Abs(a(i, j))

Next j


If Sum >= norm Then norm = Sum

Next i


End Sub

При вызове процедуры параметры не надо заключать в скобки

Norma A, n1

Следующий пример решает систему линейных алгебраических уравнений методом Гаусса с помощью процедуры.

Sub gauss(a, x, f)

ReDim b(N, N)' N- глобальная переменная

For i = 1 To N

For j = 1 To N

b(i, j) = a(i, j)

Next j


Next i

For k = 1 To N - 1

For i = k + 1 To N

m = b(i, k) / b(k, k)

For j = k To N

b(i, j) = b(i, j) - m * b(k, j)

Next j

f(i) = f(i) - m * f(k)



Next i

Next k


x(N) = f(N) / b(N, N)

For i = N - 1 To 1 Step -1

x(i) = f(i)

For j = i + 1 To N

x(i) = x(i) - b(i, j) * x(j)

Next j


x(i) = x(i) / b(i, i)

Next i


End Sub


Тема 3.Подбор и поиск решения


Подбор решения применяется для нахождения решения уравнения вида f(x)=0. Для этого задается в ячейке A1 начальное x, а в ячейке B1- значение функции для данного значения. Затем по команде Сервис, Подбор параметра откроется диалоговое окно «Подбор параметра», в котором указываются следующие значения:
Установить в ячейке: B1

Значение: 0

Изменяя значение ячейки: A1
После этого в окне «Результат подбора параметра» выводится текущее значение функции в ячейку B1 при значении аргумента (решение уравнения) в ячейке A1.
Поиск решений применяется для решения оптимизационных задач. Рассмотрим некоторые примеры таких задач.

Решение системы нелинейных уравнений. Рассмотрим следующий пример:

Пара чисел (x, y) будет являться решением системы, если она является решением уравнения с двумя неизвестными:



С помощью средства поиска решений вместо системы будем решать равносильное ей нелинейное уравнение.

Определяемое решение нелинейной задачи зависит от начального приближения. Его можно найти, протабулировав функцию по двум параметрам x и y или построив график этой функции (или двух функций в системе).

Для нахождения приближенного решения отведем под переменные x и y ячейки A10 и B10 и введем в них начальные приближения (например –1.5 и 1.5). В ячейку C10 введем формулу, вычисляющая значение правой части нелинейного уравнения: =(A10^2+B10^2-3)^2+(2*A10+3*B10)^2.

По команде Сервис, Поиск решения откроется диалоговое окно «Поиск решения» куда введем следующие значения:
Установить целевую ячейку: C10

Равной: Значению 0

Изменяя ячейки: A10:B10

в диалоговом окне «Параметры»:

снять флажок на «Линейная модель»
Полученное решение уравнения поместится в ячейки A10 и C10, а значение правой части уравнения в ячейку C10.

Эта система имеет два решение, поэтому для нахождения второго решения поступаем аналогично.



Тема 4.Построение форм


Панель инструментов (Forms) состоит из кнопок:


Объект VBA

Элемент формы

Labels

-Надпись

EditBoxes

-Текстовое поле




-Рамка

Buttons

-Кнопка

CheckBoxes

-Флажок

OptionButtons

-Переключатель

ListBoxes

-Список

DropDowns

-Раскрывающийся список




-Поле со списком




-Поле с раскрывающимся списком

ScrollBars

-Полоса прокрутки

Spinners

-Счетчик

Эти элементы можно помещать на рабочий лист, на форму, созданную в редакторе VBA или на диалоговом листе Excel 2.0.

Каждому элементу можно назначить макрос, выбрав из контекстного меню команду Назначить макрос, а, выбрав команду Формат объекта, можно изменить его свойства: размер, шрифт, поля, установить выравнивание и защиту.

Приведем пример создания автоматизированного оглавления рабочей книги с помощью объектов панели инструментов Forms («Формы»).


1.Кнопки. Создадим рабочую книгу с 5 рабочими листами с именами Оглавление, Интерполирование, Интегрирование, Слау, Вспомогательный. В редакторе VBA, выбрав меню Вставка, Модуль, введите 4 процедуры.
Sub Интерполирование()

Sheets("Интерполирование").Activate

End Sub
Sub Интегрирование()

Sheets("Интегрирование").Activate

End Sub
Sub Слау()

Sheets("Слау").Activate

End Sub
Sub Возврат()

Sheets("Оглавление").Activate

End Sub
Метод Activate активизирует объект, в данном случае - рабочий лист. Создайте на листе Оглавление 3 кнопки. Имена на кнопках измените на «Интерполирование», «Интегрирование», «Слау» и назначьте им соответственно макросы Интерполирование, Интегрирование, Слау. На каждом листе Интерполирование, Интегрирование, Слау создайте кнопку «Возврат» и назначьте ей макрос Возврат. Проверьте работу кнопок.
2.Раскрывающийся список. На рабочем листе Вспомогательный в ячейки А1, А2, А3 введите строки Интерполирование, Интегрирование, Слау. С помощью команды Вставка, Имя, Присвоить присвойте диапазону А1:А3 имя Список. Ячейке А6 - имя Номер, ячейке А7 - имя Лист. В А7 введите формулу =ИНДЕКС(Список; Номер;1).

Эта функция возвращает значение ячейки диапазона типа массив с заданными номерами строки и столбца. Если ввести в Номер значение от 1 до 3 функция ИНДЕКС будет возвращать в ячейку Лист содержимое соответствующей ячейки из диапазона Список. Например, если в Номер ввести 3, то Лист будем изменен на Слау. Введем процедуру


Sub Список1()

Dim s As String

s = Range("Лист").Value

Sheets(s).Select

End Sub
Процедура считывает значение из ячейки Лист в строковую переменную s и активизирует лист с именем, записанным в эту переменную. Нажмите кнопку «Поле со списком» на панели инструментов «Формы» и создайте на рабочем листе Оглавление раскрывающийся список, назначьте ему процедуру Список1. Теперь, щелкнув на список правой кнопкой мыши, выберите в раскрывающемся меню команду Формат объекта. В открывшемся диалоговом окне «Форматирование объекта» сделайте следующие изменения:
Элементы управления:

Форматировать список по диапазону: Список

Перемещать результат в ячейку: Номер

Количество строк списка: 3
Теперь раскрывающийся список будет выводить значение, записанное в диапазон Список, а номер выбранного элемента в ячейку Номер.

Также можно оформить элемент формы «Список».


3. Счетчик. Присвойте ячейке С6 рабочего листа Вспомогательный имя Первый_лист. Создайте рядом счетчик и кнопку, на поверхности которой напишите «OK». Ячейке H5 листа Оглавление присвойте имя Первый и введите в нее формулу =ИНДЕКС( Список; Первый_лист;1). В модуле введите процедуру:
Sub Счетчик()

Dim s As String

s = Range("Первый").Value

Sheets(s).Activate

End Sub
Назначьте эту процедуру кнопке OK. Щелкнув счетчик правой кнопкой мыши, выберите в меню команду Формат объекта. В открывшемся диалоговом окне «Формат объекта» внесите следующие значения:
Начальное значение:2

Минимальное значение: 1

Максимальное значение: 3

Шаг изменения: 1

Перемещать результат в ячейку: Первый_лист
Таким образом счетчик будет изменять значение ячейки Первый_лист в диапазоне от 1 до 3, функция ИНДЕКС - выводить соответствующее значение диапазона список в ячейку Первый, а процедура Счетчик- по щелчку на кнопку OK осуществлять переход на лист с именем, введенным в ячейку Первый.
4. Полоса прокрутки. Присвойте ячейке С7 листа Вспомогательный имя Второй_лист. Создайте на листе Оглавление кнопку, на поверхности которой напишите «Перейти на лист». С помощью формы «Полоса прокрутки» создайте полосу прокрутки. Ячейке G13 листа Оглавление присвойте имя Второй и введите в нее формулу =ИНДЕКС(Список;Второй_лист;1). В модуле введите процедуру:
Sub Полоса_прокрутки()

Sheets(Range("Второй").Value).Activate

End Sub
Назначьте процедуру кнопке «Перейти на лист». Щелкнув правой кнопкой мыши полосу прокрутки, выберите в меню Формат объекта. Заполните открывшееся окно также как в предыдущем примере, кроме поля:
Помещать результат в ячейку: Второй_лист

5. Переключатели. Создайте на рабочем листе Оглавление кнопку и напишите на ней «Перейти». С помощью формы «Рамка» создайте группу, а с помощью формы «Переключатель» три положения переключателя в группе. Введите процедуру
Sub Переключатель()

Dim s As String

Set ns = Sheets("Оглавление")

With ns Do

If .OptionButtons(1).Value=xlOn Then s= "Интерполирование"

If .OptionButtons(2).Value=xlOn Then s= "Интегрирование"

If .OptionButtons(3).Value=xlOn Then s= "Слау"

End With


Sheets(s).Select

End Sub
Назначьте эту процедуру кнопке «Перейти». OptionButtons(номер)– это объект «кнопка переключателя» с указанным номером. Постоянная xlOn используется, чтобы описать ситуацию, когда кнопка переключателя выбрана. Таким образом, процедура Переключатель в зависимости от включенного переключателя присваивает переменной s одно из возможных значений, а кнопка «Перейти» активизирует лист с соответствующим именем. Так же можно оформить элемент формы «Флажок».



Пример создания диалоговых окон.


Для считывания введенных в диалоговом окне значений используйте команды

UserForm1.Show

n = CInt(UserForm1.TextBox1.Text)

d = CDbl(UserForm1.TextBox2.Text)

ep = CDbl(UserForm1.TextBox3.Text)


Тема 5. Задания по численным методам


Выполнение задания оценивается следующим образом:

Оценка «удовлетворительно»-за выполненный 1 пункт задания.

Оценка «хорошо»-за выполненные 1-2 пункты задания.

Оценка «отлично»-за выполненные 1-3 пункты задания.



      5.1 Задача интерполирования

  1. На заданном отрезке [a, b] через определенный шаг h вывести значения функции y=f(x) (через функцию VBA). По заданным N-узловым точкам (xi, yi=f(xi), i=0…N) восстановить функцию с помощью многочлена Лагранжа, линейного и кубического сплайна, используя функции VBA. Построить графики этих функций на отдельном листе. Построить график зависимости погрешности интерполирования многочленом Лагранжа от количества узлов интерполирования.

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

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

  2. Создать диалоговое окно для выполнения задания.




    1. Численное интегрирование

  1. На заданном отрезке [a, b] для функции y=f(x) построить таблицу значений с заданным шагом h. По этим данным построить график. Вычислить интеграл.

dx

с помощью формул прямоугольника, трапеции, Симпсона, Гаусса (приближенное вычисление интеграла). Сравнить с точным значением интеграла. Вывести оценки остаточных членов соответствующих формул. Построить графики зависимостей погрешности вычисления от шага h.



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

  2. Создать диалоговое окно для выполнения задания.




    1. Решение нелинейных уравнений

  1. На заданном отрезке [a, b] построить график функции y=f(x). Найти отрезок, в котором график пересекает ось Ох один раз. Этот отрезок запомнить и найти решение уравнения f(x)=0 на этом отрезке следующими способами:

  • с помощью команды Подбор параметра,

  • с помощью формул на рабочем листе таблицы вывести пошаговое нахождение решения методом деления отрезка пополам,

  • с помощью функций, возвращающих приближенное решение, полученное методами Ньютона, Ньютона модифицированным, Хорд и Чебышева с заданной точностью, вывести количество итераций по каждому методу.

  1. Создать элементы форм на рабочем листе: для ввода начальных данных, для выбора вида уравнения, для выбора метода решения уравнения и для очистки результатов.

  2. Создать диалоговое окно для выполнения задания.




    1. Спектральная задача

  1. Найти наибольшее и наименьшее собственное значение и соответствующие им собственные вектора заданной матрицы А размерности n*n методом скалярных произведений и методом вращения. Вывести количество полученных итераций.

  2. Создать один из элементов формы на рабочем листе: для ввода начальных данных, для выбора вида матрицы, для выбора метода решения и для очистки результатов.

  3. Создать диалоговое окно для выполнения задания.




    1. Решение системы линейных алгебраических уравнений

  1. Найти решение СЛАУ различными численными методами: методом Гаусса, методом Гаусса с выбором главного элемента, методом простой итерации, методом Зейделя, методом релаксации, методом минимальных невязок и методом сопряженных градиентов. Вывести количество итераций и погрешность полученного решения.

Найти число обусловленности матрицы коэффициентов.

Исследовать вопросы устойчивости решения по правой части. Для этого введите возмущение - delta( малая величина <<1), добавьте его к первой компоненте правой части f(1)=f(1)+delta и проанализируйте поведение решения возмущенной системы. Используйте тесты для хорошо обусловленных и плохо обусловленных матриц( например, матрицы Гильберта, элементы которой определяются по формуле ai,j=1/(i+j-1) )

Вычислить определитель матрицы и найти обратную матрицу с помощью метода Гаусса и с помощью функций VBA. Сделать проверку вычислений и вывести погрешность.


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

  2. Создать диалоговое окно для выполнения задания.




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

  1. Для заданной системы из двух уравнений построить графики функции. Задать начальное значение. Найти решение с помощью средства Excel – команды Поиск решения.

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

  1. Создать один из элементов формы на рабочем листе: для ввода начальных данных, для выбора вида системы, для выбора метода решения, для вывода погрешности методов и для очистки результатов.

  2. Создать диалоговое окно для выполнения задания.

Литература

1. Биллиг В.А. VBA в Office 2000. Офисное программирование. Издательско-торговый дом "Русская редакция", М., 1999, 480с.

2. Гарнаев А. Использование MS Excel и VBA в экономике и финансах. БХВ– Санкт-Петербург, 1999, 336с.

3. Малышев С.А. Самоучитель VBA. Как это делается в Word, Excel, Access. Наука и техника . Санкт-Петербург, 2001, 490с.





страница 1


Смотрите также:





     

скачать файл




 



 

 
 

 

 
   E-mail:
   © zaeto.ru, 2018