5249

11

Задание 2. Обработка данных в Excel

Данное задание состоит из трех частей (А, Б, В) и предусматривает создание таблиц заданной структуры, ввод в них исходной информации, в соответствии с указанными ниже номерами вариантов, выполнение действий с использованием возможностей MS Excel по анализу данных, поиску оптимальных решений и прогнозированию.

Отчёт о выполненной работе должен включать следующие разделы:

полный текст заданий;

отформатированные таблицы, заполненные исходными данными;

копию диалоговых окон MS Excel с рассчитанными таблицами в режиме отображения формул (Сервис – Параметры – Вид – Формулы);

результаты выполнения заданий, с указанием их названий; описание действий при выполнении каждого пункта заданий, сопровождая

его копиями окон диалога, характеризующих выполнение работы.

Порядок выполнения заданий (для всех вариантов)

1. Решить квадратное уравнение подбором параметров модели (задание А).

Таблица 1

Исходные данные для выполнения задания А

№				Уравнение					№				Уравнение
варианта									варианта
1	18 x3		20 x2		32 x	19	142		7	8 x3		29 x2		47 x		29	542
2	48 x3		20 x2		2 x	19	148		8	15 x3		25 x2		32 x		9	197
3	18 x3		50 x2		82 x	19	14		9	78	x3	20 x2		32	x	89	962
4	44 x3		8 x2	32 x		7	154		10	59	x3	7 x2		72 x		9	782
5	118	x3	120	x2	132 x		119	1142	11	97	x3	80	x2	22	x	99	772
6	158	x3	120	x2	32 x 87			158	12	63	x3	28	x2	92 x		79	555

Для	выполнения	работы	необходимо построить
				x	y
таблицу,	в которой в	ячейке	х ввести произвольное

значение, а в ячейке y ввести уравнение. Далее используется стандартная технология анализа данных – подстановка табличных значений параметров в функционал модели. (Сервис / Подбор параметра).

12

2. Выполнить анализ данных (задание Б).

Для этого постройте 2 уравнения регрессии:

для расчёта затрат на ремонт оборудования в зависимости от срока эксплуатации (с использованием линии тренда);

для расчёта затрат на ремонт оборудования в зависимости от срока эксплуатации и выпуска продукции (с использованием средств ана-

лиза данных Сервис/Анализ данных).

2.1 Создайте в MS Excel таблице 2.

		Таблица 2
Исходные данные для выполнения задания Б
Срок эксплуатации	Выпуск продукции,	Затраты на ремонт,
оборудования, лет	тыс. р. / год	тыс. р. / год
1	2	3
1,3	1 200	0,12
2,1	2 100	1,1
4,1	5 000	2,3
1	4 500	0
0	5 000	0
1,75	6 000	1,1
2,3	3 200	1,8
12,1	1 000	7,8
10	6 700	7,1
1	1 200	0,67
8,6	4 300	6,75
3,4	2 670	1,75

К исходным данным 1-й графы прибавляется поправочный коэффициент равный номеру варианта (N), к данным 2-й графы – (N ˑ 10), а к данным 3-й графы (N ˑ 0,1).

2.2Найдите уравнение зависимости Затрат на ремонт от Срока эксплуатации. Для этого необходимо построить точечный график, линию тренда с уравнением и оценкой параметров.

2.3Выполните прогноз вперёд на 2 пункта.

Рисунок 1 – Параметры регрессии

13

2.4 С помощью команды меню Сервис/Анализ данных вызовите инструмент анализа – Регрессия и укажите следующие параметры для регрессии:

входной интервал Y – блок ячеек, содержащий затраты на ремонт; входной интервал X – блоки ячеек, содержащие срок эксплуатации оборудования и выпуск продукции; не выбирать Константа 0 (линия регрессии не проходит через начало координат); уровень надёжно-

сти – 67%;

выходной интервал – ячейка листа; установите Остатки,

Стандартизованные остатки, График остатков, График подбора, График нормальной вероятности;

нажмите кнопку OK.

Параметры регрессии приведены на рисунок 1.

В таблицах 3 – 5 приведены результаты анализа данных для исходных данных таблицы 2.

