Сначала вставьте несколько пустых строк над таблицей данных и скопируйте туда заголовок таблицы - это будет диапазон с условиями (выделен желтым цветом для наглядности):
Между желтыми ячейками и исходной таблицей должна быть как минимум одна пустая строка.
Введите критерии (условия) в желтые ячейки, которые затем будут использоваться для фильтрации. Например, если вы хотите отобрать бананы для АШАН Москва в 3 квартале, условия будут выглядеть следующим образом:
Чтобы выполнить фильтрацию, выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Данные - Дополнительно). В открывшемся окне диапазон данных уже должен быть введен автоматически, и нам остается только указать диапазон условий, т.е. A1:I2:
Пустая ячейка в условном диапазоне будет рассматриваться Excel как отсутствующий критерий, а пустая строка - как запрос на вывод всех данных без разбора.
Переключатель Copy Result To Elsewhere позволит вам не фильтровать список сразу на этом листе (как при обычном фильтре), а выгрузить выбранные строки в другой диапазон, который затем нужно будет указать в поле Place Result In Range. В данном случае мы не используем эту функцию, оставим список Filter на месте и нажмем OK. Выбранные строки будут отображены на листе:
Добавляем макрос
"И где же здесь удобство?" - спросите вы, и будете правы. Вам не только придется вручную вводить условия в желтые ячейки, но и открывать диалоговое окно, вводить туда диапазоны и нажимать кнопку OK. Грустно, я согласен! Но "все меняется, когда они приходят". - макросы!
Работу с расширенным фильтром можно сделать в сотни раз быстрее и проще с помощью простого макроса, который будет автоматически запускать расширенный фильтр, когда вы вводите условия, то есть изменяете любую желтую ячейку. Щелкните правой кнопкой мыши по ярлыку текущего рабочего листа и выберите Исходный код. В открывшемся окне скопируйте и вставьте следующий код:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion End If End Sub
Эта процедура будет выполняться автоматически при изменении любой ячейки в текущем рабочем листе. Если адрес измененной ячейки находится в желтом диапазоне (A2:I5), то макрос удаляет все фильтры (если они есть) и заново применяет расширенный фильтр к исходной таблице данных, начиная с A7, т.е. все будет отфильтровано сразу после ввода следующего условия:
Подготовка диапазона для условий фильтрации
В автофильтре условия фильтрации данных задаются в выпадающем меню после нажатия кнопки в заголовке таблицы. В расширенном фильтре условия фильтрации должны быть заданы в отдельном диапазоне. Этот диапазон может находиться на том же листе, что и данные, на другом листе или даже в другой книге. Самое главное, он должен быть отделен от исходных данных хотя бы одним пустым столбцом или строкой (чтобы Excel не воспринимал их как один диапазон).
Также обратите внимание, что после определения диапазона условий на шаге 3 (работа в мастере расширенного фильтра) необходимо выделить заголовок и строки, в которых присутствуют какие-либо критерии. Пустые строки не должны быть выделены, иначе фильтр будет рассматривать их как сигнал 'Отобразить все строки'.
Диапазон для задания условий фильтрации - это копия заголовка основной таблицы (или, по крайней мере, тех полей, по которым вы хотите отобрать данные) и соответствующее количество пустых строк под этим заголовком.
Правильная организация данных для работы с расширенным фильтром
Формирование условий фильтрации
Этот шаг является решающим и раскрывает всю мощь инструмента. Для начала необходимо научиться правильно задавать критерии отбора.
Они могут быть 3 типов:
- текстовые критерии.
Если в качестве текстового критерия ввести слово, например, "Москва", то будут выбраны ВСЕ строки, в столбце которых есть слово "Москва".
Если вы хотите искать слово или часть слова не с начала строки, а со всей строки, используйте подстановочные знаки. В примере ниже показаны все строки со словом "Санкт-Петербург" в столбце "Город".
Если вам нужно найти точное вхождение слова или фразы, вам нужно задать критерий с помощью несколько необычной формулы. Например, чтобы найти строки, содержащие "Санкт-Петербург" и не отображающие "Санкт-Петербург", введите формулу: ="=Петербург" (именно так, с двумя знаками "=").
- числовые критерии и даты
В качестве критерия можно ввести число (при этом будут отобраны только те строки, значение столбца которых совпадает с этим числом).
Вы также можете вводить выражения, используя булевы операторы (>, =, ). Например, вы можете найти строки с суммой более 500 000, введя критерий >500000.
Вы должны быть особенно внимательны при вводе критериев в форме даты. Даты должны вводиться через косую черту. Например, чтобы выбрать все операции после 4 января 2017 года, введите критерий - >04/01/2017 в поле "Дата" (некоторые версии Excel требуют ввода даты в формате ММ/ДД/ГГГГ, т.е. месяц должен быть введен первым. Помните об этом при работе).
- формулы
Самое лучшее в расширенном фильтре - это то, что он использует формулы в качестве критерия. Для того чтобы она работала, введенная вами формула должна возвращать TRUE (в этом случае строка будет напечатана) или FALSE (строка будет скрыта). Важно отметить, что заголовок столбца формулы должен отличаться от любой записи в заголовке таблицы (можно вообще оставить его пустым). При написании формул не забывайте правильно размещать абсолютные и относительные ссылки.
Например, если вы хотите показать 5 верхних строк по количеству полей, введите следующую формулу:
=F10>MOST($F$10:$F$37;6),
где F10 - ячейка первой строки столбца "Сумма" (она не постоянна, так как формула будет проходить строки по очереди), $F$10:$F$37 - ссылка на диапазон, который занимает столбец "Сумма" (ссылка постоянна, так как столбец не меняется).
В результате формула пройдет по всем строкам (с 10-й по 37-ю) и скроет все, кроме тех, где значение больше шестого по величине (то есть оставит первые 5).
Конечно, все критерии и примеры, описанные выше, могут быть реализованы с помощью простого автофильтра (кроме возможности использовать формулы). Однако весь потенциал расширенного фильтра раскрывается, когда вы знаете, как правильно объединить несколько критериев.
Итак, основные понятия, которые необходимо усвоить, чтобы успешно использовать расширенный фильтр:
- Заголовок столбца, в котором вводится критерий отбора, должен точно совпадать со столбцом, к которому применяется критерий. То есть, если вы выбираете строки, в которых столбец 'Amount' имеет значение больше 500, вы также должны ввести условие >500 под заголовком 'Amount';
- Условия в одной строке рассматриваются фильтром как связанные оператором AND. Например, на изображении ниже показано условие AND Год 2017, AND Город Москва, AND Менеджер Петров.
- Выражения, записанные в разных строках, рассматриваются оператором OR как комбинированные. Условия можно применять к одному столбцу или к разным столбцам. Например, на изображении ниже, условие ИЛИ город Москва, ИЛИ менеджер Иванов. Таким образом, каждая строка представляет собой один набор условий.
- Если необходимо сделать условие AND, но с использованием одного и того же столбца (например, AND сумма больше 500 000 AND сумма меньше 600 000), заголовок такого столбца должен быть продублирован дважды. Пример:
Теперь вы знаете, какие критерии можно установить и как правильно их комбинировать. Этого достаточно для создания сложных запросов, чего не может сделать простой автофильтр. Например, если вам нужно показать все сделки в Москве в 2017 году с суммой выше 500 000, и в то же время показать все сделки Иванова в 2016 году, входящие в первую пятерку, критерии будут выглядеть следующим образом:
Зачем нужны фильтры в таблицах Эксель
А затем иметь возможность быстро выбирать только нужные данные, скрывая ненужные строки таблицы. Таким образом, фильтр просто позволяет временно скрыть строки таблицы Excel, не удаляя их.
Строки таблицы, скрытые фильтром, не исчезают. Условно можно представить, что их высота становится равной нулю (я уже говорил об изменении высоты строк и ширины столбцов ранее). Таким образом, оставшиеся строки, не скрытые фильтром, так сказать, "склеиваются". Результатом будет таблица с примененным фильтром.
Таблица Excel с фильтром выглядит так же, как и любая другая таблица Excel, но в верхней части каждого столбца появляются специальные стрелки.
Как сделать (наложить) фильтр на таблицу Excel
Это очень просто! Предположим, у нас есть таблица с заголовками. Поскольку фильтр электронной таблицы Excel применяется к определенной части таблицы, эта часть должна быть выделена первой. Сам Excel никогда не догадается, о чем идет речь, поэтому мы выделяем нужные ячейки в строке заголовка таблицы.
Внимание:
Если вы выделите ВЕСЬ ряд, Excel автоматически отфильтрует ВСЕ столбцы, в которых есть хоть что-то. Если в столбце ничего нет, фильтр применяться не будет.
После того как вы выбрали нужные столбцы, необходимо настроить фильтр. Самый простой способ сделать это - перейти на вкладку Главная / Фильтр и сортировка на ленте и нажать на фильтр. Здесь приведен снимок экрана из Office 2010, в других версиях Excel фильтр применяется к таблице аналогичным образом.
При нажатии этой кнопки фильтр будет добавлен к выбранным ячейкам.
Если не выделять несколько ячеек, а просто щелкнуть по одной, фильтр будет добавлен ко ВСЕЙ строке, как если бы вы выделили ее.
В моем примере из трех столбцов, показанных на рисунке, фильтр был добавлен только к двум ячейкам, которые были ранее выделены.
Пример включения фильтра в таблицу является самым простым. Давайте посмотрим, как это работает.
Как пользоваться фильтром в Эксель
Сразу после активации фильтра таблица не изменится (кроме стрелок, появившихся в заголовках столбцов). Чтобы отфильтровать нужную вам часть данных, нажмите стрелку в столбце, который вы хотите отфильтровать.
Идея, лежащая в основе фильтра:
заключается в том, что Excel оставит в таблице только те строки, которые содержат ячейку с выбранным значением в ЭТОМ (с установленным фильтром) столбце. Остальные строки будут скрыты.
Чтобы удалить фильтрацию (без удаления фильтра!), просто выделите все поля. Тот же эффект произойдет при полном удалении фильтра - стол вернется к своему первоначальному виду.
Наложение нескольких фильтров на таблицу Эксель
Вы можете комбинировать фильтры в таблице Excel. Наложение нескольких фильтров происходит по логическому правилу "И". Что это значит, давайте рассмотрим на примере.
Если в таблице есть несколько столбцов, мы можем сначала установить фильтр, например, на первый столбец. Это позволит скрыть некоторые строки в соответствии с условием фильтра. Затем установите фильтр на следующий столбец. Строки, оставшиеся после первого фильтра, будут подвергнуты дальнейшей фильтрации. Просто к каждой строке будет применено 2 фильтра - один для первого столбца и один для второго.
По мере применения каждого последующего фильтра количество строк, соответствующих ВСЕМ фильтрам, будет уменьшаться. Таким образом, применяя последовательно несколько фильтров, можно быстро сделать небольшой выбор из огромной таблицы, состоящей из тысяч строк.
С полученным выделением можно выполнять дальнейшие операции. Вы можете, например, скопировать эти строки и переместить их в другую таблицу. Или, наоборот, вы можете отфильтровать строки, которые хотите полностью удалить! А если после их удаления вы удалите фильтр, у вас будет таблица, очищенная от ненужных данных.
Теперь посмотрите видео, где я показываю реальный пример того, как фильтровать данные в прайс-листе с более чем 15 000 строк.
Как задать несколько параметров
После фильтрации информации по одному из параметров колонки пользователь может продолжить фильтрацию по другим колонкам. Для фильтрации потребуется меньше значений, но результат будет более подробным.
Например, вы уже отфильтровали таблицу по условию "Страна", и в ней остались только товары российского происхождения.
Теперь нам нужно дополнительно выделить товары со стоимостью 100 рублей, для этого применим экранирование в разделе "Цена".
Нажимаем "ОК" и получаем только товары стоимостью 100 рублей, произведенные в России.
Как поставить расширенный поиск
Расширенный поиск позволяет просеивать информацию о нескольких условиях одновременно. Перед применением фильтра к электронной таблице Excel необходимо подготовить саму таблицу, создав над ней пространство с несколькими пустыми строками и скопировав заголовки.
Затем в пустой строке под скопированными заголовками задайте необходимые условия поиска. Например, вам нужно найти товар, произведенный в России, продаваемый менеджером Ивановым, стоимостью менее 300 рублей.
После того как параметры введены правильно, снова откройте вкладку "Данные" и выберите функцию "Дополнительно".
Перед пользователем появится окно, в котором нужно будет заполнить две строки:
- "Source Range" - это диапазон таблицы, информация которой должна быть отфильтрована, т.е. исходная таблица. Excel введет его автоматически;
- "Диапазон условий" - это ячейки, из которых программа будет извлекать значения для фильтрации - вторая таблица, которую мы создали вверху. Чтобы значения появились в строке окна, нам нужно захватить две его строки: с названием раздела и введенными значениями.
После того как оба диапазона будут созданы, нажмите 'OK' и оцените результат.
Как удостовериться, поставлен ли фильтр
Чтобы проверить, задан поиск или нет, посмотрите на строку с параметрами - это первая строка. Если да, то в каждой ячейке строки появляется маленький символ.
'1' указывает на столбец, в котором поиск был задан, но не применен. Символ "2" означает, что поиск уже применен.
Использование макроса расширенного фильтра
Большинство людей согласятся, что усовершенствованный фильтр в стандартной комплектации ужасно неудобен. И да, это действительно так. За исключением того, что вам придется создать таблицу специально для условий, вручную ввести туда данные, а также открыть отдельный диалог и ввести там диапазоны. Но если вы используете макросы, большинство операций можно автоматизировать.
И все, что вам нужно сделать, это использовать очень легкий макрос, который запрограммирован на запуск расширенного фильтра при изменении любой ячейки в диапазоне условий. Вы согласитесь, что это гораздо более удобное решение. Для этого вызовите контекстное меню ярлыка рабочего листа и выберите "Исходный текст". После этого появится окно, в которое нужно вставить следующий код
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:I5")) Тогда ничего
При ошибке продолжить Далее
ActiveSheet.ShowAllData
Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion
End If
End Sub
Очевидно, что вставляйте те ячейки, которые наиболее подходят для данной ситуации. Теперь при каждом изменении любой ячейки будет выполняться эта подпрограмма. Как только вы отредактируете ячейку, находящуюся в условном диапазоне (проще говоря, в пустой ячейке), все фильтры будут автоматически удалены и будет установлен расширенный фильтр.
Вот и все, никаких дальнейших действий предпринимать не нужно. Фильтр автоматически подстраивается под любые изменения, вносимые пользователем.
Подробнее о сложных запросах
Теперь, когда мы реализовали команду автоматического применения расширенного фильтра к таблице при малейшей корректировке условий, мы можем посмотреть, как работают расширенные условия в расширенном фильтре.
Помимо точных совпадений, человек может использовать дополнительные символы, некоторые из которых позволяют более гибко подходить к поиску. Не имеет значения, в каком падеже находятся персонажи. Чтобы пояснить это, приведем таблицу с подробным описанием всех возможных условий.
Здесь следует иметь в виду несколько моментов:
- Символ * обозначает любое количество символов, а ? - Только один.
- Числовые и текстовые запросы обрабатываются по-разному. Например, если пользователь указывает условную ячейку, содержащую число 5, это не означает, что фильтр будет искать все числа, начинающиеся с этого числа. Однако если ячейка содержит букву B, фильтр выберет данные, содержащие текст, начинающийся с этой буквы. Проще говоря, если текст не начинается со знака =, то он эквивалентен тексту со знаком * в конце.
- Если используются даты, они вводятся в формате, принятом в США. Сначала вводится месяц, затем день, затем год, а элементы выписки разделяются дробью. Это относится даже к Excel, локализованному для России.
Логические условия
Большим преимуществом расширенного фильтра является возможность применения логических условий. Что такое логическое условие? Логическое условие - это условие, в котором используется логический оператор AND или OR. Конечно, есть и другие, но они описаны выше.
Особенностью любой логической операции является то, что она возвращает состояние True или False. В случае расширенного фильтра условие будет иметь следующий вид: если заданное условие истинно.
То есть, логическое условие "AND" на самом деле расшифровывается как "если критерий 1 и критерий 2 удовлетворяют критерию 3". Например, если яблоки и морковь стоят одинаково, то. Or аналогичен, только вместо "And" вставлено "Or". На практике это может выглядеть следующим образом. Если пользователи приобрели товары у компании 1 или компании 2, отобразите эти покупки. В этом случае, если они покупают что-то у третьей компании, это не будет отображаться.
В действительности каждый фильтр, будь то простой или расширенный, содержит логические условия. Но вы можете сделать их использование более гибким.
Итак, что вам нужно знать? Если условия находятся в одной строке, но в разных ячейках, они считаются связанными логическим оператором 'AND'.
13
В приведенном выше примере фильтр покажет только те бананы, которые были куплены в Ашан Москва в третьем квартале.
Если вы хотите, чтобы условия были объединены с помощью логического оператора OR, вам нужно написать их на разных строках.
Например, в примере ниже фильтр будет рассматривать только заказы Волина на персики, купленные в Москве, и лук, проданный в Самаре в третьем квартале.
Если вы хотите применить более одного условия к одному столбцу, вы можете просто продублировать заголовок столбца в диапазоне критериев и ввести под ним другие условия. например, таким образом вы можете применить фильтр, чтобы показать все транзакции с марта по май.
Таким образом, если следовать приведенным выше рекомендациям, расширенный фильтр уже не будет казаться таким неудобным инструментом, а его набор функций станет очень широким и будет покрывать практически любую задачу, которую поставит перед собой пользователь.
Настройка фильтрации
Чтобы настроить фильтрацию, используйте
-
кнопка Кнопка "Фильтрация" на вкладке Конструктор панели инструментов (также на вкладке Главная или вкладке Дата для Экспресс-отчета);
-
Вкладка "Фильтрация" на боковой панели.
Внимание. Используйте язык программирования Fore для применения расширенных настроек фильтрации.
Доступны следующие параметры фильтрации:
-
Скрыть пустые значения. Строки/столбцы, не содержащие никакой информации, будут скрыты;
-
Скрыть нулевые значения. Строки/столбцы, содержащие нулевые значения, скрываются;
-
Скрыть нечисловые значения. Скрытыми будут только строки/столбцы, содержащие нечисловые значения;
-
Оставьте владельцев неисключенных элементов. Родители неисключенных строк/столбцов не будут скрыты;
-
Скрыть по штатам. Строки/столбцы будут скрыты, если все данные ячеек соответствуют условию, указанному в раскрывающемся списке. В большинстве условий используется одно или два числа для сравнения с результатом ячейки:
-
-
равна A;
-
не равна A;
-
больше, чем A;
-
меньше, чем A;
-
больше или равно A;
-
меньше или равно A;
-
между A и B включительно;
-
меньше A или больше B;
-
Чтобы задать сложную формулу, используйте опцию 'выражение'. При использовании этой опции доступна подстановка 'value', которая соответствует значению ячейки. При использовании выражения доступны стандартные арифметические операции, объединяющие выражение со скобками. В нормативных отчетах доступно использование вложенных модулей> функций;
-
Скрыть по формуле. Строки/столбцы, соответствующие формуле, заданной через "Редактор выражений>", будут скрыты;
-
Исключить выбранное из фильтрации. Выбранные элементы измерения будут скрыты. Настройка доступна, когда строки и столбцы фильтруются отдельно.
Внимание. Если у вас есть несколько измерений строк/столбцов, будут применены настройки последнего включенного измерения.
Отключение фильтрации
Чтобы отключить фильтрацию, нажмите верхнюю часть кнопки "Фильтр" на вкладке ленты "Данные" или кнопку "Конструктор" (для экспресс-отчета) или кнопку "Конструктор" (для выбранного блока на панели анализа).
Срезы
Деления - это то же самое, что и фильтры, но в отдельной области и с удобным графическим представлением. Они не являются частью листа ячеек, а представляют собой отдельную сущность, набор кнопок, размещенных на листе Excel. Использование срезов не заменяет автофильтр, но облегчает фильтрацию благодаря удобной визуализации: все примененные критерии видны сразу. Слайсы были добавлены в Excel в версии 2010 года.
Создание срезов
В Excel 2010 вы можете использовать срезы для перекрестных таблиц, а в Excel 2013 вы можете создать срез для любой таблицы.
Для этого выполните следующие действия:
- Выделите одну ячейку в таблице и выберите вкладку [Дизайн].
- Выберите кнопку Insert Slicer в группе Tools (или на вкладке Insert в группе Filters).
- В диалоговом окне выберите поля, которые вы хотите включить в срез, и нажмите OK.
Форматирование срезов
- Выберите ломтик.
- На вкладке Параметры выберите группу Стили слайсера, содержащую 14 стандартных стилей и возможность создания собственного пользовательского стиля.
- Выберите кнопку с соответствующим стилем форматирования.
Чтобы удалить фрагмент, выделите его и нажмите клавишу Delete.
Текстовый фильтр в Эксель
Рассмотрим, как отфильтровать ячейки с определенным текстом в Excel. Самый простой способ - это, как и в предыдущем примере, ввести нужный текст (или его часть) в поле поиска.
Однако можно настроить выбор и более гибко. Если в окне фильтров нажать на "Текстовые фильтры", то в контекстном меню появится выбор методов согласования: равно, не равно, начинается с, заканчивается с, содержит, не содержит.
Например, вам нужно отобрать людей, чье первое имя не Богдан. Выберите "не содержит" и добавьте к нему критерий "Богдан". Поставьте пробелы перед и после имени. Иначе, например, Богданов Егор Егорович тоже попадет под фильтр, хотя его зовут не Богдан:
Настраиваемый тестовый фильтр
Я расскажу вам, как настроить фильтр в Excel для двух условий в одной ячейке. Для этого нажмите Фильтры текста - Настраиваемый фильтр.
Предположим, что мы хотим выбрать людей по имени Богдан или Никита. Давайте запишем логику, как показано на рисунке
И вот результат:
Как определить, какой оператор сравнения выбрать, "AND" или "OR"? Логика выглядит следующим образом:
- И - когда необходимо, чтобы оба условия выполнялись одновременно
- ИЛИ - когда должно произойти хотя бы одно из двух условий.
Подробнее о логических операторах вы можете прочитать в этой статье.
Вы также можете использовать операторы в условии:
- ? - это любой один символ
- * - это любое количество произвольных символов.
Например, чтобы выбрать имя, в котором встречается строка 'ktor', мы напишем условие следующим образом: * ktor*.
Как поставить фильтр в Экселе на столбец с числами
Числовые фильтры также являются гибкими, есть возможность выбрать
- Равный или не равный
- Больше, больше или равно, меньше или равно
- Между (в диапазоне)
- Первые 10
- Выше среднего, ниже среднего
Вы видите, что есть интересные варианты. Давайте попробуем отобрать людей с объемом продаж, например, 200 000 - 500 000:
Для рисунков, как и для текста, доступен настраиваемый фильтр.
Как сделать фильтр в Excel по строкам?
Вы не сможете сделать это стандартными методами. Microsoft Excel будет отфильтровывать данные только в столбцах. Поэтому вам придется искать другие решения.
Ниже приведены примеры расширенных критериев фильтрации строк в Excel:
- Используйте формулы, чтобы отобразить в строке именно то, что вам нужно. Например, превратите указатель в выпадающий список. И введите формулу в соседнюю ячейку с помощью функции IF. При выборе конкретного значения из выпадающего списка рядом с ним появится его параметр.
Чтобы привести пример работы фильтра строк в Excel, давайте создадим таблицу:
Давайте создадим выпадающий список для списка товаров:
Вставим пустую строку над таблицей с исходными данными. В ячейки введите формулу, показывающую, из каких столбцов взята информация.
Рядом с раскрывающимся списком Ячейка введите следующую формулу: Его задача - выбрать из таблицы те значения, которые соответствуют определенному продукту
Таким образом, используя инструмент раскрывающегося списка и встроенные функции Excel, Excel выбирает данные в строках в соответствии с определенным критерием.
