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

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

2010_pivot_5.gif

Неудобства такого способа очевидны:

  • При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
  • Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.

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

2010_pivot_6.gif

Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer):

2010_pivot_7.gif

 

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

2010_pivot_8.gif

 

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

2010_pivot_9.gif

Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections), которая открывает соответствующее диалоговое окно:

2010-slicers-connection.png

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

Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:

2010_pivot_11.gif

...и возможность создавать свои варианты оформления:

2010_pivot_12.gif

 

А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:

2010_pivot_13.gif

Ссылки по теме


17.07.2013 12:01:06
Нет ни одного комментария, я что, одна не знала про такую невероятно удобную вещь?!?!
Огромное, настоящее, человеческое спасибо!
21.07.2013 12:00:55
Не за что :)
rvk
25.07.2013 18:13:49
Здравствуйте! А можно ли значение среза вывести в содержимое ячейки? Например, если выбран месяц "Август",то в одной из ячеек появляется слово "Август".
13.11.2013 10:14:42
Думаю, только макросом.
15.08.2013 15:33:56
По каким причинам срез может быть неактивен?
13.11.2013 10:14:01
Книга старой версия Excel?
17.10.2013 18:42:30
кнопка "Вставить срез" может быть неактивна из-за того, что работаете в документе созданном в формате "Excel 97-2003". Сохраните с расширением *.xlsx
13.11.2013 15:11:26
Спасибо! Сама пришла к такому же ответу)
23.12.2013 09:39:37
в 2007 это же нельзя сделать?
25.12.2013 19:27:57
Нет, эта функция появилась начиная с версии Excel 2010.
15.04.2014 13:50:43
Возможно ли поставить запрет на выбор нескольких элементов на срезе?
19.04.2014 09:41:38
Насколько я знаю - нет, по умолчанию - мультивыбор :(
11.06.2014 23:48:34
Здравствуйте! Почему при попытке подключить срез к нескольким сводным таблицам видит только таблицу на текущей странице?
14.06.2014 12:20:50
Подключить срез можно к нескольким сводным таблицам если они построены по одной исходной таблице, т.е. у них один источник данных. У вас один?
21.12.2015 14:58:55
Добрый день! А если 2 или 3 источника данных? Но есть общий столбец (наименование розничной точки) можно как-то "объединить" срезы в один? чтобы при изменении одного среза они применялись и для других.. или просто какой-то общий срез...
А если у них два источника данных? как поступить?Как можно их вместе связать? что бы два одинаковых фильра у двух сводных таблиц работали синхронно. Даже при помощи выпадающего списка
25.11.2014 13:40:11
О, сколько нам открытий чудных, готовит просвещенья дух!!!!!
26.01.2015 17:10:45
Здравствуйте. Подскажите в чем проблема ( Excel 2010) во вкладке: Параметры -Вставить срез (данная опц. не активна?
Спасибо.
28.01.2015 13:19:15
Добрый день. Книга в новом формате, а опция среза не активна. :cry:
01.04.2015 14:24:18
zdrastvuiwe , a video kursi ne budzet?
15.04.2015 23:07:12
Добавил видео ;)
17.04.2015 14:08:35
А где можно скачать файл с примером, который именно на видео???
19.04.2015 17:04:21
А какая разница на каком именно примере это показывать? :)
27.05.2015 13:43:25
Здравствуйте, Николай!

Столкнулся с проблемой, что при работе со сводной таблицей на основе OLAP куба, не получается вставить срез.
Кнопка активная, но когда нажимаешь "Вставить срез" таблица обновляется, а срез не вставляется.
Погуглил этот вопрос, кое-где рекомендовали поставить 2013 Excel... - поставил, однако проблемы это не решило.
Возможно Вы знаете, куда еще ткнуться, чтобы заработало)  
02.07.2015 13:32:36
Добрый день!
А можно как-то внедрить слайсеры на отдельный лист с диаграммой? .Т.е. на тот лист, который формируется, когда диаграмма перемещается на отдельный лист (move chart -> new sheet)?
10.08.2015 08:51:11
Здравствуйте, Николай! Спасибо Вам за Ваш труд!
Хотелось бы уточнить возможно ли группировать даты в рамках срезов? Например, есть столбец с датами в исходной табличке, на основании этого строится срез для фильтрации значений в сводной. И чтобы в срезе был не перечень дат, а список дат сгруппированный в месяцы.
Григорий, создайте стандартно группировку в сводной. Только в окне группировки выберите не только месяцы, но и дни(сами дни можно потом убрать из сводной и оставить только месяцы). И тогда при вставке среза будет доступно еще одно поле - Месяцы. Полагаю, это именно то, что Вам надо.
14.10.2015 21:28:22
Николай! У меня на срезах дат всегда 2 дополнительные кнопки: меньше и больше интервала дат, представленных в таблице. Можно ли убрать эти кнопки?(по ним фильтруются пустые значения)
19.06.2016 06:34:15
Muchas gracias a Nick! Usted ha hecho un excelente recurso, y deseo éxito en todos sus esfuerzos!!!  
23.12.2016 10:37:42
подскажите, а есть ли способы делать синхронизацию среза со списком?
у меня в сводной (ну и в срезе) порядка 200 -300 записей для выбора и несколько таких таблиц за несколько лет.
можно ли как-то сделать фиксированные списки выбора для фильтра?
чтобы один срез брал значения из одного списка, другой срез (другая сводная) из другого списка.
и т.д.
24.12.2016 19:32:35
удалите
12.01.2017 15:04:30
Здравствуйте! Очень понравилась информация про срезы, но кнопка "вставить срез" не активна, хотя Excel 2010 дома и 2013 на работе, файлы с расширением xlsx. Ни в одном, ни в другом случае не получилось.
Подскажите, пожалуйста что еще надо сделать или проверить, чтобы активировать срезы?
28.03.2017 15:08:59
Первый раз смотрю видеоурок с таким живым, интересным объяснением, не монотонным голосом, под который можно уснуть, как в большинстве подобных, а рассказ человека, который любит то, что делает.
28.03.2017 15:26:33
Спасибо :) Приятно слышать :)
25.04.2017 13:31:40
Добрый день!
Временная шкала предусматривает такую же функцию? Возможно ли подсоеденить ее к нескольким сводам?
Заранее благодарен 8) (за все видеоуроки)
07.08.2017 14:29:32
а с чем может быть связано то, что у меня не отображаются ячейки в пивоте, но фильтрация по ним получается (как бдуто они там есть)?
15.02.2018 16:20:42
Т.е., я так понимаю, если книга была создана в более ранней версии Excel, нежели 2010-я, но работаю я с этой книгой в Excel 2016-го года, то уже никак не сделать подобный фильтр?
Смена формата файла не помогает, кнопка "Подключения к отчетам" все так же остается не активной.
20.02.2018 17:46:39
Спасибо за шикарную инструкцию!

