6. Закройте программу MS Excel.

7. Создайте факс на основе созданного шаблона FAX.xltx:

• загрузите программу MS Excel 2013;

• на стартовой странице в списке ЛИЧНЫЕ выберите и загрузите шаблон FAX.

8. Введите произвольную информацию в загруженный шаблон. Начиная с 17 строки, введите текст передаваемого по факсу сооб­щения. Проанализируйте изменение форматов вывода при вводе данных в строки 5, 7, 8, 17-27.

9. Сохраните созданный документ с именем «Факс» и закройте его.

10. Откорректируйте шаблон с целью защиты от записи группы ячеек. Для этого:

• запустите программу MS Excel и загрузите шаблон FAX;

• выделите несмежные области, в которых не требуется защита от изменений (диапазон ячеек в строках 5, 7, 8, 11-14, 17-27, блок ячеек С10:D10). Выделять при нажатой клавише CTRL;

• выполните команду Главная/Ячейки/Формат/Защита /Формат ячеек;

• в диалоговом окне Списки на вкладке Формат ячеек снимите флажок Защищаемая ячейка;

• нажмите OK;

• для защиты листа выполните команду Рецензирование/Изменения/Защитить лист;

• в диалоговом окне «Защита листа» введите пароль (запомните его!) и нажмите OK;

• подтвердите введенный ранее пароль и снова нажмите OK.

11. Сохраните откорректированный шаблон и закройте его.

12. Закройте документ Excel без сохранения.

13. Загрузите шаблон для создания нового факса.

14. Выполните попытку редактирования названия фирмы «Рога и копыта», изменения даты и других защищенных ячеек. Закройте документ.

15. Снимите защиту с шаблона с целью добавления в него элементов управления. Для этого:

• откройте шаблон факса с именем FAX.xltx;

• для снятия защиты выполните команду Рецензиро­вание/Изменения/Снять защиту листа;

• в диалоговом окне «Снять защиту листа» введите пароль и нажмите OK;

16. Встройте в шаблон некоторые элементы управления, чтобы бланк можно было использовать не только для факса, но и для письма (см. рис 2.).

Рис. 2. Шаблон с элементами управления

Для этого:

• вставьте несколько (8) пустых строк в верхнюю часть ра­бочего листа;

• добавьте вкладку Разработчик на ленту. Для этого выполните команду Файл/ Параметры, выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик.

• разместите на рабочем листе элемент управления Счетчик, выполнив команду Разработчик/Элементы управления/ Вставить/Элементы управления формы/Счетчик;

• установите курсор (крестообразной формы) на место размещения этого элемента на рабочем листе, нажмите левую клавишу мыши и растяните рамку на необходимый размер, затем отпустите клавишу мыши (см. рис. 2);

• над объектом Счетчик разместите аналогичным образом объект Подпись с текстом «Срочность»;

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

• для изменения названия внедренного объекта установите указатель мыши на объект, щелкните правой клавишей, в контекстном меню выберите команду Изменить текст и введите название;

• объедините кнопки объекта Переключатель с помощью объекта Группа в группу «Отправитель».

17. Определите действия для объекта Счетчик. Для этого:

• щелкните по нему правой клавишей мыши;

• в контекстном меню выберите команду Формат объекта;

• на вкладке Свойства уберите флажок Выводить объект на печать;

• на вкладке Элемент управления диалогового окна Формат элемента управления введите соответственно 1, 1, 3, 1 в окошки ввода Текущее значение, Минимальное значение, Максимальное значение и Шаг изменения, а в окно ввода Связь с ячейкой введите адрес той ячейки, в которую выбранные значения будут помещены (например, $L$6);

• нажмите ОК;

• введите в область рабочего листа, например, L2:M4 таблицу 1:

Таблица 1

• в ячейку, расположенную под названием бланка (например, J12) введите формулу вывода срочности, установленной объектом Счетчик:

=ВПР(L6; L2:M4;2);

• проверьте действие объекта Счетчик.

18. Определите действия для переключателя «Факсом». Для этого:

• щелкните по нему правой клавишей мыши;

• в контекстном меню выберите команду Формат объекта;

• на вкладке Свойства уберите флажок Выводить объект на печать;

• на вкладке Элемент управления диалогового окна Формат элемента управления выберите в поле Значение переклю­чатель «установлен», в окне Связь с ячейкой введите ссылку на любую свободную ячейку (например, $L$1);

