Новые возможности сводных таблиц в Excel 2013
Рекомендуемые сводные таблицы
На вкладке Вставка (Insert) появилась кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables), предлагающая несколько подходящих сводных таблиц разного типа на выбор в зависимости от вида исходных данных:
Для тех, кто точно знает, что хочет увидеть в своем отчете и будет делать его самостоятельно - штука бесполезная, но для начинающих пользователей - приятная помощь. Из готовых вариантов выбрать иногда проще.
Фильтрация с помощью Временной Шкалы
К возможности фильтровать сводные таблицы срезами (slicers), появившейся в Excel 2010, добавили Временную шкалу (Timeline) - интерактивную графическую ось времени, которую можно вставить на вкладке Анализ (Analysis) . Выделив любой временной интервал на оси, мы получим в привязанной к ней сводной результаты именно за выбранный период:
Новый механизм расчета сводных таблиц (Data Object Model)
В Excel 2013 впервые реализована возможность строить сводные таблицы не на основе классического механизма кэша, как во всех прошлых версиях до этого. Теперь при построении сводной таблицы можно установить в нижней части диалога неприметную галочку Добавить эти данные в модель данных (Add to Data Object Model) и, тем самым, использовать для построения сводной "движок" Power Pivot, т.е. по-сути полноценную базу данных, встроенную в Excel:
Именно объектная модель позволяет сделать один из самых впечатляющих трюков этой версии - построить сводную сразу по нескольким диапазонам данных.
Связи и сводная по нескольким таблицам
На вкладке Данные (Data) появилась кнопка Отношения (Relationships), позволяющая связать нескольких таблиц по ключевым полям (столбцам) с помощью вот такого диалога:
Нюанс в том, что подчиненная (вторая) таблица должна содержать только уникальные значения элементов в ключевом поле - иначе связь создать не получится. Если сначала связать две таблицы, а потом начать строить сводную по одной из них, то в списке полей будут отображаться заголовки столбцов не только текущей, а уже обеих связанных таблиц:
Если попытаться забросить в одну сводную поля из разных таблиц, то в списке полей появится подсказка о возможной недостающей связи, которую надо создать:
Функция подсчета количества уникальных элементов
Если вы строите сводную с использованием Data Object Model, то в списке функций производимых над данными (правой кнопкой мыши по полю - Параметры поля) добавится еще одна новая - Число различных элементов (Distinct Count). Она вычислит не общее количество непустых элементов, как обычная функция Счет (Count), а количество неповторяющихся представителей.
В приведенном выше примере для сравнения подсчитывается общее количество проданных товаров и количество уникальных наименований по каждому городу.
Экспресс-просмотр
Тем, кто работает со сводными таблицами, известна простая, но крайне полезная функция: если сделать двойной щелчок по любой ячейке с числовым результатом в области значений, то на отдельный лист будет выведена детализация по этой ячейке. Это позволяет оперативно проверить исходные данные и понять откуда получился такой результат.
Логическим продолжением и развитием этой идеи стала новая функция Экспресс-просмотр (Quick Explore) в сводных таблицах Excel 2013.
При выделении в сводной любой ячейки с числовым результатом рядом появляется смарт-тег Экспресс-просмотра. Нажав на него, можно выбрать нужную нам таблицу (если сводная строится по нескольким таблицам) и интересующее нас поле для детализации. Так, например, если я выделю ячейку с результатами продаж менеджера Иванова и выберу поле Заказчик для детализации:
... то получу на выходе новую сводную таблицу с подробными результатами продаж именно Иванова по всем заказчикам:
Недостатки
Из замеченного в процессе работы:
- В сводных таблицах построенных на основе Объектной модели не работает группировка. Это хоть и не критично, но печально. Будем надеяться, что исправят.
- Иногда названия столбцов в списке полей не соответствуют реальности, т.е. берутся не из ячеек шапки, а непонятно откуда. Помогает предварительное форматирование исходных диапазонов как "умных" таблиц.
- Для выгрузки исходных данных из Объектной модели приходится использовать команду Данные - Подключения (Data - Connections).
За описание спасибо - заинтересовало.
Печально! А еще наверняка остался неприятный момент, когда раскрываешь одну позицию, а раскрывается сразу несколько строк, имеющих такое же название.
Николай, как раз ищу способ решения такой проблемы.
К примеру. Структура в сводной выглядит след. образом:
Магазин 1
продукты питания
фрукты
персики
Магазин 2
продукты питания
фрукты
персики
Если раскрывать группу "продукты питания" в "Магазине 1", то все содержимое аналогичной группы в "магазине 2" и всех следующих магазинах открывается автоматически одновременно. Если группировки достаточно объемные, это мешает, особенно когда при раскрытии группы внизу сводной открываются все ранее стоящие группы и эксель перебрасывает (вернее оставляет) тебя на том же номере строки, что ты был при закрытых группах.
Как настроить сводную, чтобы повторяющиеся значения в других группах не открывались автоматом?
Вопрос очень срочный. Буду очень признательна за оперативный ответ.
Спасибо.
Можно побороть, прописав непосредственно в таблице ПаверПивот формулу с использованием функции SWITCH.
Извращение, конечно, но на безрыбье...
Еще одним недостатком является невозможность полноценно развернуть ячейку двойным кликом (полная детализация). Максимальное количество строк - 1000
Вот когда исправят эти недостатки, можно будет переходить на ПаверПивот с обычных сводных.
Можно зайти в надстройку и глянуть.
Я понял следующее, при построенни сводной и галковании "добавить эти данные в модель данных" получаем сводные ПаверПивотовские (т.е. так называемые сводные на основе объектной модели). Это можно проверить, заглянув в соответствующую надстройку. А если галочку не ставить, получаем обычные сводные.
Но по-прежнему отсутствует возможность более глубокой детализации (т.е. ограничение в 1000 строк осталось) и фильтры нужно "снимать", потому как с фильтрами детализацию сделать невозможно.
Версия Excel - 16.0.4027.1008 (64-битная)
К этой статье нет файла примера. Да и как бы он, по-вашему, мог выглядеть?
1. Создается расчетный столбец функцией =MONTH('Название таблицы времени'[Колонка даты] ) она вытаскивает номер месяца.
2. Затем создается еще один расчетный столбец =FORMAT( 'Название таблицы времени'[Колонка даты];"MMMM"), это вытащит полное название месяца.
3. И наконец кнопкой "Группировка по столбцу" связывается столбец Названия месяца и Номера месяца.
4. Итог: у вас есть месяцы и группируются они по хронологическому порядку.
Тоже самое если вы решите создать кварталы, только там надо будет поиграться с функцией =IF. Для года там простая функция =YEAR.
Можно ли это как нибудь исправить? Чтобы выбор периода на Временной шкале влиял на результат детализации данных.
Заранее спасибо за ответ!
Версия Excel - 16.0.4201.1005 (64-битная)