информатика.методичка

x = x + dx i = i + 1

If x <= xk + e Then GoTo 10 End Sub

Некоторые комментарии к программе:

впрограммевлогическомвыраженииx <= xk + e используется цифровая константа e = 0.001, применение которой обеспечивает получение спектра значений X от -1 до 0.3 включительно;

в тексте программы встречаются обращения к приложению,

например, Cos(Application.WorksheetFunction.Pi), которое озна-

чает, что для вызова функции π последовательно обращаются сначала к приложению Application, затем к функциям рабочего листа WorksheetFunction и, наконец, к конкретной функции Pi;

чтобынедопуститьошибкивэтойдостаточносложнойграмматической конструкции, следует воспользоваться возможностями дружественного интерфейса VBA, для чего в тексте программы написать Cos(Application., и после постановки точки появится подменю разделов Приложения (рис. 4.2).

Рис. 4.2

После точки следует добавить букву W, и тогда в подменю поя-

вится раздел WorksheetFunction (рис. 4.3).

Выбрать раздел WorksheetFunction, после чего поставить точку и в появившемся подменю списка функций рабочего листа выбрать функцию Pi, рис. 4.4.

Рис. 4.3

Рис. 4.4

Продолжить запись арифметического выражения Cos(Application.WorksheetFunction.Pi в соответствии с выполняемым заданием.

5. ПОСТРОЕНИЕ ПОВЕРХНОСТИ

Лабораторная работа «Построение поверхности» также состоит из двух частей:

табулирование функции поверхности в Excel;табулирование функции поверхности в VBA.

Критерием правильности является совпадение полученных таблиц.

Отличием в табулировании функции от двух параметров Z(x,y) является то, что спектр значений аргумента X формируется в столбце в диапазоне A6:A16, а спектр значений аргумента Y формируется в строке в диапазоне B5:L5 (рис. 5.1).

Величина начального значения аргумента X устанавливается

вячейке A6 и выбирается в соответствии с выполняемым заданием, в данном случае A6=0.

Величина начального значения аргумента Y устанавливается

вячейке B5 и также выбирается в соответствии с выполняемым заданием, в данном случае B5=0.

Значение ячейки A7 определяется как результат вычисления арифметического выраженияA7 = A6 + $B$1, где $B$1 – шаг изменения аргумента X, который выбирается произвольно.

Значения диапазона A8:A16 получаются в результате копирования ячейки A7 по указанному диапазону.

Значение ячейки С5 определяется как результат вычисления арифметическоговыраженияС5 = B5 + $B$2, где$B$2 – шагизменения аргумента Y, который выбирается произвольно.

Значения диапазона D5:L5 получаются в результате копирования ячейки C5 по указанному диапазону.

Ключевым моментом в формировании таблицы значений функции Z(x,y) является запись в ячейку B6 арифметического выражения, соответствующего функции поверхности выполняемого зада-

ния Z(x,y) = 2x2 + 3y2.

Витогев ячейкуB6 должнобытьзаписаноарифметическоевыражение

=2*$A6^2+3*B$5^2

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

апазон B6:L16.

В результате любая ячейка из диапазона B6:L16 в качестве аргумента X использует соответствующую (относительная адресация) ячейку столбца A диапазона A6:A16 (абсолютная адресация), а в качестве аргумента Y использует соответствующую (относительная адресация) ячейкустроки5 диапазонаB5:L5 (абсолютнаяадресация).

Рис 5 1 Рис. 5.1

Диапазон B6:L16 используется для построения графика поверхности.

Программа построения поверхности в VВА

Private Sub CommandButton2_Click() Dim i As Integer

Dim ni As Integer Dim j As Integer Dim nj As Integer Dim x As Single Dim xn As Single Dim xk As Single Dim dx As Single Dim y As Single Dim yn As Single Dim yk As Single Dim dy As Single e = 0.001

xn = InputBox("Xn = ", "Ввод начального значения X", 0, 8000, 2000) xk = InputBox("Xk = ", " Ввод конечного значения X", 1, 8000, 1000) dx = InputBox("dX = ", " Ввод значения шага X", 0.1, 8000, 2000)

yn = InputBox("Yn = ", " Ввод начального значенияY", 0, 8000, 2000) yk = InputBox("Yk = ", " Ввод конечного значенияY", 1, 8000, 1000) dy = InputBox("dY = ", " Ввод значения шагаY", 0.1, 8000, 2000)

i = InputBox("i = ", "Ввод начала таблицы, строка I", 20, 8000, 1000) j = InputBox("j = ", " Ввод начала таблицы, столбец J", 1, 8000, 2000) Cells(i, j) = "X/Y"

ni = i: nj = j: x = xn 10 Cells(i + 1, j) = x x = x + dx: i = i + 1

If x <= xk + e Then GoTo 10 y = yn: i = ni

20 Cells(i, j + 1) = y y = y + dy: j = j + 1

If y <= yk + e Then GoTo 20 y = yn: j = nj

30 i = ni: x = xn

40 z = 2 * x ^ 2 + 3 * y ^ 2 Cells(i + 1, j + 1) = z

24

i = i + 1: x = x + dx

If x <= xk + e Then GoTo 40 y = y + dy: j = j + 1

If y <= yk + e Then GoTo 30 End Sub

Некоторые комментарии к программе:

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

X и Y;

в программе с помощью промежуточных переменных ni и nj запоминаются начальные значения i – строки, с которой начинается таблица в VBA и j – столбца, с которого начинается таблица в VBA, таккак после формирования значений осейX и Y переменные i и j приобрели конечные значения.

6. КВАДРАТИЧНЫЕ ФОРМЫ

Целью лабораторной работы «Квадратичные формы» является освоение матричных функций Excel и VBA.

Выполнение лабораторной работы предполагает знание элементов векторного анализа, в частности, матричных операций.

Лабораторная работа «Квадратичные формы» состоит из двух частей, в первую часть входят:

решение матричного уравнения в Excel;

решение матричного уравнения в Excel с предварительным преобразованием;

вычисление квадратичной формы в Excel;

вычисление квадратичной формы с использованием матричных функций Excel;

вычисление квадратичной формы с использованием матричных функций VBA,

а во вторую:

операции над массивами в Excel;операции над массивами в VBA.

25

Критерием правильности решений матричных уравнений являются результаты проверки подстановкой найденных значений корней в исходные уравнения.

Критерием правильности вычислений квадратичной формы является совпадение полученных значений трех способов вычислений.

Выполнение лабораторной работы начинается с внесения исходных данных задания в соответствующие ячейки таблицы Excel, в выбранной адресации:

значения матрицы А – А8:D11;значения вектора B – F8:F11;значения вектора Y – H8:H11 (рис. 6.1).

ЧАСТЬ ПЕРВАЯ

6.1. Решение матричного уравнения в Excel

Так как решением матричного уравнения АХ=В является вектор Х=А–1В, то необходимо сформировать обратную матрицу А–1, это выполняется в ячейкахA14:D17, для чего:

выделитьA14:D17,

черезмастерфункций fx вызватьматричнуюоперациюМОБР, в окне «массив» которой указать адрес исходной матрицы А –

А8:D11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

В результате в ячейках A14:D17 появятся значения элементов обратной матрицы А–1 (рис. 6.2), а для любой ячейки диапазона A14:D17 встрокесостояний– подтверждениевыполненияматричной операции {=МОБР(A8:D11)}.

Так как результатом произведения исходной матрицы А на обратную матрицу А–1 является единичная матрица E, то для проверки правильности значений элементов полученной обратной матрицы необходимо:

выделить F14:I17;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне (рис. 6.3) «Массив1» которой указать адрес

Рис. 6.1

Рис. 6.2

Рис. 6.3

исходной матрицы А – А8:D11, а в окне «Массив2» – указать адрес обратной матрицы А–1 – А14:D17;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

Врезультате в ячейках F14:I17 появятся значения элементов единичной матрицы E (см. рис. 6.2), а для любой ячейки диапазона F14:I17 в строке состояний – подтверждение выполнения матрич-

ной операции {=МУМНОЖ(A8:D11;A14:D17)}.

Для получения вектора решений Х=А–1В необходимо:выделить J8:J11;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне «Массив1» которой указать адрес обратной матрицы А–1 – F14:I17, а в окне «Массив2» – указать адрес вектора

свободных членов B – F8:F11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

Врезультате в ячейках J8:J11 появятся значения элементов вектора решений X (см. рис. 6.2), а для любой ячейки диапазона J8:J11

встроке состояний – подтверждение выполнения матричной опе-

рации {=МУМНОЖ(F14:I17; F8:F11)}.

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

висходное уравнение АХ=В, для чего следует:

выделить K8:K11;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне «Массив1» которой указать адрес исходной матрицы А – А8:D11, а в окне «Массив2» – указать адрес полученного вектора решений X – J8:J11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

В результате в ячейках K8:K11 появятся значения элементов вектора свободных членов B (см. рис. 6.2), совпадающие со значениями элементов исходного вектора B – F8:F11, а для любой ячейки диапазона K8:K11 в строке состояний – подтверждение выполнения матричной операции {=МУМНОЖ(A8:D11; J8:J11)}.

6.2.Решение матричного уравнения в Excel

спредварительным преобразованием

Для решения второго матричного уравнения AATAX=B необходимо произвести предварительные преобразования, т. е. сформировать матрицу AATA.

Для чего следует:выделитьA20:D23;

через мастер функций fx вызвать матричную операцию ТРАНСП, в окне «массив» которой указать адрес исходной матри-

цы А – А8:D11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

В результате в ячейках A20:D23 появятся значения элементов транспонированной матрицы АТ (рис. 6.4), а для любой ячейки диапазонаA20:D23 в строке состояний – подтверждение выполнения матричной операции {=ТРАНСП(А8:D11)}.

Рис. 6.4

Далее сформировать матрицу AAT, для чего необходимо:выделить F20:I23;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне рис. 6.3 «Массив1» которой указать адрес исходной матрицы А – А8:D11, а в окне «Массив2» – указать адрес транспонированной матрицы АТ – A20:D23;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

ВрезультатевячейкахF20:I23 появятсязначенияэлементовматрицы AAT (см. рис. 6.4), а для любой ячейки диапазона F20:I23

встроке состояний – подтверждение выполнения матричной опе-

рации {=МУМНОЖ(A8:D11; A20:D23)}.

Далее в ячейках A26:D29 формируется матрица AATA, затем

вячейках F26:I29 – обратная матрица (AATA)–1 и по аналогии с решением первого матричного уравнения находится вектор решений X – K26:K29 и осуществляется проверка полученного решения вычислением вектора свободных членов B – L26:L29.

6.3. Вычисление квадратичной формы в Excel

Вычисление квадратичной формы z =YTATA3Y в Excel производится строго в последовательности, указанной в задании.

Первый шаг – вычисление элементов вектора YT:выделить F32:I32;

через мастер функций fx вызвать матричную операцию ТРАНСП, в окне «массив» которой указать адрес вектора Y –

H8:H11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

В результате в ячейках F32:I32 появятся значения элементов транспонированного вектора YТ, т. е. строки (рис. 6.5), а для любой ячейки диапазона F32:I32 в строке состояний – подтверждение выполнения матричной операции {=ТРАНСП(H8:H11)}.

Второй шаг – вычисление элементов транспонированной матрицы AT:

уже выполнен раньше в ячейкахA20:D23, рис. 6.2.

Рис. 6.5

Третий шаг – вычисление элементов матрицы A2:выделитьA32:D35;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне «Массив1» которой указать адрес исходной матрицы А – А8:D11, в окне «Массив2» – адрес исходной матрицы

А – А8:D11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

Врезультате в ячейках A32:D35 появятся значения элементов матрицы A2 (рис. 6.5), а для любой ячейки диапазона A32:D35

встроке состояний – подтверждение выполнения матричной опе-

рации {=МУМНОЖ(А8:D11; А8:D11)}.

Четвертый шаг – вычисление элементов матрицы A3:выделить F38:D41;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне «Массив1» которой указать адрес матрицы A2 – А32:D35, а в окне «Массив2» – адрес исходной матрицы А –

А8:D11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

Врезультате в ячейках A38:D41 появятся значения элементов матрицы A3 (см. рис. 6.5), а для любой ячейки диапазона A38:D41

встроке состояний – подтверждение выполнения матричной опе-

рации {=МУМНОЖ(А32:D35; А8:D11)}.

Пятый шаг – вычисление элементов вектора-строки YT AT:выделить F35:I35;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне «Массив1» которой указать адрес векторастроки YT – F32:I32, а в окне «Массив2» – адрес транспонирован-

ной матрицы AT – А20:D23;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

Врезультате в ячейках F35:I35 появятся значения элементов вектора-строки YT AT (см. рис. 6.5), а для любой ячейки диапазона F35:I35 в строке состояний – подтверждение выполнения матрич-

ной операции {=МУМНОЖ(F32:I32; А20:D23)}.

Шестой шаг – вычисление элементов вектора-строки YT AT A3:выделить F38:I38;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне «Массив1» которой указать адрес векторастроки YT AT – F35:I35, а в окне «Массив2» – адрес матрицы A3 –

А38:D41;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

Врезультате в ячейках F38:I38 появятся значения элементов вектора-строки YT AT A3 (см. рис. 6.5), а для любой ячейки диапазона F38:I38 в строке состояний – подтверждение выполнения матричной операции {=МУМНОЖ(F35:I35; А38:D41)}.

Седьмой шаг – вычисление значения z = YT AT A3Y:выделить ОДНУ ячейку F41;

через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне «Массив1» которой указать адрес векторастроки YT AT A3– F38:I38, а в окне «Массив2» – адрес исходного

вектора-столбца Y – H8:H11;

затем одновременно нажать клавиши Ctrl-Shift-Enter.

Врезультате в ячейке F41 появится значение квадратичной формы z = YT AT A3Y (см. рис. 6.5), а для этой ячейки F41 в строке состояний – подтверждение выполнения матричной операции

{=МУМНОЖ(F38:I38; H8:H11)}.

Таким образом, значение квадратичной формы z = YT AT A3Y для заданного примера, последовательно вычисленное в Excel, равно

1220520.

6.4. Вычисление квадратичной формы с использованием матричных функций Excel

Для вычисления квадратичной формы z = YT AT A3Y с использованием матричных функций Excel необходимо в ячейке H41 записать следующее выражение:

{=МУМНОЖ(МУМНОЖ(ТРАНСП(H8:H11);ТРАНСП(A8:D11 ));МУМНОЖ(A38:D41;H8:H11))}.

Формирование указанного выражения осуществляется по частям, в соответствии с заданием z = YT AT A3Y, поэтапно же можно осуществлять и отладку, сравнивая промежуточные результаты

с промежуточными результатами пошагового вычисления квадратичной формы в Excel.

ВрезультатевячейкеF41 появитсязначениеквадратичнойформы, равное 1220520.

6.5. Вычисление квадратичной формы с использованием матричных функций VBA

Для вычисления квадратичной формы в VBA необходимо создать пользовательскую функцию в виде модуля VBA.

Программа вычисления квадратичной формы в VВА

Function zz(a As Variant, y As Variant) As Variant z1 = Application.Transpose(y) 'транспонирование y z2 = Application.Transpose(a)

z3 = Application.MMult(z1, z2)

z4 = Application.MMult(a, a)

z5 = Application.MMult(z4, a)

z6 = Application.MMult(z3, z5)

z7 = Application.MMult(z6, y) zz = z7

End Function

Некоторые комментарии к программе:

впрограммеиспользуютсяпромежуточныепеременныеz1 – z7;в программе можно ввести комментарии для каждой строки;процесс отладки модуля можно выполнять по частям, для чегосоответствующимобразомизменятьпоследнийоператорпри-

сваивания, например zz = z5.

После отладки программного модуля и внесения его в мастер функций fx следует, находясь в ячейке G41, обратиться через мастер функций fx в данном случае к функции с именем zz (рис. 6.6).

Так как функция zz(A, Y) от двух параметров, то в появившемся окне функции в окне «A» указать адрес объекта (параметра) А – A8:D11, а в окне «Y» указать адрес объекта (параметра) Y – H8:H11.

Рис. 6.6

Затем одновременно нажать клавиши Ctrl-Shift-Enter. В ячейке G41 появится ответ 1220520.

Таким образом, значение квадратичной формы z = YT AT A3Y для заданного примера, вычисленное в VBA, равно 1220520.

ЧАСТЬ ВТОРАЯ

6.6. Операции над массивами в Excel

Пусть в соответствии с заданием (рис. 6.12) необходимо по формуле вычислить S при заданных массивах X, Y и B (рис. 6.7).

Выполнение лабораторной работы начинается с внесения исходных данных задания в соответствующие ячейки таблицы Excel в выбранной адресации:

Excel

Рис. 6.7

значения вектора X – B46:E46;

значения вектора Y – B48:E48;

значения матрицы B – G47:H48 (см. рис. 6.7).

Затем в ячейке D52 через мастер функций fx по формуле =СУММ(G47:H48) вычисляется сумма элементов двумерного массива B (рис. 6.8).

Далее в ячейке D55 через мастер функций fx по формуле =СУММ(B46:E46) вычисляется сумма элементов одномерного массива X (рис. 6.9).

После этого в ячейке D58 через мастер функций fx по формуле =СУММКВ(B48:E48) вычисляется сумма квадратов элементов одномерного массива Y (рис. 6.10).

Затем в ячейке D62 через мастер функций fx по формуле =СУММ(B48:E48) вычисляется сумма элементов одномерного массива Y (рис. 6.11).

После этого в ячейке F58 записывается арифметическое выра-

жение =(2*D55+2*D58+5*D52^3)/(3+D62), соответствующее зада-

нию (рис. 6.12), и вычисляется величина S (см. рис. 6.7).

Рис. 6.12

В итоге получается, что величина S, вычисленная с помощью функций Excel, равна 549,625.

6.7. Операции над массивами в VBA

ДлявычислениявеличиныS вVBA необходимосоздатьпользовательскую функцию в виде модуля VBA.

Программа вычисления величины S в VВА

Function s(x As Variant, y As Variant, b As Variant) z1 = Application.WorksheetFunction.Sum(x)

z2 = Application.WorksheetFunction.SumSq(y)

Excel

Рис. 6.13

z3 = Application.WorksheetFunction.Sum(b)

z4 = Application.WorksheetFunction.Sum(y) s = (2 * z1 + 2 * z2 + 5 * z3 ^ 3) / (3 + z4) End Function

Некоторые комментарии к программе:

впрограммеиспользуютсяпромежуточныепеременныеz1 – z4;в программе можно ввести комментарии для каждой строки;процесс отладки модуля можно выполнять по частям, для чего соответствующим образом изменять последний оператор

присваивания, например s = z2.

После отладки программного модуля и внесения его в мастер функций fx следует, находясь в ячейке G58, обратиться через мастер функций fx к функции с именем s (см. рис. 6.7).

Так как функция s(X, Y, B) от трех параметров, то в появившемся окне функции (рис. 6.13) в окне «X» указать адрес объекта (параметра) X – B46:E46, в окне «Y» указать адрес объекта (параметра) Y – B48:E48, а в окне «B» указать адрес объекта (параметра)

B – G47:H48.

Затем следует одновременно нажать клавиши Ctrl-Shift-Enter. В ячейке G58 появится ответ 549,625.

Таким образом, значение величины S для заданного примера, вычисленное в VBA, равно 549,625.

7. ОПЕРАТОРЫ ЦИКЛА

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

Лабораторная работа «Операторы цикла» выполняется только в среде VBA.

При этом одна и та же разветвляющаяся функция G(x) из ранее выполненной лабораторной работы «Табулирование разветвляющейся функции» табулируется на листе Excel семь раз с использованием семи различных конструкций операторов цикла:

Do While … loop ;Do Loop … while ;Do Until … loop ;Do Loop … until ;While … wend ;For … next ;For Each … next .

Критерием правильности является совпадение полученных таблиц (рис. 7.1).