Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

Таблица 5.4.

Данные о продажах

№	Информация	Тип данных
1	Номер продажи	числовой
2	Дата продажи	дата
3	Код товара	числовой
4	Наименование товара	строковый
5	Цена (розничная)	числовой
6	Количество	числовой

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

Первые две таблицы относительно небольшие и информация в них меняется сравнительно редко. Такого рода таблицы, содержащие условно-постоянную информацию, обычно называются справочными.

Две других таблицы очень обширны и постоянно дополняются новыми записями, поскольку процессы поставок и продаж идут постоянно.

Независимой информацией в этих таблицах является только дата поставки/продажи, поставленное/проданное количество товара и его код.

Остальная информация (наименование товара и цена) определяется по коду товара из справочных таблиц.

Таким образом все таблицы связаны между собой в следующую схему данных – рис. 5.1.

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

Это прежде всего:

1. 
   Суммы продаж. Необходимы для расчета выручки от продаж. Расчет ведется по следующей схеме.
   

Для каждого факта продажи сначала рассчитывается сумма продажи:

V_i = k_i*C_i, (5.1)

где V_i – выручка в i-ой продаже;

k_i – количество товара в i-ой продаже;

C_i – розничная цена товара в i-ой продаже.

Розничная цена рассчитывается по формуле;

C_r = C_opt*(1+N), (5.2)

где C_opt – оптовая цена товара;

N – величина наценки на данный товар.

---

Общая сумма выручки определяется как сумма всех продаж за отчетный период времени:

V₀ = ∑V_i (5.3)

2. 
   Суммы поставок. Необходимы для расчета сумм оплаты поставщикам. Расчет ведется по следующей схеме.
   

Для каждого факта поставки сначала рассчитывается сумма поставки:

S_i=k_i*C_i, (5.4)

где S_i – сумма i-ой поставки;

k_i – количество товара в i-ой поставке;

C_i – оптовая цена товара в i-ой поставке.

Общая сумма поставок определяется как сумма всех поставок за отчетный период времени:

S₀ = ∑S_i (5.5)

3. 
   Прибыль за отчетный период. Рассчитывается исходя из первых двух показателей по формуле:
   

P = V₀ – S₀ (5.6)
5.2.2. Разработка проекта

5.2.2.1. Проектирование базы данных

Исходя из разработанной информационной модели магазина база данных должна состоять из четырех взаимосвязанных таблиц – рис. 5.1.

Однако исходя из требований получения отчетности эти таблицы должны быть расширены:

– в таблице «Продажи» необходимо добавить поле «Сумма продажи», которое рассчитывается по формуле (5.1).

– в таблице «Поставки» необходимо добавить поле «Сумма поставки», которое рассчитывается по формуле (5.4).

Кроме того, система должна обеспечивать типовые операции при работе с базой данных:

– ввод новых данных;

– удаление ненужных данных;

– редактировать имеющиеся данные;

– производить сортировку данных по заданным критериям;

– производить поиск необходимой информации.

– формировать необходимые отчеты. Например, отчеты о доходах и расходах.
5.2.2.2.Проектирование общей архитектуры

Под архитектурой понимается общая структура ИС. Она тесно связана с интерфейсом и дизайном системы. Продуманность указанных аспектов должна обеспечить удобство работы конечных пользователей и, в конечном счете, определяет коммерческий успех ИС.

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

В данном случае можно предложить следующую архитектуру.

1. При запуске ИС должна появиться заставка;

2. После щелчка по ней мышью система должна перейти к главному меню

---

;

3. Главное меню должно обеспечивать доступ к следующим функциям системы:

– сведения о товарах;

– сведения о продажах;

– сведения о поставщиках;

– сведения о поставках;

– отчеты;

– выход.

4. При выборе соответствующего пункта меню система должна переходить на нужную форму (применительно к Excel произойдет переход на соответствующий лист), где реализованы необходимые операции.

5. На каждом листе должна находиться кнопка возврата к главному меню.

Спроектированная архитектура в виде схемы представлена на рис.5.2.


Рис. 5.2. Общая архитектура ИС

5.2.3. Создание ИС

5.2.3.1. Создание общей архитектуры

Работа по созданию ИС начинается с создания ее компонентов.

