Как добавить фильтр в сводную таблицу: 7 шагов

Приемы работы в MS Excel, обучение MS Excel, надстройка Ёxcel.

GIF

kak-otfiltrovat-stolbets-svodnoj-tablitsy_3.gif

Чтобы увидеть видео включите в браузере GIF-Анимацию.

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

Введение в фильтр сводных таблиц

Сводная таблица – это удобный инструмент для работы с электронными таблицами в Excel, который позволяет суммировать, группировать, выполнять математические операции, такие как SUM, AVERAGE, COUNT и т. Д., Из организованных данных, которые хранятся в базе данных. Помимо математических операций, Pivot обладает одной из лучших функций – фильтрацией, которая позволяет извлекать определенные результаты из наших данных.

Давайте посмотрим на несколько способов использования фильтра в PIVOT.

Как отфильтровать сводную таблицу в Excel?

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

Вы можете скачать этот фильтр сводных таблиц здесь – Фильтр сводных таблиц

Пример № 1 – Создание встроенного фильтра в таблице PIVOT

Шаг 1: Давайте разместим данные на одном из рабочих листов.

pivot-table-filter-2.png.webp

Приведенные выше данные состоят из 4 различных столбцов с номерами слотов, номеров квартир, ковровых покрытий и SBA.

Шаг 2. Перейдите на вкладку «Вставка» и выберите сводную таблицу, как показано ниже.

pivot-table-filter-3.png.webp

При нажатии на сводную таблицу появляется окно «Создать сводную таблицу».

pivot-table-filter-4.png.webp

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

Шаг 3: Поле сводной таблицы будет доступно на правом конце листа, как показано ниже. Мы можем наблюдать поле Filter, где мы можем перетащить поля в фильтры, чтобы создать фильтр в сводной таблице.

pivot-table-filter-5.png.webp

Давайте перетащим поле Flat no в Фильтры, и мы увидим, что фильтр для Flat no был бы создан.

pivot-table-filter-6.png.webp

Исходя из этого, мы можем отфильтровывать номера Flat согласно нашим требованиям, и это нормальный способ создания фильтра в сводной таблице.

Пример № 2 – Создание фильтра для областей значений

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

pivot-table-filter-7.png.webp

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

pivot-table-filter-8.png.webp

Получив фильтры, мы можем теперь выполнять различные типы операций из областей значений, а также сортировать их по величине и наименьшему, чтобы узнать максимальные продажи / площадь / что-либо. Точно так же мы можем сортировать от наименьшего к наибольшему, сортируя по цвету, и даже мы можем выполнять числовые фильтры, такие как <=, =, > и многие другие. Это играет важную роль в принятии решений в любой организации.

Пример № 3 – Отображение списка нескольких элементов в фильтре сводной таблицы

В приведенном выше примере мы узнали о создании фильтра в Pivot. Теперь давайте посмотрим, как мы отображаем список по-разному. 3 наиболее важных способа отображения списка нескольких элементов в фильтре сводной таблицы: –

  • Использование слайсеров
  • Создание списка ячеек с критериями фильтра
  • Список значений, разделенных запятыми

1. Использование слайсеров:

Давайте создадим простую сводную таблицу с различными столбцами, такими как Регион, Месяц, Номер единицы, Функция, Отрасль, Возрастная категория.

pivot-table-filter-9.png.webp

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

pivot-table-filter-10.png.webp

Это просто, поскольку мы просто выбираем любую ячейку внутри сводной таблицы, и мы перейдем к анализу вкладки на ленте и выберем вставку среза, а затем мы вставим срез в нашу область фильтра, поэтому в этом случае «Функция» подали в нашу область фильтра и затем нажали Ok, и это собирается добавить слайсер на лист.

pivot-table-filter-11.png.webp

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

pivot-table-filter-12.png.webp

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

Итак, «Создать список ячеек с критериями фильтра» приходит нам на помощь.

2. Создайте список ячеек с критериями фильтра:

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

pivot-table-filter-13.png.webp

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

pivot-table-filter-14.png.webp

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

pivot-table-filter-15.png.webp

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

3. Список значений, разделенных запятыми:

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

Это новая формула или новая функция, которая была введена в Excel 2016 и называется TEXTJOIN (если у вас нет Excel 2016, вы также можете использовать функцию сцепления) объединение текста значительно упрощает этот процесс.

TEXTJOIN в основном дает нам три разных аргумента.

Разделитель – который может быть запятой или пробелом.

Ignore Empty – true или false, чтобы игнорировать пустые ячейки или нет.

Текст – добавьте или укажите диапазон ячеек, которые содержат значения, которые мы хотим объединить.

pivot-table-filter-16.png.webp

