Как привязать срез к нескольким сводным таблицам
Перейти к содержимому

Как привязать срез к нескольким сводным таблицам

  • автор:

Использование нескольких таблиц для создания сводной таблицы

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

Сводная таблица, содержащая несколько таблиц

Список полей для нескольких таблиц

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

Создание сводной таблицы с использованием нескольких таблиц

Ниже приведены три основных шага для добавления нескольких таблиц в список полей сводной таблицы.

Шаг 1. Импорт связанных таблиц из базы данных

Импортируйте их из реляционной базы данных, например Microsoft SQL Server, Oracle или Access. Вы можете импортировать несколько таблиц одновременно:

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

Шаг 2. Добавление полей в сводную таблицу

Обратите внимание: список полей содержит несколько таблиц.

Список полей сводной таблицы

Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ. Вы можете:

  • Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».
  • Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.

Шаг 3. Создание связей при необходимости

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

Кнопка

Использование модели данных для создания новой сводной таблицы

Примечание Модели данных не поддерживаются в Excel для Mac.

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

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

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

Раскрывающийся список вставки сводной таблицы с параметром

  1. Щелкните любую ячейку на листе.
  2. Выберите Вставка и щелкните стрелку вниз элемента Сводная таблица.

Сводная таблица из внешнего источника

Выберите Из внешнего источника данных.

Таблицы в модели данных

См. также

  • Создание модели данных в Excel
  • Получение данных с помощью надстройки Power Pivot
  • Упорядочение полей сводной таблицы с помощью списка полей
  • Создание сводной таблицы для анализа данных на листе
  • Создание сводной таблицы для анализа внешних данных
  • Создание сводной таблицы, подключенной к наборам данных Power BI
  • Изменение диапазона исходных данных для сводной таблицы
  • Обновление данных в сводной таблице
  • Удаление сводной таблицы

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Работаем со срезами сводных таблиц

Опубликовано 07.04.2013 Автор Ренат Лотфуллин

Начиная с Excel 2010 сводные таблицы обзавелись новой возможностью фильтрации данных, этот инструмент называется Срезы.

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

На рисунке изображена сводная таблица со срезами, расположенными справа. Каждый срез относится к определенному полю данных. В данном случае срезы представляют Федеральный округ и Численность. Сводная таблица отображает информацию о площади территории для Северо-Западного и Южного округов с численностью от 1000 до 10000 тыс. человек.

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

два среза на сводной таблице

Добавление среза

Для того чтобы добавить срез в рабочую книгу, щелкните на любой ячейке сводной таблицы. Затем выберите Работа со сводными таблицами -> Анализ -> Фильтр -> Вставить срез.

вставить срез лента

Появиться диалоговое окно Вставить срез, со списком всех полей сводной таблицы. Выберите необходимый срез и щелкните ОК.

вставка срезов диалоговое окно

Настройка срезов

По существу, срезы – это графические объекты, находящиеся на листе. Их можно перемещать, изменять размеры и внешний вид. Когда вы выбираете срез, в Excel появляется новая вкладка Инструменты для среза. Используйте ее для изменения стиля вашего среза.

Фильтрация срезом

Количество кнопок среза зависит от количества пунктов выбранного поля. Для установки фильтра, просто щелкните на кнопке среза. Если вы хотите выбрать несколько элементов фильтра, удерживая нажатой клавишу Ctrl, выбирайте необходимые пункты.

Если вы хотите убрать фильтр с конкретного среза, нажмите на иконку Удалить фильтр, находящуюся в верхнем правом углу среза (или нажмите Alt+C, когда срез выбран).

Подключение к фильтрам

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

Для подключения среза ко второй сводной таблице, выберите любую ячейку во второй сводной таблице. Переходим по вкладкам Работа со сводными таблицами –> Анализ –> Фильтр -> Подключения к фильтрам.

подключения к фильтрам лента

В появившемся диалоговом окне Подключения фильтров, выбираем необходимый нам срез, жмем ОК.

подключения к фильтрам диалоговое окно

Теперь обе сводные таблицы подключены к одному срезу. Если вы выберете пункт в срезе, обе сводные таблицы будут отфильтрованы. В примере ниже показано, как в срезе Федеральный округ выбраны Уральский, Северо-Западный и Южный и обе сводные таблицы отображают релевантную информацию.

две сводные таблицы с одним срезом

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

  • Создание сводной таблицы из данных PowerPivot
  • Группировка в сводных таблицах
  • Новая временная шкала в Excel 2013
  • Применение нескольких фильтров в сводной таблице
  • Создание базы данных в Excel
  • Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
  • Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
  • Создание списка из сводной таблицы
  • Добавление визуализации в сводную таблицу
  • Создание сводных таблиц в Excel

