关键词不能为空

当前您在: 主页 > 高中公式大全 >

数三角形个数的公式EXCEL条件求和与条件计数函数在学生成绩统计分析中的应用(原文)

作者:高考题库网
来源:https://www.bjmy2z.cn/gaokao
2020-09-14 19:43
tags:excel求和公式

八个字的留言-数学顺口溜


EXCEL条件求和与条件计数函数在学生成绩统计分析中的应用
古蔺县蔺阳中学 薛亮

在学生成绩的统计与分析中,需要用到EXCEL条件求和函数和条件计数函数的地方非常之 多,可以
用的函数和方法也很多,但是有的简单有的复杂。我们怎样才能事半功倍,用最简单的方法解决 复杂的问
题呢?现就个人在EXCEL条件求和和条件计数方面的一些体会,用一些实际操作的实例跟大 家分享一下。
一、单条件计数——COUNTIF函数
1.语法格式:=COUNTIF(区域,条件)
2.函数作用:用来计算区域中满足给定条件的单元格的个数。
3.单条件计数实例——成绩表中班级参考人数统计,直接用相应的单元格地址代替班级数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
A
考号
081010111
081010101
081010113
081010102
081010105
081010103






B
班级
1
2
1
2
3
4

班级
1
2
3
4
C
姓名
张三
李四
王五
赵二
孙六
孔七

参考人数




D
语文
110
113
111
122
114
105






E
数学
127
120
117
116
126
120






R
外语
114
114
128
108
123
105






G
政治
90
84
82
78
84
86






H
历史
98
84
82
78
84
86






I
地理
78
94
82
86
66
90






J
总分
617
609
606
597
594
591






