☰

自动选择散点图上的系列

tag-icon • tag-icon microsoft-excel charts
自动选择散点图上的系列

使用 Excel 365

下面是我一直在研究的一个例子，它非常接近我想要的：https://docs.google.com/spreadsheets/d/1zZiFDaUhuZAhyHm3HZkcZrUl9KEoV97N/edit?usp=sharing&ouid=108210493177158563166&rtpof=true&sd=true

基本上，我有数据 A、B 和 C，对于表中每个具有多个条目的唯一 A，我想在散点图中用线绘制 B 与 C。我正在设计一个模板，它需要处理所有数据排列。

我得到的结果与此非常接近，但有几个问题我无法解决：

  1. 我需要为表中 A 和 B 的所有可能值分配足够的空间。这不是什么大问题，我目前处理的数据集非常小，但如果它是完全动态的，能够处理任意数量的值就好了。

  2. 图例显示表格中所有未使用的列的空白条目。我只希望它显示我有数据的条目。

  3. 对于不相邻的 B 值，该线不相连（请参见第 3 行的示例）。我希望它在每个系列内都相连。

希望这是有意义的，请随时提出任何澄清问题。

提前致谢。

答案1

这是您需要做的，让每一点都充满活力（参考动画）：

在此处输入图片描述

  • 首先，不要使用空白范围进行迭代，而是使用Structured Referencesaka Tables--> 名为的表Table1
  • 用于列的公式F3:G16将J2:O8逐渐扩大。

• 单元格中使用的公式F3

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _Uniq, UNIQUE(_A),
     HSTACK(_Uniq, COUNTIF(_A,_Uniq)))

• 单元格中使用的公式J2

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _B, INDEX(_Data,,2),
     _C, TAKE(_Data,,-1),
     _UniqA, TOROW(UNIQUE(_A)),
     _UniqB, UNIQUE(_B),
     _ArrayB, VSTACK("",_UniqB),
     _DataBody, VSTACK(_UniqA&"A", MAKEARRAY(ROWS(_UniqB), COLUMNS(_UniqA), LAMBDA(r,c, 
                FILTER(_C, (INDEX(_UniqA,1,c)=_A)*(INDEX(_UniqB,r)=_B),NA())))),
     HSTACK(_ArrayB, _DataBody))

上述公式也可以通过使用LAMBDA()--> 来定义，因此您需要做的就是将上述公式包装在LAMBDA()-->内parameter_or_calculation并将asarray替换为。因此它变为并输入Table1arrayrefers to当将名称定义为

=LAMBDA(array, LET(_Data, array, the rest continues as is)

在此处输入图片描述

在Excel表中输入：

=SCATTER_PLOT(Table1)

在此处输入图片描述

  • 请注意，我使用了辅助MAKEARRAY()函数，LAMBDA()而不是使用REDUCE()，也可以使用，但它是占用大量资源的函数，并且在堆叠数据时REDUCE()效率不高。MAKEARRAY()
  • 插入时，Scatter with smooth lines and markers确保选择数据范围J2:O8，并记住Connect data points with line选择Hidden and“空单元格”框。
  • 我也用过Conditional Formatting边框。这是Excel。

答案2

可能的修改：

  1. 为了给更多数据腾出空间：

    F3: =UNIQUE(FILTER(B:B,ISNUMBER(B:B)))
    G3: =COUNTIF(B:B,F3#)
    K3: =TRANSPOSE(F3#)
    K4: =K3# & " A"
    J5: 复制=UNIQUE(FILTER(C:C,ISNUMBER(C:C)))
    K5到 =FILTER($D:$D,($B:$B=K$3)*($C:$C=$J5),#N/A)所需区域。
    最好在此处使用真正需要的范围，例如
    =FILTER($D$1:$D$100,($B$1:$B$100=K$3)*($C$1:$C$100=$J5),#N/A)

  2. 这取决于Chart data range。我不知道有没有办法让它动态化（没有 VBA）。我们可以定义一个公式来动态定义这个数据范围，但是如果我们在这里使用它，它将被立即计算并保存为静态地址。

  3. 为了在点之间建立连接，您应该更改一个图表选项：
    Select Data Source > Hidden and Empty Cells > Connect data points with line

相关内容