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

Добрый день! Сегодня я хочу показать вам как работать с углубленным или – по другому – с расширенным фильтром в Excel.

Большинство из нас привыкли к обычному фильтру. Он прекрасно позволяет выбрать необходимую информацию из таблицы

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

• Невозможно вытащить несколько текстовых значений по их содержанию. Максимум допустимы 2 значения.

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

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

• Нельзя автоматически скопировать результат в новое место, поэтому приходится итог фильтра выделять, копировать, переходить в нужное расположение и вставлять. если это делается только один раз, то еще терпимо, а если несколько? А если необходимо обработать несколько таблиц?

Вот тут как раз приходит на помощь расширенный фильтр. Выглядит он так.

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

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

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

Ну и наконец, самое главное – диапазон условий. Это на десерт. Именно в нем надо указывать правила, которые мы применяем. На многих Интернет-ресурсах пишут, что правила обязательно надо писать в виде формул.

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

Первый момент. Заголовок условий должен соответствовать заголовку таблицы – это правда. Но это не значит, что они должны быть одинаковы! В примере ниже я в условиях указал только цену и номенклатуру. Конкретно, меня интересует только кефир с ценой менее 400

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

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

Если же надо использовать более сложную формулу   - но опять-таки без указывания конкретных ячеек диапазона! – то можно пойти на хитрость. Например, стоит задача – показать пять наилучших продаж. Тогда вначале в отдельной ячейке, не входящей в условия, применяем такую формулу: =НАИБОЛЬШИЙ(E2:E101;5).

Здесь Е2:Е101 это весь диапазон с суммами, а 5 – это нужный порядковый номер. Затем вручную используем полученное значение в диапазоне условий.

Момент третий. Никто не мешает при задании условия – ТЕКСТОВОГО! – применять постановочные знаки. Звездочка позволяет заменитьлюбой набор любых знаков. Например, если указано следующее:

Сахар* – то выбраны будут все позиции, гле текст начинается с слова сахар. Например, «сахар-рафинад»

*сахар – наоборот, те где текст заканчивается на слово сахар, например, «кубинский сахар»

*сахар* - означает что слово «сахар» вообще присутствует. К примеру, в данном случае выберет и «сахар-рафинад», и «кубинский сахар», и «кубинский сахар-рафинад». В следующем примере я выберу в номенклатуре топлива только бензин, а в подразделениях укажу Москву и Московскую область. Обратите внимание, как я это указал

Если же нужен вариант посложнее, то добавляем проверочный столбец, в котором применяем нужный расчет, а затем указываем его в условиях. Например, если надо отобрать только номенклатуру с названием ровно в пять знаков, делаем так. Добавляем столбец ЗНАКИ ( название любое) и пишем в нем нужное логическое выражение. В нашем примере оно такое:

=ДЛСТР(A2)=5

Затем используем расширенный фильтр, в условиях указываем добавленный столбец знаки и значение ИСТИНА для поиска. Кстати, после применения фильтра проверочный столбец можно смело удалить

В третьих, надо проверить значения ячеек по очереди, то в условиях пишем нужную  формулу, применяя ее к ПЕРВОЙ ячейке в нужном диапазоне. Адрес этой ячейке должен быть относительным!  При применении фильтра в этом случае Excel переберёт все строки и отобразит только те, где результат формулы для очередной проверяемой ячейки в выбранном столбце будет истина

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

=И(ПРАВСИМВ(A2;1)="а";ДЛСТР(A2)=5)

В четвертых, если необходимо применить условия к разным столбцам использую выбор типа «ИЛИ», то пишем эти варианты В РАЗНЫХ СТРОКАХ дипазона с условиями.

Например, или товар  кефир, или вес свыше 75 кг. При этом для кефира будут выбраны все позиции, а для остальных товаров только те, где вес превысит 75 кг.

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

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

="морковь"

Для веса указываем так

=">=50"

Обратите внимание, что использованы именно кавычки, а не двойной апостроф!

На этом пока все. Встретимся на занятиях!