Практическая работа № 9 Тема: Создание телефонной книги и электронной сметы

Цель: Научиться использовать макросы при решении задач различной сложности

Задание:

1. Создать макрос телефонной книги своей группы.

2. Создать макрос электронной праздничной сметы на подарки

Ход работы

Задание 1.Создание телефонной книги с использованием макросов по следующему алгоритму:

I. Создать новый файл;

2. Подготовить рабочую область. Для этого переименовать листы:

• «Лист!» - «База»;

• «Лист2» - «Переменные»;

• «ЛистЗ» - «Результат».

3. Заполнить лист «База»:

• в ячейку А1 вводим — «ФИО»;

• в ячейку В1 вводим - «Адрес»;

• в ячейку С1 вводим — «Телефон»;

• выделяем ячейки с А1 по С1 ив системном меню выбирать Данные -> Форма... В результате чего появится окно удобного заполнения ячеек, с помощью которого вносим 10 записей, завершая внесение каждой записи нажатием кнопки «Добавить». Для возврата в рабочую книгу следует нажать кнопку «Закрыть».

4. Заполнить лист «Переменные»:

• ячейку А1 оставить пустой (для ввода сегодняшней даты );

• в ячейку А2 ввести формулу: =ДВССЫЛ(АДРЕС(А 1 + 1;2;;;"База"));

• в ячейку АЗ ввести формулу: =ДВССЫЛ(АДРЕС(А1+1;3;;; «База»)).

Функция ДВССЫЛ() возвращает ссылку, заданную текстовой строкой. Она используется, если требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.

Функция АДРЕС() создаёт адрес ячейки в виде текста, используя номер строки и номер столбца.

А1 + 1 — вторая строка;

2(3) — второй (третий) столбец;

"База" - имя листа (указывается в кавычках)

5. Заполняем лист «Результат»:

- в ячейку А1 ввести функцию текущей даты:=СЕГОДНЯ();

- для вывода выбранных фамилий в строке «ФИО»,в ячейку А2 записать формулу вида: =ДВССЫЛ(АДРЕС(Переменные!А1 + 1;1;;;"База"));

- в ячейки АЗ и А4 занести формулы, благодаря которым переменные будут изменяться в соответствии с выбранной фамилией:

АЗ - =Переменные!А2;

А4 - =Переменные!АЗ.

6. Создать диалоговое окно:

• удерживая клавишу Shift, выделить заголовки листов;

• удерживая правую кнопку мыши на выделенных заголовках, в появившемся меню выбрать пункт «Добавить». В результате появится окно, из которого выбираем «Окно диалога Ехсеl 5.0» нажать кнопку «ОК»;

• после этого появится новый лист «Диалог1», на котором размещены редактируемое окно диалога и панель инструментов «Формы»

- переименовать заголовок листа из «Диалог1» в «Телефонная книга»;

- удалить одну из кнопок на форме, оставшуюся переименовать в «Выход»;

- используя инструмент «Надпись» создать метки «ФИО», «Адрес» и «Телефон»;

-с помощью инструмента «Поле со списком» создать раскрывающийся список, располагая его напротив метки «ФИО» и осуществить привязку раскрывающегося списка к ячейкам:

а) щелчок левой кнопкой мыши на раскрывающемся списке;

б) на панели инструментов «Формы» выбрать кнопку «Свойства элемента управления

- на вкладке «Элемент управления» в строке «Формировать список по диапазону» указать диапазон ячеек, в котором лежат фамилии людей (База!$А$2:$А$11), в строке «Связь с ячейкой» указать ячейку, куда будет заноситься результат (Переменные!$А$1), в строке «Количество строк списка» указать количество отображаемых в списке записей – 10. Затем нажать «ОК» и возвратиться на окно диалога;

- используя кнопку «Текстовое поле» создать поля ввода, располагая их напротив меток

«Адрес» и «Телефон».

7. Создать макрос:

• заходим в системное меню, выбираем Сервис -> Макрос -> Макросы... Либо, используя комбинацию клавиш А11+Р8, активизировать окно пользовательских макросов

• ввести имя макроса - «Телефон»;

• нажать кнопку «Создать»;

• ввести текст макроса:

Sub Auto_open()

DialogSheets("Телефонная книга").Show

End Sub

Данный макрос отвечает за автоматический запуск диалогового окна при открытии файла «Телефонная книга». Он будет размещён в этой книге, где:

DialogSheets — диалоговое окно;

Show — открыть (показать);

- в этом же окне ввести текст второго макроса: Sub ТЕЛЕФОН()

Sheets ("Телефонная книга").DrawingObjects ("Поле ввода N").Characters.Техt = Sheets ("Переменные"). Range("А2")

Sheets ("Телефонная книга").DrawingObjects ("Поле ввода N").Characters.Техt = Sheets ("Переменные"). Range("АЗ")

End Sub

В данном макросе осуществляется присвоение значений ячеек А2 и АЗ, расположенных на листе «Переменные», полям ввода в диалоговом окне «Телефонная книга», возвращающим адрес и телефон, где:

Sheets — лист;

DrawingObjects — рисуемый объект;

N - номер вашего поля ввода (его можно увидеть при выделении поля ввода в поле имени);

Characters -тип;

Техt — текстовый;

Range - адрес.

8. Осуществить привязку макроса «Телефон» к раскрывающемуся списку и полям ввода:

• возвратиться на лист «Телефонная книга»;

• Удерживая клавишу Shift, выделить раскрывающийся список и поля ввода;

• На одном из выделенных объектов щелкнуть правой кнопкой мыши и в контекстном меню выбрать пункт «Назначить макрос»;

• В появившемся окне макросов выбрать макрос «Телефон» и нажать кнопку «ОК».

«Закрыть».

Задание 2 Создание электронной праздничной сметы

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

1. Запустить MS Excel;

2. Сохранить файл как «Смета новогоднего поздравления»

3. Подготовить рабочую область, т.е. переименовать листы:

• «Лист1» - «Поздравитель»;

• «Лист2» - «Подарок»;

• «Лист3 – «Переменные»;

• «Лист4» - «Смета».

4. Заполнить лист «Поздравитель»:

• в ячейку А1 ввести – «Поздравитель»;

• в ячейку В1 ввести – «Стоимость»;

• выделить ячейки А1, В1 и активизировать пункт системного меню Данные →Форма…

На экране выводится форма. С её помощью создать пять записей. Для возрата в рабочую книгу нажать кнопку «закрыть».

5. Заполнить лист «Подарок»

• в ячейку А1 ввести - «Наименование»;

• в ячейку В1 ввести — «Стоимость»;

• выделить ячейки А1, В1 и зайти в системное меню, а именно: Данные → Форма...

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

6. Заполнить лист «Переменные» (для удобства ссылок присвоить ячейкам имена):

• выделить ячейку А1;

• выбрать пункт меню: Вставка → Имя →Присвоить

• на экране выводится окно присвоения «Присвоение имени»;

• в поле «Имя» ввести – «Н_Поздравителя»;

• далее аналогично присвоить:

а) ячейке В1 - «Н_Подарка»;

б) ячейке С1 - «Н_Концерта»;

в) ячейке О1 - «Н_Тип_оплаты»;

г) ячейке Е1 - «Н_Постоянный_клиент»;

• в ячейку А2 ввести формулу:

=ДВССЫЛ(АДРЕС(Н_Поздравителя+1;2;;; “Поздравитель”))

• в ячейку В2 ввести:

= ДВССЫЛ(АДРЕС(Н_Подарка+1;2;;; “Подарок”))

- в ячейку С2 занести

=ВЫБОР(Н_Концерта;0;350;750)

Функция ВЫБОР() возвращает в зависимости от значения в ячейке «Н_Концерта» одну из стоимостей;

- в ячейку D2 ввести:

=СУММ(А2:С2)

Функция СУММ() вычисляет сумму ячеек с А2 по С2, в задаче - это сумма заказа без скидок и наценок;

- в ячейку Е2 ввести:

=ЕСЛИ(Н_Постоянный_клиент;D2*0,9;D2)

- в ячейку F1 ввести:

=ЕСЛИ(Н_Тип_оплаты;Е2;Е2*1,25).

7. Создать диалоговое окно:

• щелкнуть правой кнопкой мыши на перечне заголовков листов;

• в контекстном меню выбрать пункт «Добавить»;

• на вкладке «Общие» выбрать «Окно диалога Ехсе! 5.0» (см. рис. 1.13);

• затем нажать «ОК».

8. Заполнить появившееся диалоговое окно:

• переименовать заголовок листа из «Диалог1» в «Смета новогоднего поздравления»

• для заполнения использовать доступную панель инструментов «Формы» (если она не отображается, то: Вид → Панели инструментов → Формы);

• удалить одну из кнопок на форме, оставшуюся переименовать в «Выход»;

• используя кнопку «Надпись», создать метки «Клиента поздравляет» и «Преподнесённый подарок»;

• используя инструмент «Поле со списком» создать списки, располагая их напротив меток «Клиента поздравляет» и «Преподнесённый подарок»;

- осуществить привязку раскрывающихся списков к ячейкам:

а) щелкнуть левой кнопкой мыши на первом раскрывающемся списке;

б) на панели инструментов «Формы» выбрать кнопку «Свойства элемента управления»

в) на вкладке «Элемент управления» в строке «Формировать список по диапазону» указать диапазон ячеек, в котором лежит список поздравителей (Поздравитель!$А$2:$А$6), в строке «Связь с ячейкой» указать ячейку, куда будет заноситься результат (Н_Поздравителя), в строке «Количество строк списка» указать «5», затем нажать «ОК» и возвратиться на окно диалога;

• аналогичным образом осуществить привязку второго раскрывающего­ся списка: «Формировать список по диапазону»: Подарок!$А$2:$А$5; «Связь с ячейкой»: Н_Подарка ; «Количество строк списка»: 4;

• ниже разместить группу, используя кнопку «Группа», заменив текст заголовка на «Вид концерта»;

• внутри группы разместить три радиокнопки, используя кнопку «Переключатель»:

а) изменить название первой радиокнопки на «НЕТ»;

б) используя кнопку «Свойства элемента управления», осуществить настройку радиокнопки;

в) на вкладке «Элемент управления» в строке «Связь с ячейкой» ввести «Н_Концерта»;

г) на той же вкладке установить переключатель в состояние «Установлен»;

д) изменить название второй радиокнопки на «Стандартный»;

е) сменить название третьей радиокнопки на «Специальный». Свойства последних двух радиокнопок устанавливаются автоматически;

• расположить первый «Флажок» вне группы:

а) сменить название флажка на «Предоплата»;

б) используя кнопку «Свойства элемента управления» настроить флажок;

в) на вкладке «Элемент управления» в строке «Связь с ячейкой» ввести «Н_Тип_оплаты»;

г) на той же вкладке установить состояние флажка «Снят»;

- расположить второй флажок немного ниже первого:

а) изменить название флажка на «Постоянный клиент»;

б) «Свойства элемента управления»;

в) на вкладке «Элемент управления» в строке «Связь с ячейкой» ввести «Н_Постоянный_клиент»;

г) на той же вкладке установить состояние «Снят»;

• используя кнопку «Надпись» создать метку «Стоимость заказа»;

• с помощью инструмента «Текстовое поле» создать поле ввода, располагая его напротив метки «Стоимость заказа».

9. Создать макрос:

- зайти в системное меню, выбрать Сервис → Макрос → Макросы...

• ввести имя макроса - «Сумма»;

• в открывшемся окне редактора Visual Basic набрать текст макроса: Sub Auto_open()

DialogSheets("Смета новогоднего поздравления").

Show End Sub

Данный макрос отвечает за автоматический запуск диалогового окна при открытии файла «Смета новогоднего поздравления».Он будет размещен в этой книге.

• здесь же вводим текст второго макроса:

Sub Сумма()

Sheets("Смета новогоднего поздравления").DrawingObjects("Поле ввода N').Characters.Техt =Sheets("Переменные").Range("F1")

End Sub

В данном макросе осуществляется присвоение значения ячейки F1 на «Переменные» полю ввода в диалоговом окне «Смета поздравления», которое возвращает итоговую стоимость заказа.

- осуществить привязку макроса «Сумма» к раскрывающимся спискам, полю ввода, радиокнопкам и флажкам:

а) необходимо вернуться на лист «Смета новогоднего поздравления»;

б) удерживая клавишу Shift, выделить все элементы управления, влияющие на итоговую стоимость заказа;

в) на одном из выделенных объектов щёлкнуть правой кнопкой мыши;

г) в контекстном меню выбрать пункт «Назначить макрос»;

д) в появившемся окне макросов выбираем макрос «Сумма» и нажимаем кнопку «ОК»;