excel 数组备忘

2009年4月18日星期六加班，突然发现一篇文章，他定义的“名称”很爽，借来一用。

“数据库”表字段构成：
“项目名称” “费用明细” “隶属费用科目” “预算金额（万元）” “备注”
针对“项目名称”这个字段所有行记录进行定义的名称公式是：
项目=OFFSET(数据库!$A$1,1,0,COUNTA(数据库!$A:$A)-1,1)
那么，针对“隶属费用科目”这个字段所有行记录进行定义的名称公式是：
1，老方法，针对C列编写公式：
科目=OFFSET(数据库!$C$1,1,0,COUNTA(数据库!$C:$C)-1,1)
2，今天学到的方式是
科目=OFFSET(项目,,2)
在"项目"这个定义的基础上，偏移0行，2列！但是，要求这两个字段的记录要相等！！！
————————————————————————————————————————
关于多条件求和

序号 产品编号 生产日期 产品单价 产品数量
1 AA 2000-6-15 1 100
2 AA 2000-6-20 1 125
3 BB 2000-6-30 2 150
4 BB 2000-7-10 2 175
5 CC 2000-7-15 3 200
6 CC 2000-7-20 3 225
7 AA 2000-7-30 1 250
8 AA 2000-8-10 1 275
9 BB 2000-8-15 2 300
10 BB 2000-8-20 2 325
11 CC 2000-8-30 3 350
12 CC 2000-10-10 3 375
13 DD 2000-10-15 4 400
14 DD 2000-10-30 4 425

要求出产品"BB"的8月份总数量。
1，常用的解法是SUM数组：{=SUM(条件1*条件2*数据)}
2，后来又有了一种不需要按三键的公式： =SUMPRODUCT(条件1*条件2*数据）
3，{=MMULT(--(A22=TRANSPOSE(B3:B16)),(MONTH(C3:C16)=B22)*E3:E16)}
条件1、条件2和数据三者哪个作为MMULT的第一或第二参数是有限制的。
本题的结果是一行一列的数组。根据MMULT的特性，MMULT的第一参数应为一行N列，而第二参数应为N行一列，并且均为数值
C24公式中，A24=TRANSPOSE(B6:B19)是一个一行14列的数组，前面加上--()就使它变成了数值：{0,0,1,1,0,0,0,0,1,1,0,0,0,0}
(MONTH(C6:C19)=B24)*E6:E19则是一个14行一列的数组：{0;0;0;0;0;0;0;275;300;325;350;0;0;0}
两个数组做矩阵相乘后，就得到了最终的结果：625（一行一列数组)

明白了上面的原理，我们就知道：只要注意到MMULT的特性，条件和数据的位置也是可以灵活变化的。
比如，可以把公式写成：MMULT(条件2,条件1*数据）
即：{=MMULT(--(B22=TRANSPOSE(MONTH(C3:C16))),(A22=B3:B16)*E3:E16))
也可以写成：MMULT(条件2*条件1，数据）
即：{=MMULT((A22=TRANSPOSE(B3:B16))*(B22=TRANSPOSE(MONTH(C3:C16))),E3:E16)}
甚至还可以写成：MMULT(条件1*条件2*数据，标准矩阵)
即：{=MMULT((A22=TRANSPOSE(B3:B16))*(B22=TRANSPOSE(MONTH(C3:C16)))*TRANSPOSE(E3:E16),ROW(B3:B16)^0)}
所有这些变化的关键，是要随时注意MMULT第一、第二参数对行列的要求。