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

Содержание

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

Есть разные способы фильтрации данных в сводной таблице в Excel.

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

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

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

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

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

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

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

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

В этом разделе рассматриваются следующие примеры:

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

Отфильтровать 10 лучших элементов в сводной таблице

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

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

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

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

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

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

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

Перейдите в Фильтр меток строки -> Фильтры значений -> Первые 10.
В диалоговом окне Top 10 Filter вам необходимо указать четыре параметра:
- Сверху / внизу: в этом случае, поскольку мы ищем 10 ведущих розничных продавцов, выберите «Сверху».
- Количество элементов, которые нужно отфильтровать. В этом случае, поскольку мы хотим получить 10 лучших элементов, это будет 10.
- Третье поле представляет собой раскрывающийся список с тремя вариантами: Элементы, Процент и Сумма. В этом случае, поскольку нам нужны 10 крупнейших розничных продавцов, выберите Товары.
- В последнем поле перечислены все различные значения, перечисленные в области значений. В этом случае, поскольку у нас есть только сумма продаж, отображается только «Сумма продаж».

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

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

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

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

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

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

Перейдите в Фильтр меток строки -> Фильтры значений -> Первые 10.
В диалоговом окне Top 10 Filter вам необходимо указать четыре параметра:
- Сверху / внизу: в этом случае, поскольку мы ищем ведущих розничных продавцов, на долю которых приходится 25% от общего объема продаж, выберите «Сверху».
- Во втором поле вам нужно указать процент продаж, который должны составлять ведущие розничные продавцы. В данном случае, поскольку мы хотим получить топ-ритейлеров, которые обеспечивают 25% продаж, это будет 25.
- В третьем поле выберите Процент.
- В последнем поле перечислены все различные значения, перечисленные в области значений. В этом случае, поскольку у нас есть только сумма продаж, отображается только «Сумма продаж».

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

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

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

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

Вы можете сделать это с помощью фильтра «Топ-10» в сводной таблице.

Сделать это:

Перейдите в Фильтр меток строки -> Фильтры значений -> Первые 10.
В диалоговом окне Top 10 Filter вам необходимо указать четыре параметра:
- Сверху / внизу: в этом случае, поскольку мы ищем ведущих розничных продавцов с общим объемом продаж 20 миллионов, выберите «Сверху».
- Во втором поле необходимо указать значение, которое должны учитывать ведущие розничные продавцы. В этом случае, поскольку мы хотим получить крупнейших розничных продавцов с объемом продаж 20 миллионов, это будет 20000000.
- В третьем поле выберите Сумма.
- В последнем поле перечислены все различные значения, перечисленные в области значений. В этом случае, поскольку у нас есть только сумма продаж, отображается только «Сумма продаж».

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

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

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

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

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

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

Перейдите в Фильтр меток строки -> Фильтры значений -> Больше, чем.
В диалоговом окне «Фильтр значений»:
- Выберите значения, которые вы хотите использовать для фильтрации. В данном случае это сумма продаж (если у вас есть больше элементов в области значений, раскрывающийся список покажет все).
- Выберите условие. Поскольку мы хотим, чтобы объем продаж всех розничных продавцов превышал 3 миллиона, выберите «больше чем».
- В последнем поле введите 3000000.
Щелкните ОК.

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

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

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

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

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

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

Перейдите в Фильтр меток строки -> Фильтры меток -> Содержит.
В диалоговом окне фильтра меток:
- «Содержит» выбрано по умолчанию (поскольку мы выбрали «Содержит» на предыдущем шаге). Вы можете изменить это здесь, если хотите.
- Введите текстовую строку, по которой вы хотите отфильтровать список. В данном случае это «доллар».
Щелкните ОК.

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

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

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

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

Фильтрация списка с использованием поля поиска во многом похожа на параметр «содержит» в фильтре меток.

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

Вот шаги:

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

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

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

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

О панели поиска нужно знать несколько важных вещей:

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

Вам также могут понравиться следующие руководства по сводным таблицам: