Работа со связями в сводных таблицах
Сводные таблицы традиционно конструируются с использованием OLAP-кубов и других сложных источников данных, которые уже имеют большое количество соединений между таблицами. Однако в книге PowerPivot можно добавлять данные к существующим источникам данных или создавать свои собственные связи между таблицами. Хотя этот метод отличается большой гибкостью, он облегчает объединение несвязанных данных, что приводит к неожиданным результатам.
В этом разделе объясняется, как нужно работать со связями в контексте сводной таблицы и связывать данные, чтобы получать нужные отчеты.
Несвязанные данные в сводных таблицах. Проблема и решение
Пример создания сводной таблицы определенного вида. Необходимо создать разбивку покупок по регионам, поэтому поле объема покупок помещено в область Значения, а поле продаж в регионе помещено в область Метки столбцов. Но результат неверен.
.gif "DFarmer_bad_pivot")
Как можно это исправить?
Проблема заключается в том, что поля, добавляемые в сводную таблицу, могут быть в одной книге, но таблицы, которые содержат каждый из столбцов, не связаны. Например, есть таблица, в которой указан каждый регион продаж, и другая таблица, в которой указаны покупки для всех регионов. Чтобы создать сводную таблицу и получить правильные результаты, необходимо создать связь между двумя таблицами.
После создания связи сводная таблица правильно объединяет данные из таблицы покупок со списком регионов, и результаты выглядят следующим образом.
.gif "DFarmer_good_pivot")
PowerPivot для Excel содержит разработанную Microsoft Research технологию автоматического обнаружения и устранения такого рода проблем со связями.
Использование автоматического обнаружения
Автоматическое обнаружение проверяет новые поля, добавленные в книгу, содержащую сводную таблицу. Если новое поле не связано с заголовками столбцов и строк сводной таблицы, то в области уведомлений в верхней части сводной таблицы появляется сообщение о возможной необходимости связи. PowerPivot также анализирует новые данные, чтобы обнаружить потенциальные связи.
Можно пропустить это сообщение и продолжить работу со сводной таблицей, однако если нажать кнопку Создать, запускается алгоритм, который выполняет анализ данных. Этот процесс может занять несколько минут в зависимости от значений новых данных, размера и сложности сводной таблицы, а также от уже созданных связей.
Процесс состоит из двух этапов.
Обнаружение связей. После завершения связей можно просмотреть список рекомендуемых связей. Если не отменить работу, PowerPivot автоматически перейдет к следующему шагу создания связей.
Создание связей. После применения связей появляется диалоговое окно подтверждения. Чтобы просмотреть список созданных связей, можно щелкнуть ссылку Сведения.
Процесс обнаружения можно отменить, но процесс создания отменить нельзя.
Алгоритм MSR обнаруживает «наилучший возможный» набор связей для соединения таблиц в модели. Этот алгоритм обнаруживает все возможные связи для новых данных, учитывая имена столбцов, типы данных столбцов, значения в столбцах и столбцы, находящиеся в сводных таблицах.
Затем PowerPivot выбирает связь с высшей оценкой «качества», определяемой встроенными эвристическими процедурами. Дополнительные сведения см. в разделах Общие сведения о связях и Устранение неполадок в связях.
Изменение связей вручную
Если автоматическое обнаружение не дает правильный результат, можно изменять связи, удалять их и создавать новые связи вручную. Дополнительные сведения см. в разделе Создание связи между двумя таблицами.
.gif "Примечание") Примечание |
|---|
Обратите внимание, что если при построении сводной таблицы потребуется добавить связи или новые данные, то необходимо вначале вернуться в окно PowerPivot. После добавления связей или изменения данных можно вернуться в книгу Excel. Однако если какое-либо из изменений влияет на сводную таблицу, потребуется повторное вычисление всей книги. |
Пустые строки в сводной таблице (неизвестный элемент)
Сводная таблица объединяет связанные таблицы данных, поэтому, если какая-либо таблица содержит данные, которые не могут быть связаны по ключу или по совпадающему значению, такие данные должны быть каким-либо образом обработаны. В многомерных базах данных несвязанные данные обрабатываются путем назначения всех строк, которые имеют несовпадающие значения, неизвестному элементу. В сводной таблице неизвестный элемент отображается в виде пустого заголовка.
Например, если создается сводная таблица, в которой продажи должны группироваться по магазинам, но некоторые записи в таблице продаж не содержат названия магазина, все записи без допустимого названия магазина группируются вместе.