Реферат Создание базы данных средствами табличного процессора МS Excel

Работа добавлена на сайт bukvasha.net: 2015-10-28

Поможем написать учебную работу

Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.

Предоплата всего

от 25%

Подписываем

договор

Имя

Выберите тип работы:

Принимаю Политику конфиденциальности

Скидка 25% при заказе до 10.11.2024

Министерство образования и науки Российской Федерации

Федеральное агентство по образованию ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ "ОРЕНБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ"

Факультет информационных технологий Кафедра информационных систем и технологий
Расчетно-графическое задание
по дисциплине «Информатика»

Создание базы данных средствами табличного процессора МS Excel
ОГУ 230201.9008.07 ОО
Руководитель работы
_____________Никонорова О.А.

«____»_________________2008г.

Исполнитель

Студент гр.07 ИСТ-1

_______________________Десятов Е.В.

«____»__________________2008г.
Оренбург 2008

Содержание

Введение. 3

Основная часть. 4

Заключение. 10

Список используемой литературы.. 11

Приложение А.. 12

Приложение Б. 13

Приложение В. 14

Введение

Целью работы является изучение операционной системы Windows, компонентов MS Word и Excel и получение практических навыков работы с современными информационными технологиями. Получение представления о формировании табличной базы данных и о возможностях при работе с ней на примере базы данных в MS Excel.

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

Два сравниваемых объекта выбираются по М - последней цифре зачётной книжки и данным таблицы, приведенным в приложении А.

Стоимость анализируемого оборудования выбирается с учётом предпоследней цифры N зачётной книжки студента.

Обработка базы данных производится по цифрам М, N и b, где b — это сумма последней (М) и предпоследней (N) цифр зачётной книжки с отбрасыванием разряда десятков.

Так как номер студенческого билета 07066, то номер варианта получается 662.

Основная часть

1. Создание базы данных табличного типа средствами таб
личного процессора МS
Excel

Согласно варианту формируется база данных таблица 1. Так как первая цифра варианта равна 6, то данные для этой таблицы берутся из таблицы 8 методички M=6. Столбцы A, B,C заполняются, начиная со строки 10.
Таблица 1 – База данных

1. Память	ТИП		ЦЕНА(у.е)
DIMM 16MB SDRAM 10ns	DIMM		17
DIMM 16MB SDRAM 8ns (PC 100)	DIMM		20,5
DIMM 32MB SDRAM EDO ECC	DIMM		54
DIMM 32MB SDRAM 8ns (PC 100)	DIMM		31
DIMM 64MB SDRAM 8ns (PC 100)	DIMM		60
DIMM 64MB SDRAM 10ns	DIMM		65
DIMM 128MB SDRAM 8ns (PC 100)	DIMM		116
2. Память
SIMM 4Mb 30 pin	SIMM	11
SIMM 4Mb 72 pin EDO	SIMM	8
SIMM 8Mb 72Mb	SIMM	16
SIMM 8Mb 72 pin EDO	SIMM	13
SIMM 16Mb 72 pin	SIMM	36
SIMM 16Mb 72 pin EDO	SIMM	23
SIMM 32Mb 72 pin EDO	SIMM	50

В столбце E нужно указать розничную цену для данного оборудования, а также цену в рублях. Для этого используются формулы.

Для определения розничной цены используется генератор случайных чисел. Так как число N моего варианта равно 6, то диапазон случайных чисел будет определяться рамками от 0,6 до 3 (от 0,1*N до 0,5*N). В ячейку D11 вводится формула =СЛЧИС()*(0,1*6-0,5*6)+0,5*6+C11. Ячейка копируется (нажатием правой кнопки мыши по ячейки, в появившимся меню выбирается пункт «копировать») и вставляется в ячейки D12-D18. Полученные данные столбца D копируются в соседний столбец E, используя команду «специальная вставка» и флаг «значения». Столбец D скрывается (правой кнопкой мыши нужно щелкнуть по столбику D, в появившемся меню выбирается пункт «скрыть»).

Теперь нужно добавить в базу данных столбец с ценой в рублях. Для этого нужно в ячейках А1 и А2 указать курс доллара 25, ячейки А1 присвоить имя «КУРС», а в ячейку F11 ввести формулу «=E11*25», аналогичная формула вводится в остальные ячейки столбца F. Результатом выполненных действий является таблица 2.
Таблица 2 – Цена на оборудование

	A	B	C	E	F	G
…
10	Память	ТИП	ЦЕНА (у.е)	розничная цена(у.е)	Цена в рублях	\|Ц-МАКС\|
11	SIMM 4Mb 72 pin EDO	SIMM	8	9,30	232,52	1 051,49
12	SIMM 4Mb 30 pin	SIMM	11	13,33	333,19	950,82
13	SIMM 8Mb 72 pin EDO	SIMM	13	14,86	371,57	912,44
14	SIMM 8Mb 72Mb	SIMM	16	18,75	468,85	815,16
15	DIMM 16 MB SDRAM 10ns	DIMM	17	19,31	482,82	801,19
16	DIMM 16 MB SDRAM 8ns (PC100)	DIMM	20,5	22,18	554,58	729,43
17	SIMM 16Mb 72 pin EDO	SIMM	23	25,97	649,23	634,78
18	DIMM 32 MB SDRAM 8ns (PC100)	DIMM	31	32,55	813,63	470,38
19	SIMM 16Mb 72 pin	SIMM	36	38,63	965,78	318,23
20	SIMM 32Mb 72 pin EDO	SIMM	50	51,36	1 284,01	0,00
21	DIMM 32 MB SDRAM EDO ECC	DIMM	54	56,18	1 404,50	120,49
22	DIMM 64 MB SDRAM 8ns (PC100)	DIMM	60	62,34	1 558,60	274,59
23	DIMM 64 MB SDRAM 10ns	DIMM	65	67,95	1 698,75	414,74
24	DIMM 128 MB SDRAM 8ns (PC100)	DIMM	116	118,73	2 968,26	1 684,25

В таблице все данные округляются с точностью до копеек или центов (т.е. до сотых), для этого нужно выделить нужные ячейки щелкнуть по ним правой кнопкой мыши, в появившемся меню выбрать пункт «формат ячеек…». В окне «Формат ячеек» выбрать числовые форматы «числовой», и указать два знака после запятой.

Таблицу нужно отсортировать по возрастанию стоимости оборудования, так как b – четный. Для этого выделяется столбик с ценой оборудования и выполняется команда Данные→Сортировка…В появившимся окне ставится пункт автоматически расширить выделенный диапазон и нажимаю кнопку Сортировка… Появится окно сортировка диапазона. В нем задаются нужные параметры, а именно сортировать по Цена у.е. по возрастанию.
2. Расчет и анализ требуемых статистиче
ских показателей по полученной базе данных

Теперь создается таблица 3, согласно /3/. Вместо Объект 1, в ячейке А29 печатается название первого объекта – DIMM, а вместо второго в ячейке А30 – SIMM. Используясь статистическими функциями, определяется минимальная цена среди оборудований DIMM, для этого в ячейку В29 вставляется формула =МИН(F15;F16;F18;F21;F22;F23;F24), для определения самого дорого из оборудований DIMM в ячейку С29 вставляется формула =МАКС(F15;F16;F18;F21;F22;F23;F24). Чтобы определить среднюю цену оборудования нужно в ячейке Е29 прописать формулу =СРЗНАЧ(F15;F16;F18;F24;F23;F22;F21). Для того чтобы определить стандартное отклонение в ячейку F29 вводится формула =СТАНДОТКЛОН(F15;F16;F18;F24;F23;F22;F21) Аналогичным образом заполняется строка SIMM. Только вводятся следующие формулы: =МИН(F11:F14;F17;F19;F20); =МАКС(F11;F12;F13;F14;F17;F19;F20); =СРЗНАЧ(F11;F12;F13;F14;F17;F19;F20); =СТАНДОТКЛОН(F11;F12;F13;F14;F17;F19;F20). Результатом проделанной работы является таблица 3.
Таблица 3 – Статистические показатели

Память	Цена оборудования, рубль
Память	МИН	МАКС	СРЗНАЧ	СТАНДОТКЛ
тип DIMM	482,82	2 968,26	1 354,45	862,08
тип SIMM	232,52	1 284,01	615,02	382,56

Определяется критерий К, так как β=2, то критерий К – «Наиболее близкой стоимости к максимальному значению объекта 2».

Добавляется столбец G к таблице 2, в который вносятся абсолютное значение разности «Цена у.е.» и «Макс» (тип SIMM).

Определяется название оборудования по цене наиболее близкой к максимальному значению типа 2, используется функция БИЗВЛЕЧЬ и составляется таблица 4. Первая строка содержит имя поля критерия, вторая – значение, по которому идет отбор.
Таблица 4 – Поиск оборудования

	…	С		E	F	G
1	…	По критерию К			Критерии
2	…	Память	Цена в рублях		Объект (тип)		\|Ц-МАКС\|
3	…	DIMM 32 MB SDRAM EDO ECC	1 404,50		DIMM		120,49

В ячейку С3 вводится формула =БИЗВЛЕЧЬ(A10:G24;A10;G2:G3), в ячейку Е3 =БИЗВЛЕЧЬ(A10:G24;F10;G2:G3), в ячейку G3 вводится формула =МИН(G21:G24;G11:G19).

Определяется объем продаж оборудования за 6 месяцев предшествующих декабрю по трем наименованиям оборудования: с минимальной и максимальной стоимостью для объекта 1 и по критерию К, т.е. получилось три оборудования: DIMM 16 MB SDRAM 10ns, DIMM 128 MB SDRAM 8ns (PC100), DIMM 32 MB SDRAM EDO ECC. На листе 3 составляется новая база данных. Дата вводится в формате мес.год. Объем продаж определяется с помощью генератора случайных чисел, аналогично тому, как определялась розничная цена, только диапазон для оборудования с максимальной стоимостью будет от NM до 1NM, для оборудования с минимальной стоимостью от NM до 3NM; и от NM до 2NM для оборудования со стоимостью по критерию К. Для генерирования случайных чисел использовались столбцы H, I и J поэтому они скрываются. В столбцы Стоимость… вводится формула вида «Объем продаж*цену оборудования», Цена оборудования берется из таблицы 2 и таблицы 3. Результаты показаны в таблице 5.
Таблица 5 – Анализ продажи оборудования

	DIMM 16 MB SDRAM 10ns		DIMM 128 MB SDRAM 8ns (PC100)		DIMM 32 MB SDRAM EDO ECC
Месяц	Объем продаж (шт.) МИН	Стоимость МИН (руб.)	Объем продаж (шт.) МАКС	Стоимость МАКС (руб.)	Объем продаж (шт.) по крит. К	Стоимость по крит.К (руб.)
июн.07	206	99 349,68	103	305730,78	163	228933,50
июл.07	343	165 422,04	120	356191,20	169	237360,50
авг.07	266	128 286,48	140	415556,40	233	327248,50
сен.07	171	82 469,88	109	323540,34	170	238765,00
окт.07	90	43 405,20	103	305730,78	139	195225,50
ноя.07	211	101 761,08	86	255270,36	80	112360,00
дек.07	132	63 560,00	93	275348,94	102	143539,90
янв.08	108	51 967,25	89	262788,59	86	120746,87
фев.08	84	40 374,49	84	250801,19	70	97953,84
мар.08	61	29 529,65	81	240082,71	54	75160,81
апр.08	37	17 936,90	77	229131,07	37	52367,79
май.08	14	6 718,10	74	219008,60	21	29574,76

Так как M=6, то строятся два графика по максимальной и минимальной стоимости. Полученные два графика отображены на рисунке 1.

Прогнозируется продажа оборудования на шесть последующих месяцев. Для прогнозирования используется функция ТЕНДЕНЦИЯ, РОСТ, и ПРОГРЕССИЯ для разных видов оборудования. Для прогноза продажи DIMM 16 MB SDRAM 10ns используется ТЕНДЕНЦИЯ, для DIMM 128 MB SDRAM 8ns (PC100) – РОСТ, а для DIMM 32 MB SDRAM EDO ECC – Арифметическая прогрессия. Столбик А заполняется еще шестью месяцами, а в ячейки B9 вводится формула =ТЕНДЕНЦИЯ(B$3:B8;A$3:A8;A9;1) и копируется ещё в пять нижних ячеек. B ячейку D9 вводится - =РОСТ(D$3:D8;A$3:A8;A9;1) и также копируется еще в пять ячеек. Выделяются ячейки F3:F8. Курсор мыши наводится на черный квадрат в правом нижнем углу рамки и границы рамки растягиваются за этот квадрат до ячейки F14 включительно.

С помощью мастера диаграмм строится диаграмма по результатам таблицы 5. Также на графики отображаются линии тренда, коэффициент аппроксимации. Для их отображения, необходимо кликнуть правой кнопкой мыши по диаграмме одного из оборудования и в появившемся окне выбрать добавить линию тренда. Для отображения коэффициента аппроксимации нужно кликнуть двойным щелчком мыши по линии тренда в появившемся окне формат линии тренда, выбрать вкладку параметры и два флага: показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R²). В результате получается диаграмма указанная на рисунке 2.

Рисунок 1 – Зависимость объема продажи оборудования за полугодие

Рисунок 2 – Зависимость продажи оборудования за год
Вывод:как видно из диаграммы, отображенной на рисунке 2, оборудование по минимальной стоимости по сравнению с максимальной продается в большем объёме.

Закон изменения стоимости оборудования DIMM 128 MB SDRAM 8ns (PC100) – полимерный, а DIMM 16 MB SDRAM 10ns – скользящее среднее (2 линейный фильтр). Коэффициент аппроксимации R² близок к единице, что указывает на высокую степень достоверности выбранного закона.

Рассчитывается «количество оборудования (выбранного по минимальной стоимости), стоимость которого больше 83».

Для этого используется функция базы данных БДСЧЕТ и критерий «Стоимость МАКС (руб.) > 83». В свободную ячейку L2 копируется содержимое ячейки D2 – Стоимость МАКС (руб.), а в ячейку L3 печатается >83. В ячейку L5 вводится функция =БСЧЁТ(A2:G14;D2;L2:L3). Ответ данной задачи 12 единиц.

Заключение

Благодаря проделанной работе были получены практические знания по работе в операционной системе Windows, были изучены ее компоненты MS Word и Excel, а также навыки работы с современными информационными технологиями. Получение представления о формировании табличной базы данных и о возможностях при работе с ней на примере базы данных в MS Excel.

Список используемой литературы

1.     Информатика: Учебник. – 3-е перераб. изд / Под ред. Н.В.Макаровой. – М.: Финансы и статистика, 2004. – 768с.: ил

2.     Информатика. Базовый курс / С.В.Симонович [и др.] - СПб: «Питер», 2001.-640 с.

3.     Методические указания к курсовой работе/О. А. Никонорова - Оренбург: ГОУ ОГУ, 2005. - 28 с.

Приложение А

(справочное)

Таблица 1- База данных (с формулами)

Память

ТИП

ЦЕНА (у.е)

розничная цена(у.е)

Цена в рублях

|Ц-МАКС|

SIMM 4Mb 72 pin EDO

SIMM

8

9,30090960476549

=E11*$A$2

=ABS($C$30-$F11)

SIMM 4Mb 30 pin

SIMM

11

13,3274159976935

=E12*$A$2

=ABS($C$30-$F12)

SIMM 8Mb 72 pin EDO

SIMM

13

14,8628040446241

=E13*$A$2

=ABS($C$30-$F13)

SIMM 8Mb 72Mb

SIMM

16

18,7538875467684

=E14*$A$2

=ABS($C$30-$F14)

DIMM 16 MB SDRAM 10ns

DIMM

17

19,3126081921059

=E15*$A$2

=ABS($C$30-$F15)

DIMM 16 MB SDRAM 8ns (PC100)

DIMM

20,5

22,1833591176786

=E16*$A$2

=ABS($C$30-$F16)

SIMM 16Mb 72 pin EDO

SIMM

23

25,969296424893

=E17*$A$2

=ABS($C$30-$F17)

DIMM 32 MB SDRAM 8ns (PC100)

DIMM

31

32,5451082406628

=E18*$A$2

=ABS($C$30-$F18)

SIMM 16Mb 72 pin

SIMM

36

38,6312160661663

=E19*$A$2

