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