Как сделать срез в excel
Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не утонуть в цифрах. Самый простой способ для этого - поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения:
Неудобства такого способа очевидны:
- При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
- Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.
Если у вас Excel 2010, то все это можно делать более изящно - с помощью срезов (slicers) . Срезы - это удобное кнопочное графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы:
Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer) :
Теперь при выделении или снятии выделения с элементов среза (можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом) в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительный приятный нюанс в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице:
Если вы используете несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных:
Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections) , которая открывает соответствующее диалоговое окно:
Тогда выделение элементов на одном срезе будет влиять сразу на несколько таблиц и диаграмм, возможно даже, на разных листах.
Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:
. и возможность создавать свои варианты оформления:
А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:
Чтобы выделить части круговой диаграммы, не изменяя при этом данные, можно вытащить отдельный сегмент,выделить всю круговую диаграмму или увеличить или сложить целые разделы с помощью круговой диаграммы или отрезка.
Чтобы подчеркнуть отдельный фрагмент круговой диаграммы, вы можете переместить его обратно от остальной части круговой диаграммы, вы графикив следующее:
Нажмите круговую диаграмму.
Дважды нажмите нужный сегмент и перетащите его от центра диаграммы.
Полное разрезание круговой диаграммы
Самый быстрый способ вытащить все срезы из круговой диаграммы — щелкнуть круговую диаграмму и перетащить ее от центра диаграммы.
Чтобы более точно управлять расширением, выполните следующие действия:
Щелкните круговую диаграмму правой кнопкой мыши и выберите формат ряда данных.
Чтобы увеличить разделение, перетащите ползунок 2010 г. или введите число в поле в процентах.
Совет: Вы также можете поворачивать круговую диаграмму, поворачив ее.
Привлекайте внимание к небольшим срезам в круговой или линой круговой диаграмме
Круговая диаграмма и линеек круговой диаграммы упрощают просмотр небольших срезов круговой диаграммы. Эти типы диаграмм отделяют небольшие от основной круговой диаграммы и отображают их во вторичной круговой диаграмме или линеек с стопкой.
В приведенного ниже примере круговая диаграмма добавляет вторичную круговую диаграмму, чтобы отобразить три самых мелких фрагмента. Прежде чем сравнивать обычную круговую диаграмму:
с круговой диаграммой после:
Если не указать, сколько точек данных должно отображаться во вторичной круговой диаграмме или стопке, на диаграмме автоматически появятся три наименьших точки. В этом примере это 3%, 3% и 4%.
Выполните следующие действия, чтобы сделать это самостоятельно:
Щелкните диаграмму правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда.
Выберите пункт Круговая, а затем — Вторичная круговая или Вторичная линейчатая.
Чтобы изменить вид круговой диаграммы, щелкните ее часть, а затем в поле Разделить ряд на области Формат ряда данных выберите тип данных, которые будут отображаться на вторичной диаграмме.
Изменить количество точек данных вторичной диаграммы можно двумя способами.
Если ряд разделяется по позиции: в поле Значения во второй области уведите нужное количество позиций (например, 3, если вы хотите получить 3 наименьших).
Если ряд разделяется по значению или проценту: в поле Значения меньше введите другое число. В примере выше (с 3%, 3% и 4%) можно ввести 5 %.
Диаграмма слева — всегда основная, а диаграмма справа — всегда вторичная. Изменить их порядок нельзя.
Соединитеальные линии будут добавлены автоматически. Вы можете удалить их или изменить их стиль.
Срезы предоставляют кнопки, которые можно использовать для фильтрации таблицили срезов. Помимо быстрой фильтрации срезы также указывают текущее состояние фильтрации, что позволяет легко понять, что именно отображается в данный момент.
С помощью среза можно с легкостью фильтровать данные в таблице или таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке "Главная" перейдите к срезу>".
В диалоговом окне "Вставка срезов" выберите флажки для полей, которые вы хотите отобразить, а затем выберите "ОК".
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Чтобы выбрать несколько элементов, нажмите клавишу 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.
При создании сложных отчетов и, особенно, дашбордов в 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) мы увидим все наши сводные, т.к. построены они теперь по связанным исходным таблицам. Останется включить недостающие флажки и нажать на ОК - и наш срез начнёт фильтровать все выбранные сводные таблицы одновременно.
Срезы облегчают отображение данных в сводных таблицах, с использованием меню. В этом уроке, вы узнаете, как быстро добавить Срез для вашей сводной таблицы в Excel, чтобы контролировать отображение данных вашей таблице.
Как Добавлять Срезы для Сводных Таблиц в Excel
Примечание: Посмотрите этот короткий видеоурок или прочитайте текстовое описание к нему:
1. Добавьте Ваш Первый Срез к Сводной Таблице в Excel
Я работаю со сводной таблицей и хочу добавить первый срез. При выделенной сводной таблице, я нахожу вкладку Анализ, и жму на кнопку Вставить Срез. В появившемся диалоговом окне буду перечислены все столбцы с данными в ваше таблице.
Как добавить срез в Excel. Сначала выберите Вставить Срез.
2. Фильтрация Данных на Основе Вашего Выбора
Давайте отметим чекбоксы для полей, чтобы создать по ним срез. Теперь нажмите ОК. Теперь вы можете видеть отмеченные нами категории в отдельных окнах. Когда я щелкаю по одной из категорий в этом окне, до данные в моей сводной таблице фильтруются по этой категории.
Используйте Срез для Сводной Таблицы в Excel, чтобы фильтровать данные на основе вашего выбора.
3. Используйте Несколько Срезов Чтобы Сузить Фильтрацию
Итак, если я щелкну только по одному клиенту, например, то отображение моей сводной таблицы изменится соответствующим образом. Я могу дальше уточнять свой выбор щелкнув по еще одному варианту в другом окне.
Срезы для двух вариантов "наложатся" друг на друга, и фильтрация данных буде сужена. Я так же могу сделать несколько выделений удерживая кнопку Control на клавиатуре и выбирая в окне другие варианты.
Настраивайте Срезы для Сводных Таблиц в Excel для более конкретного отображения данных.
Завершение!
Заметьте, когда мы выполняем манипуляции, изменяется отображение данных в сводной таблице. Срезы для сводных таблиц - это самый простой способ сделать "нарезку" данных за несколько щелчков мышкой.
Больше Уроков по Сводным Таблицам Excel на Envato Tuts+
Загляните в наше Руководство по Работе со Сводными Таблицами в Excel, чтобы узнать больше о работе с мощным инструментом для электронных таблиц, или посмотрите один из наших уроков:
Так же, вы можете найти больше отличных уроков по Excel на Envato Tuts+, которые помогут вам работать увереннее с вашими электронными таблицами.
Читайте также: