如果有权访问 Microsoft 365LET()
函数,这可以使用函数的组合来完成:
公式为E1
:
=LET(X,SORT(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("#",,A1:C2),"#","</s><s>")&"</s></t>","//s[.!='']"))),INDEX(SORT(CHOOSE({1,2},UNIQUE(X),MMULT(--(UNIQUE(X)=TRANSPOSE(X)),SEQUENCE(COUNTA(X),,,0))),2,-1),{1;2;3;4;5},{1,2}))
解释:
LET() https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999允许在公式中使用可重复使用的变量。因此,在上面我们分配了一个值数组并将其命名为“X”。该数组由以下方式拉取:
=SORT(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("#",,A1:C2),"#","</s><s>")&"</s></t>","//s[.!='']")))
Where:
-
"<t><s>"&SUBSTITUTE(TEXTJOIN("#",,A1:C2),"#","</s><s>")&"</s></t>"
- 用于创建有效的 XML 字符串;
-
"//s[.!='']"
- 用于检索所有非空字符串的有效 xpath。
-
TRIM()
将删除所有前导和尾随空格。SORT()
然后对数组进行升序排序。
如果您想了解有关此机制的更多信息,可以使用以下方法将字符串“拆分”为元素FILTERXML()
,你可以读一读here https://stackoverflow.com/q/61837696/9758194.
现在我们有了一个变量,我们可以在第三个参数里面使用它LET()
:
=INDEX(SORT(CHOOSE({1,2},UNIQUE(X),MMULT(--(UNIQUE(X)=TRANSPOSE(X)),SEQUENCE(COUNTA(X),,,0))),2,-1),{1,2,3,4,5},{1;2})
Using INDEX()
我们可以从数组中“切片”行/列,其中:
-
SORT(CHOOSE({1,2},UNIQUE(X),MMULT(--(UNIQUE(X)=TRANSPOSE(X)),SEQUENCE(COUNTA(X),,,0))),2,-1)
- 结构有点复杂,但这里的想法是CHOOSE({1,2}
允许二维数组,其中第一列填充为UNIQUE()
来自“X”的值,第二个是总数组中每个唯一元素的计数。计数是使用完成的MMULT()
其中每个独特的元素都被计入TRANSPOSE()
是“X”。然后二维数组在第二列上排序。
完成上述操作后,我们可以简单地使用INDEX()
使用以下命令检索我们需要的 5 行(以及两列):
=INDEX(<TheAbove>,{1;2;3;4;5},{1,2})