Материал: MS EXCEL в расчетных задачах(учебник,по которому выполнены работы )

67

неравенства заданы в окне рис 4.5.б, условия соединены знаком логической операции И.

Р

Рис. 6.4.а

Рис. 6.4.б

6.4.2. ПОИСК ТЕКСТОВЫХ ЗНАЧЕНИЙ

Для нахождения всех текстовых значений столбца, попадающих в заданный алфавитный интервал необходимо раскрыть список критериев этого столбца и выбрать элемент Условие...

В диалоговом окне Пользовательский автофильтр следует создать два условия, соединенные логическим И. Например, если нам нужно выбрать работников с фамилиями начинающимися на С и Т, нужно установить фильтр по столбцу ФИО, задав в диалоговом окне два условия:

68

больше или равно С

И

меньше или равно Т

Т.к. отдельных букв С и Т в раскрывающемся меню нет, их надо просто ввести в соответствующие поля диалогового окна (рис. 6.4.б).

Та же задача может быть решена другим способом: по полю ФИО в диалоговом окне Пользовательский автофильтр следует создать два условия, соединенные логическим ИЛИ

начинается с С

6.3.3. УДАЛЕНИЕ АВТОФИЛЬТРОВ

Для удаления фильтра по столбцу нужно в раскрывающемся списке критериев этого столбца выбрать параметр Выделить все. Для удаления всех действующих фильтров выберите команду Очистить (Вкладка Данные, группа Сортировка и фильтр). Стрелки раскрывающихся списков критериев удаляются при повторном нажатии кнопки Фильтр.

64.4. КОМАНДА РАСШИРЕННЫЙ ФИЛЬТР

Команда Расширенный фильтр позволяет выполнять следующие операции:

создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ.

создавать критерии с тремя или более условиями для заданного столбца, связанными по крайней мере одним союзом ИЛИ.

создавать вычисляемые критерии.

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

69

6.4.5. ИНТЕРВАЛ КРИТЕРИЕВ

Команда Расширенный фильтр в отличие от команды Автофильтр

требует задания критерия в отдельном интервале рабочего листа. Разместить его лучше выше или ниже списка.

Интервал критериев должен состоять не менее чем из двух строк. В первой строке размещаются заголовки столбцов, а во второй и в последующих строках - соответствующие критерии фильтра. Если не используются вычисляемые критерии, то заголовки в интервале критериев должны точно совпадать с заголовками столбцов списка. Для этого можно выделить заголовки столбцов списка и скопировать их в первую строку интервала с помощью команд Копировать и Вставить из группы

Буфер обмена вкладки Главная.

Пример критерия с двумя условиями

Пусть из списка персонала, приведенного на рис 6.1, нужно выделить как работников с тарифом (столбец Тариф) выше 40 , так и работников, отработавших более 100 час (столбец Отработано час.).

Выполним следующие действия:

Вставим несколько строк для интервала критериев выше списка.

Создадим интервал критериев в первых трех строках, как показано на рис. 6.5.

В диалоговом окне Расширенный фильтр(кнопка Дополнительно из группы Сортировка и фильтр) введем параметры в соответствии с рис

.6.5

Убедитесь, что установлен переключатель фильтровать список на месте, щелкните ОК

Рис.6.5

70

Результат фильтрации приводится на рис.6.6.

По команде Расширенный фильтр, также как и по команде Автофильтр скрываются все строки, не удовлетворяющие критериям фильтра, выводятся номера строк в контрастирующем цвете, а в строке состояния выводится сообщение Фильтр: отбор.

На рис.6.6 в ячейках А1:В3 критерии записаны в виде обыкновенных меток. Под заголовком Тариф введено >40, под заголовком Отработано час. введено >100.

Обратите внимание, что критерии в данном примере располагаются на отдельных строках.

Интервал критериев может содержать любое число условий, которые интерпретируются следующим образом:

Условия на одной строке объединяются по правилу И Условия на отдельных строках объединяются по правилу ИЛИ.

Рис.6.6

Пример критерия с условиями, объединяемыми по правилам И и ИЛИ одновременно.

На рис.6.7 показан критерий и результат фильтрации для решения следующей задачи. Показать всех работников, фамилия которых начинается на букву “П“, у которых Сумма больше 3000 или Разряд выше 4.

Буква П присутствует в критерии в обеих строках поскольку записи, удовлетворяющие как условию Сумма> 3000, так и условию Разряд >4, должны одновременно удовлетворять критерию на фамилию (первая буква -

П).

71

Рис.6.7

6.4.6. ТЕКСТОВЫЕ КРИТЕРИИ

Интерпретация текстовых критериев не так очевидна, как может показаться. Обработка таких критериев выполняется Excel по следующим правилам:

Если задана одна буква, то по равенству (=) будут найдены все начинающиеся на эту букву значения. Например, по критерию =М будут найдены Мария, Максим, М.Шолохов и т.п.

По условию больше (>) или меньше (<), будут найдены значения, которые располагаются в алфавитном порядке соответственно после или до заданного значения. Например, по критерию > М в столбце ФИО будут выделены работники с фамилиями, начинающимися с букв от М до Я. Противоположный критерий <М позволяет отобрать фамилии, начинающиеся с букв от А до Л.

По критерию =“=текст” выделяются значения, точно совпадающие с заданным выражением текст. Например, чтобы найти записи с фамилией Иванов, следует задать =“=Иванов”. Если задать не формулу, а просто Иванов, то будут выделены записи с фамилиями Иванов, Иванова, Ивановский и т.п.

Символы шаблона обрабатываются так же, как в автофильтре.

6.4.7.ВЫЧИСЛЯЕМЫЕ КРИТЕРИИ

Вычисляемыми являются критерии, включающие более сложные операции, чем простое сравнение значения столбца с константой. Если для поиска работников с зарплатой более 3000р. достаточно простого критерия, то для поиска работника с зарплатой, большей медианы (средней), требуется вычисляемый критерий.

Отметим три правила применения вычисляемых критериев:

1.Заголовок столбца над вычисляемым критерием не должен совпадать с заголовком какого-либо столбца в анализируемом списке. Он может быть