Практическая часть

Постановка задачи

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

Порядок выполнения работы

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

1. Спроектируем рабочую книгу, включающую листы: «Список книг», «Продавцы», «Справочные данные», «Заказы», «Учет заказов», «Оплата продавцов», «Переменные».

2. На листе «Список книг» введем название будущих полей таблицы: Код книги, Автор, Название, Издательство, Год издания, Цена. Эти названия будут являться заголовками столбцов с соответствующими данными.

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

Ввод информации в базу данных непосредственно в таблице Excel – самый простой, но не самый удобный способ. Использование формы для работы с базой данных предпочтительнее непосредственного изменения данных в списке с точки зрения их целостности. Форма обеспечивает добавление и удаление всей записи целиком, не может возникнуть «загрязнение» базы данных не до конца удаленными или неправильно введенными записями. Кроме того, снижается вероятность случайного изменения значений полей при просмотре базы данных.

4. Установим курсор в ячейку А2, выберем команду Форма (Меню-Параметры Excel-Настройка-Все команды-Форма-Добавить). Появится диалоговое окно (с именем листа), позволяющее выполнять основные операции по добавлению, изменению, удалению и поиску данных в базе данных (рис. 39).

5. Используя кнопку Добавить, добавим еще несколько (15-20) записей в таблицу. Для удобства ввода от поля к полю можно перемещаться с помощью клавиши Tab, для возврата в предыдущее поле можно нажать комбинацию клавиш Shift+Tab. После ввода всего списка нажмем кнопку Закрыть. Кнопка Удалить служит для удаления записи из базы данных Excel. Кнопка Вернуть – для восстановления п режнего значения при ошибочном изменении записи.

Рис. 39. Окно формы «Список книг»

4. Добавив информацию в список книг в таблице Excel, самостоятельно попробуем найти ранее введенные записи в базе данных. Для этого вновь откроем окно формы и, щелкнув по кнопке Критерии, зададим условия поиска в необходимые поля. Для поиска записи, предшествующей текущей и удовлетворяющей установленному условию, нужно щелкать по кнопке Назад. Для поиска следующей записи, соответствующей условию, нажимают кнопку Далее. Если критерий не установлен, по умолчанию будут использоваться все записи.

При поиске необходимой записи в случае отсутствия полной информации можно использовать символы подстановки – * и ?. При работе с числовыми значениями можно использовать операторы сравнения – <, >, =, о, <=, >=

4. Аналогично на листе «Продавцы» введем названия будущих полей списка: Код продавца, Фамилия, Имя, Отчество, Разряд. Заполним поля первой записи и командой Данные-Форма откроем окно формы Продавцы для последующего ввода данных (5-7 записей).

5. На листе «Справочные данные» создадим таблицу с полями Разряд и Разрядный коэффициент и заполним ее 3-5 записями. В отдельную ячейку внесем данные о минимальной оплате труда.

6. . Для организации связанных полей используем формы Excel. На листе «Заказы» самостоятельно создадим форму для ввода заказов на литературу, используя элементы панели Вставить и функции Excel.

Рис.40.Форма «Заказ литературы»

Для этого выделим цветом диапазон ячеек в качестве области для будущей формы. Внесем имена необходимых полей. Активизируем панель инструментов Вставить командой меню Разработчик-Вставить и, используя элементы этой панели и функции Excel, создадим форму «Заказ литературы» (рис. 40).

4. Промежуточные результаты вычислений поместим на листе «Переменные». Перейдем на этот лист и щелкнем мышью в ячейке А1. Выполним команду меню Присвоить (Меню-Параметры Excel-Настройка-Все команды-Присвоить имя-Добавить) и в открывшемся окне диалога Присвоение имени в поле Имя: введем Вид_платы и нажмем кнопку ОК. Теперь во всех ссылках вместо Переменные!$А$1 можно вводить Вид_оплаты. Это будет нагляднее и проще при разработке больших приложений.

