Практическая работа №6 работа с базой данных

База данных – это упорядоченный набор данных, содержащий строку заголовков (имена полей) и строки данных, которые могут быть числовыми и текстовыми. В электронных таблицах базы данных часто называют списками. Максимальный размер списка не может превышать размер одного рабочего листа (не более 256 колонок и не более 65535 строк). В базах данных колонки принято называть полями, а строки – записями. Для того чтобы работать с таблицей как с базой данных, она должна отвечать некоторым требованиям:

• база данных обязательно должна содержать заголовки столбцов – имена полей, размещенные в одной строке (верхняя строка списка);

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

• в списке не допускается наличие пустых строк, колонок и ячеек;

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

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

1. Загрузите программу Excel.

2. В новой книге разработайте приведенную ниже таблицу.

3. Поле Дата закупки заполните значениями дат по рабочим дням месяца, воспользуйтесь командой Правка/Заполнить/Прогрессия.

4. Значения в колонке Стоимость рассчитайте по формуле Цена * Количество.

5. Сохраните таблицу под именем Software.xls

Примечание: В Excel существует возможность автоматически включать новую информацию в область базы данных. Для этого диапазону ячеек списка можно присвоить имя База_данных с помощью команды меню Вставка/Имя/Присвоить. Если щелкнуть на кнопке списка справа от адреса текущей ячейки в строке ввода, в списке Имя появляется имя диапазона База_данных.

6. Задайте для исходного списка имя База_данных.

7. Создайте форму для добавления данных в список:

• выполните команду Данные/Форма, заголовок окна формы должен совпадать с именем рабочего листа;

• щелкните на кнопке <Добавить>, в правом верхнем углу появится надпись Новая запись;

• заполните поля формы новой информацией из приведенной ниже таблицы. Для перехода к следующей новой записи повторно щелкните на кнопке <Добавить>;

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

8. Закройте форму кнопкой <Закрыть>.

9. Убедитесь в том, что вновь введенные записи также учитываются в диапазоне База_данных. Для этого откройте список в поле Имя слева от строки ввода, щелкните на имени База_данных, выделенная область содержит старые и новые данные.

СОРТИРОВКА СПИСКА

10. Расположите записи списка в порядке убывания стоимости заказа. Для этого установите курсор в любую ячейку поля Стоимость и воспользуйтесь кнопкой Сортировка по возрастанию на ПИ Стандартная.

11. По аналогии отсортируйте БД по убыванию значений количества единиц товара, используя кнопку Сортировка по убыванию.

12. Выполните сортировку данных в списке по двум полям – в алфавитном порядке значений поля Тип программы, а для одинаковых типов программ расположите данные по возрастанию Даты закупки. Выполните следующие действия:

• установите курсор в любую ячейку диапазона База_данных (в любую ячейку таблицы);

• выполните команду Данные/Сортировка;

• в диалоге Сортировка диапазона в поле Сортировать по выберите из списка Тип программы, по возрастанию, в поле Затем по – Дата закупки, по убыванию, остальные параметры сортировки оставьте по умолчанию, <ОК>.

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

13. По аналогии выполните сортировку списка по двум полям – по убыванию Количества, а внутри в алфавитном порядке Наименований.

ПОИСК И ВЫБОРКА ЗАПИСЕЙ В РЕЖИМЕ АВТОФИЛЬТР

В Excel поиск информации осуществляется с помощью фильтров двух типов Автофильтр и Расширенный фильтр.

14. Выберите для просмотра из списка информацию об обучающих программных продуктах, используя Автофильтр:

• установите курсор в любую ячейку диапазона База_данных;

• выполните команду Данные/Фильтр/Автофильтр, в ячейках с именами полей справа появятся кнопки списков со стрелками;

• раскройте список поля Тип программы, выберите значение обучение, в результате на экране останутся записи об обучающих программах;

Внимание! Если Автофильтр включен, стрелки на кнопках Автофильтра окрашиваются в синий цвет. При необходимости можно выполнять фильтрацию данных по нескольким полям базы данных.

• отмените фильтрацию повторно выполнив команду Данные/Фильтр/Автофильтр или выберите в списке Автофильтра поля Тип программы значение Все (в этом случае режим Автофильтр остается активным и можно повторить фильтрацию с другим условием).

15. Используя Автофильтр выберите для просмотра информацию из базы данных о компьютерных играх с ценой 250. Для этого установите Автофильтр в двух полях Тип программы – игры и Цена - 250.

16. Отмените режим Автофильтр.

Внимание! Если стандартных условий Автофильтра не достаточно можно создать собственный Пользовательский Автофильтр. Для его создания используется диалоговое окно Пользовательский Автофильтр, которое открывается командой Условие… из раскрывающегося списка кнопки Автофильтра в поле, по которому осуществляется поиск.

17. Выберите из базы данных информацию о компьютерных программах с ценой от 200р. до 1000р. Выполните следующие действия:

• выделите все значения в поле Цена;

• активизируйте режим Автофильтр;

• в списке Автофильтра поля Цена выберите значение Условие…;

• в диалоговом окне Пользовательский Автофильтр в строке Цена выберите условие больше или равно, рядом в пустой строке введите значение цены 200;

• укажите оператор логического объединения И, так как значение цены должно удовлетворять двум условиям одновременно;

• во втором блоке условий выберите из списка условие меньше или равно, рядом ведите значение 1000, <ОК>.

18. В результате в списке останутся только записи, отвечающие условиям цены:

19. Отмените режим Автофильтр.

20. Скопируйте базу данных на второй лист текущей книги.

