利用EXCEL进行数据统计若干技巧范文.docx
《利用EXCEL进行数据统计若干技巧范文.docx》由会员分享，可在线阅读，更多相关《利用EXCEL进行数据统计若干技巧范文.docx（12页珍藏版）》请在冰点文库上搜索。
利用EXCEL进行数据统计若干技巧范文
利用Excel进行数据统计的若干技巧
一、认识常用的统计函数：
1、SUM：
计算单元格区域内所有数据的和。
表达式为：
=SUM（D2：
F50）
2、AVERAGE：
计算单元格区域内所有数据的算术平均数。
表达式为：
=AVERAGE（D2：
F50）
3、COUNT：
计算表中的数字参数和包含数字的单元格的个数。
表达式为：
=COUNT（D2：
F50）
4、COUNTIF：
计算单元格区域内满足给定条件单元格的个数。
表达式为：
=COUNTIF（D2：
F50，">=90"）
5、MAX：
返回一组数值中的最大值。
表达式为：
=MAX（D2：
F50）
6、MIN：
返回一组数值中的最小值。
表达式为：
=MIN（D2：
F50）
7、MIDB：
自文字起始位置开始提取指定长度字符串。
表达式为：
=MIDB（B2,8,6）
8、LARGE：
返回数据中第K个最大值。
表达式为：
=LARGE（F2:
F34,11）
9、SMAL：
返回数据中第K个最小值。
表达式为=SMALL（F2:
F28,5）
10、RANK：
返回指定数字在列中的排位。
表达式为：
=RANK（I2,I:
I）
利用好统计公式中的定位符$例：
RANK（I2,$I$2:
$I$180）
11、EXACT：
比较两个字符串是否完全相同（区分大小写），如果完全相同，返回TREU，否则返回FALSE。
表达式为：
=EXACT（D2,J2）
12、VALUE：
将代表数值的文本字符串转换成数值。
表达式为：
=VALUE（F2）
13、IF：
判定一个条件是否满足，如果满足则返回一个值，如果不满足则返回另一个值。
表达式为：
=IF（F2>=80,"合格","不合格"）
同时满足多重条件的表达式：
=IF（C2>=90,IF（D2>=84,IF（E2>=38,1,0）,0）,0）
注意：
表达式中输入的数据、符号必须在英文状态下输入。
二、简单数据的简便统计：
（一）数据整理。
1、按单位整体集中数据（班为单位），统一编序号，删除与统计无关项目。
把学校、班级列调整到靠近数据区。
（操作方法：
剪切要移动列→选中要移入的目标列→点击“【插入已剪切的单元格】”）
2、按升序或降序方式逐项清理各科数据，处理不合理数据（文本数据，超满分数据）。
注意：
利用升降序工具进行操作时，数据必须至少有一方与数据区域相连。
而按菜单【数据】→【排序】操作则可避免此问题。
3、划分分数线。
按升序或降序方式：
例：
按A段人数（3500*40%=1400），选中语文科第1400行数据，将语文科降序排列，选中单元格即语文科A段分数线；其它学科照此类推。
（二）利用分类汇总工具进行数据统计。
1、平均分：
【数据】→【分类汇总】→【分类字段】：
班级→【汇总方式】：
平均值→【选定汇总项】：
各科均选中→【确定】→【分级显示】：
选中2级。
抄录有关数据到统计总表。
2、合格段人数：
【移动或复制工作表】（选中【建立副本】）→语文科成绩降序排列→删除分数线下所有单元格数据。
→以班为单位排序→【分类汇总】→【分类字段】：
班级→【汇总方式】：
计数→【选定汇总项】：
语文科→【确定】→【分层显示】：
选中2层。
抄录有关数据到统计总表。
其它各科数据照此类推。
3、数据高级筛选：
（适用于均合格；有效上线人数）
（1）数据前插入条件区域，建立条件项：
（在相应栏目上方，与数据区域有隔断行）
语文数学总分
>85>78>125
（2）筛选数据：
【数据】→【筛选】→【高级筛选】→点中【“筛选结果复制到基它区域”】→选定数据区域→选定条件区域→选定筛选结果存放区域→【确定】
（3）对筛选出来区域的数据进行统计。
（三）利用数据透视表进行数据统计。
（1）【数据】→【数据透视表和数据透视图】→根据“数据透视表和数据透视图导向”进行操作：
（2）【键入数据源区域】→【下一步】→【现有工作表】，指定建立统计区域位置→【布局】：
“学校”拖入“行”，“班级”拖入“列”，各科成绩拖入“数据”（双击科目可变更统计项，也可将各科成绩多次拖入，变更成不同的统计项）→【确定】→【完成】
（3）数据透视表中数据的显示：
点击学校项下拉箭头，可显示指定学校数据。
点击班级项下拉箭头，可显示指定班级数据。
（四）建立班级统计模板
1、在原始成绩表输入学生原始成绩。
（学生顺序不能变，缺考空位）
（1）锁定单元格：
【工具】→【保护】→【允许用户编辑区域】→【新建】→【引用单元格】：
选择可编辑区（各科成绩、总分、位次列）→【工作表保护】→【确定】
（2）计算总分：
选中总分下第一格→输入=SUM（C2:
F2）→【确定】→填充
计算位次：
选中总分下第一格→输入=RANK（G2,G:
G）→确定→填充
2、建班级档案工作表
（1）在班级档案工作表中选中单元格A1，输入=原始成绩!
A1→确定→拖动拖动填充柄至B56，复制学生信息。
（2）在班级档案工作表中选中单元格C1，输入=原始成绩!
G1→确定→拖动拖动填充柄至D56，复制第一次总分、位次信息。
（3）在班级档案工作表中选中C、D两列，【复制】→选中E、F两列，【选择性粘贴】→【粘贴数值】→重命名列标题，固定第一次总分、位次信息。
以后每次获得的信息均以此方式固定，即可形成学生成绩档案。
3、建成绩分析表
（1）选中“成绩分析”工作表，建立统计项：
标题、科目、考试人数、各分数段、最高分、最低分、平均分
（2）分别在各统计项后单元格内输入统计公式：
考试人数：
=COUNT（原始成绩!
C:
C）→确定→拖动填充柄至各科。
90-100分人数：
=COUNTIF（原始成绩!
C:
C,">=90"）→确定→拖动填充柄至各科。
80-89分人数：
=COUNTIF（原始成绩!
C:
C,">=80"）-B4→确定→拖动填充柄至各科。
70-79分人数：
=COUNTIF（原始成绩!
C:
C,">=70"）-B4-B5→确定→拖动填充柄至各科。
60-69分人数：
=COUNTIF（原始成绩!
C:
C,">=60"）-B4-B5-B6→确定→拖动填充柄至各科。
60分以下人数：
=COUNTIF（原始成绩!
C:
C,"<60"）→确定→拖动填充柄至各科。
最高分：
=MAX（原始成绩!
C:
C）→确定→拖动填充柄至各科
最低分：
=MIN（原始成绩!
C:
C）→确定→拖动填充柄至各科
平均分：
=AVERAGE（原始成绩!
C:
C）→确定→拖动填充柄至各科
（3）使用图表分析数据：
【插入】→【图表】→【图表向导】→【图表类型】：
饼型→【下一步】→【数据区域】→单击【折叠对话框】：
选择数据区域（只选择与本学科有关的数据，不连续区域的选择办法是：
选了第一区域后，按住CTEL控制键，选第二区域；依次类推）→【下一步】→【下一步】→【作为其中对象插入】→【确定】
三、大面积数据的综合统计
（一）整理数据：
1、列的排列依次为：
考号、学校、班级、各科成绩及总分
2、学校、班数据集中。
3、利用函数LARGE（返回数据中第K个最大值。
）
（1）确定分数线。
复制各科标题为分数线标题排在数据区域之右侧，选中分数线存放位置；
在公式编辑栏中输入分式：
例：
=LARGE（F2:
F34,10）
（2）确定第一科分数线后，向右拖动填充柄，即可获取各科分数线；
在下行复复制公式，更改分数段人数，即可获取另一层次分数线。
（3）将分数线数据复制到新建空白统计表中：
更改表名（选中表标签，点右键，重命名：
分数线）；复制数据表中分数线→在分数线表中选中左上角单元格→点鼠标右键→【选择性粘贴】→【粘贴：
数值】。
（二）分类汇总
1、点击数据区域。
【数据】→【分类汇总】→【分类字段】：
学校→【汇总方式】：
计数→【选定汇总项】：
第一科→【确定】。
2、再次以班为字段分类汇总。
【数据】→【分类汇总】→【分类字段】：
班级→【汇总方式】：
计数→【选定汇总项】：
第一科→【确定】。
把【“替换当前分类汇总”】选项取消。
3、筛选：
（1）选中表中任意单元格。
【数据】→【筛选】→【自动筛选】→点击学校列【下拉箭头】→【自定义】→【包含】→内容内输入“计数”
（2）将筛选出的“学校计数项”复制到班级列。
（选中复制区域，拖动填充柄到班级列释放即可）
（3）点击学校列下拉箭头→【显示全部数据】。
（4）在“班级”列重复第
（1）步操作：
点击班级列下拉箭头→【自定义】→【包含】→内容内输入“计数”
（三）建立统计区
1、在数据区域正面（隔两行）建立统计项目：
（空）学校班级参统人数语文数学……
平均分A段数B段数平均分A段数B段数
2、替换引用区：
选中第一科（即D列）所有筛选出的项目，执行替换操作：
【编辑】→【替换】→【查找内容】：
D→【替换为】$D$→【全部替换】
3、替换公式：
选中第一科（即D列）所有筛选出的项目，执行替换操作：
【编辑】→【替换】→【查找内容】：
subtotal（3,→【替换为】counta（→【全部替换】
4、将替换后的内容整体复制到统计区域：
选中第一科（即D列）所有筛选出的项目，复制
选中统计区“学校”项下单元格，点击鼠标右键→【选择性粘贴】→【粘贴公式】。
5、删除原始数据区内含公式的行：
选中原始数据区内所有筛选出的行，全部删除
6、去除行标题中“计数”字符：
选中统计区内所有包含“计数”字符的区域，执行替换操作：
【编辑】→【替换】→【查找内容】：
计数→【替换为】（保持空格）→【全部替换】
（四）分科统计（统计第一科数据）
1、复制参考人数栏内数据到“语文”科下各统计项下”平均分”、“A段数”列下：
选中参考人数栏内数据，拖动填充柄到目标列下释放。
2、选中“平均分”列所有数据，执行替换操作：
【编辑】→【替换】→【查找内容】：
counta→【替换为】:
average→【全部替换】
3、选中“A段数”列所有数据，执行替换操作：
【编辑】→【替换】→【查找内容】：
）→【替换为】：
”>=85”→【全部替换】（即：
把后括号替为A段分数线）
4、选中“A段数”列所有数据，再次执行替换操作：
【编辑】→【替换】→【查找内容】：
counta→【替换为】：
countif→【全部替换】
5、复制“A段数”列下数据到“B段数”列下：
（拖动填充柄到目标列下释放）
6、选中“B段数”列所有数据，执行替换操作：
【编辑】→【替换】→【查找内容】：
”>=85”→【替换为】：
”>=70”→【全部替换】（即：
把A段分数线替为B段分数线）
（五）分科统计（统计其它各科数据）
1、将第一科统计结果复制到其它各科统计栏目下。
2、分科替换引用区：
选中第二科下所有数据，执行替换操作：
【编辑】→【替换】→【查找内容】：
$D$→【替换为】：
$E$→【全部替换】。
（即：
把引用的D列（语文科）的原始数据替为E列（数学科）的原始数据。
3、分段替换分数线：
（1）把语文A段分数线替为数学A段分数线：
选中数学A段下所有数据，执行替换操作：
【编辑】→【替换】→【查找内容】：
”>=85”→【替换为】：
”>=72”→【全部替换】
（2）把语文B段分数线替为数学B段分数线：
【编辑】→【替换】→【查找内容】：
”>=72”→【替换为】：
”>=69”→【全部替换】
4、照此类推，替换其它所有学科下的数据。
（六）建立统计表
1、插入新的空白工作表，明确统计方案：
一、分数线：
“合格”按参考人数的98%划线，“优秀”按参考人数的50%划线；“优生”人数中包括“合格”人数。
二、按考四科和三科两条线统计到学科、班级、学校。
学校按层次进行分析。
三、学科统计“平均分”、“合格率”、“优秀率”三项；学校统计“总分平均分”、“总分合格率”、“均合格率”、“均优率”四项。
“均合格（优）率”按语\数\外\综四科统计。
四、相关公式：
学科平均分比率=平均分/该科全县最高平均分*100
学科合格率=合格人数/参考人数*100
优秀率=优秀人数/参考人数*100
总分平均分比率=总分平均/全县学校最高平均分*100
总分合格率=总分合格人数/参考人数*100
均合格（优）率=四科均合格（优）人数/参考人数*100
学科积分=平均分比率+合格率+优生率
学校积分=总分平均分比率*25%+总分合格率*25%+均合格率*25%+均优秀率*25%
2、复制数据到空白统计表中
选中统计区内的所有数据：
复制
选中空白统计表左上角：
【选择性粘贴】→【粘贴数字】
3、在各段人数后分别插入空白列。
4、在“语文A段”列后空白列自定义统计公式：
选中该列数据区第一行单元格。
在公式编辑栏输入公式：
=d3/$c3*100，【确定】
双击填充柄复制公式到该列所有单元格。
5、将“语文A段”的统计公式复制到其它各科各段人数列后：
选中“语文A段”列的统计公式，复制。
分别粘贴到其它空白列：
【选择性粘贴】→【粘贴公式】
6、计算各科积分：
分别在各科后插入空白列。
学科积分=平均分比率+合格率+优生率
在公式编辑栏输入公式：
=d4/MAX（d:
d）*100+f4+h4，【确定】
7、排位
在积分后插入空白列。
在公式编辑栏输入公式：
=RANK（I2,I$4:
I$29）
（七）关于各科均优、有效上线的统计：
1、在数据区后建立标志，分别用“1”、“0”代表上线和未上线。
建立列标志，均A，均B（或A线有效，B线有效）
选中均A列下第一格，在公式编辑栏输入公式：
=IF（$D2>=89,IF（$E2>=82,IF（$F2>=70,IF（$G2>=84,1,0）,0）,0）,0）确定。
2、其它各项照此类推。
3、照第
（二）（三）（四）分科统计（统计第一科数据）的操作步骤进行统计
注意：
（1）数据引用项替换为“标志”栏的列号；
（2）将分数线全部替换为“=1”
升级会员 