这适用于一个单元格-现在如何将其应用于范围? [英] This works for one cell - now how can I apply it to a range?

问题描述

我有以下公式，使用一个单元格引用可以很好地工作.

I have the following formula which works perfectly fine with one cell reference.

=SUMPRODUCT(--ISNUMBER(SEARCH(SPLIT($B$20," "),C7)))=COUNTA(SPLIT($B$20," "))

问题是我只是无法确定如何使它适用于单元格范围.事实很可能是我已经超出了GSheets的能力极限，但这就是...

The problem is I just cannot work out how to make this work for a cell range. The truth is more than likely that I've simply exceeded the limits of my abilities with GSheets, but here it is...

单元格B20包含两个或更多单词的短语.如何搜索单元格C3:C12并返回包含所有单词的单元格的计数? (注意:我可以做到的不是短语匹配，而是短语中的所有单词，但顺序不限.)

Cell B20 contains a phrase of two or more words. How can I search Cells C3:C12 and return a count of how many cells contain all the words? (Note: not as a phrase match, that I can do, it needs to be all the words from the phrase but in no particular order).

对于任何见解将不胜感激.非常感谢.

Would be grateful for any insights. Many thanks.

推荐答案

尝试一下

=SUM(ArrayFormula(--(MMULT(--iferror(regexmatch(split(C3:C12; " "); substitute(B20; " ";"|"))); TRANSPOSE(SPLIT(REPT("1_"; MAX(LEN(C3:C12)-LEN(SUBSTITUTE(C3:C12; " ";))+1)); "_")))>=COUNTA(SPLIT(B20; " ")))))

要使匹配不区分大小写，请尝试:

To make the match case-insensitive, try:

=SUM(ArrayFormula(--(MMULT(--iferror(regexmatch(split(C3:C12; " "); "(?i)"&substitute(B20; " ";"|"))); TRANSPOSE(SPLIT(REPT("1_"; MAX(LEN(C3:C12)-LEN(SUBSTITUTE(C3:C12; " ";))+1)); "_")))>=COUNTA(SPLIT(B20; " ")))))

注意:根据您的语言环境，您可能必须将半冒号更改为逗号.

Note: Depending on your locale, you may have to change the semi colons to commas.

这篇关于这适用于一个单元格-现在如何将其应用于范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！