我认为这样做（假设你在B1：F1中有数据）： -

I think this does the job (assuming you have your data in B1:F1):-

=if(b1="",0,
SUM(1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6})),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{2;3;4;5;6;7}))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6}))+1),"/"&$A$1:A$1&"/"))))
+SUM(1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6})),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{2;3;4;5;6;7}))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6}))+1),"/"&C$1:$G$1&"/"))))
)

它提取每个单元格中的每个名称，并尝试将其与所有其他单元格匹配，但因为它使用数组常量，直接在条件格式中使用它：您必须将其放在（说）B2：F2中，并将其基于条件格式，否则使用更长的公式。

It extracts each name in each cell and tries to match it with all other cells but because it uses array constants you can't use it directly in conditional formatting: you would have to put this in (say) B2:F2 and base your conditional formatting on it, or else use an even longer formula.

这是一个数组公式，所以需要输入 Ctrl Shift 输入

It's an array formula, so need to enter it with Ctrl Shift Enter

这是一个改进配方。以前，我正在将单元格与当前单元格的左侧和右侧匹配：此单元格匹配所有单元格，包括当前单元格，并减去当前单元格与其自身匹配的匹配数量： -

Here is an improved formula. Previously I was matching cells to the left and right of the current cell: this one matches all cells including the current cell and subtracts the number of matches where the current cell matches with itself:-

=if(b1="",0,
SUM(1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6})),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{2;3;4;5;6;7}))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6}))+1),"/"&$B$1:$F$1&"/"))))
-(LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))+1)
)

如果你想要一个可以直接使用条件格式化的公式，我想你必须枚举所有可能的所有可能的子串的可能的匹配，这是非常乏味的： -

If you wanted a formula that you could use directly in conditional formatting, I think you would have to enumerate all possible matches for all possible substrings separately which is rather tedious:-

=if(b1="",0,
SUM(
1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",1)),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",2))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",1))+1),"/"&$B$1:$F$1&"/"))),
1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",2)),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",3))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",2))+1),"/"&$B$1:$F$1&"/"))),
1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",3)),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",4))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",3))+1),"/"&$B$1:$F$1&"/"))),
1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",4)),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",5))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",4))+1),"/"&$B$1:$F$1&"/"))),
1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",5)),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",6))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",5))+1),"/"&$B$1:$F$1&"/"))),
1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",6)),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",7))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",6))+1),"/"&$B$1:$F$1&"/"))))
-(LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))+1)
)