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