Как сделать фильтр в excel по столбцам

Краткое руководство: фильтрация данных с помощью автофильтра

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

При фильтрации можно проводить поиск текста и чисел, используя поле Поиск в интерфейсе фильтра.

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

Процедура

Выделите данные, которые требуется отфильтровать.

1. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр.
2. Щелкните стрелку в заголовке столбца, чтобы отобразить список, в котором можно выбрать значения фильтра. Примечание. В зависимости от типа данных в столбце Microsoft Excel отображает в списке числовые фильтры или текстовые фильтры.

Фильтрация выбором значений или поиском

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

1. Используйте поле Поиск для ввода текста или чисел, которые следует найти

2. Установите и снимите флажки для отображения значений, найденных в столбце данных

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

1. Чтобы выбрать значения для списка, снимите флажок (Выделить все). При этом снимаются все флажки. Затем выберите только те значения, которые хотите видеть, и нажмите кнопку ОК, чтобы посмотреть результаты.
2. Чтобы найти текст в столбце, введите текст или числа в поле Поиск. Также можно использовать подстановочные знаки, например звездочку (*) или знак вопроса (?). Нажмите клавишу ВВОД, чтобы получить результаты.

Фильтрация данных с указанием условий

Указывая условия, вы можете создавать собственные фильтры, которые отбирают данные именно так, как вы хотите. Для этого применяется конструирование фильтров. Оно должно быть знакомо вам, если вы когда-нибудь делали запросы к базе данных.

1. Наведите указатель мыши на числовые фильтры или текстовые фильтры в списке. Откроется меню, в котором можно задавать фильтрацию по различным условиям.
2. Выберите условие, а затем выберите или введите критерии. Нажмите кнопку И кнопка для сочетания критериев (то есть два или более критериев, все из которых должны быть выполнены), или кнопку Или, чтобы потребовать выполнения только одного из критериев.
3. Нажмите кнопку OK, чтобы применить фильтр и получить ожидаемые результаты.

Дальнейшие действия

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

Фильтрация данных в диапазоне или таблице

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

Фильтрация диапазона данных

1. Выберите любую ячейку в диапазоне данных.
2. Выберите Данные >Фильтр.
3. Выберите стрелку в столбце .
4. Выберите пункт Текстовые фильтры или Числовые фильтры, а затем — сравнение, например пункт Между.
5. Введите условия фильтрации и нажмите кнопку ОК.

Фильтрация данных в таблице

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

1. Выберите стрелку в столбца, который вы хотите отфильтровать.
2. Снимите флажок (Выделить все) и установите флажки для тех элементов, которые вы хотите отобразить.
3. Нажмите кнопку ОК.

Стрелка заглавного изменится на фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.

Горизонтальная фильтрация столбцов в Excel

Если вы не совсем начинающий пользователь, то, должно быть, уже заметили, что на 99% всё в Excel устроено для работы именно с вертикальными таблицами, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Сводные таблицы, промежуточные итоги, копирование формул двойным щелчком — всё заточено именно под такой формат данных. Однако же, нет правил без исключений и с достаточно регулярной периодичностью меня спрашивают что делать если в работе попалась таблица с горизонтальной смысловой ориентацией, либо таблица, где строки и столбцы имеют одинаковый по смыслу вес:
И если сортировать по горизонтали Excel ещё умеет (командой Данные — Сортировка — Параметры — Сортировать столбцы), то с фильтрацией всё обстоит хуже — встроенных инструментов для фильтрации столбцов, а не строк в Excel просто нет. Так что, если перед вами встала такая задача, придется придумывать обходные пути разной степени сложности.

Способ 1. Новая функция ФИЛЬТР

Если вы работаете на новой версии Excel 2021 или в подписке Excel 365, то можно воспользоваться недавно появившейся функциейФИЛЬТР(FILTER), умеющей фильтровать исходные данные не только по строчкам, но и по столбцам. Для работы эта функция требует вспомогательный горизонтальный одномерный массив-строку, где каждое значение (ИСТИНА или ЛОЖЬ) определяет показываем ли мы или, наоборот, скрываем очередной столбец в таблице. Добавим такую строку над нашей таблицей и пропишем в ней статус каждого столбца:

• Допустим, мы всегда хотим отображать первый и последний столбцы (заголовки и итоги), поэтому для них в первой и последней ячейках массива зададим значение =ИСТИНА.
• Для остальных столбцов содержимое соответствующих ячеек будет формулой, которая проверяет нужное нам условие с помощью функций И (AND) или ИЛИ (OR) . Например, что итог находится в интервале от 300 до 500.

После этого останется лишь использовать функцию ФИЛЬТР для отбора столбцов, над которыми в нашем вспомогательном массиве есть значение ИСТИНА:

Аналогичным образом можно отфильтровать столбцы и по заданному списку. В этом случае поможет функция СЧЁТЕСЛИ (COUNTIF) , проверяющая количество вхождений очередного названия столбца из шапки таблицы в разрешённый список:

Способ 2. Сводная таблица вместо обычной

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

• иметь «правильную» однострочную строку заголовка без пустых и объединенных ячеек — иначе не получится построить сводную таблицу;
• не содержать дубликатов в подписях строк и столбцов — они «схлопнутся» в сводной в список только уникальных значений;
• содержать только числа в области значений (на пересечении строк и столбцов), т.к. сводная таблица обязательно применит к ним какую-то агрегирующую функцию (сумму, среднее и т.д.) и с текстом это не сработает

