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

Целью создания электронной сметы является создание пользовательского диалога с различными элементами управления, который предназначен для оперативного расчета стоимости услуги, оказываемой разрабатываемой фирмой, либо стоимости набора товаров, если предприятие занимается коммерческой деятельностью.

Рассмотрим пример. В абстрактную коммерческую фирму, организационную подразделения на новогодние праздники, приходят разные клиенты со всевозможными запросами. Фирма может предложить набор услуг из большого перечня доступных. В электронной смете клиент (или представитель фирмы со слов клиента) выбирает только те услуги, которые ему необходимы, а смета и итоговая цена формируется автоматически. Все сообщение ведется через специально разработанный диалог, перечень услуг и расценки на них введены заранее.

4.1. Подготовительный этап работы

После запуска Ехсе1, необходимо оставить для работы 4 листа рабочей книги, а остальные удалить.

Далее необходимо переименовать оставшиеся листы:

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

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

Лист 3 - «Переменные»

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

Все сделанные изменения нужно сохранить.

4.2. Создание списков

Совокупность строк листа, содержащих однотипные данные, например, имена клиентов и их телефоны или название товаров и их стоимость называется списком. Список может использоваться в качестве базы данных, в которой записи соответствуют строкам списка, а поля - столбцам. Работать со списком легче, если он имеет заголовки столбцов. Создадим списки с перечнем услуг, предлагаемых фирмой. В данном примере это два списка:

поздравителей и подарков. Для создания списков выполним следующие действия:

- перейдем на лист «Поздравитель»;

- в ячейку А1 введем «Имя», а в ячейку В 1 «Стоимость»;

- выделим ячейки от А1 до В2. Выполним команду меню Данные-Форма. После чего появится форма «Поздравитель», для ввода значений в список.

- В поле «Имя» введем «Санта - Клаус», а в поле «Стоимость» 195. Далее используем кнопку «Добавить» и эти данные будут занесены во вторую строчку таблицы, а поля «Имя» и «Стоимость» формы очистятся для ввода следующего элемента списка. Этим же способом введем следующие данные:

Дед Мороз 230

Диснеевские персонажи 347

Дед Мороз и Снегурочка 347

Настоящий волшебник 985

- поле ввода всех данных используем кнопку «Закрыть». Форма закроется, а все веденные данные останутся на текущем листе.Этиданные можно добавлять, удалять и редактировать.

- установим ширину столбца А=23,00, В= 10,00

- перейдем на лист «Подарок». Также введем список подарков. В качестве заголовков введем «Название» и «Стоимость», а элементами списка будут служить следующие данные:

Набор сладостей95

Игрушка 345

Познавательный набор 450

Исполнение желания 1220

имеются и другие услуги, но в данном примере они вводятся не с использованием списков, а с помощью иных средств.

4.3. Присвоение имен вспомогательным ячейкам

Ячейки на листе «Переменные» будут использоваться для хранения промежуточных результатов вычислений. Для удобства ссылок на них можно присвоить им имена. Выполнением следующего действия.

- перейдем на лист «Переменные» и щелкнуть мышью в ячейку А1 выполним команду меню Вставка - Имя - Присвоить. После появления диалогового окна ввести Номер -Поздравителя. Присвоить имена следующим ячейкам.

В1 Номер_подарка

С1 Номер_концерта

D1 Тип_оплаты

Е1 Постоянный_клиент

F1 Итоговая_сумма

4.4. Создание диалога

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

выбрать команду меню Вставка - Макрос - Диалоговое окно. После этого к рабочей книге добавится лист «Диалог 1» и на экране появится панель инструментов «Элементы управления», с ее помощью в окне диалог располагаются элементы управления. В центре листа расположено окно диалога с кнопками ОК и Отмена. Это окно необходимо отредактировать и привести к требуемому виду.

после размещения элементов управления окно диалога должно иметь вид:

(вид)

- Для любого размещенного элемента управления в диалоговом окне, необходимо определить его свойства. Для этого нужно выделить этот элемент и активизировать кнопку «Свойства элемента» на панели инструментов «Элементы управления». И в появившемся диалоговом окне определить все необходимые параметры.

Правильность работы диалога можно проверить запустив его кнопкой «Запуск диалога».

