Использование расширенного фильтра предполагает обязательное наличие диапазона критериев(условий). Диапазон должен состоять, по крайней мере, из двух строк, где в первой строке указываются имена полей (строка заголовка списка), во второй и последующих строках – условия отбора.
Поместить диапазон критериев лучше над списком, если фильтрация производится на месте, или над результатом отбора, если копия отфильтрованных данных размещается на свободном месте листа.
Заголовки в диапазоне критериев должны точно совпадать с заголовками столбцов в списке, поэтому при создании лучше использовать копирование.
Если результат отбора нужно получить на отдельном листе, то на этом листе нужно поместить диапазон условий и выполнить вызов окна Расширенный фильтр.
Диапазонусловий может содержать все графы списка, хотя условия текущего отбора можно задавать только в некоторых графах. Такой диапазон легко редактировать для нового отбора.
В диапазоне условий можно задавать только графы, по которым идет отбор.
Если для графы указан интервал значений для отбора, например, по графе Рост (табл. 1) нужно отобрать записи со значениями от 170 до 180. В этом случае название графы Рост в диапазоне условий повторяют два раза (можно не рядом), и в первый раз указать условие >170, а во второй – <180.
Задание условий с логической операцией И
Логическая операция И предполагает, что из списка будут отобраны записи, в которых одновременно выполняются все заданные условия.
При формировании диапазона условий с логической операцией И, нужно условия отбора расположить в одной строке.
Практические задания
Задание 2. На лист Студенты отобрать информацию обо всех женщинах моложе 25 лет.
Порядок выполнения
- создать новый Лист, щелкнув правой кнопкой мыши (ПКМ) на ярлыке любого листа, выбрать Вставить …, затем Лист, ОК, появится Лист 3.
- переименовать лист в Студенты: щелкнуть ПКМ на ярлыке, выбрать Переименовать.
- создать Диапазон условий, для этого:
o скопировать на лист Студенты строку с заголовками граф с листа Санаторий;
o в строке под заголовком для графы Полных лет указать <25, а графы Пол ввести букву ж.
- настроить фильтр для отбора записей, результаты поместить на листе Студенты под диапазоном условий. Для чего:
o сделать активным лист Студенты:
o выбрать: закладка Данные ð группа Сортировка и фильтр ð кнопка Дополнительно;
o в диалоговом окне Расширенный фильтр заполнить поля – Исходный диапазон, где указать диапазон с таблицей для поиска (заголовки граф и все данные) – Санаторий!А6:I36, поле Диапазон условий– в нем указать только что созданный на листе Студенты диапазон (заголовки граф и строка с условиями). В группе Обработка отметить переключатель скопировать результат в другое место, в поле Поместить результат в диапазон указать адрес первой клетки диапазона (левой верхней);
- на листе Студенты появится заголовок исходной таблицы и записи, отвечающие условиям;
- над результатами отбора набрать заголовок: Команда девушек (до 25 лет).
Внимание! Здесь создан диапазон условий с логической операцией И, т. е. все условия отбора записаны в одну строку, поэтому из списка отбирались строки, в которых выполнялись одновременно оба условия (Пол = ж, Полных лет <25).
Задание 3. На лист Студенты отобрать информацию о мужчинах от 18 до 25 лет.
Порядок выполнения
- использовать имеющийся на листе Студенты диапазон условий, заменить в нем условия: в графе Пол на м, в графе Полных лет указать > 18;
- для заданиявторой границы возраста, продолжить диапазон условий,повторить заголовок – Полных лет в следующей ячейке и под ним указать второе условие – <25 (Рис.1);
| № путевки | Фамилия | Дата рождения | Полных лет | Пол | Рост | Вес | Прививки (Г-грипп; Д – дифтерия) | Оплата | Полных лет |
| >18 | м | <25 |
Рис. 1. Диапазон условий
- как в предыдущем задании настроить расширенный фильтр, учитывая, что диапазон условий изменился, он стал на одну ячейку длиннее, результат отбора поместить налисте Студенты под предыдущей таблицей;
- над результатами отбора сделать заголовок: Команда студентов (от 18 до 25 лет).
Задание условий с логической операцией ИЛИ.
При использовании логической операции ИЛИ из списка будут отобраны записи, в которых выполняются хотя бы одно из условий.
При формировании диапазона условий с логической операцией ИЛИ, условия отбора располагаются в разных строках.
Практические задания
Задание 4. Поместить на лист Студенты информацию об отдыхающих в возрасте 30 и 50 лет.
Порядок выполнения
- создать на листе Студенты диапазон условий: скопировать заголовок только одной графы – Полных лет, под ним в последующей двух строках указать 30 и 50;
- настроить расширенный фильтр на листе Студенты, исходный диапазон - на листе Санаторий, диапазон условий – на листе Студенты.
- получим одну таблицу, в которой будут фамилии отдыхающих в возрасте 30 лет и 50.
Задание условий с одновременным использованием логических операций И и ИЛИ
В одном отборе записей можно совместить логические операции И и ИЛИ.
При формировании диапазона условий с логическими операциями И и ИЛИ, диапазон условий – многострочный.
Практические задания
Задание 5. На лист Спорт отобрать мужчин в возрасте от 18 до 35 лет и женщин от 18 до 30. Результат отбора не должен содержать двух последних граф (о прививках и оплате).
Порядок выполнения
- создать новый рабочий лист, переименовать в Спорт;
- подготовить на листе Спорт диапазон условий, для этого с листа Санаторий скопировать строку заголовка без двух последних граф (см. задание).
- в строке заголовка повторить графу Полных лет, чтобы задать возраст в интервале.
- под строкой заголовка последуют две строки с условиями отбора. В первой строке в графу Пол ввести м, в одну графу Полных лет ввести >18, в другую – <35, чтобы отобрать записи с мужчинами в возрасте от 18 до 35 лет. Вторую строку условий заполнить аналогично для отбора в эту же таблицу женщин в возрасте от 18 до 30 лет.
- настроить расширенный фильтр для отбора записей на лист Спорт, учитывая, что Исходный диапазон – Санаторий!А6:G36 (без двух последних граф). Диапазон условий на листе Спорт состоит из заголовка граф исходной таблицы и двух строк с условиями.
Отбор текстовых строк
При фильтрации информации можно использовать текстовые графы. Это уже применялось, когда при отборе учитывался пол отдыхающих (м или ж), т. е. использовался весь текст графы.
Можно отобрать информацию по фрагменту в текстовой графе. Чтобы найти текстовые строки, в которых только часть знаков совпадает, нужны подстановочные знаки:
| Использовать | Чтобы найти …. |
| ? - знак вопроса | …любой знак (один) Пример: условию «бар?н» соответствуют результаты «барин» и «барон» |
| * (звездочка) | … любое количество знаков Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток» |
| ~ (тильда), за которой следует?, * или ~ | … вопросительный знак, звездочку или тильду Пример: условию «ан91~?» соответствует результат «ан91?» |
Если текст в графе начинается с искомых символов, подстановочные знаки можно не использовать.
Практические задания
Задание 5. На лист Фамилии отобрать отдыхающих, фамилии которых начинаются с символов Петр.
Порядок выполнения
- создать новый рабочий лист, переименовать в Фамилии, подготовить на нем диапазон условий, для этого с листа Санаторий скопировать строку заголовка;
- в строке под заголовком под графой Фамилии набрать Петр
- настроить фильр, результаты поместить на листе Фамилии под диапазоном условий;
- в таблицу с результатом будут отобраны отдыхающие, фамилии которых начинаются на Петр.
- Задание 6. На лист Фамилии отобрать отдыхающих, фамилии которых заканчиваются на символы ский
Порядок выполнения
- подготовить на листе Фамилии диапазон условий, т.е. с листа Санаторий скопировать строку заголовка;
- в строке под заголовком под графой Фамилии набрать *ский*, используется подстановочный знак звездочка, он допускает до и после заданных символов любые и даже их отсутствие;
- настроить фильтр, результаты поместить на листе Фамилии под диапазоном условий;
- будут отобраны отдыхающие, фамилии которых заканчиваются на ский.
