Методические указания по выполнению практической работы

Если кнопки Форма эту функцию можно использовать в Office Excel 2007, добавив кнопку Форма на панель быстрого доступа.

Добавление кнопки " Форма" на панель быстрого доступа

1. Щелкните стрелку на панели быстрого доступа и выберите элемент Добавить команды.

2. В поле Выбрать команды из щелкните элемент Все команды.

3. В списке выберите кнопку Форма и щелкните элемент Добавить.

в «Форме» следует щелкнуть мышью по кнопке Критерии. Далее надо ввести в соответствующее поле формы искомое значение, а для текстовых значений - хотя бы начальный уникальный фрагмент. Результатом поиска является отображаемая в форме первая от начала БД запись, в которой обнаружено совпадение с введенным искомым значением. Щелчками по кнопкам Следующая или Предыдущая, можно перейти к очередной или предыдущей такой записи;

• посредством использования операции Автофильтра (см. ниже примеры выполнения заданий 3 и 4);
• посредством выполнения операции Расширенного фильтра, который использует формируемый предварительно блок критериев поиска (см. ниже пример выполнения задания 5);
• с использованием функций категории Работа с базой данных, которые применяют механизм расширенной фильтрации с последующей обработкой ее результатов (см. ниже пример выполнения задания 6);
• путем построения сводной таблицы посредством диалога с Мастером сводных таблиц (см. ниже пример выполнения задания 7);
• с использованием пунктов инструментального меню Правка/Найти..., что применительно к таблице БД можно считать наименее эффективным.
1. В заданиях 5 и 6 требуется сформировать блок критериев, заполнив его заданными условиями выборки искомых записей. С этой целью предварительно необходимо зарезервировать диапазон ячеек для размещения блока критериев посредством вставки пустых строк над таблицей БД. Затем следует скопировать строку с наименованиями полей БД в первую строку блока критериев, например, с использованием папки обмена. Далее следует ввести, начиная со второй строки блока критериев, конкретные условия выборки записей. При этом следует иметь в виду, что комбинированный критерий фильтрации формируется из частных критериев в отдельных ячейках блока по правилу: объединение в строке – логической операцией И, в столбце – логической операцией ИЛИ. Полученные таким образом блоки критериев следует представить в практической работе в виде соответствующих рисунков.
2. Задание 6 предполагает использование функций категории Работа с базой данных, которые применяют механизм расширенной фильтрации с последующей обработкой ее результатов:

БДСУМ - суммирование значений в указанном столбце;

БСЧЁТ - подсчет числа значений в указанном столбце, который должен содержать не текстовые значения;

ДМАКС - нахождение максимального значения в указанном столбце;

ДМИН - нахождение минимального значения в указанном столбце;

ДСРЗНАЧ - вычисление среднеарифметического значения в указанном столбце.

Все вышеперечисленные функции имеют три аргумента:

• диапазон ячеек, занимаемых исходной БД;
• ячейка с именем поля-столбца, по которому после фильтрации производится суммирование, подсчет числа значений, поиск максимума и прочее;
• диапазон ячеек блока критериев фильтрации.

Следует иметь в виду, что при использовании функции БСЧЁТ в качестве имени поля, по которому производится подсчет числа записей, прошедших фильтрацию, следует указать поле не текстового типа, например, арифметического (см. ниже пример выполнения задания 8).

1. Перекрестный запрос к БД из задания 9 реализуется посредством диалога с Мастером сводных таблиц, состоящего из четырех шагов:

шаг 1 - подтверждение создания таблицы на основе данных, находящихся в списке или базе данных Microsoft Excel;

шаг 2 - выделения диапазона ячеек, занимаемых БД;

шаг 3 - разметка сводной таблицы посредством перетаскивания имен полей в соответствующие области создаваемой таблицы;

шаг 4 - выбор варианта расположения сводной таблицы (см. ниже пример выполнения задания 7).

ЗАДАНИЕ 1.

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

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

1. Выделение диапазона ячеек, занимаемого исходной базой данных, перетаскиванием мышью

2.

3.

4. или клавишным аккордом [Shift]+[Ctrl]+[End] (с предварительным позиционированием ячейки, содержащей имя поля Фамилия).

5. Выбор пунктов инструментального меню Данные/Сортировка...

6. Заполнение диалогового окна Сортировка диапазона согласно рис. 1.

7. Для добавления вторичной сортировки – Добавить уровень

рис. 1

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

рис. 2

1. С целью подготовки к выполнению следующего задания - отмена результатов сортировки, например, щелчком мышью по соответствующей кнопке на стандартной панели инструментов или с помощью клавишного аккорда [Ctrl]+[z].

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

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

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

рис. 3

1. Заполнение диалогового окна Настраиваемый автофильтр согласно рис.4.

рис. 4

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

1. С целью подготовки к выполнению следующего задания - отмена результатов фильтрации посредством выбора в раскрывающемся списке поля Фамилия позиции (Все).

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

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

1. Выбор в раскрывающемся списке поля Пол позиции жен. При этом используется автофильтр

1. Выбор в раскрывающемся списке поля Количество детей позиции

1. Заполнение диалогового окна Пользовательский автофильтр согласно рис.5.

рис. 5

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

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

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

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

1. Резервирование диапазона ячеек для размещения блока критериев посредством вставки четырех строк над таблицей исходной БД.

2. Копирование строки с наименованиями полей БД в первую строку блока критериев, например, с использованием папки обмена.

3. Внесение во вторую строку блока критериев условий выборки записей, как это изображено на рис. 6.

рис. 6

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

рис. 7

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

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

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

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

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

рис. 8

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

рис. 9

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

рис. 10

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

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

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

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

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

3.

Рис.12

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

Рис.13

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

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

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

Рис.14

·

рис. 15

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

рис. 16