4.5. Программирование логики электронной сметы

Для программирования логики электронной сметы нужно записать некоторые формулы на лист «Переменные» и создать два простейших макроса.

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

- в ячейку А2 введем формулу:

=ДВССЫЛ (АДРЕС (Номер_поздравителя + 1; 2;;; «Поздравитель»)), функция АДРЕС возвращает адрес ячейки на листе «Поздравитель», в котором содержится стоимость поздравителя, номер которого содержится в ячейке Номер - поздравителя. А Номер в эту ячейку помещается с помощью диалога. Номер поздравителя увеличивается на единицу, т.к. в первой строке списка находится его заголовок, а первое данное находится во второй строке «2» в качестве второго параметра указывает на столбец В, в котором и расположены суммы. Функция ДВССЫЛ ( ) подставляет значение, находящееся по данному адресу.

- в ячейку В2 введем

=ДВССЫЛ (АДРЕС(Номер_подарка + 1; 2;;; «Подарок»)).

- в ячейку С2 введем:

=ВЫБОР (Номер_концерта; 0; 350; 780) Функция ВЫБОР возвращает в зависимости от значения ячейки Номер_концерта одно из чисел, перечисленных в качестве аргументов.

- в ячейку Д2 введем:

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

в этой ячейке будет считываться стоимость услуг без учета скидок и наценок, обусловленных способом оплаты и тем, является ли клиент постоянным.

- в ячейку Е2 введем:

=ЕСЛИ (Постоянный_клиент; Д2*0,9;Д2) функция ЕСЛИ возвращает значение ячейки Д2 или то же значение, уменьшенное на 10% в зависимости, от состояния ячейки Постоянный -клиент. Если в ячейке Постоянный - клиент значение ИСТИНА, то есть клиент постоянный, то стоимость уменьшается, в противном случае стоимость не меняется

- в ячейку F1 введем формулу

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

эта формула аналогична предыдущей, при отсутствии предоплаты стоимость увеличивается на 25%

Все формулы записаны на лист «Переменные», а в ячейке Итоговая_ сумма появилось значение суммы заказа. Теперь необходимо ввести два простых макроса. Для этого выполним команду меню Вставка - Макрос -Модуль. К рабочей книге добавится лист с названием «Модуль!». Введем следующий текст:

'Макрос, автоматически запускающий диалог

'при открытии документа

'Sub Auto Open ( )

'DialogSheets («Диалог1») Show

'End Sub

'

'Макрос обновления суммы заказа в поле окна диалога

Sub Пересчет - суммы ( )

Sheets («Диалог1»). Drawing Objects (Edit Вох 16).

Characters. Техt; = Sheets («Переменные»). Range («К1»)

End Sub

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

Макрос Пересчет_суммы предназначен для обновления значения в поле Стоимость заказа.

Это значение рассчитано в ячейке F1 на листе «Переменные». Для того, чтобы этот пересчет выполнялся каждый раз при изменении полей диалога, выполним следующие действия:

- перейдем на лист Диалог 1. Удерживая клавишу Shift произведем щелчки мышью последовательно по всем элементам, которые могут изменить значение итоговой суммы по списку, раскрывающемуся списку, флажкам и переключателям. Все эти элементы будут выделены.

- выведем контекстное меню и выберем в нем команду Назначить макрос, после чего откроется диалог «Назначить макрос объекту», в котором будут отображены названия двух введенных ранее макросов. Выберем в списке макрос Пересчет_суммы и ОК. Теперь при любом изменении состава услуг будет вызываться этот макрос, и поле с итоговой суммой будет автоматически обновляться.

- теперь необходимо сделать возможным его вызов из меню Ехсе1. Для этого выполним команду меню Сервис - Макрос. Появится диалог Макрос со списком доступных макросов, из которого выберем макрос Auto – Open и, активизируем кнопкой Параметры и в появившемся диалоговом окне «Параметры макроса» в группе «Назначить макросу» включим флажок в поле «Пункт в меню «Сервис», а в поле ниже флажка введем «Запуск диалога». После чего внизу меню Сервис появится новая строчка «Запуск диалога». И используя эту команду запустится разработанный диалог, но в отличии от предыдущего раза при каждом изменении состава заказа будет меняться общая стоимость в поле Стоимость заказа.