动态 Excel 组合图的问题 - 处理缺失的数据系列和标签

动态 Excel 组合图的问题 - 处理缺失的数据系列和标签

各位好心人,大家下午好,

让我和你分享一下我一段时间以来一直在努力解决的问题。我使用数据透视表在组合图上绘制不同的数据点,其中特定工具的值由列表示,给定类别的平均值由水平直线表示。这些数据透视表(经过几次转换后)由 Power Query 从更大的“平面”文件中提供。每个数据透视表代表一个数据点(我知道,这不是最好的方法,但这不是我的主意),并且这些数据透视表上附加了切片器以更改类别。当选择特定类别时,图表应绘制工具及其类别平均值。

为了实现这一点,我使用“偏移”公式来创建数据系列和标签,例如:=OFFSET('基金级别比较数据'!$B$6,,,COUNTIF('基金级别比较数据'!$B$6:$B$500,"<>"))。简而言之,这就是它的样子,我无法为 A 列提供完整的值,但您可以了解大致意思:

数据透视表 - 一个属性的示例

它通常运行良好,但我遇到的主要问题是,一旦没有单个标签项要绘制,这些图表就会不断崩溃并改变随机(??)图表的整个布局,如下所示。这可能是因为并非每个基金都报告所有值,有时源文件中会有空白,而实际上它们应该如此,否则“0”会影响我所取的平均值。这是它在 Pivot 下的样子。

用于绘制数据透视表 - 缺少标签

显然,Excel 并没有那么智能,它不会忽略丢失的标签,也不会简单地不绘制任何保留模板格式的内容,或者:

  1. 还有其他我不知道的技巧吗?
  2. 它超出了标准 Excel 图表容量,我应该建议切换到 Power BI 吗?

Excel 会抛出以下错误:

错误 - 示例 1

错误 - 示例 2

我将非常感激任何提示。

答案1

根据我上面关于放弃数据透视表的评论,这里有一个例子。AE 是用=RANDARRAY(10,5)

样本数据

平均值的公式是=SEQUENCE(ROWS(A2#),1,AVERAGE($A2#),0)(请注意,SEQUENCE函数用于对每行数据重复平均值)

您可以在图表中打开或关闭系列以隔离一个或多个列: 系列选择

如果您的数据是表格形式: 在此处输入图片描述

列标题是=TRANSPOSE(SORT(UNIQUE(Table3[Attribute]))) ,并且日期只是省略了 TRANSPOSE。

相关内容