Подскажите, пожалуйста, можно ли создать сводную таблицу для excel-файла, предназначенного для единовременной работы в нем нескольких пользователей?
Просто в таком файле у меня сейчас опции создания сводных таблиц заблокированы, важно понять, с чем это связано.
23.02.2018 11:52:44
У меня не работает. Excel 2016
29.06.2018 14:29:59
Благодарю. Столько лет с Экселем, а про такую фишку не знал.
11.11.2018 19:20:47
Можно ли визуально настроить срезы так, чтобы пользователь мог не выделять несколько позиций среза определенным набором клавиш, а

,например, чтобы рядом с позициями среза стояли галочки (к примеру) и пользователь может либо нажимать на галку ( то есть активизировать позицию среза) либо нет
01.04.2019 12:09:54
Не знал до текущего момента, что можно просто и быстро пользоваться срезами.
Удобно, очень!
07.08.2019 16:21:57
добрый день!
Николай, пользуясь случаем выражаю вам свое уважение, скажите пожалуйста как сделать так чтобы срезы и сводные таблицы могли находиться в одном и том же диапазоне, так как во время больших отчетов, сводные и срезы прыгают туда сюда, что очень напрягает. Заранее благодарю. В ожидании Вашего скорого ответа.  
04.09.2019 00:58:16
Доброй ночи. Помогите пожалуйста решить одну проблему. Я сделал срез в таблице, которая доступна всем через office 365. Проблема заключается в том, что через некторое время этот срез опускается далеко вниз в листе, из-за чего сложно бывает вообще работать с ним. Настройку блокировки перемещения я включил, какое то время он работает, но позже начинает появляться в других местах листа.
22.05.2020 13:31:53
Добрый день, спасибо за урок. Подскажите можно ли сделать срез в виде выпадающего списка или привязать выпадающий список к срезу?
02.06.2020 11:33:10
Добрый день!
Николай, спасибо за Вашу кропотливую работу и труды, плодами которых мы пользуемся с удовольствием. У меня вопрос про срез. Есть ли возможность каким-то образом соединить, чтобы было удобно пользоваться, срез и выпадающий список с автопоиском?  Не очень удобно крутить ролик среза по тысячам строк.  
Добрый день, нужна помощь. Как увеличить шрифт в срезах ?
вопрос снят
И каким образом удалось?
17.05.2021 14:09:29
Добрый день. А есть вариант сортировки элементов среза. К примеру  чтобы дни недели были по порядку в срезе  ПН, ВТ, СР... итд

Не создавая для них  промежуточные названия  01 - Понедельник, 02 - Вторник....   и не используя заранее заготовленный справочник дней недели.

Когда дни недели выбираются из исходника "в лоб".
20.11.2021 09:47:42
Добрый день!
А как вставить срез (две кнопки), допустим "Руб" и "%" что бы при выборе итог расчитывлся в рублях или в процентах?
13.01.2022 22:09:28
Добрый вечер) Подскажите пожалуйста, а в 2007 Екселе нет возможностей вставить срез и шкалу ?
17.01.2022 18:00:04
ничо е понимаю у меня скаченный к таблице файл с примером не подходит к ролику.
нет там вообще поля Менеджер (((
29.06.2022 12:27:50
Здравствуйте! Я создала дашборд и добавила срезы ровно так, как вы пишите в статье. Все получилось!
У меня создана еще одна сводная таблица, но при создании проставлена галка в поле "Добавить модель данных", чтобы в «Параметры поля значений» можно было выбрать «Число различных элементов» в качестве операции. И считать разные значения. Как к такой сводной подключить срезы, которые уже подключены к простым сводным, без этой галки?
Или какими способами можно считать разные значения в обычных сводных?
09.09.2023 15:18:31
Добрый день, подскажите, пожалуйста, пример для скачивания и видео не совпадают, это я что то не так делаю? или пример не тот?
спасибо
25.10.2023 18:46:58
Здравствуйте. Помогите пожалуйста решить проблему: по какой причине неактивны срез и временная шкала? Все даты приведены к единому виду и в "Формат ячейки" установлен пункт "Дата"
18.11.2024 15:02:47
Добрый вечер! а подскажите как поправить формат в разрезе? Одни цифры выгрузились с разделителями, а часть слиплись.
Не смогла добавить картинку ;(
Наверх