Excel как средство управления базами данных. Сводные таблицы

Белорусский национальный технический  университет

Факультет горного дела и инженерной экологии

Кафедра «Экология»

 

 

 

 

Контрольная работа

 

 

 

Тема: Excel как средство управления базами данных. Сводные таблицы

 

 

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

 

 

 

 

студентки 2 курса заочной формы обучения

 

 

 

 

 

 

 

 

           Руководитель: Петрушкевич Алла Николаевна

 

 

 

 

 

 

 

Минск 2012

 

Часть 1

 

Базы данных в Excel.

 

База  данных (date base) – это совокупность хранимых в памяти компьютера данных, которые отображают состояние некоторой предметной области. Данные взаимосвязаны и специальным образом организованы.

При таком информационном отображении  предметных сред упор делается не на сами объектыи их свойства, а на отношения между ними, что соответствует так называемой реляционной точке зрения на базы данных.

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

Вопросы сбора данных, их хранения, учета  и обработки можно решить, имея систему управления списками. Термин список используется в Excel для обозначения базы данных.

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

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

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

 

Функции и формулы

 

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

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и  списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.

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

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

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

В Excel широко представлены математические функции. Например, вы можете выполнять различные операции с матрицами: умножать, находить обратную, транспонировать.

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

В Excel можно  решать задачи оптимизации и использовать анализ Фурье. В частности, в Excel реализован алгоритм быстрого преобразования Фурье, при помощи которого вы можете построить  амплитудный и фазовый спектр.

 

Сортировка  базы даннях

 

После ввода  данных вам может потребоваться  упорядочить их. Процесс упорядочения записей в базе данных называется сортировкой. Порядок сортировки записей  определяется конкретной задачей. При  сортировке изменяется порядок следования записей в базе данных или таблице. Таким образом происходит изменение базы данных. Вы должны иметь возможность восстановить исходный порядок следования записей. Универсальным средством для этого является введение порядковых номеров записей. В сочетании со средствами Excel по восстановлению данных это полностью защитит вашу базу от потерь при случайных сбоях в работе.

Команда Данные | Сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.

Сортировка  по возрастанию предполагает следующий  порядок:

Числа

Текст, включая  текст с числами (почтовые индексы, номера автомашин)

Логические  значения

Значения  ошибок

Пустые  ячейки

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

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

При сортировке по возрастанию текстовые данные упорядочиваются в алфавитном порядке  от А к Я. Числовые данные упорядочиваются по возрастанию значений от минимального к максимальному. Даты упорядочиваются от наиболее ранней даты к наиболее поздней. При выборе переключателя по убыванию порядок сортировки изменяется на противоположный.

 

Два дополнительных раздела Затем и В последнюю очередь, по позволяют определить порядок вторичной сортировки для записей, в которых имеются совпадающие значения.

Переключатель Идентифицировать поля по указывает, имеется ли в сортируемом списке строка заголовков, которую нужно исключить из сортировки. При наличии в списке меток столбцов выберите переключатель Подписям, в противном случае — Обозначениям столбцов листа.

Окно  диалога содержит кнопку Параметры, в результате нажатия которой открывается окно диалога “Параметры сортировки”. С помощью этого окна вы можете:

Определить  пользовательский порядок сортировки для столбца, указанного в раскрывающемся списке Сортировать по

Сделать сортировку чувствительной к использованию  прописных и строчных букв

Изменить  направление сортировки (вместо сортировки сверху вниз установить сортировку слева  направо)

 

Подведении  итогов в базе даннях

 

Один  из способов обработки и анализа  базы данных состоит в подведении различных итогов. С помощью команды Данные | Итоги можно вставить строки итогов в список, осуществив суммирование данные нужным способом. При вставке строк итогов Excel автоматически помещает в конец списка данных строку общих итогов.

После выполнения команды Данные | Итоги вы можете выполнить следующие операции:

выбрать одну или несколько групп для  автоматического подведения итогов по этим группам

выбрать функцию для подведения итогов

выбрать данные, по которым нужно подвести итоги

Кроме подведения итогов по одному столбцу, автоматическое подведение итогов позволяет:

выводить  одну строку итогов по нескольким столбцам

выводить  многоуровневые, вложенные строки итогов по нескольким столбцам

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

скрывать  или показывать детальные данные в этом списке

 

 

Как создать  базу даннях

 

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

Создание базы данных (БД) начинается с проектирования БД, т.е. с определения  ее структуры: количества полей, их имен, типа каждого поля (символьный, числовой, дата…), длины каждого поля ( максимального  количества символов ), типа данных (исходные, т.е. неизменяемые, или вычисляемые). Возможность использовать вычисляемые поля - основная особенность баз данных вExcel.

База данных создается в обычной  электронной таблице, но с выполнением  таких правил:

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

Создание сводных таблиц на основании. 

Сводная таблица —  это таблица, которая используется для быстрого подведения итогов или  объединения больших объемов  данных. Меняя местами строки и  столбцы, можно создать новые  итоги исходных данных; отображая  разные страницы можно осуществить фильтрацию данных, а также отобразить детальные данные области.

Для создания Сводной  таблицы на вкладке Вставка в группе Таблицы нажмите кнопкуСводная таблица (Рис.1)

 

 

Далее в  появившемся окне в поле Выбрать таблицу или диапазон автоматически выберется вся ваша таблица. Если вы хотите выбрать только часть таблицы для анализа, то нажмите кнопку в конце строки ввода и мышкой выделите нужную часть таблицы. Обратите особое внимание на то, что верхняя строка выделенного диапазона обязательно должна содержать названия столбцов, т.к. она не будет обрабатываться как данные, а определит будущие названия полей по которым будет происходить формирования отчета Сводной таблицы. После выделения нажмите на кнопку в конце строки ввода еще раз. Далее нажмите Ok (Рис.2).

 

 

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

 

 

 

В данном примере сформируется отчет с суммой продаж по городам и по товаром в каждом городе (Рис.4)

 

 

Часть 2

 

Создать базу данных по приведенной  ниже форме

 

Книжный магазин

 

Дата  поступления

Автор

Название книги

№ стелажа

Цена экземпляра

Кол-во экземпляров

Стоимость

 

1.Создаю сводную таблицу

 

2. Создала списки отражающие  стоимость книг различных авторов  за различные периоды времени.

 

3. Внесла изменения  в список ( количество экземпляров) и обновила сводную таблицу.

 

4. Добавила в исходном  списке две записи и обновила  сводную таблицу.

 

 

5. Убрала в исходном  списке две записи и обновила  сводную таблицу.

6. Перестроила таблицу  таким образом, чтобы она отображала  информацию стоимости книг по стеллажам за различные периоды времени.

 

 

7. Сгруппировала графу  «Дата» по месяцам.

 

 

8. Сгруппировала графу  «Дата» по кварталам  и посчитала  количество книг, поступивших в  течение различных кварталов.

 

9.Вывела данные в  области данных в процентах от итогов по всей таблице