Глава 4. ИСПОЛЬЗОВАНИЕ Автофильтра

     Автофильтр является мощным средством фильтрации данных. С его помощью можно отфильтровать данные по сложному условию, состоящему из условий, которым должны отвечать различные поля одной записи. В свою очередь условие поиска по отдельному полю может состоять из одного или двух условий, связанных логической операцией И (для задания интервала) или ИЛИ (для задания двух альтернативных операций сравнения). Например, по издательствам Мысль или Наука, найти всех авторов, гонорар которых находится в диапазоне [200; 500] и договор заключен после 1998 года.

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

     Чтобы воспользоваться Автофильтром:

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

2. Выберите команду Данные, Фильтр, Автофильтр.

Как видно на рис.6 ячейки с названиями полей превращаются в раскрывающиеся списки.

Чтобы отфильтровать записи:

1. Раскройте список, соответствующий полю, которое следует включить в критерий. (На рис.6 это поле «Изд-во»).

2. Выберите желаемый критерий. Существуют следующие возможности:

 

Все	Вывод на экран всех записей (снятие ограничений на поле)
Первые 10	Вывод на экран заданного числа или процента первых или последних элементов списка
Условие	Вывод на экран диалогового окна Пользовательский автофильтр, чтобы задать одно или два условия с использованием шаблонов, операций сравнения и логических операций И, ИЛИ (рис.8)
Точное значение	Вывод на экран записей, поля которых в точности совпадают с выбранным значением
Пустые	Вывод на экран записей с пустым полем
Непустые	Вывод на экран записей, содержащих в поле какие-либо данные

 

3. Задайте параметры в диалоговом окне выбранной команды или выберите интересующее вас точное значение поля.

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

 

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

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

При работе с Автофильтром имеется три метода фильтрации данных:

- -поиск точного соответствия;

- -поиск по команде Условие;

- -поиск по команде Первые 10.

 

Поиск точного соответствия

Раскрывающийся список поля содержит полный перечень значений этого поля, имеющих место в обрабатываемой базе данных (см.рис.6). При выборе в раскрывающемся списке рис.7 значения «Мысль» мы получим следующий результат

Поиск по команде Условие

 

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

Выбор в раскрывающемся списке Автофильтра команды Условие вызывает появление на экране диалогового окна Пользовательский автофильтр (см.рис.8). В левом раскрывающемся списке диалогового окна можно выбрать операцию сравнения, а в правом – выбрать или ввести значение. Затем, если необходимо, выбрать один из переключателей И, ИЛИ, и задать вторую операцию и значение. Завершение ввода условия поиска – нажатие кнопки ОК.

На рис.9 приведены результаты по критерию поиска рис.8.

Поиск по команде Первые 10

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

Чтобы воспользоваться этим средством:

1. Раскройте список необходимого поля

2. Выберите команду Первые 10. Откроется диалоговое окно Наложение условия по списку (рис.10).

3. Выберите 3 параметра: число, выбор из наибольших или из наименьших элементов, единицы отсчета (количество или процент).

4. Нажмите кнопку ОК.

Глава 5. РАСШИРЕННЫЙ ФИЛЬТР

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

· можно применять сложные критерии, составленные с помощью логических операций И, ИЛИ, количество которых не регламентируется;

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

Расширенный фильтр требует создания всех трех составляющих полнофункционального списка Excel: диапазона базы данных, критериев и диапазона извлечения (см. главу 1). Они должны быть подготовлены заранее.

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

Чтобы   отфильтровать список:

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

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

3. В группе Обработка установите параметр Фильтровать список на месте.

4. Если необходимо, перейдите к полю Исходный диапазон и введите ссылку или имя диапазона списка.

5. Если необходимо, перейдите к полю Диапазон условий и введите ссылку или имя диапазона критериев.

6. Установите флажок Только уникальные записи, если не хотите, чтобы одинаковые записи повторялись (будет выводиться первая из всех удовлетворяющих критерию одинаковых записей).

7. Нажмите кнопку ОК. Результат фильтрации данных рис.11 представлен на рис.12.

Для возвращения к данным полного списка необходимо выбрать команды Данные, Фильтр, Отобразить все.

 

     Поиск данных выполняется аналогично фильтрации.

     Изменения:

· в группе Обработка установите параметр Скопировать результат в другое место.

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

Результаты работы Расширенного фильтра сразу же отобразятся на рабочем листе.

Пример простого поиска данных изображен на рис.13, 14.

Расширенный фильтр отключает Автофильтр, поэтому его можно использовать независимо от того, включен или нет Автофильтр.

 

 

5.1.
Использование множественного сравнения

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

Несколько условий введено в одной строке. На рис.15 критерий поиска будет читаться так: найди данные по издательству «Мысль» И гонорар автора не должен превышать 150 руб.,т.е. будут отфильтрованы записи, удовлетворяющие всем условиям одновременно.

По одному условию введено в разных строках. В этом случае будут отфильтрованы записи, которые удовлетворяют хотя бы одному условию. На рис.16 приведены критерии и результаты поиска данных об издательствах «Мысль» или «Наука».

Несколько условий введено в нескольких строках и столбцах. Результатом поиска будут записи, которые удовлетворяют всем условиям первой строки или всем условиям второй строки или всем условиям третьей строки и т.д. На рис.17 видны записи об издательстве «Мысль» по авторам с гонорарами, большими 100, или о договорах издательства «Финансы», заключенные после 31.12.93г.

 

Вычисляемые критерии

Вычисляемые критерии необходимы, когда а) требуется найти данные, попадающие в определенный интервал, б) для поиска данных

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

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

2.Поскольку критерий поиска – формула, он должен начинаться со знака "равно" (=).

3.Т.к. формула поиска логическая, то она в обязательном порядке должна содержать операцию отношения (см.табл.1) или логическую функцию И (), ИЛИ (), НЕ () (AND, OR, NOT). Подробнее см.п.5.2.2 «Поиск соответствия по простому вычисляемому критерию».

4.Ссылки в формуле критерия могут указывать как на записи списка, так и на другие ячейки рабочего листа. Для ссылок на список следует
использовать относительные ссылки, причем они должны указывать на верхние записи в диапазоне базы данных. Ссылки на ячейки вне списка должны быть абсолютными (со знаком $).

Приведем несколько примеров сравнения полей одной записи:

= B12 = E12

= B12 <12/2

= B12 -  E12>10

= B12<>$C$5

5.Если в критерии участвуют данные одной записи, то в формуле необходимо использовать самую первую запись списка, иначе результаты фильтрации будут неверными.