excel - 在一系列单元格中获取 5 个最常见的分隔子字符串

Question

我有一个 Excel 工作表，每个单元格内都有子字符串，由 # 分隔。例如:

<头>

一个	B	C
F#G #H #I #J #K #L	M#N#O#P	A#B#C#D #E
F#G #H #I	N#O#P	A#B#C##E

如何在单元格范围内找到 5 个最常见的子字符串？

示例数据的预期输出应如下所示:

<头>

子字符串	计数
一个	2
B	2
C	2
E	2
F	2

Answer 1

如果有人可以访问 Microsoft365 的 LET()函数，这可以使用函数的组合来完成:

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() 允许在公式中使用可重复使用的变量。所以在上面我们分配了一个值数组并将其命名为“X”。数组被拉取:

=SORT(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("#",,A1:C2),"#","</s><s>")&"</s></t>","//s[.!='']")))

地点:

• "<t><s>"&SUBSTITUTE(TEXTJOIN("#",,A1:C2),"#","</s><s>")&"</s></t>" - 用于创建有效的 XML 字符串和；
• "//s[.!='']" - 检索所有非空字符串的有效 xpath。
• TRIM()将删除任何前导和尾随空格。 SORT()然后对数组进行升序排序。

如果您想了解更多关于使用 FILTERXML() 将字符串“拆分”为元素的机制的信息，您可以阅读here .

现在我们有了一个变量，我们可以在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})

使用 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() 'd“X”。然后二维数组在第 2 列排序。

排除以上问题，我们可以简单地使用 INDEX()使用以下方法检索我们需要的 5 行(以及两列):

=INDEX(<TheAbove>,{1;2;3;4;5},{1,2})