Если все эти условия выполняются, то для построения сводной, внешне похожей на нашу исходную таблицу, её (исходную) нужно будет развернуть из кросс-таблицы в плоскую (нормализовать). А это проще всего сделать с помощью надстройки Power Query — мощного инструмента преобразования любых данных, встроенного в Excel начиная с 2016-й версии.

1. Преобразуем таблицу в «умную» динамическую командой Главная — Форматировать как таблицу (Home — Format as Table) .
2. Загружаем в Power Query командой Данные — Из таблицы/диапазона (Data — From Table/Range).
3. Фильтруем строку с итогами (в сводной будут свои итоги).
4. Щёлкаем правой кнопкой мыши по заголовку первого столбца и выбираем Отменить свёртывание других столбцов (Unpivot Other Columns) . Все невыделенные столбцы преобразуются в два — имя сотрудника и значение его показателя.
5. Фильтруем столбец с итогами, который ушёл в колонку Атрибут.
6. Строим сводную таблицу по получившейся плоской (нормализованной) таблице командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close & Load — Close & Load to. ) .

Теперь можно пользоваться имеющейся в сводных таблицах возможностью фильтрации столбцов — привычными галочками напротив имён и пунктами Фильтры по подписи (Label Filters) или Фильтры по значению (Value Filters) :

И само собой при изменении данных нужно будет обновить наш запрос и сводную сочетанием клавиш Ctrl + Alt + F5 или командой Данные — Обновить всё (Data — Refresh All) .

Способ 3. Макрос на VBA

Все предыдущие способы, как легко заметить, представляют собой не совсем фильтрацию — мы не скрываем столбцы в исходном списке, а формируем новую таблицу с заданным набором столбцов из исходной. Если же требуется именно фильтровать (скрывать) столбцы в исходных данных, то нужен принципиально другой подход, а именно — макрос.

Предположим, что мы хотим «на лету» фильтровать столбцы, где имя менеджера в шапке таблицы удовлетворяет заданной в жёлтой ячейке А4 маске, например, начинается с буквы «А» (то бишь получить в результате «Анна» и «Артур»).

Как и в первом способе, сначала реализуем вспомогательный диапазон-строку, где в каждой ячейке формулой будет проверяться наш критерий и выводиться логические значения ИСТИНА или ЛОЖЬ для видимых и скрытых столбцов соответственно:

Затем добавим простой макрос. Щёлкните правой кнопкой мыши по ярлычку листа и выберите команду Исходный код (Source code) . В открывшееся окно скопируйте и вставьте следующий VBA-код:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub

Логика его работы следующая:

• В общем и целом — это обработчик события Worksheet_Change, т.е. этот макрос будет автоматически запускаться при любом изменении любой ячейки на текущем листе.
• Ссылка на изменённую ячейку всегда будет лежать в переменной Target.
• Сначала мы проверяем, что пользователь изменил именно ячейку с критерием (A4) — это делает оператор if.
• Затем запускается цикл For Each. для перебора серых ячеек (D2:O2) со значениями-индикаторами ИСТИНА / ЛОЖЬ для каждого столбца.
• Если значение очередной серой ячейки равно ИСТИНА (true), то столбец не скрывается, в противном случае — скрываем его (свойство Hidden).

Ссылки по теме

• Функции динамических массивов из Office 365: ФИЛЬТР, СОРТ и УНИК
• Сводная по таблице с многострочной шапкой с помощью Power Query
• Что такое макросы, как их создавать и использовать

Функция ФИЛЬТР

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше

Функция ФИЛЬТР позволяет выполнять фильтрацию диапазона данных на основе условий, которые вы определяете.

В следующем примере мы использовали формулу =FILTER(A5:D20,C5:C20=H2,»») для возврата всех записей Для Apple, как указано в ячейке H2, и если яблок нет, возвращается пустая строка («»).

Функция ФИЛЬТР фильтрует массив с учетом массива логических значений (ИСТИНА/ЛОЖЬ).

=ФИЛЬТР(массив;включить;[если_пусто])

Массив или диапазон для фильтрации

Массив логических переменных с аналогичной высотой или шириной, что и массив.

Значение, возвращаемое, если все значения во включенном массиве пустые (фильтр не возвращает ничего)

• Массив может рассматриваться как ряд значений, столбец со значениями или комбинация строк и столбцов значений. В приведенном выше примере массив для нашей формулы ФИЛЬТР представляет собой диапазон A5:D20.
• Функция ФИЛЬТР возвращает массив, который будет переноситься на другие ячейки, если является конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете структурированные ссылки. Дополнительные сведения см. в статье о переносе массива.
• Если набор данных потенциально может возвращать пустое значение, используйте третий аргумент ([если_пусто]). В противном случае возникнет ошибка #ВЫЧИС! , так как Excel в настоящее время не поддерживает пустые массивы.
• Если какое-либо значение аргумента include является ошибкой (#N/A, #VALUE и т. д.) или не может быть преобразовано в логическое значение, функция FILTER вернет ошибку.
• Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

Примеры

Функция ФИЛЬТР, используемая для возврата нескольких условий

В данном случае мы используем оператор умножения (*) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст «Яблоко» И находящихся в восточном регионе: =ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);»»).

Функция ФИЛЬТР, используемая для возврата нескольких условий и сортировки

В данном случае мы используем предыдущую функцию ФИЛЬТР с функцией СОРТ для возврата всех значений в диапазоне массива (A5:D20), содержащих текст «Яблоко» И находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);»»);4;-1)

В данном случае мы используем функцию ФИЛЬТР с оператором сложения (+) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст «Яблоко» ИЛИ находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)+(A5:A20=H2);»»),4;-1).

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.