Сводные таблицы Excel — фильтрация данных

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

• Фильтрация с использованием слайсеров.
• Фильтрация с использованием фильтров отчетов.
• Фильтрация данных вручную.
• Фильтрация с использованием Label Filters.
• Фильтрация с использованием Value Filters.
• Фильтрация с использованием фильтров даты.
• Фильтрация с использованием Top 10 Filter.
• Фильтрация с использованием временной шкалы.

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

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

Фильтры отчетов

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

Перетащите область из строк в фильтры в областях сводной таблицы.

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

Вы заметите, что

• Значения продавца отображаются в строках.

• Значения месяца отображаются в столбцах.

• Фильтр регионов появляется сверху, по умолчанию выбрано ВСЕ.

• Суммирующим значением является сумма суммы заказа.

  • Сумма суммы заказа в зависимости от продавца отображается в столбце Общая сумма.

  • Сумма суммы заказа По месяцам появляется в строке Итого.

• Нажмите на стрелку в поле справа от области фильтра.

Значения продавца отображаются в строках.

Значения месяца отображаются в столбцах.

Фильтр регионов появляется сверху, по умолчанию выбрано ВСЕ.

Суммирующим значением является сумма суммы заказа.

Сумма суммы заказа в зависимости от продавца отображается в столбце Общая сумма.

Сумма суммы заказа По месяцам появляется в строке Итого.

Нажмите на стрелку в поле справа от области фильтра.

Появится раскрывающийся список со значениями поля Регион. Установите флажок Выбрать несколько элементов .

По умолчанию все флажки отмечены. Снимите флажок ( Все ). Все коробки будут сняты.

Затем установите флажки — Юг и Запад и нажмите ОК.

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

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

Ручная фильтрация

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

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

Нажмите на стрелку в ячейке Метки столбцов.

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

• Снимите флажок (Выбрать все) в верхней части списка значений.

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

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

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

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

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

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

• Нажмите на значок.

• Установите / снимите флажки значений.

Нажмите на значок.

Установите / снимите флажки значений.

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

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

• Нажмите на стрелку в ячейке Ярлыки строк.

Нажмите на стрелку в ячейке Ярлыки строк.

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

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

• Снимите флажок (Выбрать все) и проверьте Уолтерс, Крис.

• Нажмите ОК.

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

Снимите флажок (Выбрать все) и проверьте Уолтерс, Крис.

Нажмите ОК.

Сводная таблица отображает только те значения, которые связаны с выбранным значением поля Месяц — февраль и значение поля Продавец — Уолтерс, Крис.

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

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

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

Фильтрация по тексту

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

Данные содержат данные о сотрудниках — EmployeeID, Title, BirthDate, MaritalStatus, Gender и HireDate. Кроме того, данные также имеют уровень менеджера сотрудника (уровни 0 — 4).

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

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

• Нажмите на стрелку в ячейке Ярлыки строк.

• Выберите Title в поле Select Field из выпадающего списка.

• Нажмите на Фильтры меток.

• Нажмите Содержит во втором раскрывающемся списке.

Нажмите на стрелку в ячейке Ярлыки строк.

Выберите Title в поле Select Field из выпадающего списка.

Нажмите на Фильтры меток.

Нажмите Содержит во втором раскрывающемся списке.

Откроется диалоговое окно «Фильтр меток (заголовок)». Введите менеджер в поле рядом с Содержит. Нажмите ОК.

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

• Нажмите на значок.

Нажмите на значок.

Ты это видишь отображается с указанием следующего —

• Фильтр меток применяется к полю — Заголовок и
• Что такое применяемый фильтр меток

Фильтрация по значениям

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

• Нажмите на стрелку в ячейке Ярлыки строк.

• Выберите Title в поле Select Field из выпадающего списка.

• Нажмите на Фильтры Значения.

• Выберите Больше или равно из второго раскрывающегося списка.

Нажмите на стрелку в ячейке Ярлыки строк.

Выберите Title в поле Select Field из выпадающего списка.

Нажмите на Фильтры Значения.

Выберите Больше или равно из второго раскрывающегося списка.

Откроется диалоговое окно «Фильтр значений (заголовок)». Введите 25 в поле справа.

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

Фильтрация по датам

Возможно, вы захотите отобразить данные всех сотрудников, которые были приняты на работу в 2015-15 финансовом году. Вы можете использовать фильтры данных для того же, как показано ниже:

• Включите поле HireDate в сводную таблицу. Теперь вам не нужны данные менеджера, поэтому вы удалите поле ManagerLevel из сводной таблицы.

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

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

• Нажмите стрелку в ячейке Ярлыки строк.

• Выберите HireDate в поле «Выбрать поле» из выпадающего списка.

• Нажмите Фильтры даты.

• Seelct Between из второго выпадающего списка.

Нажмите стрелку в ячейке Ярлыки строк.

Выберите HireDate в поле «Выбрать поле» из выпадающего списка.

Нажмите Фильтры даты.

Seelct Between из второго выпадающего списка.

Откроется диалоговое окно «Фильтр даты» (HireDate). Введите 1.04.2014 и 31.03.2015 в два поля «Дата». Нажмите ОК.

