☰

自动更新过滤器

tag-icon • tag-icon microsoft-excel
自动更新过滤器

我有一张描绘 3 年数据的图表，以及一个选择他们想要查看哪一年（些年份）数据的选项。我从图表中提取数据，并将该图表放入公式中，如果他们选择取消选择该年份，该公式会将数据更改为 #N/A。但图表仍然显示该年份，有没有办法让图表将 NA 选项识别为不应绘制的内容？

我的数据看起来像这样：

期间 # 订单 # 出货量
P6/2014; 281 74
P7/2014; 498 126
P8/2014; 589 132
P9/2014; 655 158
P10/2014; 679 161
P11/2014; 489 115
P12/2014; 574 129
P13/2014; 340 80
P1/2015; 615 225
P2/2015; 0 0
P3/2015; 0 0
P4/2015; 0 0
P5/2015; 0 0
P6/2015; 0 0
P7/2015; 0 0
P8/2015; 0 0
P9/2015; 0 0
P10/2015； 0 0
P11/2015； 0 0
P12/2015； 0 0
P13/2015； 0 0

当取消选择 2015 时，切换到此：

期间 # 订单 # 出货量
P6/2014; 281 74
P7/2014; 498 126
P8/2014; 589 132
P9/2014; 655 158
P10/2014; 679 161
P11/2014; 489 115
P12/2014; 574 129
P13/2014; 340 80
P1/2015; #N/A #N/A
P2/2015; #N/A #N/A
P3/2015; #N/A #N/A
P4/2015; #N/A #N/A
P5/2015; #N/A #N/A
P6/2015; #N/A #N/A
P7/2015; #N/A #N/A
P8/2015; #N/A #N/A
P9/2015; #N/A #N/A
P10/2015; #N/A #N/A
P11/2015; #N/A #N/A
P12/2015; #N/A #N/A
P13/2015; #N/A #N/A

我在这些领域做出改变的公式是；=IF($AY$13=TRUE,$J24,NA())

所以我希望我选择的所有这些数据图表能够显示 2015 年的任何数据，并且不会将其包含在图表中。

答案1

我假设您有一个图表，其源数据包含所有年份以及该年份的值或 #N/A - 希望是使用该NA()函数创建的。
在这种情况下，年份仍会显示在您的图表上，因为它们仍在您的数据中。您需要对其进行更改，以便完全删除源数据中的年份。
您可以使用数据透视图来执行此操作，尽管您需要编写一些 VBA 来在用户选择不同年份时刷新数据透视缓存。
您也可以使用一些公式和动态范围来执行此操作。这样做的可行性取决于您的图表数据有多大和多动态。如果您显示每年的摘要，这可能很容易。如果您显示数百个数据点，这可能会很麻烦。
您能否添加一个您想要的结果的示例，以便我们可以制定答案？（我会根据您的回复更新此答案。）

根据 OP 的更新进行更新
Excel 不会丢弃这些NA()值，因为即使没有要绘制的图形，它仍然可以看到它们。这和在那里放文本是一样的。您的轴仍然可以看到所有时间段，因此它有标签可以放在轴上，然后跳过它无法处理的数据。我们必须改变它，以便 Excel 只查看我们想要的数据。

  • 问题 1）没有绘制某些年份的图表
  • 问题 2）仍在规划未来后那些年。（仅限 2014 年和 2016 年图表）

如果只是问题 1，我们可以设置几个动态的 命名 范围并将它们设置为图表源。但是，由于问题 2，我们必须先设置一个临时表，然后然后创建命名范围。

首先，如果这篇文章有点晦涩难懂，我深表歉意。我经常被指责太冗长。我真的希望我可以在这里上传文件...

步骤1

这是我的起点的 CSV 版本。我的数据在范围内A1:D35（请注意，我在前面添加了一个名为的列Order- 稍后会有所帮助 - 并将数据扩展到 2016 年。）

订单,期间,订单,发货
1,P6/2014,281,74
2,P7/2014,498,126
3,P8/2014,589,132
4,P9/2014,655,158
5,P10/2014,679,161
6,P11/2014,489,115
7,P12/2014,574,129
8,P13/2014,340,80
9,P1/2015,615,225
10,P2/2015,0,0
11,P3/2015,0,0
12,P4/2015,0,0
13,P5/2015,0,0
14,P6/2015,0,0
15,P7/2015,0,0
16,P8/2015,0,0
17,P9/2015,0,0
18,P10/2015,0,0
19,P11/2015,0,0
20,P12/2015,0,0
21,P13/2015,0,0
22,P1/2016,0,0
23,P2/2016,0,0
24,P3/2016,0,0
25,P4/2016,0,0
26,P5/2016,0,0
27,P6/2016,0,0
28,P7/2016,0,0
29,P8/2016,0,0
30,P9/2016,0,0
31,P10/2016,0,0
32,P11/2016,0,0
33,P12/2016,0,0
34,P13/2016,0,0

第2步

将临时表添加到NA()不需要的部分。对您来说，这可能与步骤 1 相同。对我来说，我使用步骤 1 作为原始数据，因此我需要另一个步骤。我的数据在范围内F1:I35。当我选择删除 2015 时，它最终看起来像这个 CSV。

订单,期间,订单,发货
1,P6/2014,281,74
2,P7/2014,498,126
3,P8/2014,589,132
4,P9/2014,655,158
5,P10/2014,679,161
6,P11/2014,489,115
7,P12/2014,574,129
8,P13/2014,340,80
9,P1/2015,#N/A,#N/A
10,P2/2015,#N/A,#N/A
11,P3/2015,#N/A,#N/A
12,P4/2015,#N/A,#N/A
13,P5/2015,#N/A,#N/A
14,P6/2015,#N/A,#N/A
15,P7/2015,#N/A,#N/A
16,P8/2015,#N/A,#N/A
17,P9/2015,#N/A,#N/A
18,P10/2015,#N/A,#N/A
19,P11/2015,#N/A,#N/A
20,P12/2015,#N/A,#N/A
21,P13/2015,#N/A,#N/A
22,P1/2016,0,0
23,P2/2016,0,0
24,P3/2016,0,0
25,P4/2016,0,0
26,P5/2016,0,0
27,P6/2016,0,0
28,P7/2016,0,0
29,P8/2016,0,0
30,P9/2016,0,0
31,P10/2016,0,0
32,P11/2016,0,0
33,P12/2016,0,0
34,P13/2016,0,0

步骤 3

添加第三个表，该表仅提取第二个表中没有的值#N/A。这是使用数组公式完成的。还记得我们如何添加列吗？我们将找到列中Order所有不符合的值，然后使用剩余的值来提取其余数据。我的第三个表在范围内。列 中第一个值的公式是：（对于所有这些公式，重要的是输入它以将它们转换为数组公式，如两端的括号所示。）#N/AOrdersK1:N35
OrderSHIFT+ENTER{ }

{=MIN(IF(NOT(ISERROR($H$2:$H$35)),$F$2:$F$35))}

Order这将查找第二张表 ( ) 中列 ( )中的第一个值，F2:F35其中Orders列 ( H2:H35) 不是错误。列
中其余第二个值的公式Order为：

=IF(OR($K2=0,$K2=MAX($F$2:$F$35)),0,MIN(IF(NOT(ISERROR($H$2:$H$35)),IF($F$2:$F$35>$K2,$F$2:$F$35))))

首先检查前一个值（K2在本例中）是否是0第二个表中的值的最大值 - 这意味着我们完成了 - 然后返回。否则，它会在第二个表的列中找到第一个大于前一个值0的值，并且该列不是错误的值。 此公式可以拖到列的其余部分。结果看起来像这个 CSV。OrderOrders

顺序
1
2
3
4
5
6
7
8
22
23
24
25
26
27
28
29
30
31
32
33
34
0
0
0
0
0
0
0
0
0 0
0
0
0
0

步骤4

INDEX使用和的组合MATCH来查找步骤 3 中存留的任何值的值。Order公式“期间”、“订单”和“发货”的顺序如下：

