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

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

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

Устанавливаем фильтр по подписи

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

В появившемся диалоговом окне Фильтры по подписи, указываем параметр фильтрации (в нашем случае, это значение 7)

Предположим, теперь вам требуется увидеть месяца с наибольшим значением, для этого необходимо применить Фильтр по значению… для этого же поля. Опять жмем на иконку справа от поля Месяц, в выпадающем меню выбираем Фильтры по значению -> Первые 10…

В появившемся диалоговом окне Фильтр «Первые 10», настраиваем отображение первых трех наибольших значения.

Теперь сводная таблица отображает первые 3 месяца с наибольшим значением, но фильтр по подписи пропал. На рисунке отображены значения 2, 6 и 9-го месяцев.

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

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

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

Фильтрации сводных таблиц в Excel 2013 может быть выполнена десятками методов. На рис. 1 приведены лишь некоторые из них.

Рис. 1. Здесь показаны лишь некоторые из великого множества фильтров сводных таблиц

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

• Временная шкала «Дата заказа», расположенная в ячейках Н2:М8, появилась лишь в Excel 2013.
• Срез «Рынок сбыта», расположенная в ячейках Н9:О16, появился в версии Excel 2010.
• Раскрывающий список в ячейке В1 содержит фильтр, который в Excel 2003 именовался фильтром страницы, в Excel 2010 – фильтром отчета, а в Excel 2013 — просто фильтром.
• Традиционные фильтры, активизируемые флажками, находящимися возле каждого элемента сводной таблицы (см. метку «Флажки» на рис. 1).
• Поле «Поиск фильтра», появившееся в версии Excel 2010.
• Подменю, включающее фильтры по подписям.
• В зависимости от типа поля в раскрывающемся списке можно найти подменю Фильтры по значению. В этом подменю может находиться пункт «Первые 10». С помощью этого пункта можно настроить такие фильтры, как «Первые 10», «Последние 5», «Последние 3%», «Первые 8 млн долл.» и прочие подобные фильтры.
• В зависимости от типа поля в раскрывающемся списке может быть подменю Фильтры по дате. В этом подменю могут находиться до 37 виртуальных фильтров, таких как «В следующем месяце», «В прошлом году» и «В этом году».

Фильтры в названиях строк и столбцов

Если в области строк или столбцов сводной таблицы находится поле (или поля), в ячейке заголовка этого поля появится раскрывающийся список, включающий критерии фильтрации. Как показано на рис. 1, в ячейке А4 отображается раскрывающийся список Заказчик, а в ячейке ВЗ – раскрывающийся список Товар. Сводная таблица, показанная на этом рисунке, находится в табличной форме. Если же для сводной таблицы выбрана сжатая форма, раскрывающийся список появится в ячейке Названия строк либо Названия столбцов.

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

Рис. 2. Фильтрация с помощью панели Поля сводной таблицы: наведите курсор на поле, по которому хотите выполнить фильтрацию, и щелкните на появившейся кнопке; откроется список доступных опций (как в левой части на рис. 1).

Фильтрация путем установки флажков

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

Чтобы не отображать сведения о продаже товаров, относящихся к этой категории, раскройте список Товар (рис. 3б, ячейка В3) и отмените установку флажка Препарат. Это действие приведет к сокрытию сведений о продаже товаров из этой категории.

Рис. 3. Фильтрация путем установки флажков

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

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

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

Если вам приходится иметь дело с сотнями заказчиков, сэкономьте время с помощью поля поиска. База данных, приведенная на рис. 4, включает сведения о банках, кредитных союзах и страховых компаниях. Чтобы сузить область поиска до банков и кредитных союзов, выполните следующие действия.

1. Раскройте список Заказчик.
2. В поле поиска введите слово Bank (рис. 4а).
3. По умолчанию устанавливается флажок Выделить все результаты поиска. Щелкните ОК.
4. Снова раскройте список Заказчик.
5. В поле поиска введите слова Credit Union.
6. Установите флажок Добавить выделенный фрагмент в фильтр (рис. 4б). Щелкните ОК.

Рис. 4. Фильтрация данных с помощью поля поиска: (а) по слову Bank; (б) добавлены позиции, содержащие слова CreditUnion

На сводной таблице останутся строки, в названиях которых встречаются либо слова Bank, либо Credit Union.

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

Использование фильтров в областях подписей

Текстовым полям соответствует раскрывающееся меню Фильтры по подписи. Чтобы отфильтровать всех заказчиков, в названии которых содержится слово Insurance, воспользуйтесь фильтром не содержит (рис. 5а). В появившемся на экране диалоговом окне выберите критерий фильтра Ins или Ins* (рис. 5б).

Рис. 5. Использование фильтров в областях подписей: (а) среди фильтров по подписи выберите не содержит; (б) выберите критерий фильтра Ins*

Фильтры по подписи не являются аддитивными. Это означает, что одновременное применение двух разных фильтров невозможно. Если взять набор данных, показанный на рис. 5, и применить новый фильтр по подписи между с аргументами А и Czz (рис. 6), некоторые ранее отфильтрованные страховые компании отобразятся вновь.

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

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

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

1. Раскройте список Заказчик.
2. Выберите пункт Фильтры по значению, выберите фильтр Между (рис. 7а).
3. Введите значения 20000 и 30000 (рис. 7б). Щелкните на кнопке ОК.

Рис. 7. Фильтрация названий столбцов на основе данных из столбца значений (Фильтр по значению)

В результате применения фильтра отображаются заказчики, сумма дохода которых находится между значениями 20 000 и 30 000 долларов включительно (рис. 7б). Применение фильтра по значению приведет к отмене ранее примененных фильтров по подписи.

Фильтр Первые 10

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

1. Раскройте список Заказчик. Выберите пункты меню Фильтры по значению → Первые 10.
2. В первом поле диалогового окна Фильтр «Первые 10» задайте количество отображаемых заказчиков (рис. 8).
3. Во втором поле (со списком) можно выбрать один из двух параметров: наибольших или наименьших.
4. В третьем поле (со списком) можно выбрать один из трех параметров: элементов списка; % от количества элементов; Сумма. Например, в первом случае можно отобразить первые 5 заказчиков, во втором случае – заказчиков, приносящих в сумме 80% дохода, в третьем случае – заказчиков, дающих в сумме 1 000 000 долларов дохода.

Рис. 8. Создание отчета по первым пяти заказчикам

Обратите внимание, что итоговая сумма, равная 166 780 долларам, которая находится в ячейке В9, представляет собой сумму дохода, полученную от отображаемых на экране заказчиков. В эту сумму не входит доход, полученный от «невидимых» заказчиков. Если в процессе выполнения анализа возникает потребность в просмотре итоговой суммы дохода по всем заказчикам, воспользуйтесь одним из следующих способов.

• Пункт раскрывающегося списка Промежуточные итоги, находящегося на контекстной вкладке Конструктор, позволяет включить в итоги значения, соответствующие отфильтрованным элементам. Этот пункт списка доступен только для наборов данных 0LAP. Чтобы превратить обычный набор данных в набор данных 0LAP, обратитесь к средству PowerPivot (глава 10).
• Можно удалить общие итоги из сводной таблицы и создать новую сводную таблицу, состоящую из одной строки и находящуюся под текущим набором данным. Скройте строку заголовка второй сводной таблицы, что приведет к отображению подлинного общего итога в нижней части сводной таблицы.
• Если выделить пустую ячейку справа от заголовка (ячейка СЗ на рис. 8), можно будет воспользоваться фильтром на вкладке Данные. Этот фильтр не предназначен для применения в сводных таблицах и обычно недоступен, если выбрана сводная таблица. Встаньте в ячейку С3, кликните кнопку Фильтр на вкладке Данные ленты. Раскройте список в ячейке ВЗ. Выберите пункт Первые 10 и задайте отображение первых шести элементов. Отобразятся первые пять заказчиков и общий итог для исходного набора данных. Учтите, что эта методика основана на ошибке в Excel, поэтому изменение данных не приведет к обновлению фильтра.

Фильтры по дате

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

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

Рис. 9. Фильтры по дате включают различные периоды времени

Когда поле даты включает значения даты и времени, фильтры по дате могут вести себя непредсказуемым образом. Если, например, запросить отображение дат 4 /15/2015, в ответ можно получить сообщение, в котором говорится о том, что соответствующие записи отсутствуют. Это связано с тем, что значение времени 18:00, относящееся к дате 4/15/2015, хранится в памяти в виде 42109,75. Часть «75» соответствует 18 часам, отсчитываемым от полуночи. Если же нужно отобразить все записи, соответствующие любому периоду времени 15 апреля, выберите пункт Настраиваемый фильтр, и в диалоговом окне Фильтр по дате установите флажок Целые дни (рис. 10).

Рис. 10. Окно Фильтр по дате

Фильтрация с помощью области ФИЛЬТРЫ

Опытные пользователи сводных таблиц еще помнят времена, когда существовала область страниц сводной таблицы. В настоящее время эта область получила название ФИЛЬТРЫ, хотя по-прежнему устроена и работает на принципах, заложенных в основу устаревших версий Excel. Разработчики из компании Microsoft добавили в область ФИЛЬТРЫ возможность выбора нескольких элементов. И хотя эта область не столь наглядна, как срезы, она остается полезной в тех случаях, когда нужно настраивать сводную таблицу для каждого пользователя.

Добавление полей в область ФИЛЬТРЫ. Сводная таблица, показанная на рис. 11, хороша в качестве средства создания отчетов для топ-менеджеров. Стрелки раскрывающихся списков, отображенные в ячейках В1:В8, предназначены для быстрого определения величины дохода, получаемого при выборе произвольных комбинаций секторов, регионов, рынков сбыта, торговых представителей, заказчиков, товаров, дат либо дней недели. Именно для выполнения подобных задач используется фильтр отчета. Для создания подобной сводной таблицы просто перетащите поля Доход и Стоимость оборудования в область ЗНАЧЕНИЯ списка полей сводной таблицы, а затем перетащите поля, по которым будет выполняться фильтрация, в область ФИЛЬТРЫ списка полей сводной таблицы.

