Как автоматически фильтровать строки на основе значения ячейки в Excel?

Как автоматически фильтровать строки на основе значения ячейки в Excel?

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

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

1. Скопируйте отфильтрованные заголовки столбцов из списка в первую пустую строку диапазона выделения.
2. Введите нужные критерии отбора в строках под заголовками столбцов. Убедитесь, что между значениями условия и списком есть хотя бы одна пустая строка.
3. Укажите ячейку в списке и выполните команду Данные => Фильтр => Расширенный фильтр.
4. В открывшемся окне установите переключатель Обработка в положение Фильтровать список вместо (чтобы отобразить результат фильтрации, скрыв ненужные строки) или Копировать результаты в другое место (чтобы скопировать отфильтрованные строки в другую область листа). Во втором случае перейдите в поле Поместить результат в диапазон, затем укажите левую верхнюю ячейку области вставки.
5. Введите в поле Диапазон условий ссылку на ячейки, содержащие условия выбора, включая заголовки столбцов. Чтобы скрыть диалоговое окно Расширенный фильтр, пока выбран диапазон условий выделения, нажмите кнопку Свернуть диалоговое окно.
6. Нажмите кнопку OK.

Условия отбора записываются в отдельном диапазоне, который должен располагаться выше или ниже отфильтрованного списка, отделенный как минимум одной пустой строкой.

Примеры условий отбора расширенного фильтра

Диапазон условий включает минимум одну линию подписей условий и одну линию фактических условий.

Поле Диапазон условий автоматически ссылается на диапазон с именем Критерии, если он существует на листе.

Условия отбора для расширенных фильтров могут включать в себя

• Несколько условий, примененных к одному столбцу,
• Несколько условий, примененных к нескольким столбцам,
• Условия, созданные формулой.

После ввода условий отбора в области действия условия расширенного фильтра вы можете использовать элементы условия так же, как вы настраивали автоматический фильтр. Чтобы выбрать строки ячеек, значения которых находятся в определенных пределах, используйте оператор сравнения (>,<,=,<>). Условие выбора с оператором сравнения должно быть введено в ячейку под заголовком столбца в области условий.

Несколько условий для одного столбца.

При наличии для одного столбца двух и более условий отбора введите эти условия отбора непосредственно друг под другом в отдельные строки. Приведенный диапазон условий отбора отбирает строки, содержащие в столбце «Продавец» значения "Белов", "Батурин", "Сушкин".

Одно условие для нескольких столбцов.

Чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения "Продукты" в столбце «Товар», "Белов" в столбце «Продавец» и объем продаж более 1000р.

Разные условия для разных столбцов.

Чтобы найти данные, которые соответствуют одному условию в одном столбце или другому условию в другом столбце, поместите условия отбора в разные строки диапазона условий отбора.

Например, следующий диапазон условий отбора отображает все строки, содержащие значение "Продукты" в столбце «Товар», "Белов" в столбце «Продавец», либо объем продаж, превышающий 1 000р.

Один из двух наборов условий для двух столбцов

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

Например, следующий диапазон условий отбора отображает строки, содержащие как значение "Белов" в столбце «Продавец», так и объем продаж, превышающий 3 000р., а также строки по продавцу Батурину с продажами более 1 500р.

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

— Условия, записанные в одной строке, считаются связанными оператором AND (будут выбраны строки, в которых выполнены оба условия);

— Условия, записанные в разных строках, рассматриваются как связанные с помощью логического оператора OR (выбираются те строки, где выполняется хотя бы одно из условий).

Условия, создаваемые как результат выполнения формулы

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

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

Внимание. В этом случае условия выбора должны включать ДВЕ ячейки.

Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В примере формулы "L8" является ссылкой на поле (столбец L) первой записи списка.

Если в формуле условия вместо ссылки или имени диапазона используется заголовок столбца, в ячейке появится ошибка #IMS? или #SIGN! Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Пример1.

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

1. Добавьте новый столбец:
   • в ячейку N7 введите заголовок Стаж работы в фирме,
   • в ячейку N8 — формулу: = РАЗНДАТ(M8;СЕГОДНЯ();"Y") , которая вычисляет полное количество лет между датой приема на работу и датой сегодня.

Пример2

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

1. Скопируйте в ячейку А1 заголовок столбца Пол, а ячейку В1 оставить пустой.
2. Под заголовками столбцов введите условия:
   • в ячейку А2 – Ж,
   • в ячейку В2 — =(СЕГОДНЯ()-J8)/365,25>55 (т.е. формулу, сравнивающую возраст с 55); в ячейку А3 – М,
   • в ячейку В3 — =(СЕГОДНЯ()-J7)/365,25>60 (т.е. формулу, сравнивающую возраст с 60).
     В ячейках при этом отобразится результат сравнения для первой ячейки анализируемого столбца: в В2 — ИСТИНА, а в В3 — #ЗНАЧ!, что не влияет на дальнейшую работу фильтра
3. Установите курсор в любую ячейку списка и дайте команду Данные =>Фильтр =>Расширенный фильтр. Остальные параметры оставьте по умолчанию.
4. Щелкнув мышью в окне Диапазон условий, выделите ячейки от А1 до В3 (рис. 7.11).

Пример 3

Мы выберем только те строки в Списке сотрудников, которые дают информацию о сотрудниках, имеющих стаж более 8 лет и возраст до 40 лет. Поместите результат в другое место. Наши условия отбора определят, является ли возраст, рассчитанный по дате рождения, 55 лет для женщины и 60 лет для мужчины. Для этого выполните следующие действия.

1. Скопируйте заголовок столбца Расширенный возраст компании в ячейку B1, а ячейку A1 оставьте пустой.
2. Под заголовками столбцов введите условия: В ячейке B2 — >8 В ячейке A2 — =(TODAY()-J8)/365.25<40 (т.е. формула сравнения возраста с 40);В ячейке A2 будет показан результат сравнения для первой ячейки анализируемого столбца FALSE, что не влияет на остальную работу фильтра
3. В ячейке B2 — >8
4. В ячейке A2 — =(TODAY()-J8)/365.25<40 (т.е. e.
5. В ячейке A2 результат сравнения первой ячейки анализируемого столбца будет отображаться FALSE, что не повлияет на остальную работу фильтра
6. Установите курсор в любую ячейку списка и используйте команду Данные =>Фильтр =>Расширить фильтр. Выберите опцию Копировать результат в другое место, нажмите на поле Поместить результат в диапазон и выберите первую ячейку в диапазоне для размещения результата фильтрации. Выбранные данные будут помещены в диапазон из указанной ячейки
7. Щелчком мыши в окне Диапазон условий выберите ячейки с A1 по B2 (рис. 7.12).

Фильтр для уникальных записей

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

1. Выберите столбец или ячейку в списке, который требуется отфильтровать. Убедитесь, что диапазон ячеек содержит заголовок столбца
2. В меню Данные выберите команду Фильтр, а затем — команду Расширенный фильтр.
   Отобразится диалоговое окно Расширенный фильтр.
3. Выполните одно из следующих действий:
   • Если вы хотите фильтровать список на месте, аналогично функции Автофильтр, выберите Фильтровать список на месте.
   • Чтобы скопировать результат фильтра в другое место, выберите Копировать результат в другое место и введите ссылку на ячейку в поле Поместить результат в область. В противном случае нажмите Закрыть диалог, чтобы временно скрыть диалог, выберите ячейку на рабочем листе, а затем нажмите Развернуть диалог .
4. Установите флажок Только уникальные записи и нажмите кнопку ОК.

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

Пример

Определите, какие имена повторяются в списке сотрудников. Чтобы решить эту задачу, сначала определите, какие имена есть в списке, а затем подсчитайте количество повторений.

1. Выделите столбец В с заголовком, т.е. диапазон В8:В24.
2. Дайте команду Данные =>Фильтр =>Расширенный фильтр. Выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон и выделите первую ячейку диапазона для размещения результата фильтрации. Отобранные данные будут помещены в диапазон, начинающийся с указанной ячейки . Поставьте флажок в поле Только уникальные записи (рис. 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.