Экранные формы для работы с базой данных

ЛАБОРАТОРНАЯ РАБОТА №2

РАБОТА С БАЗАМИ ДАННЫХ В MS EXCEL

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

Теоретическое обоснование:

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

- база данных ГИББД с информацией об автомобилях и их владельцах;

- база данных кадрового состава организации;

- база данных аэрофлота;

- база данных библиотечного фонда.

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

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

Таким образом, база данных (БД) - это совокупность взаимосвязанных данных, хранящихся на внешних носителях информации и обрабатываемых с помощью системы управления.

Данные, организованные в Excel в виде таблиц или списка, также часто называют базой данных. Так, рабочий лист, представленный на рис.2.1, и содержащий сведения об улицах города – это база данных.

Рисунок 2.1 – Пример базы данных, созданной в Excel

Каждая строка таблиц базы данных называется запись. Запись состоит из взаимосвязанных полей, которым соответствуют столбцы таблицы. Каждое поле имеет имя.

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

Размер база данных Excel ограничен числом строк и столбцов на рабочем листе Excel.

При создании базы данных в Excel необходимо соблюдать следующие правила:

- пустая строка указывает на окончание базы данных;

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

Методика и порядок выполнения работы:

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

Под сортировкойэлементов понимается процесс упорядочения элементов по определенному правилу.

Различают одноуровневые и многоуровневые сортировки по возрастанию или по убыванию.

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

Таблица 2.1 – Примеры сортировок по возрастанию и по убыванию

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

В таблице базы данных Канцелярские товары, изображенной на рисунке 2.2, произведена простая сортировка по возрастанию по полю Наименование товара.

Рисунок 2.2 –  Таблица базы данных «Канцелярские товары»

Многоуровневая сортировка-сортировка сразу по нескольким полям. Выполнить одноуровневую сортировку по первому полю базы данных можно следующим образом:

1)        выделить все записи базы данных, которые необходимо отсортиро­вать;

2)        для сортировки по возрастанию щелкнуть по пиктограмме
 Сортировка по возрастанию, либо по пиктограмме Сортировка по убыванию - для сортировки по убыванию.

Многоуровневую сортировку, либо одноуровневую сортировку по ука­занному полю можно осуществить следующим образом:

1) выделить всю таблицу базы;

2) выполнить команду Данные ►Сортировка. В результате откроется диалоговое окно Сортировка диапазона, изображенное на рис.2.3;

3) внизу данного диалогового окна установить индикатор того, как идентифицировать поля: по подписям, либо по обозначениям столбцов листа;

4) в поле Сортировать по…указать наименование поля базы данных для первого уровня сортировки и установить индикатор того, как сортировать: по возрастанию или по убыванию;

5) в поле Затем по…указать наименование поля базы данных для вто­рого уровня сортировки и установить индикатор того, как сортировать;

6) в поле В последнюю очередь по …указать наименование поля базы данных для третьего уровня сортировки и установить индикатор того, как сор­тировать;

Рисунок 2.3 – Диалоговое окно «Сортировка диапазона»

7) нажав кнопку Параметрыданного диалоговое окна, можно установить в новом диалоговом окне (рис.2.4) порядок сортировки по первому ключу, учет регистра и направление сортировки. Нажать кнопку ОК.

Рисунок 2.4 – Диалоговое окно «Параметры сортировки»

Процесс осуществления многоуровневой сортировки поясним на примере.

Пример. Пусть необходимо провести многоуровневую сортировку по возрастанию базы данных «Оптовый склад» (таблица 2.2) сначала по полю Наименование продукции, затем - по полю Количество.

Если разложить многоуровневую сортировку на шаги, то это будет выглядеть следующим образом:

1 шаг– Сортировка по полю Наименование продукции. Результат 1 шага сортировки представлен в таблице 2.3.

Таблица 2.2 – База данных Оптовый склад

Таблица 2.3 – База данных Оптовый склад, отсортированная по полю Наименование продукции

2 шаг– Сортировка по полю Количество. Результат 2 шага сортировки представлен в таблице 2.4.

Таблица 2.4 – База данных Оптовый склад, отсортированная сначала по полю Наименование продукции, затем по полю Количество

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

Для того, чтобы выбрать записи, удовлетворяющие определенному ус­ловию, и временно скрыть все остальные записи, используется фильтр. Фильтр задают, вводя условия фильтрации.

В Excel различают автофильтр и расширенный фильтр.

Для вызова автофильтранеобходимо выполнить следующее:

1) выделить всю таблицу или часть таблицы с именами полей;