• нажмите ОК.

19. Определите действия для переключателя «Почтой» аналогично п. 18, только в поле Значение выберите переключатель «снят».

20. В ячейку, расположенную под названием бланка (напри­мер, J11) введите формулу вывода поля для указания номера факса при выборе переключателя «Факсом»:

=ЕСЛИ(L1=1;”ATTN______________”;””)

21. Введите в область рабочего листа, например O1:Q6, табл.2:

Таблица 2

Во второй и третий столбец первой строки таблицы 2 введите пробелы.

22. Встройте в шаблон элемент управления Список для автома­тического заполнения строки «Кому» на основании данных табл. 2 (см. рис.2). Для этого:

• разместите на рабочем листе элемент управления Список по команде Разработчик/Элементы управления/Вставить /Элементы управления формы/Список;

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

• над объектом Список разместите аналогичным образом объект Подпись с текстом «Список адресатов».

23. Определите действия для объекта Список и заполнения строки «Кому». Для этого:

• щелкните по нему правой клавишей мыши;

• в контекстном меню выберите команду Формат объекта;

• на вкладке Свойства уберите флажок «Выводить объект на печать»;

• на вкладке Элемент управления диалогового окна Формат элемента управления введите:

• в окно ввода Формировать список по диапазону блок ячеек табл. 2, содержащий фамилии адресатов (с первой строкой);

• в окно ввода Связь с ячейкой адрес той ячейки, в которую выбранное значение будет помещено (например, $L$7);

• нажмите кнопку OK;

• в ячейку строки «Кому» (например, В13) введите формулу вывода фамилии выбранного в списке адресата (используйте функцию ВПР);

• проверьте действие объекта Список.

24. Самостоятельно внедрите объект Поле со списком с названием «Список фирм» для создания раскрывающегося списка выбора названий фирм, определите действия для объекта Поле со списком и заполнения строки «Фирма».

25. Просмотрите шаблон факса перед печатью. Для этого выполните команду Файл/Печать и убедитесь, что вставленные в шаблон элементы управления не будут выводиться на печать. Для внедренных объектов управления, которые вывелись на печать, необходимо в диалоговом окне Формат элемента управления на вкладке Свойства убрать флажок Выводить объект на печать.

26. Для выхода из режима печати и предварительного просмотра нажмите кнопку в левом верхнем углу окна Печать.

27. Выберите в объектах Список и Поле со списком элементы без текста. Сохраните шаблон и закройте его.

28. Создайте факс на основе шаблона, используя для заполнения управляющие элементы. Сохраните документ в личной папке с именем lab5.xlsx и закройте его.

ЛАБОРАТОРНАЯ РАБОТА № 6

Математические функции МОБР, МОПРЕД и МУМНОЖ.

Запись макросов с помощью макрорекордера

и способы выполнения макросов

Цель лабораторной работы

Лабораторная работа служит для получения практических навыков по изучению следующих тем:

• использование встроенных математических функций для работы с матрицами: МОБР (для вычисления обратной матрицы), МОПРЕД (для вычисления определителя матрицы) и МУМНОЖ (для перемножения матриц);

• использование макрорекордера для записи линейной программы на языке Visual Basic for Applications (VBA) и определение различных способов для запуска созданных макросов.

Основные сведения об использовании функций

МОБР, МОПРЕД, МУМНОЖ

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

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

1. Функция МОБР возвращает обратную матрицу для мат­рицы, хранящейся в массиве.

МОБР(массив)

Массив – это числовой массив с равным количеством строк и столбцов.

• Массив может быть задан как диапазон ячеек, например А1:С3, или как имя диапазона или массива.

• Если какая-либо из ячеек в массиве пуста или содержит текст, то функция МОБР возвращает значение ошибки #ЗНАЧ!.

• МОБР также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное число строк и столбцов.

2. Функция МОПРЕД возвращает определитель матрицы (мат­рица хранится в массиве).

МОПРЕД(массив),

где массив – см. п. 1.

