Фильтрация записей списка

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

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

Команда ДАННЫЕ > Фильтр > Автофильтр для каждого столбца строит список значений, который используется для заданияусловий фильтрации. По отдельному столбцу можно указать: значение – будут выбраны только записи, которые в данном столбце содержат указанное значение; все – выбираются все записи без ограничений; первые 10 – выбираются первые десять записей; условие – выбирается, если условие фильтрации для значений в столбце более сложное, в результате выбора этого поля появляется окно Пользовательский автофильтр, в котором задаётся условие отбора данных (рис. 1).

Рисунок 1. Окно задания условий фильтрации

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

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

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

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

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

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

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

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

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

Автоматическое подведение итогов. Команда ДАННЫЕ> Итоги > Промежуточные итоги вставляет строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной итоговой функцией (рис.3).

Рисунок 3. Окно подведения промежуточных итогов

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

Команда ДАННЫЕ> Итоги может выполняться для одного и того же списка записей многократно, ранее созданные промежуточные итоги могут как заменяться новыми, так и оставаться неизменными посредством установки или снятия флажка параметра Заменить текущие итоги. Таким образом, имеется возможность подведения итогов различных уровней вложенности.

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

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

При нажатии кнопки ОК автоматически создаются промежуточные итоги. Слева от таблицы на служебном поле появляются символы структуры: кнопка «+» – показ деталей; кнопка «-» – скрытие деталей; номера уровней 1, 2, 3 и т.д. – обозначенияуровней структуры.

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

Создание сводных таблиц. КомандаДАННЫЕ> Сводная таблица вызывает Мастера сводных таблиц для построения сводов – итогов определенных видов на основании данных списков, других свободных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таблицы Excel. Сводная таблица обеспечивает различные способы агрегирования информации.

Мастер сводных таблиц осуществляет построение сводной таблицы в несколько шагов:

Шаг 1. Указание вида источника сводной таблицы: использование списка или базы данных Excel; использование внешнего источника данных; использование данных из другой сводной таблицы. В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Рассмотрим случай использования списков при построении сводных таблиц.

Шаг 2. Указание интервала ячеек для построения одной таблицы. Список (или база данных Excel) должен обязательно содержать имена столбцов (полей).

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

Шаг 3. Построение макета сводной таблицы.

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

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

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

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

Рисунок 4. Окно вычислений в сводной таблице

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

Для удобной работы со сводными таблицами программа предоставляет в распоряжение пользователя специальную панель инструментов – Сводные таблицы.

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