Как фильтровать в сводной таблице Excel

Обновлено: 05.07.2024

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

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

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

Нажмите любую ячейку в сводной таблице.
Перетащите поле в область «Фильтры» на панели «Поля сводной таблицы».

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

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

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

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

Очистить фильтр

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

Фильтр сводной таблицы очищается, и все данные снова отображаются.

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

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

Вы также можете снять отметку с поля в списке полей.

Фильтр полностью удаляется из сводной таблицы.

БЕСПЛАТНЫЙ краткий справочник

Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.

Существуют различные способы фильтрации данных в сводной таблице Excel.

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

Это руководство охватывает:

Типы фильтров в сводной таблице

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

Давайте рассмотрим эти фильтры один за другим:

Фильтр отчетов. Этот фильтр позволяет детализировать подмножество всего набора данных. Например, если у вас есть данные о розничных продажах, вы можете проанализировать данные по каждому региону, выбрав один или несколько регионов (да, это также позволяет выбирать несколько). Этот фильтр создается путем перетаскивания поля сводной таблицы в область фильтров.
Фильтр меток строк/столбцов. Эти фильтры позволяют фильтровать релевантные данные на основе элементов поля (например, фильтровать определенный элемент или элемент, содержащий определенный текст) или значений (например, фильтровать первые 10 элементов по значению или элементам). со значением больше/меньше указанного значения).
- Поисковое окно. Вы можете получить доступ к этому фильтру в фильтре ярлыков строк/столбцов, что позволяет быстро фильтровать на основе введенного текста. Например, если вам нужны данные только для Costco, просто введите здесь Costco, и он отфильтрует их для вас.
- Флажки. Они позволяют выбирать определенные элементы из списка. Например, если вы хотите вручную выбрать розничных продавцов для анализа, вы можете сделать это здесь. Кроме того, вы также можете выборочно исключить некоторых розничных продавцов, сняв соответствующий флажок.
Обратите внимание, что пользователю доступны еще два инструмента фильтрации: слайсеры и временные шкалы (которые не рассматриваются в этом руководстве).

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

Примеры использования фильтров в сводной таблице

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

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

Давайте посмотрим, как использовать фильтр Top 10 с этим набором данных.

Фильтровать верхние/нижние элементы по значению

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

Вот как это сделать:

Это даст вам отфильтрованный список из 10 розничных продавцов на основе их стоимости продаж.

Вы можете использовать тот же процесс, чтобы получить последние 10 (или любое другое число) элементов по значению.

Фильтровать верхние/нижние элементы, которые составляют указанный процент от значения

Вы можете использовать фильтр "10 лучших", чтобы получить список первых 10 процентов (или любого другого числа, скажем, 20 процентов, 50 процентов и т. д.) элементов по стоимости.

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

Вот как это сделать:

Это даст вам отфильтрованный список розничных продавцов, на долю которых приходится 25 % от общего объема продаж.

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

Фильтровать верхние/нижние элементы, составляющие указанное значение

Допустим, вы хотите найти ведущих розничных продавцов, на долю которых приходится 20 миллионов продаж.

Это можно сделать с помощью фильтра "10 лучших" в сводной таблице.

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

Фильтрация элементов по ценности

Вы можете фильтровать элементы на основе значений в столбцах в области значений.

Предположим, у вас есть сводная таблица, созданная с использованием данных о розничных продажах, как показано ниже:

Вы можете отфильтровать этот список по объему продаж. Например, предположим, что вы хотите получить список всех розничных продавцов, объем продаж которых превышает 3 млн.

Вот как это сделать:

Это немедленно отфильтрует список и покажет только тех розничных продавцов, продажи которых превышают 3 миллиона.

Аналогичным образом можно использовать множество других условий, таких как "равно", "не равно", "меньше", "между" и т. д.

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

Фильтры ярлыков удобны, когда у вас есть огромный список и вы хотите отфильтровать определенные элементы на основе их имени/текста.

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

Вот как это сделать:

Вы также можете использовать подстановочные знаки вместе с текстом.

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

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

Фильтрация данных с помощью окна поиска

Фильтрация списка с помощью поля поиска очень похожа на параметр "содержит" в фильтре ярлыков.

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

Вот шаги:

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

В поле поиска можно использовать подстановочные знаки. Например, если вы хотите получить названия всех розничных продавцов, названия которых начинаются с буквы T, используйте строку поиска как T* (за T следует звездочка). Поскольку звездочка представляет собой любое количество символов, это означает, что имя может содержать любое количество символов после T.

Аналогично, если вы хотите получить список всех розничных продавцов, имена которых заканчиваются на букву T, используйте поисковый запрос *T (звездочка, за которой следует T).

Есть несколько важных вещей, которые нужно знать о панели поиска:
- Если вы отфильтруете один раз по одному критерию, а затем снова отфильтруете по другому, первый критерий будет отброшен, и вы получите список вторых критериев. Фильтрация не является аддитивной.
- Преимущество использования окна поиска заключается в том, что вы можете вручную отменить выбор некоторых результатов. Например, если у вас есть огромный список финансовых компаний и вы хотите отфильтровать только банки, вы можете выполнить поиск по термину «банк». Но на случай, если появятся какие-то компании, не являющиеся банками, вы можете просто снять галочку и не пускать ее.
- Вы не можете исключить определенные результаты. Например, если вы хотите исключить только розничных продавцов, в которых есть доллары, это невозможно сделать с помощью окна поиска. Однако это можно сделать с помощью фильтра ярлыков с условием «не содержит».
Вам также могут понравиться следующие учебные пособия по сводным таблицам:

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

СЛОЖНЫЕ ФИЛЬТРЫ

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

Допустим, вы хотите отобразить только те строки, в которых общий объем продаж для Юга превышает 5000 долларов США.

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

Вы можете добавить срез на основе столбца "Продажи", но он вернется к исходному набору данных и отфильтрует строки. Вы не знаете, какие строки в конечном итоге принесут на Юге более 5000 долларов США.

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

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

АВТОФИЛЬТР

Чтобы использовать эту функцию, вам необходимо один раз выполнить следующие действия, чтобы сделать ее доступной:
1. Найдите кнопку "Отменить". Она расположена над или под лентой на полосе, известной как панель быстрого доступа.
2. Нажмите правой кнопкой мыши рядом с кнопкой "Отменить" и выберите "Настроить панель быстрого доступа". Откроется диалоговое окно «Параметры Excel» с панелью «Настройка панели быстрого доступа».
3. В раскрывающемся меню в левом верхнем углу диалогового окна выбраны популярные команды. Измените его на Команды не на ленте.
4. Примерно через 15 элементов вниз по левому списку вы увидите автофильтр. Нажмите на эту команду. В центре диалогового окна нажмите кнопку "Добавить>>", чтобы добавить автофильтр в список справа.
5. Нажмите «ОК», чтобы закрыть диалоговое окно «Параметры Excel». Теперь вы должны увидеть значок автофильтра на панели быстрого доступа.
Как правило, внутри сводной таблицы нельзя использовать значок "Фильтр" на вкладке "Данные" на ленте. Например, если вы выбрали E4 на рис. 1, вы увидите, что значок "Фильтр" на вкладке "Данные" неактивен.

Тем не менее, есть хак, доступный с помощью значка автофильтра на панели быстрого доступа. Выберите ячейку непосредственно под итоговой суммой по южному региону. На рисунке 1 это ячейка E17. Введите критерий фильтра в ячейку, например >5000 . Примерами других критериев могут быть >0 , 0 или . После нажатия Enter Excel обычно переходит в ячейку E18. Обязательно повторно выберите ячейку критериев в E17.

Выделив ячейку E17, щелкните значок автофильтра на панели быстрого доступа. Происходит несколько удивительных вещей:
- Раскрывающиеся списки фильтров появятся в ячейках B4:F4. (Вы могли бы подумать, что это невозможно, учитывая, что значок фильтра становится серым, когда вы выбираете ячейку в сводной таблице.)
- Некоторые части группы "Фильтр" больше не отображаются серым цветом, но могут работать не так, как ожидалось.
- Ячейка критериев в E17 теперь скрыта фильтром. Если вы хотите изменить критерий, вам сначала нужно очистить фильтр (описано ниже).
НЕЗАМЕТНАЯ ФУНКЦИОНАЛЬНОСТЬ

Обратите внимание, что автофильтр не является частью инструментов сводной таблицы, но связан с функцией "Фильтр" на вкладке "Данные". Это означает, что некоторые вещи не будут работать так, как вы ожидаете. В частности:
- В строке "Общий итог" отображается сумма всех строк, независимо от того, видимы они или нет. Изменение параметра «Включить отфильтрованные элементы в итоги» в раскрывающемся списке «Промежуточные итоги» на вкладке «Конструктор сводной таблицы» не изменит общий итог.
- Когда вы изменяете исходные данные и нажимаете "Обновить" для обновления сводной таблицы, критерий автофильтра не применяется повторно автоматически.
- Чтобы очистить фильтр, откройте раскрывающееся меню в ячейке E4 и выберите "Очистить фильтр с юга".
- Чтобы отключить раскрывающиеся списки фильтров, выберите ячейку G4, а затем включите значок "Фильтр" на вкладке "Данные".
Сценарий фильтрации сводной таблицы на основе значений определенного столбца кажется частым. Неясно, предполагала ли Microsoft, что метод автофильтра можно использовать описанным здесь способом.

