在Excel图表中动态选择系列

Question

我有一个需要绘制大量系列的图表。但是，这些系列太多了，所以我只想绘制其中一些系列。我可以通过手动复制表格并删除不必要的行来静态地完成此操作，但显然，我更喜欢一些更加动态的方法，因为新的系列不断引入。

通常，我只想绘制达到某个阈值或某个排名的系列。我可以测试条件并返回例如0或1的结果。

现在，我不知道如何使用此信息来动态选择将绘制到图表中的系列。这是否可能？

- Cyan

---

你是在寻找一个纯Excel用户界面的解决方案吗？还是你愿意使用VBA宏？ - Rachel Hettinger

我的首选是纯UI解决方案。 显然，如果这种解决方案不存在，我将不得不考虑脚本编写，但这远不如前者理想（在我们公司中，脚本被标记为“病毒”）。 - Cyan

VBA（会出现宏警告）和脚本（例如VBScript）之间存在差异，后者可能会被标记为病毒。如果宏是主要问题，我会感到惊讶，因为只有当VBA代码具有自我复制潜力（即能够生成更多代码的代码）时，它们才会产生类似于病毒的消息。 - brettdj

Answer 1

另一种方法涉及表格及其过滤器。以下是起始表格，系列名称在第一列，类别在第一行，最后一列具有一些测试值的公式，返回true或false。

使用除最后一列之外的所有表格列插入图表，可能需要切换行和列，以便按行绘制系列。

单击标题中的自动筛选下拉菜单以获取测试列，在数字筛选下，选择等于，并在框中输入True。

您的测试公式不必返回true或false，它可以仅计算值的函数或任何您想要的内容。只需选择适当的过滤条件即可隐藏图表中不想显示的内容。

- Jon Peltier

Answer 2

这是一种需要使用公式来额外处理数据的方法，但不需要 VBA。

假设 Range A 包含您的数据。在此范围的上面（或下面）的行中放置一个公式，以确定该范围是否符合条件。

构建 Range B，与 A 相同大小，并使用以下公式： =IF(A1,A2,na()) 其中 A1 是该数据列（即图表的该列）的测试单元格。如果 A1 为 true，则此单元格中出现 A2 的值，否则会得到 #N/A。

使用 Range B 构建您的图表。任何带有 #N/A 的系列都不会被绘制。

为避免混乱的图例，使用 Range B 创建您的图表，但将所有测试单元格暂时设置为 true，以便所有系列都出现。然后向每个系列的最后一个点添加数据标签（请参见 Label Last Point Updated Add-In），并删除图例。现在编写适当的测试公式。

- Jon Peltier

Answer 3

另一个选择，跟随最终帖子的脚步，因为我遇到了同样的问题。我有一个“显示”区域，其中包含摘要表格和图表。我的源数据在另一个选项卡上。我有许多系列在列中，我希望用户能够选择显示这些列，这些列位于我的“显示”区域右侧。这些系列有两组，范围A和范围B。

在图表右侧运行的“显示”区域中，我有一个复选框列表（手动添加当系列更改时）。单元格主页设置为范围A中匹配系列列的上方一个单元格。范围A具有动态范围，以使用DMIN和一些DMAX公式向下设置日期。

为了避免宏排序问题，我有第二个范围，即范围B是动态的，因为第一列从范围A中拾取第一个“TRUE”，由相关复选框设置。从第一个TRUE中列出了所有相关数据，包括系列名称。日期范围简单地链接到范围A。

现在，通过来自范围B的OFFSET公式，我有一个动态数量的系列，其中图表范围由范围A流过范围B到图表。

我有61个系列，当用户选择“全部”并且有三年每周日期时可能会变得混乱，但整个过程只需要5秒就可以刷新，这低于我的用户阈值6秒。

关键在于找到并显示正确标记的系列。我的系列实际上是分组的，所以我希望它们保持顺序。数组公式使我变慢到了9秒，但是跳过TRUE行的嵌套IF更快，只用了5秒。有一些缓慢计算的原因不适用于此，但嵌套IF比ARRAY更快。

- Nutter

Answer 4

是的。

使用动态范围在图表中非常有效。这有两个典型的应用：

1. 使用动态范围，其中系列已知，但系列的数据集长度是可变的。Stephen Bullen's FunChrt1.zip 是这种技术的一个很好的例子。无需 VBA

2. 当系列数量本身是可变的时，需要一些基本的 VBA，因为当动态范围被分配为源数据范围时，它变得硬编码（不像特定图表系列的定义名称保持“活动状态”）。 这听起来像您查询的方法。Jon Peltier 在 Dynamic Chart Source Data 中涵盖了这个问题。

- brettdj

---

谢谢，我会去研究一下。事实上，在我的情况下，范围已知且静态，而要绘制的系列数量是动态的。 - Cyan

嗯，这非常接近我的需求了。 然而，我的问题是我有大量的系列数据，我只想绘制其中的一些。我可以使用同一行中的单元格来“标记”它们，通常使用测试结果（1表示“绘制我”，0表示“不要绘制我”）。 Jon Peltier的方法的问题在于它要求所有系列都是连续的。我需要一种方法，可以根据条件选择系列，或者接受具有空行/列的表格（然后我将根据条件动态构建表格）。 - Cyan

这是一个很好的总结。我会选择表格选项（而且之前使用这种方法时，我确实就是这么做的）。你可以这样做：（1）建立一个包含冗余数据的单个表格，即总结可能存在的所有行；（2）建立第二个表格，通过对现有数据进行排名来对表格1进行排序（RANK公式在此方面非常有用）。这样，空白行就会落到底部，并且您已经拥有了现有行的动态范围（例如，表格1中的10行为非空行）。 - brettdj