【原】矩阵乘积函数mmult进阶应用8：像if({1,0})一样置换2列数据

刘卓学EXCEL 2021-04-02

展开全文

小伙伴们，还好吗？今天来分享一下mmult的另一个用法，像if({1,0},..,..)一样置换两列数据的位置，置换后可以用vlookup实现逆向查询。还是来看2个例子。

1.置换2列数值，并用vlookup逆向查询。

我们知道mmult的2个参数都只能是数值，否则会出错，所以我们先从最简单的数值置换说起。如下图左表所示，有2列数值，A列是序号，B列是年龄。我们现在要把这2列的位置置换，结果如④所示，也就是由①变为④。然后再用vlookup根据年龄逆向查询出序号。

我们先来解决置换位置的问题，其实上图已经给出答案了。用mmult来置换，第1参数还是原来的2列数值；关键是第2参数，它是{0,1;1,0}的一个二维数组，也就是上图②的部分。然后用mmult将这2个参数矩阵相乘，得到的结果刚好是置换后的效果，如④所示。

其实，第2参数的结果最开始我也不知道，我是根据①和④倒推出②的结果，也就是根据mmult的第1参数和返回的结果倒推出第2参数是什么，也就是一种尝试和探索吧，结果还得到了答案。所以置换后的公式可以写为=MMULT(D3:E10,{0,1;1,0})，如下图所示。把第2参数用常量数组的方式写出来。

其实这个常量数组还可以用一个函数来产生，这个函数和mmult一样也是个矩阵函数，所以也会和mmult结合使用。它就是munit函数，这个函数返回指定维度的单位矩阵。语法结构为MUNIT(dimension)，只有1个参数，必须是大于0的整数。

munit返回的结果是数组。下面看几个演示。=munit(1)返回的结果如①所示；=munit(2)返回的结果如②所示；其他也是一样的。我们可以发现，这个函数返回一个正方形数组，当参数是1时，返回1行1列的正方形；当参数是2时返回2行2列的正方形；当参数是3时，返回3行3列的正方形。其中左上角到右下角的对角线上的值是1，其他值是0。

这个函数的基本原理了解了，那它和我们上面说的第2参数有什么关系呢？如下图所示，我们之前的第2参数如①所示，而=munit(2)返回的结果如②所示。看出它们的差别没有，对角线的位置不同。其实我们用1-munit(2)就得到了第2参数的结果。

所以，之前的常量数组{0,1;1,0}可以用1-munit(2)得到，那么置换后的公式就可以写为=MMULT(D3:E10,1-MUNIT(2))。2列数值置换后，就可以用vlookup逆向查询了，置换后的数组作为vlookup的第2参数。

根据年龄逆向查询序号的公式为=VLOOKUP(I3,MMULT(A3:B10,1-MUNIT(2)),2,)。mmult将2列数值置换后作为vlookup的第2参数，返回第2列，精确查找。

2.置换2列文本，并用vlookup逆向查询

再把题目加深一点，现在要置换2列文本。如下图所示，将左表置换为右表。mmult只能对数字进行运算，所以我们只能把文本变为数字，然后再用mmult置换。下图我把文本转为行号和列号结合的数字，目的是为了得到R1C1样式的文本型单元格地址，最后再用indirect返回单元格的内容。不知道大家还记不记得indirect+text的套路。

首先来看下mmult的第1参数ROW(15:23)/1%+COLUMN(A:B)，也就是将文本转为数字的部分，返回的结果如下图灰色部分所示，用行号乘以100再加上列号。第2参数还是1-munit(2)，也就是{0,1;1,0}。

所以，置换后的公式为=MMULT(ROW(15:23)/1%+COLUMN(A:B),1-MUNIT(2))，结果如下图绿色部分所示。接下来就要把这些数字转为文本型的单元格地址，比如1502就代表15行2列。

转为文本型单元格地址的公式为=TEXT(MMULT(ROW(15:23)/1%+COLUMN(A:B),1-MUNIT(2)),"r0c00")，结果如红色箭头所示。再下一步，用indirect返回对应的单元格内容。

输入公式=INDIRECT(TEXT(MMULT(ROW(15:23)/1%+COLUMN(A:B),1-MUNIT(2)),"r0c00"),)，发现结果是错误的。因为现在indirect形成了多维引用，所以要用t函数降维，正确的公式为=T(INDIRECT(TEXT(MMULT(ROW(15:23)/1%+COLUMN(A:B),1-MUNIT(2)),"r0c00"),))，如下第2图所示。这样就把2列文本置换了。