Рубрика: Сводные таблицы | Метки: сводные таблицы, срезы, фильтрация | 1 комментарий | Permalink

Один комментарий

Ренат, можете пожалуйста пошагово объяснить, как вы создали поле: «Численность» и использовали его как срез с определенными диапазонами «менее 1000, от 1000 до 4999, и т.д.»?
При использовании группировки мне лишь удалось сделать подобное с определенным «шагом» (1000, 2000) и причем в срезе они сортируются не последовательно, а в разнобой.
Заранее благодарю!

Добавить комментарий Отменить ответ

Рубрики

  • Google Таблицы (3)
  • PowerPivot (8)
  • Без рубрики (1)
  • Ввод данных (13)
  • Визуализация (12)
  • Дашборды (15)
  • Диаграммы (16)
  • Макросы/VBA (4)
  • Математика (5)
  • Надстройки (7)
  • Основы (16)
  • Сводные таблицы (10)
  • Формат (3)
  • Формулы (20)

Подписаться на e-mail рассылку

Популярные статьи

  • Печать таблицы в Excel — настройка области и границ печати Excel
  • Несколько условий ЕСЛИ в Excel
  • Как рассчитать коэффициент корреляции в Excel
  • Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
  • 14 способов вставки Excel, о которых вы должны знать
  • Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ
  • Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
  • Как рассчитать регрессию в Excel
  • Создание графика в Excel с отрицательными и положительными значениями
  • Как расчитать дисперсию в Excel с помощью функции ДИСП.В

Использование срезов для фильтрации данных

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

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

Вставка среза

  1. Щелкните в любом месте таблицы или сводной таблицы.
  2. На вкладке Вставка выберите Срез.

Удалить

Примечания:

  • Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.
  • Чтобы очистить фильтры среза, выберите Очистить фильтр

Форматирование среза

  1. Параметры среза можно настроить на вкладке Срез (в более новых версиях Excel) или на вкладке Конструктор (Excel 2016 и более ранних версиях) на ленте.
  2. На вкладке Срез или Конструктор выберите нужный цветовой стиль.

Примечание: Щелкните и удерживайте угол среза, чтобы настроить и изменить его размер.

Сделать срез доступным в другой сводной таблице

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

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

  1. Сначала создайте сводную таблицу, основанную на том же источнике данных, что и сводная таблица, в которой уже есть срез, который требуется повторно использовать.
  2. Выберите срез, к которому вы хотите предоставить общий доступ в другой сводной таблице. Откроется вкладка Срез.
  3. На вкладке Срез выберите Отчет Connections.
  4. В диалоговом окне выберите поле проверка сводной таблицы, в которой должен быть доступен срез.

Отключение среза

  1. Щелкните в любом месте сводной таблицы, к которому нужно отключить срез. Откроется вкладка Анализ сводной таблицы.
  2. Перейдите на вкладку Анализ сводной таблицы и выберите Фильтр Connections.
  3. В диалоговом окне очистите поле проверка полей сводной таблицы, для которых требуется отключить срез.

Удаление среза

Выполните одно из указанных ниже действий.

  • Выберите срез и нажмите клавишу DELETE.
  • Щелкните срез правой кнопкой мыши и выберите Удалить .

Компоненты среза

Срез обычно отображает указанные ниже компоненты.

Элементы среза сводной таблицы

1. Заголовок среза указывает категорию элементов в срезе.

2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.

3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.

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

5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.

6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.

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

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

  1. Щелкните в любом месте таблицы или сводной таблицы , для которых нужно создать срез.
  2. На вкладке Вставка выберите Срез.
  3. В диалоговом окне Вставка срезов выберите поля проверка для полей, которые нужно отобразить, а затем нажмите кнопку ОК.
  4. Для каждого выбранного поля будет отображен срез. Если щелкнуть любую из кнопок среза, фильтр будет автоматически применен к связанной таблице или сводной таблице.
  • Чтобы выбрать несколько элементов, удерживайте нажатой клавишу Cmd и выберите элементы, которые нужно отобразить.
  • Чтобы очистить фильтры среза, выберите Очистить фильтр в срезе.

Форматирование среза

  1. Щелкните срез, который хотите отформатировать. Откроется вкладка Срез.
  2. На вкладке Срез щелкните цветной стиль, который хотите выбрать.

