2017年最全的excel函数大全11—多维数据集函数

上次给大家分享了《2017年最全的excel函数大全10—数据库函数》，这次分享给大家多维数据集函数。

CUBEKPIMEMBER 函数

描述

返回重要性能指示器 (KPI) 属性，并在单元格中显示 KPI 名称。 KPI 是一种用于监控单位绩效的可计量度量值，如每月总利润或季度员工调整。

注意:只有在工作簿连接到 Microsoft SQL Server 2005 Analysis Services 或更高版本的数据源时才支持 CUBEKPIMEMBER 函数。

用法

CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])

CUBEKPIMEMBER 函数用法具有以下参数：

• Connection必需。 一个表示多维数据集的连接名称的文本字符串。

• Kpi_name必需。 一个表示多维数据集的 KPI 名称的文本字符串。

• Kpi_property必需。 返回的 KPI 组件，可以是以下值之一：

• 如果您为 kpi_property 指定 KPIValue，则只在单元格中显示 kpi_name。

• Caption可选。 是显示在单元格中的可选文本字符串，而不是 kpi_name 和 kpi_property。

其他

• 当 CUBEKPIMEMBER 函数求值时，它会在检索到所有数据之前在单元格中暂时显示“#GETTING_DATA…”消息。

• 要在计算中使用 KPI，请将 CUBEKPIMEMBER 函数指定为 CUBEVALUE 函数中的 member_expression 参数。

• 如果连接名称不是存储在工作簿中的有效工作簿连接，则 CUBEKPIMEMBER 函数返回 #NAME? 错误值。 如果联机分析处理 (OLAP) 服务器未运行、不可用或返回错误消息，则 CUBEKPIMEMBER 函数返回 #NAME? 错误值。

• 当 kpi_name 或 kpi_property 无效时，CUBEKPIMEMBER 返回错误值 #N/A。

• CUBEKPIMEMBER 在以下情况下可能返回错误值 #N/A：如果您在共享连接时引用数据透视表中的基于会话的对象，如计算成员或命名集，而该数据透视表已被删除或者您已将该数据透视表转换为公式 （在“选项”选项卡的“工具”组中，单击“OLAP 工具”，然后单击“转换为公式”）。

案例

=CUBEKPIMEMBER(Sales,MySalesKPI,1)

=CUBEKPIMEMBER(Sales,MySalesKPI, KPIGoal,Sales KPI Goal)

CUBEMEMBER 函数

描述

返回多维数据集中的成员或元组。 用于验证多维数据集内是否存在成员或元组。

用法

CUBEMEMBER(connection, member_expression, [caption])

CUBEMEMBER 函数用法具有以下参数：

• Connection必需。 一个表示多维数据集的连接名称的文本字符串。

• Member_expression必需。 多维表达式 (MDX) 的文本字符串，用来计算出多维数据集中的唯一成员。 此外，也可以将 member_expression 指定为单元格区域或数组常量的元组。

• Caption可选。 显示在多维数据集的单元格（而不是标题）中的文本字符串（如果定义了一个文本字符串）。 当返回元组时，所用的标题为元组中最后一个成员的文本字符串。

备注

• 当 CUBEMEMBER 函数求值时，它会在检索到所有数据之前在单元格中暂时显示“#GETTING_DATA…”消息。

• 如果将 CUBEMEMBER 函数用作另一个 CUBE 函数的参数，该 CUBE 函数将使用标识成员或元组的 MDX 表达式，而不是在 CUBEMEMBER 函数的单元格中显示的值。

• 如果连接名称不是存储在工作簿中的有效工作簿连接，则 CUBEMEMBER 函数返回 #NAME? 错误值。 如果联机分析处理 (OLAP) 服务器未运行、不可用或返回错误消息，则 CUBEMEMBER 函数返回 #NAME? 错误值。

• 如果元组中至少有一个元素是无效的，则 CUBEMEMBER 函数返回 #VALUE! 错误值。

• 如果 member_expression 的长度大于 255 个字符（这是函数参数的长度限制），则 CUBEMEMBER 函数将返回 #VALUE! 错误值。 要使用长度大于 255 个字符的文本字符串，请在单元格中输入该文本字符串（对于单元格而言，该限制是 32,767 个字符），然后使用单元格引用作为参数。

• 当遇到下列情况时，CUBEMEMBER 函数返回错误值 #N/A：

• member_expression 用法不正确。

• MDX 文本字符串指定的成员在多维数据集中不存在。

