Excel фильтр по столбцам

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще...Меньше

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

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

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

Процедура

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

1. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр.

2. Щелкните стрелку в заголовке столбца, чтобы отобразить список, в котором можно выбрать значения фильтра.

   Примечание.    В зависимости от типа данных в столбце Microsoft Excel отображает в списке числовые фильтры или текстовые фильтры.

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

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

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

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

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

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

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

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

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

1. Наведите указатель мыши на числовые фильтры или текстовые фильтры в списке. Откроется меню, в котором можно задавать фильтрацию по различным условиям.

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

3. Нажмите кнопку OK, чтобы применить фильтр и получить ожидаемые результаты.

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

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

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

• Создайте свой фильтр, использующий несколько критериев. Дополнительные сведения см. в статье Фильтрация с помощью расширенных критериев.

• Узнайте, как использовать Фильтр уникальных значений или удаление повторяющихся значений.

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

8286 02.05.2022 Скачать пример

Если вы не совсем начинающий пользователь, то, должно быть, уже заметили, что на 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
• Что такое макросы, как их создавать и использовать

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

Используйте автофильтр или встроенные операторы сравнения, такие как «больше чем» и «первые 10» в Excel, чтобы отобразить нужные данные и скрыть остальные. После того как вы отфильтровали данные в диапазоне ячеек или таблице, вы можете либо повторно применить фильтр, чтобы получить актуальные результаты, либо очистить фильтр, чтобы снова отобразить все данные.

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

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

1. Выберите любую ячейку в диапазоне.

2. Выберите Данные > Фильтр .

3. Выберите стрелку заголовка столбца.

4. Выберите Текстовые фильтры или Числовые фильтры, , а затем выберите сравнение, например Между .

5. Введите критерии фильтра и выберите OK .

Данные фильтра в таблице

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

1. Выберите стрелку заголовка столбца для столбца, который вы хотите отфильтровать.

2. Снимите флажок (Выбрать все) и установите флажки, которые хотите отобразить.

3. Нажмите  OK .

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

Похожие темы

Обучение работе с Excel: фильтрация данных в таблице

Рекомендации и примеры сортировки и фильтрации данных по цвету

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

Фильтровать по расширенным критериям

Удалить фильтр

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

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

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

Два типа фильтров

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

Повторное применение фильтра

Чтобы определить, применяется ли фильтр, обратите внимание на значок в заголовке столбца:

• Стрелка раскрывающегося списка означает, что фильтрация включена, но не применяется.

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

• Кнопка "Фильтр" означает, что фильтр применен.

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

При повторном применении фильтра отображаются другие результаты по следующим причинам:

• Данные были добавлены, изменены или удалены в диапазоне ячеек или столбце таблицы.

• Значения, возвращаемые формулой, изменились, и рабочий лист был пересчитан.

Не смешивайте типы данных

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

Данные фильтра в таблице

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

1. Выберите данные, которые вы хотите отфильтровать. На вкладке Home нажмите Format as Table , а затем выберите Format as Table 9.0016 .

2. В диалоговом окне Создать таблицу вы можете выбрать, будут ли в вашей таблице заголовки.

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

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

3. Нажмите ОК .

4. Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтра.