☰

Excel 行和列的动态溢出函数

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 行和列的动态溢出函数

我需要一个 Excel 函数按列溢出，然后按行溢出

我在 Excel 中有一张表格，

波段表

其中包含名为 band1 . . . band20 和“测试部分”的列。想要一个仅提取带有“6F35_G2_45_44”的行的 Excel 函数（我的示例代码就是这样做的）。

然而，我需要具体说明它是如何溢出的，

这是我迄今为止尝试过的方法。我能够溢出列（我有 6 条匹配的记录），但我无法让函数从逗号分隔的值向下溢出。


=LET(
    xmlData, TEXTJOIN("</item><item>", TRUE, IF(BANDS_TABLE[Test Section]="6F35_G2_45_44", BANDS_TABLE[band1], "")),
    result, TRANSPOSE(FILTERXML("<root><item>" & xmlData & "</item></root>", "//item")),
    numRows, COUNTA(result),
    numCols, MAX(COUNTA(result)),
    finalResult, INDEX(result, SEQUENCE(numRows), SEQUENCE(1,numCols,1,1))
)

如你看到的：

我利用了 FILTERXML() 的溢出功能，但我真正需要的是第一次溢出之后再应用某种二次溢出。最终结果应该是 6 列，大约 500 行，将用于绘图。

修订版本 1： 假设我有一张这样的桌子

姓名 版本 价值观
测试1 2 1, 2, 3, 4, 5, 6, 10
测试2 2 2.3, 5, 10, 12, 11,
测试3 4 14, 15, 16,

我需要一个水平溢出的溢出函数然后垂直

测试1 测试2
1 2.3
2 5
3 10
4 12
5 11
6
10

答案1

你需要做这样的事情：

=LET(
    X,BANDS_TABLE[Test Section],
    Y,BANDS_TABLE[NAME],
    Z,BANDS_TABLE[band1],
    mtch,"A",
    TRANSPOSE(HSTACK(FILTER(Y,X=mtch,""),DROP(REDUCE("",FILTER(Z,X=mtch,""),LAMBDA(a,b,VSTACK(a,TRIM(TEXTSPLIT(b,","))))),1))))

在此处输入图片描述

REDUCE 将迭代 FILTER 返回中的行并堆叠每个 TEXTSPLIT 的结果。然后我们堆叠在 Name 列上，然后转置。

相关内容