Добавить slicer в excel
Слайсеры — очень полезная функция в Excel который используется для использования нескольких автоматических фильтров в таблице данных, если пользователю нужно использовать фильтр для каждого столбца, чтобы найти дату, тогда это требует много щелчков, а вставка среза упрощает для пользователя, поскольку это может быть сделано с помощью Несколько щелчков мышью, слайсеры доступны на вкладке вставки в параметрах фильтров.
Слайсер также можно вставить в таблицы Excel. Теперь посмотрите на приведенный ниже нормальный диапазон данных. Вы не увидите никаких опций среза для этого обычного диапазона данных.
Вам необходимо преобразовать обычный диапазон данных в таблицы Excel, чтобы использовать возможности слайсеров в Excel.
- Шаг 1: Выделите все данные и нажмите Ctrl + T чтобы добавить таблицу в диапазон данных.
- Шаг 2: Нажмите OK, и вы создадите таблицу.
- Шаг 3: Как только таблица будет создана, вы увидите новую вкладку на ленте под названием Дизайн. На этой вкладке вы можете увидеть параметр «Срезы» (только начиная с Excel 2013 г.).
- Шаг 4: Выберите опцию Insert Slicer. Он покажет вам все доступные заголовки в таблице.
- Шаг 5: Выберите нужный столбец, по которому вы хотите часто фильтровать данные.
Заметка: Вы также можете выбрать все заголовки. Но я выбрал только заголовок столбца COUNTRY.
Это слайсер, который я вставил для заголовка Country. Он перечислит все уникальные значения из списка. Если вы нажмете на конкретную страну, таблица данных отобразит только данные выбранной страны.
Теперь я выбрал название страны Канада и таблицу данных, показывающую только данные страны Канады.
Я уверен, что вы улыбаетесь прекрасному варианту слайсеров и его интересным функциям. Не удивляйтесь крутой особенности Slicer, потому что я могу многое раскрыть. Примечание. Загрузите книгу, чтобы подписаться на меня.
У меня есть большая таблица данных, которая включает в себя до 10 заголовков и имеет более 700 строк данных.
Я хочу получить сводку этих больших данных с точки зрения его валовой продажи. Я применил сводную таблицу, чтобы суммировать эти большие данные.
Я хочу видеть сводный отчет по странам, продуктам, годам и сегментам.
Сводная таблица, показывающая мне сводный отчет, потому что в данных много полей, отчет пока не удобен для пользователя. Вставив слайсер, мы можем создать удобные для пользователя параметры фильтра.
- Шаг 1: Поместите курсор внутри сводной таблицы. Удалите все поля, кроме Country и YEAR.
- Шаг 2: Перейдите в Analyze> Insert Slicer.
- Шаг 3: Как только слайсер выбран, он покажет все заголовки. Выбрать Сегмент и Продукт.
- Шаг 4: Теперь вы увидите срезы для этих двух выбранных заголовков.
Теперь в отчете отображается сводка по всем сегментам и по всем продуктам. Если вы хотите отфильтровать отчет только для правительства сегмента и для продукта Carretera, вы можете выбрать параметры из срезов. Сводная таблица начала показывать отчет только для примененных фильтров. Только ПРАВИТЕЛЬСТВО и CARRETERA были выделены в слайсере.
Мы можем применить один и тот же срез для двух сводных таблиц. Теперь взгляните на изображение ниже, где у меня есть две сводные таблицы.
Первая сводная таблица показывает отчет по сегментам, а вторая сводная таблица показывает отчет по странам. Мне нужно анализировать по месяцам для обеих сводных таблиц. Сейчас я вставлю слайсер для одной первой сводной таблицы.
На изображении выше я выбрал февраль в качестве фильтра и первой сводной таблицы, показывающей результат только за февраль. Однако во второй сводной таблице по-прежнему отображается общий отчет за все месяцы.
Чтобы связать слайсер с обеими сводными таблицами, щелкните правой кнопкой мыши слайсер и выберите параметр под названием Отчет о подключениях.
После того, как вы нажмете «Подключения к отчету», он покажет вам список всех сводных таблиц в одной и той же таблице данных. Выберите количество сводных таблиц, которые необходимо связать с этим слайсером. В данном случае всего две сводные таблицы.
Теперь вернитесь и выберите название месяца для отображения отчета.
Теперь обе сводные таблицы показывают отчет только за февраль.
Одна из распространенных проблем в SLICER — проблема с интервалами. Теперь посмотрите на приведенную ниже таблицу срезов для MONTHS.
Этот слайсер показывает только первые шесть месяцев на текущем экране. Если я хочу выбрать месяцы развертывания, мне нужно прокрутить вниз и выбрать, и это отвлечет время от моей производительности. Но я могу немного изменить выравнивание, чтобы оно отображалось так, как мне удобно.
- Шаг 1: Выберите слайсер и перейдите в Параметры.
- Шаг 2: В этом варианте перейдите к столбцам и установите значение 2.
Теперь он покажет результат в двух столбцах вместо одного столбца. Это будет намного проще, чем первое, выбрать месяцы.
Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed.
You can use a slicer to filter data in a table or PivotTable with ease.
Create a slicer to filter data
Click anywhere in the table or PivotTable.
On the Home tab, go to Insert > Slicer.
In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select OK.
A slicer will be created for every field that you selected. Clicking any of the slicer buttons will automatically apply that filter to the linked table or PivotTable.
To select more than one item, hold Ctrl, and then select the items that you want to show.
You can adjust your slicer preferences in the Slicer tab (in newer versions of Excel), or the Design tab (Excel 2016 and older versions) on the ribbon.
Note: Select and hold the corner of a slicer to adjust and resize it.
If you want to connect a slicer to more than one PivotTable, go to Slicer > Report Connections > check the PivotTables to include, then select OK.
Note: Slicers can only be connected to PivotTables that share the same data source.
Slicer components
A slicer typically displays the following components:
1. A slicer header indicates the category of the items in the slicer.
2. A filtering button that is not selected indicates that the item is not included in the filter.
3. A filtering button that is selected indicates that the item is included in the filter.
4. A Clear Filter button removes the filter by selecting all items in the slicer.
5. A scroll bar enables scrolling when there are more items than are currently visible in the slicer.
6. Border moving and resizing controls allow you to change the size and location of the slicer.
Click anywhere in the PivotTable for which you want to create a slicer.
This displays the PivotTable Analyze tab.
On the PivotTable Analyze tab, click Insert Slicer.
In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.
A slicer is displayed for every field that you selected.
In each slicer, click the items on which you want to filter.
To select more than one item, hold down Command, and then click the items on which you want to filter.
Click anywhere in a table for which you want to create a slicer.
This displays the Table tab.
On the Table tab, click Insert Slicer.
In the Insert Slicers dialog box, select the check box of the fields (columns) for which you want to create a slicer.
A slicer is displayed for every field (column) that you selected.
In each slicer, click the items on which you want to filter.
To select more than one item, hold down Command, and then click the items on which you want to filter.
Click the slicer that you want to format.
This displays the Slicer tab.
On the Slicer tab, click a colored style that you want.
If you have a slicer on a PivotTable already, you can use that same slicer to filter another PivotTable. Please note that this only works when the two PivotTables use the same data source.
First create a PivotTable that is based on the same data source as the PivotTable that already has the slicer you want to reuse.
Click the slicer that you want to share in another PivotTable.
This displays Slicer tab.
On the Slicer tab, click Report Connections.
In the dialog box, select the check box of the PivotTables in which you want the slicer to be available.
Click anywhere in the PivotTable for which you want to disconnect a slicer.
This displays the PivotTable Analyze tab.
Click the PivotTable Analyze tab, and then click Filter Connections.
In the dialog box, clear the check box of any PivotTable fields for which you want to disconnect a slicer.
Do one of the following:
Click the slicer, and then press DELETE.
Control-click the slicer, and then click Remove .
A slicer typically displays the following components:
1. A slicer header indicates the category of the items in the slicer.
2. A filtering button that is not selected indicates that the item is not included in the filter.
3. A filtering button that is selected indicates that the item is included in the filter.
4. A Clear Filter button removes the filter by selecting all items in the slicer.
5. A scroll bar enables scrolling when there are more items than are currently visible in the slicer.
6. Border moving and resizing controls allow you to change the size and location of the slicer.
You can use a slicer to filter data in a table or PivotTable with ease.
Create a slicer to filter data
Click anywhere in the table or PivotTable.
On the Home tab, go to Insert > Slicer.
In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select OK.
A slicer will be created for every field that you selected. Clicking any of the slicer buttons will automatically apply that filter to the linked table or PivotTable.
Do one of the following:
Click the slicer, and then press DELETE.
Control-click the slicer, and then click Remove .
A slicer typically displays the following components:
1. A slicer header indicates the category of the items in the slicer.
2. A filtering button that is not selected indicates that the item is not included in the filter.
3. A filtering button that is selected indicates that the item is included in the filter.
4. A Clear Filter button removes the filter by selecting all items in the slicer.
5. A scroll bar enables scrolling when there are more items than are currently visible in the slicer.
6. Border moving and resizing controls allow you to change the size and location of the slicer.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Срезы предоставляют кнопки для фильтрации таблиц или срезов. Помимо быстрой фильтрации срезы также указывают текущее состояние фильтрации, что позволяет легко понять, что именно отображается в данный момент.
С помощью среза можно легко фильтровать данные в таблице или в таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке Главная перейдите к вкладке Вставка> срез.
В диалоговом окне Вставка срезов выберите флажки для полей, которые вы хотите отобразить, а затем выберите ОК.
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.
Вы можете настроить параметры среза на вкладке Срез (в более новых версиях Excel) или на вкладке Конструктор (Excel 2016 и более старых версиях) на ленте.
Примечание: Щелкните и удерживайте угол среза, чтобы настроить и изменить его размер.
Если вы хотите подключить срез к несколько срезов, перейдите к срезу > Подключения к отчетам > проверьте, нужно ли включать срезы, а затем выберите ОК.
Примечание: Срезы могут быть подключены только к срезам, которые имеют один и тот же источник данных.
Компоненты среза
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Щелкните в любом месте сводной таблицы, для которой хотите создать срез.
Откроется вкладка Анализ сводной таблицы.
На вкладке Анализ сводной таблицы нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки для полей сводной таблицы, для которых нужно создать срез.
Для каждого выбранного поля будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните в любом месте таблицы, для которой хотите создать срез.
Откроется вкладка Таблица.
На вкладке Таблица нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки рядом с полями (столбцами), для которых нужно создать срез.
Для каждого выбранного поля (столбца) будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните срез, который хотите отформатировать.
Откроется вкладка Срез.
На вкладке Срез щелкните цветной стиль, который хотите выбрать.
Если для сводной таблицы уже есть срез, вы можете использовать его для фильтрации другой сводной таблицы. Обратите внимание: вы можете использовать эту возможность, только если для этих сводных таблиц используется один источник данных.
Сначала создайте сводную таблицу на основе того же источника данных, что использовался для сводной таблицы со срезом, который вы хотите повторно использовать.
Щелкните срез, который хотите использовать в другой сводной таблице.
Откроется вкладка Срез.
На вкладке Срез нажмите кнопку Подключения к отчетам.
В диалоговом окне установите флажки рядом со сводными таблицами, в которых должен быть доступен срез.
Щелкните в любом месте сводной таблицы, для которой хотите отключить срез.
Откроется вкладка Анализ сводной таблицы.
Перейдите на вкладку Анализ сводной таблицы и нажмите кнопку Подключения к фильтрам.
В диалоговом окне снимите флажки рядом со всеми полями сводной таблицы, для которых вы хотите отключить срез.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить .
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
С помощью среза можно легко фильтровать данные в таблице или в таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке Главная перейдите к вкладке Вставка> срез.
В диалоговом окне Вставка срезов выберите флажки для полей, которые вы хотите отобразить, а затем выберите ОК.
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить .
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
При создании сложных отчетов и, особенно, дашбордов в Microsoft Excel, весьма часто возникает необходимость одновременной фильтрации сразу нескольких сводных таблиц. Давайте разберёмся, как такое можно можно реализовать.
Способ 1. Общий срез для фильтрации сводных на одном источнике данных
Если сводные построены по одной исходной таблице данных, то проще всего использовать для их одновременной фильтрации срез - графический кнопочный фильтр, подключенный сразу ко всем сводным таблицам.
Чтобы его добавить, выделите любую ячейку в одной из сводных и на вкладке Анализ выберите команду Вставить срез (Analyze - Insert slicer) . В открывшемся окошке пометьте галочками те столбцы, по которым вы хотите фильтровать данные и нажмите ОК:
Созданный срез будет, по умолчанию, фильтровать только ту сводную, для которой он был создан. Однако, воспользовавшись кнопкой Подключения к отчетам (Report connections) на вкладке Срез (Slicer) мы можем легко добавить к списку фильтруемых таблиц другие сводные:
Способ 2. Общий срез для фильтрации сводных на разных источниках
Если ваши сводные были построены не по одной, а по разным исходным таблицам данных, то приведённый выше способ не сработает, т.к. в окне Подключения к отчётам отображаются только те сводные, которые были построены по одному источнику.
Однако, можно легко обойти это ограничение, если использовать Модель Данных (мы подробно разбирали её в этой статье). Если загрузить наши таблицы в Модель и связать их там, то фильтрация станет распространяться на обе таблицы одновременно.
Допустим, что в качестве исходных данных у нас есть две таблицы по продажам и транспортным расходам:
Предположим, что перед нами стоит задача по каждой из них построить свою сводную и фильтровать их затем одновременно по городам общим срезом.
1 . Превращаем наши исходные таблицы в динамические "умные" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) и даём им имена таблПродажи и таблТранспорт на вкладке Конструктор (Design) .
2 . Загружаем по очереди обе таблицы в Модель с помощью кнопки Добавить в модель данных (Add to Data Model) на вкладке Power Pivot.
Напрямую связать эти таблицы в Модели не получится, т.к. пока Power Pivot поддерживает только тип связей "один-ко-многим", т.е. требует, чтобы в одной из таблиц не было дубликатов в столбце, по которому мы связываем. У нас же в обеих таблицах в поле Город присутствуют повторения. Так что потребуется создать ещё одну промежуточную таблицу-справочник со списком уникальных названий городов из обеих таблиц. Проще всего это сделать с помощью функционала надстройки Power Query, которая встроена в Excel начиная с 2016 версии (а для Excel 2010-2013 бесплатно скачивается с сайта Microsoft).
3 . Выделив любую ячейку внутри "умной" таблицы, загружаем их по очереди в Power Query кнопкой Из таблицы / диапазона на вкладке Данные (Data - From table/range) и затем в окне Power Query выбираем на Главной команды Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to. ) и вариант импорта Только создать подключение (Only create connection) :
4 . Соединяем обе таблицы в одну командой Данные - Объединить запросы - Добавить (Data - Combine queries - Append) . Совпадающие по названиям в шапке колонки встанут друг под друга (как столбец Город), а не совпадающие будут разнесены в разные столбцы (но для нас это не важно).
5 . Удаляем все столбцы, кроме колонки Город, щёлкнув по её заголовку правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) и затем удаляем все дубликаты названий городов, щёлкнув ещё раз правой кнопкой мыши по заголовку столбца и выбрав команду Удалить дубликаты (Remove duplicates) :
6 . Созданный список-справочник выгружаем в Модель Данных через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to. ) и выбираем вариант Только создать подключение (Only create connection) и - самое главное! - включаем флажок Добавить эти данные в модель данных (Add this data to Data Model) :
7 . Теперь можем, вернувшись в окно Power Pivot (вкладка Power Pivot - кнопка Управление), переключиться в Представление диаграммы (Diagram view) и связать наши таблицы продаж и траспортных расходов через созданный промежуточный справочник по городам (перетаскиванием полей между таблицами):
8 . Теперь можно создать все требуемые сводные таблицы по созданной модели с помощью кнопки Сводная таблица (Pivot Table) на Главной (Home) вкладке в окне Power Pivot и, выделив любую ячейку в любой сводной, на вкладке Анализ добавить срез кнопкой Вставить срез (Analyze - Insert Slicer) и выбрать для среза в списке поле Город в добавленном справочнике:
Теперь, нажав на знакомую кнопку Подключения к отчетам на вкладке Срез (Slicer - Report connections) мы увидим все наши сводные, т.к. построены они теперь по связанным исходным таблицам. Останется включить недостающие флажки и нажать на ОК - и наш срез начнёт фильтровать все выбранные сводные таблицы одновременно.
Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не утонуть в цифрах. Самый простой способ для этого - поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения:
Неудобства такого способа очевидны:
- При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
- Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.
Если у вас Excel 2010, то все это можно делать более изящно - с помощью срезов (slicers) . Срезы - это удобное кнопочное графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы:
Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer) :
Теперь при выделении или снятии выделения с элементов среза (можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом) в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительный приятный нюанс в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице:
Если вы используете несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных:
Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections) , которая открывает соответствующее диалоговое окно:
Тогда выделение элементов на одном срезе будет влиять сразу на несколько таблиц и диаграмм, возможно даже, на разных листах.
Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:
. и возможность создавать свои варианты оформления:
А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:
Читайте также: