二维码 购物车
部落窝在线教育欢迎您!

从数据列中提取连续数的起始和终止值的最简便方法

 

作者:老菜鸟来源:部落窝教育发布时间:2020-05-25 15:34:52点击:384

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!怎么从一列数据中找出连续数并提取连续数的起始值和终止值?今天给大家分享一个最简单的、通过辅助列来解决这个问题的方法。我们将建立两个辅助列找到各段连续数的起始和终止值,然后用常用函数indexmatch把它们分列显示出来。赶紧来看看吧!

 

近日在答疑群看到一个整理编号的问题,觉得非常经典,特分享给大家。

 

问题如下图所示:

 

 

数据源为A列,为了方便后面介绍,这里虚拟了23条数据,实际上可能是成千上万条数据。现在的问题是需要将这一列卡号按照连续性整理成号段的形式,也就是C列和D列的结果,为了便于大家看明白,特意标注了不同的颜色。

 

问题明白了就该想想解决办法,这个问题似乎很有规律,但是又有点无从下手的感觉,于是各种声音都出现了,有说用VBA的,有说用PQ的,但是今天老菜鸟要和大家分享的办法就是用函数公式,而且是几个非常基础的函数,只不过还用了两个辅助列,下面就来分享解决思路。

 

要解决这个问题需要做两件事,第一是确定分段节点,也就是找到号码不连续的位置。第二就是分组,把连续的号码视为一组。当解决了这两个问题之后,最后的结果就非常容易得到了。辅助列1解决分段节点的问题,公式为:

 

=IFERROR(IF(RIGHT(A2,4)-RIGHT(A1,4)<>1,ROW(),),1)

 

 

因为卡号是包含了字母的字符串,要判断是否连续只能通过右边的几位数字来确定,也就是RIGHT(A2,4)-RIGHT(A1,4)

 

 

结果为1时,说明是连续的,为了让结果更加直观,需要加个IF函数处理一下,于是就有了IF(RIGHT(A2,4)-RIGHT(A1,4)<>1,ROW(),)

 

 

注意到D2中错误值,是因为数据源中的第一行是文字而造成的,要处理这个问题很简单,再加一个IFERROR就可以了,将错误值修正为1,这就是辅助列1的完整思路。

 

接下来要处理的就是分组问题,有了辅助列1,这个问题就很容易解决,公式为:

 

=COUNTIF($B$1:B2,">0")

 

 

COUNTIF也是比较常用的一个函数了,这个地方就是实现对大于零的数字进行计数,从而达到分组的效果。

 

有了这两个辅助列,最后再来介绍起始编号和终止编号是如何得到的。

 

起始编号的确定其实就是一个反向查找的问题,第一个起始编号就是辅助列2中数字1所对应的编号,第二个起始编号就是辅助列2中数字2所对应的编号,以此类推。

 

明白了这一点之后,起始编号的公式就会有很多种写法,老菜鸟使用了INDEXMATCH这对黄金搭档组合,公式为:

 

=INDEX(A:A,MATCH(ROW(A1),C:C,))

 

 

终止编号的公式与起始编号的公式仅有一字之差,公式为:

 

=INDEX(A:A,MATCH(ROW(A1),C:C,1))

 

 

区别就在于MATCH函数的第三参数,在计算起始编号的公式中省略了第三参数,表示0,含义为精确查找,而在计算终止编号的公式中第三参数为1,含义为升序的模糊查找。关于这一点,在之前讲解的MATCH函数教程中都有介绍,这里只简单说一下区别,如果查找值存在多个时,精确查找返回第一个符合条件的结果,当数据按升序排列时,模糊查找返回最后一个符合条件的结果,在本例中,查找值是123这些数字,第一个符合条件的结果就是起始值,而最后一个符合条件的结果就是终止值。

 

结束语:实际工作中遇到的很多问题,如果要一步到位的话往往会很难,但是如果能够发现解决问题的关键步骤,并通过辅助列这个手段去拆解问题的话,要比追求一步到位的解法更有效率。

 

本文配套的练习课件请加入QQ群:1043683754下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel中的编号问题《TEXT和SUMPRODUCT强强联合,只为解决一个“微不足道”的编号问题??

Countifs函数解析同样是countifs函数,为什么同事却使得比你好?原因在这里!

MATCH函数解析《MATCH:函数哲学家,找巨人做伴。新出道必学!

INDEX函数解析《INDEX:函数中的精确制导导弹,最强大的瘸子