随心所欲，函数大神写的一个公式，可以任意指定一些数字求和，赶紧收藏吧！

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

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

有朋友提出了这样一个有趣的问题：

这个模型还是具有一定的实际意义的，因此写出来分享给大家。

这个问题初看起来还是比较复杂的。要想办法将数字和位置信息对应起来。思考三秒钟，来一起揭开今天这个题目的神秘面纱吧！

01

在单元格B9中输入公式“=SUM(TRANSPOSE(OFFSET($A$7,,--TRANSPOSE(MID($A9,ROW($1:$3),1)))))”，三键回车并线下拖曳即可。

思路：

  • MID($A9,ROW($1:$3),1)部分，从字符串中提取前三个数字

  • --TRANSPOSE(MID($A9,ROW($1:$3),1))部分，将其转置并转换为数字型数字，其结果为{5,6,8}

  • 利用OFFSET函数，以单元格A7为基点，一次向右偏移5、6、8列

  • 再次利用TRANSPOSE转置进行降维处理。这里也可以使用N函数进行降维处理

  • 最后用SUM函数求和即可

单元格C9和单元格D9中的公式是类似的，只需要将ROW($1:$3)分别改成ROW($5:$7)和ROW($9:$11)就可以了。

但是，上面这个方法要写三次公式。公式并不是一次性完成的。本着方便的原则，我们来看看如何一次性公式完成该题目。

02

在单元格B9中输入公式“=SUMPRODUCT(MMULT(1-ISERROR(FIND(ROW($1:$10),MID(MID($A9,4*COLUMN(A:A)-3,3),COLUMN($A:$C),1))),ROW(1:3)^0)*TRANSPOSE($B$7:$K$7))”，三键回车并向下向右拖曳即可。

思路：

  • MID($A9,4*COLUMN(A:A)-3,3)部分，利用MID函数从单元格B9中的字符串依次提取1-3位、5-7位和9-11位的数字

  • MID(MID($A9,4*COLUMN(A:A)-3,3),COLUMN($A:$C),1)部分，对上一步提取出来的字符串从第一位开始，每一位依次都提取一个字符数字，构成一个内存数组{"5","6","8"}

  • 利用FIND函数在上述内存数组中查找自然数序列1-10，找不到就返回错误值

  • 1-ISERROR(FIND(ROW($1:$10),MID(MID($A9,4*COLUMN(A:A)-3,3),COLUMN($A:$C),1)))部分，将能找到的数字转换为“1”，错误值都转换为“0”

  • MMULT(1-ISERROR(FIND(ROW($1:$10),MID(MID($A9,4*COLUMN(A:A)-3,3),COLUMN($A:$C),1))),ROW(1:3)^0)部分，做矩阵相乘，结果为{0;0;0;0;1;1;0;1;0;0}，其中三个“1”所对应的位置分别是第五位、第六位和第八位

  • 将单元格区域$B$7:$K$7转置后利用SUMPRODUCT求乘积和

后面的就比较简单了，不再详细介绍了

-END-

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

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

我就知道你“在看”

注意！前方有红包挡道！速点阅读原文消灭之！

推荐阅读
赞 (0)

相关推荐