Файл: методичка_EXCEL_КОНОНОВА.docx

2.3 ЛАБОРАТОРНАЯ РАБОТА № 3

«СОЗДАНИЕ ТАБЛИЧНОЙ БАЗЫ ДАННЫХ С ПОМОЩЬЮ Microsoft EXCEL (Ч. I)»

ЦЕЛЬ РАБОТЫ: научиться создавать, структурировать и производить отбор данных в простой базе данных с помощью табличного процессора Ms. Excel.

ПРИБОРЫ И ОБОРУДОВАНИЕ: Pentium II и выше, Microsoft Excel 2003, 2007.

ЛИТЕРАТУРА:

1. Гончаров А. Excel 7.0 в примерах / А. Гончаров. - СПб: Питер, 2006.-256с.

2. Горев А., Ахаян Р., Макашарипов С. Эффективная работа с СУБД / А.Горев, Р. Ахаян, С. Макашарипов.- СПб: Питер, 2007.-704с.

3. Кэмпбелл М. Excel / М. Кэмпбелл. - М.: БИНОМ, 2006.-384с.

4. Наумов А.Н., Вендров А.М., Иванов В. К. и др. Системы управления базами данных и знаний / под ред. А.Н. Наумова. - М.: Финансы и статистика, 2001.-80с.

5. Николь Н., Ральф А. Электронные таблицы Excel 7.0 для квалифицированных пользователей / Н. Николь, А. Ральф - М.: ЭКОМ, 2006.-304с.

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ:

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

Первый этап в создании таблицы базы данных - планирование или проектирование. При этом необходимо учесть следующие моменты:

• какие поля должны содержать таблицы базы данных?

• какими должны быть имена полей? Имена должны описывать содержащуюся в поле информацию и в то же время быть максимально короткими;

• как должны быть организованы поля?

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

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

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

Таблица 1. Структура таблицы «Студент»

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

• каждая запись должна содержать не менее 7 полей;

• имена каждого поля должны быть уникальны.

Основные операции, которые выполняют СУБД при работе с табличной базой данных:

• добавить запись в табличную базу данных;

• удалить запись из табличной базы данных;

• изменить имеющуюся запись;

• найти запись по значениям нескольких полей;

Ввод данных в таблицу

Задание заголовка.

Выполнив пункт ВЫЗОВА приложения Microsoft Office – Ms Excel, вы имеете для работы новую рабочую книгу, в которой создаете табличную базу данных.

Одна из строк таблицы резервируется для названий полей. Это обычно строка из заголовка таблицы. Заголовок состоит из двух частей: общего названия и строки с названиями полей (шапки таблицы). В ячейку A1 введем общий заголовок - «Список студентов». В следующей строке введем имена полей в ячейки:

A2 - Номер;

B2 - Фамилия;

C2 - Имя;

D2 - Отчество;

E2 - Пол;

F2 - Дата рождения;

G2 - Группа.

Таким образом, заголовок списка готов. Можно приступить к заполнению таблицы.

Ввод данных в таблицу

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

• нажав клавишу ввода;

• щелкнув мышью на другой ячейке;

• покинув текущую ячейку с помощью клавиш управления курсором.

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

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

Если при вводе текста произошла ошибка, то установите курсор на ту ячейку, в которой сделана ошибка и исправьте информацию.

Оформление заголовка

Вставим после общего названия пустую строку. Это можно сделать следующим образом: установить курсор на любой ячейке той строки, перед которой надо сделать вставку (в нашем случае на второй строке) и одновременно нажать три клавиши: Ctrl, Shift, «+», либо использовать команду ВСТАВКА, Строка. В первом случае на экране появится меню, в котором надо нажать кнопку «строка» и кнопку ОК.

Для удаления строки достаточно одновременно нажать Ctrl и «-».

Появится аналогичное меню и надо нажать те же кнопки. Для удаления строки можно использовать команду ПРАВКА, Удалить.

Отформатируем заголовок. Выделим все заголовки полей. Для этого надо проделать следующие действия:

1. Установить курсор в ячейку, начиная с которой выполняется выделение.

2. Нажать левую кнопку мыши.

3. Протащить курсор, закрашивая область выделения.

После выделения блока ячеек, выполнить команду ФОРМАТ, Ячейки. Эта команда имеет несколько вкладок. Выбрать вкладку Выравнивание и выбрать опции Горизонтальное - по центру.

Просмотр и редактирование данных

Этот режим можно использовать также для ввода данных. Выделить ячейку заголовка и выполнить команду ДАННЫЕ, Форма. Excel создает удобный шаблон или форму для ввода данных. Открывается диалог задания форм, имеющий заголовок, совпадающий с именем листа, в котором можно вводить данные в табличную базу данных в соответствии с заданным заголовком. С помощью полосы прокрутки в середине можно перемещаться по таблице. Кнопки справа активизируют следующие функции:

• Предыдущая - перемещение на одну запись назад в базе данных;

• Следующая - перемещение на одну запись вперед в базе данных;

• Закрыть - закрывает диалог.

