Excel公式检查重复的单词 [英] Excel formula to check for duplicate words

问题描述

我需要一个excel公式来检查一行中的任何列中是否都重复了任何单词。我的示例行在由'，'分隔的列之下。

I require an excel formula to check if any words are being duplicated in any column in a row. My sample row is below columns separated by a ','.

列号：E221，F221，G221，H221 <

Column No: E221, F221, G221, H221

列文本：> Sam，John / Sam / Smith，Smith，Kyle

Column Text: Sam, John/Sam/Smith, Smith, Kyle

Sam&史密斯名字正在重复，所以这些单词应该以红色突出显示。

Above Sam & Smith names are being repeated so the words should be highlighted in red.

Excel文件链接：
https://docs.google.com/spreadsheets/d/1D6PZWtbk_2IVEA4l1noFzKRFu0quXSTAewmL41xOnlo/edit?usp=sharing

Excel File Link: https://docs.google.com/spreadsheets/d/1D6PZWtbk_2IVEA4l1noFzKRFu0quXSTAewmL41xOnlo/edit?usp=sharing

推荐答案

我认为这样做（假设你在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)
)

这篇关于Excel公式检查重复的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！