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

提取不重复数并从小到大组合,这个Excel操作秀到我了

 

作者:曹洪波来源:部落窝教育发布时间:2021-11-10 09:14:48点击:454

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


编按:

 

大家好啊,今天给大家分享一道趣味题,希望大家可以从中学到数据处理的思路与技巧,并且活学活用,最后能将这种Excel思维带到工作中去。

 

 

题目是这样的:

 

A列到G列里面的数据自动取尾数,然后去掉重复的尾数,按从小到大升序排列,最后合并到H列对应的单元格里。

 

 

题目读起来很简单,但这道题内涵却非常丰富,并且应用到多个函数及嵌套,是一道非常有意思的烧脑题目!

 

怎么样?小伙伴们有思路了吗?

 

 

在单元格H2中输入公式“=MID(SUM(IFERROR(SMALL(IF(MMULT(1-ISERROR(FIND(ROW($1:$14)-1,RIGHT(A2:G2))),ROW($1:$7)^0),ROW($1:$10)-1),ROW($1:$10))/10^ROW($1:$10),"")),3,10)”,三键回车并向下拖拽即可。

 

思路解析:

1.   既然要取尾数,位数包含0-910个数字,那么第一个想到的就是将这组数据的位数用RIGHT函数提取出来后,用FIND函数在源数据中查找0-910个数字。查不到则意味着该数字不包含在源数据中。因此,我们可以把公式写成“=FIND(ROW($1:$10)-1,RIGHT(A2:G2))”。这里ROW($1:$10)-1代表着0-910个数字。


2.   接下来用ISERROR函数来做一个判断,“=ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:G2)))”将错误值都转换为TRUE,数字都转换为FALSE。这一步为后面的计算做好了准备。


3.   =1-ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:G2)))”部分,用1减去步骤2,是将错误值都转换为0,数字都转换为1。因为只有真正查找到的数字才会对后面的计算有实际意义。


4.   下面该要MMULT函数出场了。“=MMULT(1-ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:G2))),ROW($1:$7)^0)”部分,如下图。它的结果表达的含义是有几个0、几个1、几个9在源数据中被查询到(图中黄色部分)。

 

 

5.   接下来利用IF函数对上述结果做一个判断。“=IF(MMULT(1-ISERROR(FIND(ROW($1:$14)-1,RIGHT(A2:G2))),ROW($1:$7)^0),ROW($1:$10)-1)”部分,如果为真(大于0的任何数字时),返回对应的0-9中的数字;为假时,返回错误值,其结果为{0;FALSE;2;FALSE;FALSE;5;6;7;8;9;FALSE;FALSE;FALSE;FALSE}


6.   =SMALL(IF(MMULT(1-ISERROR(FIND(ROW($1:$14)-1,RIGHT(A2:G2))),ROW($1:$7)^0),ROW($1:$10)-1),ROW($1:$10))”部分,利用SMALL函数依次取出上述部分从第1到第10的数字。其结果为{0;2;5;6;7;8;9;#NUM!;#NUM!;#NUM!}


7.   接下来这一步很关键。将上述结果分别除以10^ROW($1:$10),结果为{0;0.02;0.005;0.0006;0.00007;0.000008;0.0000009;#NUM!;#NUM!;#NUM!}。看看和步骤6中的结果有什么变化?


8.   利用IFERROR函数将错误值转换为空值。得出{0;0.02;0.005;0.0006;0.00007;0.000008;0.0000009;"";"";""}


9.   利用SUM函数求和,结果是0.0256789


10. 利用MID函数从第3位开始提取字符串,就是我们想要的结果。

 

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

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

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

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者曹洪波;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。