Фильтрация списков

Назначение фильтров. Фильтром называется средство Excel, позволяющее отобра­жать только те записи списка, которые удовлетворяют заданным условиям. Остальные запи­си временно скрываются за счет уменьшения до нуля высоты соответствующих строк.

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

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

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

В Excel можно использовать два типа фильтров: автофильтр и расширенный фильтр. Ни­же рассмотрены оба типа фильтров и даны рекомендации по их использованию.

Автофильтр. Перед использованием автофильтра нужно выделить любую ячейку спи­с­ка[4], после чего выбрать в меню Данные команду Фильтр 4 Автофильтр.

О включении автофильтра свидетельствует появление справа от заголовков столбцов кнопок автофильтра. Щелчком на такой кнопке можно открыть меню автофильтра (рис. 6.6,а), содержащее команды Все, Первые 10 и Условие, а также список полей данного столбца.

Рис. 6.6. Выбор условия фильтрации (а) и отфильтрованный список (б)

В примере, показанном на рис. 6.6, выполняется фильтрация списка, представленного на рис. 6.2,а, по значениям полей столбца Судно. В качестве критерия отбора выбрано поле Витязь (см. рис. 6.6,а). В результате фильтрации исходного списка высота строк 4, 6, 7 и 8-й, содержащих информацию о рейсах судов Керчь и Трепанг (см. рис. 6.2,а), уменьшится до нуля, и в списке останутся только 3, 5 и 9-я строки (см. рис. 6.6,б) с информацией о рейсах Витязя. Одновременно в строке состояния появится сообщение: Найдено записей: 3 из 7.

Таким же образом можно отфильтровать список еще по одному критерию. В результате записей в списке останется еще меньше. Например, если в качестве второго критерия в столб­це Груз выбрать 200000, то из трех записей для Витязя в списке останется только одна.

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

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

Как было сказано выше, в меню автофильтра каждого столбца, кроме списка полей, имеются три команды:

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

¡ Первые 10 – отображение 10 записей, содержащих наибольшие значения в том столбце, по данным которого выполняется фильтрация списка. Таково назначение команды при стандартных настройках. Ниже показано, как эти стандартные настройки можно изменять.

Рис. 6.7. Окно настройки условий фильтрации

При выборе команды Первые 10 открывается окно, представленное на рис. 6.7. В этом окне видны стандартные настройки. Любую из этих настроек можно изменить:

● в поле со счет­чиком можно задать необходимое количество отфильтрованных записей;

● в первом раскрывающемся списке вместо " наибольших " можно выбрать " наименьших ";

● во втором раскрыва­ю­щемся списке вместо " элементов списка " можно выбрать " % от количества элементов ". Эта настройка используется в тех случаях, когда нужно отфильтровать какой-то процент записей с наибольшими или наименьшими значениями в том столбце, по данным которого выполняется фильтрация. Например, если в окне настройки (см. рис. 6.7) задать: " 50 ", " наименьших ", " % от количества элементов " и щелкнуть на кнопке ОК, то в отфильтрованном списке останется половина от общего количества записей. Причем останутся записи с наименьшими значениями в столбце, по данным которого выполняется фильтрация.

Команда Первые 10 применима только к тем столбцам, которые содержат числа или даты. Если выбрать эту команду в меню автофильтра столбца с текстовыми данными, раздастся лишь звуковой сигнал.

¡ Условие – создание пользовательского автофильтра. При выборе этой команды открывается диалоговое окно Пользо­ва­тельский автофильтр (рис. 6.8,а), содержащее четыре элемента управления: два раскрывающихся спис­ка (слева) и два поля со списком (справа). Все они предназначены для задания условий фильтрации. Изначально в первом раскрывающемся списке выбран оператор сравнения " равно ", а все остальные поля – пустые.

Рис. 6.8. Окно пользовательского автофильтра (а) и меню операторов сравнения (б)

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

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

Например, если нужно отфильтровать список, представленный на рис. 6.2,а, таким образом, чтобы в нем остались только рейсы Витязя, выполненные в период с 10-го по 30-е января включительно, необходимо сделать следующее:

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

2. Щелкнуть на кнопке автофильтра в столбце День и выбрать в меню автофильтра коман­ду Условие. При этом откроется окно Пользовательский автофильтр (см. рис. 6.8,а).

3. В первом раскрывающемся списке выбрать оператор сравнения " больше или равно ".

4. В расположенное справа от списка поле ввести число 10 (это первая дата – 10 января).

5. Во втором раскрывающемся списке выбрать оператор сравнения " меньше или равно ".

6. В расположенном справа поле со списком выбрать число 30 (это вторая дата – 30 января). В данном случае число 30 есть в списке, поэтому его можно выбрать, а не вводить.

7. Убедиться, что включен переключатель И [5], после чего щелкнуть на кнопке ОК. В результате в списке останутся два рейса Витязя – 15-го и 30-го января.

Пользовательский автофильтр можно создавать не только с числовыми, но и с текстовы­ми критериями фильтрации. Различие состоит лишь в том, что вместо операторов сравнения в этом случае в раскрывающихся списках выбираются ключевые слова: " начинается с "; " не начинается с ";" заканчивается на ";" не заканчивается на "; " содержит ";" не содержит ".

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

При создании текстовых критериев фильтрации в окне Пользовательский автофильтр можно использовать шаблоны (маски), содержащие символы подстановки "*" и/или "?". Сим­вол "звездочка" заменяет любое число букв, цифр и спецсимволов, а вопросительный знак заменяет один символ в той позиции, где он стоит. Например, если при фильтрации списка, показанного на рис. 6.2,а, в первом раскрывающемся списке (см. рис. 6.8,а) выбрать ключевое слово " начинается с ", а в расположенное справа поле со списком ввести шаблон ??р, то в отфильтрованном списке останутся только рейсы судна Керчь.

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

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

Расширенный фильтр. По сравнению с автофильтром настройка параметров расширенного фильтра несколько сложнее, зато этот фильтр обла­дает двумя преимуществами:

¡ Он позволяет задавать более сложные параметры фильтрации, чем автофильтр (количество условий, объ­единяемых с помощью логических операторов И/ИЛИ, может быть больше двух; логические операторы могут объединять условия для полей из разных столбцов и т.д.).

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

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

Рис. 6.9. Окно расширенного фильтра

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

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

¡ Диапазон условий не может иметь меньше двух расположенных одна над другой яче­ек: верхняя – для имени поля (заголовка столбца), а нижняя – для условия отбора.

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

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

¡ Если несколько условий отбора записано в одной строке (см., например, на рис. 6.10 ячейки F2:G2), значит, эти условия связаны между собой логическим оператором И. В таком случае будут отобраны те записи, которые удовлетворяют всем заданным условиям.

¡ Если несколько условий отбора записано в разных строках (см., например, на рис. 6.10 ячейки H2:I3), значит, эти условия связаны между собой логическим оператором ИЛИ. В таком случае будут отобраны те записи, которые удовлетворяют хотя бы одному условию.

Рис. 6.10. Исходный список (A1:D5) и результаты фильтрации (A7:D8 и F5:I8)

Теперь, когда сформулированы основные правила формирования условий отбора запи­сей, рассмотрим на конкретном при­мере использование расширенного фильтра. Будем счи­­тать, что фильтруемый список находит­ся в ячейках A1:D5 (см. рис. 6.10) и нам необходимо выбрать из него те рейсы судна Керчь, в которых перевезенный груз был не менее 210000 тонн. Для решения этой задачи необходимо выполнить следующие операции:

Скопировать имена полей Судно и Груз из ячеек A1 и C1 в F1 и G1 (см. рис. 6.10).

Ввести в ячейки F2 и G2 условия отбора записей: Керчь и >= 210000.

Выделить любую ячейку фильтруемого списка и выбрать в меню Данные команду Фильтр 4 Расширенный фильтр. В результате откроется окно расширенного фильтра (см. рис. 6.9), в котором Исходный диапазон (A1:D5) будет определен автоматически[6].

Щелчком мыши или нажатием клавиши Tab перейти в поле Диапазон условий, после чего выделить диапазон F1:G2. В результате в поле Диапазон условий появится соответствующая ссылка.

Если нужно скопировать отобранные записи в какую-то область рабочего листа[7], следу­ет щелчком мыши включить переключатель Скопировать резуль­тат в другое место

Щелчком мыши или нажатиями клавиши Tab перейти в поле Поместить результат в диапазон и щелкнуть на той ячейке, которая будет верхним левым углом диапазона, куда будут скопированы отобранные записи. В нашем примере это ячейка A7.

Завершить операцию щелчком на кнопке ОК.

В результате фильтрации списка при заданных условиях будет отобрана одна запись – рейс судна Керчь 15.01.03 (см. рис. 6.10).

В качестве второго примера рассмотрим фильтрацию того же списка (A1:D5) при следующих условиях: нужно отобрать в исходном списке все рейсы судна Керчь, а также те рейсы других судов, в которых перевезенный груз был не менее 210000 тонн. В соответствии с изложенными выше правилами формирования критериев отбора, данные условия дол­жны быть записаны так, как это показано на рис. 6.10 в ячейках H2:I3.

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

В результате фильтрации списка при новых условиях будут отобраны три записи – два рейса судна Керчь 15.01.03 и 29.01.03, а также рейс Витязя 27.01.03 (см. рис. 6.10).

Флажок Только уникальные записи (см. рис. 6.9) устанавливают, когда в фильтруемом списке есть повторяющиеся записи, чтобы из них была отобрана лишь одна. Этот флажок ис­пользуют также, чтобы скрыть строки с повторяю­щимися записями при просмотре списка. В таком случае никакие условия не задаются, и список фильтруется на месте.