Excel电子表格统计分数段的数组公式
在做报表时,经常会遇上统计某一分数段的统计需求,完成这一需求方法很多,比较简单容易理解的是给数据加上一列分数档次列,利用公式先计算出这个分数的档次(例如10分分档的公式是:=int(@分数/10)*10),然后利用数据透视表进行统计。
但是上述公式需要在原始数据添加列,且步骤繁琐,这里介绍另外一种方法,是用数组公式进行统计,直接得出复合某一条件的统计。需要注意的是,数组公式必须在输入完成后,按下Ctrl+Shift+Enter进行输入,直接按下Enter是不行的,只有看到公式两端有一对大括号“{}”才是正确的。
例1:统计小于40分的数量
{=SUM(1*(表1[分数]<40))}
原理:数组公式会将参数作为一个数组对待,上例中“表1[分数]<40”会将分数列的每一项的每一行的分数与40进行比较,小于40得到 True 否则得到 False,返回的是一个包含 True 、False 的数组,将这个数字乘以1,True 乘以1 得1,False 乘以 1 得到0,这样就得到一个1、0的数组。最后使用 SUM公式进行统计,得到符条件的数量。
这样的逻辑看起来视乎很麻烦,但是运用得当,可以实现很复杂的交错条件统计:
例2:统计60分至69分的数量
{=SUM(1*(表1[分数]>=60)*(表1[分数]<70))}
说明:“并”关系只需要用乘号连起来就可以了。
例3:统计60分至69分并且不是作废的数量
{=SUM(1*(表1[分数]>=60)*(表1[分数]<70)*((表1[作废])=””))}
在做报表时,经常会遇上统计某一分数段的统计需求,完成这一需求方法很多,比较简单容易理解的是给数据加上一列分数档次列,利用公式先计算出这个分数的档次(例如10分分档的公式是:=int(@分数/10)*10),然后利用数据透视表进行统计。
但是上述公式需要在原始数据添加列,且步骤繁琐,这里介绍另外一种方法,是用数组公式进行统计,直接得出复合某一条件的统计。需要注意的是,数组公式必须在输入完成后,按下Ctrl+Shift+Enter进行输入,直接按下Enter是不行的,只有看到公式两端有一对大括号“{}”才是正确的。
例1:统计小于40分的数量
{=SUM(1*(表1[分数]<40))}
原理:数组公式会将参数作为一个数组对待,上例中“表1[分数]<40”会将分数列的每一项的每一行的分数与40进行比较,小于40得到 True 否则得到 False,返回的是一个包含 True 、False 的数组,将这个数字乘以1,True 乘以1 得1,False 乘以 1 得到0,这样就得到一个1、0的数组。最后使用 SUM公式进行统计,得到符条件的数量。
这样的逻辑看起来视乎很麻烦,但是运用得当,可以实现很复杂的交错条件统计:
例2:统计60分至69分的数量
{=SUM(1*(表1[分数]>=60)*(表1[分数]<70))}
说明:“并”关系只需要用乘号连起来就可以了。
例3:统计60分至69分并且不是作废的数量
{=SUM(1*(表1[分数]>=60)*(表1[分数]<70)*((表1[作废])=””))}