Как автоматически фильтровать строки на основе значения ячейки в Excel?
Как автоматически фильтровать строки на основе значения ячейки в Excel?
Расширенные фильтры позволяют выбрать один или несколько столбцов на основе сложных критериев отбора. Кроме того, результат фильтрации может быть помещен в указанную область текущего или нового листа.
Для фильтрации списка с помощью расширенного фильтра столбцы списка должны иметь заголовки. Электронная таблица также должна иметь не менее трех пустых строк над списком. Эти строки будут использоваться в качестве набора условий отбора. Если строк нет, вставьте их. Чтобы использовать расширенный фильтр, сделайте следующее
- Скопируйте отфильтрованные заголовки столбцов из списка в первую пустую строку диапазона выделения.
- Введите нужные критерии отбора в строках под заголовками столбцов. Убедитесь, что между значениями условия и списком есть хотя бы одна пустая строка.
- Укажите ячейку в списке и выполните команду Данные => Фильтр => Расширенный фильтр.
- В открывшемся окне установите переключатель Обработка в положение Фильтровать список вместо (чтобы отобразить результат фильтрации, скрыв ненужные строки) или Копировать результаты в другое место (чтобы скопировать отфильтрованные строки в другую область листа). Во втором случае перейдите в поле Поместить результат в диапазон, затем укажите левую верхнюю ячейку области вставки.
- Введите в поле Диапазон условий ссылку на ячейки, содержащие условия выбора, включая заголовки столбцов. Чтобы скрыть диалоговое окно Расширенный фильтр, пока выбран диапазон условий выделения, нажмите кнопку Свернуть диалоговое окно.
- Нажмите кнопку OK.
Условия отбора записываются в отдельном диапазоне, который должен располагаться выше или ниже отфильтрованного списка, отделенный как минимум одной пустой строкой.
Примеры условий отбора расширенного фильтра
Диапазон условий включает минимум одну линию подписей условий и одну линию фактических условий.
Поле Диапазон условий автоматически ссылается на диапазон с именем Критерии, если он существует на листе.
Условия отбора для расширенных фильтров могут включать в себя
- Несколько условий, примененных к одному столбцу,
- Несколько условий, примененных к нескольким столбцам,
- Условия, созданные формулой.
После ввода условий отбора в области действия условия расширенного фильтра вы можете использовать элементы условия так же, как вы настраивали автоматический фильтр. Чтобы выбрать строки ячеек, значения которых находятся в определенных пределах, используйте оператор сравнения (>,<,=,<>). Условие выбора с оператором сравнения должно быть введено в ячейку под заголовком столбца в области условий.
Несколько условий для одного столбца.
При наличии для одного столбца двух и более условий отбора введите эти условия отбора непосредственно друг под другом в отдельные строки. Приведенный диапазон условий отбора отбирает строки, содержащие в столбце «Продавец» значения "Белов", "Батурин", "Сушкин".
Одно условие для нескольких столбцов.
Чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения "Продукты" в столбце «Товар», "Белов" в столбце «Продавец» и объем продаж более 1000р.
Товар | ПРОДАВЕЦ | Продажи |
Продукты | Белов | >1000 |
Разные условия для разных столбцов.
Чтобы найти данные, которые соответствуют одному условию в одном столбце или другому условию в другом столбце, поместите условия отбора в разные строки диапазона условий отбора.
Например, следующий диапазон условий отбора отображает все строки, содержащие значение "Продукты" в столбце «Товар», "Белов" в столбце «Продавец», либо объем продаж, превышающий 1 000р.
Товар | ПРОДАВЕЦ | Продажи |
Продукты | ||
Белов | ||
>1000 |
Один из двух наборов условий для двух столбцов
Вы можете ввести эти условия отбора в отдельные строки, чтобы найти строки, удовлетворяющие одному из двух наборов условий, каждый из которых содержит условия для нескольких столбцов.
Например, следующий диапазон условий отбора отображает строки, содержащие как значение "Белов" в столбце «Продавец», так и объем продаж, превышающий 3 000р., а также строки по продавцу Батурину с продажами более 1 500р.
ПРОДАВЕЦ | Продажи |
Белов | >3000 |
Батурин | >1500 |
Следовательно, мы можем сформировать правила для условий, которые должны быть связаны следующим образом:
— Условия, записанные в одной строке, считаются связанными оператором AND (будут выбраны строки, в которых выполнены оба условия);
— Условия, записанные в разных строках, рассматриваются как связанные с помощью логического оператора OR (выбираются те строки, где выполняется хотя бы одно из условий).
Условия, создаваемые как результат выполнения формулы
Формулы могут создавать вычисленные значения, которые можно использовать в качестве критериев отбора. Если вы определяете условие отбора с помощью формулы, не используйте заголовок столбца в качестве заголовка условия; либо оставьте условие отбора пустым, либо используйте заголовок, который не является заголовком столбца.
Например, следующий диапазон условий отбора отображает строки, которые содержат значение в столбце L, превышающее среднее значение ячеек в диапазоне L8: L24; заголовок условия отбора не используется.
=L8>СРЗНАЧ($L$8:$L$24) |
Внимание. В этом случае условия выбора должны включать ДВЕ ячейки.
Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В примере формулы "L8" является ссылкой на поле (столбец L) первой записи списка.
Если в формуле условия вместо ссылки или имени диапазона используется заголовок столбца, в ячейке появится ошибка #IMS? или #SIGN! Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.
Пример1.
Выберите из списка сотрудников только те строки, которые содержат информацию обо всех женщинах, проработавших в компании более 5 лет. Для этого выполните следующие действия.
- Добавьте новый столбец:
- в ячейку N7 введите заголовок Стаж работы в фирме,
- в ячейку N8 — формулу: = РАЗНДАТ(M8;СЕГОДНЯ();"Y") , которая вычисляет полное количество лет между датой приема на работу и датой сегодня.
Пример2
Выберите из списка сотрудников только те строки, которые содержат информацию о пенсионерах. Для этого мы будем использовать функцию в условиях отбора, которая определяет, является ли возраст, рассчитанный по дате рождения, 55 лет для женщин и 60 лет для мужчин. Для этого выполните следующие действия
- Скопируйте в ячейку А1 заголовок столбца Пол, а ячейку В1 оставить пустой.
- Под заголовками столбцов введите условия:
- в ячейку А2 – Ж,
- в ячейку В2 — =(СЕГОДНЯ()-J8)/365,25>55 (т.е. формулу, сравнивающую возраст с 55); в ячейку А3 – М,
- в ячейку В3 — =(СЕГОДНЯ()-J7)/365,25>60 (т.е. формулу, сравнивающую возраст с 60).
В ячейках при этом отобразится результат сравнения для первой ячейки анализируемого столбца: в В2 — ИСТИНА, а в В3 — #ЗНАЧ!, что не влияет на дальнейшую работу фильтра
- Установите курсор в любую ячейку списка и дайте команду Данные =>Фильтр =>Расширенный фильтр. Остальные параметры оставьте по умолчанию.
- Щелкнув мышью в окне Диапазон условий, выделите ячейки от А1 до В3 (рис. 7.11).
Пример 3
Мы выберем только те строки в Списке сотрудников, которые дают информацию о сотрудниках, имеющих стаж более 8 лет и возраст до 40 лет. Поместите результат в другое место. Наши условия отбора определят, является ли возраст, рассчитанный по дате рождения, 55 лет для женщины и 60 лет для мужчины. Для этого выполните следующие действия.
- Скопируйте заголовок столбца Расширенный возраст компании в ячейку B1, а ячейку A1 оставьте пустой.
- Под заголовками столбцов введите условия: В ячейке B2 — >8 В ячейке A2 — =(TODAY()-J8)/365.25<40 (т.е. формула сравнения возраста с 40);В ячейке A2 будет показан результат сравнения для первой ячейки анализируемого столбца FALSE, что не влияет на остальную работу фильтра
- В ячейке B2 — >8
- В ячейке A2 — =(TODAY()-J8)/365.25<40 (т.е. e.
- В ячейке A2 результат сравнения первой ячейки анализируемого столбца будет отображаться FALSE, что не повлияет на остальную работу фильтра
- Установите курсор в любую ячейку списка и используйте команду Данные =>Фильтр =>Расширить фильтр. Выберите опцию Копировать результат в другое место, нажмите на поле Поместить результат в диапазон и выберите первую ячейку в диапазоне для размещения результата фильтрации. Выбранные данные будут помещены в диапазон из указанной ячейки
- Щелчком мыши в окне Диапазон условий выберите ячейки с A1 по B2 (рис. 7.12).
Фильтр для уникальных записей
Вы можете использовать функции расширенного фильтра для извлечения уникальных значений из столбца и вставки их в новое место. Этого можно добиться, выполнив перечисленные ниже действия.
- Выберите столбец или ячейку в списке, который требуется отфильтровать. Убедитесь, что диапазон ячеек содержит заголовок столбца
- В меню Данные выберите команду Фильтр, а затем — команду Расширенный фильтр.
Отобразится диалоговое окно Расширенный фильтр. - Выполните одно из следующих действий:
- Если вы хотите фильтровать список на месте, аналогично функции Автофильтр, выберите Фильтровать список на месте.
- Чтобы скопировать результат фильтра в другое место, выберите Копировать результат в другое место и введите ссылку на ячейку в поле Поместить результат в область. В противном случае нажмите Закрыть диалог, чтобы временно скрыть диалог, выберите ячейку на рабочем листе, а затем нажмите Развернуть диалог .
- Установите флажок Только уникальные записи и нажмите кнопку ОК.
Начиная с ячейки, указанной в поле Копировать в диапазон, уникальные значения копируются в новое место.
Пример
Определите, какие имена повторяются в списке сотрудников. Чтобы решить эту задачу, сначала определите, какие имена есть в списке, а затем подсчитайте количество повторений.
- Выделите столбец В с заголовком, т.е. диапазон В8:В24.
- Дайте команду Данные =>Фильтр =>Расширенный фильтр. Выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон и выделите первую ячейку диапазона для размещения результата фильтрации. Отобранные данные будут помещены в диапазон, начинающийся с указанной ячейки . Поставьте флажок в поле Только уникальные записи (рис. 7.13) и нажмите кнопку ОК.
Как автоматически фильтровать строки на основе значения ячейки в Excel?
Функция FILTER позволяет фильтровать диапазон данных в соответствии с определенными условиями.
Примечание: в настоящее время эта функция доступна только подписчикам Microsoft 365.
В следующем примере мы использовали формулу =FILTER(A5:D20;C5:C20=H2;»»), чтобы вернуть все записи для Apple, которые были выбраны в ячейке H2, или, если яблоки не были выбраны, пустую строчку («»).
Функция FILTER позволяет отфильтровать таблицу по массиву логических значений (истина/ложь).
=ФИЛЬТР(массив;включить;[если_пусто])
Матрица диапазона для фильтрации
Массив логических переменных такой же высоты или ширины, как и массив.
Значение, возвращаемое, если все элементы массива пусты (фильтр ничего не возвращает).
Это может быть диапазон значений, столбец значений или их комбинация. Массив для приведенной выше формулы FILTER — A5:D20.
Функция FILTER возвращает массив, который перемещается в другие ячейки, если он является конечным результатом формулы. Это означает, что Excel динамически создает диапазон массива соответствующего размера, когда вы нажимаете клавишу ENTER. Если вспомогательные данные хранятся в рабочем листе Excel, массив будет автоматически изменять размер при добавлении и удалении данных из пространства массива, если вы используете структурированные ссылки. Дополнительную информацию см. в статье о преобразовании массивов.
Третий аргумент будет использоваться, если набор данных может вернуть пустое значение ([if_empty]). В противном случае вы получите ошибку #CURRENT error!, поскольку Excel в настоящее время не поддерживает пустые массивы.
Если любое значение аргумента include является ошибкой (#N/A, #VALUE и т.д.) или не может быть преобразовано в булево значение, функция FILTER возвращает ошибку.
Приложение Excel имеет ограниченную поддержку динамических матриц в межбухгалтерских операциях, и этот сценарий возможен только в том случае, если открыты обе рабочие книги. Если исходная рабочая книга закрыта, все связанные формулы динамической матрицы после обновления вернут ошибку # WARNING!
Примеры
F ILTER функция используется для возврата нескольких условий
Здесь мы используем оператор умножения (*), чтобы вернуть все значения в диапазоне массива (A5:D20), которые содержат текст «Apple» и находятся в восточном регионе: =FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);»»).
Функция F ILTER используется для возврата нескольких условий и сортировки
Используя функцию FILTER и функцию сортировки, мы можем получить все значения в диапазоне массива (A5:D20), которые имеют текст «Apple» И находятся в Восточном регионе, затем отсортировать единицы в порядке убывания: =SORT(FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);4″);4;-1)
Здесь мы используем функцию FILTER с оператором сложения (+), чтобы вернуть все значения в диапазоне массива (A5:D20), которые содержат текст «Apple» ИЛИ находятся в восточном регионе, а затем отсортировать единицы в порядке убывания: =SORT(FILTER(A5:D20;(C5:C20=H1)+(A5:A20=H2);»),4;-1).
Обратите внимание, что ни одна из функций не требует абсолютных ссылок, так как они находятся только в одной ячейке, а их результаты сообщаются в соседних ячейках.
Дополнительные сведения
Задайте вопрос эксперту или обратитесь за помощью к сообществу Answers, отправив вопрос в сообщество Excel Tech.