Сводные таблицы традиционно создаются с помощью кубов OLAP и других сложных источников данных, которые уже имеют широкие подключения между таблицами. Однако в Excel можно импортировать несколько таблиц и создавать собственные подключения между таблицами. Хотя такая гибкость является мощной, она также позволяет легко объединять данные, которые не связаны, что приводит к странным результатам.
Вы когда-нибудь создавали такую сводную таблицу? Вы намеревались создать разбивку покупок по регионам, поэтому вы удалили поле сумма покупки в область Значения , а поле "Регион продаж" — в область Метки столбцов . Но результаты неверны.
Как это исправить?
Проблема заключается в том, что поля, добавленные в сводную таблицу, могут находиться в одной книге, но таблицы, содержащие каждый столбец, не связаны. Например, у вас может быть таблица, в которую перечислены все регионы продаж, и другая таблица с перечислением покупок для всех регионов. Чтобы создать сводную таблицу и получить правильные результаты, необходимо создать связь между двумя таблицами.
После создания связи сводная таблица правильно объединяет данные из таблицы purchases со списком регионов, а результаты выглядят следующим образом:
Excel 2013 содержит технологию, разработанную Microsoft Research (MSR), для автоматического обнаружения и устранения проблем, подобных этой.
Использование автоматического обнаружения
Автоматическое обнаружение проверяет новые поля, добавляемые в книгу, содержащую сводную таблицу. Если новое поле не связано с заголовками столбцов и строк сводной таблицы, в области уведомлений в верхней части сводной таблицы появится сообщение о необходимости связи. Excel также проанализирует новые данные, чтобы найти потенциальные связи.
Вы можете продолжать игнорировать сообщение и работать со сводной таблицей; Однако если щелкнуть Create, алгоритм перейдет в работу и проанализирует данные. В зависимости от значений в новых данных, размера и сложности сводной таблицы и уже созданных связей этот процесс может занять до нескольких минут.
Процесс состоит из двух этапов:
-
Обнаружение связей. После завершения анализа можно просмотреть список предлагаемых связей. Если вы не отмените эту операцию, Excel автоматически перейдет к следующему шагу создания связей.
-
Создание связей. После применения связей появится диалоговое окно подтверждения, и вы можете щелкнуть ссылку Сведения , чтобы просмотреть список созданных связей.
Процесс обнаружения можно отменить, но нельзя отменить процесс создания.
Алгоритм MSR ищет "наилучший возможный" набор связей для подключения таблиц в модели. Алгоритм обнаруживает все возможные связи для новых данных с учетом имен столбцов, типов данных столбцов, значений в столбцах и столбцов в сводных таблицах.
Затем Excel выбирает отношение с наивысшей оценкой качества, определяемой внутренней эвристики. Дополнительные сведения см. в разделах Общие сведения о связях и Устранение неполадок с связями.
Если автоматическое обнаружение не дает правильных результатов, вы можете изменить связи, удалить их или создать новые вручную. Дополнительные сведения см. в разделе Create связи между двумя таблицами или отношениями Create в представлении диаграммы.
Пустые строки в сводных таблицах (неизвестный элемент)
Так как сводная таблица объединяет связанные таблицы данных, если какая-либо таблица содержит данные, которые не могут быть связаны ключом или соответствующим значением, эти данные должны обрабатываться каким-либо образом. В многомерных базах данных способ обработки несовпаденных данных заключается в назначении всех строк, не имеющих соответствующего значения, элементу Unknown. В сводной таблице неизвестный элемент отображается как пустой заголовок.
Например, если создать сводную таблицу, которая должна группировать продажи по магазинам, но в некоторых записях в таблице sales не указано имя магазина, все записи без допустимого имени магазина группируются.
Если в конечном итоге будут пустые строки, у вас есть два варианта. Можно либо определить работающее табличное отношение, например путем создания цепочки связей между несколькими таблицами, либо удалить поля из сводной таблицы, которые вызывают возникновение пустых строк.
