Анализ данных в MS Excel
КУРСОВАЯ
РАБОТА
Анализ
данных в MS Excel
Содержание
Введение
Задание № 1. Встроенные функции EXCEL. Статистический анализ
Задание №2. Создание БД «Автомагазин»
Заключение
Список литературы
Приложения
Введение
База данных (БД) - это интегрированная
совокупность взаимосвязанных данных. Для облегчения работы с базами данных
используются системы управления базами данных (СУБД, англоязычная аббревиатура
DBMS - Database Management System) - специальные пакеты программ обеспечивающие
ввод, поиск, хранение, пополнение, корректировку данных, формирование отчетов и
ответов на запросы пользователей баз данных. СУБД также обеспечивают
сохранность и перемещение данных, а также возможность их использования другими
программными средствами. В настоящее время большинство экономических и
информационно-справочных программных комплексов реализовано на основе
применения той или иной СУБД. MS Access - наиболее популярная на сегодняшний
день СУБД для персональных компьютеров. Она представляет собой систему обслуживания
реляционных баз данных с графической оболочкой. Данные в таких базах
оформляются в виде одной или нескольких таблиц, состоящих из однотипных
записей. Система обслуживания включает в себя ввод данных в ЭВМ, отбор данных
по каким-либо признакам (критериям или параметрам), преобразование структуры
данных, вывод данных, являющихся результатом решения задач в табличном или
каком-либо ином удобном для пользователя виде.Excel - программа,
предназначенная для организации данных в таблице для документирования и
графического представления информации.
Преимуществом MS Excel является то,
что программа помогает оперировать большими объемами информации. рабочие книги
MS Excel предоставляют возможность хранения и организации данных, вычисление
суммы значений в ячейках. Ms Excel предоставляет широкий спектр методов
позволяющих сделать информацию простой для восприятия.
Знания и умения владеть MS Excel и
MS Access являются актуальными, так как, в наше время, современный мир насыщен
огромным количеством информацией, с которой просто необходимо уметь работать.
Цель курсовой работы: получение и
закрепление навыков в решении задач статистического анализа, построении
логических конструкций, создание графиков и диаграмм в MS Excel, а также в
создание, исследование и обработки базы данных (ACCESS).
excel база данные
Задание № 1. Встроенные функции
EXCEL. Статистический анализ
Необходимо найти значение Y =
/x+ex/+tg3x*lg x2 на интервале -10 <= x <= 10, с шагом Dx = 1. Заполним
столбец А значениями аргумента функции. Чтобы не вводить их вручную, применим
следующий прием. Вводим в ячейку А1 начальное значение аргумента -10. На
вкладке Главная выбираем меню Заполнить -> Прогрессия и в открывшемся
диалоговом окне указываем предельное значение (10), шаг (1) и Расположение (по столбцам).
После нажатия кнопки ОК в столбце А будут введены все значения аргумента. В
ячейке В1 вводим формулу:
=ABS(A1+EXP(A1))+TAN(3*A1)*LN(A1*A1)
Размножим ее на остальные ячейки
столбца B и получим соответствующие значения Y. Для того чтобы определить количество
отрицательных Y, в ячейку С1 вводим =СЧЁТЕСЛИ(B1:B20;"<0").
Для определения минимально значения
Y, вводим в ячейку C2 =МИН(B1:B20), для нахождения максимального значения Y
введем в ячейку C3 =МАКС(B1:B20), а чтобы найти среднее значение Y, вводим в
ячейку C4 функцию =СРЗНАЧ(B1:B20). Во всех этих функциях, в круглых скобках
передан диапазон значений, среди которого и происходят вычисления.
Используя логическую конструкцию,
вычислим сумму значений функции, если среднее, максимальное и минимальное значения
имеют одинаковые знаки и произведение в противном случае. Для этого введем в
ячейку E10
ЕСЛИ(И((C2>=0);(C3>=0);(C4>=0));СУММ(B1:B20);ПРОИЗВЕД(B1:B20)).
А теперь, давайте сгенерируем
случайное число, в ячейке E12, для этого в ней напишем следующее:
=СЛУЧМЕЖДУ(1;1000). В ячейках D13 по D32 произведем умножение значений Y на
случайное число, для этого впишем формулу =B1*E12, где B1 будем меняться вплоть
до B20. Давайте, теперь вычислим медиану числа (числа, являющегося серединой
множества). Для этого в ячейку E15 напишем следующее: =МЕДИАНА(67;262), числа
67 и 262 взяты произвольно. Результатом будет число 164,5. Для вычисления моды
(наиболее часто встречаемое значение в множестве) впишем в ячейку E16
=МОДА(D13:D32), у меня это диапазон значений от D13 до D32, но можно просто
указать в формуле какие-либо значения, через точку с запятой.
Результат #Н/Д, так как не одно
число не повторяется дважды. Следующей функцией будет дисперсия, которая
позволяет измерить, насколько данные отличаются друг от друга. Введем в ячейку
E14 формулу вида: =ДИСП(B1:B20). Как результат мы увидим число 26266971,82 что
говорит о высокой дисперсии, т.е. о высокой степени различия данных. Давайте
рассмотрим функцию стандартного отклонения, для этого введем в ячейку E13
=СТАНДОТКЛОН(98;170), в результате увидим число 50,9.
Давайте построим диаграмму на основе
значений Y и отложим минимальное, среднее и максимальное значения.
Чтобы это сделать выделяем весь
диапазон значений от B1 до B20, жмем Ctrl и выделяем ячейки C2-C4. После этого
заходим во вкладку Вставка и в меню Гистограмма выбираем любую понравившую.
Задание №2. Создание БД
«Автомагазин»
Требуется создать БД «Автомагазин»,
состоящую из таблиц:
Таблица 1. «Модели автомобилей»
Код модели
|
Модель
|
Цвет
|
Коробка передач
|
Обивка
|
Заводская цена ($)
|
12579
|
Classic liftbackXL
|
Красный
|
Ручная
|
Ткань
|
35700
|
12580
|
Classic liftback GT
|
Черный
|
Автоматическая
|
Ткань
|
39200
|
12651
|
Classic Compact XL
|
Антрацит
|
Ручная
|
Кожа
|
41100
|
12653
|
Classic Compact GT
|
Черный
|
Ручная
|
Велюр
|
37900
|
12410
|
Classic Combi
|
Антрацит
|
Автоматическая
|
Ткань
|
46200
|
Таблица 2. «Клиенты и заказы»
Код модели
|
№заказа
|
Фамилия заказчика
|
Город
|
Дата заказа
|
12579
|
123
|
Иванов
|
Санкт-Петербург
|
2859655
|
3.08.97
|
12580
|
130
|
Петров
|
Москва
|
3856743
|
6.10.97
|
12651
|
133
|
Сидоров
|
Киев
|
342679
|
25.12.97
|
12653
|
135
|
Бендер
|
Сухуми
|
56438
|
5.01.98
|
12410
|
138
|
Иванов
|
Санкт-Петербург
|
2859655
|
20.02.98
|
12653
|
140
|
Петров
|
Москва
|
3856743
|
30.06.98
|
12410
|
145
|
Сидоров
|
Киев
|
342679
|
25.08.98
|
12580
|
160
|
Дубов
|
Санкт- Петербург
|
4356723
|
17.09.98
|
12410
|
165
|
Сухов
|
Москва
|
9439965
|
20.10.98
|
12653
|
166
|
Сахаров
|
Киев
|
234567
|
25.12.98
|
Для этого запускаем MS Access и на
вкладке Создание выбираем Таблица. Из вкладки Режим таблицы в меню Режим жмем
Конструктор, нам предлагается ввести название таблицы, где мы пишем Модели
автомобилей и нажимаем кнопку Ок. В столбце Имя поля пишем Код Модели (словами)
а в столбце тип данных выбираем счетчик. Эта строка в данной таблицы и будет
Первичным ключом. Аналогично для полей Модель, Цвет, Коробка передачи и Обивка
выбираем тип данных текстовый. Нам осталось записать поле Заводская цена($) и
назначить для него Числовой тип. После этого во вкладке Конструктор выбираем из
меню Режим - Таблица. Начинаем заполнять все поля в соответствии с Таблицей 1
приведенной раннее.
Переходим к созданию второй таблицы
(Создание->Таблица), задаем ей название «Клиенты и Заказы». Переходим в
Режим Конструктор и пишем названия полей: Код Модели -> тип Числовой, №
заказа -> тип Числовой и делаем его ключевым полем (нажав на кнопку Ключевое
поле). Фамилия заказчика -> тип Текстовый, Город -> тип Текстовый,
Телефон -> Числовой, а Дата заказа -> выбираем Дата/Время. Переходим в
Режим Таблицы и заполняем ее.
Далее переходим на вкладку «Работа с
базами данных» и жмем кнопку «Схема данных». В таблице «Клиенты и заказы»
нажимаем на строку «Код модели» и не отпуская мышку тянем на строку «Код
модели» из таблицы «Модели автомобилей». В окне «Изменение связей» ставим
галочку перед: «Обеспечение целостности данных», «Каскадное обновление
связанных полей», «Каскадное удаление связанных записей» и нажимаем кнопку
«Создать». Получится как на Рисунке 1 (в приложении).
Следующим шагом необходимо создать
запрос, для определения клиентов из Санкт-Петербурга, с указанием всех
характеристик заказанных ими автомобилей. Для этого во вкладке «Создание»
нажимаем по кнопке «Мастер запросов». Выбираем простой запрос и жмем Ок. Из
таблицы «Клиенты и заказы» выбираем поле Фамилия Заказчика и Город, затем
перетаскиваем все вправо (жмем >). Выбираем таблицу «Модели автомобилей» и
перетаскиваем все поля вправо (Рисунок 2). Жмем кнопку Далее, вводим название
«Клиенты Санкт-Петербурга», а затем нажимаем Готово. Переходим в режим
Конструктор. В столбце Город, в строке «Условие отбора» пишем Санкт-Петербург и
жмем клавишу Enter. После чего во вкладке Конструктор нажимаем кнопку
«Выполнить» и мы увидим только 3 записи, удовлетворяющие нашему условию.
Теперь нам необходимо создать форму
по запросу «Клиенты Санкт-Петербурга». Для этого переходим на вкладку
«Создание» и в меню другие формы выбираем Мастер форм. Выбираем запрос «Клиенты
Санкт-Петербурга» и перетаскиваем все его поля вправо (жмем >>). Внешний
вид таблицы выбираем: в один столбец. Нажимаем далее и выбираем стиль
официальный, нажимаем опять Далее и задаем имя формы «Клиенты Санкт-Петербурга».
Жмем Готово (Рисунок 3).
Чтобы добавить в созданную форму
командную кнопку для вывода текущей формы на печать необходимо перейти в режим
конструктор. На вкладке Конструктор выбираем элемент кнопка и накладываем его
на форму. Открывается окно «Создание кнопок», в категории выбираем «Работа с
формой», а в действии «Печать текущей формы» (Рисунок 8). Выбираем Checkbox
рисунок. Жмем Далее, а затем Готово. Переходим в Режим - Таблица, если принтер
подключен, то кнопка будет работать.
Для создания отчета переходим во
вкладку Создание и выбираем Мастер отчетов. Из таблицы «Модели автомобилей»
выбираем поле «Код модели» и переносим его вправо, а из таблицы «Клиенты и
заказы» выбираем поля «Фамилия заказчика» и «Дата заказа» и жмем Далее. Вид
представления данных выбираем «Модели автомобилей», нажимаем Далее. Уровни
группировки стоят по-умолчанию, как надо, поэтому жмем Далее, сортировка не
требуется поэтому опять жмем Далее. Макет выбираем Блок, ориентация Книжная.
Нажимаем Далее выбираем стиль Официальная, затем опять Далее. Имя отчета пишем
«Обзор Заказов» и нажимаем кнопку Готово. (Рисунок 4)
Переходим в Конструктор, в разделе
Нижний Колонтитул пишем следующее: =” Автомобили АСТРО”, нажимаем Enter
(Рисунок 6). Для редактирования этой надписи, снова выделяем это поле и справа
в окне свойств выставляем следующие параметры: размер шрифта делаем 14, цвет
текста выставляем #ED1C24 (Красный), шрифт выбираем Times New Roman (Рисунок
5).
Заключение
Список литературы
1. Базы данных. Модели и языки. Автор: С.Д. Кузнецов.
Издательство: Бином-Пресс, 2008 год.
. Базы данных для инженеров. Автор: Зрюмов Е.А., Зрюмова
А.Г. Издательство: АлтГТУ 2010 год.
. Финансовое моделирование и оптимизация средствами Excel
2007. Автор: Васильев А.Н. Издательство: Питер 2009 год.
. Excel 2007 «без воды». Все, что нужно для уверенной
работы. Автор: Голышева А. В., Корнеев В.Н. Издательство: Наука и техника 2008
год.
. Функции и формулы Excel 2007. Автор: Веденеева Е.А.
Издательство: Питер 2009 год.
Приложения
Рисунок 1. Схема данных
Рисунок 2. Мастер запросов
Рисунок 3. Форма Клиентов
Санкт-Петербурга
Рисунок 4. Отчет по всем заказам
Рисунок 5. Окно свойств в режиме
Конструктор
Рисунок 6. Отчет в Режиме
Конструктора
Рисунок 7. Таблица «Модели
Автомобилей»
Рисунок 8. Создание кнопок