Основные методы использования возможностей Excel при разработке приложений описаны в [7].

Поэтому создадим новый документ Excel и, в соответствии с проектом, создадим эти компоненты. Очевидно, что исходя из возможностей Excel, запланированные компоненты будут находиться на отдельных листах.

По умолчанию в Excel уже имеются три листа.

Поэтому командой Вставка > Лист создаем нужное количество листов.

Переименуем листы в соответствии с проектом:

Лист1 –> Заставка

Лист2 –> Меню

Лист3 –> Товары

Лист4 –> Продажи

Лист5 – > Поставщики

Лист6 – > Поставки

Лист7 – > Отчеты
5.2.3.2. Организация переходов между объектами

Начнем с оформления заставки:

– переходим на лист «Заставка»;

– вызываем объект WordArt и оформляем его нужным образом (рис. 5.3).


Рис. 5.3. Пример оформления заставки
Естественно, что оформление заставки дело также очень творческое и здесь можно использовать все средства компьютерной графики.

Например:

– убрать сетку листа, заголовки строк и столбцов (командой Сервис > Параметры > Снять галочки с параметра «Сетка» и «Заголовки строк и столбцов»);

– командой Формат > Лист > Подложка

---

выбрать фоновый рисунок.

В функциональном плане от заставки требуется только вывести на экран название системы и по щелчку мыши перейти на лист меню.

При работе в Excel переход между листами обычно производится путем выбора ярлычков нужного листа.

Но наличие ярлычков характерно только для Excel. В прикладных же программах переходы осуществляются с использованием Windows или Web–интерфейса. Все эти возможности несложно реализовать и в Excel.

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

Поэтому:

– выделяем надпись на титульном листе и выполним команды:

Вставка > Гиперссылка > Место в документе > Меню > Ok;

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

Однако для этой цели имеются и более профессиональные средства – это, прежде всего, макросы.

В общем случае макросы представляют собой встроенные в документ Excel программы на Visual Basic. Они применяются в тех случаях, когда встроенных средств Excel не достаточно. По назначению их можно весьма условно разделить на следующие группы:

1. Макросы, обеспечивающие автоматизацию операций по обеспечению работы в Excel;

2. Макросы вычислительного характера;

3. Макросы, обеспечивающие необходимый интерфейс для вычислений.

В настоящем разделе покажем использование макросов для создания интерфейса.

На рис.5.4 приведен вариант интерфейса, который мы хотим создать в нашей системе.


Рис. 5.4. Внешний вид главного меню ИС

---

5.2.3.3. Этапы создания интерфейса

Создание макросов для кнопок

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


Таблица 5.5.

Назначение кнопок

N	Кнопка	Назначение
1	«Товары»	Переход на лист «Товары»
2	«Продажи»	Переход на лист «Продажи»
3	«Поставщики»	Переход на лист «Поставщики»
4	«Поставки»	Переход на лист «Поставки»
5	«Отчеты»	Переход на лист «Отчеты»
6	«Выход»	Выход из Excel
7	«Меню» на расчетных листах	Переход на лист «Меню»

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

1. Перейти на Лист1.

2. Выполнить команды – Сервис > Макрос > Начать запись.

3. На запрос о параметрах макроса необходимо только указать осмысленное имя макроса. Например, Товары и затем «Ok». При вводе имени макроса нельзя использовать пробелы.

4. Система перейдет в режим записи макроса. Но в Excel, в отличие от Word, нет внешних признаков того, что система находится в режиме записи. Поэтому здесь необходимо очень аккуратно выполнить только необходимые команды и тут же остановить запись.

5. Применительно к рассматриваемой задаче – щелкнуть по ярлычку «Товары» и затем Сервис > Макрос > Остановить запись.

Точно также можно создать макросы перехода – «Продажи», «Расчет з/п», «Отчеты» и т.д.

Но удобнее (и быстрее) остальные макросы создать следующим образом:

7. 
   После создания первого макроса («База данных») перейти в редактор Visual Basic – Сервис > Макрос > Макросы > Выбрать только что созданный > Изменить.
   
8. 
   Система перейдет в редактор Visual Basic, в котором мы увидим, как выглядит наш макрос в виде команд Бейсика. Если все было сделано правильно, то там должно быть примерно следующее:
   

---