Основы современных компьютерных технологий

         

Фильтрация списка


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

Excel предоставляет пользователю два способа фильтрации: с помощью автофильтра и с помощью расширенного фильтра.

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

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

  • когда условие отбора должно одновременно применяться к ячейкам двух и более столбцов;
  • когда к ячейкам одного столбца необходимо применить три и более условий отбора:
  • когда в условии отбора используется значение, полученное в результате вычисления заданной формулы.
  • Чтобы обработать таблицу с помощью автофильтра, необходимо вначале выбрать в качестве активной любую ее ячейку. После этого в меню Данные \ Фильтр (Data\Filter) выполнить команду Автофильтр (AutoFilter). Как только команда будет выполнена, в

    168

    первой строке таблицы рядом с именем каждого поля появятся кнопки со стрелками. Нажатие любой из этих кнопок приводит к открытию соответствующего набора строк. Каждая строка набора представляет собой одно из неповторяющихся значений ячеек выбранного столбца. Дополнительно к значениям в каждом наборе будут присутствовать строки с именами [Все] ([АН]), [Первые 10...] ([Тор 10...]) и [Условие...] ([Custom...]).

    Чтобы отфильтровать список по одному из имеющихся значений поля, достаточно выбрать это значение из развернувшегося набора строк. В списке остаются только те записи, которые содержат выбранное значение в данном столбце. Номера строк отфильтрованных таким образом записей и стрелка кнопки выбранного столбца будут высвечиваться синим цветом. Эти записи можно снова подвергнуть фильтрации по этому же или другому полю и г. д.

    Если для фильтрации по определенному полю выбрать строку [Первые 10...] ([Тор 10...]), то на экране появится диалоговое окно Наложение условия по списку (Тор 10 AutoFilter), в котором пользователь имеет возможность изменить условие: выбрать от 1 до 500 наибольших или наименьших элементов этого поля.
    После этого па экране монитора отображается выбранное количество записей, отвечающих заданному условию.

    Чтобы отфильтровать список по двум и более значениям, встречающимся в столбце, необходимо выбрать строку [Условие...] ([Custom...]). В появившимся диалоговом окне Пользовательский автофильтр (Custom AutoFilter) можно задать это условие отбора. Для этого используются операторы сравнения (>, =,

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

  • Последовательно нажимать кнопки с синими стрелками (т.е. кнопки полей, по которым была выполнена фильтрация) и в открывающихся при этом наборах строк выбирать строку [Все] ([АИ]).


  • Выполнить команду Показать все (Show All) меню Данные \ Фильтр (Data \ Filter).


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


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

    Диапазон критериев оформляется следующим образом: в первой строке записываются (или копируются) имена полей списка, для которых задаются условия отбора, а во второй и последующих строках вводятся непосредственно сами условия отбора.

    С использованием рис. 13.1 рассмотрим некоторые примеры задания условий отбора (диапазонов критериев) расширенного фильтра.

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



    169

    поле "Должность" значение "Инженер-программист", в поле "ФИО сотрудника" - "Сидоров М.А.", а в поле "Дата получения премии" - все даты позже 15.02.97. В этом случае диапазон критериев будет выглядеть следующим образом:

      А С F
    55 ФИО
    сотрудника
    Должность Дата получения премии
    56 Сидоров М.А. Инженер- программист > 15.02.97
    Обратите внимание на следующее:

    1. Диапазон критериев начинается с 55 строки (отделен от списка рис. 13.1 одной пустой строкой).

    2. Все условия отбора размещены в одной строке. Тем самым задается их взаимодействие по схеме "И" (требуется одновременное их выполнение).

    Для соединения условий отбора по схеме "ИЛИ" необходимо каждое из них разместить в отдельной строке:

      А С F
    59 ФИО
    сотрудника
    Должность Дата получения премии
    60 Сидоров М.А.    
    61   Инженер-программист  
    62     >15.02.97
    При задании оформленного таким образом диапазона критериев из списка будут выбраны все записи, содержащие либо значение "Сидоров М.А." в столбце "ФИО сотрудника", либо "Инженер-программист" в столбце "Должность", либо имеющие значение ">15.02.97" в столбце "Дата получения премии". Именно так задаются разные условия отбора на несколько полей списка одновременно.

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

      А С
    63 ФИО сотрудника Размер премии
    64 Петров С.А. >300
    65 Иванов А.С. >300
    из списка будут отобраны записи, содержащие значения "Петров С.А." и "Иванов А.С." в столбце "ФИО сотрудника" и имеющие размер премии более 300 рублей.

    170

    Пример 2. Три и более условий отбора накладываются на содержимое одного столбца списка.

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


    Например, для рассматриваемого списка следующий диапазон критериев:

      А
    67 ФИО
    сотрудника
    68 Линьков С.А.
    69 Петрова И.В.
    70 Липецких АЛ.
    задает отбор записей, содержащих в столбце "ФИО сотрудника" значения либо "Линьков С.А.", либо "Петрова И.В.", либо "Липецких А.Л.".

    Пример 3. В условии отбора используется возвращаемое формулой значение.

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

  • формула может иметь в своем составе несколько функций и зависеть от нескольких полей списка, но должна обязательно иметь в качестве результата логическую величину ИСТИНА (True) или ЛОЖЬ (False);


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


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


  • формула должна ссылаться хотя бы на одно поле списка;


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


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

      А
    72 Всего руб.
    73 =E2+G2>800
    заданный для списка на рис. 13.1, позволяет найти фамилии сотрудников, которые имеют сумму оклада и премии более 800 рублей.

    Обратите внимание, что формула помещена под именем поля ("Всего руб."), не совпадающим ни с одним из имен полей списка на рис. 13.1. В качестве имени поля можно использовать любой уникальный текст.

    171

    Приведем примеры задания формул в критериях для списка на рис. 13.1, выполняющих сравнение содержимого ячеек в пределах записи:

    =E2=G2

    =E2

    В примерах в соответствии с требованиями по оформлению диапазонов критерий указаны относительные ссылки на первые ячейки двух полей: "Оклад (руб.)" и "Размер премии (руб.)".



    Более сложные критерии используют ссылки на ячейки нескольких записей списка или на ячейки вне списка, например: =Е2=$В$72 - сравнение значений ячеек в поле "Оклад (руб.)" с значением внешней по отношению к списку ячейки $В$72.

    Логические функции И, ИЛИ, НЕ расширяют возможности вычисляемых критериев. Например, формула =И(С2="Инженер";Р2>1.02.98) в критерии задает отбор записей, содержащих в поле "Должность" значение "Инженер" и в поле "Дата получения премии" - значение дат позже 1.02.98.

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

  • Активизировать одну из ячеек списка.


  • Выполнить команду Расширенный фильтр (Advanced Filter) меню Данные \ Фильтр (Data\ Filter).


  • В ноле Диапазон критериев (Criteria Range) появившегося диалогового окна Расширенный фильтр (Advanced Filter) указать ссылку на данный диапазон критериев.


  • Нажать на ОК.


  • Замечания.

  • При задании критериев строчные и прописные буквы не различаются.


  • При задании критериев можно использовать символы "*" и "?" в соответствии с правилами оформления шаблонов: "*" - любая последовательность символов,"?" - один символ в заданной позиции.


  • Отфильтрованные записи можно поместить в любое место текущего рабочего листа. Для этого в диалоговом окне Расширенный фильтр (Advanced Filter) нужно установить переключатель в положение Скопировать результат в другое место (Copy to Another Location) и в тюле Поместить результат в диапазон: (List Range:) указать место размещения отфильтрованных записей списка.


  • 172

    168 :: 169 :: 170 :: 171 :: 172 :: Содержание


    Содержание раздела







    Forekc.ru
    Рефераты, дипломы, курсовые, выпускные и квалификационные работы, диссертации, учебники, учебные пособия, лекции, методические пособия и рекомендации, программы и курсы обучения, публикации из профильных изданий