Более простой способ присваивания имени ячейки – щелкнуть по ячейке мышью, а затем щелкнуть мышью по полю Имя, расположенному слева от строки формул, ввести название ячейки и нажать клавишу Enter.

Присвоим ячейки В1 имя Постоянный_клиент и диапазону ячеек D1:E2 – имя Код_книги.

4. Для формирования раскрывающегося списка поля Автор, с целью выбора автора, на панели инструментов Вставить выберем элемент Поле с списком и поместим его на форму. Установим необходимые параметры в свойствах элемента, выбрав команду Формат объекта из контекстного меню (рис. 41).

Рис. 41. Окно «Формат элемента управления»

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

Аналогичным способом создадим поле со списком для выбора названия книг.

4. Дату оформления заказа зададим функцией СЕГОДНЯ() для вывода текущей даты.

5. Поскольку стоимость книги определяется ее кодом, однозначно указывающим на автора и название, необходимо проставить его при составлении заказа. Для этого предварительно с листа «Список книг» из шапки таблицы на лист «Переменные» в ячейку D1 скопируем заголовок столбца Название, а в ячейку Е1 – заголовок столбца Автор. В ячейку D2 введем формулу для отображения результата при выборе названия книги в форме «Заказ литературы»:

=ИНДЕКС('Списоккниг'!$А$5:$F$7;Заказы!D10;3).

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

