湖南龙网

  1. 首页 > 生活百科 > >

excel公式教程：拆分连字符分隔的数字并放置在同一列中( 二 )

生活百科小知识


这样，上面的部分公式转换为：{“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
…… 依此类推 。

推荐阅读


上一篇：柠檬草防蚊吗 柠檬草的宜忌人群

下一篇：白附子有副作用 小心白附子的毒性