Материал: LS-Sb87076
«Массив» может быть задан как диапазон ячеек, например А1:С3, или как имя диапазона или массива. Если какая-либо из ячеек в массиве пуста или содержит текст, то функция МОБР возвращает значение ошибки #ЗНАЧ!. МОБР также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное число строк и столбцов.
2.Функция МОПРЕД возвращает определитель матрицы (матрица хранится в массиве):
МОПРЕД (массив), где массив – см. п. 1.
3.Функция МУМНОЖ возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив 1, и с таким же числом столбцов, как массив 2:
МУМНОЖ(массив1;массив2),
где массив1, массив2 – это перемножаемые массивы. Количество столбцов аргумента массив1 должно быть таким же, как количество строк аргумента массив2, и оба массива должны содержать только числа. Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки. Если хотя бы одна ячейка в аргументах пуста, или если число столбцов в аргументе массив1 отличается от числа строк в аргументе массив2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.
Основные сведения о макросах
В EXCEL VBA-макрос может быть двух типов: подпрограммой и функцией.
Макрос-подпрограмма может быть выполнена любым пользователем, либо другим макросом. Она начинается ключевым словом SUB и заканчивается END SUB. Строки, заключенные между этими операторами, составляют текст макроса.
С помощью макрорекордера можно записать только макрос-подпрограм- му. Макрорекордер записывает действия пользователя, которые можно потом многократно воспроизводить. Текст макроса может быть записан как с абсолютными, так и с относительными ссылками.
Содержание лабораторной работы
Выполнение данной лабораторной работы включает в себя:
16
использование встроенных математических функций МОБР, МОПРЕД и МУМНОЖ для вычисления обратной матрицы, определителя матрицы и перемножения матриц;
запись указанных последовательностей действий макрорекордером в видеVBA-макросовсабсолютнымииотносительнымиссылками;
запуск созданных макросов с помощью кнопок и меню.
Выполнение лабораторной работы
Использование функций МОБР, МОПРЕД и МУМНОЖ
1. Найдите матрицу, обратную данной:
|
1 |
2 |
1 |
|
|
3 |
4 |
|
|
A |
1 . |
|||
|
0 |
2 |
0 |
|
|
|
|||
Для этого:
введите элементы матрицы в диапазон ячеек А1:С3;
для получения обратной матрицы выделите несмежный диапазон ячеек такого же размера, например E1:G3, и введите формулу массива {=МОБР(А1:С3)}. Для заключения формулы в фигурные скобки после ввода формулы нажмите клавиши «CTRL» + «Shift» + «Enter».
2.Вычислите определитель матрицы А. Для этого выделите любую свободную ячейку, например А5, и введите формулу
=МОПРЕД(А1:С3)
3.Вычислите произведение матриц А и В, где
|
1 |
0 |
2 |
|
|
1 |
0 |
1 |
|
|
; |
|
5 |
1 |
4 |
|
|||||
A |
3 |
1 |
0 |
|
B |
. |
||||
|
|
|
|
2 |
0 |
1 |
|
|||
|
|
|
|
|
|
|
|
|||
Для этого:
введите элементы матрицы А в диапазон ячеек А10:С11;
введите элементы матрицы В в диапазон ячеек А13:С15;
выделите диапазон ячеек с таким же числом строк, как матрица А,
ис таким же числом столбцов, как матрица В, например E10:G11,
ивведите формулу
{=МУМНОЖ(А10:С11; А13:С15)};нажмите «CTRL» + «Shift» + «Enter».
17
4.Решите систему линейных уравнений с тремя неизвестными
x |
|
x |
x |
3; |
|
||||
1 |
|
2 |
3 |
|
|
|
|
||
2x1 x2 x3 11; |
(7.1) |
||||||||
x |
|
x |
2x |
|
|
8 |
|
||
1 |
|
2 |
|
3 |
|
|
|
||
методом обратной матрицы. |
|
|
|
|
|
|
|
|
|
Обозначим |
|
|
|
|
|
|
|
|
|
|
1 |
1 |
1 |
|
|
|
|||
|
2 |
|
1 |
1 |
|
|
(7.2) |
||
A |
|
; |
|
||||||
|
1 |
|
1 |
2 |
|
|
|
||
|
|
|
|
|
|||||
x1 |
|
|
|
|
|
3 |
|
|
|
X x |
; |
|
B 11 . |
(7.3) |
|||||
2 |
|
|
|
|
|
8 |
|
|
|
x |
|
|
|
|
|
|
|
||
3 |
|
|
|
|
|
|
|
АХ = В, где: А – |
|
Решение системы (7.1) в матричной форме имеет вид |
|||||||||
матрица коэффициентов; Х – столбец неизвестных; В – столбец свободных членов.
При условии, что квадратная матрица (7.2) системы (7.1) – невырожденная, т. е. ее определитель А 0, существует обратная матрица А–1. Тогда ре-
шением системы методом обратной матрицы будет матрица-столбец X = А–1B. Найдем это решение. Для этого:
Найдем определитель А = 5 (см. п. 2). Для этого активизируем новый рабочий лист и введем элементы матрицы коэффициентов А в диапазон ячеек А1:С3. Выделим любую свободную ячейку, например А5, и введем формулу
=МОПРЕД(А1:С3).
Так как А 0, то матрица А – невырожденная, и существует обратная матрица А–1. Найдем обратную матрицу. Для этого выде-
лим несмежный диапазон ячеек такого же размера, как матрица А, например E1:G3, и введем формулу массива {=МОБР(А1:С3)}.
|
|
0,2 |
0,6 |
0,4 |
|
A 1 |
|
0,6 |
0,2 |
0,2 |
|
|
. |
||||
|
|
0,2 |
0,4 |
0,6 |
|
|
|
|
Найдем решение системы в виде матрицы-столбца X = А–1B. Для этого введем элементы матрицы В в диапазон ячеек E6:E8, выде-
18
лим диапазон ячеек с таким же числом строк, как массив А–1, и с таким же числом столбцов, как массив В, например, G6:G8 и введем формулу массива
={МУМНОЖ(E1:G3; E6:E8)}
Получим:
4 X A 1B 2 ,1
т. е. решение системы (4; 2; 1).
Запись макросов с помощью макрорекордера
1.Активизируйте новый рабочий лист.
2.Добавьте к существующим встроенным спискам (месяцев, дней недели) новый пользовательский список автозаполнения. Для этого:
в ячейки А1:А12 введите: January, February, March, April, May, June, July, August, September, October, November, December;
выделите на листе список элементов, которые требуется включить в список автозаполнения (диапазон A1:A12);
щелкните значок «Кнопка Microsoft Office» 
, а затем «Пара-
метры Excel»;
выберите «Основные», и затем в группе «Основные параметры для работы в Excel» в строке «Создавать списки для сортировки и заполнения» нажмите кнопку «Изменить списки»;
убедитесь, что ссылка на ячейки в выделенном списке элементов отображается в поле «Импорт» списка из ячеек, и нажмите кнопку «Импорт». Элементы выделенного списка будут добавлены в поле «Списки»;
два раза нажмите кнопку «OK».
3.Для создания макросов с помощью макрорекордера необходимо:
если вкладка «Разработчик» недоступна, выполнить следующие действия для ее отображения:
–щелкнуть значок «Кнопка Microsoft Office» 
, а затем «Пара-
метры Excel»;
19
–в группе «Основные параметры работы с Excel» установить флажок «Показывать вкладку "Разработчик" на ленте», а затем нажать кнопку «OK».
4.Для установки уровня безопасности, временно разрешающего выполнение всех макросов, выполните следующие действия:
–на вкладке «Разработчик» в группе «Код» нажмите кнопку «Безопасность макросов»;
–в группе «Параметры макросов» выберите переключатель «Включить все макросы» (не рекомендуется, возможен запуск опасной
программы) и нажмите кнопку «OK».
Примечание. Для предотвращения запуска потенциально опасного кода по завершении работы с макросами рекомендуется вернуть параметры, отключающие все макросы.
5.Запишите макрос в режиме с абсолютными ссылками. Для этого:
–на вкладке «Разработчик» в группе «Код» нажмите кнопку Запись макроса;
–в поле «Имя макроса» введите имя макроса (по умолчанию «Мак-
рос1»);
Примечание. Первым символом имени макроса должна быть буква. Последующие символы могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не допускаются пробелы; в качестве разделителей слов следует использовать знаки подчеркивания. Если используется имя макроса, являющееся ссылкой на ячейку, может появиться сообщение об ошибке, указывающее на недопустимое имя макроса.
в списке «Сохранить в» выберите книгу, в которой необходимо сохранить макрос (по умолчанию «Эта книга»);
введите описание макроса в поле «Описание»;
для начала записи макроса нажмите кнопку «OK»;
введите в ячейку C1 слово January, затем создайте ряд (установите курсор на черный квадратик в правом нижнем углу активной ячейки C1 и протяните его, не отпуская кнопку мыши, до ячейки C12);
выделите сформированный ряд и задайте розовый цвет для выделенных ячеек (на вкладке «Главная» в группе «Шрифт»);
на вкладке «Разработчик» в группе «Код» нажмите кнопку «Остановить запись» 
. (Можно также нажать кнопку «Остановить запись» слева от строки состояния.)
20