• 由于指定的值不交叉，元组无效。 （当多个元素来自同一层次结构时会发生此情况。）

• 集合至少包含一个其维数与其他成员都不同的成员。

• CUBEMEMBER 在以下情况下可能返回错误值 #N/A：如果您在共享连接时引用数据透视表中的基于会话的对象，如计算成员或命名集，而该数据透视表已被删除或者您已将该数据透视表转换为公式 （在“选项”选项卡的“工具”组中，单击“OLAP 工具”，然后单击“转换为公式”）。

案例

=CUBEMEMBER(Sales,[Time].[Fiscal].[2004])

=CUBEMEMBER($A$1,D$12)

=CUBEMEMBER(Sales,(B4, C6, D5),SalesFor2004)

=CUBEMEMBER(xlextdat8 FoodMart 2000 Sales,([Product].[Food],[Time].[1997]))

=CUBEMEMBER($A$1,C$12:D$12)

CUBEMEMBERPROPERTY 函数

描述

Excel 中的一个多维数据集函数CUBEMEMBERPROPERTY会返回多维数据集中成员属性的值。用于验证多维数据集内是否存在某个成员名并返回此成员的指定属性。

用法

CUBEMEMBERPROPERTY(connection, member_expression, property)

CUBEMEMBERPROPERTY 函数用法具有以下参数：

• Connection必需。一个表示多维数据集的连接名称的文本字符串。

• Member_expression必需。一个文本字符串，表示多维数据集中的一个成员的多维表达式 (MDX)。

• Property必需。一个文本字符串，表示返回的属性的名称或对包含该属性的名称的单元格的引用。

备注

• 当 CUBEMEMBERPROPERTY 函数求值时，它会在检索到所有数据之前在单元格中暂时显示“#GETTING_DATA…”消息。

• 如果连接名称不是存储在工作簿中的有效工作簿连接，则 CUBEMEMBERPROPERTY 函数返回 #NAME? 错误值。如果联机分析处理 (OLAP) 服务器未运行、不可用或返回错误消息，则 CUBEMEMBERPROPERTY 函数返回 #NAME? 错误值。

• 如果 member_expression 用法不正确，或者 member_expression 指定的成员在多维数据集中不存在，则 CUBEMEMBERPROPERTY 函数返回错误值 #N/A。

• CUBEMEMBERPROPERTY 在以下情况下可能返回错误值 #N/A：如果你在共享连接时引用数据透视表中的基于会话的对象，如计算成员或命名集，而该数据透视表已被删除或者你已将该数据透视表转换为公式 （在“选项”选项卡的“工具”组中，单击“OLAP 工具”，然后单击“转换为公式”）。

• CUBEMEMBERPROPERTY 对在Power Pivot中编辑的 Excel数据模型无效，因为它们不是多维数据集。

案例

=CUBEMEMBERPROPERTY(Sales,[Time].[Fiscal].[2014],$A$3)

=CUBEMEMBERPROPERTY(Sales,[Store].[MyFavoriteStore],[Store].[Store Name].[Store Sqft])

CUBERANKEDMEMBER 函数

描述

返回集合中的第 n 个或排在一定名次的成员。 用来返回集合中的一个或多个元素，如业绩最好的销售人员或前 10 名的学生。

用法

CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

CUBERANKEDMEMBER 函数用法具有以下参数：

• Connection必需。 一个表示多维数据集的连接名称的文本字符串。

• Set_expression必需。 集表达式的文本字符串，例如 {[Item1].children}。 Set_expression 也可以是 CUBESET 函数，或者是对包含 CUBESET 函数的单元格的引用。

• Rank必需。 用于指定要返回的最高值的整型值。 如果排名值为 1，它将返回最高值；如果排名值为 2，它将返回第二高的值，依此类推。 要返回最高的前 5 个值，请使用 5 次 CUBERANKEDMEMBER ，每一次指定从 1 到 5 的不同排名。

• Caption可选。 显示在多维数据集的单元格（而不是标题）中的文本字符串（如果定义了一个文本字符串）。

备注

• 当 CUBERANKEDMEMBER 函数求值时，它会在检索到所有数据之前在单元格中暂时显示“#GETTING_DATA…”消息。

• 如果连接名称不是存储在工作簿中的有效工作簿连接，则 CUBERANKEDMEMBER 函数返回 #NAME? 错误值。 如果联机分析处理 (OLAP) 服务器未运行、不可用或返回错误消息，则 CUBERANKEDMEMBER 函数返回 #NAME? 错误值。

