Методические указания к выполнению контрольной работы по дисциплине
ИНФОРМАЦИОННЫЕ СИСТЕМЫВ ЭКОНОМИКЕ
на тему:
Обработка табличной базы данных в MS Excel
для направления подготовки «Экономика»
О.Н. Косырева
Дзержинск
Содержание
1. Цель и задачи контрольной работы.. 3
2. Выбор варианта. 3
3. Порядок выполнения работы.. 3
4. Требования к оформлению контрольной работы.. 5
5. Комплектация пояснительной записки к контрольной работе: 5
4. Критерии оценок за контрольную работу. 5
6. Литература. 5
ПРИЛОЖЕНИЕ 1. 6
ПРИЛОЖЕНИЕ 2. 7
1. Цель и задачи контрольной работы
Цель работы: Получение практических навыков формирования табличной базы данных и работы с ней на примере базы данных в MS Excel.
Задачи:
- В MS Excel создать табличную базу данных для двух объектов.
- Отсортировать базу данных согласно варианту.
- Рассчитать требуемые показатели.
- Спрогнозировать характер изменения объема продажи оборудования на последующие шесть месяцев.
- Оформить пояснительную записку к контрольной работе с описанием решения зданий.
2. Выбор варианта
Номер варианта задания выбирается по коду МNb, где М – последняя цифра зачетной книжки, N – предпоследняя цифра зачетной книжки, b – сумма М и N с отбрасыванием разряда десятков. Например: М=4, N=6; b=0 (4+6=10).
3. Порядок выполнения работы
1. Открыть файл БД.xls, рабочий лист с базой данных Лист1 переименовать в ПРАЙС-ЛИСТ.
2. На Лист2 с десятой строки сформировать подобную ПРАЙС-ЛИСТ таблицу (Поля «Наименование», «Тип», «Описание», «Цена у.е.»), включающую оборудование по варианту М (см. Приложение1). Например, для М=4 в таблицу включаются только позиции ноутбуков HP и Sony. Подобрать оптимальный формат для числовых и текстовых ячеек (При работе с денежными единицами результаты округлять до центов или копеек). Начальные строки оставляются для ввода дополнительных данных и выполнения вспомогательных операций.
· Данные столбца D получить путем увеличения содержимого соответствующих ячеек столбца С на величину случайного числа, которое задается в диапазоне от 0,1*N до 0,5*N (при N=0 случайное число выбирается в диапазоне от 0 до 1).
· Полученные данные столбца D скопировать в столбец E, используя команду «Специальная вставка» и флаг «значения» (рис.1).
Рисунок 1
· Столбец D скрыть.
· Данные о курсе 1у.е. указать в ячейках А1-А2. Присвоить ячейке А2 имя «курс» (выбрать ячейку с курсом и в поле имени написать «курс»).
· В столбце F рассчитать стоимость оборудования в рублях.
3. Для четного b отсортировать таблицу по возрастанию цены в рублях, для нечетного b – по убыванию (Данные=> Сортировка).
4. На Лист3 сформировать таблицу (рис.2). Столбец «Тип объекта» заполнить по М (табл.1, Приложение 1). Используя функции MS Excel для работы с базой данных (ДМИН, ДМАКС, ДСРЗНАЧ, БСЧЕТ), определить минимальную и максимальную цену оборудования в рублях, среднее значение и количество оборудования по типам объектов.
| Тип | МИН | МАКС | СРЕД | КОЛ-ВО |
| Тип | МИН | МАКС | СРЕД | КОЛ-ВО |
Рис. 2
5. Используя функции по работе с базой данных, определить и вывести стоимость и название оборудования первого вида по условию К (табл.2, Приложение1). Следует исключить совпадение стоимости по условию К с минимальным и максимальным значениями для объектов первого вида, для чего при выборе объектов создать критерий для функции выбора.
Перед выполнением задания необходимо дополнить базу столбцом с расчетом абсолютной разницы цены в рублях и среднего/минимального/максимального значения цены оборудования объектов первого/второго вида (в зависимости от вида отклонения по параметру К).
Провести анализ характера изменения объема и стоимости помесячной продажи оборудования в пределах 6 месяцев, предшествовавших моменту заполнения базы. Для этого на Лист3 составить новую базу данных (рис.3) по трем наименованиям оборудования с минимальной и максимальной стоимостью объекта 1, а также по условию К.
| Месяц | Наименование оборудования по МИН | Наименование оборудования по МАКС | Наименование оборудования по К | ||||
| Объем продаж, шт. | Стоимость, руб. | Объем продаж, шт. | Стоимость, руб. | Объем продаж, шт. | Стоимость, руб. | ||
| январь | |||||||
| февраль | |||||||
| март | |||||||
| апрель | |||||||
| май | |||||||
| июнь |
Рис. 3
Столбец «Объем продаж» заполняется по месяцам полугодия путем копирования из вспомогательного столбца только значений случайных чисел (см. задание1). Случайные числа берутся из диапазона (NM)÷(1NM) для оборудования с максимальной стоимостью, (NM)÷(2NM) для оборудования со стоимостью по условию К, (NM)÷(3NM) для оборудования с минимальной стоимостью. (Например, для N=3, M=5: для оборудования с максимальной стоимостью диапазон 35÷135, для оборудования со стоимостью по условию К - 35÷235, для оборудования с минимальной стоимостью - 35÷335) Цены оборудования, необходимые для вычисления данных столбца «стоимость», берутся из предыдущих вычислений.
6. Используя результаты задания 5, построить диаграмму для двух видов оборудования, выбранных по таблице 3 Приложения 1 по М.
Вид диаграммы выбирается из соображений наглядности представляемой информации. На диаграмме отразить ее название, задать название осей, легенду, надпись (наименование оборудования).
7. Для каждого из видов оборудования в задании 4 спрогнозировать объем помесячной его продажи за шесть последующих месяцев. Для этого использовать функции ТЕНДЕНЦИЯ(), РОСТ(), ПРОГРЕССИЯ для разных видов оборудования. Для двух видов оборудования на отдельном листе построить диаграмму, отражающую характер изменения стоимости их помесячной продажи за год. В эту диаграмму добавить соответствующие линии тренда. Для обоснования выбора тренда поместить на диаграмму величину достоверности аппроксимации и закон аппроксимирующей кривой.
8. Оформить пояснительную записку к контрольной работе.
4. Требования к оформлению контрольной работы
- Титульный лист оформляется по требованиям Академии.
- Пояснительная записка оформляется в MS Word и предоставляется в печатном виде.
- Весь текст оформляется единообразным стилем. Таблицы переносятся копированием из MS Excel (шрифт в таблицах допускается 10пт). Все таблицы и рисунки должны иметь соответствующую нумерацию.
· Таблицы представляются в двух вариантах – с формулами, по которым ведутся вычисления, и с результатами вычислений. (Переключение режимов отображения Лента «Формулы – кнопка «Показать формулы»).
· На всех страницах, исключая титульный лист, оформляются колонтитулы: верхний колонтитул – ФИО, номер группы, значения M, N, b; нижний колонтитул – два элемента автотекста Автор, стр.<№>, дата и Полное имя файла.
5. Комплектация пояснительной записки к контрольной работе:
- Титульный лист.
- Содержание.
- Введение (включает цель работы, задачи, описание выбора варианта).
- Основная часть (подробное описание хода работы, включая пояснение к формулам и используемым функциям).
- Заключение (выводы).
- Список литературы.
- Приложения (если требуется).
4. Критерии оценок за контрольную работу
| Критерий | Оценка |
| Решена полностью, соответствует требованиям к оформлению | отлично |
| Решена с недочетами, соответствует требованиям к оформлению или оформлена с незначительными недочетами | хорошо |
| Решена наполовину, соответствует требованиям к оформлению или оформлена с незначительными недочетами | удовлетворительно |
| Сделан только первый этап в решении, или решена неосновная часть задания, или нет решения, или не соответствует требованиям к оформлению | неудовлетворительно |
6. Литература
1. Каракулев Ю.А. Руководство к решению задач с применением электронных таблиц Excel [Электронный ресурс]: учебное пособие / Ю.А. Каракулев, А.Н. Иванов. — Электрон. текстовые данные. — СПб.: Университет ИТМО, 2010. — 48 c. — 2227-8397. — Режим доступа: https://www.iprbookshop.ru/68103.html (Доступ по паролю)
2. Агафонова Н.С. Технология расчетов в MS Excel 2010 [Электронный ресурс]: учебное пособие / Н.С. Агафонова, В.В. Козлов. — Электрон. текстовые данные. — Самара: Самарский государственный архитектурно-строительный университет, ЭБС АСВ, 2016. — 97 c. — 978-5-9585-0699-6. — Режим доступа: https://www.iprbookshop.ru/61434.html (Доступ по паролю)
3. Карабутов Н.Н. Создание интегрированных документов в Microsoft office. Введение в анализ данных и подготовку документов [Электронный ресурс] / Н.Н. Карабутов. — Электрон. текстовые данные. — М.: СОЛОН-ПРЕСС, 2010. — 293 c. — 5-98003-200-2. — Режим доступа: https://www.iprbookshop.ru/65138.html (Доступ по паролю)
4. Башмакова Е.И. Умный EXCEL. Экономические расчеты [Электронный ресурс]: учебное пособие / Е.И. Башмакова. — Электрон. текстовые данные. — М.: Московский гуманитарный университет, 2014. — 176 c. — 978-5-906768-21-6. — Режим доступа: https://www.iprbookshop.ru/39699.html (Доступ по паролю)
ПРИЛОЖЕНИЕ 1
Таблица 1 - Выбор типа объектов, обрабатываемых в заданиях 2-7
| Вариант М | Тип объекта | |
| первого вида | второго вида | |
| Fujitsu-Siemens | BenQ | |
| Apple | Samsung | |
| Dell | Fujitsu-Siemens | |
| Acer | Apple | |
| HP | Sony | |
| Asus | Dell | |
| Sony | Toshiba | |
| Samsung | HP | |
| Toshiba | Asus | |
| BenQ | Acer |
Таблица 2 - Выбор оборудования по условию К (к заданию 5)
| b | Параметр К (по стоимости в руб.) |
| Оборудование, имеющее наименьшее отклонение от среднего значения стоимости объектов 1-го вида | |
| Оборудование, имеющее наибольшее отклонение от минимального значения стоимости объектов 2-го вида | |
| Оборудование, имеющее наименьшее отклонение от максимального значения стоимости объектов 1-го вида | |
| Оборудование, имеющее наибольшее отклонение от среднего значения стоимости объектов 2-го вида | |
| Оборудование, имеющее наименьшее отклонение от минимального значения стоимости объектов 2-го вида | |
| Оборудование, имеющее наименьшее отклонение от максимального значения стоимости объектов 2-го вида | |
| Оборудование, имеющее наибольшее отклонение от максимального значения стоимости объектов 2-го вида | |
| Оборудование, имеющее наименьшее отклонение от минимального значения стоимости объектов 1-го вида | |
| Оборудование, имеющее наибольшее отклонение от среднего значения стоимости объектов 1-го вида | |
| Оборудование, имеющее наименьшее отклонение от среднего значения стоимости объектов 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 близок к единице, что указывает на высокую степень достоверности выбранного закона.