Давайте наберем текст join- (delimiter-, который будет «, » в этом случае TRUE (поскольку мы должны игнорировать пустые ячейки), A: A (так как список выбранных элементов из фильтра будет доступен в этом столбце), чтобы присоединить любое значение, а также игнорировать любое пустое значение в фильтре сводных таблиц)

pivot-table-filter-17.png.webp

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

pivot-table-filter-18.png.webp

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

pivot-table-filter-19.png.webp

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

То, что нужно запомнить

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

Рекомендуемые статьи

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

  1. Сортировка данных в сводной таблице
  2. Сводная таблица с несколькими листами
  3. Условное форматирование Excel в сводной таблице
  4. VBA Pivot Table | Шаблон Excel
  5. Сводная таблица обновления VBA (примеры)

Шаги

  1. 1

    Запустите Microsoft Excel.

  2. 2

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

  3. 3

    Выберите рабочий документ со сводной таблицей. Откройте его в новой вкладке.

  4. 4

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

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

  6. 6

    Перетяните нужный ярлык столбца, который вы будете использовать для создания фильтра, в секцию “Установить фильтр” в списке опций для редактирования сводной таблицы.
    • Имя этого поля уже может находиться в секции “Ярлыки столбцов”.
    • Название ярлыка уже может быть указано в списке названий всех клеток таблицы.
  7. 7

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

Применение фильтра к данным сводного отчета

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

Написание макросов для фильтров

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

kartinka-1.png

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

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

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

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

302-8706fd84bdeda9d836fe70ea347366f3.png

В Excel предусмотрено три типа фильтров:

  1. Автофильтр – для отбора записей по значению ячейки, по формату или в соответствии с простым критерием отбора.
  2. Срезы – интерактивные средства фильтрации данных в таблицах.
  3. Расширенный фильтр – для фильтрации данных с помощью сложного критерия отбора.

Функция Срез

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

Чтобы вставить срез выполните следующие действия:

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

Появится раздел Работа со сводными таблицамисо вкладками Параметрыи Конструктор.

2. На вкладке Параметрыв группе Сортировка и фильтрнажмите кнопку Вставить срез.

image010_83.gifimage011_54.jpg

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

4. Нажмите кнопку ОК. Для каждого из выбранных полей будет отображен срез.

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

 

Выноска 2: Фильтр, примененный в срезеВыноска 2: Кнопка Очистить фильтрimage016_26.jpg

Чтобы выбрать несколько элементов, щелкните их по очереди, удерживая нажатой клавишу CTRL.

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

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

Управление сводными таблицами в Excel

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

Об этой статье

Эту страницу просматривали 10 530 раз.

Интеллектуальное автозаполнение при вводе формул

В OfficeExcel2010 существует очень удобная возможность. Предположим, что имеется отформатированная с помощью командыФорматировать как таблицу (FormatAsTable)таблица. В нее нужно добавить еще один столбец, в каждую ячейку которого нужно ввести формулу. После ввода формулы в первую ячейку добавленного столбца и нажатия на клавишу [Enter],введенная формула автоматически копируется вниз до конца столбца таблицы.

Выноска 3: Добавленный столбецВыноска 3: Формула, введенная в первую ячейку столбцаimage019_19.jpg

ВыноскаВыноска 3: Параметры автозаполненияimage022_16.jpg

Расширенный фильтр

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

Чтобы применить расширенный фильтр, нужно создать диапазон условий дляобработки данных, которые требуется фильтровать. Диапазон условий полезен для извлечениязаписей, основанных на вычисленных критериях, так как в формулах, по которым производитсярасчет значений для критериев, можно использовать функции Excel. С помощью этихкритериев вы можете либо фильтровать список на месте, подобно инструменту Фильтр (Filter),либо скопировать в другое место.

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

· верхнюю строку вы должны заполнить теми же заголовками столбцов, которые содержатся в диапазоне данных с сохранением порядка их следования;

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

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

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

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

1. Создайте диапазон условий для списка.

2. В первой строке введите соответствующие заголовки столбцов.

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

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

5. Щелкните на любой ячейке из диапазона данных и на вкладке Данные (Data) в группе команд Сортировка и фильтр (Sort&Filter) нажмите на кнопку Дополнительно (Advanced).

Откроется диалоговое окно Расширенный фильтр (AdvancedFilter):

Блок-схема: узел: 3Блок-схема: узел: 2Блок-схема: узел: 1image037_16.gif

1 – Выбор места расположенияфильтрованных данных

2 – Ссылка на диапазонданных, включая заголовкистолбцов.

3- Ссылка на диапазонусловий, включаязаголовки столбцов.

6. Заполните все поля диалогового окна Расширенный фильтр (AdvancedFilter) и нажмите ОК.

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

7. Чтобы отключить расширенный фильтр, щелкните по кнопке Очистить (Clear).

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...