数组公式中的行提取

Question

我认为这是一个简单的语法问题。我从字符串中提取关键字。关键字集位于列中，源字符串位于单独的列中。

我希望在结果列的开头使用一个数组式类型构造来获得相邻列中每个字符串的关键字结果。我可以查询、筛选或任何其他类型的动态数组公式。

真实世界的电子表格有一个不确定大小的测试字符串列，根据构建查询的不同，从零项到大约4000项不等。关键字列也是动态的，当系统需要追加或删除关键字时会发生更改。它目前只有60行左右。每个字符串有四个结果的限制，匹配关键字没有特殊的优先级，它们的出现顺序也没有限制。

关键字可以是任意数量的单词。所以“树”和“高树”应该是两排。较长的关键字字符串总是优先。例如，字符串‘我花园里有一棵大树’的关键字结果是‘高树’，而不是‘树，高树’。

一个简短的例子: My关键字集(列A-并给出‘myWords’的命名范围)：

    ate
    blue
    cat
    the cat
    for
    dead
    bob
    alive

我的字符串要测试(B栏)：

Bob ate the dead cat
The cat ate live bob
No cat ate live dog
Bob is dead
Bob and the cat are alive

我的预期结果(C栏)：

Bob, Ate, Dead, Cat
The Cat, Ate, Bob
Cat, Ate
Bob, Dead
Bob, The Cat, Alive

示例电子表格是这里.

如果我复制下面的公式，我就会得到我的预期结果。

=PROPER(TEXTJOIN(", ",TRUE,ArrayFormula(IFERROR(REGEXREPLACE($A2,REGEXREPLACE($A2,"(?i)("&TEXTJOIN("|",TRUE,myWords)&")","(.*)"),{"$1","$2","$3","$4"})))))

效果好，不想要的方法：

Bob, Ate, Dead, Cat
The Cat, Ate, Bob
Cat, Ate
Bob, Dead
Bob, The Cat, Alive

如果我构造了一个数组公式版本，那么我得到了正确的结果，但都在第一行。

=arrayformula(PROPER(TEXTJOIN(", ",TRUE,IFERROR(REGEXREPLACE($A$2:$A$20,REGEXREPLACE($A$2:$A$20,"(?i)("&TEXTJOIN("|",TRUE,myWords)&")","(.*)"),{"$1","$2","$3","$4"})))))

不想要的结果，首选的方法类型：

Bob, Ate, Dead, Cat, The Cat, Ate, Bob, Cat, Ate, Bob, Dead, Bob, The Cat, Alive

我觉得答案会很简单，令人尴尬--但我就是搞不懂！

更新31/12/2020下面的答案是player0的完美解决方案。此答案已更新了示例电子表格。

Answer 1

用途：

=ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 PROPER(IFERROR(REGEXREPLACE(B2:B14, REGEXREPLACE(B2:B14, "(?i)("&
 TEXTJOIN("|", 1, myWords)&")", "(.*)"), 
 {"$1,", "$2,", "$3,", "$4,"})))),,9^9))), ",$", ))

​

​

最新情况：

=ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 PROPER(IFERROR(REGEXREPLACE(REGEXREPLACE(B2:B14, "\+", "♂"), 
 REGEXREPLACE(REGEXREPLACE(B2:B14, "\+", "♂"), "(?i)("&
 TEXTJOIN("|", 1, Answer!myWords)&")", "(.*)"), 
 {"$1,", "$2,", "$3,", "$4,"})))),,9^9))), ",$", ))

​

​