• 如果 set_expression 用法不正确，或者集合至少包含一个维数与其他成员都不同的成员，则 CUBERANKEDMEMBER 函数将返回错误值 #N/A。

案例

=CUBERANKEDMEMBER(Sales,$D$4,1,Top Month)

=CUBERANKEDMEMBER(Sales,CUBESET(Sales,Summer,[2004].[June],[2004].[July],[2004].[August]),3,Top Month)

提示:要返回最低的 n 个值，请使用 CUBESET 函数的 sort_order 和 sort_by 参数来反转集的顺序，以便在排序后的集合中最高值成为最低值。 例如，CUBERANKEDMEMBER (Sales, $D$4,1) 将返回最后一个成员，CUBERANKEDMEMBER (Sales, $D$4, 2) 返回倒数第二个成员，依此类推。

CUBESET 函数

描述

定义成员或元组的计算集。方法是向服务器上的多维数据集发送一个集合表达式，此表达式创建集合，并随后将该集合返回到 Microsoft Excel。

用法

CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

CUBESET 函数用法具有以下参数：

• Connection必需。 一个表示多维数据集的连接名称的文本字符串。

• Set_expression必需。 产生一组成员或元组的集合表达式的文本字符串。 Set_expression 也可以是对 Excel 区域的单元格引用，该区域包含一个或多个成员、元组或包含在集合中的集合。

• Caption可选。 显示在多维数据集的单元格（而不是标题）中的文本字符串（如果定义了一个文本字符串）。

• Sort_order可选。 要执行的排序类型（如果有），可以为下列类型之一：

• 默认值为 0。 对一组元组进行字母排序是以每个元组中最后一个元素为排序依据的。 有关这些不同的排序顺序的详细信息，请参阅 Microsoft Office SQL Analysis Services 帮助系统。

• Sort_by可选。 排序所依据的值的文本字符串。 例如，要获得销售量最高的城市，则 set_expression 为一组城市，sort_by 为销售量。 或者，要获得人口最多的城市，则 set_expression 为一组城市，sort_by 为人口量。 如果 sort_order 需要 sort_by，而 sort_by 被忽略，则 CUBESET 函数返回 #VALUE! 错误消息。

备注

• 当 CUBESET 函数求值时，它会在检索到所有数据之前在单元格中暂时显示“#GETTING_DATA…”消息。

• 如果连接名称不是存储在工作簿中的有效工作簿连接，则 CUBESET 函数返回 #NAME? 错误值。 如果联机分析处理 (OLAP) 服务器未运行、不可用或返回错误消息，则 CUBESET 函数返回 #NAME? 错误值。

• 如果 set_expression 用法不正确，或集合至少包含一个其维数与其他成员都不同的成员，CUBESET 函数返回错误值 #N/A。

• 如果 set_expression 的长度大于 255 个字符（这是函数参数的长度限制），则 CUBESET 函数将返回 #VALUE! 错误值。 要使用长度大于 255 个字符的文本字符串，请在单元格中输入该文本字符串（对于单元格而言，该限制是 32,767 个字符），然后使用单元格引用作为参数。

• CUBESET 在以下情况下可能返回错误值 #N/A：如果您在共享连接时引用数据透视表中的基于会话的对象，如计算成员或命名集，而该数据透视表已被删除或者您已将该数据透视表转换为公式 （在“选项”选项卡的“工具”组中，单击“OLAP 工具”，然后单击“转换为公式”）。

案例

=CUBESET(Finance,Order([Product].[Product].[Product Category].Members,[Measures].[Unit Sales],ASC),Products)

=CUBESET(Sales,[Product].[All Products].Children,Products,1,[Measures].[Sales Amount])

CUBESETCOUNT 函数

描述

返回集合中的项目数。

用法

CUBESETCOUNT(set)

CUBESETCOUNT 函数用法具有以下参数：

• Set必需。 Microsoft Office Excel 表达式的文本字符串，该表达式计算出由 CUBESET 函数定义的集合。 Set 也可以是 CUBESET 函数，或者是对包含 CUBESET 函数的单元格的引用。

备注

当 CUBESETCOUNT 函数求值时，它会在检索到所有数据之前在单元格中暂时显示“#GETTING_DATA…”消息。

案例

=CUBESETCOUNT(A3)

=CUBESETCOUNT(CUBESET(Sales,[Product].[All Products].Children,Products,1,[Measures].[Sales Amount]))

CUBEVALUE 函数

描述

从多维数据集中返回汇总值。

用法

