昨天看见一位吧友问了个问题,有几千条的数据,一列数据有银行名称、银行卡号、银行姓名,只需要需要标记出重复名字,看到这个问题,大家想到的就是条件格式标记重复值,可问题是他需要的是标记姓名的重复值,而银行名称大部分都是相互重复的,不需要标记。如图所示,数据是我自己为演示而做,非真实的数据。 这个问题也不难,就是在条件格式里添加判断条件。 我们先来分享下这列数据: 银行名称单元格内不管是哪家银行,都有建行两个字。 银行卡号单元格卡号都是数字(或许是文本格式,但肯定都是数字组合)。 姓名可能2个字,3个字,4个字都有可能。 故要判断姓名是不是重复值,我们需要运用三个条件,公式如下: 第一个条件使用函数FIND从B1单元格开始查找单元格是否包含银行两个字,找到银行两个字返回所在的位置,如B2单元格是卡号找不到银行两个字返回错误,再用COUNT函数计算出找不到银行两个字的单元格,这就踢出去了银行名称这一行。 第二个条件使用ISNUMBER函数从B1单元格内是否是数字,只有卡号行会返回TRUE,其它两行则返回FALSE,剔除了银行卡号这一行。 第三个条件使用COUNTIF函数从B1单元格依次检测在这一列的个数是否大于1,大于1表示有多个。 最前面添加AND函数连接这三个条件,同时满足这三个条件的,就是姓名重复的。 使用这个公式的方法有两种: 方法一:添加辅助列筛选 在表格的右侧添加一列辅助列,整列添加上公式,返回的结果是TRUE或FASLE, TRUE就表示找到重复的姓名,FALSE表示是唯一值。 全选表格,添加筛选标记,筛选出TRUE。 标记上填充色,或直接查看重复的姓名都可以。 方法二:条件格式 选中B列,点击菜单栏上“开始-条件格式”,下拉选择“新建规则”。 弹出窗口,选择规则类型“使用公式确定要设置格式的单元格”,在输入框里复制公式“=AND(COUNT(FIND("银行",B2))=0,ISNUMBER(B2*1)=FALSE,COUNTIF(B:B,B2)>1)”,点击格式,设置填充色。 点击确定后,B列中姓名重复的就填充上了颜色。 怎么样,以上方法你都学会了吗? |
|
来自: 和我学软件技巧 > 《EXCEL操作技巧》