如何将条件格式公式转换为单元格范围的基于单元格的公式? [英] How to convert conditional formatting formula to cell-based formula for cell range?

问题描述

这是我的下一个挑战，它与上一个挑战有关(在此处找到:这适用于一个单元格-现在我该如何申请到一定范围?).

Here's my next challenge, and it's related to the previous one (found here: This works for one cell - now how can I apply it to a range?).

我最终得到了一个令人讨厌的条件格式公式，它似乎可以工作(也许是靠运气)……

I've ended up with a godawful ugly formula for conditional formatting, and somehow (perhaps by dumb luck) it seems to work...

=OR(ARRAYFORMULA(IF(ISNUMBER(SEARCH($B$18,D7)),SIGN(SEARCH($B$18,D7)),IF(ISNUMBER(SEARCH(SPLIT($B$19,","),D7)),SEARCH(SPLIT($B$19,","),D7)))))

对于任何单个目标单元格(在此示例中为D7)，它返回true，并检查它是否包含B18中的字符串或B19中两个或多个字符串值之一(用逗号分隔).

It returns true for any single target cell (D7 in this example), checking whether it contains either the string in B18 or one of two or more string values, separated by commas, in B19.

与前面的情况一样，我不知道如何将其转换为公式(数组公式?)，该公式可以应用于范围(D3:D12)并计算满足条件的单元格数.

As with the previous scenario, I can't work out how to turn this into a formula (array formula?) which I can apply to a range (D3:D12) and count how many cells meet the criteria.

也许更好的问题是，优先于上面引用的我的科学怪人的科学怪人公式来解决这个问题的正确方法是什么！

Or maybe the better question is, what would be the correct way to tackle this in preference to my Frankenstein's Monster of a kludged-up formula quoted above!

任何人和所有见解都表示赞赏:)

Any and all insights appreciated :)

推荐答案

假定B19中的值用逗号分隔，然后用空格分隔，请尝试:

Assuming the values in B19 are separated by a comma, followed by a space, try:

=sum(ArrayFormula(--(REGEXMATCH(D3:D12, B18&"|"&SUBSTITUTE(B19, ", ", "|")))))

如果逗号后没有空格，请使用，"而不是，". 如果您希望匹配不区分大小写，请尝试:

If there is no space after the comma use "," instead of ", ". If you want the match to be case-insensitive, try:

=sum(ArrayFormula(--(REGEXMATCH(D3:D12, "(?i)"&B18&"|"&SUBSTITUTE(B19, ", ", "|")))))

看看是否可行?

这篇关于如何将条件格式公式转换为单元格范围的基于单元格的公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！