Автор: Пользователь скрыл имя, 14 Декабря 2011 в 22:23, контрольная работа
Цель работы: Получение практических навыков формирования табличной базы данных и работы с ней на примере базы данных в MS Excel.
Задачи:
- В MS Excel создать табличную базу данных для двух объектов.
- Отсортировать базу данных согласно варианту.
- Рассчитать требуемые показатели.
- Спрогнозировать характер изменения объема продажи оборудования на последующие шесть месяцев.
Введение……………………………………………………………………………………...….3
1 Основная част……………………………………………………………………………...….4
Создание базы данных…………………………………………………………...…………4
Определение минимального, максимального и среднего цен оборудования………….25
Определение стоимости и названия оборудования по условию К…………………..…25
Анализ характера изменения объема и стоимости помесячной продажи
Оборудования……………………………………………………………………………...34
Прогноз продажи оборудования за шесть последних месяцев…………………………37
Выводы……………………………………………………………………………………..…..40
Список литературы………………………………………………………………………..…..41
Для выполнения задания создаем таблицу критериев для выбора из базы данных (диапазон F2:G3). Первая строка критериев содержит имя поля критерия, вторая – значение, по которому идет выбор. Критерий в диапазоне F2:F3 исключает совпадение с минимальной ценой оборудования, критерий G2:G3 – задает наименование объекта.
Максимальное отклонение от минимального значения стоимости оборудования Acer определяем в диапазоне H2:H3 с помощью функции ДМАКС. Название оборудования и его цену определяем с помощью функции БИЗВЛЕЧЬ.
Таблица 8
| Наименование | Цена руб. | Цена руб. | Тип | IЦена-МинI |
| =БИЗВЛЕЧЬ(A10:G118;A10;H2:H3) | =БИЗВЛЕЧЬ(A10:G118;F10;H2:H3) | <>13991,36 | Acer | =ДМАКС(A10:G118;G10;F2:G3) |
Таблица 8.1
| Наименование | Цена руб. | Цена руб. | Тип | IЦена-МинI |
| Ноутбук Acer Aspire 8930G-944G64Bi (LX.AT20X.027) | 69271,04 | <>13991,36 | Acer | 55279,68 |
1.4 Анализ характера изменения объема и стоимости помесячной продажи оборудования
Чтобы проанализировать характер изменения объема продажи оборудования на Лист4 составляем новую базу данных, в которой отражаются три наименования оборудования: с максимальной, минимальной стоимостью и по условию К. Наименование оборудования извлекаются в соответствующие ячейки B1, D1, F1 новой базы данных с помощью функции БИЗВЛЕЧЬ.
Таблица 9
| Месяц | =БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; |
=БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; |
=БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; | |||
| Объем продаж,шт | Стоимость,
руб |
Объем продаж,шт | Стоимость,
руб |
Объем продаж,шт | Стоимость,
руб | |
Таблица 9.1
| Месяц | Ноутбук Acer Aspire 8930G-944G64Bi (LX.AT20X.027) | Ноутбук Acer Aspire AOA110-Ab (LU.S030A.078) | Ноутбук Acer Aspire 8930G-944G64Bi (LX.AT20X.027) | |||
| Объем продаж,шт | Стоимость,
руб |
Объем продаж,шт | Стоимость,
руб |
Объем продаж,шт | Стоимость,
руб | |
Столбец «Объем продаж» заполняем по месяцам полугодия путем копирования из вспомогательного столбца только значений случайных чисел.
Для этого в ячейки J3:J8, K3:K8, L3:L8 заносим случайные числа, которые берутся из диапазона 29-129 (NM-1NM) для оборудования с максимальной стоимостью, 29-329 (NM-3NM) для оборудования с минимальной стоимостью и 29-229 (NM-2NM) для оборудования со стоимостью по условию К.
Для получения случайных чисел в заданном диапазоне используем функцию СЛУЧМЕЖДУ(Нижн_гран; Верхн_гран).
Таблица 10
| =СЛУЧМЕЖДУ(29;129) | =СЛУЧМЕЖДУ(29;329) | =СЛУЧМЕЖДУ(29;229) |
| =СЛУЧМЕЖДУ(29;129) | =СЛУЧМЕЖДУ(29;329) | =СЛУЧМЕЖДУ(29;229) |
| =СЛУЧМЕЖДУ(29;129) | =СЛУЧМЕЖДУ(29;329) | =СЛУЧМЕЖДУ(29;229) |
| =СЛУЧМЕЖДУ(29;129) | =СЛУЧМЕЖДУ(29;329) | =СЛУЧМЕЖДУ(29;229) |
| =СЛУЧМЕЖДУ(29;129) | =СЛУЧМЕЖДУ(29;329) | =СЛУЧМЕЖДУ(29;229) |
| =СЛУЧМЕЖДУ(29;129) | =СЛУЧМЕЖДУ(29;329) | =СЛУЧМЕЖДУ(29;229) |
Таблица 10.1
| 56 | 128 | 107 |
| 73 | 311 | 210 |
| 63 | 325 | 83 |
| 50 | 150 | 213 |
| 112 | 112 | 117 |
| 85 | 216 | 107 |
Полученные значения копируем с помощью
команды «Специальная
вставка» и флага «значения» в соответствующие
ячейки B3:B8, D3:D8, F3:F8.
Таблица 11
| Месяц | Ноутбук Acer Aspire 8930G-944G64Bi (LX.AT20X.027) | Ноутбук Acer Aspire AOA110-Ab (LU.S030A.078) | Ноутбук Acer Aspire 8930G-944G64Bi (LX.AT20X.027) | |||
| Объем продаж,шт | Стоимост,
руб |
Объем продаж,шт | Стоимост,
руб |
Объем продаж,шт | Стоимость,
руб | |
| янв.11 | 56 | 128 | 107 | |||
| фев.11 | 73 | 311 | 210 | |||
| мар.11 | 63 | 325 | 83 | |||
| апр.11 | 50 | 150 | 213 | |||
| май.11 | 112 | 112 | 217 | |||
| июн.11 | 85 | 216 | 107 | |||
Стоимость оборудования в рублях (столбцы C, E, G) рассчитываем из объема его продажи и стоимости за единицу оборудования.
Таблица 12
| Месяц | =БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; |
=БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; |
=БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; | |||
| Объем продаж,шт | Стоимость,
руб |
Объем продаж,шт | Стоимость,
руб |
Объем продаж,
шт |
Стоимость,
руб | |
| 40544 | 56 | =B3*Показатели!$C$5 | 128 | =D3*Показатели!$B$5 | 107 | =F3*БД!$E$3 |
| 40575 | 73 | =B4*Показатели!$C$5 | 311 | =D4*Показатели!$B$5 | 210 | =F4*БД!$E$3 |
| 40603 | 63 | =B5*Показатели!$C$5 | 325 | =D5*Показатели!$B$5 | 83 | =F5*БД!$E$3 |
| 40634 | 50 | =B6*Показатели!$C$5 | 150 | =D6*Показатели!$B$5 | 213 | =F6*БД!$E$3 |
| 40664 | 112 | =B7*Показатели!$C$5 | 112 | =D7*Показатели!$B$5 | 217 | =F7*БД!$E$3 |
| 40695 | 85 | =B8*Показатели!$C$5 | 216 | =D8*Показатели!$B$5 | 107 | =F8*БД!$E$3 |
Таблица 12.1
| Месяц | Ноутбук Acer Aspire 8930G-944G64Bi (LX.AT20X.027) | Ноутбук Acer Aspire AOA110-Ab (LU.S030A.078) | Ноутбук Acer Aspire 8930G-944G64Bi (LX.AT20X.027) | |||
| Объем продаж,шт | Стоимость,
руб |
Объем продаж,шт | Стоимость,
руб |
Объем продаж,шт | Стоимость,
руб | |
| янв.11 | 56 | 3879178 | 128 | 1790894,1 | 107 | 7412001 |
| фев.11 | 73 | 5056786 | 311 | 4351313 | 210 | 14546918 |
| мар.11 | 63 | 4364076 | 325 | 4547192 | 83 | 5749496 |
| апр.11 | 50 | 3463552 | 150 | 2098704 | 213 | 14754732 |
| май.11 | 112 | 7758356 | 112 | 1567032,3 | 217 | 15031816 |
| июн.11 | 85 | 5888038 | 216 | 3022133,8 | 107 | 7412001 |
Используя данные таблицы и «Мастер диаграмм» строим диаграмму продажи оборудования за 6 месяцев . Т.к. b=1, то оборудование выбираем по максимальной и минимальной стоимости.
Диаграмма 1
Прогноз продажи соответствующего оборудования за 6 последующих месяцев отображаем в диапазонах B9:B14, D9:D14, F9:F14. Для прогноза продажи оборудования по максимальной стоимости используем функцию ТЕНДЕНЦИЯ(), по минимальной стоимости – РОСТ(), по условию К – Арифметическую прогрессию.
В ячейку B9 вводим формулу =ТЕНДЕНЦИЯ($B$3:B8;$A$3:A8;A9;
Таблица 13
| Месяц | =БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; |
=БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; |
=БИЗВЛЕЧЬ(БД!A10:G118;БД!A10; | |||
| Объем
продаж,шт |
Стоимость,руб | Объем продаж,шт | Стоимость,руб | Объем продаж,шт | Стоимость,
руб | |
| 40544 | 56 | =B3*Показатели!$C$5 | 128 | =D3*Показатели!$B$5 | 107 | =F3*БД!$E$3 |
| 40575 | 73 | =B4*Показатели!$C$5 | 311 | =D4*Показатели!$B$5 | 210 | =F4*БД!$E$3 |
| 40603 | 63 | =B5*Показатели!$C$5 | 325 | =D5*Показатели!$B$5 | 83 | =F5*БД!$E$3 |
| 40634 | 50 | =B6*Показатели!$C$5 | 150 | =D6*Показатели!$B$5 | 213 | =F6*БД!$E$3 |
| 40664 | 112 | =B7*Показатели!$C$5 | 112 | =D7*Показатели!$B$5 | 217 | =F7*БД!$E$3 |
| 40695 | 85 | =B8*Показатели!$C$5 | 216 | =D8*Показатели!$B$5 | 107 | =F8*БД!$E$3 |
| 40725 | =ТЕНДЕНЦИЯ($B$3:B8;$A$3:A8;A9; |
=B9*Показатели!$C$5 | =РОСТ($D$3:D8;$A$3:A8;A9;1) | =D9*Показатели!$B$5 | 115 | =F9*БД!$E$3 |
| 40756 | =ТЕНДЕНЦИЯ($B$3:B9;$A$3:A9; |
=B10*Показатели!$C$5 | =РОСТ($D$3:D9;$A$3:A9;A10;1) | =D10*Показатели!$B$5 | 123 | =F10*БД!$E$3 |
| 40787 | =ТЕНДЕНЦИЯ($B$3:B10;$A$3:A10; |
=B11*Показатели!$C$5 | =РОСТ($D$3:D10;$A$3:A10;A11;1) | =D11*Показатели!$B$5 | 134 | =F11*БД!$E$3 |
| 40817 | =ТЕНДЕНЦИЯ($B$3:B11;$A$3:A11; |
=B12*Показатели!$C$5 | =РОСТ($D$3:D11;$A$3:A11;A12;1) | =D12*Показатели!$B$5 | 159 | =F12*БД!$E$3 |
| 40848 | =ТЕНДЕНЦИЯ($B$3:B12;$A$3:A12; |
=B13*Показатели!$C$5 | =РОСТ($D$3:D12;$A$3:A12;A13;1) | =D13*Показатели!$B$5 | 162 | =F13*БД!$E$3 |
| 40878 | =ТЕНДЕНЦИЯ($B$3:B13;$A$3:A13; |
=B14*Показатели!$C$5 | =РОСТ($D$3:D13;$A$3:A13;A14;1) | =D14*Показатели!$B$5 | 164 | =F14*БД!$E$3 |