=INDEX(B$2:B$35,MATCH($K2,$A$2:$A$35,0))
=INDEX(C$2:C$35,MATCH($K2,$A$2:$A$35,0))
=INDEX(D$2:D$35,MATCH($K2,$A$2:$A$35,0))

将它们作为第一条记录输入，然后复制/粘贴或向下拖动其余记录。请注意，这些引用回第一个表，唯一的区别是第一个参数。这是当您选择隐藏 2015 年时 CSV 的样子。

订单,期间,订单,发货
1,P6/2014,281,74
2,P7/2014,498,126
3,P8/2014,589,132
4,P9/2014,655,158
5,P10/2014,679,161
6,P11/2014,489,115
7,P12/2014,574,129
8,P13/2014,340,80
22,P1/2016,0,0
23,P2/2016,0,0
24,P3/2016,0,0
25,P4/2016,0,0
26,P5/2016,0,0
27,P6/2016,0,0
28,P7/2016,0,0
29,P8/2016,0,0
30,P9/2016,0,0
31,P10/2016,0,0
32,P11/2016,0,0 33
,P12/2016,0,0
34,P13/2016,0,0
0,#N/A,#N/A,#N/A
0,#N/A
,#N/A,#N/A
0,#N/A,
#N/A,#N/A,#N/A
0,#N/A,#N/A,#N/A
0,#N/A,#N/A,#N/A
0,#N/A,#N/A,#N/A
0,#N/A,#N/
A,#N/A 0,#N/A,
#N/A,#N/A 0,#N/A,#N/
A,#N/A 0,#N/A,#N/A,#N/A
0,#N/A,#N/A,#N/A

步骤 5

为 X 轴标签、系列 1 和系列 2 创建动态命名范围。我使用的是 Excel 2010，如果是动态命名范围，它不允许您一次设置整个图表数据源；它会不断恢复到不会更新的硬编码范围。这意味着我们必须为每个系列和轴单独创建一个。要创建命名范围，请转到功能区Formulas并单击Name Manager中间附近的某个位置。在对话框窗口中，单击New左上角的按钮。为其命名并指定要引用的范围。这里的技巧是某些函数（如OFFSET返回范围对象）可以在此处使用它们，而不是类似的东西=A1:D35。这是我创建的 3 个命名范围及其公式。

名称：rngPeriod
公式：

=OFFSET(Sheet1!$L$2,0,0,IFERROR(MATCH(0,Sheet1!$K:$K,0)-2,MATCH(MAX(Sheet1!$A:$A),Sheet1!$K:$K,0)-1))

名称：rngOrders
分子式：

=OFFSET(Sheet1!$M$2,0,0,IFERROR(MATCH(0,Sheet1!$K:$K,0)-2,MATCH(MAX(Sheet1!$A:$A),Sheet1!$K:$K,0)-1))

名称：rngShipments
分子式：

=OFFSET(Sheet1!$N$2,0,0,IFERROR(MATCH(0,Sheet1!$K:$K,0)-2,MATCH(MAX(Sheet1!$A:$A),Sheet1!$K:$K,0)-1))


每个公式的工作方式都相同。从我们想要的数据的顶部开始。向下移动一个。找到列0中的第一个Order或找到最大值 - 表示我们想要的数据的末尾 - 并使范围足够高以覆盖从第一行到最后一行的所有内容。关键点：如果您选择隐藏每一年，这将导致错误，并且您的图表将不会显示，因此每个值都是，#N/A并且表中的第一个值Order是0因为MATCH函数返回1，1-2=-1并且您不能有-1行高的范围。

步骤 6

设置图表以使用这些动态命名范围。使用您想要的任何值，Series name:但Series values:应该是动态范围的名称。这里非常烦人的关键点是您必须包含文件名。对水平（类别）轴标签执行相同的操作，您就一切就绪了。我的图表最终使用这些作为系列 1、系列 2 和轴标签：

=sample.xlsx!rngOrders
=sample.xlsx!rngShipments
=sample.xlsx!rngPeriod

相关内容