Найдя нужную запись, можно внести необходимые изменения.

Ввод данных

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

Удаление записи из базы данных

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

Если запись была удалена по ошибке, то ее можно восстановить, нажав кнопку Восстановить.

Поиск нужной записи в базе данных

Нажав кнопку Критерии, получаем доступ к окну диалога задания критериев поиска. Можно задать столько критериев для поиска, сколько существует полей. Программа будет искать запись в базе данных, которая удовлетворяет всем введенным критериям. Условие поиска может состоять из одного или нескольких логических выражений. В последнем случае выражения соединяются логическими операторами И или ИЛИ. Применительно к полям, содержащим текстовые данные, имеют смысл два оператора: «равно» и «не равно». Операторы, в которые входят знаки «больше» или «меньше», применимы только к числовым данным. Если поиск был успешным, а в базе данных существует несколько строк, удовлетворяющих критериям поиска, то для их просмотра можно использовать кнопки Предыдущая и Следующая.

Сортировка записей базы данных

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

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

• от меньших чисел к большим;

• по алфавиту;

• в хронологическом порядке дат;

Сортировка по убыванию осуществляется:

• от больших чисел к меньшим;

• в обратном алфавитном порядке;

• в обратном хронологическом порядке.

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

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

Проверяйте результат сортировки. Если получили не то, что хотели, используйте команду ПРАВКА, Отменить для восстановления исходного состояния базы данных.

Фильтрация (выборка) записей базы данных

Команда ДАННЫЕ , Фильтр обеспечивает выборку подмножества данных из табличной базы данных. Фильтр представляет собой конструкцию, предназначенную для отбора тех строк, которые удовлетворяют заданному условию и временному сокрытию остальных. Excel предоставляет две команды : Автофильтр, для простых условий отбора и Расширенный фильтр, для более сложных критериев.

Автофильтрация

Команда ДАННЫЕ, Фильтр, Автофильтр для каждого поля строит список значений , который используется для задания условий фильтрации.

По каждому полю можно указать:

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

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

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

• все - выбираются все записи без ограничения.

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

Расширенный фильтр

Команда ДАННЫЕ , Фильтр , Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей базы данных:

• критерий сравнения;

• вычисляемый критерий.

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

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

1-й этап - формирование интервала критериев;

2-й этап - фильтрация записей списка.

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

Вычисляемый критерий представляет собой формулу, записанную в строке условий интервала критериев, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием адресов ячеек, встроенных функций, констант различных типов (число, текст, дата, логическая константа), операторов отношений.

Для снятия действий условий фильтрации выполняется команда ДАННЫЕ, Фильтр, Показать все.

ВЫПОЛНИТЕ САМОСТОЯТЕЛЬНО:

Задание по созданию табличной базы данных.

При выполнении самостоятельной работы необходимо:

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

• ввести не менее 15 записей базы данных, выполнив все действия по созданию и форматированию заголовка;

• просмотреть содержимое базы данных, используя команду ДАННЫЕ, Форма. Изменить несколько записей в базе данных и одну запись удалить;

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

• отсортировать созданную базу данных, задав не менее двух ключей сортировки;

• установите курсор в область базы данных и выполните команду ДАННЫЕ, Фильтр, Автофильтр. Задайте не менее двух условий отбора. Отмените результат Автофильтрации.

Варианты заданий (выбор варианта осуществляется по номеру последней цифры в зачетной книжке или в студенческом билете):

1. База данных о работе магазина по продаже джинсов.

2. База данных о работе фирмы по продаже недвижимости.

3. База данных о студенческой успеваемости.

4. База данных о работе коммерческого киоска.

5. База данных о продажах автомобилей.

6. База данных об абитуриентах.

7. База данных о работе сбербанка.

8. База данных о работе отдела кадров.

9. База данных о студенческом расписании.

10. База данных о работе библиотеки.

11. База данных о продаже театральных билетов.

12. База данных о работе налоговой инспекции.

13. База данных о работе бухгалтерии.

14. База данных о работе поликлиники.

15. База данных о торговле компьютерами.

16. База данных о торговле газетами.

17. База данных о работе ателье.

18. База данных о киоске канцтоваров.

19. База данных о клубе дрессировки собак.

20. База данных о книготорговой фирме.

21. База данных о работе парикмахерской.

22. База данных о работе ресторана.

23. База данных о яхт-клубе.

24. База данных о мебельном магазине.

25. База данных об автозаправочной станции.

КОНТРОЛЬНЫЕ ВОПРОСЫ:

1. Дайте определение базы данных.

2. Что такое система управления базами данных (СУБД)?

3. Где могут быть использованы базы данных?

4. Какие функции должна обеспечивать СУБД?

5. Дайте определение следующим терминам: запись, поле.

6. Как можно классифицировать СУБД?

7. Дайте характеристику иерархической, сетевой и реляционной БД.

8. В чем преимущества реляционной базы данных?

9. Что такое “электронная таблица”? Какой еще термин применяют к этому классу программ?

10. Почему информационная структура электронных таблиц не статическая, а динамическая?