Сначала вставьте несколько пустых строк над таблицей данных и скопируйте туда заголовок таблицы — это будет диапазон с условиями (выделен желтым для ясности):
Между желтыми ячейками и исходной таблицей должна быть хотя бы одна пустая строка.
именно в желтые ячейки нужно ввести критерии (условия), которые затем будут отфильтрованы. Например, если в московском «Ашане» в III квартале необходимо отобрать бананы, то условия будут такими:
Чтобы выполнить фильтрацию, выберите любую ячейку в диапазоне с исходными данными, откройте вкладку «Данные» и нажмите кнопку «Дополнительные» («Данные — Дополнительно»). В открывшемся окне диапазон с данными уже должен быть автоматически введен, и нам останется только указать диапазон условий.
Обратите внимание, что диапазон условий не может быть назначен «с полем», то есть вы не можете выбрать лишние пустые желтые строки, потому что пустая ячейка в диапазоне условий интерпретируется Excel как отсутствие критерия и весь пробел строка как запрос на просмотр всех данных без разбора.
Флажок Копировать результат в другое место позволит вам фильтровать список не только там, на этом листе (как обычный фильтр), но и загружать выбранные строки в другой диапазон, который затем нужно будет указать в Результате размещения в поле диапазона. В этом случае мы не используем эту функцию, мы оставляем список фильтров на месте и нажимаем ОК. Выбранные строки будут отображаться на листе:
«Ну, а где здесь удобство?» — спросите и будете правы. Вам нужно не только вручную ввести условия в желтые ячейки, но также открыть диалоговое окно, ввести там диапазоны, нажать ОК. Грустно, согласен! Но «все меняется, когда они приходят» — макро!
Работу с расширенным фильтром можно значительно ускорить и упростить, используя простой макрос, который автоматически запускает расширенный фильтр при вводе условий, например, путем изменения любых желтых ячеек. Щелкните правой кнопкой мыши вкладку текущего листа и выберите Исходный код. В открывшемся окне скопируйте и вставьте следующий код:
Private Sub Worksheet_Change (ByVal Target As Range) Если не пересекается (Target, Range («A2: I5»)), то при ошибке ничего не происходит. Возобновить следующий диапазон ActiveSheet.ShowAllData («A7»). CurrentRegion.AdvancedFilter Действие: = xlFilterInPlace, CriteriaRange: = Интервал («A1»). CurrentRegion End If End Sub
Эта процедура запускается автоматически при изменении любой ячейки на текущем листе. Если измененный адрес ячейки попадает в желтый диапазон (A2: I5), этот макрос удаляет все фильтры (если есть) и повторно применяет расширенный фильтр к исходной таблице данных, начиная с A7, т.е все будет отфильтровано немедленно, сразу после ввода следующее условие:
В автоматическом фильтре условия фильтрации данных задаются в меню, которое появляется после нажатия кнопки в заголовке таблицы. В расширенном необходимо указать условия фильтрации в отдельном диапазоне. Этот диапазон может находиться в той же таблице, на другом листе или даже в другой книге. Главное, чтобы он был отделен от исходных данных хотя бы одним пустым столбцом или строкой (чтобы Excel не воспринимал их как диапазон).
Диапазон задания условий фильтрации — это копия заголовка основной таблицы (или хотя бы тех полей, по которым нужно выбирать данные) и достаточное количество пустых строк под этим заголовком.также важно помнить, что при указании диапазона условий на шаге 3 (работа в мастере «Расширенный фильтр») вам необходимо выбрать заголовок и строки, в которых присутствуют некоторые критерии. Выбирать пустые строки необязательно, иначе фильтр интерпретирует их как сигнал «Показать все строки”.
Правильная организация данных для работы с расширенным фильтром
Этот этап является фундаментальным и раскрывает всю мощь инструмента. Для начала нужно научиться правильно выставлять критерии отбора.
Они бывают 3-х видов:
— текстовые критерии
Если в качестве критерия текста ввести слово в поле, например «Москва», будут выбраны ВСЕ строки, в которых запись в указанном столбце начинается со слова «Москва”
Если вам нужно искать слово или часть слова не с начала строки, а в целом, вам нужно использовать подстановочные знаки. В следующем примере вы найдете все строки, в которых столбец «Город» содержит слово «Петербург”
Если вам нужно найти точное вхождение слова или фразы, критерий должен быть установлен с помощью несколько необычной формулы. Например, чтобы найти строки, содержащие «Петербург», но не отображать строки «Санкт-Петербург», вам нужно ввести формулу: = »= Петербург» (правильно, с двумя знаками “=”).
— числовые критерии и даты
В качестве критерия можно ввести число (и тогда будут выбраны строки, в которых значения столбцов равны этому числу)
вы также можете вводить выражения с помощью логических операторов (>, =, ). Например, вы можете найти строки с суммой больше 500 000, введя критерий> 500 000
с особой осторожностью нужно быть при вводе критериев в виде даты. Даты должны вводиться через косую черту. Например, чтобы выбрать все транзакции после 4 января 2017 года, необходимо ввести критерий для поля «Дата» -> 01.04.2017 (в некоторых версиях Excel необходимо вставить в поле MM / DD / ГГГГ, т.е нужно предварительно указать месяц в уме при работе).
— формулы
Лучшее, что может сделать расширенный фильтр, — это использовать формулы в качестве критерия. Чтобы это работало, указанная формула должна возвращать ИСТИНА (и, таким образом, строка будет отображаться) или ЛОЖЬ (строка будет скрыта). Чрезвычайно важно, чтобы заголовок столбца формулы отличался от любой записи в заголовке таблицы (вы можете оставить его полностью пустым). При написании формул не забывайте правильно размещать абсолютные и относительные ссылки.
Например, если вы хотите отобразить первые 5 строк в поле суммы, вам нужно будет ввести следующую формулу:
= F10> БОЛЬШОЙ (10 F $: 37,6 F),
где F10 — это ячейка первой строки в столбце «Сумма» (она не заблокирована, поскольку формула будет перебирать строки одна за другой), $ F $ 10: $ F $ 37 — ссылка на диапазон, который столбец «Сумма» занимает »(ссылка заблокирована, так как столбец не меняется).
В результате формула пройдет по всем строкам (с 10-й по 37-ю) и скроет все, кроме тех, у которых значение больше шестого по величине (то есть останется 5 первых).
Конечно, все описанные критерии и примеры можно реализовать с помощью обычного автоматического фильтра (за исключением возможности использования формул). Однако весь потенциал расширенного фильтра раскрывается, когда вы знаете, как правильно комбинировать множество критериев друг с другом.
Итак, вот основные концепции, которые вам необходимо понять, чтобы успешно применить расширенный фильтр:
— заголовок столбца, в который мы записываем критерий выбора, должен быть точно таким же, как заголовок столбца, к которому мы применяем этот критерий. То есть, если мы выбираем строки, в которых значение в столбце «Сумма» больше 500, условие> 500 записывается под заголовком «Сумма”;
— условия, записанные подряд, воспринимаются фильтром как связанные оператором I.
— условия, написанные разными строками, воспринимаются фильтром как связанные оператором ИЛИ. Условия могут применяться к столбцу или к разным столбцам. Например, на изображении ниже показано условие ИЛИ город Москва, ИЛИ менеджер Иванов. Следовательно, каждая строка представляет собой единый набор условий.
— если вам нужно установить условие И, но при этом использовать тот же столбец (например, И сумма больше 500 000 И сумма меньше 600 000), заголовок этого столбца должен быть продублирован дважды. Пример:
Теперь вы знаете, какие критерии можно задать и как их правильно комбинировать. Этого достаточно для построения сложных запросов, недоступных обычному автофильтру. Например, если вам нужно показать все предложения Москвы на 2017 год на сумму больше 500000 и при этом также отобразить все предложения из Иванова на 2016 год, которые находятся в ТОП5, критерии будут выглядеть так:
А затем, чтобы иметь возможность быстро выбирать только нужные вам данные, скрывая ненужные строки таблицы. Следовательно, фильтр позволяет временно скрыть их, не удаляя строки таблицы Excel.
Строки таблицы, скрытые фильтром, не исчезают. Условно можно представить, что их высота станет равной нулю (я говорил об изменении высоты строк и ширины столбцов). Поэтому остальные строки, которые не скрываются фильтром, как бы «склеиваются». Результатом является отфильтрованная таблица.
Внешне таблица с фильтром в Excel выглядит так же, как и любая другая, но в верхней части каждого столбца появляются специальные стрелки.
это очень просто! Допустим, у нас есть таблица с заголовками. Поскольку фильтр в таблице Excel применяется к определенной части таблицы, вам необходимо сначала выбрать эту часть. Сам Excel никогда не угадает, что вы хотите, поэтому выделим нужные ячейки в строке с заголовками таблиц.
Примечание:
После выбора нужных столбцов вам нужно фактически установить фильтр. Самый простой способ — перейти на вкладку «Основные» / «Фильтр и сортировка» ленты и щелкнуть фильтр. Вот скриншот Office 2010; в других версиях Excel фильтр применяется к таблице таким же образом.Если вы выберете ВСЕ строку, Excel автоматически установит фильтр на ВСЕ столбцы, которые хоть что-то содержат. Если в столбце ничего нет, фильтр не применяется.
После нажатия этой кнопки к выбранным ячейкам будет добавлен фильтр.
В моем примере из трех столбцов, показанных на изображении, фильтр будет добавлен только к двум, ячейки которых были выбраны ранее.Если вы больше не выбираете ячейки, а просто щелкаете по одной, фильтр добавляется к ВСЕЙ строке, как если бы вы его выбрали.
Показанный пример включения фильтра для таблицы является самым простым. Посмотрим, как это работает.
Сразу после активации фильтра таблица не изменится (кроме стрелок, которые появляются в заголовках столбцов). Чтобы отфильтровать некоторые из необходимых данных, щелкните стрелку в столбце, по которому нужно выполнить фильтрацию.
Чтобы убрать фильтрацию (не снимая фильтр!), Просто отметьте все флажки. Тот же эффект появится при полном удалении фильтра: таблица вернется к своей исходной форме.Значение фильтра:
заключается в том, что Excel оставит только те строки таблицы, которые в ЭТОМ (с настроенным фильтром) столбце содержат ячейку с выбранным значением. Остальные строки будут скрыты.
Фильтры в таблице Excel можно комбинировать. Несколько фильтров применяются по принципу логического «И». Что это значит, давайте посмотрим на примере.
Если у нас есть несколько столбцов в таблице, мы можем сначала установить фильтр, например, для первого столбца. В этом случае часть строк будет скрыта согласно условию фильтрации. Затем мы установим фильтр в следующем столбце. В этом случае оставшиеся после первого фильтра строки будут дополнительно отфильтрованы. Вкратце, к каждой строке будут применяться 2 фильтра одновременно: к первому столбцу и ко второму.
По мере применения каждого последующего фильтра количество отображаемых в таблице строк, удовлетворяющих условиям ВСЕХ фильтров, будет уменьшаться. Таким образом, последовательно применяя несколько фильтров, вы можете быстро сделать небольшой выбор из огромной таблицы из тысяч строк.
С полученным образцом можно проделывать дальнейшую работу. Например, вы можете скопировать эти строки и перенести их в другую таблицу. Кроме того, вы можете отфильтровать строки, от которых хотите полностью избавиться! И тогда, сняв фильтр после их удаления, вы получите таблицу, очищенную от ненужных данных.
Теперь посмотрите видео, где я показываю реальный пример фильтрации данных в списке из более чем 15 000 строк.
После фильтрации информации по параметрам одного из столбцов пользователь имеет право продолжить фильтрацию для остальных столбцов. Значения выпадения уже меньше, но результат будет более детальным.
Например, мы уже отфильтровали таблицу по условию «Страна» и оставили только товары российского происхождения.
Теперь необходимо дополнительно разделить товар стоимостью 100 рублей, для этого применяем скрининг в разделе «Цена».
Нажимаем «Ок» и получаем только товары на 100 рублей российского производства.
Расширенный поиск позволяет фильтровать информацию по нескольким условиям одновременно. Работая с ним, перед тем, как вставить фильтр в таблицу excel, нужно подготовить саму таблицу: создать поле над ней из нескольких свободных строк и скопировать заголовки.
Затем в свободной строке под скопированными заголовками задайте необходимые условия поиска. Например, нужно найти товары российского производства, которые продает менеджер Иванов, которые стоят менее 300 рублей.
После того, как параметры были введены правильно, необходимо повторно открыть вкладку «Данные» и выбрать функцию «Дополнительно».
Перед пользователем появится окно, в котором ему нужно будет заполнить две строчки:
После того, как вы сформировали оба диапазона, нажмите «ОК» и оцените результат.
Чтобы узнать, установлен ли поиск данных или нет, нужно посмотреть строку с названием параметров — это первая строка. Если он есть, в каждой ячейке строки появится маленький значок.
«1» указывает столбец, в котором поиск установлен, но не применяется. Значок «2» указывает на уже выполненный поиск.
Большинство людей согласятся, что стандартный расширенный фильтр — вещь ужасно неудобная. И да, это действительно так. Кроме того, необходимо создать конкретную таблицу условий, вручную ввести туда данные, а также открыть отдельное диалоговое окно и ввести туда диапазоны. Но если вы используете макросы, большинство операций можно автоматизировать.
Кроме того, достаточно использовать очень легкий макрос, который запрограммирован на запуск расширенного фильтра при изменении любой ячейки в диапазоне условий. Согласитесь, это намного дешевле. Для этого вызовите контекстное меню ссылки на этот лист и щелкните запись «Исходный текст». После этого появится окно, в которое вы можете вставить этот код.
Частный вторичный рабочий лист_Изменение (значение ByVal как диапазон)
Если он не пересекается (Target, range («A2: I5»)), это ничего, тогда
В случае ошибки Продолжить Далее
ActiveSheet.ShowAllData
Интервал («А7»). CurrentRegion.AdvancedFilter Действие: = xlFilterInPlace, CriteriaRange: = Диапазон («A1»). CurrentRegion
Конец, если
Конец подзаголовка
Конечно, вам нужно вводить наиболее подходящие ячейки в конкретной ситуации. Теперь, когда ячейка изменяется, эта подпрограмма будет выполняться. Как только вы измените ячейку, которая находится в диапазоне с условиями (другими словами, в пустую ячейку), все фильтры будут автоматически удалены, а расширенный также будет открыт.
Все, никаких дополнительных действий выполнять не нужно. Фильтр автоматически подстраивается под любые изменения, сделанные пользователем.
После того, как была реализована команда автоматического применения расширенного фильтра к таблице, при малейшем исправлении условий теперь вы можете понять, как обычно работают сложные условия в расширенном фильтре.
Помимо точных совпадений, человек имеет право использовать дополнительные символы, некоторые из которых позволяют более гибко настраивать поиск. При этом не имеет значения, какой регистр у символов. Чтобы было понятнее, вот таблица с подробным описанием всех возможных условий.
Здесь необходимо учитывать следующие моменты:
Огромным преимуществом расширенного фильтра является то, что вы можете использовать логические условия. Что это? Логическое условие — это условие, в котором используется логический оператор И или ИЛИ. Конечно, есть и другие, но они описаны выше.
Особенностью любой логической операции является то, что она возвращает условие «Истина» или «Ложь». В случае расширенного фильтра условие будет следующим: если определенное условие выполнено.
То есть логическое условие «И» фактически означает «если критерий 1 и критерий 2 удовлетворяют критерию 3». Например, если яблоки и морковь одинаковые, то. Или — точно так же, просто поставьте «Или» вместо «Е». На практике это может выглядеть так. Если пользователи приобрели продукты у компании 1 или компании 2, просмотрите эти покупки. В этом случае, когда вы покупаете что-то у третьего лица, это не отображается.
Фактически, любой фильтр, простой или расширенный, содержит логические условия. Но вы можете сделать их использование более гибким.
Итак, что вам нужно знать? Если условия находятся в одной строке, хотя и в разных ячейках, они рассматриваются как те, которые связаны между собой логическим оператором «И».
В описанном примере фильтр покажет только те бананы, которые были куплены в московском Ашане в третьем квартале.
Если условия необходимо связать с помощью логического оператора «ИЛИ», то они должны быть записаны в разных строках.
Например, в приведенном ниже примере фильтр будет учитывать только заказы менеджера Волиной на персики, закупленные в Москве, и лук, проданный в третьем квартале в Самаре.
Если вам нужно наложить более одного условия на столбец, то в качестве варианта вы можете просто продублировать заголовок столбца в диапазоне критериев и написать под ним следующие условия, например, вы можете применить фильтр, чтобы отображались все транзакции, сделанные из С марта по май.
Поэтому при соблюдении вышеперечисленных рекомендаций расширенный фильтр больше не будет казаться таким неудобным инструментом, а набор его функций будет очень широким и фактически покроет любую задачу, которую пользователь ставит перед собой.
Чтобы настроить использование фильтра:
- кнопка «Фильтр» на вкладке «Дизайн» ленты (также на вкладке «Главная» или «Данные» для быстрого отчета);
- вкладка «Фильтр» на боковой панели.
Примечание. Чтобы применить расширенные настройки фильтра, используйте язык программирования Fore.
Доступны следующие варианты фильтров:
Скрыть пустые значения. Строки / столбцы, не содержащие информации, будут скрыты;
Скрыть нулевые значения. Строки / столбцы, содержащие нулевые значения, будут скрыты;
Скрыть нечисловые значения. Строки / столбцы, содержащие только нечисловые значения, будут скрыты;
Не исключены и владельцы предметов. Родители строк / столбцов, не исключенных фильтром, не будут скрыты;
Скрыть условно. Строки / столбцы будут скрыты, если все данные ячеек соответствуют указанному условию в раскрывающемся списке. В большинстве условий для сравнения с результатом ячейки используется одно или два числа:
- равно A;
- не равно A;
- большой A;
- самый маленький;
- больше или равно А;
- меньше или равно A;
- между A и B включены;
- меньший или больший B;
Для сложной формулы используйте параметр «Выражение». При использовании этого элемента доступна подстановка «значение», соответствующая значению ячейки. При использовании выражения доступны стандартные арифметические операции, объединяющие выражение в круглые скобки. Использование функций прикрепленных модулей доступно в текущих отчетах:
- Скрыть по формуле. Строки / столбцы, соответствующие формуле, указанной через «Редактор выражений>», будут скрыты>»;
- Исключить отмеченное фильтром. Выбранные элементы измерения скрыты. Параметр доступен при раздельной фильтрации строк и столбцов.
Примечание. Если имеется несколько измерений строки / столбца, будут применены настройки последнего включенного измерения.
Чтобы отключить фильтр, нажмите верхнюю часть кнопки «Фильтр» на вкладке ленты «Данные» или «Дизайн» (в быстром отчете) или «Дизайн» (для выбранного блока панели мониторинга).
Слайсеры представляют собой те же фильтры, но размещены в отдельной области и имеют удобное графическое представление. Срезы — это не часть листа ячеек, а отдельный объект, набор кнопок, расположенный на листе Excel. Использование слайсеров не заменяет автофильтр, но благодаря удобному виду облегчает фильтрацию: все примененные критерии видны одновременно. Срезы добавляются в Excel с 2010 года.
В Excel 2010 вы можете использовать слайсер для сводных таблиц, а в 2013 вы можете создать слайсер для любой таблицы.
Для этого выполните следующие действия:
Давайте посмотрим, как фильтровать ячейки с определенным текстом в Excel. Самый простой способ — по аналогии с предыдущим примером ввести в поиск необходимый текст (или его часть.
Однако вы можете настроить выбор более гибко. Если вы нажмете «Текстовые фильтры» в окне фильтра, в контекстном меню появится выбор метода сопоставления: равно, не равно, начинается с, заканчивается, содержит, не содержит.
Например, вам нужно выбрать людей, которых зовут не Богдан. Выбираем вариант «не содержит» и пишем для него критерий «Богдана». Ставим пробелы до и после имени. В противном случае, например, Богданов Егор Егорович тоже попадет под фильтр, хотя имя его не Богдан:
Я расскажу, как в Excel вставить фильтр для двух условий в одну ячейку. Для этого щелкните Текстовые фильтры — Пользовательский фильтр.
Допустим, нам нужно выбрать людей с именем Богдан или Никита. Пишем логику как на картинке
И вот результат:
Как определить, какой оператор сравнения выбрать: «И» или «ИЛИ»? Логика такая:
Подробнее о логических операторах вы можете прочитать в этой статье.
Кроме того, в условии могут использоваться следующие операторы:
Например, чтобы выбрать полное имя, содержащее строку «ctor», напишите условие следующим образом: * ctor*.
Числовые фильтры тоже можно гибко настраивать, есть такие способы выбора:
Как видите, есть интересные варианты. Попробуем выделить людей с оборотом 200 000 — 500 000. Выберем пункт МЕЖДУ. Нравится:
для числовых данных, как и для текста, есть собственный фильтр.
Стандартными способами — ничего. Microsoft Excel выбирает только данные в столбцах. Следовательно, необходимо искать другие решения.
Вот несколько примеров расширенных критериев строки фильтра в Excel:
Чтобы дать пример того, как работает фильтр строк в Excel, давайте создадим таблицу:
Создадим выпадающий список для списка товаров:
Вставьте пустую строку над таблицей с исходными данными. В ячейки мы введем формулу, которая покажет, из каких столбцов берется информация.
Рядом с выпадающим списком ячеек введите следующую формулу: Ее задача — выбрать из таблицы те значения, которые соответствуют определенному продукту
Поэтому с помощью инструмента «Раскрывающийся список» и встроенных функций Excel выбирает данные в строках на основе определенного критерия.
