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

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

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

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

Факультет информационных технологий Кафедра информационных систем и технологий

Расчетно-графическое задание

по дисциплине «Информатика»

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

ОГУ 230201.9008.07 ОО

Руководитель работы
_____________Никонорова О.А.

«____»_________________2008г.

Исполнитель

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

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

«____»__________________2008г.

Оренбург 2008

Содержание

Введение. 3

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

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

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

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

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

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

Введение

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

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

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

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

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

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

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

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

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

Таблица 1 – База данных

В столбце 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 – Цена на оборудование

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

Таблицу нужно отсортировать по возрастанию стоимости оборудования, так как 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 – Статистические показатели

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

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

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

Таблица 4 – Поиск оборудования

В ячейку С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 MBSDRAM 8ns (PC100), DIMM 32 MB SDRAM EDO ECC. На листе 3 составляется новая база данных. Дата вводится в формате мес.год. Объем продаж определяется с помощью генератора случайных чисел, аналогично тому, как определялась розничная цена, только диапазон для оборудования с максимальной стоимостью будет от NM до 1NM, для оборудования с минимальной стоимостью от NM до 3NM; и от NM до 2NM для оборудования со стоимостью по критерию К. Для генерирования случайных чисел использовались столбцы H, I и J поэтому они скрываются. В столбцы Стоимость… вводится формула вида «Объем продаж*цену оборудования», Цена оборудования берется из таблицы 2 и таблицы 3. Результаты показаны в таблице 5.

Таблица 5 – Анализ продажи оборудования

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

Прогнозируется продажа оборудования на шесть последующих месяцев. Для прогнозирования используется функция ТЕНДЕНЦИЯ, РОСТ, и ПРОГРЕССИЯ для разных видов оборудования. Для прогноза продажи DIMM 16 MB SDRAM 10ns используется ТЕНДЕНЦИЯ, для DIMM 128 MBSDRAM 8ns (PC100) – РОСТ, а дляDIMM 32 MBSDRAMEDOECC – Арифметическая прогрессия. Столбик А заполняется еще шестью месяцами, а в ячейки 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 MBSDRAM 8ns (PC100) – полимерный, а DIMM 16 MBSDRAM 10ns – скользящее среднее (2 линейный фильтр). Коэффициент аппроксимации R²
близок к единице, что указывает на высокую степень достоверности выбранного закона.

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

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

Заключение

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

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

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

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

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

Приложение А

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

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

Приложение Б

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

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

Приложение В

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

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