|
HI，大家好，我是星光。 Excel工作表函数有上百个，但要问最难的是哪个？对大部分人而言，绝对是我们今天分享这个——MMULT。  MMULT的绰号叫'妹妹'，但她一点也不温柔可亲。如果你是她的靖哥哥，时时刻刻就有暴揍她狗头的冲动。 …… 1，基本语法 妹妹的语法倒很简单： MMULT(array1, array2) 就俩参数，都是数字矩阵，返回结果是两个矩阵的相乘。 解释一下什么是矩阵。 简单来说，矩阵就是长方形的表格区域（注：小学老师说过正方形是长方形的一种特殊）。    ↑ 左上角是一个正方形 如上图所示，A4:C8是第一个矩阵，5行3列，D1:E3是第2个矩阵，3行2列。第2个矩阵的行数等于第1个矩阵的列数，都是3。 这样两个矩阵对角摆放一起后，左上角就必然形成一个正方形。 作为新手，如果你记不住第2个矩阵的行数必须等于第1个矩阵的列数，那就记住上面这条正方形规则也可以。 我们在D4单元格输入公式： =MMULT(A4:C8,D1:E3) 结果它返回了一个矩阵，该矩阵的大小是第1矩阵的行数和第2矩阵的列数。  ↑ 运算结果是一个矩阵 它的计算过程是第1个矩阵每一行和第2个矩阵每一列相乘再分别聚和。 D4单元格的值等于A4:C4*D1:D3，按照数组运算规则，也就是A4*D1+B4*D2+C4*D3，结果返回14. E4单元格的值等于A4:C4*E1:E3，也就是A4*E1+B4*E2+C4*E3，结果返回18. D5单元格的值等于A5:C5*E1:E3。 其它以此类推。 …… 有朋友说，这不就是线性代数吗？还有朋友说，这不就是numpy的张量点乘运算dot吗？还还有朋友说，星光，拜拜呐你，什么玩意，我去烫头，不学了。 后面这位道友请留步。 函数语法这家伙，了解一下就好了，懂不懂都在其次。自古深情留不住，只有套路得人心。接下来咱们分享几个常用的MMULT函数应用，你不妨看看日常工作能否用得上。  2，实际应用 话说回来，有这么一个让人掉头发的妹妹有啥好的呢？换句话说，MMULT在实际函数应用中到底有啥不可替代的作用？ 我先举一个简单的例子。
=SUM(A2:D2) 此外也可以在E2:E6区域输入一条MMULT函数获取全部结果： =MMULT(B2:D6,{1;1;1})  MMULT函数的第1参数是B2:D6，第2参数是一个1列3行的常量数组。按照矩阵相乘运算规则，最后一次性返回如上图所示E2:E6区域的结果。 这里使用MMULT函数并非最佳选择，但它说明了MMULT函数最重要的一个特性：它可以将一个多行多列的数据矩阵（引用或数组），聚合为一个单列或单行结构。就目前的函数体系而言，这特性还无人可代替。 举两个实战例子。 第1个例子是并且关系的乱序多关键字数据查询。 有一段数据，如下图所示，A列是信息，B列是人名。现在需要查询A列信息中同时包含三个关键字'围棋','二班','帅'的对应人名。小声说一下，正确的查询结果是'看见星光'——就怪不好意思的。  参考解法公式如下： =INDEX(B:B,MATCH(3,MMULT(ISNUMBER(SEARCH({'围棋','二班','帅'},A1:A5))+0,{1;1;1}),0)) 解释一下公式的运算过程： ISNUMBER(SEARCH({'围棋','二班','帅'},A1:A5))+0，这部分使用SEARCH函数查询3个关键字在A1:A5中是否存在，如果存在，返回一个序列值，否则返回错误值。然后使用ISNUMBER函数判断返回结果是否为数值，得到一个由逻辑值构成的内存数组，+0之后将逻辑值转换为数值1或0。 将这部分公式计算的结果映射到单元格区域，如下图所示，是一个5行3列的矩形。  然后借助MMULT函数，将这个矩阵横向聚合，也就是数据源每行包含关键字的个数，结果为一个5行1列的内存数组 我们假设3个关键字同时存在，于是使用MATCH函数在MMULT函数返回的单列内存数组中查询3的索引位置，再使用INDEX函数按图索骥即为结果。 …… 第2个例子还是模糊匹配查询。 这个例子我在之前的推文有分享过：《函数也可以这么炸裂，数据模糊匹配查询，看完这篇都不是事》
如下图所示，A:B列是数据源，需要根据D列的简称查询对应的考试成绩。比如D2透视表空空对应的结果是A2讲透视表的那个空空。 参考解法公式如下： =INDEX(B:B,MATCH(,MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26)),)) ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)) MID函数从D2单元格的第1(A)个位置至第26(A-Z)个位置分别截取1个字符；FIND函数判断MID函数的返回结果在A1:A10单元格中是否存在，如果存在，返回位置序号，否则，返回错误值，最后通过ISERR函数搭配减法运算，将FIND函数的结果转化为-1和0，构成一个26列10行的矩阵数组。 MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26)) MMULT函数对矩阵数据进行计算，合并为一个单列26行数组；当D2单元格字符串的每一个字符都在A$1:A$10区域某个单元格中存在时，对应行的计算结果为0。 最后通过MATCH函数，取得MMULT函数返回结果首次为0的位置，再通过INDEX函数取值即可。 总结而言，这个公式的思路是判断所查询的字符串中每一个字符是否都在查找范围内存在，就是玩一个文字归属计数游戏，其本质和我们上一个例题是一致的。 将这个公式稍加进化，就可以实现数据最大近似度模糊匹配，相关公式可以参考《函数也可以这么炸裂，数据模糊匹配查询，看完这篇都不是事》。 …… |
|
|
来自： hercules028 > 《excel》
