Файл: МУ к лабам Excel 2013.docx

7. Для создания макросов с помощью макрорекордера необходимо:

• Если вкладка Разработчик недоступна, выполните следующие действия для ее отображения:

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

• выберите команду Настройка ленты;

• в разделе Основные вкладки установите флажок Разработчик;

• нажмите кнопку ОК.

• Для установки уровня безопасности, временно разрешающего выполнение всех макросов, выполните следующие действия:

• на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов;

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

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

8. Запишите макрос в режиме с абсолютными ссылками. Для этого:

• на вкладке Разработчик в группе Код нажмите кнопку Запись макроса;

• в поле Имя макроса введите имя макроса (по умолчанию Макрос1);

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

• в списке Сохранить в выберите книгу, в которой необходимо сохранить макрос (по умолчанию Эта книга);

• введите описание макроса в поле Описание;

• для начала записи макроса нажмите кнопку ОК;

• введите в ячейку C1 слово January, затем создайте ряд (установите курсор на зеленый квадратик в правом нижнем углу активной ячейки C1 и протяните его, не отпуская кнопку мыши, до ячейки C12);

• выделите сформированный ряд и задайте желтый цвет для выделенных ячеек (на вкладке Главная в группе Шрифт);

• на вкладке Разработчик в группе Код нажмите кнопку Остановить запись .

9. Просмотрите последовательность команд Visual Basic, записанную макрорекордером. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос1) и нажмите кнопку Войти. По окончании просмотра программы, записанной макрорекордером, вернитесь в экран Microsoft Excel щелчком по кнопке панели инструментов Standard (Стандартная).

10. Выполните макрос. Для этого:

• активизируйте новый рабочий лист;

• на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос1) и нажмите кнопку Выполнить;

11. Очистите область рабочего листа, нажав на кнопку Выделить все на пересечении заголовков строк и заголовков столбцов, затем на кнопку Delete на клавиатуре и на кнопку Нет заливки пиктографического меню Цвет заливки на вкладке Главная в группе Шрифт.

12. Запишите новый макрос в режиме с относительными ссылками. Для этого:

• на вкладке Разработчик в группе Код нажмите кнопку Относительные ссылки, а затем кнопку Запись макроса;

• в поле Имя макроса введите имя макроса (по умолчанию Макрос2) и нажмите кнопку ОК;

• введите в активную в данный момент (!) ячейку листа слово January, затем создайте ряд (установите курсор на зеленый квадратик в правом нижнем углу активной ячейки и протяните его, не отпуская кнопку мыши, на 11 ячеек вниз);

• выделите сформированный ряд и задайте голубой цвет для выделенных ячеек;

• на вкладке Разработчик в группе Код нажмите кнопку Остановить запись и отожмите кнопку Относительные ссылки.

13. Очистите область рабочего листа.

14. Выполните второй макрос. Для этого:

• выделите произвольную ячейку;

• на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос2) и нажмите кнопку Выполнить;

15. Сравните тексты программ Макрос1 и Макрос2, расположенные в Модуле1. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя макроса (Макрос1 или Макрос2) и нажмите кнопку Войти. По окончании просмотра программ, записанных макрорекордером, вернитесь в экран Microsoft Excel щелчком по кнопке панели инструментов Стандартная.

16. Запишите самостоятельно новый макрос (Макрос3), очи­щаю­щий области рабочего листа, занятые результатами работы макросов, и проверьте его выполнение.

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

17. Создайте кнопку для вызова Макрос1. Для этого:

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

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

• в появившемся диалоговом окне Назначить макрос объекту выберите в списке макросов Макрос1 и щелкните кнопку OK;

• откорректируйте название кнопки (назовите, например, «Месяцы»);

 Примечание.  Чтобы указать свойства кнопки, щелкните ее правой кнопкой мыши и выберите пункт Формат объекта.

18. Выполните Макрос1 с помощью кнопки.

19. Создайте кнопку для вызова Макрос3 и выполните этот макрос с помощью кнопки.

20. Добавьте кнопку запуска макросов на панель быстрого доступа. Для этого:

• выполните Файл/Параметры/Панель быстрого доступа;

• в списке Выбрать команды из выберите Макросы и нажмите кнопку Добавить;

• нажмите ОК.

 Примечание.  Для перемещения панели быстрого доступа щелкните кнопку Настроить панель быстрого доступа и выберите в списке Разместить под лентой.

Запуск макросов с помощью командной кнопки в форме

21. Создайте электронную форму для ввода данных в таблицу сведений о студентах. Форма должна содержать:

• заголовок «Сведения о студенте»;

• поле для ввода фамилии с инициалами;

• поле со списком для выбора номера группы;

• список для выбора наименования специальности;

• 2 переключателя для выбора пола;

• счетчик для выбора года рождения (1990—2010);

• кнопку для запуска макроса, осуществляющего запись сведений о студенте в таблицу, расположенную на другом листе.

Для этого выполните следующие действия:

• переименуйте один из листов книги Excel в «Форма»;

• разместите на листе «Форма» в ячейках А30:А39 список номеров 10 групп, например, 8271-8280. Разместите в ячейках С30-С39 список названий специальностей;

• введите в ячейку D2 заголовок формы: “Сведения о студенте”. Введите в ячейки В4, В5, В7, В12, В15 следующие названия: ФИО, Группа, Специальность, Пол, Год рождения;

• в ячейку D4 введите фамилию;

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

• щелкнув правой клавишей мыши по элементу Поле со списком, вызовите контекстное меню. Выберите пункт Формат объекта;

• установите вкладку Элемент управления. Щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с номерами групп. Разверните вкладку. Щелкните по кнопке сворачивания в поле Связь с ячейкой, затем щелкните по ячейке H5 и разверните вкладку. В поле Количество строк введите значение 5. Включите флажок Объемное затемнение, нажмите ОК;

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

• введите в ячейку D5 формулу для расшифровки порядкового номера группы в списке: =ИНДЕКС($А$30:$А$39;$Н$5). Используйте вариант функции со ссылкой. Убедитесь в правильности вывода номера группы в ячейке D5;

• на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Список и очертите прямоугольный контур в области ячеек G7:I10. Вызовите контекстное меню элемента Список и выберите пункт Формат объекта;

• щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с названиями специальностей. Разверните вкладку. Включите флажок выбора только одинарного значения, затем щелкните по кнопке сворачивания в поле Связь с ячейкой и введите адрес ячейки щелчком по кнопке K7. Разверните вкладку и включите флажок Объемное затемнение. Нажмите ОК;

• убедитесь в возможности выбора названия специальности из списка и изменении порядкового номера в ячейке К7;

• введите в ячейку D7 формулу для расшифровки порядкового номера группы в списке: =ИНДЕКС($С$30:$С$39;$K$7). Убедитесь в правильности названия специальности в ячейке D7;

• на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Переключатель и очертите прямоугольный контур в области ячейки F12. Вызовите контекстное меню элемента Переключатель и выберите пункт Формат объекта;

• на вкладке Элемент управления щелчком по ячейке D12 введите в поле Связь с ячейкой ее абсолютный адрес, включите флажок Значение установлен и нажмите ОК. Замените название флажка на «М»;

• аналогично расположите значок переключателя в области ячейки F13 и замените его название на «Ж», при этом повторного связывания с ячейкой не требуется;

• в разделе Элементы управления формы выберите элемент Счетчик и очертите прямоугольный контур в области ячеек F15:F16. Вызовите контекстное меню элемента Счетчик и выберите пункт Формат объекта;

• на вкладке Элемент управления введите в поле Текущее значение: 1990. Введите в поле Минимальное значение: 1990. Введите в поле Максимальное значение: 2010. Введите в поле Шаг изменения: 1. Введите в поле Связь с ячейкой абсолютный адрес ячейки D15, нажмите ОК;

• проверьте работу счетчика;

• в разделе Элементы управления формы выберите элемент Кнопка и очертите прямоугольный контур в области ячеек C18:D18. Появится окно Назначить макрос объекту. Закройте окно, не назначая макрос. Замените название кнопки на «Запись в таблицу».

22. Создайте на новом листе с именем «Список студентов» во 2-ой строке шапку таблицы с названиями столбцов: ФИО, Группа, Специальность, Пол, Год рождения. Отрегулируйте ширину столбцов.

23. На листе «Форма» в ячейки B25, С25, D25, E25, F25 вставьте формулы, ссылающиеся на ячейки D4, D5, D7, D12 и D15. Проверьте формулы в ячейках B25:F25:

В ячейке В25 должна быть формула: =$D$4

В ячейке С25 должна быть формула: =$D$5

