Создание и ведение списков – ЧАСТЬ 8

? звездочка (*) заменяет любую последовательность символов;

? вопросительный знак (?) заменяет любой символ.

Так, для поиска всех фамилий, начинающихся  с буквы В, условие  отбора  примет вид больше или равно  В*. Если вопросительный  знак (?) или звездочку (*) нужно включить в условие как литерал,  введите перед ними тильду  (~).

Вычисление итогов после применения автофильтра

Необходимо учитывать,  что при применении  обычных статистических  функций, таких как СУММА (SUM), СРЕДНЕЕ (AVERAGE), МИН  (MIN), МАКС (МАХ), вычисления производятся  над данными списка, а не только для строк, отобранных фильтром. Чтобы выполнить  вычисления    только    для    строк,    удовлетворяющих    условиям    отбора, используйте    функцию  ПРОМЕЖУТОЧНЫЕ.ИТОГИ   (SUBTOTAL)   с   подходящим аргументом номер_фуикции. Информация об этой функции  приведена  в разделе «Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ» данной главы.

Удаление автофильтра

Чтобы  отменить автофильтр для  конкретного  столбца,  раскройте   соответствующий список автофильтра и выберите  в нем пункт Все (Аll). Чтобы отменить все примененные  автофильтры, выберите команду Данные ? Фильтр ? Отобразить все (Data ? Filter

? Show All). Для удаления всех кнопок автофильтров  выберите  команду Данные ? Фильтр ? Автофильтр (Data ? Filter ? AutoFilter) — тем самым вы снимете  флажок рядом с командой Автофильтр.

Расширенный фильтр

Команда Расширенный фильтр (Advanced Filter) предоставляет более  широкое поле деятельности по сравнению с командой Автофильтр:

? Можно задавать  критерий фильтрации для нескольких столбцов, соединяя логическим оператором ИЛИ (OR) два  и более условия отбора.

? Можно задавать три и  более условия  для конкретного столбца с  использованием по крайней мере одного логического оператора ИЛИ.

? Допускается  задание вычисляемых условий.   Например, можно   найти   только  тех сотрудников, у которых оклад хотя бы на 25% выше среднего.

ПРИМЕЧАНИЕ

Нельзя извлечь строки из списка и вставить их в отдельный лист, хотя диапазон условий не обязан  находиться  на  текущем  листе.  После   извлечения  строк  вы,  конечно,  вправе копировать и перемещать их в другое место.

Задание диапазона условий

Команда Расширенный фильтр, в отличие   от команды Автофильтр,  требует задания условий отбора строк в отдельном диапазоне рабочего листа. Поскольку при фильтрации скрываются целые  строки, диапазон  условий  лучше  поместить выше списка или   на отдельном  листе. Диапазон  условий  должен  содержать  по  крайней  мере  две строки. Введите один или несколько заголовков столбцов в верхней строке, а условия отбора — во  вторую и  последующие строки. За исключением вычисляемых  условий,  заголовки в диапазоне условий  должны  совпадать с  заголовками столбцов  в  списке (они могут различаться  регистром  букв     и   форматированием).   Для   обеспечения  точности  эти заголовки  лучше всего создавать, выделяя  заголовки  столбцов в  списке и  копируя их  в

верхнюю строку диапазона условий  с помощью команд Копировать и  Вставить меню

Правка.

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

Наложение условий на два столбца с помощью оператора ИЛИ

На рис. 28.23 показан список домов для продажи. (Подчеркнутые числа в  столбце А являются   гиперссылками  на    фотографии    домов.)    Предположим,   что    для    нас привлекательны дома с размером участка  (столбец F) не меньше двух  акров.  Интерес представляют также дома и с меньшим участком, расположенные в округе U (столбец К), в  непосредственной близости от школ. Чтобы отобрать предложения,  соответствующие любому из  этих двух  критериев, мы создали диапазон  условий  в  трех вставленных над списком новых строках (рис. 28.24).

Рис. 28.23. Мы хотим использовать команду Расширенный фильтр для поиска домов в этом списке, удовлетворяющих определенным условиям

Если вы выберете  команду Данные ? Фильтр ? Расширенный фильтр (Data ? Filter

? Advanced Filter) и  заполните диалоговое окно Расширенный фильтр, как показано на рис. 28.25, Excel отобразит отфильтрованный  список, изображенный на рис. 28.26.

Некоторые поля в  столбце F в  отфильтрованном списке пустые, поскольку  в  исходных

данных отсутствует информация об участках для некоторых домов. Excel рассматривает пустые значение как нулевые (и, следовательно, меньше, чем 2) и включает такие строки в конечный список,  если их поля Округ содержат код U.

Рис. 28.24. Диапазон условий в ячейках А1 :ВЗ позволяет отобрать  дома с участком не менее двух акров или расположенные в школьном округе U

