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 должна быть формула: =ИНДЕКС($A$30:$A$39;$H$5)
В ячейке D25 должна быть формула: =ИНДЕКС($C$30:$C$39;$K$7)
В ячейке Е25 должна быть формула: =$D$12
В ячейке F25 должна быть формула: =$D$15
24. Осуществите запись начального макроса макрорекордером. Для этого:
· на вкладке Разработчик в группе Код нажмите кнопку Запись макроса;
· в полеИмя макросавведите имя макроса(по умолчанию);
· для начала записи макроса нажмите кнопку ОК;
· на листе Форма выделите ячейки B25:F25;
· на вкладкеГлавнаяв группеБуфер обмена нажмите кнопку Копировать;
· перейдите на лист Список студентов и выделите ячейку А3;
· на вкладке Главная в группе Буфер обмена раскройте список Вставитьи выберите команду Вставить значения;
· на вкладке Разработчик в группе Код нажмите кнопку Остановить запись;
25. Проверьте работу созданного макроса. Для этого на листе «Список студентов» очистите диапазон ячеек А3:Е3, перейдите на лист «Формы», на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Выполнить. Строка сведений будет вставлена на то же место.
26. Для того чтобы новые сведения вставлялись в таблицу в следующие по порядку строки, необходимо откорректировать текст макроса. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Изменить. Откроется окно редактора Visual Basic.
27. В окне редактора Visual Basic внесите изменения в текст программы после строки Sheets("Список студентов").Select
28. Закройте окно редактора, щелкнув по самому левому значку на инструментальной панели редактора с изображением логотипа Excel. Повторно выполните макрос.
29. Назначьте кнопке «Запись в таблицу» созданный макрос. Для этого выделите кнопку правой клавишей мыши, в контекстном меню выберите пункт Назначить макрос, в окне Назначить макрос объекту выделите соответствующий макрос и нажмите ОК.
30. Выполните макрос щелчком по кнопке.
31. С помощью созданного макроса заполните список студентов данными о принятых в университет студентах (10-15 человек).
32. Используя созданный в предыдущем задании список студентов, создайте на новом листе с именем «Справка» автоматизированную форму для выдачи справки студенту следующего образца:
СПРАВКА
Настоящая справка выдана студенту_____________ _____ года рождения
в том, что он (она) в настоящее время учится в Санкт-Петербургском
государственном инженерно-экономическом университете
в группе _____ по специальности ____________
Ректор университета Иванов А.В.
Дата выдачи 2 декабря 2014 г.
Соответствующие данные должны заноситься в справку автоматически посредством выбора фамилии студента из поля со списком.
Для этого выполните следующие действия:
· Разместите на листе «Справка» в ячейках 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 с поддержкой макросов.
Примечание. Чтобы запустить макросы после открытия сохраненной книги, необходимо установить уровень безопасности, временно разрешающий выполнение всех макросов. Для этого:
· на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов;
· в категории Параметры макросов в группе Параметры макросов нажмите кнопку Включить все макросы (не рекомендуется, возможен запуск опасной программы), а затем нажмите ОК.
Важно! Для предотвращения запуска потенциально опасного кода по завершении работы с макросами рекомендуется вернуть параметры, отключающие все макросы.
Список литературы
1. Сурядный А. С. Microsoft Office 2010. Лучший самоучитель. – М.: АСТ, Астрель, ВКТ, 2011. – 512 с.
2. Васильев А. А., Стоцкий Ю. А., Телина И. С. Microsoft Office 2010. Самоучитель. – Спб.: Питер, 2011. – 432 с.
3. Курбатова Е. Microsoft Office Excel 2010. Самоучитель. – М.: Диалектика/Вильямс, 2010. – 416 с.
4. Уокенбах Д. Microsoft Excel 2010. Библия пользователя. – М.: Диалектика/Вильямс, 2011. – 912 с.
5. Карлберг К. Бизнес-анализ с использованием Excel. – М.: Вильямс, 2012. – 576 с.
6. Уокенбах Д. Формулы в Microsoft Excel 2010. – М.: Диалектика, 2011.—704 с.
7. Сингаевская Г.Функции в Microsoft Office Excel 2010. – М.: Диалектика/Вильямс, 2011. – 1094 с.
8. Джелен Б., Александер М. Сводные таблицы в Microsoft Excel 2010. – М.: Диалектика/Вильямс, 2011. – 464 с.
[1] Надстройка – вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.
[2] Макрос – действие или набор действий, используемые для автоматизации выполнения задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA).
