Работа со списками

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

Расширенный фильтр позволяет реализовать подобие запросов QBE (query by example – запрос по образцу), используемых в настоящих базах данных. Для использования расширенного фильтра необходимо для каждого запроса формировать блок критериев. Блок критериев должен состоять минимум из двух ячеек – имени поля и условия поиска по этому полю. Условие должно быть либо числом, либо текстом (аналогично условиям в функциях sumif() и countif(), которые были рассмотрены в предыдущей главе). Блок критериев целесообразно располагать над списком с данными. Обязательно наличие пустой строки перед блоком критериев и после него. Таким образом, в нашем примере перед диапазоном исходных данных нужно вставить несколько строк.

Рис. 3.19. Создание блока критериев

Пусть из списка сотрудников требуется выбрать лиц, начавших трудовую деятельность в 1960, 1983 и в 1990 годах. Здесь потребуется создать расчетное поле "Год", аналогично тому, что делалось при рассмотрении автофильтра. По этому полю нужно удовлетворить одновременно трем условиям, поэтому автофильтр не годится.

Для использования расширенного фильтра в первую очередь необходимо сформировать блок критериев. Он формируется путем копирования строки с именами полей в пустую строку над таблицей данных, а затем под именем поля "Год" в блоке критериев записываются одно под другим три условия (искомые значения годов), как показано на рис. 3.19. Условия, записанные одно под другим, обеспечивают выполнение логической операции "ИЛИ".

После этого выделяется диапазон исходных данных, включая строку с именами полей и вызывается диалог настройки расширенного фильтра "Данные/Фильтр/Расширенный фильтр...". Этот диалог имеет две вкладки. Первая вкладка (Ввод) позволяет определить диапазоны исходных данных и блока критериев (поэтому блок критериев уже должен существовать) (рис. 3.20). В нашем случае данные занимают диапазон $A$9:$K$83, а критерии – диапазон K4:K7 (в блок критериев входит имя поля "Год" и три значения под ним). Вторая вкладка (Вывод) позволяет определить, куда будут записываться результаты работы фильтра (рис. 3.22).

При задании диапазона списка критериев нет возможности выделить эти диапазоны в ЭТ, если диалог настройки расширенного фильтра полностью открыт, как показано на рис. 3.20. Для указания диапазонов путём выделения блоков ЭТ мышью нужно свернуть диалог, нажав на "кнопку" справа от соответствующего поля ввода. Диалог примет вид окна указания диапазона (рис. 3.21), после чего уже можно выделять нужный блок ячеек. По окончании выделения нажатием на ту же "кнопку" следует вернуть диалоговое окно в первоначальный вид.

Рис. 3.20. Определение диапазона исходных данных и блока критериев для расширенного фильтра

Рис. 3.21. Окно для указания диапазона ячеек

В данном случае выбран вариант "Фильтровать на месте", однако результаты работы фильтра могут быть скопированы в другой диапазон на том же листе, на другой лист или даже в другой документ. Блок критериев также может быть размещен на другом листе, но это уже дело вкуса и привычки.

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

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

Рис. 3.23. Результат работы расширенного фильтра

Рис. 3.24. Сложные условия для расширенного фильтра

Рис. 3.25. Условия с вычисляемым критерием поиска

Из приведенного рисунка видно, что в условиях расширенного фильтра можно использовать как точное соответствие, так и операции сравнения для числовых полей. Расположение условий в одной строке означает одновременное выполнение условий (отношение "И"), а расположение условий друг под другом означает требование выполнения хотя бы одного из условий (отношение "ИЛИ").

В условиях расширенного фильтра можно также использовать результаты работы формул. Например, нужно найти сотрудников с окладом ниже среднего по предприятию. Тогда сначала подсчитываем средний оклад с помощью функции average() по столбцу с окладами (например, в ячейке K6), рядом в какой-то ячейке (например, в K5) записываем знак сравнения "<" (в ячейке, а не в условии, потому что критерий поиска может измениться), а в условии пишем формулу "=concatenate(K5;round(K6;0))". Функция round() используется для округления среднего значения до целого.

Блок критериев, полученный с использованием формулы, показан на рис. 3.25.

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