Таблица 3

Коэффициент детерминации

Регрессионная статистика

Множественный R	0,989660048
R-квадрат	0,97942701
Нормированный R-квадрат	0,974855235
Стандартная ошибка	0,463096191

14

Наблюдения	12

Эти значения свидетельствуют о наличии сильной связи показателя затрат на ремонт от срока службы оборудования и объёма выпуска продукции.

Таблица 4

Дисперсионный анализ

Параметр	df	SS	MS	F	Значимость F
Регрессия	2	91,88816893	45,94408446	214,233402	2,56944E-08
Остаток	9	1,930122737	0,214458082
Итого	11	93,81829167

df – число степеней свободы (независимые значения); SS – сумма квадратов отклонений;

MS – дисперсия, рассчитывается как отношение SS/df;

F –отношение дисперсии регрессии к дисперсии остатка;

Значимость F – уровень значимости, рассчитывается как MS Регрессия / MS Остаток.

Таблица 5

Параметры уравнения регрессии

Переменная	Коэффициенты	Стандартная	t-статистика	Р-значение
		ошибка
Y - пересечение	-0,626947661	0,313436806	-2,000236249	0,07652367
Переменная Х1	0,718853035	0,034966961	20,55806445	7,1218E-09
Переменная Х2	8,77076Е-05	7,14606Е-05	1,227356158	0,2508233
Переменная	Нижние 95 %	Верхние 95 %	Нижние 67 %	Верхние 67 %
Y - пересечение	-1,335990976	0,082095653	-0,949713076	-0,304182246
Переменная Х1	0,639752274	0,797953796	0,6828453776	0,754860694
Переменная Х2	-7,39475Е-05	0,000249363	1,4140202Е-05	0,000161295

Вкачестве переменной Х1 обозначается срок эксплуатации оборудования,

аХ2 – выпуск продукции.

Коэффициент переменной может использоваться в уравнении регрессии, если вычисленная для него величина (1 – Р-значение) близка к 1. Пара-

15

метры «Переменная Х2» и «Y-пересечение» не являются значимыми. Поэтому модельное уравнение регрессии по исходным данным таблицы 2

Y 0,62695 0,71885Xсрок 8,77E- 0,5Xвыпуск

можно представить в виде Y 0,71885Xсрок .

Коэффициенты для уравнения регрессии принадлежат интервалу, верхние и нижние значения которого соответствуют указанному уровню надежности (67 %).

3. Решите задачу оптимизации в табличном редакторе Microsoft Excel

(задание В)

Дано уравнение зависимости спроса (Y) от цены товара (X1) и его себестоимости (X2), которое имеет следующий вид:

Y A B X1 C X 22 D X12 ,

где A, B, C, D – коэффициенты, выбираемые из таблицы 6.

Таблица 6

Исходные данные для выполнения задания В

Коэффициент					Номер варианта
	1	2	3	4	5	6	7	8	9	10	11	12
A	0,1	0,2	0,3	0,4	-0,5	0,6	-0,7	-0,8	0,9	-1	1,1	1,2
B	-1,1	1,2	-1,3	-1,4	1,5	-1,6	1,7	1,8	1,9	2	-2,1	2,1
C	0,5	-0,6	0,7	0,8	-0,9	1	-1,1	1,2	-1,3	1,4	1,5	-1,6
D	-1,5	1,6	-1,7	-1,8	1,9	-2	2,1	-2,2	2,3	2,4	2,5	2,6

3.1 Постройте таблицу для отображения результатов поиска оптимального решения. В ячейки X1 и X2 введите произвольные числа, а в ячейку Y уравнение согласно данных своего варианта.

X1	X2	Y

3.2 Постройте таблицу с ограничениями для осуществления поиска оптимального решения.

X1	X2
0,1	0,3
1	1,2

16

3.3 Установите параметры для решения задачи Сервис/Поиск решения. Определите при каких значениях X1 и X2 значение Y max, если X1 меняется в пределах от 0,1 до 1, а X2 от 0,3 до 1,2.

3.4 Постройте график и уравнение зависимости спроса от цены товара.

17

