Excel 2013 : Label deconfliction in labeled scatter plot

Question

在 Excel 2013 中，我使用单元格中的值标记散点图。我希望标签不重叠。我可以手动移动标签，但我创建了一个过滤器来自动创建新绘图，因此我希望标签冲突也能自动发生。

这可能吗？无需 VBA 的解决方案的奖励。

下面重叠标签的示例:

Answer 1

这是一个非 VBA 解决方案，如果点不多且值的范围相当稳定，则该解决方案有效。

它的工作原理是将 Y 系列分成两个单独的系列，一个系列的标签位于点上方，另一个系列的标签位于点下方。

默认情况下，点将被放入系列中，标签位于点下方。但是，对于每个点，分割公式都会检查附近是否存在另一个低于当前点或与当前点水平的点。如果有，则将当前点放入系列中，并在该点上方添加标签。

我假设您的数据分为 3 列:筛选器、X、Y。(如果您仅筛选 X 或 Y，则这也适用，您可以忽略筛选器列)

需要四个工作列。前两个是 X 过滤和 Y 过滤。

使用下面屏幕截图中的示例，此公式进入 D2 并填充 D 和 E 列:

=SUBTOTAL(9,B2)

如果数据点被过滤掉，这只会导致数据点变为 0,0，从而在分割公式中忽略它们。

为了使内容更易于阅读，请将过滤值的名称设置为 X (D2:D10) 和 Y (E2:E10)

然后这个公式作为数组公式进入 F2，使用 CTRL + SHIFT + ENTER 输入，然后向下填充。

=IF(SUM((ABS(D2-X)<0.75)*(E2-Y>0)*(E2-Y<0.75))+(SUM((ABS(D2-$D$2:D2)<0.75)*($E$2:E2=E2))>1),E2,NA())

此公式创建在该点上方带有标签的系列。该公式执行两项检查:

1. 检查是否存在“接近”且低于当前点的任何其他点。
2. 检查是否存在与当前点“接近”且水平的任何先前点。 (对于此检查，我们不能与所有点进行比较，否则两个点都会在该点上方有标签。我们只检查当前点，以便第一个点仍然有一个下方的标签，后续点有一个上方的标签。)

如果满足这些条件中的任何一个，则使用该值，否则使用 NA()(以便不显示任何点)。

在此公式中，X 轴和 Y 轴上的 close 定义为“0.75”。您需要根据数据中的“接近”值更改公式中的这些值。 IE。 X 和 Y 值的差异需要将标签放置在点的相对侧。

然后，此公式进入 G2 以创建在该点下方带有标签的系列，即所有剩余值:

=IF(ISERROR(F2),E2,NA())

只需一个技巧即可使自动过滤器对数据起作用。由于我们使用了 SUBTOTAL 公式，Excel 会认为最后一行是小计行，并自动将其从自动筛选中排除，即使我们在应用自动筛选之前选择了所有数据也是如此。要使用自动筛选器，您实际上需要在创建自动筛选器之前创建一个没有小计函数的额外虚拟行。

现在，创建包含 2 个系列的散点图:

使用上述 X 和 Y 的系列 1

使用下面的 X 和 Y 的系列 2

(这些数据系列中不需要包含空白行)

将两个系列的所有细节设置为相同，只是系列 1 的标签显示在点上方，系列 2 的标签显示在点下方。

下面的屏幕截图显示了结果，包括过滤时的结果。标签将始终低于值，除非附近有另一个可见点。每个屏幕截图还显示其中一个公式。

所有点:

按“a”过滤

按“b”过滤

编辑:我们实际上可以使公式更智能一些，以处理多个收盘价位值。这将使每个后续值在上方和下方的标签之间交替。但是，它仅在所有值都在“接近”范围内且数据首先按 X 排序时才有效

=IF(SUM((ABS(D2-X)<0.75)*(E2-Y>0)*(E2-Y<0.75))+(MOD(SUM((ABS(D2-$D$2:D2)<0.75)*($E$2:E2=E2)),2)=0),E2,NA())