Расширенная фильтрация.

Автофильтрация.

Самым простым является использование автофильтра (пункт Автофильтр).При выборе этого пункта слева от него изображается галочка, а в заголовке каждого столбца появится кнопка-треугольник выбора фильтра для данного поля. Щелчок по любой из кнопок выбора повлечет предъявление ниспадающего меню, содержащего список всех имеющихся значений поля. Например, нажатие на кнопку столбца Имя,вызовет меню содержащее значения, встречающиеся в столбце, отсортированные по алфавиту. В большой таблице их может оказаться сотни, что, конечно, сильно затрудняет поиск нужного элемента меню. Ускорить этот процесс можно, нажав клавишу с первой буквой нужного слова. Меню правильно реагирует на такое действие, сразу перемещая нас на первую строку с указанной буквой. Если какая-то из кнопок была использована для назначения фильтра, треугольник в ней делается синего цвета (исходный цвет - черный).

Пункты, заключенные в скобки, выполняют функции:

(Все)- предъявляются все данные (фильтр по этому полю отменяется).

(Первые 10)- предъявляется подмножество (например, 10 элементов) данных, находящихся в начале/конце отфильтрованной таблицы. Здесь можно задать любое (а не только 10) число наибольших/наименьших элементов списка или процентов от всего множества данных.

(Условия...) - вызываются средства задания более сложного фильтра. Положим, нужно найти все продажи, совершенные в первом квартале 2001г. Можно установить условие Дата>=1.1.01 И Дата<1.4.01,что позволит отобрать данные о продажах, происшедших с 1.1.2001 по 1.4.2001 (т.е. в январе, феврале и марте).

(Пустые)- предъявляются строки, не содержащие данных в колонке.

(Непустые)- предъявляются только непустые строки в колонке.

Последние два пункта включаются в меню, если в колонке имеются незаполненные клетки.

При задании ключа фильтрации могут использоваться символы шаблона * и ?. Их применение позволяет осуществлять поиск по не полностью заданному ключу. Положим, нам нужно отделить всех сотрудников, фамилии которых начинаются с букв "Пе". Задание шаблона вида "Пе*" покажет нам все строки, содержащие фамилии Петр, Петров, Пешков, Петраков и др Шаблон вида "?????" отделит всех сотрудников с фамилиями, состоящими строго из пяти букв. Шаблон "*цкий" - отфильтрует работников с фамилиями, оканчивающимися на "цкий" и произвольным числом предшествующих знаков.

Пункт Данные ÞФильтр ÞРасширенный фильтрдает возможность задать сложный критерий отбора данных непосредственно в клетках рабочего листа без ограничения числа условий. Этот критерий должен содержать точные названия заголовков колонок (лучше создать их копированием), для которых формируется критерий, а под ними собственно условия фильтрации. Условия, находящиеся в одной строке, объединяются в фильтре логической функцией И, условия, находящиеся на отдельных строках - функцией ИЛИ.Допускается соединение условий И и ИЛИв одном фильтре.

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

Удобно, чтобы между областью условий и областью данных находилась пустая строка (здесь строка 3). Названия колонок и условия фильтрации введем в область А1:В2. Собственно сортировка осуществляется с помощью окна:

Другой фильтр. Пусть требуется показать продажи столов истульев в этот же период времени, т.е. сформировать условие вида (Дата>=1.3.2001 И Дата<1.4.2001 И Товар=стол)

ИЛИ (Дата>=1.3.2001 И Дата<1.4.2001 И Товар=стул).Результат показан на рисунке (даты в области условий повторены). Здесь Исходный диапазон: | $А$5:$Р$181,а Диапазон условий: |$А$1:$С$3|.

Еще фильтр. Нужно выявить продавцов-кандидатов на премирование, Пусть, это работники, имевшие выручку более 50 тыс. руб. или продавшие шкафы (положим, шкафы покупаются плохо).

Если Исходный диапазонзадать прежним, а Диапазон условий-А1:ВЗ, результат фильтрации будет таким, как показано на рисунке. Здесь реализован фильтр Товар=Шкаф ИЛИ Выручка>50.

Отмена действия любого фильтра осуществляется выбором пункта меню Данные ÞФильтр ÞОтобразить все.

Инструмент Расширенный фильтрпозволяет использовать в качестве критерия ссылки на ячейки и формулы. Пусть нам нужно показать продажи на сумму больше средней в полтора раза. В ячейку А2 помещаем выражение:

А2=С5>1,5*СРЗНАЧ($С$5:$С$17),

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

Хотя в самой формуле сравнение происходит только с ячейкой С5, после выполнения действий в окне Расширенный фильтр,оно будет распространено на весь диапазон данных. В Диапазон условийздесь включена область А1:А2, которая обязательно должна содержать заголовок, не совпадающий ни с каким из заголовков собственно данных, в том числе и даже пустую ячейку (в нашем случае - слова Больше 1,5 средней).В ячейке А2 может быть выведено слово ИСТИНАили ЛОЖЬ,в зависимости от того, каково значение ячейки С5 и на него не нужно обращать внимания.