Сводная таблица будет отфильтрована для отображения только данных с HireDate в период с 1 апреля 2014 года по 31 марта 2015 года.

Вы можете сгруппировать даты в кварталы следующим образом —

• Щелкните правой кнопкой мыши по любой из дат. Откроется диалоговое окно « Группировка ».

• Введите 1/4/2014 в поле Начиная с. Установите флажок

• Введите 31.03.2015 в поле Окончание в. Установите флажок

• Нажмите Кварталы в поле под.

Щелкните правой кнопкой мыши по любой из дат. Откроется диалоговое окно « Группировка ».

Введите 1/4/2014 в поле Начиная с. Установите флажок

Введите 31.03.2015 в поле Окончание в. Установите флажок

Нажмите Кварталы в поле под.

Даты будут сгруппированы в кварталы в сводной таблице. Вы можете сделать таблицу компактной, перетащив поле HireDate из области ROWS в область COLUMNS.

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

Фильтрация с использованием Top 10 Filter

Фильтр Top 10 можно использовать для отображения нескольких верхних или нижних нескольких значений поля в сводной таблице.

• Нажмите стрелку в ячейке Ярлыки строк.

• Нажмите Фильтры значений.

• Нажмите Top 10 во втором выпадающем списке.

Нажмите стрелку в ячейке Ярлыки строк.

Нажмите Фильтры значений.

Нажмите Top 10 во втором выпадающем списке.

Откроется диалоговое окно Top 10 Filter (Title).

• В первом поле нажмите сверху (вы также можете выбрать снизу).

• Во втором поле введите число, скажем, 7.

• В третьем поле у ​​вас есть три варианта, по которым вы можете фильтровать.

  • Нажмите на элементы для фильтрации по количеству элементов.

  • Нажмите на проценты, чтобы отфильтровать по процентам.

  • Нажмите на сумму, чтобы отфильтровать по сумме.

• Поскольку у вас есть счет EmployeeID, нажмите Элементы.

• В четвертом окне нажмите на поле Count of EmployeeID.

• Нажмите ОК.

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

Во втором поле введите число, скажем, 7.

В третьем поле у ​​вас есть три варианта, по которым вы можете фильтровать.

Нажмите на элементы для фильтрации по количеству элементов.

Нажмите на проценты, чтобы отфильтровать по процентам.

Нажмите на сумму, чтобы отфильтровать по сумме.

Поскольку у вас есть счет EmployeeID, нажмите Элементы.

В четвертом окне нажмите на поле Count of EmployeeID.

Нажмите ОК.

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

Как вы можете заметить, наибольшее количество рабочих мест в финансовом году — это количество техников-технологов, и преобладающее число таких сотрудников — в Qtr1.

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

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

Создайте сводную таблицу из данных сотрудника, которые вы использовали ранее, и добавьте данные в модель данных в диалоговом окне «Создание сводной таблицы».

• Перетащите поле Название в область ROWS.

• Перетащите поле EmployeeID в область AL VALUES и выберите «Счет» для расчета.

Перетащите поле Название в область ROWS.

Перетащите поле EmployeeID в область AL VALUES и выберите «Счет» для расчета.

• Нажмите на сводную таблицу.

• Нажмите вкладку INSERT.

• Нажмите Временная шкала в группе Фильтры. Откроется диалоговое окно «Вставка временных шкал».

Нажмите на сводную таблицу.

Нажмите вкладку INSERT.

Нажмите Временная шкала в группе Фильтры. Откроется диалоговое окно «Вставка временных шкал».

• Установите флажок HireDate.
• Нажмите ОК. Временная шкала появится на листе.
• Инструменты временной шкалы появляются на ленте.

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

• Нажмите на стрелку рядом с — МЕСЯЦЕВ.

• Выберите КВАРТАЛЫ из выпадающего списка. Отображение временной шкалы меняется на Все периоды — в кварталах.

Нажмите на стрелку рядом с — МЕСЯЦЕВ.

Выберите КВАРТАЛЫ из выпадающего списка. Отображение временной шкалы меняется на Все периоды — в кварталах.

• Нажмите на 2014 Q1.

• Держите нажатой клавишу Shift и перетащите на 2014 Q4. Период временной шкалы выбирается с 1 по 4 квартал 2014 года.

• Сводная таблица фильтруется для этого периода времени.

Нажмите на 2014 Q1.

Держите нажатой клавишу Shift и перетащите на 2014 Q4. Период временной шкалы выбирается с 1 по 4 квартал 2014 года.

Сводная таблица фильтруется для этого периода времени.

Очистка фильтров

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

Очистка всех фильтров в сводной таблице

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

• Нажмите вкладку HOME на ленте.
• Нажмите Сортировка и фильтр в группе Редактирование.
• Выберите Очистить из выпадающего списка.

Очистка метки, фильтра даты или значения

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

Нажмите на значок в метках строк или меток столбцов.

Нажми на <имя поля>, из которого вы хотите очистить фильтр в поле «Выбрать поле» в раскрывающемся списке.

Нажмите Очистить фильтр от <Filed Name>, которое появится в раскрывающемся списке.

Нажмите ОК. Конкретный фильтр будет очищен.