на предыдущую  на следующую

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

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

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

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

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

Примеры условий отбора расширенного фильтра

Диапазон условий состоит, по крайней мере, из одной строки подписей условий и, по крайней мере, одной строки собственно условий.

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

В условия отбора расширенного фильтра может входить

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

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

Несколько условий для одного столбца.

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

Одно условие для нескольких столбцов.

Чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения "Продукты" в столбце «Товар», "Белов" в столбце «Продавец» и объем продаж более 1000р.

Разные условия для разных столбцов.

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

Например, следующий диапазон условий отбора отображает все строки, содержащие значение "Продукты" в столбце «Товар», "Белов" в столбце «Продавец», либо объем продаж, превышающий 1 000р.

Один из двух наборов условий для двух столбцов

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

Например, следующий диапазон условий отбора отображает строки, содержащие как значение "Белов" в столбце «Продавец», так и объем продаж, превышающий 3 000р., а также строки по продавцу Батурину с продажами более 1 500р.

Таким образом можно сформулировать правила соединения условий следующим образом:

- условия, записанные на одной строке, считаются соединенными логическим оператором И (будут отобраны строки, для которых выполняются оба условия);

- условия, записанные на разных строках, считаются соединенными логическим оператором ИЛИ (будут отобраны строки, для которых выполняются хотя бы одно из условий).

Условия, создаваемые как результат выполнения формулы

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

Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце L значение, превышающее среднее значение ячеек диапазона L8: L24; заголовок условия отбора не используется.

Внимание! В этом случае условия отбора должны содержать ДВЕ ячейки.

Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В примере формулы "L8" является ссылкой на поле (столбец L) первой записи списка.

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

Пример1.

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

1. Добавьте новый столбец:
   • в ячейку N7 введите заголовок Стаж работы в фирме,
   • в ячейку N8 - формулу: = РАЗНДАТ(M8;СЕГОДНЯ();"Y"), которая вычисляет полное количество лет между датой приема на работу и датой сегодня.

   • растяните полученную формулу до конца списка.
2. Скопируйте в ячейку А1 заголовок столбца Пол, а в ячейку В1 – Стаж работы в фирме.
3. Под заголовками столбцов введите условия, определенные поставленной задачей (рис.7.9).
   
4. Установите курсор в любую ячейку списка и дайте команду Данные=>Фильтр=> Расширенный фильтр.
5. В открывшемся окне, щелкнув мышью в окне Диапазон условий, выделите ячейки от А1 до В2. Остальные параметры оставьте по умолчанию. Образец заполнения окна диалога можно увидеть на рис. 7.9.

   

   Рис. 7.9.

6. Щелкнув мышью на кнопке ОК, получите отфильтрованный на том же месте список (рис. 7.10).

   

   Рис. 7.10.

Пример2

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

1. Скопируйте в ячейку А1 заголовок столбца Пол, а ячейку В1 оставить пустой.
2. Под заголовками столбцов введите условия:
• в ячейку А2 – Ж,
• в ячейку В2 - =(СЕГОДНЯ()-J8)/365,25>55 (т.е. формулу, сравнивающую возраст с 55); в ячейку А3 – М,
• в ячейку В3 - =(СЕГОДНЯ()-J7)/365,25>60 (т.е. формулу, сравнивающую возраст с 60).
  В ячейках при этом отобразится результат сравнения для первой ячейки анализируемого столбца: в В2 - ИСТИНА, а в В3 - #ЗНАЧ!, что не влияет на дальнейшую работу фильтра
3. Установите курсор в любую ячейку списка и дайте команду Данные =>Фильтр =>Расширенный фильтр. Остальные параметры оставьте по умолчанию.
4. Щелкнув мышью в окне Диапазон условий, выделите ячейки от А1 до В3 (рис. 7.11).

   

   Рис. 7.11.

5. Щелкнув по кнопке ОК, получите на том же месте результат, который может отличаться каждый день, т.к. зависит от текущей даты (рис. 7.12).

   

Рис. 7.12.

Пример 3

Отберем из списка Список сотрудников только строки, в которых содержится информация о работниках, имеющих стаж более 8 лет и возраст менее 40 лет..Результат поместить в другое место. Для этого мы будем использовать в условиях отбора функцию, определяющую превышение вычисленного от даты рождения возраста 55 лет для женщин и 60 для мужчин .Для этого проделайте следующие действия.

1. Скопируйте в ячейку В1 заголовок столбца Стаж работы в фирме, а ячейку А1 оставьте пустой.
2. Под заголовками столбцов введите условия:
• в ячейку В2 – >8
• в ячейку А2 - =(СЕГОДНЯ()-J8)/365,25<40 (т.е. формулу, сравнивающую возраст с 40);
• В ячейке А2 при этом отобразится результат сравнения для первой ячейки анализируемого столбца ЛОЖЬ, что не влияет на дальнейшую работу фильтра
3. Установите курсор в любую ячейку списка и дайте команду Данные =>Фильтр =>Расширенный фильтр. Выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон и выделите первую ячейку диапазона для размещения результата фильтрации. Отобранные данные будут помещены в диапазон, начинающийся с указанной ячейки
4. Щелкнув мышью в окне Диапазон условий, выделите ячейки от А1 до В2 (рис. 7.12).

Рис. 7.12.

Фильтр для уникальных записей

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

1. Выберите столбец или ячейку в списке, который требуется отфильтровать. Убедитесь, что диапазон ячеек содержит заголовок столбца
2. В меню Данные выберите команду Фильтр, а затем — команду Расширенный фильтр.
   Отобразится диалоговое окно Расширенный фильтр.
3. Выполните одно из следующих действий:
• Чтобы отфильтровать список на месте, как и при использовании функции Автофильтр, выберите вариант Фильтровать список на месте.
• чтобы скопировать результат действия фильтра в другое место, выберите вариант скопировать результат в другое место и введите в поле Поместить результат в диапазон ссылку на ячейки. В противном случае нажмите кнопку Свернуть диалоговое окно  для временного скрытия диалогового окна, выберите ячейку на листе, а затем нажмите кнопку Развернуть диалоговое окно  .
4. Установите флажок Только уникальные записи и нажмите кнопку ОК.

Уникальные значения из выбранного диапазона, начиная с ячейки, указанной в поле Копировать в диапазон, будут cкопированы в новое место..

Пример

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

1. Выделите столбец В с заголовком, т.е. диапазон В8:В24.
2. Дайте команду Данные =>Фильтр =>Расширенный фильтр. Выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон и выделите первую ячейку диапазона для размещения результата фильтрации. Отобранные данные будут помещены в диапазон, начинающийся с указанной ячейки. Поставьте флажок в поле Только уникальные записи (рис. 7.13) и нажмите кнопку ОК.

   

Рис. 7.13.

3. В появившемся результате фильтрации видны все фамилии списка (рис. 7.14).

   

   Рис. 7.14.

4. Для определения числа повторений заполняем соседний столбец: в ячейку В26 вводим текст количество, в ячейку вводим формулу =СЧЁТЕСЛИ($B$8:$B$24;A27), подсчитывающую количество ячеек в исходном диапазоне, значения которых равны значению в ячейке А27, протягиваем формулу до конца полученного столбца (рис.7,15) .

   

   Рис. 7.15.

в начало

на предыдущую   на следующую

Copyright © 2010-2024
Ющик Е.В. All Rights Reserved

E-mail:
mailto:yuschikev@yandex.ru?subject=Письмо автору