Применение расширенного фильтра
Основной целью использования расширенного фильтра является проведение отбора данных по сложным критериям (более двух связанных условий и/или с использованием встроенных функций). Возможно использование расширенного фильтра с применением ранее рассмотренных критериев. Существует два варианта просмотра результатов фильтрации:
1. фильтрация списка на месте (записи, не удовлетворяющие заданному критерию, становятся скрытыми);
2. копирование найденных записей на свободное поле рабочего листа.
Основные понятия при работе с расширенным фильтром следующие.
Блок ввода – это база данных, называемая исходным диапазоном.
Блок вывода – таблица, содержащая результаты фильтрации.
Блок критериев – это таблица, содержащая условия отбора (критерий), называемая диапазоном условий.
Различают критерии сравнения и вычисляемые критерии.
Критерий сравнения – условие поиска, используемое для извлечения данных, сравниваемых с конкретным значением. Критерий сравнения может быть последовательностью символов, такой как ИЧП Гранд, или >300 (оператор сравнения и число).
Вычисляемый критерий – условие поиска, задаваемое в виде логической формулы. В таких критериях используется ссылки на ячейки, содержащих первые значения в соответствующих столбцах списка. Например, =Е5>300; =e5-f5>100.
Логическая формула - состоит из ссылки на ячейку, знака (знаков) сравнения, логической функции (функций). В качестве сравниваемого значения может использоваться константа (константы), ссылка (ссылки) на ячейку, встроенная функция (функции). Результатом логической формулы является значение «ИСТИНА» либо «ЛОЖЬ».
|
|
Примеры задания логических формул:
1. =C5>20
2. =D16>H16
3. =D16>=$K$2
4. =И(C5>10;C5<50)
5. =ИЛИ(С5<=10;C5>=50)
6. =G4>СРЕДЗНАЧ($А$4:$А$20)
Сложный критерий – критерий, в котором используется несколько условий поиска как по одному так и нескольким полям. Сложный критерий может сочетать критерий сравнения и вычисляемый критерий для разных столбцов списка (но не для одного).
При применении расширенного фильтра, предварительно следует создать блок критериев и если это необходимо подготовить блок вывода.
Подготовка Блока вывода заключается в копировании имен полей (столбцов) из исходного диапазона в свободное поле рабочего листа (в случае вывода информации не по всем исходным полям).
Для создания Блока критериев следует скопировать имена полей (столбцов) в свободную часть таблицы и задать критерии поиска.
Рекомендуется придерживаться следующих правил создания блока критериев.
· Блок критериев должен состоять минимум из двух строк. Первая строка –заголовки столбцов (метки критериев), вторая и нижеследующие строки - критерии.
|
|
· Блок критериев не должен содержать пустых строк.
· Между диапазоном условий и исходным диапазоном следует оставить по крайней мере одну пустую строку.
· При задании критерия сравнения метки критериев (заголовки столбцов в блоке критериев) должны полностью совпадать с заголовками соответствующего столбца в исходном диапазоне.
· При задании вычисляемого критерия метка критерия должна отличаться от заголовка соответствующего столбца в исходном диапазоне, либо быть пустой.
· При задании вычисляемого критерия указывается относительная ссылка[4] на ячейку, расположенную в первой строке столбца, на основании которого выполняется фильтрация.
· Если в вычисляемом критерии для сравнения значений ячеек столбца списка используется адрес какой-либо ячейки таблицы, то его следует оформлять в виде абсолютной ссылки[5] (Пример 3.,6. стр.24).
· В сложных критериях используются логические связки ''И'', ''ИЛИ''. "И" требует обязательного выполнения всех задаваемых условий, "ИЛИ" - выполнения хотя бы одного из задаваемых условий.
· Для задания сложных критериев сравнения запись условий в одной строке означает логическую связку "И"; запись условий в разных строках означает логическую связку "ИЛИ".
|
|
· Для задания сложных вычисляемых критериев используются логические функции "И" либо "ИЛИ" (Пример 4.,5. стр.24).
Способы задания критериев
1. способ. Задание в качестве критерия текстовой или числовой константы.
Общий вид блока критериев: | Метка критерия | |
константа |
В качестве константы может быть использована текстовая строка или числовое значение. Например:
А) для выбора информации только по городским перевозкам
Вид перевозок | |||
городские |
Б) для выбора тех перевозок, у которых пассажирооборот составляет 10000
Пассажирооборот, тыс. пасс | ||
10000 |
2. способ. Задание текстовых критериев в виде шаблона
Знак подстановки | Обозначение | Пример |
? (знак вопроса) | Заменяет один любой символ | д?м задает поиск "дым" и "дом" |
* (звездочка) | Заменяет любое число любых символов | д*а задает поиск "да" и "дата" |
~ (тильда), за которой следует ?, * или ~ | Знак вопроса, звездочка или тильда | Что~? задает поиск “Что?” |
Например, для выбора вида перевозок, начинающихся на букву «м»:
|
|
Вид перевозок | |||
м* | |||
3. способ. Задание количественных критериев с использованием знаков сравнения (= , > , < , <> , <= , >= )
Общий вид блока критериев: | Метка критерия | |
Критерий сравнения |
Например:
А) для выбора записей со средней стоимостью проезда больше 20
Средняя стоимость проезда, руб | |||
>20 |
Б) для выбора данных по выручке, попадающих в интервал от 1 000 до 10 000
Выручка, тыс. руб | Выручка, тыс. руб | ||
>=1 000 | <=10 000 | ||
В) для выбора данных по выручке, не попадающей в интервал от 1000 до 10 000
Выручка, тыс. руб | Выручка, тыс. руб | ||
<1 000 | |||
>10 000 |
Аналогично, в расширенном фильтре можно задавать критерии для разных полей с одновременным выполнением условий или с выполнением хотя бы одного условия.
4. способ. Задание в качестве критерия логических формул.
Общий вид блока критериев: | Метка критерия (или пусто) | |
Вычисляемый критерий |
Например:
А) для выбора записей, содержащих значение средней стоимости проезда > 20
Средняя стоимость проезда (1) | |||
=G4>20 |
Б) для выбора записей с пассажирооборотом больше среднего значения
Пассажирооборот | ||
=D4>СРЗНАЧ($D$4:$D$24) |
В) для выбора записей со значением выручки от 1000 до 10 000
Выручка | |||
=И(F4>=1000000;F4<=10000000) |
Г) для выбора записей со значением выручки, не попадающей в интервал от 1000 до 10 000
Выручка | |||
=ИЛИ(F4<1000000;F4>10000000) |
Дата добавления: 2021-01-21; просмотров: 830; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!