=ABS($C$30-$F19)

SIMM 32Mb 72 pin EDO

SIMM

50

51,3603981037735

=E20*$A$2

=ABS($C$30-$F20)

DIMM 32 MB SDRAM EDO ECC

DIMM

54

56,1799215917239

=E21*$A$2

=ABS($C$30-$F21)

DIMM 64 MB SDRAM 8ns (PC100)

DIMM

60

62,3438669809507

=E22*$A$2

=ABS($C$30-$F22)

DIMM 64 MB SDRAM 10ns

DIMM

65

67,949974410446

=E23*$A$2

=ABS($C$30-$F23)

DIMM 128 MB SDRAM 8ns (PC100)

DIMM

116

118,730529489446

=E24*$A$2

=ABS($C$30-$F24)

Приложение Б

(справочное)
Таблица 2 – Анализ продажи оборудования (с формулами)

DIMM 16 MB SDRAM 10ns

DIMM 128 MB SDRAM 8ns (PC100)

DIMM 32 MB SDRAM EDO ECC

Месяц

Объем продаж (шт.) МИН

Стоимость МИН (руб.)

Объем продаж (шт.) МАКС

Стоимость МАКС (руб.)

Объем продаж (шт.) по крит. К

Стоимость по крит.К (руб.)

39234

206

=B3*482,28

103

=D3*2968,26

163

=F3*1404,5

39264

343

=B4*482,28

120

=D4*2968,26

169

=F4*1404,5

39295

266

=B5*482,28

140

=D5*2968,26

233

=F5*1404,5

39326

171

=B6*482,28

109

=D6*2968,26

170

=F6*1404,5

39356

90

=B7*482,28

103

=D7*2968,26

139

=F7*1404,5

39387

211

=B8*482,28

86

=D8*2968,26

80

=F8*1404,5

39417

=ТЕНДЕНЦИЯ(B$3:B8;A$3:A8;A9;1)

=B9*482,28

=РОСТ(D$3:D8;A$3:A8;A9;1)

=D9*2968,26

102,2

=F9*1404,5

39448

=ТЕНДЕНЦИЯ(B$3:B9;A$3:A9;A10;1)

=B10*482,28

=РОСТ(D$3:D9;A$3:A9;A10;1)

=D10*2968,26

85,9714285714286

=F10*1404,5

39479

=ТЕНДЕНЦИЯ(B$3:B10;A$3:A10;A11;1)

=B11*482,28

=РОСТ(D$3:D10;A$3:A10;A11;1)

=D11*2968,26

69,7428571428576

=F11*1404,5

39508

=ТЕНДЕНЦИЯ(B$3:B11;A$3:A11;A12;1)

=B12*482,28

=РОСТ(D$3:D11;A$3:A11;A12;1)

=D12*2968,26

53,5142857142856

=F12*1404,5

39539

=ТЕНДЕНЦИЯ(B$3:B12;A$3:A12;A13;1)

=B13*482,28

=РОСТ(D$3:D12;A$3:A12;A13;1)

=D13*2968,26

37,2857142857146

=F13*1404,5

39569

=ТЕНДЕНЦИЯ(B$3:B13;A$3:A13;A14;1)

=B14*482,28

=РОСТ(D$3:D13;A$3:A13;A14;1)

=D14*2968,26

21,0571428571426

=F14*1404,5

Приложение В

(справочное)
Таблица 3 – Статистические показатели (с формулами)

Память

Цена оборудования, рубль

МИН

МАКС

СРЗНАЧ

СТАНДОТКЛ

тип DIMM

=МИН(F15;F16;F18;F21;F22;

F23;F24)

=МАКС(F15;F16;F18;

F21;F22;F23;F24)

=СРЗНАЧ(F15;F16;F18;

F24;F23;F22;F21)

=СТАНДОТКЛОН(F15;F16;F18;F24;

F23;F22;F21)

тип SIMM

=МИН(F11:F14;F17;F19;F20)

=МАКС(F11;F12;F13;

F14;F17;F19;F20)

=СРЗНАЧ(F11;F12;F13;

F14;F17;F19;F20)

=СТАНДОТКЛОН(F11;F12;F13;F14;

F17;F19;F20)

Bukvasha