Как вставить срез в excel
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Работаем со срезами сводных таблиц
Начиная с Excel 2010 сводные таблицы обзавелись новой возможностью фильтрации данных, этот инструмент называется Срезы.
Срез – это интерактивный элемент управления, который упрощает фильтрацию данных в сводной таблице (или сводной диаграмме).
На рисунке изображена сводная таблица со срезами, расположенными справа. Каждый срез относится к определенному полю данных. В данном случае срезы представляют Федеральный округ и Численность. Сводная таблица отображает информацию о площади территории для Северо-Западного и Южного округов с численностью от 1000 до 10000 тыс. человек.
Вы можете использовать срезы для создания привлекательного интерфейса для пользователей, которые не понимают процесс фильтрации в сводных таблицах.
Добавление среза
Появиться диалоговое окно Вставить срез, со списком всех полей сводной таблицы. Выберите необходимый срез и щелкните ОК.
Настройка срезов
По существу, срезы – это графические объекты, находящиеся на листе. Их можно перемещать, изменять размеры и внешний вид. Когда вы выбираете срез, в Excel появляется новая вкладка Инструменты для среза. Используйте ее для изменения стиля вашего среза.
Фильтрация срезом
Количество кнопок среза зависит от количества пунктов выбранного поля. Для установки фильтра, просто щелкните на кнопке среза. Если вы хотите выбрать несколько элементов фильтра, удерживая нажатой клавишу Ctrl, выбирайте необходимые пункты.
Если вы хотите убрать фильтр с конкретного среза, нажмите на иконку Удалить фильтр, находящуюся в верхнем правом углу среза (или нажмите Alt+C, когда срез выбран).
Подключение к фильтрам
Если вы создаете несколько сводных таблиц с одним источником данных, вы можете подключить один срез ко всем ним и фильтровать данные одновременно.
В появившемся диалоговом окне Подключения фильтров, выбираем необходимый нам срез, жмем ОК.
Теперь обе сводные таблицы подключены к одному срезу. Если вы выберете пункт в срезе, обе сводные таблицы будут отфильтрованы. В примере ниже показано, как в срезе Федеральный округ выбраны Уральский, Северо-Западный и Южный и обе сводные таблицы отображают релевантную информацию.
Вам также могут быть интересны следующие статьи
Один комментарий
Ренат, можете пожалуйста пошагово объяснить, как вы создали поле: «Численность» и использовали его как срез с определенными диапазонами «менее 1000, от 1000 до 4999, и т.д.»?
При использовании группировки мне лишь удалось сделать подобное с определенным «шагом» (1000, 2000) и причем в срезе они сортируются не последовательно, а в разнобой.
Заранее благодарю!
Одновременная фильтрация нескольких сводных таблиц
При создании сложных отчетов и, особенно, дашбордов в Microsoft Excel, весьма часто возникает необходимость одновременной фильтрации сразу нескольких сводных таблиц. Давайте разберёмся, как такое можно можно реализовать.
Способ 1. Общий срез для фильтрации сводных на одном источнике данных
Созданный срез будет, по умолчанию, фильтровать только ту сводную, для которой он был создан. Однако, воспользовавшись кнопкой Подключения к отчетам (Report connections) на вкладке Срез (Slicer) мы можем легко добавить к списку фильтруемых таблиц другие сводные:
Способ 2. Общий срез для фильтрации сводных на разных источниках
Если ваши сводные были построены не по одной, а по разным исходным таблицам данных, то приведённый выше способ не сработает, т.к. в окне Подключения к отчётам отображаются только те сводные, которые были построены по одному источнику.
Однако, можно легко обойти это ограничение, если использовать Модель Данных (мы подробно разбирали её в этой статье). Если загрузить наши таблицы в Модель и связать их там, то фильтрация станет распространяться на обе таблицы одновременно.
Допустим, что в качестве исходных данных у нас есть две таблицы по продажам и транспортным расходам:
Предположим, что перед нами стоит задача по каждой из них построить свою сводную и фильтровать их затем одновременно по городам общим срезом.
Напрямую связать эти таблицы в Модели не получится, т.к. пока Power Pivot поддерживает только тип связей «один-ко-многим», т.е. требует, чтобы в одной из таблиц не было дубликатов в столбце, по которому мы связываем. У нас же в обеих таблицах в поле Город присутствуют повторения. Так что потребуется создать ещё одну промежуточную таблицу-справочник со списком уникальных названий городов из обеих таблиц. Проще всего это сделать с помощью функционала надстройки Power Query, которая встроена в Excel начиная с 2016 версии (а для Excel 2010-2013 бесплатно скачивается с сайта Microsoft).
Использование срезов для фильтрации данных
Срезы предоставляют кнопки, которые можно использовать для фильтрации таблицили срезов. Помимо быстрой фильтрации срезы также указывают текущее состояние фильтрации, что позволяет легко понять, что именно отображается в данный момент.
С помощью среза можно с легкостью фильтровать данные в таблице или таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке «Главная» перейдите к срезу >».
В диалоговом окне «Вставка срезов» выберите флажки для полей, которые вы хотите отобразить, а затем выберите «ОК».
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.
Чтобы очистить фильтры среза, выберите в 
«Очистить фильтр».
Вы можете настроить параметры среза на вкладке «Срез» (в более новых версиях Excel) или на вкладке «Конструктор» (в Excel 2016 и более старых версиях) на ленте.
Примечание: Щелкните и удерживайте угол среза, чтобы настроить и изменить его размер.
Если вы хотите подключить срез к одной или несколько срезов, перейдите в > «Подключения к отчетам> проверьте срез, который нужно включить в срез, и выберите «ОК».
Примечание: Срезы можно связывать только со срезами, которые имеют один и тот же источник данных.
Компоненты среза
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Щелкните в любом месте сводной таблицы, для которой хотите создать срез.
Откроется вкладка Анализ сводной таблицы.
На вкладке Анализ сводной таблицы нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки для полей сводной таблицы, для которых нужно создать срез.
Для каждого выбранного поля будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните в любом месте таблицы, для которой хотите создать срез.
Откроется вкладка Таблица.
На вкладке Таблица нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки рядом с полями (столбцами), для которых нужно создать срез.
Для каждого выбранного поля (столбца) будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните срез, который хотите отформатировать.
Откроется вкладка Срез.
На вкладке Срез щелкните цветной стиль, который хотите выбрать.
Если для сводной таблицы уже есть срез, вы можете использовать его для фильтрации другой сводной таблицы. Обратите внимание: вы можете использовать эту возможность, только если для этих сводных таблиц используется один источник данных.
Сначала создайте сводную таблицу на основе того же источника данных, что использовался для сводной таблицы со срезом, который вы хотите повторно использовать.
Щелкните срез, который хотите использовать в другой сводной таблице.
Откроется вкладка Срез.
На вкладке Срез нажмите кнопку Подключения к отчетам.
В диалоговом окне установите флажки рядом со сводными таблицами, в которых должен быть доступен срез.
Щелкните в любом месте сводной таблицы, для которой хотите отключить срез.
Откроется вкладка Анализ сводной таблицы.
Перейдите на вкладку Анализ сводной таблицы и нажмите кнопку Подключения к фильтрам.
В диалоговом окне снимите флажки рядом со всеми полями сводной таблицы, для которых вы хотите отключить срез.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Примечание: Excel в Интернете позволяет использовать срезы, которые были созданы в классических версиях Excel, но не поддерживают создание срезов, редактирование срезов и выбор нескольких значений в срезе.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel 2013. Срезы сводных таблиц; создание временной шкалы
В Excel 2010 появился новый инструмент анализа данных – срезы сводных таблиц. [1] С помощью срезов можно фильтровать сводные таблицы подобно тому, как это происходит с помощью области фильтра списка полей сводной таблицы. Различие заключается в том, что срезы обеспечивают дружественный интерфейс, позволяющий просматривать текущее состояние фильтра.
Чтобы создать срез поместите указатель мыши в область сводной таблицы, выберите контекстную вкладку ленты Анализ и щелкните на кнопке Вставить срез (рис. 1). Также, встав на сводную таблицу, можно перейти на вкладку Вставка и в области Фильтры выбрать команду Срез.
Рис. 1. Создание среза: шаг 1 – команда Вставить срез
Скачать заметку в формате Word или pdf, примеры в формате Excel
Появится диалоговое окно Вставка срезов (рис. 2). Устанавливаемые в этом окне флажки определяют критерии фильтрации. В рассматриваемом случае выполняется фильтрация по срезу Категория оборудования. Можно задать одновременно несколько срезов.
Рис. 2. Создание среза: шаг 2 – выбор критерия, по которому осуществляется фильтрация с помощью среза
Когда срез создан (рис. 3) можно щелкнуть на значениях фильтра для выполнения фильтрации сводной таблицы. Для выбора нескольких фильтров во время щелчка мышью удерживайте клавишу Ctrl.
Рис. 3. Настройка фильтра с помощью среза
Еще одно преимущество, связанное с использованием срезов, заключается в том, что каждый срез может быть связан с несколькими сводными таблицами. Другими словами, в случае применения фильтра к какому-либо срезу произойдет его применение к нескольким сводным таблицам. Для подключения среза к нескольким сводным таблицам щелкните на нем правой кнопкой мыши и в контекстном меню выберите пункт Подключения к отчетам. На экране появляется диалоговое окно Подключения сводной таблицы. В этом окне установите флажки, соответствующие сводным таблицам, которые будут фильтроваться с помощью данного среза (рис. 4).
Рис. 4. Подключение одного фильтра к нескольким сводным таблицам
С этого момента любой фильтр, примененный к срезу, будет также применен ко всем подключенным к нему сводным таблицам. Срезы имеют одно уникальное преимущество по сравнению с областью фильтра сводной таблицы: они позволяют контролировать состояние фильтров для нескольких сводных таблицы. Область же фильтра позволяет контролировать состояние только той сводной таблицы, в которой она находится.
Обратите внимание на то, что срезы не являются частью сводной таблицы. Они представляют собой отдельные объекты, которые могут использоваться самыми разными способами.
Заметьте, что показанный на рис. 4 список сводных таблиц выглядит довольно абстрактно (СводнаяТаблица2, СводнаяТаблица3). Эти обобщенные имена автоматически присваиваются сводным таблицам и применяются для идентификации. На самом деле довольно сложно распознать нужную вам сводную таблицу на основе подобных обобщенных названий. Поэтому лучше выбирать более осмысленные названия, облегчающие идентификацию сводных таблиц. Чтобы изменить название сводной таблицы, установите указатель мыши в произвольном ее месте, выберите контекстную вкладку Анализ и введите понятное имя в поле Имя сводной таблицы, находящемся в левой части ленты (рис. 5).
Рис. 5. Присвоение сводной таблицы «говорящего» имени
Создание временной шкалы
Временная шкала появилась в Excel 2013. Этот фильтр напоминает стандартный срез тем, что для фильтрации сводной таблицы используется механизм визуального выбора фильтра вместо прежних полей фильтра. Различие же заключается в том, что временная шкала предназначена исключительно для работы с полями даты и обеспечения визуального способа фильтрации и группирования дат в сводной таблице.
Необходимое условие создания временной шкалы — наличие поля сводной таблицы, в которой все данные имеют формат даты. Недостаточно иметь столбец данных, включающий несколько дат. Все значения, находящиеся в поле даты, следует отформатировать с помощью корректного формата даты. Если хотя бы одно значение в столбце даты пустое или не является корректной датой, Excel не сможет создать временную шкалу.
Чтобы создать временную шкалу, поместите указатель мыши в область сводной таблицы, выберите вкладку ленты Анализ и щелкните на значке Вставить временную шкалу (рис. 6). Также, встав на сводную таблицу, можно перейти на вкладку Вставка и в области Фильтры выбрать команду Временная шкала.
Рис. 6. Создание временной шкалы – шаг 1 – Вставить временную шкалу
На экране появится диалоговое окно Вставка временных шкал (рис. 7). В этом окне отображаются все поля дат, доступные в выбранной сводной таблицы. Выберите поля, для которых будут созданы временные шкалы.
Рис. 7. Создание временной шкалы – шаг 2 – выбор полей, для которых будут созданы временные шкалы
После создания временной шкалы у вас появится возможность фильтровать данные в сводной таблице с помощью этого динамического механизма выбора данных. Как показано на рис. 8, после щелчка на фильтре Апрель будут отображены данные сводной таблицы, относящиеся к апрелю.
Рис. 8. Щелкните на выбранной дате для фильтрации сводной таблицы
Вы можете выбрать связный диапазон, щелкнув на левую крайнюю точку, и нажав Shift, щелкнуть на правой крайней точке. Вы также можете потянуть за границы диапазона в одну и другой сторону.
Если вам нужно отфильтровать сводную таблицу по кварталам, щелкните на раскрывающемся списке временного периода и выберите параметр Кварталы. В случае необходимости можно также выбрать параметр Годы или Дни (рис. 9).
Рис. 9. Быстрое переключение между параметрами Годы, Кварталы, Месяцы или Дни
Временным шкалам не присуща обратная совместимость, поэтому они могут использоваться только в Excel 2013. Если открыть соответствующую рабочую книгу в Excel 2010 либо в более ранней версии Excel, временные шкалы будут отключены.
Временным шкалам не присуща обратная совместимость, поэтому они могут использоваться только в Excel 2013. Если открыть соответствующую рабочую книгу в Excel 2010 либо в более ранней версии Excel, временные шкалы будут отключены.
Макет любого среза можно изменить, кликнув на срезе и перейдя на контекстную вкладку Параметры из набора контекстных вкладок Инструменты для среза. Количество колонок с полями, отображаемыми в области среза, можно увеличить. Это особенно полезно, если элементов много. Перейдите в область Кнопки контекстной вкладки Параметры. С помощью кнопки-счетчика Столбцы увеличьте количество столбцов среза. Воспользуйтесь маркерами изменения размера для расширения либо сужения среза. Для каждого поля среза можно выбрать другой цвет с помощью коллекции Стили срезов (рис. 10).
Рис. 10. Форматирование срезов
В области среза могут отображаться до трех цветов. Темный цвет соответствует выделенным элементам. Белые квадратики часто означают, что запись, служащая источником данных для среза, отсутствует. Серые квадратики соответствуют невыделенным элементам.
Путем щелчка на кнопке Настройка среза, находящейся на контекстной вкладке Параметры, можно изменить название среза и порядок отображения его полей. Чтобы изменить стиль среза, щелкните правой кнопкой мыши на существующем стиле и в контекстном меню выберите параметр Дублировать. Теперь можете изменять шрифт, цвета и другие элементы стиля среза (подробнее о создании собственного стиля оформления см. Создание стиля сводной таблицы).
2 комментария для “Excel 2013. Срезы сводных таблиц; создание временной шкалы”
Добрый день.
Подскажите пожалуйста, представляется ли возможным реализовать следующее. Есть сводная таблица данные в которой фильтруются, например с помощью среза. Возможно ли фильтровать данные во второй сводной таблице (источник данных один) «негативно» по отношению к фильтру первой.
Пример.
Есть название продуктов — 1, 2, 3, 4, 5 … 9. Если для первой таблицы устанавливается фильтр на 1-5, то чтобы автоматически во второй таблице фильтр был для того что не отмечено, т.е. 6-9.
Переключение рисунков в отчетах с помощью срезов и сводных таблиц
Кстати, переключать не только рисунки, но и визуальные представления – таблицы и диаграммы, можно и с помощью smart-таблиц (умных таблиц). Этот прием используется в примере ниже, чтобы показать численность населения по крупным городам России и его рост с 1926 по 2018 годы.
Срезы для переключения рисунков в Excel
И в том, и другом файле для отображения рисунков используются срезы – удобные кнопочные фильтры для сводных и smart-таблиц, сводных диаграмм.
Кроме срезов, в Excel переключение изображений можно сделать еще несколькими способами. Например, с помощью элементов управления формы (вкладка Разработчик → Вставить → Элементы управления формы). Хотя эти элементы и могут добавить дополнительную информативность отчету, они «пришли» из старых версий Excel и в их работе есть свои нюансы. Один из самых досадных – они не поддерживаются в онлайн-представлении, в отличие от срезов, которые применяются широко.
Еще один вариант переключения – списки проверки (вкладка Данные → Проверка данных). У этого варианта тоже есть минусы. Так, выпадающий список в качестве элемента меню выглядит не очень профессионально, потому что возможность выбора вариантов становится заметной только когда ячейка со списком проверки активна (ее выделили с помощью мышки или клавиатуры).
Настройка отчета
Для настройки отчета в первом файле мы использовали следующие инструменты Excel:
Разберем, как настроить срез для переключения рисунков в первом файле.
= СМЕЩ ( товары!$C$1; ЕСЛИ ( СЧЁТЗ ( ‘1’!$B:$B ) > 2; 2; ПОИСКПОЗ ( ‘1’!$B$3; товары!$B:$B; 0)-1); 0; 1; 1)
Эта формула ищет нужное изображение на листе «товары». А если на срезе будет выбрано больше одного товара, то перенаправит на ячейку с подсказкой.
Скачивайте файлы, разбирайтесь, как они работают и присоединяйтесь к нам в социальных сетях!