Множественные критерии на основе логических операций
Множественные критерии позволяют поиск записей, удовлетворяющих нескольким условиям. В режиме автофильтра для этого используется диалоговое окно Пользовательский автофильтр[1], где есть возможность определить два условия. Если используется расширенный фильтр, в распоряжении имеется область критериев, которая позволяет задавать разнообразные комбинации условий.
Для наложения нескольких условий поиска в Excel служат логические операции И, ИЛИ ( AND , OR ). В диалоговом окне Пользовательский автофильтр два условия можно соединить с помощью любой из двух логических операций. Расширенный фильтр допускает ввод множества условий, соединенных различными способами. Если условия находятся в одной строке, то они считаются соединенными логической операцией И, если в разных – то логической операцией ИЛИ.
Вычисляемые критерии на основе логических формул
Вычисляемые критерии отличаются от обычных условий тем, что позволяют использовать значения, возвращаемые формулой.
Правила записи вычисляемых критериев:
- Заголовок над вычисляемым критерием должен отличаться от любого имени поля в списке, он может быть пустым или содержать произвольный текст. Если заголовок пустой, он все равно включается в диапазон критерия.
- Ссылки на ячейки, находящиеся вне списка, как правило, должны быть абсолютными.
- Ссылки на ячейки, находящиеся в списке, как правило, должны быть относительными.
|
|
|
Средства фильтрации
Автофильтр
Автофильтр предоставляет простой доступ к мощным средствам рабочего листа.
С помощью элементарных действий мышью можно быстро отфильтровать данные, оставив на экране только то, что необходимо видеть .
В режиме автофильтра строка заголовка таблицы преобразуется в группу раскрывающихся списков, снабженных характерными кнопками ▼, позволяющие задать условия фильтрации для каждого из полей. При раскрытии списка в каком-либо поле выводится список всех уникальных элементов этого поля и некоторые критерии отбора, в том числе пользовательский автофильтр, доступный посредством выбора одной из команд Текстовые фильтры, Числовые фильтры, Фильтры по дате (в зависимости от типа поля) из меню в раскрывающемся списке поля заголовка таблицы.
Замечание
Как автофильтр, так и расширенный фильтр скрывают строки рабочего листа целиком. Если около боковых границ списка имеются другие данные, они могут оказаться спрятанными вместе с записями списка.
Для дальнейшей работы создайте таблицу, которая представлена на рисунке 1.
Рисунок 1 – Таблица исходных данных
Пример 1.
С помощью автофильтра составьте список фирм, у которых Дата счета, между 20.04.2007 и 20.07.2007.
|
|
|
Чтобы перевести список в режим автофильтра необходимо сделать следующее:
1. Выделите диапазон исходных данных – А1:Е11 (рисунок 2)
Рисунок 2 – Диапазон исходных данных
2. Выберите на ленте вкладку Данные. В группе Сортировка и фильтр выберите команду Фильтр (рисунок 3).
Рисунок 3 – Команда Фильтр
Список будет преобразован соответствующим образом, (рисунок 4). Обратите внимание на изменившийся вид верхней строки диапазона – в каждом заголовке появилась характерная кнопка раскрывающегося списка ▼
Рисунок 4 – Список в режиме автофильтра
3. Выберите в раскрывающемся списке нужного поля строки заголовка таблицы команду Текстовые фильтры, Числовые фильтры, Фильтры по дате(согласно типу поля).
4. В открывшемся меню выберите один из пунктов равно, не равно, больше, меньше , междуи т.д. либо пункт Настраиваемый фильтр. Откроется диалоговое окно Пользовательский автофильтр (рисунки 5 и 6).
Рисунок 5 – Выбор критерия фильтрации в меню автофильтра
5. Задайте условие сравнения с помощью раскрывающихся списков. В левых списках задаются способы сравнения, в правых – значения, с которыми производится сравнение (рисунок 6).
|
|
|
6. С помощью переключателя И или ИЛИ задайте способ объединения условий.
7. Нажмите кнопку OK .
Рисунок 6 – Выбор условия в диалоговом окне Пользовательского автофильтра примера1
Результат фильтрации отображен на рисунке 7
Рисунок 7 – Результат работы Пользовательского автофильтра примера 1
Чтобы отменить фильтрацию, выберите на ленте вкладку Данные. В группе Сортировка и фильтр выберите команду Очистить (рисунок 8).
Рисунок 8 – Отмена фильтрации
Выход из режима автофильтра производится так же, как и его включение. Выберите на ленте вкладку Данные. Вгруппе Сортировка и фильтр, команду Фильтр.
Расширенный фильтр
Расширенный фильтр требует большей работы, чем автофильтр, однако и предоставляемые им возможности фильтрации шире. Можно более свободно применять операции И, ИЛИ, а также составлять вычисляемые критерии.
Для использования расширенного фильтра необходимо создать диапазон критериев, где задаются условия поиска данных.
Правила записи диапазона критериев:
- Верхняя строка диапазона должна содержать заголовки полей, которые в точности повторяют по написанию заголовки полей в области данных. Проще всего можно обеспечить точность совпадения путем копирования.
|
|
|
- Не требуется включать все имеющиеся заголовки и сохранять их порядок.
- Кроме того, диапазон критериев должен включать хотя бы одну пустую строку ячеек сразу под заголовками. В этой строке (или строках) записываются условия поиска, причем все, что записано в столбце под заголовком поля, относится именно к этому полю.
- Для ввода условий можно использовать несколько строк.
Рассмотрим использование расширенного фильтра на примере 2.
Пример 2.
С помощью расширенного фильтра составьте список фирм, Сумма в счете у которых больше 50000 рублей.
1. В свободной области электронной таблицы создайте таблицу критериев (см. рекомендации по составлению таблицы критериев и рисунок 9.
Рисунок 9 – Создание таблица критериев
2. В свободной области электронной таблицы создайте выходной документ (где будет напечатан результат работы расширенного фильтра), в котором указываются имена тех полей из исходной таблицы базы данных, для которых осуществляется фильтрация (рисунок 10).
Рисунок 10 – Создание выходного документа
3. Выберите на ленте вкладку Данные. В группеСортировка и фильтр выберите команду Дополнительно. Появится диалоговое окно Расширенный фильтр (рисунок 11).
Рисунок 11 – Диалоговое окно Расширенный фильтр
4. Выберите одно из положений переключателя Обработка. При выборе положения фильтровать список на месте не удовлетворяющие критерию записи будут скрыты, при выборе положения скопировать результат в другое место список данных останется нетронутым, а отобранные записи будут помещены в указанный диапазон.
5. Введите диапазон списка данных в поле Исходный диапазон (ввод лучше всего осуществлять путем протаскивания указателя мыши по диапазону исходного списка данных) – A1:E11.
6. Введите диапазон критериев в поле Диапазон условий – G2:G3.
7. Если выбрано положение скопировать результат в другое место, укажите диапазон на рабочем листе, где необходимо вывести результаты работы расширенного фильтра и укажите его в поле Поместить результат в диапазон –B13:C13.
8. Установите флажок Только уникальные записи, если не хотите, чтобы одинаковые записи повторялись (будет выводиться только первая из всех удовлетворяющих критерию одинаковых записей).
9. Нажмите кнопку OK .
Результаты работы расширенного фильтра отобразятся на рабочем листе (рисунок 12).
Рисунок 12 – Результат работы расширенного фильтрапримера 2
Пример 3.
Отобрать записи фирм, Сумма в счете у которых от 50000 до 90000 рублей.
1. Введите диапазон базы данных (А1:Е11).
2. Введите диапазон условий (G2:H3). В этом примере два условия соединяются логической операцией И (И Сумма в счете > 50000, И Сумма в счете < 90000), поэтому в диапазоне условия они располагаются в одной строке (рисунок 13).
3. Поместите результат в диапазон (В13:С13) (рисунок 14).
Рисунок 13 – Пример с использованием логической операцииИ
Рисунок 14 - Результат работы расширенного фильтра примера
Пример 4.
Определить, у каких фирм Сумма в счете от 30000 рублей, и Дата счета позже 20.04.2007.
1. Введите диапазон базы данных (А1:Е11).
2. Введите диапазон условий (G2:H3). В этом примере два условия соединяются логической операцией И (И Сумма в счете > 30000, И Дата счета >= 20.04.2007), поэтому в диапазоне условия они располагаются в одной строке.
3. Поместите результат в диапазон (В13:D13) (рисунок 15).
Рисунок 15 – Выполнение примера 4
Результат фильтрацииотображен на рисунке 16.
Рисунок 16 - Результат работы расширенного фильтра примера 4
Пример 5.
С помощью расширенного фильтра составьте список фирм, у которых счета, выставленные раньше июля 2007, на сумму от 10000 руб., рассматривая лишь фирмы со специализацией, соответствующей образцу Быт* (Бытовая техника или Бытовая химия).
1. Введите диапазон базы данных (А1:Е11).
2. Введите диапазон условий (G2:I3). В этом примере три условия соединяются логической операцией И, поэтому в диапазоне критерия они располагаются в одной строке в трех столбцах.
3. Поместите результат в диапазон (В13:E13) (рисунок 17).
Рисунок 17 – Выполнение примера 5.
Пример 6.
Отобрать фирмы и сумму в счете, которые специализируются на Цветах и Косметике.
В этом примере условия соединяются логической операцией ИЛИ (ИЛИ Цветы, ИЛИ Косметика), следовательно, в диапазоне критерия они располагаются в разных строках одного столбца (одного поля - Специализация).
Диапазон условий (G2:G4). Выполнение примера отображено на рисунке 18.
Рисунок 18 - Выполнение примера 6
Пример 7.
Дата добавления: 2021-01-21; просмотров: 165; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!