如何将SPLIT去标点公式与随机选3个唯一词公式合并为一个公式？

合并后的完整公式 #

=ARRAYFORMULA(ARRAY_CONSTRAIN(SPLIT(FLATTEN(QUERY(QUERY(QUERY(SPLIT(FLATTEN(ROW(INDIRECT("A2:A"&COLUMNS(FILTER(REGEXREPLACE(SPLIT(A2," "),"[,.?!]",""), REGEXREPLACE(SPLIT(A2," "),"[,.?!]","")<>"")))&"×"&RANDARRAY(1, COLUMNS(FILTER(REGEXREPLACE(SPLIT(A2," "),"[,.?!]",""), REGEXREPLACE(SPLIT(A2," "),"[,.?!]","")<>"")))&"×"&FILTER(REGEXREPLACE(SPLIT(A2," "),"[,.?!]",""), REGEXREPLACE(SPLIT(A2," "),"[,.?!]","")<>"")), "×")),"select max(Col3) group by Col2 pivot Col1"),"offset 1", 0),,9^9)), " "), 9^9, 3))

合并时的关键改动 #

• 替换数据源：把原第二个公式里所有引用B2:G2的地方，换成第一个公式的核心处理逻辑FILTER(REGEXREPLACE(SPLIT(A2," "),"[,.?!]",""), REGEXREPLACE(SPLIT(A2," "),"[,.?!]","")<>"")，加FILTER是为了过滤掉处理后可能出现的空值（比如连续标点拆分出来的无效空元素）。
• 匹配数组维度：原公式里的RANDARRAY(ROWS(B2:G2), COLUMNS(B2:G2))改成了RANDARRAY(1, COLUMNS(过滤后的单词数组))，因为第一个公式生成的是横向的单词列表，行数固定为1，列数等于有效单词的数量。
• 去掉冗余嵌套：删掉第一个公式外层的ARRAYFORMULA，因为第二个公式已经有外层的ARRAYFORMULA负责数组运算，重复嵌套会出错。

更简洁的替代公式 #

要是觉得上面的公式太繁琐，也可以用这个逻辑更直观的版本，同样能实现随机选3个唯一单词：

=ARRAYFORMULA(INDEX(UNIQUE(FILTER(REGEXREPLACE(SPLIT(A2," "),"[,.?!]",""), REGEXREPLACE(SPLIT(A2," "),"[,.?!]","")<>"")), RANDBETWEEN(1, COUNTA(UNIQUE(FILTER(REGEXREPLACE(SPLIT(A2," "),"[,.?!]",""), REGEXREPLACE(SPLIT(A2," "),"[,.?!]","")<>""))), 3))

这个公式先处理得到去重后的有效单词列表，再通过INDEX搭配RANDBETWEEN随机选出3个不重复的元素。

内容的提问来源于stack exchange，提问作者Jelmer405