excel - 动态范围的偏移功能不忽略折线图中#N/A 的单元格

Question

我面临一个水平和垂直增加范围的excel偏移功能问题。我有一个从不断更新的原始数据构建的数据透视表。我主要关注枢轴中每季度的产品和相应的价格。我创建了另一张表，从枢轴和相应的混合价格中提取产品（根据枢轴值计算 - 销售额/单位）。

下面是代码：

将产品从支点拉出的公式：

IFERROR(INDEX($A$10:$A$155, AGGREGATE(15,3,ROW($A$10:$A$155)-ROW($A$9)/($A$10:$A$155<>""),行($A$10:A10))),#N/A)

显示产品相应混合价格的公式：

IFERROR(索引(AZ$10:AZ$155, MATCH($CC10,$A$10:$A$155,0)),#N/A)

现在我需要为上述数据创建一个折线图。下面是名称管理器的代码：

Quarters=OFFSET('混合价格计算'!$CD$9,0,0,1,COUNTA('混合价格计算'!$CC$9:$DA$9)-1)

0.45_Saline=OFFSET('混合价格计算'!$CD$10,0,0,1,COUNTA('混合价格计算'!$CC$10:$DA$10)-1)

0.9_Saline=OFFSET('混合价格计算'!$CD$11,0,0,1,COUNTA('混合价格计算'!$CC$11:$DA$11)-1)

10_Dextrose=OFFSET('混合价格计算'!$CD$12,0,0,1,COUNTA('混合价格计算'!$CC$12:$DA$12)-1)

其他产品也一样

我希望横轴是 Quarters 而不是 Products。

仪表板 - 基于切片器的过滤器

没有过滤器的原始 Excel

应用切片器过滤器时的 Excel（仅显示 2 个产品）

问题：当我在仪表板中使用 Slicer 过滤数据时，图表显示 #N/A。切片器作用于数据透视表。基本上我需要关于偏移函数的帮助，它只会选择那些同时具有行和列数据的值。目前我有适用于列的公式，但它不适用于行，因此折线图正在考虑所有行。但由于我从 100 多种产品中仅选择了几种产品（使用切片器），因此折线图完美地显示了这些选定产品的图形，而其余产品则显示 #N/A。

如何在图表图例中不显示#N/A？我认为结合垂直和水平偏移功能应该可以解决我的问题，但我正在努力寻找解决方案。

非常感谢任何帮助！谢谢！

标签： exceldynamicoffset

Answer 1

这里的问题是您将十个系列硬连线到图表中。您可以使用范围名称动态更改系列中的数据点数，但不能使用公式方法动态更改常规 Excel 图表中的系列数。这将需要VBA。

如果您改用数据透视图，则可以使用切片器选择系列，图例将仅显示可见系列。

编辑：这是一个屏幕截图，说明如何过滤数据透视图并且未使用的系列将不会显示在图例中。