公式解读 第三期 -单元格中数字的去重排序!
本期涉及到函数较多,你可以一次性根据这个案例,学习多少很多常用 函数的用法,同时我也会讲解思路,这个很重要,思路决定了出路。
直接先看一下我们的效果图:
效果图
公式:=REPT(0,ISNUMBER(FIND(0,A2)))&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A2))*ROW($1:$9)*10^(10-ROW($1:$9))),0,)
庖丁解牛:
整体来看,公式分两个部分(FM表示FORMULA):
FM1:REPT(0,ISNUMBER(FIND(0,A2)))
FM2: SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A2))*ROW($1:$9)*10^(10-ROW($1:$9))),0,)
下面我们分别剖析:
第一部分:0怎么处理
公式:REPT(0,ISNUMBER(FIND(0,A2)))
功能:判断A2中是否含有0,如果有就显示0,否则显示为空
剖析: 1.1
示例:从截图中可以看出,10中含所有0,0在第二位,所以返回2;11中没有0,所以返回#VALUE!错误
FIND(0,A2)效果图
1.2、ISNUMBER:比较简单,如同其名,用于判断是否是数值。
在1.1中,我们判断的结果要不是数值,要不错误值,返回TRUE或者FALSE
ISNUMBER效果图
1.3、REPT:这是第一部分的重点,因为很少有人这么用。一般肯定想到IF函数。其实效果一样,但是简洁一些!其中有两点也是使用比较巧妙的
a、FALSE在特定情况表示0,TRUE表示1
b、REPT:重复函数,这里重复1次或者0次,重复0就显示空(重点)
如此我们便解决了是否包含0的问题。
REPT
第二部分:非0部分去重提取
第二部分相对复杂一些,不过也不用担心,我们一般遇到这种公式是从外向内解析的。但是给大家解释我们就得从内开始,否则你不知道内部,很难看懂。
公式:
SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A2))*ROW($1:$9)*10^(10-ROW($1:$9))),0,)
2.1、ISNUMBER(FIND(ROW($1:$9),A2)):之所以直接整体拿出来讲,是因为和我们第一部分分类似,只是这次查询的不是一个0,而是查询1-9,看看是否在其中出现过,如果出现返回其对应的位置,否则错误,通过ISNUMBER知道是否存在。
示例:通过按下F9,我们可以看到显示的对应的结果{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
只有起三个是TRUE,说明只包含1-3,其他无
ISNUMBER(FIND(ROW($1:$9),A2)) 转化后的结果图
2.2、我们我们把2.1基础上再乘以1-9,一一对应相乘,那么TRUE就是返回对应的数值,否则返回0公式:ISNUMBER(FIND(ROW($1:$9),A2))*ROW($1:$9)结果:{1;2;3;0;0;0;0;0;0}
现在是一个常量数组,我们要向他们可以123这种,只需要把他们扩大10对次方,然后相加即可比如{1000,200,30} 相加就是1230
2.3、10^(10-ROW($1:$9)):此部分就是我们上面说的10的次方
这里相当于:10^({9;8;7;6;5;4;3;2;1})
如果还看不明白我们再展开就是:{10^9,10^8,10^7,10^6,10^5,10^4,10^310^2,10^1}
也就是:{1000000000;100000000;10000000;1000000;100000;10000;1000;100;10}
在2.2中我们得到了,如果存在1-9的中的数就是线上对应的数值,否则就返回0,
那么二者相乘结果:ISNUMBER(FIND(ROW($1:$9),A2))*ROW($1:$9)*10^(10-ROW($1:$9)){1000000000;200000000;30000000;0;0;0;0;0;0}
这里更多的是数理逻辑要清晰。
2.4SUM(2.3的结果):就是把{}中的每个数值相加,可以得到1230000000
最后我们把0替换掉即可
2.5、SUBSTITUTE(2.4的结果,0,""),SUBSTITUTE函数提到替换作用,把0替换成空。
再测试一下其他数据:
如果有0:
OK!本期公式庖丁解牛就到这里!小编已尽力解析,如还有什么地方不是很透彻的老铁,欢迎留言交流!知无不言,言无不尽!
这里是EXCEL办公实战,欢迎关注我们!
如果觉得《c# 获取excel单元格公式结果_EXCEL公式解析:单元格中数字去重排序思路及公式详解...》对你有帮助,请点赞、收藏,并留下你的观点哦!