Лабораторная работа №8

Упражнение № 1

Цель: Знакомство с использованием MS Query для работы с внешними базами данных.

1.Работа со списками Excel как с "базой данных".

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

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

Критерии для выборки следующих данных:

• лица женского пола, (критерий №1);

• лица мужского пола с суммой выплат больше 500 руб. (критерий №2);

• лица мужского пола с суммой выплат меньше 400 руб. (критерий №3);

• мужчины - референты и водители с выплатой больше 250 и меньше 500, а также женщины - менеджеры с выплатой больше 500 и меньше 700 (критерий №4).

1.3.Пользуясь режимом "Фильтр"-"Расширенный фильтр" выполнить выборку данных в соответствии с указанными критериями и разместить результат выборки в диапазоне ячеек справа от критериев (рис.П.16.1).

Рис.П.16.1

2.Импорт текстовых файлов.

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

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

2.3.Запустить текстовый процессор Word и загрузить в него сохраненный файл. Просмотреть его структуру.

2.4.Закрыть текстовый файл с данными. Деактивировать текстовый процессор Word.

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

2.6.Просмотреть загруженную таблицу, сравнить её с исходной таблицей. Обратить внимание на количество листов в созданной книге.

3.Использование MS Query для доступа к внешним базам данных.

3.1.Проверить наличие в Вашей папке файла, содержащего базу данных (название файла уточнить у преподавателя (DB_Access.mdb)).

3.2.Перейти на новый лист Excel. Запустить программу взаимодействия с базами данных MS Query, используя пункты меню "Данные"-"Внешние данные"-"Создать запрос".

3.2.1.Выбрать в качестве источника данных файлы, создаваемые MS Access 97 и отказаться от использования мастера запросов (рис.П.16.2).

Рис.П.16.2

3.2.2.В открывшемся диалоговом окне "Select Database" (рис.П.16.3) найти и выбрать файл DB_Access.mdb, содержащий базу данных.

Рис.П.16.3

3.2.3.В окне "Добавление таблицы" выбрать один файл с именем "Таблица1" для дальнейшего использования.

3.2.4.Научиться добавлять, удалять и перемещать столбцы (поля базы данных) в области данных. Для размещения столбцов использовать три метода: непосредственно из таблицы, в области данных, через меню. Разместить в области данных столбцы-поля в следующем порядке: "№", "Фамилия", "Пол", "Должность", "Оклад", "Телефон", "Год рожд".

3.2.5.Используя режим "Записи"-"Изменить столбец", создать заголовки столбцов, отличающиеся от наименований полей в базе данных, как показано на рис.П.16.4.

3.2.6.Используя команду "Вид"-"Условия", изменить внешний вид запроса так, чтобы в нем присутствовали три области: области таблиц (исходные данные), область критериев (где формулируется запрос) и область результатов (рис.П.16.4.).

3.2.7.Убрать (скрыть) столбец, соответствующий полю "№".

3.2.8.Извлечь все записи из таблицы базы данных, выполнив команду "Файл"-"Вернуть данные в Microsoft Excel", разместив их, начиная с ячейки А1 текущего листа Excel.

3.2.9.Отсортировать извлеченные данные в соответствии со следующими правилами:

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

• в порядке убывания по году рождения;

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

3.2.10.Находясь в области полученных данных, вернуться в MS Query, используя команду "Данные"-"Внешние данные"-"Изменить запрос".

3.2.11.Научиться выполнять переходы к записям (строкам) с произвольным номером. Просмотреть первую, последнюю и 8-ю записи. Внести исправления в номера телефонов первой, последней и 2-й записей, предварительно указав возможность правки записей ("Записи"-"Разрешить правку").

Рис.П.16.4

3.2.12.Вернуть данные в рабочий лист Excel.

3.2.13.Изменить свойства диапазона данных, указав включение номеров строк (рис.П.16.5).

4.Формирование критериев для выбора данных из внешней базы данных.

4.1.Формирование простых критериев.

4.1.1.На новом листе Excel выполнить запрос, используя область критериев ("Вид"-"Условия"), в которой указать в качестве поля для определения критерия поле "Должность", а в качестве значения поля - менеджер.

4.1.2.Выполнить выборку данных и просмотреть результат в области значений.

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

4.1.4.Записать новый критерий для выбора данных о лицах мужского пола. Для формирования критерия воспользоваться режимом диалогового задания критерия. Последовательно открывая диалоговые окна"Добавление условия" и "Выбор значений", записать название поля (Пол) и значение поля (м). Пример записи такого критерия представлен на рис.П.16.6.

4.1.5.Выполнить запрос и проверить полученный результат.

4.1.6.Сохранить созданный запрос под именем "Запрос1".

4.2.Критерии, содержащие несколько логических условий (связанные критерии).

4.2.1.Сформировать критерий для выборки данных о лицах, фамилии которых начинаются на "И" или "С".

4.2.2.Сформировать критерий для выборки данных о менеджерах - мужчинах) и секретарях.

4.2.3.Сохранить созданный запрос под именем "Запрос2".