Excel中如何判定1/10和10/1是相同数据 管道段重复判定 数组公式 图文

2019年7月13日15:46:08 评论 373 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

Excel数组公式应用 判定A/B和B/A是相同数据 管道段重复判定

问题提出

昨天在Excel880的q群里有朋友问了这么一个问题,如下图所示,它的数据是管道段按 前/后 这样的结构标记的数据,这里面1/2和2/1实际上是同一个管道段,也就是说前后相同不管顺序,各位函数高手可以尝试一下写一个公式的大下图中的结果

问题解析:

1、借助辅助列重组数据后再做标记判断

这个前后都是数字,那么其实有比较简单的做法,就是按/分列后 2个数字取出最小值 / 最大值 这样就把所有数据都转换成了 统一的顺序 然后直接match或者countif函数判断即可

这里统一用函数解决 按/分列公式如下,利用find函数找出斜杠位置 然后用left和mid函数分别提取前后值 用--转为数值 然后D列用min和max按从小到大的顺序重组数据

=--LEFT(A2,FIND("/",A2)-1)

=--MID(A2,FIND("/",A2)+1,100)

=MIN(B2:C2)&"/"&MAX(B2:C2)

最后利用match函数检查当前行是否第一次出现

当然你可以考虑把上面辅助列写成数组公式,只是那样比较长我就不演示了

难度提升

上面的案例,因为都是数值所以我们投机用了min和max重组数据,但是如果数字换成了字母或者字符串,这个方法就完全不管用了,如下图所示

这可怎么办呢?

这里介绍一个比较通用的 前后值正反相同判定的方法

1、把A/B与自己连接变为 A/B/A/B

2、注意上面这样处理后,B/A如果存在 就一定会出现在上面的字符串中间,类似于做了一个环形结构,就能够包含他的反向数据,只要判定是否包含在 A/B/A/B 里就可以判定2个管道段是否一样

具体实现详见下图

=IF(MATCH("*"&A2&"*",B:B,0)=ROW(),"首次","重复了")

上面公式里用了一个match函数的通配符技巧,这里可以找到第一个包含了A2也就是A/B的行号,当他第一次出现的行号与本行行号相同说明是首次出现,如果不相同,那就是在之前出现过了,也就是重复了

好现在把这个公式写成数组公式,注意输入后要按下CTRL+shift+enter三键,然后往下填充公式即可,$A$2:$A$20&"/"&$A$2:$A$20这个地方相当于就是把刚才辅助列B列搬到了数组公式里,就不需要再用辅助列 ,直接一个公式得到结果

=IF(MATCH("*"&A2&"*",$A$2:$A$20&"/"&$A$2:$A$20,0)=ROW(A1),"首次","重复了")

鸣谢:如果觉得文章对你有帮助,记得关注点赞转发和评论哦!

另外本号每天晚上8:30到10点30在头条进行Excel直播教学,免费收看,欢迎各位捧场!

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin

发表评论

您必须才能发表评论!