Т.к. отдельных букв С и Т в раскрывающемся меню нет, их надо просто ввести в соответствующие поля диалогового окна (рис 3.5б).
Та же задача может быть решена другим способом: по полю ФИО в диалоговом окне Пользовательский автофильтрследует создать два условия, соединенные логическим ИЛИ
начинается с С
начинается с Т
При создании критериев можно использовать два символа шаблона: звездочка (*) и вопросительный знак (?).
Символ * используется для представления любой последовательности символов.
Символ ? ‑ для представления любого отдельного символа.
В таблице 3.1 даны примеры использования символов шаблона, удовлетворяющих таким критериям (пропускаемых фильтром):
Допустимы любые комбинации символов шаблона.
Таблица 3.1
Фильтр
Пропускаемые значения
=П?пов
Попов, Пупов
=С??оров
Сидоров, Суворов, Створов
=Б*в
Беляев, Белов, Бобров
УДАЛЕНИЕ АВТОФИЛЬТРОВ
Для удаления фильтра по столбцу нужно в раскрывающемся списке критериев этого столбца выбрать параметр Все. Для удаления всех действующих фильтров выберите команду Отобразить все из меню Данные >Фильтр. Стрелки раскрывающихся списков критериев удаляются при повторном выполнении команды Автофильтр из менюДанные >Фильтр.
КОМАНДА РАСШИРЕННЫЙ ФИЛЬТР
Команда Расширенный фильтр позволяет выполнять следующие операции:
§ создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ.
§ создавать критерии с тремя или более условиями для заданного столбца, связанными по крайней мере одним союзом ИЛИ.
§ создавать вычисляемые критерии.
Кроме того, команда Расширенный фильтр дает возможность автоматически извлекать строки и помещать их копии в другое место текущего рабочего листа.
ИНТЕРВАЛ КРИТЕРИЕВ
Команда Расширенный фильтр в отличие от команды Автофильтр требует задания критерия в отдельном интервале рабочего листа. Разместить его лучше выше или ниже списка.
Интервал критериев должен состоять не менее чем из двух строк. В первой строке размещаются заголовки столбцов, а во второй и в последующих строках - соответствующие критерии фильтра. Если не используются вычисляемые критерии, тозаголовки в интервале критериев должны точно совпадать с заголовками столбцов списка. Для этого можно выделить заголовки столбцов списка и скопировать их в первую строку интервала с помощью команд Копировать и Вставить из меню Правка.
Пример критерия с двумя условиями
Пусть из списка персонала, приведенного на рис 3.1 нужно выделить как работников с тарифом (столбец Тариф) выше 40 , так и работников, отработавших более 100 час (столбецОтработано час.).
Выполним следующие действия:
§ Вставим несколько строк для интервала критериев выше списка так, как это показано на рис.3.1.
§ Создадим интервал критериев в первых трех строках, как показано на том же рисунке.
§ Выполним команду Расширенный фильтриз меню Данные > Фильтри введемпараметры в соответствии с рис .3.6
§ Убедитесь, что установлен переключатель фильтровать список на месте, щелкните ОК
Рис .3.6
Результат фильтрации приводится на рис.3.7
По команде Расширенный фильтр, также как и по команде Автофильтр скрываются все строки, не удовлетворяющие критериям фильтра, выводятся номера строк в контрастирующем цвете, а в строке состояния выводится сообщение Фильтр: отбор.
На рис.3.7 в ячейках А1:В3 критерии записаны в виде обыкновенных меток. Под заголовком Тариф введено >40, под заголовком Отработано час. введено >100.
Обратите внимание, что критерии в данном примере располагаются на отдельных строках.
Интервал критериев может содержать любое число условий, которые интерпретируются следующим образом:
Условия на одной строке объединяются по правилу И
Условия на отдельных строках объединяются по правилу ИЛИ.
Рис.3.7
Пример критерия с условиями, объединяемыми по правилам И и ИЛИ одновременно.
На рис.3.8 показан критерий и результат фильтрации для решения следующей задачи. Показать всех работников, фамилия которых начинается на букву “П“, у которых Сумма больше 3000 или Разряд выше 4.
Буква П присутствует в критерии в обеих строках поскольку записи, удовлетворяющие как условию Сумма> 3000, так и условию Разряд >4, должны одновременно удовлетворять критерию на фамилию (первая буква - П).
Рис.3.8
ТЕКСТОВЫЕ КРИТЕРИИ
Интерпретация текстовых критериев не так очевидна, как может показаться. Обработка таких критериев выполняется Excel по следующим правилам:
§ Если задана одна буква, то по равенству (=) будут найдены все начинающиеся на эту букву значения. Например, по критерию =М будут найдены Мария, Максим, М.Шолохов и т.п.
§ По условию больше (>) или меньше (<), будут найдены значения, которые располагаются в алфавитном порядке соответственно после или до заданного значения. Например, по критерию > М в столбце ФИО будут выделены работники с фамилиями, начинающимися с букв от М до Я. Противоположный критерий <М позволяет отобрать фамилии, начинающиеся с букв от А до Л.
§ По критерию =“=текст” выделяются значения, точно совпадающие с заданным выражением текст. Например, чтобы найти записи с фамилией Иванов, следует задать =“=Иванов”. Если задать не формулу, а просто Иванов, то будут выделены записи с фамилиями Иванов, Иванова, Ивановский и т.п.
§ Символы шаблона обрабатываются так же, как в автофильтре.
ВЫЧИСЛЯЕМЫЕ КРИТЕРИИ
Вычисляемыми являются критерии, включающие более сложные операции, чем простое сравнение значения столбца с константой. Если для поиска работников с зарплатой более 3000р. достаточно простого критерия, то для поиска работника с зарплатой, большей медианы(средней), требуется вычисляемый критерий.
Отметим три правила применения вычисляемых критериев:
1. Заголовок столбца над вычисляемым критерием не должен совпадать с заголовком какого-либо столбца в анализируемом списке. Он может быть пустым или содержать любой другой текст. Это правило противоположно тому, что требуется при задании обычных критериев.
2. Ссылки на ячейки вне списка должны быть абсолютными.
3. Ссылки на ячейки внутри списка как правило относительны.
ССЫЛКИ НА ЯЧЕЙКИ ВНЕ СПИСКА
Пусть требуется показать всех работников, заработок которых больше, чем средний заработок членов бригады 21. Для этого выполним следующую последовательность действий:
1) В ячейку G1 занести значение 21.
2) В ячейку J2 ввести формулу =СУММЕСЛИ($G$5: $G$40;G1;$J$5:$J$40) для того, чтобы вычислить суммарный заработок работников бригады 21.
3) В ячейку J3 ввести формулу =СЧЁТЕСЛИ($G$5: $G$40;G1) для того, чтобы вычислить число работников бригады 21.
4) В ячейку J1 ввести формулу =J3/J2 для вычисления среднего заработка работников бригады 21.
5) В ячейку А1 введите текст: “Сумма больше, чем среднее по Бригаде”.
6) В ячейку А2 введите формулу =J6>$J$1 (Ссылка на ячейку вне списка абсолютная!)
7) Поставьте курсор на любую ячейку внутри списка.
8) Выберите команду Расширенный фильтриз меню Данные > Фильтр.
9) В качестве критерия (поле Диапазон условий) введите $A$1:$A$2, затем нажмите кнопку ОК.
В результате на рабочем листе отображается список сотрудников, чей средний заработок выше, чем у членов бригады 21 (рис.3.9).
Рис.3.9
Рассмотренный пример иллюстрирует следующее:
· Заголовок интервала критерия не совпадает ни с одним заголовком в списке, иначе фильтр работал бы неправильно.
· В формуле только ячейка J6 (адрес первой записи) сравнивается c J1, но при обработке фильтра сравнение выполняется последовательно для всех ячеек столбца J, начиная с первой ячейки J6 и до конца списка.
· Ссылка на ячейку J1 абсолютная. Если в ячейке задать формулу =J6>J1 с относительной ссылкой, то ячейка J6 сравнивается с ячейкой J1, ячейка J7 - с ячейкой J2 и т.д., что очевидно неправильно.
На значение, возвращаемое формулой критерия в ячейке А2, можно не обращать внимания. В данном случае значение ЛОЖЬ соответствует тому, что заработок первого работника меньше содержимого ячейки J1.
ССЫЛКИ НА ЯЧЕЙКИ ВНУТРИ СПИСКА
Пример1. Для списка, приведенного на рис 3.1, требуется найти работников, отработавших в среднем больше 8 часов в день. Для этого разделим число отработанных часов на количество рабочих дней (23) и поместим в формулу критерия:
=I6/23>8
В формуле используются относительные ссылки.
Пример2. Пусть задан список (рис.3.10). Требуется показать аудитории, не укомплектованные принтерами, т.е., в которых число компьютеров больше, чем число принтеров.
Критерий показан на том же рисунке, он задан формулой =B7>C7, ссылки на ячейки относительные. Это обеспечивает перебор всех значений списка, т.е. на втором шаге сравнивается В8 и С8, на третьем В9 и С9 и т.д. до конца списка.
Рис. 3. 10
Рис.3.10
Команда Расширенный фильтр позволяет копировать отфильтрованные строки в другое место на рабочем листе. Для этого в диалоговом окне Расширенный фильтр (рис. 3.11) необходимо нажать кнопку Скопировать результат на другое место и в полевводаПоместить результат в диапазоне указать соответствующий интервал.Результат фильтрации показан на том же рисунке.
Рис. 3.11
ПОДВЕДЕНИЕ ИТОГОВ
Часто бывает необходимо знать промежуточные итоги для тех или иных таблиц. Допустим, мы хотим подсчитать сумму заработка каждой бригады.
Для этого необходимо выполнить следующие действия:
1) Отсортировать таблицу по столбцу
2) Поместить курсор на любую ячейку внутри списка.
3) Выбрать команду Данные >Итоги…
4) В диалоговом окне Промежуточные итогиустановите необходимые параметры (рис.3.12). После этого нажмите кнопку ОК.
Рис.3.12
В результате Excel создает новые строки промежуточных результатов и структуру таблицы (рис.3.13.). Слева выведены символы структуры- кнопки со знаками плюс и минус. Если щелкнуть мышью по кнопке со знаком минус, то соответствующая область таблицы будет скрыта.
Когда соответствующая часть таблицы скрыта, то линия структуры, которая указывает на эту часть таблицы “схлопнута”, а на кнопке появляется знак плюс. Если щелкнуть по кнопке со знаком плюс, то скрытая часть таблицы будет вновь на экране.
Используя символы уровня строки (кнопки помеченные цифрами 1,2,3), можно просмотреть итоговую таблицу с различной степенью детализации. При нажатой кнопке 1 будет показана таблица с указанием каждого работника, при нажатой кнопке 2 будут показаны итоги по полю Бригада, при нажатой кнопке 3 - только общие итоги.
