如何调整正则表达式修复Google表格拼接字符串的匹配高亮问题

问题场景 #

你的Google表格数据如下：

需求是：在输入框（比如固定单元格$B$27）输入类似R,B的内容后，只要列A/列B的单元格包含输入的全部字母（重复字母忽略，比如B,B,R等价于B,R，单元格有额外字母也符合条件），就把该单元格标绿。

原公式的问题 #

你当前用的公式：

=REGEXMATCH(Join("",Unique((SORT(Transpose(Split(B2, ",")))))), "^" & Join("",Unique((SORT(Transpose(Split($B$27, ","))))))

之所以会失败，是因为排序后拼接的字符串顺序不匹配。比如单元格内容G,B,R排序后是BGR，输入R,B排序后是BR，正则用^BR去匹配BGR自然匹配不上，导致本该符合条件的单元格没被标绿。

解决方案：换思路验证每个字符 #

不用正则匹配整个字符串，改成检查输入的每一个唯一字符都存在于当前单元格的唯一字符集合里，用下面的公式作为条件格式规则：

=AND(ARRAYFORMULA(ISNUMBER(MATCH(UNIQUE(TRANSPOSE(SPLIT($B$27, ","))), UNIQUE(TRANSPOSE(SPLIT(B2, ","))), 0))))

公式拆解

1. SPLIT(B2, ",")：把单元格内容按逗号拆成数组
2. TRANSPOSE(...)：把横向数组转成纵向，方便去重
3. UNIQUE(...)：去掉重复字符，得到单元格的唯一字符列表
4. 对输入框$B$27做同样操作，得到输入的唯一字符列表
5. MATCH(输入字符, 单元格字符列表, 0)：检查每个输入字符是否在单元格里，存在返回位置，不存在返回错误
6. ISNUMBER(...)：把MATCH结果转成布尔值，存在就是TRUE，否则FALSE
7. AND(ARRAYFORMULA(...))：确保所有输入字符都存在（所有结果都是TRUE）

条件格式设置步骤 #

1. 选中要设置格式的单元格范围（比如A2:B3）
2. 点菜单栏「格式」→「条件格式」
3. 右侧面板选「自定义公式」作为规则类型
4. 粘贴上面的公式，设置填充色为绿色
5. 点「完成」就搞定了

测试效果 #

比如输入框$B$27输入R,B：

• A2（G,B,R）包含B和R，标绿
• B2（W,R,R）只有R没有B，不标绿
• A3（G,B,B）只有B没有R，不标绿
• B3（R,B,R）包含B和R，标绿

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