Анализ данных в MS Excel

КУРСОВАЯ РАБОТА Анализ данных в MS Excel

Содержание Введение Задание № 1. Встроенные функции EXCEL. Статистический анализ Задание № 2. Создание БД «Автомагазин»

Заключение

Список литературы Приложения

Введение

База данных (БД) — это интегрированная совокупность взаимосвязанных данных. Для облегчения работы с базами данных используются системы управления базами данных (СУБД, англоязычная аббревиатура DBMS — Database Management System) — специальные пакеты программ обеспечивающие ввод, поиск, хранение, пополнение, корректировку данных, формирование отчетов и ответов на запросы пользователей баз данных. СУБД также обеспечивают сохранность и перемещение данных, а также возможность их использования другими программными средствами. В настоящее время большинство экономических и информационно-справочных программных комплексов реализовано на основе применения той или иной СУБД. MS Access — наиболее популярная на сегодняшний день СУБД для персональных компьютеров. Она представляет собой систему обслуживания реляционных баз данных с графической оболочкой. Данные в таких базах оформляются в виде одной или нескольких таблиц, состоящих из однотипных записей. Система обслуживания включает в себя ввод данных в ЭВМ, отбор данных по каким-либо признакам (критериям или параметрам), преобразование структуры данных, вывод данных, являющихся результатом решения задач в табличном или каком-либо ином удобном для пользователя виде.

Microsoft 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). Как результат мы увидим число 26 266 971,82 что говорит о высокой дисперсии, т. е. о высокой степени различия данных. Давайте рассмотрим функцию стандартного отклонения, для этого введем в ячейку E13 =СТАНДОТКЛОН (98;170), в результате увидим число 50,9.

Давайте построим диаграмму на основе значений Y и отложим минимальное, среднее и максимальное значения.

Чтобы это сделать выделяем весь диапазон значений от B1 до B20, жмем Ctrl и выделяем ячейки C2-C4. После этого заходим во вкладку Вставка и в меню Гистограмма выбираем любую понравившую.

Задание № 2. Создание БД «Автомагазин»

Требуется создать БД «Автомагазин», состоящую из таблиц:

Таблица 1. «Модели автомобилей»

Таблица 2. «Клиенты и заказы»

Для этого запускаем MS Access и на вкладке Создание выбираем Таблица. Из вкладки Режим таблицы в меню Режим жмем Конструктор, нам предлагается ввести название таблицы, где мы пишем Модели автомобилей и нажимаем кнопку Ок. В столбце Имя поля пишем Код Модели (словами) а в столбце тип данных выбираем счетчик. Эта строка в данной таблицы и будет Первичным ключом. Аналогично для полей Модель, Цвет, Коробка передачи и Обивка выбираем тип данных текстовый. Нам осталось записать поле Заводская цена ($) и назначить для него Числовой тип. После этого во вкладке Конструктор выбираем из меню Режим — Таблица. Начинаем заполнять все поля в соответствии с Таблицей 1 приведенной раннее.

Переходим к созданию второй таблицы (Создание->Таблица), задаем ей название «Клиенты и Заказы». Переходим в Режим Конструктор и пишем названия полей: Код Модели -> тип Числовой, № заказа -> тип Числовой и делаем его ключевым полем (нажав на кнопку Ключевое поле). Фамилия заказчика -> тип Текстовый, Город -> тип Текстовый, Телефон -> Числовой, а Дата заказа -> выбираем Дата/Время. Переходим в Режим Таблицы и заполняем ее.

Далее переходим на вкладку «Работа с базами данных» и жмем кнопку «Схема данных». В таблице «Клиенты и заказы» нажимаем на строку «Код модели» и не отпуская мышку тянем на строку «Код модели» из таблицы «Модели автомобилей». В окне «Изменение связей» ставим галочку перед: «Обеспечение целостности данных», «Каскадное обновление связанных полей», «Каскадное удаление связанных записей» и нажимаем кнопку «Создать». Получится как на Рисунке 1 (в приложении).

Следующим шагом необходимо создать запрос, для определения клиентов из Санкт-Петербурга, с указанием всех характеристик заказанных ими автомобилей. Для этого во вкладке «Создание» нажимаем по кнопке «Мастер запросов». Выбираем простой запрос и жмем Ок. Из таблицы «Клиенты и заказы» выбираем поле Фамилия Заказчика и Город, затем перетаскиваем все вправо (жмем >). Выбираем таблицу «Модели автомобилей» и перетаскиваем все поля вправо (Рисунок 2). Жмем кнопку Далее, вводим название «Клиенты Санкт-Петербурга», а затем нажимаем Готово. Переходим в режим Конструктор. В столбце Город, в строке «Условие отбора» пишем Санкт-Петербург и жмем клавишу Enter. После чего во вкладке Конструктор нажимаем кнопку «Выполнить» и мы увидим только 3 записи, удовлетворяющие нашему условию.

Теперь нам необходимо создать форму по запросу «Клиенты Санкт-Петербурга». Для этого переходим на вкладку «Создание» и в меню другие формы выбираем Мастер форм. Выбираем запрос «Клиенты Санкт-Петербурга» и перетаскиваем все его поля вправо (жмем >>). Внешний вид таблицы выбираем: в один столбец. Нажимаем далее и выбираем стиль официальный, нажимаем опять Далее и задаем имя формы «Клиенты Санкт-Петербурга». Жмем Готово (Рисунок 3).

Чтобы добавить в созданную форму командную кнопку для вывода текущей формы на печать необходимо перейти в режим конструктор. На вкладке Конструктор выбираем элемент кнопка и накладываем его на форму. Открывается окно «Создание кнопок», в категории выбираем «Работа с формой», а в действии «Печать текущей формы» (Рисунок 8). Выбираем Checkbox рисунок. Жмем Далее, а затем Готово. Переходим в Режим — Таблица, если принтер подключен, то кнопка будет работать.

Для создания отчета переходим во вкладку Создание и выбираем Мастер отчетов. Из таблицы «Модели автомобилей» выбираем поле «Код модели» и переносим его вправо, а из таблицы «Клиенты и заказы» выбираем поля «Фамилия заказчика» и «Дата заказа» и жмем Далее. Вид представления данных выбираем «Модели автомобилей», нажимаем Далее. Уровни группировки стоят по-умолчанию, как надо, поэтому жмем Далее, сортировка не требуется поэтому опять жмем Далее. Макет выбираем Блок, ориентация Книжная. Нажимаем Далее выбираем стиль Официальная, затем опять Далее. Имя отчета пишем «Обзор Заказов» и нажимаем кнопку Готово. (Рисунок 4)

Переходим в Конструктор, в разделе Нижний Колонтитул пишем следующее: =" Автомобили АСТРО", нажимаем Enter (Рисунок 6). Для редактирования этой надписи, снова выделяем это поле и справа в окне свойств выставляем следующие параметры: размер шрифта делаем 14, цвет текста выставляем #ED1C24 (Красный), шрифт выбираем Times New Roman (Рисунок 5).

Заключение

На этом я заканчиваю свою курсовую работу по закреплению навыков работы в MS Excel и MS Access. В наш стремительный век невозможно представить себе какое-либо начинание без возможности мгновенного получения нужной информации и ее структурирования, а в этом нам помогают программы для организации данных в таблице для документирования и графического представления информации и записи информации в системах управления базами данных (СУБД). Яркими представителями этих возможностей являются продукты Microsoft Excel и Microsoft Access. На практических занятиях я применил теоретические знания по MS Excel и MS Access, а именно в решении задач статистического анализа, построении логических конструкций, создании графиков и диаграмм в MS Excel, создании таблиц, отчетов, запросов, форма, создании связей между таблицами (ACCESS). Если Вы решили познать все возможности структурирования информации, то вам предстоит долгий и нелегкий путь, в котором я желаю вам упорства, терпения, и интересных открытий.

1. Базы данных. Модели и языки. Автор: С. Д. Кузнецов. Издательство: Бином-Пресс, 2008 год.

2. Базы данных для инженеров. Автор: Зрюмов Е. А., Зрюмова А. Г. Издательство: АлтГТУ 2010 год.

3. Финансовое моделирование и оптимизация средствами Excel 2007. Автор: Васильев А. Н. Издательство: Питер 2009 год.

4. Excel 2007 «без воды». Все, что нужно для уверенной работы. Автор: Голышева А. В., Корнеев В. Н. Издательство: Наука и техника 2008 год.

5. Функции и формулы Excel 2007. Автор: Веденеева Е. А. Издательство: Питер 2009 год.

Приложения Рисунок 1. Схема данных Рисунок 2. Мастер запросов Рисунок 3. Форма Клиентов Санкт-Петербурга Рисунок 4. Отчет по всем заказам Рисунок 5. Окно свойств в режиме Конструктор Рисунок 6. Отчет в Режиме Конструктора Рисунок 7. Таблица «Модели Автомобилей»

Рисунок 8. Создание кнопок