excel公式教程:拆分连字符分隔的数字并放置在同一列中
发布时间:2023-05-29 06:26:42
在单元格区域A1:A6中,有一些数据,有的是单独的数字,有的是由连字符分隔的一组数字,例如13-16表示13、14、15、16,现在需要将这些数据拆分并依次放置在列D中,如下图1所示。
图1
先不看答案,自已动手试一试。
公式
在单元格D1中输入数组公式:
=IF(ROWS($D$1:$D1)>SUM(last-first+1),””,SMALL(IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)),ROWS($D$1:$D1)))
向下拖拉至出现空单元格为止。
公式解析
公式中的first和last是定义的两个名称。
名称:first
引用位置:=LEFT(SUBSTITUTE($A$1:$A$6,”-“,REPT(“”,5)),5)
名称:last
引用位置:=RIGHT(SUBSTITUTE($A$1:$A$6,”-“,REPT(“”,5)),5)
我们来看看这两个名称是怎样运转的。第一个名称:first=LEFT(SUBSTITUTE($A$1:$A$6,”-“,REPT(“”,5)),5)
转换为:=LEFT(SUBSTITUTE({“1-2”;”4-6”;”9”;”10-11”;”13-16”;”21”},”-“,REPT(“”,5)),5)
转换为:=LEFT(SUBSTITUTE({“1-2”;”4-6”;”9”;”10-11”;”13-16”;”21”},”-“,“ ” ),5)
转换为:=LEFT({“1 2”;”4 6”;”9”;”10 11”;”13 16”;”21”},5)
得到的结果为:={“1 ”;”4 ”;”9”;”10 ”;”13 ”;”21”}
上面公式中的数字5是任意选的,只要能保证将数字筛选出来即可。
对于第二个名称:last。与上面的原理相同,最后得到的结果为:={“ 2”;” 6”;”9”;” 11”;” 16”;”21”}
再来看公式中IF语句的第一部分:IF(ROWS($D$1:$D1)>SUM(last-first+1),””
使用定义的名称替换公式相对应的名称位置:IF(ROWS($D$1:$D1)>SUM({“ 2”;” 6”;”9”;” 11”;” 16”;”21”}-{“1 ”;”4 ”;”9”;”10 ”;”13 ”;”21”}+1),””
得到:IF(ROWS($D$1:$D1)>SUM({2;3;1;2;4;1}),””
注意,这里没有必要对两个数组使用TRIM函数,Excel在进行数学减法运算时忽略数字前后的空格并强制转换成数学运算。
这样,我们可以看到上面的结果数组中对应于单元格A1:A6中每个数据要返回的数字个数,例如“1-2”将返回2个值、“4-6”将返回3个值,依此类推。因此,该数组的和就是我们想要返回的数字的总数:
IF(ROWS($D$1:$D1)>13,””
所以,向下复制公式时,超过13行将返回空值。
下面看看公式中的主要部分:
SMALL(IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)),ROWS($D$1:$D1))
可以看到,下面的部分出现了2次:first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)
其中,last-first在前面已经讲过,生成数组:{1;2;0;1;3;0}
其最大值是3,然后加上1得到4,即:MAX(last-first)+1
的结果是4。实际上,这个值代表我们从A1:A6的各字符串中范围最大的字符串返回的数字数量。
这样,就将上面的部分公式转换为:first+TRANSPOSE(ROW(INDIRECT(“1:”&4))-1)
转换为:first+TRANSPOSE({1;2;3;4}-1)
得到:first+{0,1,2,3}
这里是公式的关键技巧所在:首先生成一个单列数组,该数组由0至3(即数值范围的最大间隔)组成,然后将其转置为单行数组{0,1,2,3}。接着,将该数组与first生成的数组({“1 ”;”4 ”;”9”;”10 ”;”13 ”;”21”})相加。因为这两个相加的数组正交,一个6行1列的数组加上一个1行4列的数组,结果是一个6行4列的数组,有24个值。
这样,上面的部分公式转换为:{“1 ”;”4 ”;”9”;”10 ”;”13 ”;”21”}+{0,1,2,3}
结果为:{1,2,3,4;4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24}
这个数组包含我们想要的数值,但是也包含一些我们不想要的值。其实,之所以生成4列数组,是为了确保能够添加足够数量的整数,因为A1:A6中最大的间隔范围就是4个整数。
要去除不需要的数值,只需将上面数组中的每个值与last生成的数组相比较,(last数组生成的值为A1:A6中每个数值范围的上限)。例如对于上面数组中的第4行{10,11,12,13},在last数组中对应的值是11,因此剔除12和13,只保留10和11。公式中的比较部分为:
IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””
转换为:IF({1,2,3,4;4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24}>{2;6;9;11;16;21},””
Excel对公式中生成的两个数组在相同行中进行比较,例如,左边数组第2行的值{4,5,6,7}与右边数组第2行的值6进行比较、左边数组第5行的值{13,14,15,16}与右边数组第5行的值16进行比较,依此类推。得到的结果为:
IF({FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE;FALSE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE},””
由上述的推导可知,公式中的IF语句:
IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1))
可变为:
IF({FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE;FALSE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE},””,{1,2,3,4;4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24})
转换为:{1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}
此外,公式中的:ROWS($D$1:$D1)
随着向下复制,得到1、2、3…等数字。
综上,在单元格D1中原来的公式:
=IF(ROWS($D$1:$D1)>SUM(last-first+1),””,SMALL(IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)),ROWS($D$1:$D1)))
转换为:=IF(1>13,””,SMALL({1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}, 1))
结果是:1
对于单元格D2中的公式转换为:=IF(2>13,””,SMALL({1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}, 2))
结果是:2
对于单元格D3中的公式转换为:=IF(3>13,””,SMALL({1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}, 3))
结果是:4
…… 依此类推。
猜你喜欢
- Excel怎么快速删除工作表中空白行1、启动Excel 2013并打开工作表,这张工作表中存在一些空行。在工作表中选择数据区域,在“开始”选
- 再一般情况下,Word文档是不显示的网格线的,但有时候因个别特殊情况下需显示网格线,那么怎样显示呢?请大家按下面的步骤操作Word中,标尺包
- 福昕PDF阅读器怎么添加电子图章?福昕PDF阅读器想要给pdf文件添加图章,该怎么添加呢?下面我们就来看看详细的教程,很简单,需要的朋友可以
- 相信小伙伴们都知道,在Word文档中我们可以对大量文字内容进行各种专业化的编辑和处理,比如设置字体字号,设置字体颜色,下划线,上标下标,大写
- excel设置显示第几页的方法:设置显示页数步骤1:菜单档点击,插入-页眉和页脚,效果如图; 设置显示页数步骤2
- 本教程为Excel入门教程,详细介绍一般excel表格的制作过程。制作表格前需要你在心里先构思下表格的大致布局和样式,以便实际操作的顺利完成
- 第一,word默认显示比例Word默认显示比例为100%,如果想以后启动打开word,将word显示比例改为150%,该如何操作呢?新建一个
- WORD中的注释应该指所加的”批注“和”增删“的内容。以word2007为例,说明去掉的方法:1、删除批注:依次点击”审阅“、”删除“、”删
- 有时候在03版的word中需要选择,又不能直接点击打勾,这就需要用到03版里面的一些工具,下面小编就为大家介绍在word2003文档中怎么添
- Word2019怎么更改文件图标?wps2019文件的图标样式想要更换一个,该怎么更换呢?下面我们就来看看word2019图标更换方法,需要
- word作为办公软件使我们经常用到的软件之一,那么word2007怎么使用查找替换功能?下面小编就为大家详细介绍一下,不会的朋友可以参考本文
- Win10电脑磁盘分区卷标丢失导致无法F10恢复出厂设置怎么办?我们在使用电脑的时候电脑操作系统经常会需要升级系统,而我们将系统升级有时候会
- 本文介绍excel中根据A列公司名称找出重复的记录,并按照C列时间保留最近的一条记录的方法,希望对大家有所帮助。A列公司名称,B列公司地址,
- 1、首先我们自然是要将插入的图片设置为统一的高度和宽度,调整为同样大小。 2、按ctrl+g组合键将图片全选,接着
- 用户在使用Word2003编辑文档的过程中,复制文本是最常用的操作之一。通过复制文本可以快速得到特定部分文本的多份,从而提供了工作效率。在W
- Word允许您将任何符号粘贴到文档中,只要您知道适当的秘密即可。您可以使用“插入”选项卡的“符号”按钮,或者对于许多常用符号,可以使用键盘快
- 我们在学习使用Excel的时候,除了说需要学会掌握各类的函数,要想更加高效快速的处理好我们的日常工作,还有一点就是你需要熟练掌握各种快捷操作
- Word 2007无法输入中文怎么解决呢?很多小伙伴对此还不是很清楚,小编这里就给大家带来有关Word 2007无法输入中文怎么办的回答,希
- Word虽然看似简单,里面其实大有学问。比如如何删除空白页,比如将Word转换成PDF等等,不太熟悉Word的朋友们注意了!今天小编要为大家
- 有些朋友或许不知道word中标题文字上面部分显示不全怎么办,那么今天小编就讲解word中标题文字上面部分显示不全的处理方法,一起来了解一下吧