Рис. 11. Благодаря нескольким полям в области ФИЛЬТРЫ сводной таблицы можно создавать самые разные отчеты

Область ФИЛЬТРЫ можно настроить совершенно неочевидным образом. На вкладке Анализ щелкните на кнопке Параметры, находящейся в группе Сводная таблица и еще раз Параметры (рис. 12). Окно Параметры сводной таблицы также можно вызвать, кликнув правой кнопкой мыши на сводной таблице, и в контекстном меню выбрать пункт Параметры сводной таблицы.

Рис. 12. Вызов окна Параметры сводной таблицы

В открывшемся окне Параметры сводной таблицы перейдите на вкладку Макет и формат и введите в поле Число полей фильтра отчета в столбце положительное значение, отличное от нуля (рис. 13). Я ввел двойку.

Рис. 13. Окно Параметры сводной таблицы

На рис. 14 показаны фильтры отчета, включающие два поля в колонке. Порядок следования полей можно изменять с помощью раскрывающегося списка Отображать поля в области фильтра отчета, находящегося в окне Параметры сводной таблицы (см. рис. 13). В примере использован параметр вниз, затем поперек. Альтернативный вариант – поперек, затем вниз. Последовательность полей можно менять перетаскивая названия полей в области ФИЛЬТРЫ панели Поля сводной таблицы (см. правую часть рис. 12).

Рис. 14. Область Фильтры представлена четырьмя группами по два поля

Выбор элементов в области ФИЛЬТРЫ. Чтобы отфильтровать сводную таблицу, щелкните на кнопке раскрывающегося списка одного из полей в области фильтра отчета. Раскрывающееся меню начинается со значения (Все), далее приводятся все уникальные элементы списка (рис. 15). Чтобы выбрать нескольких элементов в фильтре отчета в нижней части меню кликните на флажке Выделить несколько элементов. После установки этого флажка можно фильтровать сводную таблицу сразу по нескольким уникальным элементам.

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

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

Рис. 16. Если в фильтре выбрано несколько элементов, то неясно, какие именно; если это критично, воспользуйтесь срезом

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

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

1. Настройте форматирование сводной таблицы. Поскольку вы планируете создать несколько копий сводной таблицы и не собираетесь форматировать каждый лист рабочей книги, тщательно проверьте формат числовых значений и заголовков до репликации.
2. В область ФИЛЬТРЫ добавьте поле Сектор. Для этого поля оставьте заданное по умолчанию значение (Все).
3. Чтобы отобразить контекстную вкладку ленты Анализ, выделите одну из ячеек сводной таблицы.
4. Найдите кнопку Параметры, находящуюся в левой части вкладки Анализ. Справа от этой кнопки находится стрелка раскрывающегося списка. Щелкните на ней. Выберите опцию Отобразить страницы фильтра отчета… (см. рис. 12).
5. В диалоговом окне Отображение страниц фильтра отчета показан список всех полей области отчета. Поскольку сводная таблица включает единственное поле Сектор, в данном случае проблема выбора отсутствует (рис. 17).
6. Щелкните на кнопке ОК, чтобы завершить создание копий сводной таблицы.

Рис. 17. Окно Отображение страниц фильтра отчета

Excel создает новый лист для каждой записи в поле Сектор (рис. 18).

Рис. 18. Для каждой записи поля Сектор автоматически добавлен новый лист

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

В случае изменения исходных данных можно обновить все листы, созданные для записей поля Сектор. Для выполнения этой задачи воспользуйтесь параметром Обновить одной из соответствующих сводных таблиц. Например, после обновления листа Связь обновятся остальные сводные таблицы. Это связано с тем, что все сводные таблицы, созданные репликацией, используют один и тот же кеш (подробнее см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?).

О фильтрации с помощью срезов и временных шкал подробнее см. заметку Excel 2013. Срезы сводных таблиц; создание временной шкалы.

Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.

Надо отметить, что у меня этот трюк не получился. – Прим. Багузина

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

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

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

Для примера возьмем следующую таблицу:

Создадим сводную таблицу: «Вставка» — «Сводная таблица». Поместим ее на новый лист.

Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.

Напомним, как выглядит диалоговое окно сводного отчета:

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

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

Например, среднее количество заказов по каждому поставщику:

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

Установим фильтр в сводном отчете:

1. В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
2. Перетащим это поле в область «Фильтр отчета».
3. Таблица стала трехмерной – признак «Склад» оказался вверху.

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

Например, «1»:

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

Отфильтровать отчет можно также по значениям в первом столбце.

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

Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».

Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:

После нажатия ОК сводная таблица приобретает следующий вид:

Отсортируем данные в отчете по значению столбца «Стоимость». Кликнем правой кнопкой мыши по любой ячейке или названию столбца. Выбираем «Сортировка» и способ сортировки.

Значения в сводном отчете поменяются в соответствии с отсортированными данными:

Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:

Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».

Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):

Формулы в сводных таблицах Excel

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

1. Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
2. Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
3. Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.

Сводный отчет стал более удобным для восприятия:

Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».

Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.

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

Скачать пример управления сводными таблицами

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