Применение приложения excel как базы данных

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

«Южно – Уральский государственный университет»

«Факультет: Экономика и Управление»

«Кафедра: Экономика и Финансы»

Челябинск, 2011

 

Руководитель

__________

__________2011г.

Автор работы

Студент группы ЭиУ-222

__________

___________2011 г.

Работа защищена с оценкой

____________________

______________2011 г.

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

 К КУРСОВОЙ РАБОТЕ

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

ЮУрГУ-080105.2011.ПЗ.КР

 

 

Оглавление

Введение 3

Применение  приложения excel как базы данных 4

Главная таблица  «Автовокзал» 6

Сортировка 6

Фильтрация  данных 11

Структурирование 13

Промежуточные итоги 13

Консолидация  данных 14

Сводная таблица  и диаграмма 16

Применение  приложения Access для решения задач в различных предметных областях 19

Таблица «Автовокзал» 19

Запросы 19

Отчеты 21

Формы 23

Заключение……………………………………………………………………….25

Список литературы 26

 

 

 

Введение

Основные идеи современной  информационной технологии базируются на концепции, согласно которой данные должны быть организованы в базы данных с целью адекватного отображения  изменяющегося реального мира и  удовлетворения информационных потребностей пользователей. Эти базы данных создаются  и функционируют под управлением  специальных программных комплексов, называемых системами управления базами данных (СУБД).

Что можно сказать о  современных программах обработки  электронных таблиц? Сегодня это  средства, позволяющие не только выполнять  вычисления, но и управлять списками и создавать диаграммы. Если же говорить об EXCEL, которая является одной из наиболее известных программ обработки электронных таблиц. Возможности EXCEL очень высоки. Обработка текста, управление базами данных - программа настолько мощна, что во многих случаях превосходит специализированные  программы - редакторы или программы баз данных. Такое многообразие функций может поначалу запутать, нежели заставить применять их на практике. Но по мере приобретения опыта начинаешь по достоинству ценить то, что границ возможностей EXCEL тяжело достичь.

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

Целью этой работы является применение EXCEL как базы данных и применение приложения ACCESS для решения задач в различных предметных областях

 

Применение приложения excel как базы данных

Главная таблица  «Автовокзал»

Первым делом по заданию  курсовой работы нужно было заполнить  таблицу двадцатью записями и  с помощью формул вычислить значения столбцов помеченными звездочкой. Вычисляемыми полями являются «Общая стоимость билетов», «Количество свободных мест», «Начальная станция, отправившая автобус с наибольшим количеством свободных мест», «Время пути», «Название конечной станции, цена билета до которой наименьшая», «Количество автобусов, имеющих наибольшее количество мест». Таблица с вычисленными значениями представлена ниже (Таблица 1).

Столбец «Общая стоимость билетов» вычисляется как произведение стоимости билета и количества мест. Формула для первой записи будет следующей: «=E3*F3». Далее в курсовой работе будем указывать формулу только для первой записи, т.к. значения других строк будут подсчитаны с помощью копирования данной формулы.

Столбец «Количество свободных мест» вычисляется как разность количеством мест и количеством проданных билетов. Формула для вычисления количества свободных мест будет следующей: «=E3-H3». Далее в курсовой работе будем указывать формулу только для первой записи, т.к. значения других строк будут подсчитаны с помощью копирования данной формулы.

В столбец «Начальная станция, отправившая автобус с наибольшим количеством свободных мест» заносится значение из столбца «Начальной станции» если значение из столбца «Количество свободных мест» равно максимальному значению из этого столбца. Формула для вычисления столбца «Начальной станции с максимальным количеством свободных  мест» будет  следующей: «=ЕСЛИ(J3=НАИБОЛЬШИЙ($J$3:$J$22;1);B3;" ")».

В столбец «Время пути» вычисляется как разность датой прибытия на конечную станцию и датой отправки от начальной станции. Формула для вычисления время пути будет следующей: «=M3-L3». Далее в курсовой работе будем указывать формулу только для первой записи, т.к. значения других строк будут подсчитаны с помощью копирования данной формулы.

В столбец «Название конечной станции, цена билета до которой наименьшая» заносится значение из столбца «Название конечной станции», если значение в из столбца «Цена билета» равно минимальному значению из этого столбца. Формула будет следующей: =ЕСЛИ(F3=НАИМЕНЬШИЙ($F$3:$F$22;1);C3;" ")

В столбец «Количество автобусов, имеющих наибольшее количество мест» заносится сумма значений из столбца «Количество мест» если значение из столбца «Количество мест» является максимальным. Формула  будет  следующей: «=СЧЁТЕСЛИ(E3:E22;НАИБОЛЬШИЙ(E3:E22;1))».

 

 

Таблица 1 - "Автовокзал".

Рисунок 1- Титульный лист

 

Сортировка

Одноуровневая сортировка была выполнена по столбцу «Количество мест» в порядке по убыванию. Таблица с одноуровневой сортировкой представлена в таблице 2.

Двухуровневая сортировка была выполнена по столбцу «Конечная станция» в порядке от «А» до «Я», потом по столбцу «Цена билета» в порядке по убыванию. Двухуровневая сортировка представлена в таблице 3.

Трехуровневая сортировка была выполнена по столбцу «Начальная станция» в порядке от «А» до «Я», потом по столбцу «Тип автобуса» в порядке от «А» до «Я» и в конце по столбцу «Количество мест» в порядке возрастания. Трехуровневая сортировка представлена в таблице 4.

 

 

Таблица 2 - Одноуровневая сортировка

Рисунок2-Одноуровневая сортировка

 

