Практическая часть Выбор варианта

Виды сравниваемого оборудования выбрать по своему усмотрению, воспользовавшись прайс-листом компьютерного салона или интернет-магазина (пример исходных данных приведен в Приложении 2).

Для выбора вариантов заданий необходимо знать номер зачетной книжки. Обозначим за М– последнюю цифру зачетной книжки, заN– предпоследнюю, заР– сумму последней (М) и предпоследней (N) цифр зачётной книжки с отбрасыванием разряда десятков.

Задание для выполнения

1. Сформируйте на Листе 1базу данных для 2-х видов оборудования в виде единой таблицы. Пример таблицы представлен на рис. 1.

Для этого:

• Столбцы А,ВиСзаполните исходными данными.

• Данные ячеек столбца Dполучите путем увеличения содержимого соответствующих ячеек столбцаС(Опт. цена в у.е.) на величину случайного числа, которое задается в диапазоне от 0,1*N до 0,5*N (при N=0 цена увеличивается на величину в диапазоне от 0 до 1).

• Полученные данные столбца Dскопируйте в соседний столбецЕ, используя командуСпециальная вставка и флаг Значение.

• Полученные результаты округлите с точностью до копеек (центов).

• Столбец Dскройте.

• В ячейке А2укажите информацию о курсе 1 у.е. в рублях (курс определяется на момент выполнения задания). Присвойте ячейкеА2имяКурс.

• В столбце F(Цена в руб.) рассчитайте стоимость оборудования в рублях.

• Отсортируйте базу данных по возрастанию стоимости оборудования для чётного Р и по убыванию – для нечетного.

• Переименуйте Лист 1вЗадание 1.

Рис. 1. Результат выполнения задания 1

2. Созданную в Задании 1таблицу скопируйте в буфер обмена и вставьте наЛист 2четыре раза. В 1-й копии таблицы, используя командуПромежуточные итоги,определите минимальную цену (в руб.) оборудования сравниваемых видов, во 2-й копии – максимальную, в 3-й копии – среднюю цену и в 4-й – количество оборудования каждого вида. Результат работы представьте так, как показано на рис. 2. ПереименуйтеЛист 2вЗадание 2.

Рис. 2. Результат выполнения задания 2

3. Извлеките из исходной таблицы строку с описанием оборудования 1-го вида, удовлетворяющего условию К(условие выбирается из табл. 2). Пример выполненного задания представлен на рис. 3. Для выполнения задания:

• Созданную таблицу (Листа1)и необходимые для выполнения задания данные скопируйте наЛист 3.

• С помощью команды Фильтр вкладки Данныевыберите оборудование 2-го вида.

• Удалите отобранные строки (с оборудованием 2-го вида).

• Отмените действие Фильтра,останется оборудование 1-го вида.

• Если необходимо, проведите дополнительные вычисления.

• Примените расширенный фильтр для выполнения задания.

Задайте листу 3имяЗадание 3.

Примечание. Следует исключить совпадение стоимости оборудования поусловию Кс минимальным или максимальным значением стоимости оборудования 1-го вида, для этого следует при выборе объектов добавить дополнительное условие.

Таблица 2 – Условия отбора К

Р	Условие К (по стоимости в руб.)
0	Оборудование 1-го вида, цена которого имеет наименьшее отклонение от среднего значения стоимости оборудования 1-го вида
1	Оборудование 1-го вида, цена которого имеет наибольшее отклонение от минимального значения стоимости оборудования 2-го вида
2	Оборудование 1-го вида, цена которого имеет наибольшее отклонение от среднего значения стоимости оборудования 1-го вида
3	Оборудование 1-го вида, цена которого имеет наибольшее отклонение от среднего значения стоимости оборудования 2-го вида
4	Оборудование 1-го вида, цена которого имеет наименьшее отклонение от минимального значения стоимости оборудования 2-го вида
5	Оборудование 1-го вида, цена которого имеет наименьшее отклонение от максимального значения стоимости оборудования 2-го вида
6	Оборудование 1-го вида, цена которого имеет наименьшее отклонение от среднего значения стоимости оборудования 1-го вида
7	Оборудование 1-го вида, цена которого имеет наибольшее отклонение от максимального значения стоимости оборудования 2-го вида
8	Оборудование 1-го вида, цена которого имеет наибольшее отклонение от среднего значения стоимости оборудования 1-го вида
9	Оборудование 1-го вида, цена которого имеет наименьшее отклонение от среднего значения стоимости оборудования 2-го вида

Рис. 3. Результат выполнения задания 3

4. Используя функцию БИЗВЛЕЧЬ, извлеките из исходной таблица (Лист 1) и выведите наЛист4наименование и цену оборудования 1-го вида: с минимальной и максимальной ценой, а так же с ценой, удовлетворяющейусловию К(рис. 4). ПереименуйтеЛист 4 вЗадание 4.

