本帖最后由 rinyxa 于 2013-1-5 11:30 编辑
x126397 发表于 2013-1-4 18:35
哇 这么厉害 这个是什么公式来的 我是新手 可以解释一下吗
用于多条件计数用数学函数SUMPRODUCT计算符合2个及以上条件的数据个数
⑴数据表如下数据表所示: 统计表E列 F列 G列中一 中二男女要求:统计上面数据表中男、女性中分别是中一、中二的人数。如下图片,A1:C11数据区域,在E1:G3区域统计男、女中中一和中二的人数是多少。
⑵公式在数据统计区域中的F2单元格编辑如下公式:
=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))
向下复制到F3,向右复制到G3。
⑶公式分解及分析
①条件1——$B$2:$B$11=$E2在计算过程中,条件1是一个数组,返回多值,写成公式如下:=$B$2:$B$11=$E2具体操作:选中10个连续的单元格,输入上述公式后,按Ctrl+Shift+回车键确认,返回10个逻辑判断值——TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE。这10个逻辑判断值构成一个新的由TRUE主FALSE组成的数组1。
②条件2——$C$2:$C$11=F$1与条件1相同,是一个数组,返回多值,写成公式如下:=$C$2:$C$11=F$1具体操作:同样的方法,选中对应的10连续单元格,输入上述公式,按Ctrl+Shift+回车键确认,返回10个逻辑判断值——TRUE;FALSE;TRUE;TRUE;TRUE;FALSE; FALSE;TRUE; TRUE;TRUE。这10个逻辑判断值构成另一个新的由TRUE主FALSE组成的数组2。
③($B$2:$B$11=$E2)*($C$2:$C$11=F$1)由新构成的数组1乘以数组2,即:
=($B$2:$B$11=$E2)*($C$2:$C$11=F$1)
={数组1*数组2}
={ TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE }*{ TRUE;FALSE;TRUE;TRUE;TRUE;FALSE; FALSE;TRUE; TRUE;TRUE }
={TRUE*TRUE;FALSE*FALSE;FALSE*TRUE;TRUE*TRUE;FALSE*TRUE;TRUE*FALSE; FALSE*FALSE;TRUE*TRUE;TRUE*TRUE; FALSE*TRUE}
={1;0;0;1;0;0;0;1;1;0}
其中,逻辑值TRUE与FALSE参与计算时:
TRUE=1,FALSE=0,TRUE*TRUE=1,TRUE*FALSE=FALSE*TRUE=0,FALSE*FALSE=0
因此{数组1*数组2}={1;0;0;1;0;0;0;1;1;0}由1和0构成了一个新的数组3。
④=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))
函数SUMPRODUCT对新的数组3中的所有数据求和。即:
=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))=SUMPRODUCT({数组1*数组2})
=SUMPRODUCT({数组3})
=SUMPRODUCT({1;0;0;1;0;0;0;1;1;0})
=4
这里需要说明的是,公式编辑按照函数SUMPRODUCT的一般格式,可以编辑如下等效的公式:
=SUMPRODUCT(($B$2:$B$11=$E2)*1,($C$2:$C$11=F$1)*1)
函数SUMPRODUCT的作用是对数组($B$2:$B$11=$E2)与数组($C$2:$C$11=F$1))计算其乘积的和,即:
=SUMPRODUCT(($B$2:$B$11=$E2)*1,($C$2:$C$11=F$1)*1)
=SUMPRODUCT({ TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE }*1,{ TRUE;FALSE;TRUE;TRUE;TRUE;FALSE; FALSE;TRUE; TRUE;TRUE }*1)
=SUMPRODUCT({1;0;0;1;0;1;0;1;1;0},{1;0;1;1;1;0;0;1;1;1})
=SUMPRODUCT({1*1;0*0;0*1;1*1;0*1;1*0;0*0;1*1;1*1,0*1})
=SUMPRODUCT({1;0;0;1;0;0;0;1;1,0})
=4
注意:TRUE*1=1,FALSE*1=1*FALSE=0,TRUE*0=0*TRUE=0 。数组中用分号分隔,表示数组是一列数组,分号相当于换行。两个数组相乘是同一行的对应两个数相乘。