数据透视表传统上是使用 OLAP 多维数据集和其他复杂数据源构造的,这些数据源在表之间已具有丰富的连接。 但是,在 Excel 中,可以自由导入多个表并在表之间建立自己的连接。 虽然这种灵活性非常强大,但也可以轻松地将不相关的数据汇集在一起,从而产生奇怪的结果。
你是否曾经创建过这样的数据透视表? 您打算按区域创建购买明细,因此将购买金额字段拖放到 “值” 区域,并将销售区域字段删除到 “列标签 ”区域。 但结果是错误的。
如何解决此问题?
问题在于,已添加到数据透视表的字段可能位于同一工作簿中,但包含每列的表不相关。 例如,可能有一个表列出每个销售区域,另一个表列出所有区域的购买。 若要创建数据透视表并获取正确的结果,需要在两个表之间创建关系。
创建关系后,数据透视表会将购买表中的数据与区域列表正确合并,结果如下所示:
Excel 包含由 Microsoft Research (MSR) 开发的技术,用于自动检测和修复此类关系问题。
使用自动检测
自动检测将检查添加到包含数据透视表的工作簿的新字段。 如果新字段与数据透视表的列标题和行标题无关,则数据透视表顶部的通知区域中会显示一条消息,告知可能需要建立关系。 Excel 还将分析新数据以查找潜在关系。
可以继续忽略消息并使用数据透视表;但是,如果单击“ 创建”,算法将正常工作并分析数据。 根据新数据中的值、数据透视表的大小和复杂性以及已创建的关系,此过程最多可能需要几分钟时间。
此过程由两个阶段组成:
-
关系检测。 分析完成后,可以查看建议的关系列表。 如果不取消,Excel 将自动继续下一步创建关系。
-
创建关系。 应用关系后,将显示一个确认对话框,你可以单击 “详细信息 ”链接以查看已创建的关系列表。
可以取消检测过程,但不能取消创建过程。
MSR 算法搜索“最佳可能”的关系集,以连接模型中的表。 该算法会检测新数据的所有可能关系,同时考虑列名、列的数据类型、列中的值以及数据透视表中的列。
然后,Excel 选择具有最高“质量”分数的关系,由内部试探法确定。 有关详细信息,请参阅 关系概述和 关系故障排除。
如果自动检测未提供正确的结果,可以编辑、删除关系或手动创建新关系。 有关详细信息,请参阅 在两个表之间创建关系 或在 关系图视图中创建关系
数据透视表中的空白行 (未知成员)
由于数据透视表汇集了相关数据表,因此,如果任何表包含的数据不能由键或匹配值关联,则必须以某种方式处理该数据。 在多维数据库中,处理不匹配数据的方法是将没有匹配值的所有行分配给 Unknown 成员。 在数据透视表中,未知成员显示为空白标题。
例如,如果创建了一个数据透视表,该数据透视表应按商店对销售额进行分组,但销售表中的某些记录未列出商店名称,则所有没有有效商店名称的记录都会分组在一起。
如果最终返回空行,则有两种选择。 可以通过在多个表之间创建关系链来定义有效的表关系,也可以从数据透视表中删除导致出现空白行的字段。