2) выполнить команду Данные ►Фильтр ►Автофильтр. В результате рядом с именами полей появятся кнопки выпадающего списка  , например, как показано на рис. 2.5.

Рисунок 2.5 – Активизация автофильтра

Каждый из таких списков содержит условия фильтрация для конкретного поля.

3) задать для какого-либо поля условие фильтрации, выбрав его из предложенного списка условий.

Рисунок 2.6 – Диалоговое окно Пользовательский автофильтр

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

Чтобы выключить автофильтр, достаточно опять выполнить команду Данные ►Фильтр ►Автофильтр.

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

1) скопировать имена полей выше таблицы в свободную часть для задания блока критериев, например, как показано на рис.2.7;

2) в блоке критериев задать критерии (условия) фильтрации. На рис.2.7 это условие одно - для поля Стоимость всего, оно указано в ячейке Е3;

Рисунок 2.7 – Блок критериев и исходная таблица базы данных «Канцелярские товары»

3) выполнить команду Данные ► Фильтр ► Расширенный фильтр. В результате откроется диалоговое окно Расширенный фильтр, показанное на ри­с.2.8;

Рисунок 2.8 – Диалоговое окно «Расширенный фильтр»

4) в поле Исходный диапазонследует указать диапазон исходной таблицы, в поле Диапазон условий– диапазон блока критериев. Если необходимо результат фильтрации поместить в другое место, то в поле Поместить результат в диапазонследует указать куда. Если установить флажок в поле Только уникальные записи, то повторяющиеся записи не будут показываться.

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

Рисунок 2.9 – Результат фильтрации базы данных «Канцелярские товары»

Экранные формы для работы с базой данных

Экранные формы позволяют проводить работу с базой данных (редакти­ровать базу, добавлять новые поля и т.д.), при этом, делают эту работу более простой и наглядной.

Для вызова экранной формы необходимо осуществить следующие дей­ствия:

1) выделить таблицу вместе с именами полей;

2) выполнить команду Данные ► Форма. В результате на экране поя­вится диалоговое окно (рис.2.10) с экранной формой для работы с базой данных. Данное диалоговое окно позволяет редактировать имеющиеся записи, добавлять новые записи, удалять записи и т.д. Кроме того, если выбрать пункт Критерии, в качестве критерия задать любое имеющееся значение поля и на­жать ввод, будет осуществлен переход к нужной записи.

Рисунок 2.10 – Диалоговое окно с экранной формой для работы с базой данных

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

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

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

Рисунок 2.11 – Электронная база данных «Канцелярские товары»

Рисунок 2.12 – База данных «Канцелярские товары»с промежуточными итогами

Для того, чтобы подвести ряд промежуточных итогов автоматически необходимо выполнить следующие действия:

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

2. выделить всю базу данных, включая наименования полей;

3. выполнить команду Данные ► Итоги. Откроется диалоговое окно Промежуточные итоги, показанное на рис.2.13.

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

Рисунок 2.13 – Диалоговое окно «Промежуточные итоги»

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

2. в третьем поле указывается столбец, по которому считаются промежуточные итоги, например, Стоимость. Можно также установить три индикатора внизу окна в поля: - заменить текущие итоги; - конец страницы между группами; - итоги под данными. Нажать ОК;

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

Цифры в левом верхнем углу таблицы (рис.2.13) позволяют показывать различные элементы ее структуры.

Если щелкнуть по цифре [1], то в таблице будет отображен только общий итог (рис. 2.14).

Рисунок 2.14 – Таблица с общим итогом

Если щелкнуть по цифре [2], то в таблице будут отображены итоги по группам (рис. 2.15).

Рисунок 2.15 – Таблица с итогами по группам и общим итогом

И данные, и итоги выводятся при щелчке по кнопке [3] (рис.2.15). Этим же целям служат находящиеся слева от таблицы плюсы [+] и минусы [-] (рис.2.15).

Контрольные вопросы

1. Что понимается под базой данных?

2. Что такое запись и поле базы данных?

3. Приведите пример любой базы данных, перечислите ее поля?

4. Какие действия можно осуществлять с базой данных?

5. Что понимается под сортировкой базы данных?

6. Какие виды и способы сортировки существуют?

7. Для чего используется фильтр в Excel?

8. Как установить автофильтр на поля базы данных?

9. Как создать расширенный фильтр базы данных?

10. Какие действия можно выполнять с базой данных с помощью экранных форм?

11. Каким образом можно автоматизировать процесс подведения некоторых итогов по базе данных?