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

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

Правила фильтрации с помощью расширенного фильтра:

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

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

4. Ниже списка, отступив строку, необходимо скопировать имена столбцов, которые нужно вывести.

6. В пункте меню Данные выбрать пункт Фильтр, затем команду Расширенный фильтр.

7. В диалоговом окне установите переключатель Обработка в положение Фильтровать список на месте, чтобы скрыть ненужные строки (рис. 8).

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

с обработкой Фильтровать список на месте

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

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

с обработкой Скопировать результат в другое место

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

8. Ввести в поле Диапазон критериев ссылку на диапазон условий отбора, включая заголовки.

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

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

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

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

Таким образом, поиск с помощью расширенного фильтра предполагает следующее.

1. Подготовить диапазон критериев для расширенного фильтра:

– первая строка должна содержать заголовки полей, по которым будет производиться отбор (точное соответствие заголовкам полей списка);

– условия критерия записываются в пустые строки под подготовительной строкой заголовка.

2. Поместить указатель в список (или выделить весь список).

3. Выполнить команду Данные – Фильтр – Расширенный фильтр.

4. В диалоговом окне Расширенный фильтрзадать необходимые параметры.

Пример 2. В исходной базе данных (рис. 1), используя Расши ренный фильтр, показать записи о проданном товаре в январе в количестве от 10 до 42 шт.

Прежде чем воспользоваться расширенным фильтром, необходимо подготовить диапазон критериев (условие отбора), как показано на рис. 10.

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

Результат выполнения отбора с использованием расширенного фильтра представлен на рис. 12.

MS Excel предоставляет широкие возможности для проведения анализа данных, находящихся в списке. К средствам анализа относятся:

 обработка списка с помощью различных формул и функций;

 построение диаграмм и использование карт MS Excel;

 проверка данных рабочих листов и рабочих книг на наличие ошибок;

Специальные средства анализа выборочных записей и данных – подбор параметра, поиск решения и др.

Вторая часть подсказки: «Знак ”*” обозначает последовательность любых знаков». Если в условии невозможно определить, какие символы и в каком количестве должны находиться в строке, то вместо них подставьте «*». Примеры условий:

Как отфильтровать в excel по слову

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Применение нескольких фильтров в сводной таблице

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

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

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

Устанавливаем фильтр по подписи

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

В появившемся диалоговом окне Фильтры по подписи, указываем параметр фильтрации (в нашем случае, это значение 7)

Предположим, теперь вам требуется увидеть месяца с наибольшим значением, для этого необходимо применить Фильтр по значению… для этого же поля. Опять жмем на иконку справа от поля Месяц, в выпадающем меню выбираем Фильтры по значению -> Первые 10…

В появившемся диалоговом окне Фильтр «Первые 10», настраиваем отображение первых трех наибольших значения.

Теперь сводная таблица отображает первые 3 месяца с наибольшим значением, но фильтр по подписи пропал. На рисунке отображены значения 2, 6 и 9-го месяцев.

Меняем параметры фильтров сводной таблицы

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

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

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

Практическое задание по установке фильтров в Ms Excel | Информационные технологии

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