Критерии бывают двух типов

Способы фильтрации списков

Фильтрация или выборка - очень частая операция во время работы со списками. Суть ее в том, чтобы отобрать из списка все строки (записи), удовлетворяющие определенным условиям. Условий может быть много, они могут быть простыми и сложными, связанными друг с другом или независимыми. Существует несколько способов фильтрации списков в Excel.

Способ 1. Автофильтр

Отфильтровать список автофильтром - значит скрыть все строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в меню Данные - Фильтр – Автофильтр. В первой строке, содержащей заголовки столбцов, появятся кнопки со стрелками - кнопки автофильтра:

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

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

К сожалению, ни одна версия Excel, кроме Excel 2007 не позволяет выбрать из выпадающего списка более одного варианта.

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

Пользовательский автофильтр

Мы можем использовать автофильтр для выборки записей по более сложным условиям. Допустим, необходимо выбрать из списка строки, где оклад сотрудника лежит в диапазоне от 1000 до 2000 или меньше/больше определенной величины. Для выполнения такой фильтрации из выпадающего списка поля "Стоимость заказа" необходимо выбрать пункт Условие. Появится окно Пользовательского автофильтра:

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

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

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

В отличие от Автофильтра и Пользовательского автофильтра - Расширенный фильтр практически не имеет ограничений на количество условий, налагаемых на список. Но требует некоторых подготовительных операций.

1. Необходимо предварительно создать таблицу критериев (условий). Эта таблица должна состоять из двух строк: заголовка и самого условия, поэтому минимум может быть 2 ячейки: заголовок и условие.

Критерии бывают двух типов.

¯ Критерии сравнения – это набор условий для поиска, используемый для извлечения данных. Критерий сравнения может быть последовательностью символов (константой) или выражением (например, Цена > 700).

Порядок создания таблицы критериев сравнения:

• В любую свободную область (лучше всего вставить над списком несколько пустых строк) нужно скопировать из "шапки" списка заголовки столбцов, по которым будут вводиться условия.
• Затем, ниже скопированных заголовков в пустые ячейки вводятся условия для фильтрации. Причем, условия, введенные в ячейки одной строки Excel будет связывать логическим "И", а в ячейки разных строк - логическим "ИЛИ".

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

Excel отберет всех мужчин с окладом 1000 и более, неженатых и женщин не старше 1 января 1950 года рождения, незамужних, с детьми.

 

¯ Критерии вычисления – это критерии, которые являются результатом вычисления формулы.

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

выше среднего

=F7>СРЗНАЧ($F$7:$F$21)

Например, диапазон критериев =F7>СРЗНАЧ($F$7:$F$21) выводит на экран строки, имеющие в столбце F значения большие, чем среднее значение величин в ячейках F7:F21. Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА. При фильтрации будут доступные только те строки, значения которых будут придавать формуле значения ИСТИНА.

2. Для того, чтобы отфильтровать данные по критериям из нашей таблицы, выделите любую ячейку исходного списка сотрудников и выберите в меню Данные - Фильтр - Расширенный фильтр. Откроется окно Расширенного фильтра, в которое необходимо ввести адрес исходного диапазона, адрес диапазона условий и указать место, куда поместить результаты фильтрации:

Флажок Только уникальные записи служит для отбора неповторяющихся данных

Значение переключателей и полей окна Расширенный фильтр следующие:

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

скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или на другое место на этом же рабочем листе;

Исходный диапазон – поле, определяющее диапазон, который содержит список, подлежащий фильтрации;

Диапазон условий – поле, определяющее диапазон ячеек на рабочем листе, который содержит необходимые условия;

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

Только уникальные записи – переключатель, который выводит только строки, удовлетворяющие критерию и не содержащие неповторяющихся элементов. Если диапазон критериев не определен, то в этом случае все строки списка, содержащие дубликаты, будут скрыты.