Тема: Автоматизация вычислений в Excel с помощью функций поиска.

Частное предприятие «Альянс» поставляет топливо (бензин марок А95, А92, А76 и дизельное топливо) на заправки города. На каждую поставку оформляется накладная.

Оформить таблицу поставок топлива с сентября по ноябрь месяц 2010 года по образцу:

Задание:

1. В таблице создать 20 записей (строк).
2. Значение поля (столбца) «Номер накладной» должно быть уникальным (т.е. неповторяющимся).
3. Для значений полей (столбцов) «Код заправки» и «Марка топлива» организовать выбор значений из списка.
4. Значение поля «Адрес заправки» найти с помощью функции =ВПР().
5. Значение поля «ФИО владельца» вычислять с помощью функции =ГПР().
6. Значение поля «Дата поставки» заполнить с помощью элемента управления Календарь. Для этого необходимо ыполнить следующую последовательность действий:

§ Открыть редактор Visual Basic: п.м. Сервис – Макрос – Редактор Visual Basic.

§ Создать пользовательскую форму: Insert – User form. Задать свойство формы Caption – Календарь.

§ Добавить на панель элементов новый элемент управления: щелкнуть по серому фону панели правой кнопкой мыши и выбрать Additional Controls, выбрать переключатель Календарь (Calendar Control 8.0).

§ Добавить на форму ЭУ Календарь.

 

 

§ Создать следующие процедуры:

§ для считывания значения даты в активную ячейку:

§

§ для установки даты при активизации формы

§

§ Создать модуль для организации показа созданной формы: Insert – Module:

§

§ Создать в MS Excel макрос для вызова процедуры ShowCalendar: п.м. Сервис – Макрос – Макросы. Назначить макросу подходящее сочетание клавиш (кнопка Параметры).Private Sub Calendar1_Click()

§ActiveCell = Calendar1.Value

§ActiveCell.NumberFormat="dd/mm/yy"

§End Sub

 

§Private Sub UserForm_Activate()

§Me.Calendar1.Value = Date

§End Sub

§Sub ShowCalendar()

§UserForm1.Show

§End Sub

 

§ Ячейке с заголовком Дата добавить примечание о способе вызова календаря: п.м. Вставка – Примечание.

1. Значение поля «Количество литров» вводить с клавиатуры.
2. Значение поля «Цена за 1л» вычислить с помощью функции =ПРОСМОТР().
3. Поле «Наценка» вычислять с помощью функции =Если() по следующему правилу: если поставка осуществлялась в субботу или воскресение (функция → Дата/время → =ДЕНЬНЕД() 1 -воскресение, 7 -суббота, то наценка будет составлять 3% от цены).
4. Поле «Скидка» вычислять с помощью функции =Если() по следующему правилу: если количество литров меньше 100, то скидки нет, если количествоот 100 до 400, то скидка – 3,5%, если количество от 400, до 700, то скидка 5%, а если количество больше 700, то скидка составит 7%.
5. Поле «Наличный расчёт» оформить с помощью пункта меню Вид → Панель инструментов →Формы → ЭУ Флажок (установить связь ЭУ с той же ячейкой, где находится флажок. (В ячейке будет содержаться значение «истина» - если флажок установлен и значение «ложь», если флажок снят).
6. Поле «Стоимость топлива, руб.» вычислять как «цена» * «количество» + «наценка» - «скидка» с учётом «наличного расчёта». ( Если наличный расчёт, то стоимость увеличивается на 0,05% )
7. Поле «Стоимость топлива, у.е.» вычислять по курсу на момент даты поставки из листа Справочный! с помощью функции =ПРОСМОТР().
8. Справочный лист оформить по образцу.

Образец расчётной таблицы:

Образец справочного листа: