PowerPivot 中的聚合

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

“聚合”是一种对数据进行折叠、汇总或分组的方法。在您开始使用表或其他数据源中的原始数据时,数据通常是平面的,这意味着尽管有很多细节,但未通过任何方式进行组织或分组。由于在汇总或结构上的这一不足,可能导致从数据中发现有意义的模式比较困难。数据建模的一个重要部分就是定义聚合,这些聚合为解决特定的业务问题而对模式进行简化、提取或汇总。

最常见的聚合,如使用平均计数DISTINCTCOUNT最大值最小值SUM可以在一个度量值中自动创建通过使用自动求和。其他类型的聚合,例如AVERAGEXCOUNTXCOUNTROWSSUMX返回一个表,并需要使用数据分析表达式 (DAX)创建公式。

了解 Power Pivot 中的聚合

为聚合选择组

聚合数据时,按产品、价格、区域或日期等属性对数据分组,然后定义用于组中所有数据的公式。例如,创建年的总计时,就是在创建一个聚合。如果您创建今年对去年的比例并以百分比形式显示,这就是另一种聚合方式。

如何对数据分组取决于业务问题。例如,聚合可以回答以下问题:

计数   一个月发生了多少笔交易?

平均值    本月每个销售人员的平均销售额是多少?

最小和最大值    销售量列前 5 位的是哪些地区?

若要创建回答这些问题的计算,您必须具有包含要计数或求和的数字的详细数据,并且这些数字数据必须以某种方式与您要用于组织结果的组相关。

如果数据不包含值,可以使用进行分组,例如产品类别或商店所在的地理区域的名称,您可能希望添加类别组向您的数据。生成在 Excel 中的组时,您必须手动键入或选择要使用从您的工作表中的列的组。但是,在关系系统中,例如产品类别的层次结构通常比事实不同表或表值中存储。通常类别表链接到事实数据通过某些类型的键。例如,假设您查找数据中包含产品 Id,但未产品或其类别的名称。若要将类别添加到平面的 Excel 工作表,必须复制包含类别名称列中。与Power Pivot,您可以将产品类别表导入到您的数据模型、 创建产品类别列表中,使用数字数据的表之间的关系,然后使用类别对数据进行分组。有关详细信息,请参阅创建表之间的关系

为聚合选择函数

确定并添加了要使用的分组后,必须确定要用于聚合的数学函数。聚合一词通常用作在聚合中使用的数学或统计运算(如求和、平均值、最小值或计数)的同义词。但 Power Pivot 除了允许使用在 Power Pivot 和 Excel 中提供的标准聚合外,还允许创建自定义的聚合公式。

例如,对于在前面的示例中使用的同一组值和分组,您可以创建回答以下问题的自定义聚合:

筛选的计数   一个月发生了多少笔交易(月末维护窗口期除外)?

使用某时段内平均值的比例    与去年同期相比,销售额增长百分比或下降百分比是多少?

分组的最小和最大值    对于每个产品类别或对于每种促销方式哪些地区的销售额名列前茅?

将聚合添加到公式和数据透视表

在您大致确定如何对数据进行分组才有意义以及要使用的值后,可以决定是生成数据透视表还是在表中创建计算。Power Pivot 扩展并改进了 Excel 的固有功能,以便创建求和、计数或平均值之类的聚合。您可以在 Power Pivot 的 Power Pivot 窗口中或在 Excel 数据透视表区域中创建自定义聚合。

  • 计算列中,您可以创建考虑到当前行上下文的聚合,以便从另一个表中检索相关行,然后对相关行中的这些值进行求和、计数或求平均值运算。

  • 在一种度量值,您可以创建使用在公式中定义的筛选器和筛选器规定的设计数据透视表和切片器、 列标题和行标题的所选内容的动态聚合。通过使用自动求和,或通过创建公式,可以在Power Pivot中创建使用标准聚合的度量值。您还可以创建隐式度量值在 Excel 中的数据透视表中使用标准聚合。

将分组添加到数据透视表

当您设计数据透视表时,可以将代表分组、类别或层次结构的字段拖到数据透视表的列和行部分,以便对数据进行分组。然后将包含数值的字段拖到值区域中,以便可以对它们进行计数、求平均值或求和。

如果将类别添加到数据透视表但是类别数据与事实数据无关,就可能会得到错误或奇怪的结果。通常 Power Pivot 将通过自动检测和提出关系建议来尝试解决问题。有关详细信息,请参阅在数据透视表中使用关系

还可以将字段拖到切片器中,从而选择要查看的某些数据组。切片器允许您以交互方式对数据透视表中的结果进行分组、排序和筛选。

在公式中使用分组

还可以通过创建各表之间的关系,然后创建利用这些关系来查找相关值的公式,使用分组和类别对存储在表中的数据进行聚合。

换言之,如果您想要创建按类别对值进行分组的公式,则应首先使用关系来连接包含详细数据的表和包含类别的表,然后生成公式。

有关如何创建使用查找的公式的详细信息,请参阅 PowerPivot 公式中的查找

在聚合中使用筛选器

Power Pivot中的新功能是对列和表的数据,用户界面中和在数据透视表或图表,不仅非常公式用于计算聚合中应用筛选器的能力。可以在计算列和 s 中的公式中使用筛选器。

例如,在新的 DAX 聚合函数中,不需要指定要求和或计数的值,可以将整个表作为参数指定。如果没有将任何筛选器应用到该表,则聚合函数适用于该表的指定列中的所有值。但是,在 DAX 中,您可以为表创建动态或静态筛选器,以便根据筛选条件和当前上下文针对不同的数据子集应用聚合运算。

通过组合公式中的条件和筛选器,您可以创建随公式中提供的值变化的聚合,或创建随数据透视表中选择的行标题和列标题变化的聚合。

有关详细信息,请参阅在公式中筛选数据

Excel 聚合函数和 DAX 聚合函数的比较

下表列出了 Excel 提供的一些标准聚合函数,并提供指向 Power Pivot 中这些函数的实现的链接。这些函数的 DAX 版本在行为上与 Excel 版本十分相似,只是在语法以及对某些数据类型的处理上有一些细微差别。

标准聚合函数

函数

用途

AVERAGE

返回列中所有数字的平均值(算术平均值)。

AVERAGEA

返回列中所有值的平均值(算术平均值)。处理文本和非数字值。

COUNT

对列中的数值进行计数。

COUNTA

计算列中不为空的值的数目。

MAX

返回列中的最大数值。

MAXX

返回对某个表执行计算的一组表达式中的最大值。

MIN

返回列中的最小数值。

MINX

返回对某个表执行计算的一组表达式中的最小值。

SUM

对列中的所有数字求和。

DAX 聚合函数

DAX 提供一些聚合函数,这些函数允许您指定要对其执行聚合的表。因此,代替只对列中的值求和或求平均值,这些函数可用于创建动态定义要聚合的数据的表达式。

下表列出了 DAX 中可用的聚合函数:

函数

用途

AVERAGEX

计算对表进行求值的一组表达式的平均值。

COUNTAX

计算对表进行求值的一组表达式的数目。

COUNTBLANK

计算列中空白值的数目。

COUNTX

计算表中行的总数。

COUNTROWS

计算从嵌套的表函数(例如筛选器函数)返回的行的数目。

SUMX

返回对表进行计算的一组表达式之和。

DAX 聚合函数和 Excel 聚合函数之间的差异

这些函数的名称与相应的 Excel 函数相同,但它们使用 Power Pivot 的内存内分析引擎并经过重新编写以使用表和列。不能在Excel 工作簿中使用 DAX 公式,反之亦然。它们只能用于 Power Pivot 窗口和基于 Power Pivot 数据的数据透视表中。此外,虽然这些函数具有相同的名称,但行为可能稍有不同。有关详细信息,请参阅相应的函数参考主题。

在聚合中计算列的方式与 Excel 处理聚合的方式也有所不同。下面将以一个例子来帮助阐明这个不同。

假设您希望获得 Sales 表的 Amount 列中各值的总和,因此创建以下公式:

=SUM('Sales'[Amount])

在最简单的情况下,该函数从单个未筛选列中获取值,而结果也与在 Excel 中时相同,只是对 Amount 列中的各值始终只进行相加合计。然而,在 Power Pivot中,对该公式的解释为:“获取 Sales 表中每行的 Amount 值,然后合计这些单独的值。”Power Pivot 会对执行聚合运算的每一行进行求值,并为每一行计算一个标量值,然后对这些值执行聚合运算。因此,如果筛选器已应用于某个表,或者如果基于可能已筛选的其他聚合对值进行计算,公式的结果可能会不同。有关详细信息,请参阅 DAX 公式中的上下文

DAX 时间智能函数

除了上一节所述的表聚合函数之外,DAX 还提供处理指定日期和时间的聚合函数,从而提供内置“时间智能”。这些函数使用日期范围来获取相关的值并对值进行聚合。还可以比较各个日期范围中的值。

下表列出了可以用于聚合的时间智能函数:

函数

用途

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

计算处于给定期间的日历末尾的值。

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

计算处于给定期间前的期间的日历末尾的值。

TOTALMTD

TOTALYTD

TOTALQTD

针对以期间的第一天开始到以指定日期列中的最晚日期结束的间隔,计算某个值。

时间智能函数部分(时间智能函数)中的其他函数是可用于检索要在聚合中使用的日期或自定义日期范围的函数。例如,您可以使用 DATESINPERIOD 函数来返回某一范围的日期,并且使用该日期集作为其他函数的参数,以便只计算那些日期的自定义聚合。

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×