Задание 3. Работа с базами данных

В рамках задания необходимо сформировать базу данных по теме, согласно варианта таблицы 7 . База данных должна содержать не менее 5 таблиц, по 20 записей в каждой.

Таблица 7

	Исходные данные 3-го задания
Номер варианта	Тема
1	Салон продажи сотовых телефонов
2	Оптовая база
3	Зоомагазин
4	Торговля недвижимостью
5	Мебельная фабрика
6	Кондитерская фабрика
7	Контроль входящих документов
8	Библиотека
9	Каталог музыкальных компакт-дисков
10	Реестр имущества студенческого городка

При создании структуры таблиц следует правильно определить типы полей (для текстовых полей рекомендуется указать их размеры), создать ключевые поля. При вводе имен полей в структуру таблиц допускается их сокращение, не приводящее к утрате понимания названия поля.

Отчёт о выполнении задания должен включать:

1)текст задания (рекомендуется оформить его в MS Word);

2)таблицы, демонстрирующие структуру таблиц созданной базы данных (Имя поля – Тип данных – Размер поля) (можно создать их в MS Word);

3)копии таблиц, заполненных исходными данными;

4)копию окна схемы созданной базы данных, отображающей связи, установленные между её таблицами;

5)копии окон (или их фрагментов) MS Access, иллюстрирующие сформированные Вами условия запросов, если они предусмотрены вариантом задания, форм и отчетов, в том числе в режиме конструктора. В режиме конструктора необходимо ввести в форму (отчёт) информацию об авторе работы.

18

Порядок выполнения задания (общая для всех вариантов часть)

Необходимо составить базу данных. Для этого:

3.1Запустить программу Microsoft Access (ПускПрограммы Microsoft Access). В окне выбрать «Новая база данных». Щелкнуть по кнопке «Ok». Сохранить файл под своим именем.

3.2Открыть панель Таблицы. Создать необходимые таблицы (не менее 5) в любом режиме. Осуществить связь между несколькими полями в различных таблицах с использованием Мастера Подстановок. Задать ключевые поля.

3.3Создать межтабличные связи. Для этого выбрать СервисСхема данных.

3.4Создать формы для каждой таблицы. Типы – любые подходящие. Стиль – стандартный.

3.5Занести в формы необходимую информацию (не менее 20 записей в каждой таблице). Обязательно заполнение всех реквизитов таблиц!

3.6Распечатать все таблицы и созданные формы (3 таблицы и 3 формы).

3.7Создать запросы к таблицам базы данных. Условия отбора должны содержать знаки: <, >, =, [ ], #, « », а также включать сортировку и групповые операции.

3.8Отформатировать созданные таблицы.

Для этого необходимо:

Создать заголовок каждой формы – расширить область Заголовок и вставить надпись (например, Номенклатура) затем в свойствах задать выравнивание по центру, размер и тип шрифта, определить цвет.

Вставить в область данных любой рисунок из набора Windows.

Изменить цвет фона (элемент Область данных, атрибут Цвет фона).

Изменить внешний вид полей: выбрать группу полей (держа нажатой клавишу Shift) и в окне свойств изменить атрибут Оформление.

Отредактировать подписи полей и изменить их расположение друг относительно друга: достаточно воспользоваться возможностями визуального редактирования элементов.

Добавить в область данных разделительную линию. Для этого использовать элемент Линия (задать цвет и ширину линии).

Добавить кнопки завершения работы на каждой форме. В одну из форм добавить вычисляемое поле.

19

3.9Создать отчёт по базе данных.

3.10Создать общую форму для всех таблиц, содержащую название базы

данных, кнопки открытия других форм и кнопку закрытия формы.

Библиографический список

1. Автоматизированные информационные технологии в экономике / под ред. Г. Титоренко. – М. : ЮНИТИ, 2006.

2.Антикризисное управление : учебник / Е. П. Жарковская, Б. Е. Бродский. – М. : Омега-Л, 2005.

3.Балдин К. В., Уткин В. Б. Информационные системы в экономике : учебник. – 3-е изд. – М. : Дашков и К°, 2006.

