Фильтрация сводных таблиц Срезами и Шкалой
Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не утонуть в цифрах. Самый простой способ для этого - поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения:
Неудобства такого способа очевидны:
- При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
- Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.
Если у вас Excel 2010, то все это можно делать более изящно - с помощью срезов (slicers). Срезы - это удобное кнопочное графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы:
Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer):
Теперь при выделении или снятии выделения с элементов среза (можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом) в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительный приятный нюанс в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице:
Если вы используете несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных:
Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections), которая открывает соответствующее диалоговое окно:
Тогда выделение элементов на одном срезе будет влиять сразу на несколько таблиц и диаграмм, возможно даже, на разных листах.
Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:
...и возможность создавать свои варианты оформления:
А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:
Ссылки по теме
- Что такое сводные таблицы, как их строить
- Группировка чисел и дат с нужным шагом в сводных таблицах
- Построение отчета сводной таблицы по нескольким диапазонам исходных данных
- Настройка вычислений в сводных таблицах
Огромное, настоящее, человеческое спасибо!
Спасибо.
Столкнулся с проблемой, что при работе со сводной таблицей на основе OLAP куба, не получается вставить срез.
Кнопка активная, но когда нажимаешь "Вставить срез" таблица обновляется, а срез не вставляется.
Погуглил этот вопрос, кое-где рекомендовали поставить 2013 Excel... - поставил, однако проблемы это не решило.
Возможно Вы знаете, куда еще ткнуться, чтобы заработало)
А можно как-то внедрить слайсеры на отдельный лист с диаграммой? .Т.е. на тот лист, который формируется, когда диаграмма перемещается на отдельный лист (move chart -> new sheet)?
Хотелось бы уточнить возможно ли группировать даты в рамках срезов? Например, есть столбец с датами в исходной табличке, на основании этого строится срез для фильтрации значений в сводной. И чтобы в срезе был не перечень дат, а список дат сгруппированный в месяцы.
у меня в сводной (ну и в срезе) порядка 200 -300 записей для выбора и несколько таких таблиц за несколько лет.
можно ли как-то сделать фиксированные списки выбора для фильтра?
чтобы один срез брал значения из одного списка, другой срез (другая сводная) из другого списка.
и т.д.
Подскажите, пожалуйста что еще надо сделать или проверить, чтобы активировать срезы?
Временная шкала предусматривает такую же функцию? Возможно ли подсоеденить ее к нескольким сводам?
Заранее благодарен 8) (за все видеоуроки)
Смена формата файла не помогает, кнопка "Подключения к отчетам" все так же остается не активной.
Подскажите, пожалуйста, можно ли создать сводную таблицу для excel-файла, предназначенного для единовременной работы в нем нескольких пользователей?
Просто в таком файле у меня сейчас опции создания сводных таблиц заблокированы, важно понять, с чем это связано.
,например, чтобы рядом с позициями среза стояли галочки (к примеру) и пользователь может либо нажимать на галку ( то есть активизировать позицию среза) либо нет
Удобно, очень!
Николай, пользуясь случаем выражаю вам свое уважение, скажите пожалуйста как сделать так чтобы срезы и сводные таблицы могли находиться в одном и том же диапазоне, так как во время больших отчетов, сводные и срезы прыгают туда сюда, что очень напрягает. Заранее благодарю. В ожидании Вашего скорого ответа.
Николай, спасибо за Вашу кропотливую работу и труды, плодами которых мы пользуемся с удовольствием. У меня вопрос про срез. Есть ли возможность каким-то образом соединить, чтобы было удобно пользоваться, срез и выпадающий список с автопоиском? Не очень удобно крутить ролик среза по тысячам строк.
Не создавая для них промежуточные названия 01 - Понедельник, 02 - Вторник.... и не используя заранее заготовленный справочник дней недели.
Когда дни недели выбираются из исходника "в лоб".
А как вставить срез (две кнопки), допустим "Руб" и "%" что бы при выборе итог расчитывлся в рублях или в процентах?
нет там вообще поля Менеджер (((
У меня создана еще одна сводная таблица, но при создании проставлена галка в поле "Добавить модель данных", чтобы в «Параметры поля значений» можно было выбрать «Число различных элементов» в качестве операции. И считать разные значения. Как к такой сводной подключить срезы, которые уже подключены к простым сводным, без этой галки?
Или какими способами можно считать разные значения в обычных сводных?
спасибо