Рис. 4. Результат выполнения задания 4

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

Для этого на Листе5создайте таблицу для трех наименований оборудования: с минимальной и максимальной стоимостью, а также поусловию К(табл. 3). Содержанием ячеек с наименованием оборудования являются ссылки на соответствующие ячейки листаЗадание 4.

Таблица 3 – Анализ характера изменения объёма и стоимости помесячных продаж оборудования

Месяц	Наименование оборудования с мин. стоимостью		Наименование оборудования с макс. стоимостью		Наименование оборудования по условию. К
	Объём продаж (шт.)	Стоимость (руб.)	Объём продаж (шт.)	Стоимость (руб.)	Объём продаж (шт.)	Стоимость (руб.)
мес.год

Столбец Объём продажзаполните по месяцам полугодия путем копирования значений случайных чисел из вспомогательного столбца. Случайные числа берутся в диапазоне NM1NM для оборудования с максимальной стоимостью, в диапазоне NM2NM – для оборудования со стоимостью поусловию К и в диапазоне NM3NM – для оборудования с минимальной стоимостью. Цены на оборудование, необходимые для вычисления данных столбцаСтоимость, берутся с листаЗадание 4.

Пример выполнения задания 5 приведен на рис. 5.

Рис. 5. Результат выполнения задания 5

6. Используя данные табл. 3, постройте диаграмму для 2-х видов оборудования, выбранных из табл. 4.

Таблица 4– Выбор видов оборудования

М	Виды оборудования
0, 3, 6, 9	По максимальной и минимальной стоимости
1, 4, 7	По максимальной стоимости и по условию К
2, 5, 8	По минимальной стоимости и по условию К

Вид диаграммы выбирается из соображений наглядности представляемой информации. На диаграмме необходимо отразить название диаграммы, название осей, легенду. Диаграмму разместить на листе с таблицей. Пример выполнения задания 6 представлен на рис. 6.

7. Переименовать Лист4вЗадание 5-6.

Рис. 6. Результат выполнения задания 6

7. Для каждого из видов оборудования спрогнозируйте в табл. 3 объем помесячных продаж на 6 последующих месяцев.

Для оборудования с минимальной ценой используйте функцию ТЕНДЕНЦИЯ,для оборудования с максимальной ценой функциюРОСТ и функцию ПРОГРЕССИЯдля оборудования с ценой поусловию К.

Для двух видов оборудования (в соответствии с вариантом из табл. 4) на отдельном листе построите диаграмму, отражающую характер изменения стоимости их помесячных продаж за год. На эту диаграмму добавьте линии тренда, сделайте прогноз на один период вперед. Для обоснования выбора тренда поместите на диаграмму величину достоверности аппроксимации и закон аппроксимирующей кривой. Лист с диаграммой назовите Прогноз продаж. Пример выполнения задания представлен на рис. 7.

Рис. 7. Результат выполнения Задания 7

8. Для данных из табл. 3, используя соответствующие функции из категории Работа с базой данных,решите задачу, приведенную в табл. 5.

Таблица 5 – Варианты заданий

Р	Искомый параметр
0	Подсчитать суммарную стоимость проданного оборудования с ценой по условию К, проданного за месяцы, в которые объём продаж не превышал1NM.
1	Вычислить среднюю стоимость оборудования с минимальной ценой, проданного за месяцы, в которые объём продаж находился в пределах от 1NMдо2NM.
2	Подсчитать количество оборудования с максимальной ценой, проданного за месяцы, в которые стоимость продаж была больше произведения 1NМ*Стоимость единицы оборудования.
3	Вычислить суммарную стоимость оборудования с ценой по условию К, проданного за месяцы, в которые объём продаж был больше1NM.
4	Вычислить среднюю стоимость оборудования с максимальной ценой, проданного за месяцы, в которые объём продаж был меньше 1NM.
5	Рассчитать количество оборудования с ценой по условию К, проданного за месяцы, в которые стоимость продаж была меньше произведения 2NM*Стоимость единицы оборудования.
6	Подсчитать суммарную стоимость оборудования с минимальной ценой, проданного за месяцы, в которые объём продаж находился в пределах от 1NMдо 2NM.
7	Вычислить среднюю стоимость оборудования с ценой по условию К, проданного за месяцы, в которые объём продаж был больше1NM.
8	Вычислить количество оборудования с минимальной ценой, проданного за месяцы, стоимость продаж в которые была меньше произведения 2NМ*Стоимость единицы оборудования.
9	Подсчитать суммарную стоимость оборудования с максимальной ценой, проданного за месяцы, в которые объём продаж был меньше 1NM.

9. Используя данные табл. 3, с помощью соответствующей функции из категории Работа с базой данных,выведите месяц с наибольшей суммой продаж оборудования с ценойпо условию К.

9. Работу сохраните на CD-диск.