Тема: Автоматизация вычислений в 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. Справочный лист оформить по образцу.

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

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