Добавить slicer в excel
Обновлено: 29.06.2024
Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не утонуть в цифрах. Самый простой способ для этого - поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения:
Неудобства такого способа очевидны:
- При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
- Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.
Если у вас Excel 2010, то все это можно делать более изящно - с помощью срезов (slicers) . Срезы - это удобное кнопочное графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы:
Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer) :
Теперь при выделении или снятии выделения с элементов среза (можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом) в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительный приятный нюанс в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице:
Если вы используете несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных:
Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections) , которая открывает соответствующее диалоговое окно:
Тогда выделение элементов на одном срезе будет влиять сразу на несколько таблиц и диаграмм, возможно даже, на разных листах.
Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:
. и возможность создавать свои варианты оформления:
А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:
Срезы предоставляют кнопки, которые можно использовать для фильтрации таблицили срезов. Помимо быстрой фильтрации срезы также указывают текущее состояние фильтрации, что позволяет легко понять, что именно отображается в данный момент.
С помощью среза можно с легкостью фильтровать данные в таблице или таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке "Главная" перейдите к срезу>".
В диалоговом окне "Вставка срезов" выберите флажки для полей, которые вы хотите отобразить, а затем выберите "ОК".
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.
Вы можете настроить параметры среза на вкладке "Срез" (в более новых версиях Excel) или на вкладке "Конструктор" (в Excel 2016 и более старых версиях) на ленте.
Примечание: Щелкните и удерживайте угол среза, чтобы настроить и изменить его размер.
Если вы хотите подключить срез к одной или несколько срезов, перейдите в > "Подключения к отчетам> проверьте срез, который нужно включить в срез, и выберите "ОК".
Примечание: Срезы можно связывать только со срезами, которые имеют один и тот же источник данных.
Компоненты среза
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Щелкните в любом месте сводной таблицы, для которой хотите создать срез.
Откроется вкладка Анализ сводной таблицы.
На вкладке Анализ сводной таблицы нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки для полей сводной таблицы, для которых нужно создать срез.
Для каждого выбранного поля будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните в любом месте таблицы, для которой хотите создать срез.
Откроется вкладка Таблица.
На вкладке Таблица нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки рядом с полями (столбцами), для которых нужно создать срез.
Для каждого выбранного поля (столбца) будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните срез, который хотите отформатировать.
Откроется вкладка Срез.
На вкладке Срез щелкните цветной стиль, который хотите выбрать.
Доступ к срезу для использования в другой сводной таблицеЕсли для сводной таблицы уже есть срез, вы можете использовать его для фильтрации другой сводной таблицы. Обратите внимание: вы можете использовать эту возможность, только если для этих сводных таблиц используется один источник данных.
Сначала создайте сводную таблицу на основе того же источника данных, что использовался для сводной таблицы со срезом, который вы хотите повторно использовать.
Щелкните срез, который хотите использовать в другой сводной таблице.
Откроется вкладка Срез.
На вкладке Срез нажмите кнопку Подключения к отчетам.
В диалоговом окне установите флажки рядом со сводными таблицами, в которых должен быть доступен срез.
Щелкните в любом месте сводной таблицы, для которой хотите отключить срез.
Откроется вкладка Анализ сводной таблицы.
Перейдите на вкладку Анализ сводной таблицы и нажмите кнопку Подключения к фильтрам.
В диалоговом окне снимите флажки рядом со всеми полями сводной таблицы, для которых вы хотите отключить срез.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить <имя среза>.
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Примечание: Excel в Интернете позволяет использовать срезы, которые были созданы в классических версиях Excel, но не поддерживают создание срезов, редактирование срезов и выбор нескольких значений в срезе.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить <имя среза>.
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Как вы все прекрасно знаете, в Excel 2010 появилась такая штука, как срез или slicer . Срез представляет из себя, по сути, кнопочный фильтр. Выглядит это вот так:
То есть - все уникальные значения некоего столбца данных выводятся в срезе в виде графических кнопок. Если нажать на кнопку, то источник данных, с котороым связан срез отфильтрует соответствующий столбец согласно выбранному значению. Это гораздо нагляднее и быстрее, чем обычный фильтр, однако занимает больше места на экране. А, учитывая такую популярную вещь, как дашборд (dashboard), то срезы и вовсе незаменимы.
Так вот, в Excel 2010 можно было ассоциировать со сводными таблицами и со сводными диаграммами. В Excel 2013 стало возможно их применять и с умными таблицами. В этой статье я расскажу, как можно задействовать срезы для работы с обычными таблицами. Вы спросите зачем? Я вам отвечу, что на практике часто бывает так, что сводная таблица не может быть тем объектом, который вы показываете пользователю в качестве результата, так как, например, то, что вы в итоге должны показать, опирается на 2 и более сводных таблицы, поэтому результаты приходится собирать из кусков данных промежуточных сводных таблиц. Тут не может быть ничего гибче обычной таблицы, которую вы можете контролировать на 100%.
С другой стороны, меня крайне расстраивает, что, как и в случае с умными таблицами, срезы совершенно не дружат с защитой листа. Невозможно, например, сделать так, чтобы и срез работал, и было невозможно модифицировать его настройки или вообще удалить с экрана. При защите листа приходится выставлять галочку " Изменение объектов ". Только с этой настройкой срез работает, как надо, но эта же настройка и означает, что пользователь властен делать с ним всё, что захочет.
Файл примера
Исходные данные
Исходные данные, располагаются на листе Data в умной таблице tblSales .
Классика
Для начала покажем, как срезы используются для свобной таблицы. Смотрим лист примера Метод 1 - сводная таблица . Тут сделано следующее:
Добавлена сводная таблица на основе tblSales . Она элементарная - я хочу видеть продажи в разрезе регионов и модельного ряда.
На лист добавлены 2 среза, при помощи которых я собираюсь красиво, наглядно и удобно выбирать интересующий меня период времени - год(ы) и месяц(ы).
Убрана галка в настройках сводной таблицы " Автоматически изменять ширину столбцов при обновлении " дабы таблица выглядела стабильно, а не меняла ширину столбцов при использовании срезов.
По мелочи: сводная таблица и срезы получили стили форматирования, которые более-менее органично смотрятся друг с другом, срезы были настроены для расположения над таблицей, в частности, количество столбцов у них сейчас 6, а не 1 (так по умолчанию).
В результате я, не написав ни единой формулы и уж тем более ни строчки VBA кода, получил добротный пользовательский отчёт, который выглядит достаточно эстетично и его не стыдно показать боссу или клиенту.
Я могу выбирать не обязательно один какой-то год / месяц - можно выбрать набор лет / месяцев и сводная таблица без проблем отобразит правильные результаты. Единственный недостаток - это то, что довольно затруднительно получить текущие параметры фильтрации, чтобы, к примеру, автоматически формировать заголовок нашей таблицы. Параметры фильтрации можно получить при помощи VBA, но в данной статье я не буду об этом говорить.
Срез в обычной таблице
Теперь давайте обсудим, как нам приспособить такой полезный инструмент, как срез, к обычной таблице Excel. Смотрим на лист Метод 2 - обычная таблица . Как видите, я полностью воспроизвёл дизайн сводной таблицы, однако сердцевина таблицы состоит из формул СУММЕСЛИМН , которые через один полезный приём, о котором я собираюсь вам рассказать, получают параметры фильтрации из срезов.
Это устроено следующим образом:
На листе Ref я создал 2 вспомогательных умных таблицы tblYears и tblMonths . Они ни с чем не связаны - просто значения, которые мы хотим видеть в наших срезах для обычной таблицы.
На том же листе созданы 2 крошечные сводные таблицы ptYears и ptMonths на основе соответствующих вышеперечисленных вспомогательных таблиц.
Особенностью этих сводных таблиц является то, что они состоят только из фильтра.
Для каждой сводной таблицы был добавлен срез, который оказывается связанным с фильтром по полям Годы таблицы ptYears и Месяцы таблицы ptMonths . Выбор какого-либо года в срезе автоматически приводит к фильтрации по таблице ptYears .
Если в срезе по годам не выбрано ничего, то ячейка Ref ! F1 будет содержать значение "(Все)", если выбран конкретный год, то - значение этого года, если - несколько лет, то - значение "(Несколько элементов)". Вот этим мы и пользуемся. В Ref ! F3 я размещаю формулу, которая принимает значение 0, если в срезе не выбрано ничего, значение года, если выбран год, и значение -1 в остальных случаях (то есть случай выбора нескольких лет). Для удобства на базе F3 создаю именованный диапазон SelectedYear . Та же история и со вторым срезом.
Теперь у нас в SelectedYear и SelectedMonth содержатся либо год/месяц, либо 0 (ничего не выбрано), либо -1 (безобразие со множественным выбором). Эти именованные диапазоны мы используем в нашей таблицы, где при помощи формул ЕСЛИ (IF), И (AND) и, конечно же, СУММЕСЛИМН (SUMIFS) выдираем данные из tblSales . Формула выглядит страшновато, но на самом деле она шаблонна и просто уточняет состояние вышеуказанных ИД, чтобы применить формулу суммирования с нужными параметрами. Например, если SelectedYear = 0, а SelectedMonth >0, то никакой год не выбран, но выбран какой-то месяц, поэтому надо из формулы СУММЕСЛИМН убрать критерий для года, но оставить критерий для месяца. Вот и всё - остальное по аналогии.
Похожим образом формируем заголовок таблицы, который на этот раз отражает состояние фильтров, что довольно ценно. В примере со сводной таблицей мы этого добиться без VBA не смогли.
Итак, мы смогли довольно просто использовать срезы для работы с обычной таблицей, что достаточно ценно для создателей различных отчётов и дашбордов. До встречи!
Будучи пользователем Excel, вы могли использовать фильтры для своих таблиц данных, не так ли? Что, если я скажу вам, что есть некоторые фильтры, которые визуально привлекательны, могут быть добавлены к вашим таблицам, сводным диаграммам и сводным диаграммам? Кроме того, есть ли необходимость добавлять такие под ваши таблицы, сводные таблицы или сводные диаграммы? Ответ большой ДА! Чтобы сделать ваши таблицы, сводные диаграммы и сводные диаграммы более динамичными по своей природе, вы всегда можете добавить такие визуально привлекательные фильтры, которые называются срезами в Microsoft Excel. Точно работают как обычные фильтры (это значит фильтровать данные, связанные с конкретными критериями). Однако преимущество этих фильтров перед обычным состоит в том, что вы можете видеть, какие фильтры вы выбрали.
Что такое слайсер?
Срезы - это не что иное, как кнопки визуального фильтра, которые можно добавлять в таблицы Excel, сводные таблицы и сводные диаграммы Excel. Преимущество этих слайсеров состоит в том, что они также дают вам представление о том, какие фильтры применяются к данным, что позволяет пользователю более точно понимать данные. В этой статье мы рассмотрим слайсеры, как мы можем добавить их в Table, PivotTable и PivotChart.
Как вставить слайсер в Excel?
Давайте разберемся, как вставить слайсер в Excel с некоторыми примерами.
Пример № 1 - Как вставить слайсер под таблицами в Excel
Предположим, у нас есть данные о продажах за последние два года, как показано ниже:
Посмотрим, как можно добавить слайсеры для этих данных. Следуйте инструкциям ниже:
Шаг 1. Нажмите вкладку « Вставка »> выберите « Таблица» в меню параметров « Таблицы» .
- Выберите все данные от A1 до E93 и вставьте таблицу для этих диапазонов. Появится новое окно под названием «Создать таблицу» со всеми диапазонами, которые мы выбрали для вставки таблицы. Нажмите на кнопку ОК .
Ваша таблица должна выглядеть так, как показано на скриншоте ниже.
Как только вы вставите таблицу, вы увидите новый инструмент, добавленный в правой части верхней панели ленты под параметром «Инструменты таблицы» под названием « Дизайн таблицы » .
Шаг 4: Вы можете увидеть срез, добавленный под вашей таблицей Excel со всеми ярлыками стран.
Шаг 5: Вы можете использовать каждую кнопку страны для фильтрации данных. Например, если я хочу увидеть данные, связанные с Индией как страной, все, что мне нужно сделать, это просто нажать на кнопку среза Индии и увидеть волшебство. Он будет применять фильтр к таблице для всех строк с Индией как страной. Смотрите скриншот ниже.
Вот как мы можем применить слайсер к таблице Excel.
Пример №2 - Как вставить слайсеры для данных сводной таблицы
Предположим, у нас есть данные о продажах с учетом потребностей клиентов за 2018 год, как показано на скриншоте ниже. Эти данные накапливают 100 строк. Смотрите частичный скриншот ниже для вашей справки.
Мы хотели бы сначала нарезать и нарезать эти данные с помощью Excel PivotTable.
Шаг 1: Нажмите на вкладку « Вставка », расположенную на верхней ленте активного листа Excel. В разделе « Таблицы» вы найдете опцию « Сводная таблица ». Нажмите на него, чтобы вставить сводку на текущий рабочий лист.
Шаг 2. Как только вы нажмете кнопку « Сводная таблица» в разделе « Таблицы », появится новое окно с именем « Создать сводную таблицу». Выберите все данные в виде таблицы / диапазона, а затем выберите место, куда вы хотите добавить сводную точку. Нажмите ОК, когда закончите. Пожалуйста, смотрите скриншот ниже.
Шаг 3: Как только вы нажмете ОК, сводная таблица будет успешно добавлена в выбранные диапазоны данных. Теперь вы можете выбрать столбцы, которые вы хотели видеть в сводном макете. Я выберу следующий вариант макета.
- Ряды: Клиент, Страна
- Значения: количество, продажи, маржа
Пожалуйста, смотрите скриншот, приведенный ниже.
Теперь нам нужно добавить срезы для этой сводной таблицы. Посмотрим, как мы можем это сделать.
Шаг 4: Как только вы создадите сводную таблицу, вы увидите две новые вкладки, активные на ленте Excel, а именно: анализ и дизайн . Нажмите на вкладку « Анализ » из этих.
Шаг 6: Как только мы нажмем кнопку « Вставить слайсеры», появится новое окно, в котором будут представлены все имена столбцов, на основе которых вы можете вставить слайсеры в свой свод. Отметьте тот, который вы хотите добавить в качестве слайсера. Я хотел бы добавить столбец Customer под слайсеры и нажать кнопку ОК . Смотрите скриншот ниже.
Вы можете увидеть срез клиентов, добавляемых в ваш свод. Смотрите скриншот ниже.
Я могу выбрать конкретного клиента из списка или нескольких клиентов одновременно (удерживая нажатой клавишу CTRL и нажимая на клиентов по одному), применить фильтры к сводной таблице.
Вот как мы можем вставить слайсеры в сводную таблицу. Это конец этой статьи. Давайте завернем вещи с некоторыми пунктами, которые будут помнить.
Что нужно помнить о слайсере в Excel
- Срезы - это не что иное, как динамические фильтры, которые можно применять к таблицам, сводным диаграммам или сводным диаграммам.
- Вы можете выбрать более одного элемента под слайсеры. Удерживайте кнопку CTRL и нажимайте один за другим слайсеры, с которыми вы хотите нарезать данные.
Рекомендуемые статьи
Это руководство по Slicer в Excel. Здесь мы обсуждаем, как вставить слайсер в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: