Лабораторная работа №4. «Работа с базой данных».

Исходную базу данных скопируйте на шесть рабочих листов. Назовите их Задание 1, Задание 2, и т.д. Файл назовите своей фамилией.

На каждом листе напишите формулировку задания (можно скопировать из этой методички).

ЗАДАНИЕ 1. Провести двухуровневую сортировку БД, используя критерии: первичный - по убыванию количества детей; вторичный - по алфавиту групп семейного положения.

Сценарий сортировки

1. Выделение диапазона ячеек, занимаемого исходной базой данных, перетаскиванием мышью или клавишным аккордом [Shift]+[Ctrl]+[End] (с предварительным позиционированием ячейки, содержащей имя поля Фамилия).
2. Выбор пунктов инструментального меню Данные/Сортировка...
3. Заполнение диалогового окна Сортировка диапазона согласно рис. 1.

рис. 1

1. Визуальный контроль результатов сортировки.
   (Ниже на рис. 2 приведен начальный фрагмент БД после сортировки).

рис. 2

ЗАДАНИЕ 2. Используя операцию автофильтра, провести выборку записей из БД согласно критерию - фамилии, состоящие из трех или четырех букв.

Сценарий запроса к БД

1. Выделение диапазона ячеек, занимаемого исходной базой данных.
2. Выбор пунктов инструментального меню Данные/Фильтр/Автофильтр.
   (Ниже на рис. 3 приведен начальный фрагмент БД после включения автофильтра с преобразованием всех наименований полей в раскрывающиеся списки).

рис. 3

1. Выбор в раскрывающемся списке поля Фамилия позиции (Условие...)
2. Заполнение диалогового окна Пользовательский автофильтр согласно рис.4.

рис. 4

1. Визуальный контроль результатов фильтрации.

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

Сценарий запроса к БД

1. Выбор в раскрывающемся списке поля Пол позиции ж. При этом используется автофильтр, установленный в задании 5.
2. Выбор в раскрывающемся списке поля Количество детей позиции (Условие...)
3. Заполнение диалогового окна Пользовательский автофильтр согласно рис.5.

рис. 5

1. Визуальный контроль результатов фильтрации.

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

Сценарий запроса к БД

1. Резервирование диапазона ячеек для размещения блока критериев посредством вставки четырех строк над таблицей исходной БД.
   
   
   
2. Копирование строки с наименованиями полей БД в первую строку блока критериев, например, с использованием папки обмена.
3. Внесение во вторую строку блока критериев условий выборки записей, как это изображено на рис. 6.
   

рис. 6

1. Выделение диапазона ячеек исходной БД.
2. Выбор в инструментальном меню пунктов Данные/Фильтр/Расширенный фильтр...
3. Заполнение диалогового окна Расширенный фильтр согласно рис. 7.

рис. 7

1. Визуальный контроль результатов фильтрации.

ЗАДАНИЕ 5. Реализовать запрос к БД, используя функции категории Работа с базой данных. Привести расчетную формулу для подсчета числа работников, состоящих в браке и не имеющих детей.

Сценарий запроса к БД

1. Заполнение блока критериев новыми условиями выборки записей, как это показано на рис. 8.

рис. 8

1. Ввод в ячейку A86 (под таблицей исходной БД) текста пояснения планируемого результата запроса, а в ячейку E86 с помощью Мастера функций - соответствующей расчетной формулы. Выбор в процессе диалога с Мастером функций категории функций Работа с базой данных и имени функции БСЧЁТ, как это показано на рис. 9:

рис. 9

1. Заполнение диалогового окна с указанием трех аргументов функции согласно рис. 10.

рис. 10

1. Завершение диалога с Мастером функций, в результате чего в ячейку E86 должна быть введена формула: БСЧЁТ(A5:I84;G5;H1:I3), где G5 - ячейка имени поля с арифметическими значениями- окладами, используемыми для подсчета числа записей, удовлетворяющих условиям фильтрации.
2. Наблюдение в ячейке E86 результата запроса к БД.

ЗАДАНИЕ 6. Реализовать перекрестный запрос к БД, используя операцию построения сводной таблицы: минимальные оклады по каждой группе семейного положения отдельно для женщин и мужчин.

Сценарий запроса к БД

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

2.Реализация первого шага диалога с Мастером сводных таблиц - выбор варианта Создать таблицу на основе данных, находящихся в списке или базе данных

Microsoft Excel.

3.Реализация второго шага диалога с Мастером сводных таблиц - выделение диапазона ячеек, занимаемых БД.

4.Реализация третьего шага диалога с Мастером сводных таблиц (см. рис. 12):

· Щелкните кнопку Макет

· перетаскивание имени поля Семейное положение в область строк сводной таблицы;

· перетаскивание имени поля Пол в область столбцов сводной таблицы;

· перетаскивание имени поля Оклад в область данных сводной таблицы;

· раскрытие списка вариантов вычислений в области данных двойным щелчком в соответствующем участке области данных и выбор позиции Минимум, как это показано на рис.11.

рис. 11

5.Перед завершением третьего шага диалога с Мастером сводных таблиц - наблюдение структуры создаваемой таблицы, как это показано на рис. 12.

рис. 12

6. Выбор варианта Поместить таблицу в новый лист.

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

рис. 13

САМОСТОЯТЕЛЬНОЕ ЗАДАНИЕ ПО ЭЛЕКТРОННЫМ ТАБЛИЦАМ EXCEL

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

ЗАДАНИЕ 1. Провести двухуровневую сортировку БД согласно критериям в таблице 1. При этом на первом этапе провести сортировку согласно первичному критерию, а на втором этапе для записей, имеющих одинаковые значения первичного критерия, предусмотреть сортировку согласно вторичному критерию. Подробно описать действия пользователя с представлением в виде рисунка диалогового окна Сортировка диапазона (используйте комбинацию клавиш ALT+Print Screen для того, чтобы «сфотографировать» диалоговое)

Таблица 1

Варианты индивидуальных заданий

ЗАДАНИЕ 2. Используя операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице 2 критериям фильтрации. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунка диалоговое окно пользовательского автофильтра.

Таблица 2

Варианты индивидуальных заданий

ЗАДАНИЕ 3. Используя многошаговую операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице 3 критериям фильтрации. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунков диалоговые окна применяемых пользовательских автофильтров

Таблица 3

Варианты индивидуальных заданий

ЗАДАНИЕ 4. Используя операцию расширенного фильтра, выполнить одношаговую фильтрацию согласно критериям задания 3.

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

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

Таблица 4

Варианты индивидуальных заданий

ЗАДАНИЕ 6. Реализовать перекрестный запрос к БД, используя операцию построения сводной таблицы. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунка диалоговое окно шага 3 из 4 Мастера сводных таблиц

Таблица 5

Варианты индивидуальных заданий

Варианты индивидуальных заданий