例如:在上面的成绩表中要统计1、2、3、4班的参考人数。也许有 人会在C10、C11、C12、C13中
分别输入公式“=COUNTIF(B2:B7,”、“=C OUNTIF(B2:B7,”、“=COUNTIF(B2:B7,”、
“=COUNTIF(B2: B7,”,这样做当然没有错,是常规方法,但是如果是30个班、40个班或者更多呢,
那就有点麻烦 了。我们其实有更简单的方法——直接引用班级单元格地址。首先,在C10中输入公式
“=COUNT IF($$B$$2:$$B$$7,”,然后双击C10右下角填充句柄复制公式就可以了,C11、C12、C13< br>中公式就直接变成了“=COUNTIF($$B$$2:$$B$$7,”、“=COUNTIF($$B$$2:$$ B$$7,”、“=COUNTIF
($$B$$2:$$B$$7,”。因为B2:B7这个区域是固定不变的 ,为了复制公式时不发生变化,我们就直接
写成绝对引用形式$$B$$2:$$B$$7。而后面的条件部分, 我们直接用相应的单元格地址代替了班级1,
复制公式时自动变化,从而简化了操作,方便复制。但是一 定注意三点:1.关系运算符必须用英文的撇
起来,2. 关系运算符和单元格地址之间必须用&连接。3.注意单元格地址的相对引用和绝对引用。
二、多条件计数——SUMPRODUCT函数
1.语法格式:=SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))
2.函数作用:统计同时满足条件1、条件2到条件n的记录的个数。
3.多条件计 数实例一——成绩表中班级单科入围人数统计,直接用单元格地址代替班级数据和入围分
第 1 页 共 4 页
数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
单入围统

入围

1
2
3
4
语文
110




数学
120




A
考号
081010111
081010101
081010113
081010102
081010105
081010103
B
班级
1
2
1
2
3
4
C
姓名
张三
李四
王五
赵二
孙六
孔七
D
语文
110
113
111
122
114
105
E
数学
127
120
117
116
126
120

外语
115




政治
86




历史
84




地理
85




总分
600










R
外语
114
114
128
108
123
105
G
政治
90
84
82
78
84
86
H
历史
98
84
82
78
84
86
I
地理
78
94
82
86
66
90
J
总分
617
609
606
597
594
591 例如:在上面的成绩表中要统计1、2、3、4班的各科的单入围人数。我们首先分析一下要满足的条
件:1.班级数据区域对应的数据必须等于相应的班级;2.各科成绩区域对应的数据必须大于或等于相应的< br>入围分。对于这种多个条件的计数,我们按照SUMPRODUCT函数的语法格式,我们可以直接在C1 1中
输入“=SUMPRODUCT((B2:B7=1)*(D2:D7>=110))”,求出1班 语文科的单入围人数。但是七科4个班就必
须输入或修改28次公式,如果是更多的班级呢,这也太复杂 了吧。我们完全可以吸取上面COUNTIF函数
使用时的经验——直接引用相应的单元格地址代替相应 数据,可以直接在C11中输入公式
“=SUMPRODUCT(($$B$$2:$$B$$7=$$B11)* (D$$2:D$$7>=C$$10))”,公式说明:($$B$$2:$$B$$7=$$B11) 指定条件——
班级数据区域对应的数据必须等于相应的班级(地址),因为班级数据区域不变,所以用绝对引用,而后
边的班级1、2、3、4班都在B列,在复制公式时必须保证班级单元格地址的列不变,加$$;(D$$2:D$$7 >=C$$10)
指定条件——语文科成绩区域对应的数据必须大于或等于语文科的入围分(地址),因为 所有科目的数据
都是从第2行到第7行,而所有科目的入围分都在第10行,在复制公式时必须保证这些 行都不变,加$$。
在C11中输完公式后向右和向下复制公式就直接把1、2、3、4班的语文、数学、 外语、政治、历史、地
理、总分的单科入围情况统计出来了,再多的班级和科目也就输入一次公式就可以 了。但是也要注意几点:
1. SUMPRODUCT函数的条件用()括起来并用*相连,不要忘了最外层括号。2. 关系运算符直接连接数
据区域地址和单元格地址,而不需要和&。3.特别注意单元格地址的相对引用和绝对引用。
4. 多条件计数实例二——成绩表中班级单科和总分双入围人数统计

1
2
3
4
5
6
7
A
考号
081010111
081010101
081010113
081010102
081010105
081010103
B
班级
1
2
1
2
3
4
C
姓名
张三
李四
王五
赵二
孙六
孔七
D
语文
110
113
111
122
114
105
E
数学
127
120
117
116
126
120
R
外语
114
114
128
108
123
105
G
政治
90
84
82
78
84
86
H
历史
98
84
82
78
84
86
I
地理
78
94
82
86
66
90
J
总分
617
609
606
597
594
591
第 2 页 共 4 页
8
9
10
11
12
13
14
双入围统

入围

1
2
3
4
语文
110




数学
120





外语
115




政治
86




历史
84




地理
85




总分
600










例如:在上面的成绩表中要统计1、2、3、4 班的各科和总分的双入围人数。我们还是先来分析一下
要满足的条件:1.班级数据区域对应的数据必须 等于相应的班级;2.各科成绩区域对应的数据必须大于或
等于相应的入围分。3.总分数据区域对应的 数据必须大于或等于总分的入围分。跟单入围统计相比,仅仅
多了一个条件而已。因此,直接在C11中 输入公式“=SUMPRODUCT(($$B$$2:$$B$$7=$$B11)*(D$$2:D$$7>=C$$10)
*($$J$$2:$$J$$7>=$$I$$10))”,然后向右和向下复制公式就OK了。
三、单条件求和——SUMIF函数
1.语法格式:=SUMIF(条件数据区域,条件,需求和区域)
2.函数作用:根据指定条件对若干单元格求和。
3.单条件求和实例——成绩表中班级单科平均分统计,多函数结合功能更强大。

1
2
3
4
5
6
7
8
9
10
11
12
13
班级
平均分
1
2
3
4
语文




数学




A
考号
081010111
081010101
081010113
081010102
081010105
081010103
B
班级
1
2
1
2
3
4
C
姓名
张三
李四
王五
赵二
孙六
孔七
D
语文
110
113
111
122
114
105
E
数学
127
120
117
116
126
120

外语




政治




历史




地理




总分









R
外语
114
114
128
108
123
105
G
政治
90
84
82
78
84
86
H
历史
98
84
82
78
84
86
I
地理
78
94
82
86
66
90
J
总分
617
609
606
597
594
591

例如:在上面的成绩表中要统计各班的单科的平均分。我们可以先求出各班的单科的总分,再 求出各
班的人数,最后将各班的单科的总分除以各班的人数,这就得将SUMIF和COUNTIF两个 函数结合起来用。
我们直接在C10输入公式“=SUMIF($$B$$2:$$B$$7,”,
然后 向右和向下复制公式就可以了。公式说明:SUMIF($$B$$2:$$B$$7,——如果B2:B7
中有 等于B10(即1)的单元格,那么就求D2:D7中的对应的记录的和。COUNTIF($$B$$2:$$B$$7 ,
——求B2:B7中等于B10(即1)的单元格个数。
实际上,SUMIF函数的用法和 COUNTIF一样,只不过在后边多了一个需求和的数据区域而已。SUMIF
函数直接引用单元格地 址也和COUNTIF一样要注意:1.关系运算符必须用英文的撇起来,2. 关系运算
符和单元格地址之间必须用&连接。3.注意单元格地址的相对引用和绝对引用。
第 3 页 共 4 页
四、多条件求和——SUMPRODUCT函数
1.语法格式:=SUMPRODUCT((条件1)*(条件2)* (条件3) *…(条件n)*数据区域)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
班级

上线
1
2
3
4
语文
平均分




数学
平均分




A
考号
081010111
081010101
081010113
081010102
081010105
081010103
B
班级
1
2
1
2
3
4
C
姓名
张三
李四
王五
赵二
孙六
孔七
D
语文
110
113
111
122
114
105
E
数学
127
120
117
116
126
120

外语
平均分




政治
平均分




历史
平均分




地理
平均分




总分
600










R
外语
114
114
128
108
123
105
G
政治
90
84
82
78
84
86
H
历史
98
84
82
78
84
86
I
地理
78
94
82
86
66
90
J
总分
617
609
606
597
594
591
2.函数作用:汇总同时满足条件1、条件2到条件n的记录指定区域的和。
3.多条件求和实例——各班上线学生的单科平均分统计,同一函数多种功能混合使用。
例如 :在上面的成绩表中要统计各班上线学生(总分600分上线)的单科平均分。我们可以先求出各
班上线 学生的单科的总分,再求出各班的上线的人数,最后将各班上线学生的单科的总分除以各班上线的
人数, 这就得将两个SUMPRODUCT两个函数结合起来用。各班语文上线学生的单科的总分的计算:
“= SUMPRODUCT((B2:B7=B12)*(J2:J7>=$$I$$10)*I2:I7)”;各班上线 人数的计算:“=SUMPRODUCT((B2:B7
=B12)*(J2:J7>=I10))” 。但为了公式的复制,必须考虑单元格的引用问题,我们在C11中输入的公式为
“=SUMPRODU CT(($$B$$2:$$B$$7=$$B12)*($$J$$2:$$J$$7>=$$I$$10)*I$$2:I$$7)SUM PRODUCT(($$B$$2:$$B$$7=$$B12)*(
$$J$$2:$$J$$7>=$$I$$10))”, 然后向右和向下复制公式就可以求出各班上线学生的单科平均分了。
最后,值得大家注意的有以下几点 :1.公式输入必须在英文状态,避免输入非英文字符,引起函数的
语法错误。2.复制公式时,需变化 的用相对引用,不变化的用绝对引用。3.输入公式的单元格格式应选择
“常规”或“数值”,而不能是 “文本”,如果是文本格式,则单元格内显示的是公式本身而非计算结果。
第 4 页 共 4 页

服装设计课程-计算机管理


适合7到10岁儿童的电影-教孩子除法窍门


幽默又有深度的演讲稿-2016高考文综


生物必修一思维导图-舞蹈学校名字


五月的别称-漳州职业技术学校


中秋节放假几天-厦门有几个区


职业生涯规划书-儒家思想核心


2018江苏高考状元-积累知识



本文更新与2020-09-14 19:43,由作者提供,不代表本网站立场,转载请注明出处:https://www.bjmy2z.cn/gaokao/394916.html

EXCEL条件求和与条件计数函数在学生成绩统计分析中的应用(原文)的相关文章

EXCEL条件求和与条件计数函数在学生成绩统计分析中的应用(原文)随机文章