СФ ГОВОРИТ

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

Сводные таблицы создают отличные отчеты в Microsoft Excel, но добавление одного или двух фильтров может сделать их еще более гибкими. Вот как.



Изображение: utah778, Getty Images/iStockphoto

Windows: обязательно к прочтению

Сводные таблицы в Microsoft Excel — это отличный способ систематизировать и анализировать данные, и чем больше вы знаете об этой функции, тем больше пользы вы от нее получите. Например, фильтрация сводной таблицы — отличный способ сосредоточиться на конкретной информации, и вы часто увидите, что эта возможность добавляется к информационным панелям. К счастью, фильтровать сводную таблицу несложно, и в этой статье я покажу вам два способа сделать это.

Я использую Microsoft 365, но вы можете использовать более ранние версии. Вы можете скачать демонстрационный файл .xlsx или работать со своими данными.Браузерная версия полностью поддерживает инструмент сводных таблиц.

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

Сводная таблица в Excel

Прежде чем мы сможем начать фильтрацию, нам понадобится сводная таблица, поэтому мы создадим сводную таблицу, показанную на рисунке А, на основе данных, показанных на том же листе. Для этого щелкните в любом месте набора данных и выполните следующие действия:
1. Перейдите на вкладку "Вставка", а затем нажмите "Сводная таблица" в группе "Таблицы".
2. В появившемся диалоговом окне выберите параметр «Существующий рабочий лист», чтобы вы могли одновременно видеть данные и сводную таблицу, и введите F1 (рис. Б) в качестве местоположения.
3. Нажмите "ОК", и Excel отобразит рамку сводной таблицы и список полей.
4. Используя рисунок C в качестве руководства, постройте сводную таблицу, показанную на рисунке A.
Рисунок А


Создайте эту сводную таблицу.

Рисунок Б


Рисунок C


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

Как использовать автофильтр в Excel

После того как вы создадите сводную таблицу, вы можете сразу приступить к фильтрации, используя то, что уже есть. Этот раскрывающийся список в ячейке «Ярлыки строк» представляет собой автофильтр, аналогичный фильтру, который вы используете в обычном наборе данных. Нажмите на нее, и вы увидите несколько опций, с которыми вы, вероятно, уже знакомы. Существует ряд встроенных фильтров, таких как «Содержит», «Не содержит», «Равно» и т. д. В этом случае снимите флажок «Выбрать все» и установите флажок для Джеймса, чтобы просмотреть записи только для Джеймса (рис. D).


Вы также можете свернуть области Люка и Марты, щелкнув значок [-] слева от их имен. Разница в том, что их имена и общая сумма по-прежнему будут видны. Вы только удаляете их данные.

Используя раскрывающееся меню "Ярлыки строк", вы также можете выполнить фильтр поиска, введя полное или частичное значение. Например, воспользуемся этой функцией, чтобы просмотреть всех сотрудников, в именах которых есть буква а:
1. При необходимости полностью разверните сводную таблицу.
2. В поле поиска введите букву A (рис. E).
3. Нажмите "ОК".
Рисунок Д


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

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

Как добавить фильтр в интерфейс Excel

Раскрывающийся список и поисковые фильтры удобны для вас, но не подходят для тех, кто может просматривать информацию в вашей сводной таблице. К счастью, в интерфейс можно добавить элемент управления фильтрацией. Для иллюстрации добавим фильтр для региона следующим образом:
1. Во-первых, при необходимости полностью разверните сводную таблицу.
2. Нажмите внутри сводной таблицы, чтобы отобразить список полей. Если он не появляется, щелкните правой кнопкой мыши сводную таблицу и выберите «Показать список полей» в нижней части появившегося подменю.
3. В списке полей перетащите регион из верхней панели в область фильтров (рис. F). Excel добавит фильтр над сводной таблицей. В раскрывающемся списке выберите Северо-восток и следите за соответствующим обновлением сводной таблицы.
Рисунок F

Вы можете перетащить любое поле в сводной таблицы в область фильтров. Например, на рисунке G показаны данные, отфильтрованные по персоналу, в частности по Джеймсу, для Северо-восточного региона. Обратите внимание, однако, что при этом данные о персонале удаляются из фактической сводной таблицы. У Джеймса есть 1163 доллара на май месяц в северо-восточном регионе. Если хотите, потратьте немного времени на смену фильтров и посмотрите, как изменится результирующая сводная таблица.

Читайте также: