Использование срезов в сводной таблице Excel - руководство для начинающих

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

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

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

Давайте начнем.

Нажмите здесь, чтобы загрузить образцы данных и следуйте дальше.

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

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

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

Прочитайте больше: Как создать сводную таблицу с нуля.

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

Спрашивается - зачем мне слайсеры?

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

Срезы - это более наглядный способ фильтрации данных сводной таблицы на основе выбора.

Вот шаги, чтобы вставить слайсер для этой сводной таблицы:

• Выберите любую ячейку в сводной таблице.
• Перейдите в Вставка -> Фильтр -> Слайсер.
• В диалоговом окне «Вставить срезы» выберите измерение, для которого вы можете фильтровать данные. В поле Slicer Box будут перечислены все доступные измерения, и вы можете выбрать одно или несколько измерений одновременно. Например, если я выберу только «Регион», будет вставлено только поле «Срез для региона», а если я выберу оба параметра «Регион» и «Тип продавца», будет вставлено два среза.
• Щелкните ОК. Это вставит слайсер (-ы) в рабочий лист.

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

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

Вы также можете выбрать сразу несколько элементов. Для этого, удерживая клавишу Control, щелкните те, которые хотите выбрать.

Если вы хотите отменить выбор, щелкните значок фильтра (с красным крестиком) в правом верхнем углу.

Вставка нескольких срезов в сводную таблицу

Вы также можете вставить несколько срезов, выбрав более одного измерения в диалоговом окне «Вставить срезы».

Чтобы вставить несколько срезов:

• Выберите любую ячейку в сводной таблице.
• Перейдите в Вставка -> Фильтр -> Слайсер.
• В диалоговом окне «Вставить срезы» выберите все размеры, для которых вы хотите получить срезы.
• Щелкните ОК.

Это вставит все выбранные слайсеры в рабочий лист.

Обратите внимание, что эти слайсеры связаны друг с другом. Например, если я выберу «Средний Запад» в фильтре «Регион» и «Многострочный» в фильтре «Тип розничного продавца», тогда будут показаны продажи для всех многострочных розничных продавцов только в регионе Среднего Запада.

Кроме того, если я выберу Mid West, обратите внимание, что опция Specialty во втором фильтре приобретает более светлый оттенок синего (как показано ниже). Это означает, что нет данных по розничным торговцам специализированной продукции в регионе Среднего Запада.

Слайсеры Vs. Фильтры отчетов

В чем разница между срезами и фильтрами отчетов?

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

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

Вот некоторые ключевые различия между срезами и фильтрами отчетов:

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

Форматирование слайсера

Когда дело доходит до форматирования, слайсер обладает большой гибкостью.

Вот то, что вы можете настроить в слайсере.

Изменение цветов слайсера

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

• Выберите слайсер.
• Перейдите в Инструменты слайсера -> Параметры -> Стили слайсера. Здесь вы найдете несколько различных вариантов. Выберите тот, который вам нравится, и ваш слайсер мгновенно получит это форматирование.

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

Получение нескольких столбцов в блоке слайсера

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

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

Сделать это:

• Выберите слайсер.
• Перейдите в Инструменты слайсера -> Параметры -> Кнопки.
• Измените значение столбцов на 2.

Это мгновенно разделит элементы в слайсере на две колонки. Однако вы можете получить что-то ужасное, как показано ниже:

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

Сделать это:

• Выберите слайсер.
• Перейдите в Инструменты слайсера -> Параметры.
• Измените высоту и ширину кнопок и слайсера. (Обратите внимание, что вы также можете изменить размер слайсера, просто выбрав его и с помощью мыши отрегулируйте края. Однако, чтобы изменить размер кнопки, вам нужно внести изменения только в параметрах).

Изменение / удаление заголовка слайсера

По умолчанию слайсер выбирает имя поля из данных. Например, если я создаю срез для регионов, заголовок автоматически будет «Регион».

Вы можете изменить заголовок или полностью удалить его.

Вот шаги:

• Щелкните правой кнопкой мыши слайсер и выберите Параметры слайсера.
• В диалоговом окне «Параметры слайсера» измените заголовок заголовка на желаемый.
• Щелкните ОК.

Это изменит заголовок в слайсере.

Если вы не хотите видеть заголовок, снимите флажок «Отображать заголовок» в диалоговом окне.

Сортировка элементов в слайсере

По умолчанию элементы в слайсере сортируются в возрастающем порядке в случае текста и от старых к более новым в случае чисел / дат.

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

Вот как это сделать:

• Щелкните правой кнопкой мыши слайсер и выберите Параметры слайсера.
• В диалоговом окне «Параметры слайсера» вы можете изменить критерии сортировки или использовать свои собственные критерии сортировки.
• Щелкните ОК.

Прочитайте больше: Как создавать собственные списки в Excel (для создания собственных критериев сортировки)

Скрытие элементов без данных из окна слайсера

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

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

В таких случаях вы можете вообще не отображать его.

Вот как это сделать:

• Щелкните правой кнопкой мыши слайсер, в котором вы хотите скрыть данные, и выберите Параметры слайсера.
• В диалоговом окне «Настройки слайсера» в параметрах «Сортировка и фильтрация элементов» установите флажок «Скрыть элементы без данных».
• Щелкните ОК.

Подключение слайсера к нескольким сводным таблицам

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

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

Прочитайте больше: Что такое кэш сводной таблицы и как его использовать?

Ниже приведен пример двух разных сводных таблиц. Обратите внимание, что слайсер в этом случае работает только для сводной таблицы слева (и не влияет на таблицу справа).

Чтобы подключить этот слайсер к обеим сводным таблицам:

• Щелкните правой кнопкой мыши слайсер и выберите «Подключения отчетов». (В качестве альтернативы вы также можете выбрать срез и перейти в Инструменты среза -> Параметры -> Слайсер -> Подключения отчетов).
• В диалоговом окне «Соединения отчетов» вы увидите все имена сводных таблиц, которые используют один и тот же сводный кеш. Выберите те, которые хотите подключить к слайсеру. В данном случае у меня только две сводные таблицы, и я подключил обе к слайсеру.
• Щелкните ОК.

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

Создание динамических сводных диаграмм с помощью срезов

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

Что-то вроде того, что показано ниже:

Вот как вы можете создать эту динамическую диаграмму:

• Выберите данные и перейдите в Вставка -> Диаграммы -> Сводная диаграмма.
• В диалоговом окне «Создание сводной диаграммы» убедитесь, что у вас правильный диапазон, и нажмите «ОК». Это вставит сводную диаграмму на новый лист.
• Сделайте выбор полей (или перетащите поля в раздел области), чтобы получить нужную сводную диаграмму. В этом примере у нас есть диаграмма, на которой показаны продажи по регионам за четыре квартала. (Прочтите здесь, как сгруппировать даты как кварталы).
• Когда у вас будет готовая сводная диаграмма, перейдите в меню «Вставка» -> «Слайсер».
• Выберите необходимое измерение среза с диаграммой. В данном случае мне нужны типы розничных продавцов, поэтому я проверяю это измерение.
• Отформатируйте диаграмму и слайсер, и все готово.

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

Щелкните здесь, чтобы загрузить образцы данных и попробовать сами.

Вам также могут понравиться следующие руководства по сводным таблицам:

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