3.4. Создание и применение макросов

Excel позволяет автоматически написать программу на языке Visual Basic for Application (VBA) на выполненные действия. Эти программы называются макросами. Каждый макрос имеет имя. При вызове макроса по имени он повторит записанные в нем действия.

Выполнение команды Запись макроса (вкладка Вид  группа Макросы  Макросы) приводит к появлению диалогового окна Запись макроса. В поле Имя макроса можно ввести название для записываемого макроса (вместо появившегося по умолчанию имени Макрос1), а в поле Описание — краткую характеристику. Название макроса может содержать буквы, цифры и знак подчеркивания (_), но не должно содержать пробелы или другие специальные символы. Имя должно начинаться с буквы. В поле Сочетание клавиш можно задать комбинацию клавиш, нажатие которых приведет к запуску макроса. Следует иметь в виду, что Excel не предупреждает, если выбрано сочетание клавиш, вызывающее другую команду. В этом случае Excel отдает предпочтение макросам. Например, если букву v записать в поле Сочетание клавиш какого-либо макроса, то нажатие клавиш Ctrl + v приведет к запуску макроса, а не выполнению команды Вставить, как это принято в приложениях Windows. Во избежание подобных ситуаций, можно использовать сочетание буквы с клавишами Ctrl + Shift, которое встречается реже.

После ввода всех параметров необходимо нажать кнопку ОК, что приведет к инициированию записи макроса: с этого момента все выполняемые действия в Excel будут записаны в виде программы на VBA. В процессе записи макроса в строке состояния отображается кнопка Остановить запись (синий квадрат). Эта кнопка является индикатором записи макроса, а также используется для остановки записи последовательности действий по их завершению. Остановить запись макроса можно также с помощью команды Остановить запись (вкладка Вид  группа Макросы  Макросы).

По умолчанию при записи макроса используются абсолютные ссылки. Макрос, записанный с абсолютными ссылками, при выполнении всегда обрабатывает те же ячейки, которые участвовали в работе при его записи. Для выполнения действий с ячейками, начиная от текущей позиции курсора, макрос надо записывать с относительными ссылками. Для этого используется команда Относительные ссылки (вкладка Вид  группа Макросы  Макросы).

При попытке сохранения нового документа, который содержит макросы, в формате .xlsx Excel выдает предупреждение о том, что в данном формате невозможно сохранить макросы. Для того чтобы включить в файл макросы, необходимо при записи выбрать тип файла Книга Excel с поддержкой макросов. В этом случае расширение файла будет не .xlsx, а .xlsm.

Стандартные настройки Excel блокируют работу макросов при открытии документа, поскольку некоторые макросы могут нести потенциальную угрозу. На всплывающей панели сообщений содержится предупреждение системы безопасности о том, что запуск макросов отключен. На этой панели находится кнопка Параметры и, если есть уверенность в безопасности макросов документа, надо нажать данную кнопку, в появившемся диалоговом окне Параметры безопасности Microsoft Office установить переключатель в положение Включить это содержимое и нажать кнопку ОК. Данные действия необходимо проделывать каждый раз при открытии файла, содержащего макросы.

Пример 3.7. Записать макрос с именем Сдвиг, который выполняет в квадратной матрице 3-го порядка циклическую перестановку столбцов: 2-й столбец поставить на место 1-го, 3-й столбец — на место 2-го, 1-й столбец — на место 3-го и вычисляет обратную матрицу от полученной. Применить созданный макрос для вычисления суммы всех трех обратных матриц, включая обратную матрицу от исходной.

Технология выполнения данной задачи может быть такой.

Перед записью макроса проведем подготовительную работу: на рабочем листе запишем две одинаковые матрицы 3-го порядка и заголовки к исходным и результирующим матрицам (рис. 3.8). Матрица-копия необходима для осуществления контроля над правильностью перестановки столбцов и последующего восстановления исходной матрицы.

	A	B		C		D		E			F		G		H		I
	Перестановка столбцов: 2-й – на место 1-го, 3-й – на место 2-го, 1-й – на место 3-го, вычисление обратной матрицы
1
2		Исходная матрица											Матрица-копия
3		3	6		9							3		6		9
4		2	5		4							2		5		4
5		1	8		7							1		8		7
7
8		Обратная матрица											Сумма обратных матриц
9

Рис. 3.8

Затем вызовем команду Запись макроса (вкладка Вид  группа Макросы  Макросы). В появившемся диалоговом окне введем имя макроса Сдвиг (вместо Макрос1), в поле Сочетание клавиш зададим букву для быстрого запуска макроса, например, z, в поле Описание запишем комментарий к создаваемому макросу. После нажатия кнопки ОК все выполняемые нами действия будут записываться на языке VBA (в строке состояния появилась кнопка Остановить запись).