В ячейке D25 должна быть формула: =$D$7

В ячейке Е25 должна быть формула: =$D$12

В ячейке F25 должна быть формула: =$D$15

24. Осуществите запись начального макроса макрорекордером. Для этого:

• на вкладке Разработчик в группе Код нажмите кнопку Запись макроса;

• в поле Имя макроса введите имя макроса (по умолчанию);

• для начала записи макроса нажмите кнопку ОК;

• на листе Форма выделите ячейки B25:F25;

• на вкладке Главная в группе Буфер обмена нажмите кнопку Копировать;

• перейдите на лист Список студентов и выделите ячейку А3;

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

• на вкладке Разработчик в группе Код нажмите кнопку Остановить запись;

25. Проверьте работу созданного макроса. Для этого на листе «Список студентов» очистите диапазон ячеек А3:Е3, перейдите на лист «Форма», на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Выполнить. Строка сведений будет вставлена на то же место.

26. Для того, чтобы новые сведения вставлялись в таблицу в следующие по порядку строки, необходимо откорректировать текст макроса. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Изменить. Откроется окно редактора Visual Basic.

27. В окне редактора Visual Basic внесите изменения в текст программы после строки Sheets("Список студентов").Select

При этом должны быть следующие строки:

Sheets("Список студентов").Select

Range("A2").Select

If Cells(3, 1).Value <> "" Then

Cells(2, 1).Select

ActiveCell.End(xlDown).Cells(2).Select

Else

Range("A3").Select

End If

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

End Sub

28. Закройте окно редактора, щелкнув по самому левому значку на инструментальной панели редактора с изображением логотипа Excel. Повторно выполните макрос.

29. Назначьте кнопке «Запись в таблицу» созданный макрос. Для этого выделите кнопку правой клавишей мыши, в контекстном меню выберите пункт Назначить макрос, в окне Назначить макрос объекту выделите соответствующий макрос и нажмите ОК.

30. Выполните макрос щелчком по кнопке.

31. С помощью созданного макроса заполните список студентов данными о принятых в университет студентах (10-15 человек).

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

Соответствующие данные должны заноситься в справку автоматически посредством выбора фамилии студента из поля со списком.

Для этого выполните следующие действия:

• Разместите на листе «Справка» в ячейках A1:G10 постоянный текст справки так, чтобы для ввода фамилии использовалась ячейка D4, для ввода года рождения – E4, для ввода № группы – В7, наименования специальности – D7.

• На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Поле со списком и очертите указателем мыши прямоугольный контур в зоне ячеек A1:В2. Вызовите контекстное меню элемента Поле со списком и выберите пункт Формат объекта;

• Установите вкладку Элемент управления. Щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с фамилиями студентов без заголовка на листе Список студентов. Разверните вкладку. Щелкните по кнопке сворачивания в поле Связь с ячейкой. Щелкните по ячейке А20. В поле Количество строк введите значение 6;

• Перейдите на вкладку Свойства. Снимите флажок Выводить объект на печать. Закройте окно Форматирование объекта кнопкой ОК.

• Проверьте правильность работы поля со списком, наблюдая за номером элемента, отображаемого в ячейке А20 при выборе фамилии в списке;

• Присвойте диапазону ячеек, в котором находится список, имя Список. Для этого выделите диапазон ячеек, содержащий все данные о студентах без заголовков на листе Список студентов, введите в поле имен имя Список и нажмите клавишу Enter;

• Введите в ячейку D4 формулу для отображения выбранной фамилии:

=ИНДЕКС(Список;$A$20;1)

 Примечание.   Для ввода в качестве аргумента имени диапазона выберите имя Список на вкладке Формулы в группе Определенные имена из списка Использовать в формуле.

• Введите в ячейку Е4 формулу для отображения года рождения:

=ИНДЕКС(Список;$A$20;5);

• Аналогично введите в ячейку В7 формулу для отображения номера группы, а в ячейку D7 – формулу для вывода наименования специальности.

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

32. Сохраните рабочую книгу на диске в файле с именем lab6.xlsm, причем в окне Сохранение документа в списке Тип файла выберите тип файла Книга Excel с поддержкой макросов.

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

• на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов;

• в категории Параметры макросов в группе Параметры макросов нажмите кнопку Включить все макросы (не рекомендуется, возможен запуск опасной программы), а затем нажмите ОК.