Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Дополнительные задания на контрольную работу по курсу
”Информационные технологии в экономике ”
Цель работы: Автоматизация задач многомерного анализа данных
Постановка задачи:
В процессе обработки и анализа заказов за некоторый период времени необходимо рассчитать показатели:
Ход работы:
Методические указания к выполнению заданий и оформлению пояснительной записки
Поля «Наименование_заказчика» и «Наименование_товара» заполняются с помощью функции ВПР из категории «Ссылки и массивы»
Функция ВПР ищет значение в крайнем левом столбце указанного диапазона ячеек и возвращает значение в той же строке из указанного столбца таблицы
Аргументы функции:
Искомое значение – значение, которое должно быть найдено в первом столбце указанной таблицы (массива). Может быть значением, ссылкой или строкой текста.
Таблица (массив) – таблица со значениями, в которой производится поиск. Может быть ссылкой на диапазон или именем диапазона.
Номер столбца – номер столбца в таблице (массиве), в которой производиться поиск. Из указанного столбца требуется вернуть значение, соответствующее искомому значению. Первый столбец таблицы имеет номер 1.
Интервальный просмотр – логическое значение, которое задает точность поиска (значение 0 или ЛОЖЬ) или приближенность поиска (значение опущено или 1, ИСТИНА).
Для извлечения наименования заказчика в соответствующее поле таблицы «Заказы» функция ВПР заполняется следующим образом (рисунок 1): искомое значение – это код заказчика, который находится в первом столбце таблицы Заказчиков (диапазон ячеек A2:I48 на листе «Заказчики»). Диапазон таблицы должен быть абсолютной ссылкой. Наименование заказчика находится во втором столбце таблицы заказчиков. Значение должно быть найдено точно.
Рис. 1
Аналогично определяется наименование товара: искомое значение – это код товара (G2), который находится в первом столбце таблицы Товары (абсолютная ссылка на диапазон ячеек A2:D544 на листе «Товары»). Наименование товара находится во втором столбце таблицы товаров. Значение должно быть найдено точно.
Аналогично определяется тип товара: искомое значение – это код товара (G2), который находится в первом столбце таблицы Товары (абсолютная ссылка на диапазон ячеек A2:D544 на листе «Товары»). Тип товара находится во втором столбце таблицы товаров. Значение должно быть найдено точно.
В ячейку А2 вводится значение курса 1 у.е. в рублях (по текущему курсу доллара). Ячейке А2 присваивается имя курс_у_е: выделить ячейку А2 – в окне адреса ячейки изменить имя «А2» на имя курс_у_е и нажать Enter (рисунок 2).
Примечание: присвоить ячейке (или диапазону ячеек) имя можно через Диспетчер имен (MS Excel 2003 – меню Вставка – Имя – Присвоить; MS Excel 2007 – вкладка Формулы – панель Определенные имена - Присвоить имя)
Рис. 2
Цена в рублях определяется как произведение цены в у.е. и курса у.е.в рублях. Цена у.е. извлекается в формулу с помощью функции ВПР (рисунок 3). Искомое значение – это код товара, (G2), который находится в первом столбце таблицы Товары (абсолютная ссылка на диапазон ячеек A2:D544 на листе «Товары»). Цена у.е. находится в четвертом столбце таблицы товаров. Значение должно быть найдено точно.
Рис. 3
Значения по полю «Стоимость заказа, руб.» определяется как произведение количества штук заказа и цены в рублях.
Расчет скидок по условию производится с помощью функции ЕСЛИ из категории «Логические».
Функция ЕСЛИ проверяет, выполняется ли указанное условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.
Аргументы функции:
Логическое выражение – любое значение или выражение, которое может иметь значение ИСТИНА или ЛОЖЬ.
Значение, если ИСТИНА – значение, которое возвращается, если логическое выражение истинно. Если аргумент опущен, то возвращается значение «ИСТИНА». Допустимо до семи уровней вложений по аргументу.
Значение, если ЛОЖЬ – значение, которое возвращается, если логическое выражение ложно. Если аргумент опущен, то возвращается значение «ЛОЖЬ». Допустимо до семи уровней вложений по аргументу.
Возможный вариант заполнения аргументов функции для расчета скидок приведен на рисунке 4.
Рис. 4
Значения по полю «Итого к оплате, руб» определяются как разность стоимости заказа и скидки.
Ведомость на сумму заказа в разрезе заказчиков и типов ноутбуков составляется с помощью инструмента Сводная таблица.
Установить курсор в поле таблицы «Заказы». Выполнить команду Данные – Сводная таблица – в окне Мастера сводных таблиц шаг 1 из 3 установить флаг «сводная таблица» - Далее (MS Excel 2003) или вкладка Вставка – панель Таблица – Сводная таблица – Сводная таблица (MS Excel 2007).
В окне создания сводных таблиц (мастера сводных таблиц шаг 2 из 3) проверить, что выделен диапазон таблицы «Заказы»: Заказы!$A$1:$M$544. Сводную таблицу разместить на новом рабочем листе. В MS Excel 2003 в Мастере сводных таблиц шаг 3 из 3 нажать кнопку Макет.
Составить макет сводной таблицы путем перетаскивания полей с помощью левой кнопки мыши:
|
Поле |
MS Excel 2003 |
MS Excel 2007 |
|
Итого к оплате, руб. |
Область Данные |
Область Значения |
|
Заказчик |
Область Строка |
Область Названия строк |
|
Договор |
Область Строка |
Область Названия строк |
|
Тип |
Область Страница |
Область Фильтр отчета |
Изменить название поля сводной таблицы «Сумма по полю Итого к оплате, руб» на «Сумма заказа, руб» и задать значениям по полю формат Денежный.
Диаграммы по количеству штук заказа в разрезе типов ноутбуков и заказчиков рекомендуется строить с помощью инструмента Сводная диаграмма
Установить курсор в поле таблицы «Заказы». Выполнить команду Данные – Сводная таблица – в окне Мастера сводных таблиц шаг 1 из 3 установить флаг «сводная диаграмма со сводной таблицей» - Далее (MS Excel 2003) или вкладка Вставка – панель Таблица – Сводная таблица – Сводная диаграмма (MS Excel 2007).
В окне создания сводных диаграмм (мастера сводных таблиц шаг 2 из 3) проверить, что выделен диапазон таблицы «Заказы»: Заказы!$A$1:$M$544. Сводную диаграмму разместить на новом рабочем листе. В MS Excel 2003 в Мастере сводных таблиц шаг 3 из 3 нажать кнопку Макет.
Составить макет сводной диаграммы путем перетаскивания полей с помощью левой кнопки мыши:
|
Поле |
MS Excel 2003 |
MS Excel 2007 |
|
Заказ, шт |
Область Данные |
Область Значения |
|
Тип |
Область Строка |
Область Поля осей (категорий) |
|
Заказчик |
Область Страница |
Область Фильтр отчета |
Изменить название поля сводной таблицы «Сумма по полю Заказ, шт» на «Сумма заказа, шт». Тип диаграммы изменить на круговую. Добавить на диаграмму подписи данных и задать вывод имен категорий, значений и долей в процентном формате с точностью до сотых долей.
Пояснительная записка оформляется в MS Word и предоставляется в печатном виде. Вариант – последняя цифра номера зачетной книжки.
Комплектация отчета:
Привести сводную таблицу с фильтром по типу товара (тип выбирается по варианту из таблицы 1)
Таблица 1
|
Вариант |
Тип товара |
|
1 |
Apple |
|
2 |
Dell |
|
3 |
Acer |
|
4 |
HP |
|
5 |
Asus |
|
6 |
Sony |
|
7 |
Samsung |
|
8 |
Toshiba |
|
9 |
BenQ |
|
0 |
Fujitsu-Siemens |
Привести полностью сводную таблицу со скрытыми значениями по договору (фрагмент таблицы приведен на рисунке 5)
Рис. 5
Привести полностью сводную таблицу, на основе которой построена сводная диаграмма (фрагмент таблицы приведен на рисунке 6)
Рис. 6
Привести сводную диаграмму с фильтром по наименованию фирмы-заказчика (выбирается по варианту из таблицы 2).
Таблица 2
|
Вариант |
Наименование заказчика (для сводной диаграммы) |
|
1 |
"Лампорт" |
|
2 |
"Мерисел" |
|
3 |
"Стиплер" |
|
4 |
"TOPs" |
|
5 |
"Алси" |
|
6 |
"Весть" |
|
7 |
"Диалог СФТ" |
|
8 |
"CompuLink Trade" |
|
9 |
"Концерн Белый Ветер" |
|
0 |
"Корпорация Софт Лайн" |
Содержит описание хода работы (порядок заполнения всех полей на листе «Заказы», работа со сводными таблицами и сводными диаграммами). Необходимо включить пояснение к формулам и используемым функциям и иллюстративный материал (клавиша PrintScreen (PrtScr) на клавиатуре – Команда Вставить из буфера).
Рекомендации по оформлению пояснительной записки:
Примечание: В файле Exampl.xls приведен пример без формул и на других исходных данных с другими условиями по скидкам
PAGE \* MERGEFORMAT 6