Выделим 1-й столбец исходной матрицы (В3:В5), вырежем его и поместим в конец матрицы (ячейки Е3:Е5), затем выделим всю матрицу С3:Е5, вырежем ее и поместим на прежнее место В3:D5. Первая часть задачи выполнена — осуществлена перестановка столбцов.

Затем выделим место для обратной матрицы В9:D11 и введем табличную формулу {=МОБР(В3:D5)}. Завершен второй этап задачи — вычислена обратная матрица.

Поскольку в задаче требуется вычислить сумму всех обратных матриц, надо выполнить копирование полученной матрицы в ячейки G9:I11, выделенные под итоговую матрицу. Для этого выделим ячейки В9:D11, выполним команду Копировать, выделим ячейку G9, выполним команду Специальная вставка (вкладка Главная  группа Буфер обмена  Вставить), установив в области Вставить переключатель Значение, а в области Операция переключатель Сложить. В первый момент значения, записанные в ячейках В9:D11 и G9:I11 будут одинаковыми (только в ячейках G9:I11 нет формул), а при последующих запусках макроса при выполнении команды Специальная вставка в ячейках G9:I11 будут записаны суммы соответствующих элементов обратных матриц, поскольку выбрана операция Сложить.

Все указанные в задании действия выполнены. Для прекращения записи выполним команду: Вид  группа Макросы  Макросы  Остановить запись или нажмем кнопку Остановить запись в строке состояния. Запустить макрос на выполнение можно нажатием «горячих» клавиш Ctrl + z или с помощью команды: Вид  группа Макросы  кнопка Макросы  Макросы. Во втором случае из появившегося списка макросов выберем Сдвиг и нажмем кнопку Выполнить. Макрос повторит все действия: перестановку столбцов, вычисление обратной матрицы, копирование обратной матрицы, но все эти действия будут выполнены над уже преобразованной матрицей, записанной в ячейках В3:D5. Результат работы макроса представлен на рис. 3.9.

Запустив макрос еще раз, получим в ячейках G9:I11 искомый результат — сумму трех обратных матриц. Если мы хотим еще раз продемонстрировать работу макроса, то надо удалить данные из ячеек В3:D5 и В9:I11 и с помощью матрицы-копии восстановить значения исходной матрицы. Для удобства выполнения этих действий можно создать простой макрос Сброс. Выполним следующие действия:

• команда: Вид  группа Макросы  Макросы  Начать запись; имя макроса Сброс; «горячие» клавиши Ctrl + t;

• выделим ячейки В3:D5 и B9:I11 и нажмем клавишу Delete;

• выделим ячейки G3:I5, выполним команду Копировать, выделим ячейку В3, выполним команду Вставить;

• команда: Вид  группа Макросы  Макросы  Остановить запись.

Чтобы запустить этот макрос на выполнение надо нажать «горячие» клавиши Ctrl + t или выполнить команду: Вид  группа Макросы  кнопка Макросы  Макросы, из списка макросов выбрать Сброс и нажать кнопку Выполнить.

	A	B	C	D		E	F	G	H		I
	Перестановка столбцов: 2-й – на место 1-го, 3-й – на место 2-го, 1-й – на место 3-го, вычисление обратной матрицы
1
2		Исходная матрица						Матрица-копия
3		9	3	6				3		6		9
4		4	2	5				2		5		4
5		7	1	8				1		8		7
7
8		Обратная матрица						Сумма обратных матриц
9		0,2292	-0,3750	0,0625				0,0208		-0,1250		0,1875
10		0,0625	0,6250	-0,4375				0,2917		0,2500		-0,3750
11		-0,2083	0,2500	0,1250				-0,1458		0,8750		-0,3125

Рис. 3.9

Для удобства запуска макросов надо подготовить две кнопки и назначить им макросы. Выполним такую последовательность действий:

• расположим на рабочем листе в качестве кнопок две любые геометрические фигуры (группа Иллюстрации вкладки Вставка) и с помощью команды контекстного меню Изменить текст дадим кнопкам имена Сдвиг и Сброс;

• щелкнем правой кнопкой мыши по кнопке Сдвиг, в появившемся контекстном меню выберем команду Назначить макрос, затем выберем из появившегося списка макрос Сдвиг и нажмем кнопку ОК;

• аналогично назначим макрос кнопке Сброс.

Теперь, нажимая на кнопки Сдвиг и Сброс, можно видеть результаты выполнения одноименных макросов.

