Новые возможности сводных таблиц в Excel 2013

Рекомендуемые сводные таблицы

На вкладке Вставка (Insert) появилась кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables), предлагающая несколько подходящих сводных таблиц разного типа на выбор в зависимости от вида исходных данных:

2013-pivot-recommended.png

Для тех, кто точно знает, что хочет увидеть в своем отчете и будет делать его самостоятельно - штука бесполезная, но для начинающих пользователей - приятная помощь. Из готовых вариантов выбрать иногда проще.

Фильтрация с помощью Временной Шкалы

К возможности фильтровать сводные таблицы срезами (slicers), появившейся в Excel 2010, добавили Временную шкалу (Timeline) - интерактивную графическую ось времени, которую можно вставить на вкладке Анализ (Analysis) . Выделив любой временной интервал на оси, мы получим в привязанной к ней сводной результаты именно за выбранный период:

2013-timeline.png 

Новый механизм расчета сводных таблиц (Data Object Model)

В Excel 2013 впервые реализована возможность строить сводные таблицы не на основе классического механизма кэша, как во всех прошлых версиях до этого. Теперь при построении сводной таблицы можно установить в нижней части диалога неприметную галочку Добавить эти данные в модель данных (Add to Data Object Model) и, тем самым, использовать для построения сводной "движок" Power Pivot, т.е. по-сути полноценную базу данных, встроенную в Excel:

2013-pivot-object-model.png 

Именно объектная модель позволяет сделать один из самых впечатляющих трюков этой версии - построить сводную сразу по нескольким диапазонам данных.

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

На вкладке Данные (Data) появилась кнопка Отношения (Relationships), позволяющая связать нескольких таблиц по ключевым полям (столбцам) с помощью вот такого диалога:

2013-pivot-relations.png 

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

 2013-pivot-field-list.png

Если попытаться забросить в одну сводную поля из разных таблиц, то в списке полей появится подсказка о возможной недостающей связи, которую надо создать:

2013-pivot-need-relations.png 

Функция подсчета количества уникальных элементов

Если вы строите сводную с использованием Data Object Model, то в списке функций производимых над данными (правой кнопкой мыши по полю - Параметры поля) добавится еще одна новая - Число различных элементов (Distinct Count). Она вычислит не общее количество непустых элементов, как обычная функция Счет (Count), а количество неповторяющихся представителей.

2013-distinct-count.png

В приведенном выше примере для сравнения подсчитывается общее количество проданных товаров и количество уникальных наименований по каждому городу.

Экспресс-просмотр

Тем, кто работает со сводными таблицами, известна простая, но крайне полезная функция: если сделать двойной щелчок по любой ячейке с числовым результатом в области значений, то на отдельный лист будет выведена детализация по этой ячейке. Это позволяет оперативно проверить исходные данные и понять откуда получился такой результат.

Логическим продолжением и развитием этой идеи стала новая функция Экспресс-просмотр (Quick Explore) в сводных таблицах Excel 2013.

При выделении в сводной любой ячейки с числовым результатом рядом появляется смарт-тег Экспресс-просмотра. Нажав на него, можно выбрать нужную нам таблицу (если сводная строится по нескольким таблицам) и интересующее нас поле для детализации. Так, например, если я выделю ячейку с результатами продаж менеджера Иванова и выберу поле Заказчик для детализации:

2013-pivot-drill1.png

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

2013-pivot-drill2.png

Недостатки

Из замеченного в процессе работы:

  • В сводных таблицах построенных на основе Объектной модели не работает группировка. Это хоть и не критично, но печально. Будем надеяться, что исправят.
  • Иногда названия столбцов в списке полей не соответствуют реальности, т.е. берутся не из ячеек шапки, а непонятно откуда. Помогает предварительное форматирование исходных диапазонов как "умных" таблиц.
  • Для выгрузки исходных данных из Объектной модели приходится использовать команду Данные - Подключения (Data - Connections).

 



08.01.2013 13:33:27
Надо бы опробовать, очень уж нужные изменения в сводных таблицах добавили, наконец-то:). А вот про механизм работы Data object Model хотелось бы поподробнее (по какому принципу происходит объединениние таблиц inner join? - или только идентичные по заголовкам таблицы можно сопоставить). В общем, сегодня завтра надо организовать test drive8).
За описание спасибо - заинтересовало.
11.01.2013 18:43:00
Можно и с разными заголовками таблицы связывать - вы сами и указываете какие поля в каких таблицах друг другу соответствуют. Попробовать, в любом случае, стоит по-моему.
Евгений
09.01.2013 06:53:32
"В сводных таблицах построенных на основе Объектной модели не работает группировка. "  
Печально! А еще наверняка остался неприятный момент, когда раскрываешь одну позицию, а раскрывается сразу несколько строк, имеющих такое же название.
11.01.2013 18:10:29
Не очень понял - о чем вы. Что значит "раскрываешь" в вашем понимании?
20.11.2017 19:37:19
Добрый день.