CUBEVALUE(connection, [member_expression1], [member_expression2], …)

CUBEVALUE 函数用法具有以下参数：

• Connection必需。 一个表示多维数据集的连接名称的文本字符串。

• Member_expression可选。多维表达式 (MDX) 的文本字符串，用来计算出多维数据集内的成员或元组。另外，member_expression 可以是由 CUBESET 函数定义的集合。使用 member_expression 作为切片器来定义要返回其汇总值的多维数据集部分。如果 member_expression 中未指定度量值，则使用该多维数据集的默认度量值。

备注

• 当 CUBEVALUE 函数求值时，它会在检索到所有数据之前在单元格中暂时显示“#GETTING_DATA…”消息。

• 如果 member_expression 使用单元格引用，并且该单元格引用包含 CUBE 函数，则 member_expression 使用引用的单元格中的项目的 MDX 表达式，而不是显示在该引用的单元格中的值。

• 如果连接名称不是存储在工作簿中的有效工作簿连接，则 CUBEVALUE 函数返回 #NAME? 错误值。如果联机分析处理 (OLAP) 服务器未运行、不可用或返回错误消息，则 CUBEVALUE 函数返回 #NAME? 错误值。

• 如果元组中至少有一个元素是无效的，则 CUBEVALUE 函数返回 #VALUE! 错误值。

• 当遇到以下情况时，CUBEVALUE 函数返回错误值 #N/A：

• member_expression 用法不正确。

• member_expression 所指定的成员在多维数据集中不存在。

• 由于指定的值不交叉，元组无效。 （当多个元素来自同一层次结构时会发生此情况。）

• 集合至少包含一个其维数与其他成员都不同的成员。

• CUBEVALUE 在以下情况下可能返回错误值 #N/A：如果您在共享连接时引用数据透视表中的基于会话的对象，如计算成员或命名集，而该数据透视表已被删除或者您已将该数据透视表转换为公式 （在“选项”选项卡的“工具”组中，单击“OLAP 工具”，然后单击“转换为公式”）。

问题：Null 值转换为零长度字符串

在 Excel 中，如果某个单元格没有数据（由于您从未更改它或已删除其内容），该单元格将包含一个空值。在许多数据库系统中，空值被称为 Null 值。空值或 Null 值的字面意思就是“没有值”。但是，公式始终不会返回空字符串或 Null 值。公式通常返回以下三种值中的一种：数值、文本值（可能是零长度字符串）或者错误值（如 #NUM! 或 #VALUE）。

如果公式中包含连接到联机分析处理 (OLAP) 数据库的 CUBEVALUE 函数，并且对此数据库的查询的结果为 Null 值，Excel 会将此 Null 值转换成一个零长度字符串，即使该公式本来将返回一个数值也是如此。这会造成这样的情况：某个单元格区域中既包含数字又包含零长度字符串值，而这种情况会影响其他引用该单元格区域的公式的结果。例如，如果 A1 和 A3 包含数值，而 A2 包含带有返回零长度字符串的 CUBEVALUE 函数的公式，则以下公式会返回 #VALUE! 错误：

=A1+A2+A3

为了防止发生这种情况，可以使用 ISTEXT 函数来测试是否存在零长度字符串，并使用 IF 函数将零长度字符串替换成 0（零），如下面的案例所示：

=IF(ISTEXT(A1),0,A1)+IF(ISTEXT(A2),0,A2)+IF(ISTEXT(A3),0,A3)

或者，如果 CUBEVALUE 函数计算结果为零长度字符串，则您还可以在返回 0 值的 IF 条件中嵌套 CUBEVALUE 函数，如下面的案例所示：

=IF (CUBEVALUE(Sales,[Measures].[Profit],[Time].[2004],[All Product].[Beverages])=, 0, CUBEVALUE(Sales,[Measures].[Profit],[Time].[2004],[All Product].[Beverages]))

请注意，SUM 函数不需要进行这样的零长度字符串测试，因为它在计算返回值时将自动忽略零长度字符串。

案例

=CUBEVALUE(Sales,[Measures].[Profit],[Time].[2004],[All Product].[Beverages])

=CUBEVALUE($A$1,[Measures].[Profit],D$12,$A23)

=CUBEVALUE(Sales,$B$7,D$12,$A23)

以上是所有EXCEL的多维数据集函数描述用法以及使用案例。这次分享中存在哪些疑问或者哪些不足，可以在下面进行评论。如果觉得不错，可以分享给你的朋友，让大家一起掌握这些excel的多维数据集函数。