Рис.  28.25.  В  диалоговом  окне  Расширенный  фильтр  установите   переключатель  в положение   Фильтровать   список   на   месте   (Filter    The   List,   In-Place)   и   укажите местонахождение списка и диапазона условий

Рис.  28.26.  После  нажатия  кнопки  ОК  в  диалоговом  окне   Расширенный   фильтр  Excel

отфильтрует список и покажет интересующие нас дома

Подобно команде Автофильтр, команда Расширенный фильтр скрывает все отсеянные строки, а номера отобранных строк отображает  контрастным цветом. Чтобы выяснить, сколько      строк      удовлетворяют      заданным      условиям,      используйте     формулу ПРОМЕЖУТОЧНЫЕ.ИТ0ГИ(3;А:А), конечно, при условии,  что столбец  А  включен в отфильтрованный список.

Обратите  внимание, что два условия  на рис. 28.24 введены на разных  строках. Если вы поместите  их на одном уровне, Excel отберет только те строки, для которых выполняются оба  условия.   Другими  словами, условия   на  одной  строке  считаются соединенными логическим оператором  И,  а   условия   на  разных  строках  считаются соединенными оператором ИЛИ. При этом количество строк с условиями может быть любым.

Оба условия введены как простой текст. Значение U ниже заголовка Округ предписывает Excel найти  строки,  в которых значение в столбце Округ начинается с буквы U. (Другими словами, после буквы U неявно  присутствует  символ  шаблона * (звездочка).) Если вы хотите задать точное  совпадение с буквой U, введите  ="=U". Эта неуклюжая формула отображается в  ячейке как =11. Условие >=2 ниже заголовка Участок указывает  найти строки,  в которых значение в столбце Участок больше или равно 2. В числовых условиях применимы любые операторы сравнения (=, >,  <, >=, <=). При этом вы вправе   ввести число  без знака равенства, если нужно точное совпадение.

Учтите,  что   пустая  ячейка   в    диапазоне условий   означает  «любое   значение»  для соответствующего столбца.  Если вы случайно  включите   пустую  строку  в   диапазон условий, получите неотфильтрованный список.

СОВЕТ  

В том случае, когда диапазон условий находится на одном листе со  списком, Excel назначает  этому  диапазону  имя  Критерии  (Criteria)  сразу  после  применения  фильтра.  Вы можете использовать это имя для перемещения по рабочему листу. Например, если нужно вернуться  в  диапазон  для  изменения  условия,  нажмите  клавишу  F5  и  выберите  пункт Критерии (Criteria) в диалоговом окне Переход (Go To).

Использование трех операторов ИЛИ для одного столбца

Предположим, что  нам  требуется  выбрать из   списка все дома,   находящие в   трех школьных округах — U, F или  Т. В этом случае  следует включить   в диапазон условий только заголовок Округ и ввести   буквы  U, F и  Т в  трех строках непосредственно ниже заголовка. После  применения  команды Расширенный фильтр Excel представит  список, показанный на рис. 28.27.

Рис.  28.27.  Используя  диапазон  условий  А1:А4,  мы  сократили  список,  и  теперь  в  нем представлены только дома, расположенные в школьных округах L), F или Т

Совместное использование операторов ИЛИ и И

Если вы хотите ограничить отображаемый список домами в школьных округах F или Т с полезной  площадью  не  менее  2000  квадратных   футов, создайте  диапазон условий

аналогично  рис. 28.28. Заметьте, условие >=2000 введено  в двух  строках, поскольку для каждого школьного округа (F и  Т) мы хотим видеть   в  списке дома площадью 2000 квадратных футов или больше.

Рис. 28.28. Чтобы увидеть список домов площадью не менее 2000  квадратных футов в школьных округах F или Т, нужно повторить  условие  >=2000 в каждой строке диапазона условий

СОВЕТ

При каждом выполнении команды Расширенный фильтр Excel анализирует полный список, а не текущее множество ранее  отфильтрованных строк. Вследствие этого перед изменением фильтра совсем не обязательно использовать команду Отобразить все (Show All).

Наложение нескольких условий на один столбец

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

Например, чтобы получить выборку  из домов стоимостью  от $50 000 до $90 000, диапазон условий должен иметь следующий вид:

>=50000                                          <=90000

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

<=50000

                                               >=90000                                                                         

Вычисляемые условия

Вычисляемые условия  отличаются от обычных условий  сравнения  тем, что  позволяют оперировать значениями, возвращаемыми формулой. При поиске домов,

цена  которых  превышает $100  000,  вычисляемые  условия  не  требуются.  Однако  для домов,  оцененных   ниже  медианы  индивидуальных    стоимостей  всех  домов,  без вычисляемых условий уже не обойтись.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий