- •Указания к выполнению контрольной работы
- •Приложение 1
- •Выбор типа объектов, обрабатываемых в заданиях 2-7
- •Выбор оборудования по условию к (к заданию 5)
- •Выбор оборудования для построения диаграмм (к заданию 6-7)
- •Приложение 2
- •1. Cоздается база данных. Начальные строки оставляются для ввода дополнительной информации (курс, таблица критериев и т.Д.). Первые три столбца (а, в, с) заполняется соответствующими данными.
- •8 Ннгу им. Н.И. Лобачевского, факультет фпрк, кафедра ТиМдо
Приложение 1
Таблица 1
Выбор типа объектов, обрабатываемых в заданиях 2-7
Вариант М |
Тип объекта |
|
первого вида |
второго вида |
|
0 |
Fujitsu-Siemens |
BenQ |
1 |
Apple |
Samsung |
2 |
Dell |
Fujitsu-Siemens |
3 |
Acer |
Apple |
4 |
HP |
Sony |
5 |
Asus |
Dell |
6 |
Sony |
Toshiba |
7 |
Samsung |
HP |
8 |
Toshiba |
Asus |
9 |
BenQ |
Acer |
Таблица 2
Выбор оборудования по условию к (к заданию 5)
b |
Параметр К (по стоимости в руб.) |
0 |
Оборудование, имеющее наименьшее отклонение от среднего значения стоимости объектов 1-го вида |
1 |
Оборудование, имеющее наибольшее отклонение от минимального значения стоимости объектов 2-го вида |
2 |
Оборудование, имеющее наименьшее отклонение от максимального значения стоимости объектов 1-го вида |
3 |
Оборудование, имеющее наибольшее отклонение от среднего значения стоимости объектов 2-го вида |
4 |
Оборудование, имеющее наименьшее отклонение от минимального значения стоимости объектов 2-го вида |
5 |
Оборудование, имеющее наименьшее отклонение от максимального значения стоимости объектов 2-го вида |
6 |
Оборудование, имеющее наибольшее отклонение от максимального значения стоимости объектов 2-го вида |
7 |
Оборудование, имеющее наименьшее отклонение от минимального значения стоимости объектов 1-го вида |
8 |
Оборудование, имеющее наибольшее отклонение от среднего значения стоимости объектов 1-го вида |
9 |
Оборудование, имеющее наименьшее отклонение от среднего значения стоимости объектов 2-го вида |
Таблица 3
Выбор оборудования для построения диаграмм (к заданию 6-7)
Вариант М |
Виды оборудования |
0, 3, 6, 9 |
По максимальной и минимальной стоимости |
1, 4, 7 |
По максимальной стоимости и по условию К |
2, 5, 8 |
По минимальной стоимости и по условию К |
Приложение 2
Пример использования функций MS Excel
В качестве примера рассматривается один объект – колонки Genius тип SP (Рис.1).
1. Cоздается база данных. Начальные строки оставляются для ввода дополнительной информации (курс, таблица критериев и т.Д.). Первые три столбца (а, в, с) заполняется соответствующими данными.
Рис. 1
Определение розничной цены (столбец Е):
Для определения розничной цены во вспомогательном столбце D используется генератор случайных чисел.
Для получения случайного числа в диапазоне от 0 до 1 используется функция СЛЧИС().
Для получения случайного числа в пределах от p до q используется формула СЛЧИС()*(q-p)+p.
Данные столбца D получить путем увеличения содержимого соответствующих ячеек столбца С на величину случайного числа, которое задается в диапазоне от 0,1N до 0,5N (при N=0 случайное число выбирается в диапазоне от 0 до 1). Т.о., данные столбца D рассчитываются по формуле =Cn+ СЛЧИС()*(q-p)+p (при N≠0) или =Cn+ СЛЧИС() (при N=0), где n – номер строки, Cn – ссылка на соответствующую ячейку столбца С, q = 0,5N, p = 0,1N. Полученные данные столбца D копируются в столбец E, используя команду «Специальная вставка» и флаг «значения». Столбец D скрыть (рис.2).
Рис. 2
В ячейку А2 вводится значение курса 1 у.е. в рублях, который используется для расчета стоимости аппаратуры в рублях (столбец F). Ячейке А2 присваивается имя – курс: выделить ячейку А2 – в окне адреса текущей ячейки изменить имя ячейки «А2» на имя «курс» и нажать Enter. Полученные данные (столбец Е и F) c помощью функции ОКРУГЛ(Число; Число_разрядов) округляются с точностью до центов и копеек соответственно (Рис. 3).
Рис. 3
2. Минимальная и максимальная цена оборудования, среднее значение и количество оборудования определяются с помощью функций MS Excel: ДМИН, ДМАКС, ДСРЗНАЧ, БСЧЕТ (Рис. 4). Результаты полученных значений выводятся на Лист2 (Рис. 5).
Рис. 4
Рис. 5
3. Стоимость и название оборудования объекта по условию К определяются с помощью функций по работе с базой данных БИЗВЛЕЧЬ, ДМИН (или ДМАКС).
Для выполнения задания необходимо создать таблицу критериев для выбора из базы данных (диапазон F2:G3). Первая строка критериев содержит имя поля критерия, вторая – значение, по которому идет выбор. Критерий в диапазоне F2:F3 исключает совпадение с минимальной (максимальной или средней) ценой оборудования, критерий G2:G3 – задает наименование объекта.
В рассматриваемом примере b=0 и выбор оборудования по критерию К осуществляется по стоимости, наиболее близкой к среднему значению колонок. Для этого добавляется столбец G к таблице (Рис. 1), в который вносится абсолютное значение разности «Цена (руб.)» и «СРЕД» с Листа2 (Рис. 6). Его минимум определяется в диапазоне Н2:Н3 с помощью функции ДМИН (Рис. 7).
Название оборудования и его цена определяются с помощью функции БИЗВЛЕЧЬ (Рис. 8).
Рис. 6
Рис. 7 Рис. 8
4. Чтобы проанализировать характер изменения объема продажи оборудования на Лист3 составляется новая база данных (Рис. 3, стр. 2), в которой отражаются три наименования оборудования: с минимальной, максимальной стоимостью и по условию К. Наименование оборудования извлекаются из таблицы Листа1 в соответствующие ячейки В1, D1, F1 новой базы данных с помощью функции БИЗВЛЕЧЬ.
В ячейки J3:J8, K3:K8, L3:L8 заносятся случайные числа с учетом задания, которые соответствуют объемам продаж оборудования по требуемой стоимости. Например, согласно заданным диапазонам для N=0 M=6:
В ячейки J3:J8 заносятся случайные числа от 06 до 106;
В ячейки K3:K8 заносятся случайные числа от 06 до 206;
В ячейки L3:L8 заносятся случайные числа от 06 до 306.
Для получения случайных чисел в заданном диапазоне используется функция СЛУЧМЕЖДУ(Нижн_гран; Верхн_гран). Полученные значения копируются с помощью команды «Специальная вставка» и флага «значения» в соответствующие ячейки В3:В8, D3:D8, F3:F8.
Стоимость оборудования в рублях (столбцы С, Е, G) рассчитывается из объема его продажи и стоимости за единицу оборудования, взятой из таблиц Листа2 и Листа3 (Рис. 9).
Рис. 9
5. Используя данные таблицы с Листа3 и «Мастер диаграмм», строится диаграмма продажи оборудования за предшествующие 6 месяцев (рис.10).
Рис. 10
6. Прогноз продажи соответствующего оборудования за 6 последующих месяцев отображается в диапазонах В9:В14, D9:D14, F9:F14 таблицы Листа3. Для прогноза продажи оборудования по максимальной стоимости используется функция ТЕНДЕНЦИЯ(), по минимальной стоимости - РОСТ(), по условию К – Арифметическая прогрессия.
В ячейку В9 вводится формула =ТЕНДЕНЦИЯ($B$3:B8;$A$3:A8;A9;1)с последующим заполнением ячеек столбца В. Прогноз с помощью функции РОСТ() ячеек D9:D14 выполняется аналогично.
Прогноз функцией ПРОГРЕССИЯ ячеек F9:F14 производится с помощью автозаполнения (Рис.11).
Рис. 11
По результатам таблицы с Листа3 строится диаграмма (Рис. 12). На диаграмме отображаются соответствующие линии тренда, аппроксимирующие зависимость стоимости выбранного типа оборудования.
Рис. 12
Выводы:
Как видно из диаграммы (Рис. 12), оборудование минимальной стоимости по сравнению с максимальной продается в большем объеме.
Закон изменения стоимости оборудования SP-718 – полиномиальный, а SP-M06А – степенной.
Коэффициент аппроксимации R2 близок к единице, что указывает на высокую степень достоверности выбранного закона.