Таким образом, диапазон ячеек 'Переменные'!D1:Е2, с именем Код_книги, является критерием для выбора кода заказываемой книги. Для возвращения значения выбранного кода книги в поле Код книги в форме «Заказ литературы» введем формулу =БИЗВЛЕЧЬ('Список книг!A4:F7; ''Список книг!A4;Код_книги). Функция БИЗВЛЕЧЬО извлекает из базы данных значение, удовлетворяющее заданному критерию.

4. Для автоматического возвращения значения стоимости книги в зависимости от ее кода в поле Стоимость введем формулу

=ИНДЕКС('Списоккниг'!$А$5:$Р$10;В12;6).

В случае выбора значений полей Автор и Название книги, не принадлежащих одной записи в полях Код книги и Стоимость, будет выведено сообщение об ошибке #ЗНАЧ!.

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

15. Предусмотрим систему скидок в зависимости от наличия предварительной оплаты при оформлении крупных заказов, например, более 30 экземпляров. Выделим рамкой диапазон ячеек для размещения элементов, указывающих вид оплаты. На панели инструментов Вставить выберем кнопку Переключатель , поместим его внутри рамки и изменим на нем текст на «Нет». Выполним команду Формат объекта, в открывшемся окне Формат элемента управления выберем вкладку Элемент управления и в поле Связь с ячейкой: запишем Вид_оплаты. Установим значение переключателя в положение установлен и щелкнем по кнопке ОК. Ниже поместим еще два переключателя с текстами «Частичная», «Полная».

15. Для учета постоянных клиентов при оформлении заказа поместим Флажок с панели инструментов Вставить, изменим текст на «Постоянный клиент». Выполним команду Формат объекта, в открывшемся окне Формат элемента управления выберем вкладку Элемент управления и в поле Связь с ячейкой: запишем Постоянный_клиент. Установим значение переключателя в положение установлен и щелкнем по кнопке ОК.

16. Введем формулу в поле Сумма заказа для расчета предварительной стоимости заказа. Итоговую стоимость заказа с учетом скидок рассчитаем по формуле =ЕСЛИ(Постоянный_клиент;D27*0,97;О27)*ВЫБОР(Вид_оплаты;1;0,95;0,9). Функция ВЫБОР() возвращает в зависимости от значения ячейки Вид_оплаты одно из чисел, перечисленных в качестве аргументов (в данном случае это скидки).

17. Для учета продавцов, оформляющих заказ, в форме «Заказ литературы» расположим список кодов продавцов. На панели инструментов Вставить выберем кнопку Список , поместим его на форму. Выполним команду Формат объекта, в открывшемся окне Формат элемента управления выберем вкладку Элемент управления и в поле Формировать список по диапазону: укажем диапазон ячеек, содержащих коды продавцов. В поле Связь с ячейкой: укажем ячейку В5 на листе «Переменные» (рис. 42).

Рис. 42. Окно «Формат элемента управления» для списка

20. Для исключения оформления заказов с одинаковыми номерами в поле Номер заказа введем формулу =НАИБОЛЬШИЙ('Учет заказов'!А4:А5;1)+1.

21. Для формирования списка оформленных заказов информацию, содержащуюся в форме «Заказ литературы», необходимо занести в базу данных. С этой целью создадим макрос и присвоим его элементу Кнопка с панели инструментов Вставить.

На листе «Переменные» предварительно сформируем запись базы данных. Начиная с ячейки А4 запишем имена полей, необходимых при формировании списка. В следующей строке введем формулы, связывающие поля записи с формой ввода «Заказ литературы», как представлено на рис. 43 (исключение составляет заполненная ранее ячейка В5).

Скопируем названия полей записи с листа «Переменные» на лист «Учет заказов».

Рис. 43. Вспомогательный лист «Переменные»

22. Запишем макрос для добавления записей в базу данных с формы «Заказ литературы». Для этого выполним следующие операции:

находясь на листе «Заказы», включим режим автоматической записи макроса, выполнив команду меню Вид-Макрос-Запись макроса;

перейдем на лист «Учет заказов» и установим курсор в строке ниже заголовка таблицы;

добавим новую строку командой меню Главная-Вставить строки на лист;

перейдем на лист «Переменные» и скопируем строку со значениями полей записи в буфер;

вернемся на лист «Учет заказов», установим курсор в добавленную строку (под шапкой таблицы), командой Специальная вставка (Меню-Параметры Excel-Настройка-Все команды-Специальная вставка-Добавить) откроем окно Специальная вставка, выберем пункт значения и щелкнем по кнопке ОК;

перейдем на лист «Заказы», удалим из поля Количество экземпляров внесенное при заполнении данных значение и остановим запись макроса.

23. Назначим макрос элементу Кнопка (Меню-Параметры Excel-Настройка-Все команды-Кнопка-Добавить). Проверим работу макроса, введя новые данные в форму и добавив их в базу данных.

23. Заполним базу данных на листе «Учет заказов».

24. По сформированному списку учета заказов построим сводную таблицу (Вставка-Сводная таблица), отображающую общую сумму оформленных заказов по каждому продавцу, сгруппированную по месяцам.

25. На листе «Оплата продавцов» сформируем список с полями: Код продавца, Оклад, Сумма начислении. Рассчитаем сумму начислений для каждого продавца в зависимости от его разряда и общей суммы оформленных им за текущий месяц заказов:

Оклад = Минимальная оплата * Разрядный коэффициент,

Надбавка = Сумма оформленных заказов * Процент надбавки.

Разрядные коэффициенты извлекаются функцией ВПР() с листа «Справочные данные». Сумма начислений рассчитывается с учетом надбавки в зависимости от суммы оформленных продавцом заказов за месяц, в соответствии с табл. 7. Для удобства вычислений создадим эту таблицу на листе «Справочные данные».

Таблица 7

Процент надбавок

Сумма, р	Менее 10000	10000-20000	Более 20000
Процент надбавки, %	0	5	10

23. Разработаем титульный лист. Поместим на нем название работы и для более удобной работы создадим на титульном листе и на других листах рабочей книги управляющие макросы (например, для удобного и быстрого перехода по листам).

24. Вспомогательный лист «Переменные» можно скрыть, выполнив команду Главная-Формат-Скрыть или отобразить-Скрыть лист.

Задания для самостоятельной работы

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

1. Меховое ателье	9. Автовокзал
2. Аптека	10. Оптика
3. Автосервис	11. Оптовая фирма
4. Швейное ателье	12. Салон красоты
5. Магазин	13. Сапожная мастерская
6. Кинотеатр	14. Автосалон
7. Ателье проката	15. Авиакассы
8. Компьютерный салон

Оформить отчет по работе в соответствии со стандартами.