Таблица 3 - Двухуровневая сортировка

Рисунок 3-Двухуровневая сортировка

Таблица 4 - Трехуровневая сортировка

Рисунок 4- Трёхуровневая сортировка 

Фильтрация данных

Автофильтр. Была произведена фильтрация данных с помощью автофильтра по столбцу «Тип автобуса». В параметрах автофильтра было указано отображать записи только «Микро» и «Газель».

Расширенный фильтр. Для фильтрации данных с более сложными условиями был использован расширенный фильтр. В качестве исходной таблицы была указана Главная таблица «Автовокзал». Условие расширенного фильтра указано в таблице 5.

Таблица 5 - Условие расширенного фильтра

Рисунок 5- Условие расширенного фильтра

В итоге были отображены только те записи, у которых Тип автобуса – «Икарус» и цена билета равна 186 и количество свободных мест меньше 20. Результат расширенного фильтра представлен таблице 6.

 

 

Таблица 6 - Результат расширенного фильтра

Рисунок 6- Результат расширенного фильтра 

Структурирование

Для группировки и суммирования данных из списка с данными можно создать многоуровневую структуру с числом уровней в пределах восьми, по одному для каждой группы. Каждый внутренний уровень, представленный большим числом символов структуры, отображает подробные данные для соответствующего внешнего уровня. При этом внешний уровень представлен меньшим числом символов структуры (рисунок 1).

Было выполнено структурирование в 3 уровнях.

Первый уровень – Общий итог

Второй уровень – Итог по начальной станции

Третий уровень – Итог по конечной станции

Рисунок 7- Структурирование 

Промежуточные итоги

Microsoft Excel может автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов Microsoft Excel изменяет разметку списка, что позволяет отображать и скрывать строки каждого промежуточного итога.

Перед тем как вставить промежуточные итоги, необходимо отсортировать  список, чтобы сгруппировать строки, по которым нужно подвести итоги. После этого можно подсчитать промежуточные итоги любого столбца, содержащего числа.

Были подведены промежуточные  итоги по главной таблице «Почта России» (Рисунок 2). Для этого была выделена таблица «Почта России» и выполнена операция «Данные -> Структура -> Промежуточные итоги». В качестве операции для подсчета итогов было выбрано суммирование, и указаны два столбца «Начальная станция» и «Конечная станция» для подведения итогов.

Рисунок 8- Промежуточные  итоги 

Консолидация данных

При консолидации данных объединяются значения из нескольких диапазонов данных. Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

Для консолидации данных есть 2 таблицы одинаковой структуры, но с разными значениями, расположенные  на листах «Данные для консолидации 1» и «Данные для консолидации 2». В этих таблицах хранятся итоги  по типам начальной станции. На листе «Консолидация» данные консолидируются по трехмерным ссылкам «'Данные для консолидации1'!$A$1:$P$4» и «'Данные для кончолидации2'!$A$1:$P$4». Вычисляется суммарное значение по столбцу "Количество мест" и сумма по столбцу "Цена билета". Так же была создана связь с исходными данными. В случае если исходные данные изменятся, то будет пересчитаны консолидированные данные. Результаты консолидации отображены в таблице 7.

Таблица 7 - Консолидация данных

Рисунок 9-Консолидация данных

 

Сводная таблица 

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

Сводные таблицы предназначены для удобного просмотра данных больших таблиц, т.к. обычными средствами делать это неудобно, а порой, практически невозможно.

Для построения сводной таблицы  в качестве исходной таблицы была указана таблица «Автовокзал». Названиями строк будут являться следующие столбцы: «Тип автобуса», «Начальная станция» и «Конечная станция». По этим данным будет подсчитываться сумма и отображаться в столбце «Сумма по полю Общая стоимость билетов». Сводная таблица отображена в таблице 8.

Таблица 8 - Сводная таблица

Рисунок 10-Сводная таблица

С помощью сводной таблицы  мы можем увидеть итоги в удобном  виде по типу автобусов, начальной станции и конечной станции, а так же общий итог.

 

Сводная диаграмма

Диаграммы являются важной частью отчетности на основе анализа  данных Microsoft Office Excel, поскольку они позволяют легко и наглядно выполнять сравнение данных, изучать их структуру и изменение.

Была построена сводная  диаграмма, в качестве источника  данных была указана сводная таблица.

Диаграмма отображает данные из сводной таблицы в графическом виде (Рисунок 3).

Рисунок 11-Сводная диаграмма

На диаграмме отчетливо  видно, что максимальная сумма платежа за билет была при поездке на автобусе курганского типа, направляющегося с Троицка до Кургана.

 

Применение приложения Access для решения задач в различных предметных областях

 

Таблицы в Microsoft Access – это основной объект в базе данных, состоящий из полей различного типа, которые содержат однотипные данные.

Запросы

Запрос Access - это возможность выбора определенного типа информации из существующей базы данных. Создание запросов Access может происходить как с помощью мастера, так и вручную.

В моей базе данных использовались 2 типа запросов: простой запрос, и запрос с параметром.

Простой запрос. Позволяет получить все записи из таблицы «Автовокзал».

Рисунок 13-Простой запрос 

Запрос с параметром.  Данный запрос возвращает записи из таблицы «Автовокзал» только с той начальной станцией, которая была указана в параметре.

 

Рисунок 14- Запрос с параметром

 

Отчеты

Отчет – это форматированное представление данных, которое выводится на экран, в печать или файл. Они позволяют извлечь из базы нужные сведения и представить их в виде, удобном для восприятия, а также предоставляют широкие возможности для обобщения и анализа данных.