Материал: Решение задач в области техносферной безопасности с использованием пакетов прикладных программ. Сазонова С.А., Колодяжный С.А

Фильтрация записей расширенным фильтром

После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 9.4).

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

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

Рис. 9.4. Диалоговое окно «Расширенный фильтр»

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

Для снятия действия условий фильтрации выполняется команда Данные, Фильтр, Отобразить все.

Фильтрация с помощью формы данных

ППП Excel позволяет работать с отдельными записями списка с помощью экранной формы (рис. 9.5). Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка.

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей - названия столбцов списка.

Для просмотра записей используется полоса прокрутки либо кнопки <Назад> или <Далее>, выводится индикатор номера записи. При просмотре записей возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи с помощью кнопки <Вернуть> можно отменить.

Для создания новой записи нажимаете кнопку <Добавить>, при этом выполняется заполнение пустых полей экранной формы; для перехода между полями формы используются курсор мыши либо клавиша <ТаЬ>. При повторном нажатии кнопки <Добавить> сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка <Удалить>. Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.

С помощью экранной формы задаются критерии сравнения. Для этого нажимаете кнопку <Критерии>, при этом форма очищается для условий поиска в полях формы с помои кнопки <Очистить>, а название кнопки <Kритерии> заменяется на название <Правка>. После ввода критериев сравнения нажимаются кнопки <Назад> или <Далее> для просмотра отфильтрованных записей в нужном направлении. При просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка <Правка>, для выхода из формы - кнопка <3акрыть>.

Рис. 9.5. Экранная форма для работы со списком записей

Задание 9.1. Использование Автофильтра

Выберите данные из списка по критерию отбора, используя Автофильтр.

Проведите подготовительную работу - переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных.

Выберите из списка данные, используя критерий:

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

2. вид занятий - л.

Отмените результат автофильтрации.

Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету n1 на оценки 3 и 4.

Отмените результат автофильтрации.

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

Последовательность выполнения задания 9.1

1. Выберите из списка данные, используя критерий: для преподавателя - а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий - л. Для этого: установите курсор в область списка и выполните команду Данные, Фильтр, Автофильтр; в каждом столбце появятся кнопки списка.

2. Сформируйте условия отбора записей:

в столбце Таб. № препод. нажмите кнопку , из списка условий отбора выберите а1;

в столбце Оценка нажмите кнопку , из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;

в столбце Вид занятия нажмите кнопку , из списка условий отбора выберите л.

3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные, Фильтр, Автофильтр.

4. Выберите из списка данные, используя критерий - для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь аналогичной п. 3 технологией фильтрации

5. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные, Фильтр, Автофильтр.

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

Задание 9.2. Использование Расширенного фильтра.

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

1. Проведите подготовительную работу - переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных.

2. Скопируйте имена полей списка в другую область на том же листе.

3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5.

4. Произведите фильтрацию записей на том же листе.

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.

6. Сформируйте в области условий отбора Вычисляемый критерий - для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий - л; результат отбора поместите на новый рабочий лист.

7. Произведите фильтрацию записей на новом листе.

8. Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.

Последовательность выполнения задания 9.2

1. Проведите подготовительную работу:

переименуйте Лист4 - Расширенный фильтр;

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

Этап 1. Формирование диапазона условий по типу Критерий сравнения.

2. Скопируйте все имена полей списка в другую область на том же листе например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 - имена полей области критерия, J2:О5 - область значений критерия.

3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

в столбец Номер группы - точное значение - 133;

в столбец Код предмета - точное значения - n1;

в столбец Оценка - условие ->3.

Этап 2. Фильтрация записей расширенным фильтром.

4. Произведите фильтрацию записей на том же листе:

установите курсор в область списка (базы данных);

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

в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например;

Скопировать результат в другое место: установите флажок

• исходный диапазон: A1:G17;

• диапазон условия: J1:O5.

Поместить результат в диапазон: J6; нажмите кнопку <ОК>.

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе, соблюдая технологию п.3 и п.4.

Этап 3. Формирование диапазона условий по типу Вычисляемый критерий.

6. Сформируйте в области условий отбора Вычисляемый критерий - для каждого преподавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий - л; результат отбора поместите на новый рабочий лист. Для этого:

в столбец Вид занятия введите точное значения - букву л;

переименуйте в области критерия столбец Оценка, например, на имя Оценка 2:

в столбец Оценка 1 введите вычисляемый критерий, например, вида

=G2>CP3HAЧ($G$2:$G$17),

где G2 — адрес первой клетки с оценкой в исходном списке, $G$2 : $G$I7 - блок ячеек с оценками, СРЗНАЧ — функция вычисления среднего значения.

Этап 4. Фильтрация записей расширенным фильтром.

7. Произведите фильтрацию записей на новом листе;

установите курсор в область списка (базы данных);

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

в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например:

Скопировать результат в другое место: установите флажок

• исходный диапазон: A1:G17;

• диапазон условия: Л:05.

Поместить результат в диапазон: перейдите на новый лист и щелкните мышью в любой ячейке нажмите кнопку <ОК>.

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

Задание 9.3. Использование Формы для выбора данных из списка

Используя Форму, выберите данные из списка.

1. Проведите подготовительную работу - переименуйте новый лист на Форма и скопируйте на него исходную базу данных.

2. Просмотрите записи списка с помощью формы данных, добавьте новые.

3. Сформируйте условие отбора с помощью формы данных - для преподавателя выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий - л.

4. Просмотрите отобранные записи.

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

Последовательность выполнения задания 9.3

1. Проведите подготовительную работу:

переименуйте Лист5 - Форма;

выделите блок ячеек исходного списка, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Форма;

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

2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопки <Назад> и <Далее>. С помощью кнопки <Добавить> добавьте новые записи.

3. Сформируйте условие отбора - для преподавателя - а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий - л. Для этого:

нажмите кнопку <Критерии>, название которой поменяется на <Правка>;

в пустых строках имен полей списка введите критерии:

в строку Таб № препод. введите а1;

в строку Вид занятия введите л;

в строку Оценка введите условие > 2.

4. Просмотрите отобранные записи, нажимая на кнопку <Назад> или <Далее>.

5. Аналогично сформируйте собственные условия отбора записей и просмотрите их.

Лабораторная работа № 10 Структурирование таблиц

Цель работы:

1. изучение особенности структурирования таблиц в Excel;

2. приобретение навыков структурирования таблиц.

Теоретические сведения

Структурирование таблиц

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

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

Структурирование выполняется с помощью команды Данные, Группа и Структура, а затем выбирается конкретный способ - автоматический или ручной.

При ручном способе структурирования необходимо предварительно выделить область - смежные строки или столбцы. Затем вводится команда Данные, Группа и Структура, Группировать, которая вызывает окно «Группирование» для указания варианта группировки - по строкам или столбцам.

В результате создается структура таблицы (рис. 10.1) со следующими элементами слева и/или сверху на служебном поле:

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

кнопка <плюс> - для раскрытия групп структурированной таблицы;

кнопка <минус> - для скрытия групп структурированной таблицы;

кнопки <номера уровней 1, 2, 3> - для открытия или скрытия соответствующего уровня.

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

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

Рис. 10.1. Фрагмент структурированной таблицы

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

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

Для отмены всех структурных компонентов таблицы - команда Данные, Группа Структура, Удалить структуру.

Автоструктурирование

Автоструктурирование выполняется для таблиц, содержащих формулы, которые ссылаются на ячейки, расположенные выше и (или) левее результирующих ячеек, образуя с ними смежную сплошную область. После ввода в таблицу исходных данных и формул курсор устанавливается в произвольную ячейку списка и выполняется команда Данные, Группа и Структура, Создать структуру. Все структурные части таблицы создаются автоматически.

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

Пример такой таблицы приведен на рис. 10.2. В таблице расчета заработной платы введены столбцы, в которых по каждому работнику по формулам рассчитываются: общий налог, итоговая сумма доплат и сумма в выдаче. Кроме того, по каждому виду начислений (по столбцам) в строке Итого рассчитывается с помощью функции СУММ общая сумма, Порядок следования исходных данных и результатов (итогов) - слева направо, сверху вниз, что позволяет применить автоструктурирование таблицы. На рис. 10.3 показан вид таблицы после автоструктурирования.