Материал: Заданиие по работе №5_1

Создание базы данных в MS Excel Кафедра «Информатика и информационная безопасность» Профессор кафедры Дергачев Алексей Иванович 17 октября 2016 год

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

Создать лист заказа закупки товаров в MS Excel и создать базу данных. На листе заказа разместить:

1. № бланка заказа, дата оформления.

2. Наименование товара, тип, цена, количество, стоимость в $, стоимость в рублях.

3. Способ доставки товара (без доставки -0%, доставка в городе -5%, доставка в области -10%).

4. Представление скидки (представляется 1-5%, не представляется -0%).

5. Подвести итог (стоимости в $, стоимость в рублях, стоимость доставки, сумма скидки, к оплате и ФИО менеджера).

Базу данных сформировать из следующих данных:

• Номер заказа;

• дата оформления заказа;

• сумма заказа без скидок;

• стоимость доставки;

• сумма скидок;

• сумма к оплате;

• фамилия менеджера.

Данная работа выполняется на 4 листах:

• Лист заказа

• Справка

• Промежуточные данные

• База данных

Лист заказа

На листе заказа заполняются заголовки, представленные на рис. 1.

Рисунок 1- Лист заказа

Лист Справка

После оформления заголовков таблицы преступим к заполнению ячеек. Данные столбцов В и С берутся из Листа Справка (см. рис.2).

Рисунок 2 - Лист Справка

Порядок выполнениЯ

На Листе заказа, в ячейку В3 вставляем Поле со списком из элементов управления (ЭУ). Далее вставляем в Поле со списком из ЭУ по диапазону А2:А8 и связываем с ячейками А13, С13, E13, G13 на листе Справка см. рис.3.

Рисунок 3 – Установка ЭУ Поле со списком

• В ячейки С3:С6 вставляем формулу:

• =ИНДЕКС(Справка!$B$2:$B$8;Справка!A13) – для Компьютера

• =ИНДЕКС(Справка!$D$2:$D$8;Справка!C13) – для Монитора

• =ИНДЕКС(Справка!$Е$2:$Е$8;Справка!Е13) – для Принтера

• =ИНДЕКС(Справка!$G$2:$G$4;Справка!G13) – для Сканера

• В ячейку D3 вставляе м ЭУ счетчик и привязываем к этой ячейке(D3). Затем это выполним и ячейках D4 :D6.

Рисунок 4 – Вставка ЭУ счетчик

• В ячейки Е3:Е6 вставляем формулу =C3*D3.

• В ячейки Е8 вставляем формулу =CУMM(Е3:Е6)

• В ячейки F3:F6 записывается формула: ==$D$15*E3..

• В ячейки F8 вставляем формулу =CУMM(F3:F6)

• В ячейку F10 записываем формулу :

=F8*ИНДЕКС(Справка!$B$15:$B$17;Справка!C15) см.рис.5.

Рисунок 5 – Заполнение ЭУ и расчетными формулами Листа заказа

• На листе Лист заказа выставим группу кнопок из элементов управления переключатель и флажок см.рис.6а, 6б.

Справка!$C$159

Рисунок 6а. –Установка ЭУ переключатель

Справка!$C$19

Рисунок 6б. –Установка ЭУ флажок

Переключатели связываем с ячейкой С15 на листе Справка.

Флажок связываем с ячейкой С19 на листе Справка (ячейки С15, С19) см. рис 7.

Рисунок 7 – Связь с ячейками

В столбец F12 записывается формула:

= ЕСЛИ(ЛистСправка!С19= ИСТИНА;F8*0,15;” “)

В столбец F14 записывается формула: =СУММ(F10:F12)

В столбец F16 с помощью ЭУ ПОЛЕ СО СПИСКОМ берутся данные из Листа_Справка - cтолбцы ЛистСправка!G15:G19 по формуле:

=ИНДЕКС(Справка!G15:G19;Справка!H15) см.рис.8.

Рисунок 8 – Готовый лист заказа

Лист промежуточные данные

Все колонки данного листа заполняются из данных Листа заказа с помощью формулы: ='Лист заказа'!А1 и т.д.(F1, F10 и т.д) см.рис.9.

Рисунок 9 – Лист промежуточные данные

Формирование листа База данных

Алгоритм создания базы данных:

• Из Листа Промежуточные данные копируем заголовок таблицу в ячейку А1 Листа Базы данных.

• На Листе заказа вставляем из ЭУ кнопку .

• В рабочее поле Листа заказа в появившемся окне Назначить имя Макроса «База данных» (См. рис.6), далее щелнуть Записать … .

• В появившемся окне Запись Макроса (согласиться с именем макроса «База данных») см. рис. 10-11, щелкнуть ОК).

Рисунок 10 - Назначение имя макроса Рисунок 11- Запись макроса

• Переходим в Лист База Данных.

• Выделить свободную ячейку к примеру А3 и на Листа Базы данных и на панели Главного меню выбрать: Вставить затем выбрать Вставить строки на лист.

• Перейти на Лист Промежуточные данные, выделить всю строку с данными, а затем выполнить команду КОПИРОВАТЬ и перейти на Лист База данных.

• Курсор установить в ячейку А3, щелкнуть правой клавишей мышки по ячейке и выполнить команду: СПЕЦИАЛЬНАЯ ВСТАВКА … , выбрать Вставить значения и щелкнуть Значения и форматы чисел(3) см. рис.12-13.

Рисунок 12 - Команда: СПЕЦИАЛЬНАЯ ВСТАВКА …

Рисунок 13 - Команда: СПЕЦИАЛЬНАЯ ВСТАВКА …

Затем останавливаем запись макроса.

• Далее на Листе заказа после заполнения очередного заказа кликнем по кнопке (15-20 заказов).

• В результате этого у нас заполняется таблица Листа Базы данных см. рис. 14.

Рисунок 14 – Заполненная таблица Базы данных

После заполнения таблица Базы данных необходимо выполнить:

1. Отчет о получении денежных средств за проданную оргтехнику одним из менеджеров (в рублях ).

2. Отчет о предоставлении скидок за проданную оргтехнику (в рубл.).

3. Отчет о получении денежных средств за счет доставки оргтехники (в рубл.) .

Переносим заголовки : «Фамилия менеджера» , «Сумма к оплате», «Сумма скидок» и «Стоимость доставки» в отдельную табличку .

Выбираем трех менеджеров, чтобы сравнить показатели их работы См. рис. 15-16.

Столбцы М,N,O заполняем с помощью формулы СУММЕСЛИ() .

Рисунок 15 – Анализ работы менеджера(формулы расчета)

Рисунок 16 – Анализ работы менеджера

Вывод:

Менеджер Никифоров П.Г. принес компании больше прибыли, так как в соотношении общей суммы скидок и суммы доставок, процент будет выше, а значит, и прибыли для фирмы - больше.