Доступ к срезу для использования в другой сводной таблице

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

  1. Сначала создайте сводную таблицу на основе того же источника данных, что использовался для сводной таблицы со срезом, который вы хотите повторно использовать.
  2. Щелкните срез, который хотите использовать в другой сводной таблице. Откроется вкладка Срез.
  3. На вкладке Срез выберите Отчет Connections.
  4. В диалоговом окне установите флажки рядом со сводными таблицами, в которых должен быть доступен срез.

Отключение среза

  1. Щелкните в любом месте сводной таблицы, для которой хотите отключить срез. Откроется вкладка Анализ сводной таблицы.
  2. Перейдите на вкладку Анализ сводной таблицы и выберите Фильтр Connections.
  3. В диалоговом окне снимите флажки рядом со всеми полями сводной таблицы, для которых вы хотите отключить срез.

Удаление среза

Выполните одно из указанных ниже действий.

  • Выберите срез и нажмите клавишу DELETE.
  • Щелкните срез, удерживая нажатой клавишу CONTROL, а затем выберите Удалить .

Компоненты среза

Срез обычно отображает указанные ниже компоненты.

Элементы среза сводной таблицы

1. Заголовок среза указывает категорию элементов в срезе.

2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.

3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.

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

5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.

6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.

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

Примечание: В Excel для Интернета доступно только создание локального среза сводной таблицы. Чтобы создать срезы для таблиц, сводных таблиц модели данных или сводных таблиц Power BI, используйте Excel для Windows или Excel для Mac.

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

  1. Щелкните в любом месте сводной таблицы.
  2. На вкладке Сводная таблица выберите Вставить срез.

Вставка среза

Вставка среза 2

  • Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.
  • Чтобы очистить фильтры среза, выберите Очистить фильтр в срезе.

Форматирование среза

Выберите срез, который нужно отформатировать. Откроется вкладка Срез.

На вкладке Срез выберите нужный цветной стиль.

Примечание: Щелкните и удерживайте угол среза, чтобы настроить и изменить его размер.

Доступ к срезу для использования в другой сводной таблице

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

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

  1. Сначала создайте сводную таблицу на основе того же источника данных, что использовался для сводной таблицы со срезом, который вы хотите повторно использовать.
  2. Выберите срез, к которому вы хотите предоставить общий доступ, в другой сводной таблице. Откроется вкладка Срез.
  3. На вкладке Срез выберите Параметры.
  4. В области в разделе Connections сводной таблицы установите флажок сводных таблиц, на которые вы хотите, чтобы срез был доступен.

Отключение среза

  1. Выберите в любом месте сводной таблицы, для которого нужно отключить срез. Откроется вкладка Сводная таблица .
  2. Перейдите на вкладку Сводная таблица , а затем выберите Параметры.
  3. В области в разделе Срез Connections снимите флажок всех полей сводной таблицы, для которых требуется отключить срез.

Удаление среза

Выполните одно из указанных ниже действий.

  • Выберите срез и нажмите клавишу DELETE.
  • Щелкните срез, удерживая клавишу CONTROL, и нажмите кнопку Удалить.

Компоненты среза

Срез обычно отображает указанные ниже компоненты.

Элементы среза сводной таблицы

1. Заголовок среза указывает категорию элементов в срезе.

2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.

3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.

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

5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.

6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech 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) : Загружаем справочник в Модель Данных Excel
7 . Теперь можем, вернувшись в окно Power Pivot (вкладка Power Pivot — кнопка Управление), переключиться в Представление диаграммы (Diagram view) и связать наши таблицы продаж и траспортных расходов через созданный промежуточный справочник по городам (перетаскиванием полей между таблицами):
Создаем связи между таблицами
8 . Теперь можно создать все требуемые сводные таблицы по созданной модели с помощью кнопки Сводная таблица (Pivot Table) на Главной (Home) вкладке в окне Power Pivot и, выделив любую ячейку в любой сводной, на вкладке Анализ добавить срез кнопкой Вставить срез (Analyze — Insert Slicer) и выбрать для среза в списке поле Город в добавленном справочнике: Добавляем срез по справочнику
Теперь, нажав на знакомую кнопку Подключения к отчетам на вкладке Срез (Slicer — Report connections) мы увидим все наши сводные, т.к. построены они теперь по связанным исходным таблицам. Останется включить недостающие флажки и нажать на ОК — и наш срез начнёт фильтровать все выбранные сводные таблицы одновременно.

Ссылки по теме

  • Преимущества сводной по Модели Данных
  • План-факт анализ в сводной таблице с Power Pivot и Power Query
  • Независимая группировка сводных таблиц

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *