Фильтрация данных с помощью расширенного фильтра
Для задания более сложных условий, когда внутри одного поля требуются выбрать больше двух значений, используются расширенные (пользовательские) фильтры.
При этом следует учитывать следующее:
- Чтобы наложить условия отбора, выполняемые одновременно на несколько столбцов, введите условия в ячейки, расположенные в одной строке диапазона условий.
- Если вы введёте условия в ячейки, расположенные в разных строках диапазона условий, то отобранные записи будут удовлетворять, хотя бы одному из них.
- Чтобы задать для отдельного столбца три или более условия отбора, введите их в ячейки, расположенные в смежных строках.
- В условии фильтрации можно использовать возвращаемое формулой значение.
- При создании условия отбора с помощью формулы оставьте пустой ячейку заголовка столбца условий.
- Формула, используемая для создания условия отбора, должна использовать относительные ссылки на соответствующие поля первой записи списка. Все остальные ссылки в формуле должны быть абсолютными.
- Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
- Скопируйте содержимое листа «Товары» в новый лист рабочей книги и присвойте ему имя «Расширенный фильтр».
5.3.1. Выведите марки товаров, которых на складе или много (> 100) или мало (<10) и поместите их в новое место.
F Выполнение задания
- В ячейках В82:D87 организуйте область критериев и область вывода данных (рис. 5.9).
- Для этого в ячейки D82, В87:С87 скопируйте содержимое ячеек заголовка списка, а в ячейки D83 и D84 вручную введите критерий отбора. В ячейке D87 напечатайте текст заголовка дополнительного столбца.
Примечание. Настоятельно рекомендуем вам текст ячеек копировать, а не писать.Даже лишний пробел не даст вам выполнить задуманную выборку. Три четверти ошибок, не позволяющих выполнить расширенный фильтр, возникает из-за пренебрежения к таким деталям.
Рис. 5. 9. Результат выполнения расширенного фильтра для условия "На складе" и (>100 и <10) - фрагмент
- Курсор установите в любой ячейке списка области A1:J78.
- На вкладке Данные в группе Сортировка и фильтр выберите команду Дополнительно (рис. 5.1), чем вы запустите механизм расширенной фильтрации (рис. 5.10).
Рис. 5. 10. Задание параметров расширенного фильтра для задания 5.3.1
Исходный диапазон $A$1:$J$78 должен установиться автоматически. Если этого не произошло, то выделите его сами.
- В качестве диапазона условий выделите область 'Расширенный фильтр'!$D$82:$D$84. Она включает имя поля «На складе» и условия «>100» и «<10».
Примечание. Текст области 'Расширенный фильтр'! означает название листа рабочей книги. В некоторых случаях, он может и не появляться.
- Установите переключатель скопировать результат в другое место. В поле Поместить результат в диапазон укажите границу верхнего диапазона вывода фильтрованных данных 'Расширенный фильтр'!$B$87:$C$87.
- Щелкните по кнопке ОК. Будут выделены марки товаров и их количество на складе для тех записей, у которых в поле На складе значения превышают 100 или менее 10 и помещены в область, начиная со строки 88 (рис. 5.9).
Автоматическое задание текста в зависимости от значения числа в поле «На складе»
Если имеется излишек товаров, то должно появиться сообщение «Излишек товаров», а при недостатке – сообщение «Недостаток товаров». Кроме того, в первом случае, ячейка должна быть залита желтым цветом, а во втором – красным.