3. Функция МУМНОЖ возвращает произведение матриц (мат­рицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2.

МУМНОЖ(массив1;массив2)

Массив1, массив2 – это перемножаемые массивы.

• Количество столбцов аргумента массив1 должно быть таким же, как количество строк аргумента массив2, и оба массива должны содержать только числа.

• Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки.

• Если хотя бы одна ячейка в аргументах пуста, или если число столбцов в аргументе массив1 отличается от числа строк в аргументе массив2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.

Основные сведения о макросах

В EXCEL VBA-макрос может быть двух типов: подпрограммой и функцией.

Макрос-подпрограмма может быть выполнена любым пользователем, либо другим макросом. Она начинается ключевым словом SUB и заканчивается END SUB. Строки, заключенные между этими операторами, составляют текст макроса.

С помощью макрорекордера можно записать только макрос-подпрограмму.

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

Содержание лабораторной работы

Выполнение данной лабораторной работы включает в себя:

• использование встроенных математических функций МОБР, МОПРЕД и МУМНОЖ для вычисления обратной матрицы, определителя матрицы и перемножения матриц;

• запись указанных последовательностей действий макрорекордером в виде VBA-макросов с абсолютными и относительными ссылками;

• запуск созданных макросов с помощью кнопок и меню.

Выполнение лабораторной работы

Использование функций МОБР, МОПРЕД и МУМНОЖ

1. Найдите матрицу, обратную данной:

Для этого:

• введите элементы матрицы в диапазон ячеек А1:С3;

• для получения обратной матрицы выделите несмежный диапазон ячеек такого же размера, например E1:G3, и введите формулу массива {=МОБР(А1:С3)}. Для заключения формулы в фигурные скобки после ввода формулы нажмите клавиши CTRL+Shift+Enter.

2. Вычислите определитель матрицы А. Для этого выделите любую свободную ячейку, например А5, и введите формулу

=МОПРЕД(А1:С3)

3. Вычислите произведение матрицы А на матрицу В, где

; .

Для этого:

• введите элементы матрицы А в диапазон ячеек А10:С11;

• введите элементы матрицы В в диапазон ячеек А13:С15;

• выделите диапазон ячеек с таким же числом строк, как массив А, и с таким же числом столбцов, как массив В, например, E10:G11 и введите формулу

{=МУМНОЖ(А10:С11; А13:С15)};

• нажмите CTRL+Shift+Enter.

4. Решите систему линейных уравнений с 3-мя неизвестными

(1)

методом обратной матрицы.

Обозначим

; (2)

; .

Решение системы (1) в матричной форме имеет вид АХ = В,

где: А – матрица коэффициентов;

Х – столбец неизвестных;

В – столбец свободных членов.

При условии, что квадратная матрица (2) системы (1) невырожденная, т.е. ее определитель А  0, существует обратная матрица А. Тогда решением системы методом обратной матрицы будет матрица-столбец X = A B. Найдем это решение. Для этого:

• Найдем определитель А = 5 (см. п. 2). Для этого активизируем новый рабочий лист и введем элементы матрицы коэффициентов А в диапазон ячеек А1:С3. Выделим любую свободную ячейку, например А5, и введем формулу

=МОПРЕД(А1:С3).

• Так как А  0, то матрица А – невырожденная, и существует обратная матрица А. Найдем обратную матрицу. Для этого выделим несмежный диапазон ячеек такого же размера, что и матрица А, например E1:G3, и введем формулу массива {=МОБР(А1:С3)}.

• Найдем решение системы в виде матрицы-столбца

X = A B.. Для этого введем элементы матрицы В в диапазон ячеек E6:E8, выделим диапазон ячеек с таким же числом строк, как массив А, и с таким же числом столбцов, как массив В, например, G6:G8 и введем формулу массива

={МУМНОЖ(E1:G3; E6:E8)};

Получим:

,

т.е. решение системы (4; 2; 1).

Запись макросов с помощью макрорекордера

5. Активизируйте новый рабочий лист.

6. Добавьте к существующим встроенным спискам (месяцев, дней недели) новый пользовательский список автозаполнения. Для этого:

• в ячейки А1:А12 введите: January, February, March, April, May, June, July, August, September, October, November, December;

• выделите на листе список элементов, которые требуется включить в список автозаполнения (диапазон A1:A12);

• на вкладке Файл выберите пункт Параметры;

• выберите категорию Дополнительно, а затем в разделе Общие нажмите кнопку Изменить списки;

• убедитесь, что ссылка на ячейки в выделенном списке элементов отображается в поле Импорт списка из ячеек, и нажмите кнопку Импорт. Элементы выделенного списка будут добавлены в поле Списки;

• два раза нажмите кнопку ОК.