Посмотреть содержимое созданных макросов и внести какие-либо изменения и дополнения, если это необходимо, можно с помощью команды: Вид  группа Макросы  кнопка Макросы  Макросы  имя нужного макроса  кнопка Изменить. Откроется окно интегрированной среды разработки программ на VBA, где в окне кода можно увидеть исходные тексты программ созданных макросов.

Sub Сдвиг( )

'

' Сдвиг Макрос

'

' Сочетание клавиш: Ctrl+z

'

Range ("B3:B5").Select

Selection.Cut

Range("E3").Select

ActiveSheet.Paste

Range ("C3:E5").Select

Selection.Cut

Range("B3").Select

ActiveSheet.Paste

Range ("B9:D11").Select

Selection.FormulaArray = "MINVERSE(R[-6]C:R[-4]C[2])"

Selection.Copy

Range("G9").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, _ SkipBlanks:=False, Transpose:=False

Range("G13").Select

End Sub

--------------------------------------------

Sub Сброс( )

'

' Сброс Макрос

'

' Сочетание клавиш: Ctrl+t

'

Range ("B3:D5,B9:I11").Select

Range("B9").Activate

Selection.ClearContents

Range ("G3:I5").Select

Selection.Copy

Range("B3").Select

ActiveSheet.Paste

End Sub

Строки, которые начинаются знаком ' (апостроф), являются комментариями к программе. Первые восемь операторов подпрограммы Сдвиг осуществляют перестановку столбцов в матрице. В них использованы следующие объекты, свойства, методы: Range — диапазон, ActiveSheet — активный рабочий лист, Select — выделить, Cut — удалить в буфер, Copy — копировать, Paste — вставить. Следующие два оператора выделяют диапазон B9:D11 и вводят в него табличную формулу для вычисления обратной матрицы. При записи формул применяется другой стиль ссылок, называемый стилем R1C1 (Row — ряд, Column — колонка). В этом стиле R1C1 = $A$1, R2C1 = $A$2, R3C2 = $B$3 и т.д. Относительные ссылки записываются с указанием смещения от текущей ячейки. Запись MINVERSE(R[‑6]C:R[–4]C[2]) означает, что надо вычислить обратную матрицу от матрицы, левая верхняя ячейка которой находится в том же столбце, что и текущая ячейка, но на 6 строк выше, а правая нижняя ячейка исходной матрицы находится на 4 строки выше и на 2 столбца правее. Следующая группа операторов предназначена для выполнения команд Копировать и Специальная вставка. В операторе Selection.PasteSpecial Paste:=… указаны опции команды Специальная вставка, выбранные при записи макроса: Paste:=xlPastevalues — установлен переключатель Значения в области Вставить, Operation:=xlAdd — установлен переключатель Сложить в области Операция, SkipBlanks:=False, Transpose:=False — не установлены флажки в полях Пропускать пустые ячейки и Транспонировать.

В текст макроса можно вносить изменения: удалять, добавлять, корректировать операторы. Поскольку созданный нами макрос для выполнения поставленной задачи надо запускать три раза, можно в подпрограмму Сдвиг добавить оператор цикла типа For … Next, чтобы все операторы подпрограммы автоматически выполнялись указанное число раз. Для этого перед первым выполняемым оператором вставим строку For k = 1 To 3, а в конце подпрограммы перед End Sub вставим строку Next k. После Next k можно поставить еще один оператор MsgBox "Конец работы макроса", который после выполнения всех указанных в подпрограмме действий выведет на экран информационное окно с сообщением «Конец работы макроса».

Задания

В этой работе надо выполнить три задания.

Задание 3.1. Действия с матрицами

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

Варианты задания

1) X = A^-1B^-1 – (AB)^-1 2) X = AB(AB)^-1

3) R = A^-1B – AB^-1 4) T = (A + B)C – (AC + BC)

5) Z = A – A^-1CD 6) R = F + DB^-1C

7) X = (E – A)^-1(E + A) 8) Y = E – CDA^-1

9) R = D^T – ACF^-1 10) X = (A + B)^-1CF^-1

11) Y = C^T(A + A^-1) 12) Z = (AB)^-1 – B^-1A^-1

13) X = (A^T )^-1 – (A^-1)^T 14) Z = (D^TD)^-12C

15) X = (AC)D – A(CD)

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

Решить систему 3-х линейных уравнений с 3-мя неизвестными АХ = В двумя способами:

• по формуле Х = А^-1В, где А — матрица коэффициентов при неизвестных, В — столбец свободных членов;

• по формулам Крамера: х_i = |А_i | / |А|, где i = 1, 2, 3; |А| — определитель матрицы А; |А_i | — определитель матрицы А_i, получаемой из матрицы А заменой i-го столбца (т.е. столбца коэффициентов при неизвестном х_i ) вектором свободных членов В.