4.Барановская Т. П., Лойко В. И., Семёнов М. И., Трубилин А. И. Информационные системы и технологии в экономике : учебник. – 2-е изд. – М. : Финансы и статистика. 2006. – 416 с.

5.Бочаров Е. П. Интегрированные корпоративные информационные системы : Принципы построения. Лабораторный практикум на базе системы "Галактика": учеб. пособие / Е. П. Бочаров, А. И. Колдина. – М. : Финансы и статистика, 2007. – 288 с.

6.Бугорский В. Н., Соколов Р. С. Сетевая экономика и проектирование информационных систем : Ценообразование в сетевой экономике; Сферы приложения; Экономическая эффективность и др. – СПб. : Питер, 2007. – 320 с.

7.Васина Е. Н., Партыка Т. Л., Попов И. И. Автоматизированные информационные си-

стемы бухгалтерского учёта : учеб. пособие. – М. : ФОРУМ : ИНФРА-М, 2006. – 432 с.

8.Гниденко И., Соколовская С. Информационные технологии в бизнесе. – М. : Вектор, 2005.

9.Емельянова Н. З., Партыка Т. Л., Попов И. И. Основы построения автоматизированных информационных систем : учеб. пособие. – М. : ФОРУМ, ИНФРА-М,

2007. – 416 с.

10.Закарян И., Рафалович В. Что такое Internet, WWW и HTML. – М. : Интернеттрейдинг, 2003.

11.Информационные системы / В. Н. Петров – СПб. : Питер, 2003.

12.Информационные системы и технологии в экономике и управлении : учеб. пособие / под ред. проф. В. В. Трофимова. – 2-е изд., перераб. и доп. – М. : Высшее образование, 2007.

20

13.Исаев Г. Н. Информационные системы в экономике : учеб. пособие – М. : Омега – Л. , 2006.

14.Костров А. В. Основы информационного менеджмента: учеб. пособие. – М. : Финансы и статистика, 2003.

15.Патрушина С. М. Информационные системы в бухгалтерском учёте : учеб. пособие. – М. : МарТ, 2003.

16.Рудикова Л. В. Базы данных. Разработка приложений. – СПб. : БХВ-Петербург,

2006.

17.Торопцев Е. Л., Королёв В. А., Мараховский А. С. и др. Информационные системы в экономике : практикум для вузов / под ред. П. В. Акинина. – М. : КноРус,

2008 – 256 с.

18.Трофимов В. В., Ильина О. П., Трофимова Е. В. Информационные системы и технологии в экономике и управлении : учеб. пособ. для вузов / под ред. В. В. Трофимова. – М. : Высшее образование, 2007. – 480 с.

19.Хелд Г. Технологии передачи данных. – СПб. : Питер, 2003. – 720 с.

20.Белозеров О. Информационные технологии в управлении : учеб. пособ. – Хабаровск : РИЦ ХГАЭП, 2005.

21.Вишневский А. Н. Справочная правовая система КонсультантПлюс : методические указания по выполнению лабораторных работ для студентов всех курсов, специальностей и форм обучения. – Хабаровск : РИЦ ХГАЭП, 2005.

22.Вишневский А. Н., Раевская О. Г. Справочная правовая система Гарант Платформа F1 : учеб. пособ. – Хабаровск : РИЦ ХГАЭП, 2006.

23.Власова Л. А., Самойлова Л. В. Работа в Microsoft Access : учеб.-практ. пособ. – Хабаровск : РИЦ ХГАЭП, 2004.

24.Власова Л. А., Самойлова Л. В. Система управления базой данных Microsoft Access : учеб.-практ. пособ. – Хабаровск : РИЦ ХГАЭП, 2004.

25.Власова Л. А. Защита информации : учеб. пособ. для студентов 1– 4-го курсов всех специальностей и форм обучения. – Хабаровск : РИЦ ХГАЭП, 2007.

26.Любицкий Ю. Базы данных : учеб. пособ. – Хабаровск : РИЦ ХГАЭП, 2006.

27.Самойлова Л. В. Обработка и анализ данных в Microsoft Excel : учеб. пособ. – Хабаровск : РИЦ ХГАЭП, 2007.