Mmult经典用法讨论.xls

1、Mmult函数用法讨论 引言： sumif/subtotal/countif 这三个函数可用于条件求和、条件计数等功能， 但由于这三个函数的第一参数均要求为单元格区域的引用，因此在一些 复杂条件下，这些函数的使用受到了限制。在这些情况下，运用mmult函 数进行矩阵变换也可以达到类似的运算目的，在一定程度上起到替代以上 这几个函数的作用。 注：用线框包围的单元格区域表示使用了多单元格的区域数组公式。 在论坛中查看此帖 Mmult函数用于数组的条件求和 (替代sumif函数) Mmult函数用于数组的逐级累加 (替代subtotal函数) Mmult函数用于数组的条件计数 (替代countif函

2、数) Mmult函数用于数组的数据比较 (替代countif函数等) Mmult函数用法讨论 sumif/subtotal/countif 这三个函数可用于条件求和、条件计数等功能， 但由于这三个函数的第一参数均要求为单元格区域的引用，因此在一些 复杂条件下，这些函数的使用受到了限制。在这些情况下，运用mmult函 数进行矩阵变换也可以达到类似的运算目的，在一定程度上起到替代以上 包围的单元格区域表示使用了多单元格的区域数组公式。 Mmult函数用于数组的条件求和 (替代sumif函数) Mmult函数用于数组的逐级累加 (替代subtotal函数) Mmult函数用于数组的条件计数 (替代c

3、ountif函数) Mmult函数用于数组的数据比较 (替代countif函数等) 返回目录 示例之一8247863293340 5655609432297 121405347153 2871657817259 2242495257222 3599182095267 2789656142284 691046787237 4454902100290 2663111336149 401531488472606 Sumif401531488472606 Subtotal401531488472606 Mmult401531488472606 对于直接引用单元格区域的情况，使用sumif函数和subt

4、otal函数均可以比较方便地得到各行列方向上的求和值，但如果某些情况下不能直接引用行列区域，例如有更多筛选条件的情况下、或计算内存数组的行列和值时，就需要使用mmult函数来替代。 增加要求，现在需要根据条件进行求和：例如，需要对数据区域中小于50的数字进行求和 Mmult19410012267174 验证19410012267174 再看一个稍微复杂一些的案例：要求得下面这个表格中，型号E最大一个月的产量。 示例之二月份型号产量 3E150.59辅助列做法：高效做法： 5F1690.1810 9C546.342902.35 10F328.6530 4B482.6640 8D1176.7150

5、 10B974.3560 3B519.577640.5 11D752.38105.54 6A1449.290 6B1563.8100 8B1460.16110 5C202.97121597.13 10A290.45max1597.13 6C1447.1公式1($B$30:$B$70=COLUMN(A:L)*($C$30:$C$70=“E“)*($D$30:$D$70)生成一个二维内存数组，以月份为列标题，然后使用mmult对各列纵向数据进行求和运算 2D1784.02公式2和公式3可以看作是公式1的矩阵乘法变形。 9C688.6 8B1801.61如果要同时得到最大产量的月份： 6D1283.

6、68产量月份 6D1728.571965.112 10C76.57 7F1160.74 11D949.18 5B793.78 7A1851.79 7D812.55 2E902.35 6B1747.79 4D537.54 4A184.2类似具体实例： 4D1871.1 6B1947.35 12E1597.13 7F1530.89 8B103.1 11A393.07 2C625.96 2C1972.13 6C1615.83 8E105.54 7E640.5 返回目录 SumifSubtotalMmultMmult验证 3403403407979 2972972973232 153153153100

7、100 2592592594545 222222222113113 2672672677373 2842842846969 2372372371414 2902902904646 1491491498686 公式1公式2公式3 MmultMmultMmult 1965.11965.11965.1 公式1array1: 111 array2: 1月2月3月 D00150.6 对于直接引用单元格区域的情况，使用sumif函数和subtotal函数均可以比较方便地得到各行列方向上的求和值，但如果某些情况下不能直接引用行列区域，例如有更多筛选条件的情况下、或计算内存数组的行列和值时，就需要使用mmul

8、t函数来替代。 增加要求，现在需要根据条件进行求和：例如，需要对数据区域中小于50的数字进行求和 公式1($B$30:$B$70=COLUMN(A:L)*($C$30:$C$70=“E“)*($D$30:$D$70)生成一个二维内存数组，以月份为列标题，然后使用mmult对各列纵向数据进行求和运算 公式2和公式3可以看作是公式1的矩阵乘法变形。 C000 C000 F000 B000 D000 B000 B000 D000 A0160.40 B000 B000 C000 A000 C000 D000 C000 B000 D000 D000 C000 F000 D000 B000 A000 D0

9、01691 E000 B000 D000 A000 D000 B000 E000 F000 B000 A000 C000 C000 C000 E000 E000 1111111111111111 4月5月6月7月8月9月10月11月12月 000000000 对于直接引用单元格区域的情况，使用sumif函数和subtotal函数均可以比较方便地得到各行列方向上的求和值，但如果某些情况下不能直接引用行列区域，例如有更多筛选条件的情况下、或计算内存数组的行列和值时，就需要使用mmult函数来替代。 公式1($B$30:$B$70=COLUMN(A:L)*($C$30:$C$70=“E“)*($D$

10、30:$D$70)生成一个二维内存数组，以月份为列标题，然后使用mmult对各列纵向数据进行求和运算 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 00894.3000000 000000000 00569.1000000 000000000 000000000 000000000 000889.500000 000000000 000000000 000000000 000000010300 0000

11、00000 000000000 000000000 000000000 000000000 000000001965 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 1111111111111111 111111 返回目录计计算算每每天天的的库库存存量量 示例之一日期入库量SumifSubtotalMmult 6月10日26262626 6月11日56828282 6月12日93175175175 6月13日47222222

12、222 6月14日19241241241 6月15日60301301301 6月16日78379379379 6月17日1380380380 6月18日7387387387 6月19日83470470470 对于直接引用单元格区域的情况，使用sumif函数和subtotal函数均可以比较方便地进行累加求和。 计计算算每每天天的的库库存存量量 日期入库量出库量SumifSubtotalMmultMmult 6月10日37325555 6月11日18320202020 6月12日26424444 6月13日352712121212 6月14日604428282828 6月15日9541828282

13、82 6月16日8713237373737 6月17日910128128128128 6月18日379075757575 6月19日831499999 公式1公式2 条件中增加了“出库量”数据列，需要对此列数据取负值同时计入累计的和值中。虽然用sumif和subtotal函数还是可以完成，但公式长度会随着统计的数据列的增多而大幅增加。 而使用mmult函数则可以同时对多个数据列进行运算，公式长度也比较经济。 公式1中使用了offset的多维引用，同时使用sumif函数进行了降维处理，同时得到入库和出库两列累计和值，然后通过mmult进行横向求和得到结果。 公式2运算思路稍有变化，先将出库与入库

14、进行相减，然后再进行累计求和。这种方法也是拓展性比较强的一种公式做法。 计计算算A A仓仓库库每每天天的的库库存存量量 仓库日期入库量出库量Mmult A6月10日13121 A6月11日24619 B6月12日53719 增增加加条条 件件 增增加加条条 件件 A6月13日906742 A6月14日32272 A6月15日7911239 B6月16日222539 B6月17日619439 A6月18日9619116 A6月19日128444 公式3 再次增加条件后，使用sumif和subtotal函数就不太好处理了，而使用mmult函数继续沿用前面的运算思路却仍可以胜任。 公式3与上面公式2

15、的思路基本一致。 要要求求:寻找甲原料最早达到库存量50的那一天，并且确定是在哪个仓库达到此库存量。 原料仓库日期入库量出库量MmultMmult 乙B6月10日4516日期所在仓库 甲C6月11日3122#VALUE!无 甲B6月12日55公式4公式5 乙C6月13日6943 甲C6月14日3022 乙C6月15日229 乙B6月16日544 甲A6月17日7034 甲B6月18日2114 甲B6月19日6623 乙B6月20日313 乙A6月21日5217 甲B6月22日6853 乙B6月23日647 乙A6月24日4924 甲B6月25日6712 乙A6月26日6358 甲B6月27日3

16、611 乙B6月28日31 甲B6月29日3718 再次增加条件后，整体思路仍然与上面的相似，只是在mmult函数中的array2中需要变换为两维的矩阵， 这样可同时对A、B、C三个仓库进行累计求和。 类似具体实例： 增增加加条条 件件 返回目录 array1:创建一个三角形矩阵array2: 100000000026 110000000056 111000000093 111100000047 111110000019 111111000060 111111100078 11111111001 11111111107 111111111183 公式2 array1:一个三角形矩阵array2

17、: 10000000005 110000000015 1110000000-16 11110000008 111110000016 111111000054 1111111000-45 111111110091 1111111110-53 1111111111-66 array1:一个三角形矩阵array2: 1000000000A1 1100000000A18 1110000000B0 对于直接引用单元格区域的情况，使用sumif函数和subtotal函数均可以比较方便地进行累加求和。 条件中增加了“出库量”数据列，需要对此列数据取负值同时计入累计的和值中。虽然用sumif和subtotal

18、函数还是可以完成，但公式长度会随着统计的数据列的增多而大幅增加。 公式1中使用了offset的多维引用，同时使用sumif函数进行了降维处理，同时得到入库和出库两列累计和值，然后通过mmult进行横向求和得到结果。 公式2运算思路稍有变化，先将出库与入库进行相减，然后再进行累计求和。这种方法也是拓展性比较强的一种公式做法。 1111000000B23 1111100000A30 1111110000B-33 1111111000A0 1111111100B0 1111111110B77 1111111111A-72 array1: 同样为一个2020的三角形矩阵 array2:ABC 000

19、乙 000 甲 000 甲 000 乙 000 甲 000 乙 000 乙 000 甲 001 甲 000 甲 090 乙 000 乙 0 28 0 甲 000 乙 060 乙 000 甲 000 乙 000 甲 000 乙 15 00 甲 再次增加条件后，使用sumif和subtotal函数就不太好处理了，而使用mmult函数继续沿用前面的运算思路却仍可以胜任。 要要求求:寻找甲原料最早达到库存量50的那一天，并且确定是在哪个仓库达到此库存量。 再次增加条件后，整体思路仍然与上面的相似，只是在mmult函数中的array2中需要变换为两维的矩阵， 返回目录各各“ “代代码码“ “出出现现次次

20、数数 示例之一代码CountifMmult型号代码 722A3 322B1 122B1 911B5 322B2 722C4 811A3 411B3 511C4 122A1 当需要计数的对象为数据区域时，使用countif而当计数的对象包含某些条件、不可直接引用数据区域时（相当于计数对象为内存数组）， 函数可以很方便地得到各个数据在数据组中的个数。就需要使用其他函数来替代countif函数，例如sumproduct和mmult函数。其中sumproduct函数只能生成单个结果，不能生成数组用于后续的计算。 H列和I列的数据可以分别看作条件1和条件2，目的是要对同时满足条件1和条件2时的对象计数，

21、 公式1的通用形式为：=mmult(条件1=transpose(条件1)*(条件2=transpose(条件2),row(1:数组高度)0) 公式2的通用形式为：=mmult(n(条件1&条件2=transpose(条件1&条件2),row(1:数组高度)0) 出出现现次次数数最最多多的的代代码码 示例之二代码CountifMmult型号代码 334A1 3A3 2B1 5B3 2A2 5B0 3B5 2A2 1B2 0A1 类似具体实例： 返回目录 增增加加条条件件 增增加加条条件件 “ “型型号号“&“&“代代码码“ “的的出出现现次次数数 Sumproduct MmultMmult 22

22、2 222 222 111 111 222 222 111 222 111 公式1公式2 各各型型号号出出现现次次数数最最多多的的代代码码 型号MmultMmult A24 B54 公式3公式4 公式3通过对IF($H$25:$H$34=K25,$I$25:$I$34) 这个附加条件后生成的数组结果进行转置比较，生成一个矩阵，对行(或列)求和后得到各个元素的计数值。 公式4则将附加条件直接放置到mmult函数的运算矩阵参数中。 而当计数的对象包含某些条件、不可直接引用数据区域时（相当于计数对象为内存数组）， 就需要使用其他函数来替代countif函数，例如sumproduct和mmult函数。

23、其中sumproduct函数只能生成单个结果，不能生成数组用于后续的计算。 H列和I列的数据可以分别看作条件1和条件2，目的是要对同时满足条件1和条件2时的对象计数， 公式1的通用形式为：=mmult(条件1=transpose(条件1)*(条件2=transpose(条件2),row(1:数组高度)0) 公式2的通用形式为：=mmult(n(条件1&条件2=transpose(条件1&条件2),row(1:数组高度)0) 沿对角线对称的矩阵 公式44314214452 array1:41001001100 30100000000 10010010000 41001001100 2000010

24、0001 10010010000 41001001100 41001001100 50000000010 20000100001 array2:A1 B0 B0 B0 B0 就需要使用其他函数来替代countif函数，例如sumproduct和mmult函数。其中sumproduct函数只能生成单个结果，不能生成数组用于后续的计算。 公式3通过对IF($H$25:$H$34=K25,$I$25:$I$34) 这个附加条件后生成的数组结果进行转置比较，生成一个矩阵，对行(或列)求和后得到各个元素的计数值。 B0 B0 A1 B0 B0 返回目录 示例之一 2414031 求求不不重重复复的的数数

25、据据个个数数： Countif 5 这个例子虽然从目标功能上来讲是进行数据的对比，但在公式的实质上还是使用了mmult的求和或累加功能。 公式1思路是使用mmult函数将整列数据合并成一个完整的字符串然后进行对比。 类似具体实例： 示例之二计计算算误误点点次次数数(抵达时间晚于预计时间)： 抵达时间 预计时间Sum 7:036:576 15:3515:37 20:1120:07 20:1420:35 17:5617:59 15:3815:35 1:010:52 5:556:18 0:130:34 17:2416:56 类似具体实例： 返回目录 增增加加条条件件 增增加加条条件件 2212211

26、 1221121 2112221 1222221 1211112 求求不不重重复复的的数数据据列列(整列数据排列顺序相同认定为相同数据列)： Mmult 6 公式1 求求完完全全没没有有误误点点的的航航班班数数量量：沿对角线对称的矩阵 航班抵达时间 预计时间Mmultarray1:AAC A7:037:110A110 A15:3515:21公式2A110 C20:1119:56C001 C20:1420:03C001 B17:5617:32B000 C15:3816:02C001 A1:011:01A110 C5:556:21C001 D0:130:17D000 B17:2417:15B000

27、 公式2使用mmult函数对航班分类别进行统计误点的情况，只有求和结果array2: 为0的项表示此航班类别误点次数为0。然后再用不重复数的筛选公式得A0 # 到最终的数量统计。A1 # mmult函数在数据对比中的用法其实与“条件计数”中的用法十分相似，C1 # 只是由于题目条件的有所区别，而使得在具体公式构造矩阵时稍有变化。C1 # B1 # C0 # A1 # C0 # D0 # B1 # 这个例子虽然从目标功能上来讲是进行数据的对比，但在公式的实质上还是使用了mmult的求和或累加功能。 增增加加条条件件 增增加加条条件件 CB CACD B 0001000 0001000 1010100 1010100 0100001 1010100 0001000 1010100 0000010 0100001 沿对角线对称的矩阵