Николай, как раз ищу способ решения такой проблемы.
К примеру. Структура в сводной выглядит след. образом:
Магазин 1
  продукты питания
     фрукты
        персики
Магазин 2
  продукты питания
     фрукты
        персики

Если раскрывать группу "продукты питания" в "Магазине 1", то все содержимое аналогичной группы в "магазине 2" и всех следующих магазинах открывается автоматически одновременно. Если группировки достаточно объемные, это мешает, особенно когда при раскрытии группы внизу сводной открываются все ранее стоящие группы и эксель перебрасывает (вернее оставляет) тебя на том же номере строки, что ты был при закрытых группах.

Как настроить сводную, чтобы повторяющиеся значения в других группах не открывались автоматом?

Вопрос очень срочный. Буду очень признательна за оперативный ответ.
Спасибо.
12.01.2013 03:45:33
Очень хотелось бы увидеть на Вашем примере  http://www.planetaexcel.ru/techniques/8/133/ пошаговое создание объектной модели и общей сводной таблицы из диапазонов разных листов, с использованием новых возможностей Excel 2013!
12.01.2013 15:41:01
Для этого примера Excel 2013 не поможет - в нем можно связывать только таблицы, где в одной нет повторений в ключевом столбце (т.е. она играет роль типа поисковой таблицы для функции ВПР). В этом же примере нужно просто скопипастить все таблицы в одну, что и делается макросом, но не на листе, а в памяти компьютера. Так что этот пример и сводные по нескольким таблицам в Excel 2013 - вещи разные.
12.01.2013 17:35:02
Если создать объектную модель-подключение из нескольких листов, в которой все данные, как динамические диапазоны будут в одной таблице, а из нее создать сводную, то не придется каждый раз запускать макрос при обновлении данных... Или я ошибаюсь?
12.01.2013 17:37:16
Вот этот пример  http://www.planetaexcel.ru/techniques/8/133/ нельзя решить встроенными средствами даже в Excel 2013 (см. мой предыдущий комментарий). Только макросами.
12.01.2013 17:46:16
Николай, я имел ввиду не первичный сбор данных с нескольких листов, это безусловно делает только Ваш макрос:) Интересно увидеть создание обновляемой сводной таблицы из нескольких подключенных диапазонов
16.01.2013 05:45:41
"В сводных таблицах построенных на основе Объектной модели не работает группировка. "

Можно побороть, прописав непосредственно в таблице ПаверПивот формулу с использованием функции SWITCH.
Извращение, конечно, но на безрыбье...
Еще одним недостатком является невозможность полноценно развернуть ячейку двойным кликом (полная детализация). Максимальное количество строк - 1000 :(
Вот когда исправят эти недостатки, можно будет переходить на ПаверПивот с обычных сводных.
20.01.2013 19:43:48
Ну, я в этой статье, вообще говоря, не про PowerPivot писал, а про обычные сводные. У них сейчас в 2013 версии стало много общего, но это, все ж таки, разные вещи.
21.01.2013 01:30:57
А сводные на основе объектной модели - это и есть ПаверПивот.
Можно зайти в надстройку и глянуть.
Я понял следующее, при построенни сводной и галковании "добавить эти данные в модель данных" получаем сводные ПаверПивотовские (т.е. так называемые сводные на основе объектной модели). Это можно проверить, заглянув в соответствующую надстройку.  А если галочку не ставить, получаем обычные сводные.
21.01.2013 12:17:10
Хотя у Вас, Николай, в принципе, об этом и написано. Правда, Вы говорите о движке ПаверПивот. А реально используется весь функционал ПаверПивота при объектном моделировании. Со всеми его достоинствами и недостатками (отсутствие группировки, детализация, ограниченная 1000 строк, пр.).
Let
01.02.2013 14:05:05
А есть предположения, когда поправят проблему группировки в сводных таблицах построенных на основе Обьектной модели? В этом году? :)
03.02.2013 13:21:20
Я не думаю, что это проблема. Я думаю, что это техническая особенность реализации объектной модели. Насчет сроков ничего сказать не могу - в Microsoft не работаю :)
01.06.2015 21:32:27
Все. В 2016 офисе группировка есть.
Но по-прежнему отсутствует возможность более глубокой детализации (т.е. ограничение в 1000 строк осталось) и фильтры нужно "снимать", потому как с фильтрами детализацию сделать невозможно.
Версия Excel - 16.0.4027.1008 (64-битная)
18.03.2013 16:58:20
Спасибо Вас за все ваши учебные материалы. Вы не представляете как упрощаете нам жизнь. Спасибо за то что грамотно и безупречно делаете свою работу!
19.08.2013 10:19:57
Огромное спасибо за Ваш труд. Вопрос: Будет работать Ваш макрос "фильтр исходных данных" в сводной по нескольким таблицам? И больше всего я надеялся, что в 2013 этот функционал внесут в стандартный инструмент. Не вышло. И Ваш макрос я так и не смог до конца прикрутить с сводной таблице 2010 офиса. :cry:
14.05.2014 12:19:14
Трудно сказать, надо пробовать. Думаю, должен.
14.05.2014 10:18:36
СКАЧАТЬ ФАЙЛ НЕВОЗМОЖНО!
14.05.2014 12:18:36
"И незачем так орать" (с) Винни-Пух :)
К этой статье нет файла примера. Да и как бы он, по-вашему, мог выглядеть?
24.07.2014 16:26:53
Проблема группировок есть и в PowerPivot, когда переходишь на него со сводных таблиц, но там она решается достаточно просто. Там нужно создавать отдельный стол с временным шкалами,  но зато он потом может использоваться для всех данных РР, где есть дата. А в плане порядка месяцев, тоже там такая проблема возникала, решается так(ну вдруг кто не знает):
1. Создается расчетный столбец функцией =MONTH('Название таблицы времени'[Колонка даты] ) она вытаскивает номер месяца.
2. Затем создается еще один расчетный столбец =FORMAT( 'Название таблицы времени'[Колонка даты];"MMMM"), это вытащит полное название месяца.
3. И наконец кнопкой "Группировка по столбцу" связывается столбец Названия месяца и Номера месяца.
4. Итог: у вас есть месяцы и группируются они по хронологическому порядку.

Тоже самое если вы решите создать кварталы, только там надо будет поиграться с функцией =IF. Для года там простая функция =YEAR.
29.12.2014 06:05:09
Николай, добрый день. Подскажите, а почему Фильтрация с помощью Временной Шкалы имеет только "визуальный эффект"? То есть итоговые результаты в сводной таблице за выбранные период на Временной шкале указываются правильно, однако если попытаться детализировать эти данные, то выводится все строки, как буд-то бы фильтр не был применен. Для детализации приходится создавать еще дополнительно "Срез по Датам", а это загромождает отчет.
Можно ли это как нибудь исправить? Чтобы выбор периода на Временной шкале влиял на результат детализации данных.
Заранее спасибо за ответ!:)
21.06.2015 15:50:39
Этот недостаток уже устранен в 2016 офисе.
Версия Excel - 16.0.4201.1005 (64-битная)
21.06.2015 23:21:46
Так и Срезы и Шкала так работают - они не фильтруют реально данные, а только скрывают. И двойной щелчок, чтобы провалиться в детализацию никак фильтры Срезами и Шкалой не учитывает, к сожалению.
03.08.2015 12:24:35
Добрый день, Николай! Подскажите пожалуйста, как решить проблему общего итога строк при формировании сводной таблицы по нескольким диапазонам с разных листов. Так как путем наименшего сопротивления не получается  т.е. через конструктор/общие итоги - формирует только общие итоги по столбцам (даже если выбрать общие итоги и по строкам, и по столбцам).  
04.05.2016 17:11:16
Спасибо за материал! Очень интересно почитать про PowerPivot, не нашла на русском толковых примеров, можно ли там создавать вычисляемые поля (формулы расчетов) с использованием данных из разных связанных таблиц?
23.12.2016 16:51:09
Построила сводную с числом разных элементов, но при добавлении строк в исходную таблицу и последующем обновлении, сводная слетает. Приходится строить заново. Можно это как-то победить? И можно ли в такой сводной делать вычисляемые столбцы и элементы? У меня почему-то не получается, строки в меню не активны.
01.09.2017 12:34:14
Добрый день. У меня установлен эксель 2013, сегодня впервые, еще до прочтения этой статьи попробовал построить сводную, на основании нескольких таблиц. К моей великой радости мне это удалось. Но мое ликование продлилось не долго. Т.к. после построения таких сводных таблиц, вычисляемое поле перестает быть активным. Может кто нибудь решил эту проблему?
31.05.2018 18:31:15
Здравствуйте, скажите, а почему при создании связи и построения сводной по этим связанным 2-ум таблицам, я не могу создать вычисляемое поле? Этот пункт меню просто не активен. Заранее спасибо.
Наверх