Редактор электронных таблиц MS Excel.
Занятие №5.
Фильтры, автофильтры.

Автофильтр. Расширенный фильтр. Условия расширенных фильтров. Фильтрация с помощью формул. Убрать все фильтры и показать все.

Автофильтр

Если таблица состоит из нескольких столбцов с заголовками, можно применять инструмент Автофильтр. (Меню=>Данные=>Фильтр=>Автофильтр).

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

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

1. Самое простое - выбрать все записи, с заданным значением в данном столбике. Надо просто выбрать это значение в списке (который открывается, если кликаешь по стрелке Автофильтра). Например, дана таблица (она длинная - иначе смысла особого автофильтры не имеют):

   Абонент	Тариф	Долг
   Петров	Базовый	700
   Иванов	007	800
   Сидоров	Базовый	900
   Иванов	Базовый	700
   …	…	…

   Чтобы вывести всех абонентов, подключенных по тарифному плану “базовый”, надо во-первых, навешать автофильтр, во-вторых, выбрать из списка в ячейке “Тариф” слово “базовый”. Ненужные строчки, например “Иванов - 007 - 800”, будут скрыты с наших глаз и в распечатке не появятся.

2. Задание посложнее: выбрать несколько самых хороших. Например, мы хотим выбрать 100 человек, которые должны компании больше всего денег. Включаем автофильтр. В ячейке “Долг” выбираем из списка пункт “Первые 10…”) устанавливаем “100 - наибольших - элементов списка”.
3. Кроме того, можно задать условия (одно или два), выбрав соответствующий пункт списка.
4. Если одно условие уже выбрано, то при второй фильтрации скрытые строки не отобразятся. Например, если мы хотим выловить всех должников с фамилией Иванов, подключенных по тарифному плану Базовый, мы должны в списке “Абонент” выбрать строчку “Иванов”, а в списке “Тариф” - строчку “Базовый”.

Расширенный фильтр.

Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки.

На листе также должно быть не менее трех пустых строк выше списка. Эти строки будут использованы в качестве диапазона условий отбора.

1. Скопируйте из списка заголовки фильтруемых столбцов.
2. Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора.
3. Введите в строки под заголовками условий требуемые критерии отбора. Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка.
4. Укажите ячейку в списке.
5. Выберите пункт Меню=>Данные=>Фильтр=>Расширенный фильтр.
6. Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение “Фильтровать список на месте”.
7. Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель Обработка в положение “Скопировать результаты в другое место”, перейдите в поле “Поместить результат в диапазон”, а затем укажите верхнюю левую ячейку области вставки.
8. Введите в поле “Диапазон критериев” ссылку на диапазон условий отбора, включающий заголовки столбцов.

Совет. Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон.

Условия расширенных фильтров.

Условия, написанные в одной строке выполняются ВСЕ одновременно. Причем будут выведены все строки, удовлетворяющие условию хотя бы в одной строке.

Например, для следующего диапазона условий будут отобраны строки, содержащие либо “Белов”, либо “Батурин” в столбце “Абонент”, либо содержащие “Сушкин” в столбце “Абонент” с долгом больше 1000.

Фильтрация с помощью формул.

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

Примечания:

В ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Убрать все фильтры и показать все строки.

Чтобы показать все строки, скрытые фильтром, надо выбрать пункт Меню=>Данные=>Фильтр=>Отобразить все.

Чтобы убрать стрелочки автофильтра, надо отключить пункт Меню=>Данные=>Фильтр=>Автофильтр.