21. Самостоятельно, используя Автофильтр, выберите из базы данных в просмотр:

• информацию об антивирусных программах, приобретенных фирмой до 15 февраля 2006г. (на первом листе);

• записи о программных продуктах для абитуриентов (на втором листе).

22. Сдайте работу преподавателю.

23. Отмените режим Автофильтр. Сохраните таблицу.

ПОИСК И ВЫБОРКА ДАННЫХ В РЕЖИМЕ

РАСШИРЕННЫЙ ФИЛЬТР

Для решения более сложных задач поиска информации в базе данных используется режим расширенной фильтрации. В отличие от Автофильтра Расширенный фильтр позволяет определить более сложные критерии поиска, устанавливать вычисляемые условия отбора. Расширенный фильтр более гибкий чем Автофильтр, он позволяет копировать результат поиска и выборки записей в другое место таблицы или на новый лист рабочей книги. Однако его использование требует некоторых подготовительных действий. Чтобы использовать Расширенный фильтр нужно сформировать три отдельных блока таблицы: Исходный диапазон, Диапазон условий (критериев), Диапазон вывода. Блоки не должны пересекаться.

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

Диапазон критериев – область рабочего листа, в которую вносятся условия отбора. Диапазон критериев должен содержать не менее двух строк, первая из которых содержит имена полей исходной базы данных. Для правильного выполнения поиска в Диапазоне критериев не должно быть пустых строк. Рекомендуется Диапазон критериев формировать над списком или под ним. Условия отбора вносятся в пустые ячейки Диапазона критериев. Если условия отбора расположены в одной строке, они объединяются логическим оператором И. Условия, расположенные на разных строках, соединяются логическим оператором ИЛИ.

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

24. Используя Расширенный фильтр, выберите из списка записи, соответствующие обучающим программам. Для этого выполните следующие действия:

• для формирования диапазона условий скопируйте из базы данных имя поля Тип программы в ячейку А18, в ячейку А19 введите условие поиска – обучение;

• для формирования блока вывода скопируйте строку с именами полей из исходной базы данных в строку A22:F22;

• установите курсор в любую ячейку области База_данных;

• выполните команду меню Данные/Фильтр/Расширенный фильтр;

• в диалоговом окне Расширенный фильтр установите режим Копировать результат в другое место;

• укажите координаты трех диапазонов: Исходный диапазон – $A$1:$F$14 (заполняется автоматически, если курсор находится в области базы данных), Диапазон условий – $A$18:$A$19, Поместить результат в диапазон - $A$22:$F$30. Для записи координат диапазонов используются абсолютные ссылки на ячейки.

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

• Закройте диалог кнопкой <ОК>. В результате будут отобраны записи, соответствующие типу программ – обучение.

25. На втором листе текущей рабочей книги отберите информацию о компьютерных программах, закупленных до 10 февраля 2006г. с общей стоимостью заказа до 20000 руб:

• сформируйте Диапазон условий, включив в него два поля Дата закупки и Стоимость. Во второй строке Диапазона условий задайте условия поиска: в поле Дата закупки ведите выражение <10.02.06, в поле Стоимость – выражение <20000;

• сформируйте в свободной области рабочего листа Диапазон вывода;

• в диалоге Расширенный фильтр укажите координаты трех диапазонов, выполните поиск;

• проанализируйте полученные результаты.

Использование текстовых условий

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

• на первом листе текущей рабочей книги очистите диапазон ячеек А18:А19 (Диапазон условий), в ячейку А18 скопируйте имя поля Наименование, в ячейку А19 запишите условие <А (русский алфавит);

• очистите Диапазон вывода, оставив в нем строку с именами полей;

• установите курсор в области База_данных в любой ячейке;

• откройте диалог Расширенный фильтр, проверьте правильность указания координат диапазонов, выполните поиск;

• проанализируйте результаты.

Использование вычисляемых условий

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

• добавьте в текущую рабочую книгу дополнительный лист (Лист4) командой Вставка/Лист;

• скопируйте на Лист4 список, содержащий информацию о компьютерных программах – диапазон ячеек А1:F14 с первого листа текущей рабочей книги (Лист1);

• в ячейке Е16 рассчитайте среднее значение цены всех программ по формуле

= СРЗНАЧ (Е2:Е14)

в ячейке слева введите название рассчитанного показателя – средняя цена;

• для формирования Диапазона условий в ячейку А19 введите вычисляемое условие

= E2 > $E$16

где E2 – адрес первой ячейки, содержащей значение цены (ссылка на эту ячейку должна быть относительной), $E$16 – адрес ячейки, содержащей формулу расчета средней цены (ссылка на эту ячейку должна быть абсолютной); в ячейке А18 над вычисляемым условием введите наименование – цена больше средней (эта ячейка может оставаться пустой);

Внимание! В ячейке А19, содержащей вычисляемое условие отобразится логическое значение ИСТИНА (если первая ячейка поля Цена отвечает заданному условию) или логическое значение ЛОЖЬ (если первая ячейка поля Цена не отвечает заданному условию).

• для формирования Диапазона вывода скопируйте строку с именами полей из базы данных в 22-ю строку таблицы А22:F22;

• установите курсор в любую ячейку списка;

• откройте диалог Расширенный фильтр;

• в поле Диапазон условий укажите координаты блока условий $A$18:$A$19 (в Диапазон условий обязательно включается ячейка над вычисляемым условием);

• укажите координаты Диапазона вывода $А$22:$F$30;

• выполните поиск, проанализируйте результаты, сравните полученные результаты с таблицей на рисунке.

28. Сохраните таблицу.

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

30. Сдайте работу преподавателю.