【原】谁说不同模块函数不能融会贯通？财务函数都跨界搞排序了！

EXCEL应用之家 2022-06-14 发布于上海

展开全文

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，今天要和大家分享一则非常有意思的题目。通过这道题目，我们可以很好地掌握MMULT函数的综合运用，以及对函数公式的综合理解。

话不多说，请看题目！如下图，要求提取8列数据的尾数，去重后依次从小到大排列。

看到这样的题目后，小伙伴们有思路吗？反正我是考虑了一段时间才慢慢摸索出来的，大体思路如下：

首先要提取尾数

然后去除重复值。这样的取出操作最适合用MMULT函数来完成

从小到大排列

好了，让我们一起来看看该如何书写公式吧！

MATCH函数配合TEXT函数就可以返回正确答案。

在单元格J2中输入公式“=RIGHT(TEXT(SUM(SMALL(IFERROR((MATCH(ROW($1:$10)-1,RIGHT(A2:H2,1)*1,0)^0)*(ROW($1:$10)-1),0),ROW($1:$10))*10^(10-ROW($1:$10))),"0000000000"),COUNT(MATCH(ROW($1:$10)-1,RIGHT(A2:H2,1)*1,0)))”，三件回车并向下拖曳即可。

这个公式看起来挺长的，我们就简单介绍一下思路吧。

MATCH(ROW($1:$10)-1,RIGHT(A2:H2,1)*1,0)部分，对0-9这几个数字查找在内存数组RIGHT(A2:H2,1)*1中的位置
MATCH(ROW($1:$10)-1,RIGHT(A2:H2,1)*1,0)^0部分将数值都转换为1
(MATCH(ROW($1:$10)-1,RIGHT(A2:H2,1)*1,0)^0)*(ROW($1:$10)-1)部分，返回1所对应的的数字
IFERROR函数将错误值转换为0
*10^(10-ROW($1:$10))部分将上述内存数组中的数字对应乘以10的10-ROW($1:$10)幂次方
下面的SMALL函数和SUM函数分别将上面生成的内存数组中的数字从小到大排列，并求和
TEXT函数将和值转变为"0000000000"结构
COUNT(MATCH(ROW($1:$10)-1,RIGHT(A2:H2,1)*1,0))部分计算字符串的长度
RIGHT函数提取数字

这个公式是比较复杂的，我更喜欢下面第三个公式。

这个公式的逻辑思路和上面的一个大同小异，这里不再详细介绍了。

这个公式使用的方式是比较常用的方法之一。

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

这个公式的逻辑思路我们在之前有过多次介绍了，这里就不再赘述了。如有任何不清楚的地方，朋友们可以查阅往期推文或者私信我哦！

最后这一个公式则是本期推文要重点向大家介绍的一个方法。

在单元格J2中输入公式“=MID(NPV(9,IF(MMULT((1-ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:H2)))),ROW($1:$8)^0),ROW($1:$10)-1)),3,100)”，三键回车并向下拖曳即可。

思路：

FIND(ROW($1:$10)-1,RIGHT(A2:H2))部分，在由各尾数组成的内存数组中查找0-9这10个数字
ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:H2)))部分，将查找到后返回的位置数值转换为FALSE，错误值转换为TRUE
(1-ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:H2))))部分，将FALSE转换为TRUE，将TRUE转换为FALSE。这一步的目的是将所有能查找到的数值所对应的位置数值都转换为TRUE
MMULT((1-ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:H2)))),ROW($1:$8)^0)部分，利用MMULT函数计算出能查找到的数字的个数和
IF(MMULT((1-ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:H2)))),ROW($1:$8)^0),ROW($1:$10)-1)部分，返回对应的数字
NPV(9,IF(MMULT((1-ISERROR(FIND(ROW($1:$10)-1,RIGHT(A2:H2)))),ROW($1:$8)^0),ROW($1:$10)-1))部分，是本题的精华所在
NPV函数的特性是，在本题中将第二个参数（我们内存数组中的第一个数字）除以10，将第三个参数（我们内存数组中的第二个数字）除以100，以此类推，最后求和
最后由MID函数来提取最终的正确答案

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”