使用Google表中的数组公式搜索多行的多个行的内容，以获取特定文本
我有一个列，每行包含多个单词，以下示例：
Fight; (People: Jack, Gil, Row, Pitt); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow,)
Fight; (People: Jack, Clive, Gareth); (Link: Box, Wave, Arrow)
Fight; (People: Jack, Gil, Jan); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow, Square, Attack)
我正在尝试从这些行中汲取特定的单词，以便我可以对其他信息进行分解，例如这些单词出现了多少次或与这些单词相关的时间。问题是我还没有找到一种专门搜索该词和该词的方法。
| A列 - 类型 | B-时间 |
|---|---|
| 战； （人：杰克，吉尔，行，皮特）； （链接：盒子，波浪，线，后卫，圆形，宽，箭头） | 0：02：43 |
| 战斗； （人：杰克，克莱夫，加雷斯）； （链接：框，波，箭头） | 0：01:54 |
| 战斗； （人：杰克，吉尔，扬）； （链接：框，波浪，线，后卫，圆形，宽，箭头，广场，攻击） | 0：00：23 |
在搜索上述信息中使用以下公式搜索以下公式，我会得到以下结果：
**Participation #**
=ARRAY_CONSTRAIN(ARRAYFORMULA(FILTER(COUNTIF($A2:$A4, "*" &$D8:$D14 & "*"),NOT(ISBLANK($D8:$D14)))),COUNTA($D8:$D14),1)
**Time**
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(SUMIF($A2:$A4,"*" & $D8:$D14 & "*",$B2:$B4),"")),COUNTA($D8:$D14),1)
| 人们 | 参与＃ | 时间 |
|---|---|---|
| 杰克 | 3 | 0:05 ：00 |
| GIL | 2 | 0:03:06 |
| 行 | 第3 | 0:05:00 |
| PITT | 1 | 0:02:43 |
| CLIVE | 1 | 0:01:54 |
| GARETH | 1 | 0:01:54 |
| JAN | 1 | 0:00:00:23 |
排名3次，尽管只参加了一次，但由于“箭头”一词。是否可以进行修改，以便我只搜索特定的单词？
I have a column where each row contains multiple words, example below:
Fight; (People: Jack, Gil, Row, Pitt); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow,)
Fight; (People: Jack, Clive, Gareth); (Link: Box, Wave, Arrow)
Fight; (People: Jack, Gil, Jan); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow, Square, Attack)
I'm trying to pull specific words from those rows so that I can tally other information, such as how many times those words appear or how much time are associated with those words. The issue is I haven't found a way to specifically search for that word and that word only.
| Column A - Type | Column B - Time |
|---|---|
| Fight; (People: Jack, Gil, Row, Pitt); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow) | 0:02:43 |
| Fight; (People: Jack, Clive, Gareth); (Link: Box, Wave, Arrow) | 0:01:54 |
| Fight; (People: Jack, Gil, Jan); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow, Square, Attack) | 0:00:23 |
When searching the above information with the following formula, I get these results:
**Participation #**
=ARRAY_CONSTRAIN(ARRAYFORMULA(FILTER(COUNTIF($A2:$A4, "*" &$D8:$D14 & "*"),NOT(ISBLANK($D8:$D14)))),COUNTA($D8:$D14),1)
**Time**
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(SUMIF($A2:$A4,"*" & $D8:$D14 & "*",$B2:$B4),"")),COUNTA($D8:$D14),1)
| People | Participation # | Time |
|---|---|---|
| Jack | 3 | 0:05:00 |
| Gil | 2 | 0:03:06 |
| Row | 3 | 0:05:00 |
| Pitt | 1 | 0:02:43 |
| Clive | 1 | 0:01:54 |
| Gareth | 1 | 0:01:54 |
| Jan | 1 | 0:00:23 |
Row is being counted 3 times, despite only participating once, because of the word "Arrow". Is there a modification I can make so that I only search for the specific word? Google Sheet for reference and thank you for your time!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试在E8中尝试：
Try in E8: