【请教】Excel的多条件求和或计数如何实现?

shh_czh9

姓名        语文        数学考分
学生1        83        92
学生2        80         89
学生3        78         88
学生4        81         93
学生5        76         85
学生6        82         79
学生7        74         86
学生8        68         78
学生9        72         82
学生10        62         77

用Excel的函数(类似sumif、countif）进行多条件求和或计数如何实现，以上为例：对二门课成绩同时达到80分以上的学生进行统计和总分累加，能否用单用函数实现，特请教，并先谢！

bluesmansion

第一个很简单，用SUMPRODUCT函数：
=SUMPRODUCT((B2:B11>80)*(B2:B11>80))

第二个我想一下告诉你。

[ Edited by  bluesmansion on 2008-10-15 12:42 ]

bluesmansion

第二个的结果貌似有问题，但思路应该没错，也是用SUMPRODUCT函数：
=sumproduct((b2:b11>80)*(b2:b11>80),(B2:B11+C2:C11))

这楼的附件是更新后的，看粗体的结果。

nicolan

QUOTE:

Posted by bluesmansion on 2008-10-15 12:41
第一个很简单，用SUMPRODUCT函数：
=SUMPRODUCT((B2:B11>80)*(B2:B11>80))

第二个我想一下告诉你。

[ Edited by  bluesmansion on 2008-10-15 12:42 ]

第一个很简单，用SUMPRODUCT函数,,意思对了,不过写错了,应该是：
=SUMPRODUCT((B2:B11>80)*(C2:C11>80))

bluesmansion

结果应该一致吧？二楼的附件中体现出来了。

larryh

用传统的EXCEL 2003或以前的版本实现这个很不直观。bluesmansion 的方法可以实现，不过两处公式的C2:C11都写成了B2:B11导致结果计算错误。

直观只有EXCEL2007才能做到

至于原因，就是因为EXCEL 2007有了过去没有的XXXXIFS系列函数，可以多条件SUM、COUNT等等

[ Edited by  larryh on 2008-10-15 14:26 ]

nicolan

呵呵,你看楼主题目,正确答案是2个.
姓名        语文        数学考分
学生1        83        92
学生4        81         93
学生6        82         79  (这个是不能算的,因为数学<80了!)

公式SUMPRODUCT((B2:B11>80)*(B2:B11>80))计算的是同一列,应该是:
=SUMPRODUCT((B2:B11>80)*(C2:C11>80)) ,才有2列结果一起判定的.
要不你换成=SUMPRODUCT((C2:C11>80)*(C2:C11>80)) ,看看是多少?

larryh

符合条件的学生是2个还是3个，这是因为LZ没有用精确数学语言描述其需求造成，“达到80分以上”，到底含不含80分？这模棱两可。但一般老师的心理应当都是含的，80-100都算“达到80分以上”，如果不含，自然就是2个(学生1、4），含，就是3个（学生1、2、4），公式里是>80还是>=80的区别

IFS系列函数的好处还有：对于求和的区域，如果其中有非数字内容，可以自动跳过，不予计算，而SUMPRODUCT就只能以报错告终了，例如学生1的数学分数修改为“未参加”，SUMPRODUCT就无能为力了。

[ Edited by  larryh on 2008-10-15 14:35 ]

bluesmansion

果然错了……

我去面壁……

shh_czh9

谢楼上各位高手．
用SUMPRODUCT函数做了，结果也对，但对该函数能用于此处的含义不甚理解.
????IFS函数看来可以解决,但我用的是2003看来要升到2007了,甚谢

larryh

bluesmansion DX用SUMPRODUCT的思想核心是(以下全部与bluesmansion的假定相同，用>80而不用>=80)：
对于比较表达式的结果，TRUE为1，FALSE为0
所以
B2:B11>80的结果就是一个一维数组(array)：1,0,0,1,0,1,0,0,0,0
C2:C11>80的结果也是一个一维数组(array)：1,1,1,1,1,0,1,0,1,0
两个数组，同下标的对应位置相乘，这就是PRODUCT的含义，得到结果1,0,0,1,0,0,0,0,0,0，这样就把两列结果都为1（意味着都>80）的行标志出来了，SUM一把，就得到计数值（有多少个满足条件的）
至于第二个要求，还是拿到这个标志数组，再与第一列与第二列相加的和组成的数组：就是),B2:B11+C2:C11，再行行相乘，标志为0的，自然就为0了，标志为1的，自然得到的就是两列成绩相加的结果，再SUM一把，总和就得到了

shh_czh9

理解了,larryh表述很清楚,谢各位

bluesmansion

QUOTE:

Posted by larryh on 2008-10-15 15:17
bluesmansion DX用SUMPRODUCT的思想核心是(以下全部与bluesmansion的假定相同，用>80而不用>=80)：
对于比较表达式的结果，TRUE为1，FALSE为0
所以
B2:B11>80的结果就是一个一维数组(array)：1,0,0,1,0,1,0, ...

非常感谢larryh的讲解！

我对excel也是一知半解，只会套用函数公式而已，呵呵。