Excel数据处理常用方法分类解析

时间:2022-09-09 11:20:24

Excel数据处理常用方法分类解析

摘要:Excel中,我们经常要用到有关数据处理的函数和公式,用以处理纷繁复杂的数据信息。数据处理的方法包括数据筛选、数据统计和数据计算等等。这些数据处理的方法很实用,为我们处理庞大的数据信息带来了很大的便利。对这些方法进行归类和解析,更有助于初学者更快更迅速地掌握和理解这些数据处理的方法。

关键词:单元格; 数据;条件

中图分类号:TP311 文献标识码:A 文章编号:1009-3044(2014)02-0426-02

Excel中,根据数据处理的目的和要求,可以把数据处理的方法分为如下几类:

1 数据筛选

1.1 使用“自动筛选”命令筛出符合条件的数据

选择命令:数据—筛选—自动筛选,在要进行数据筛选的列上单击黑色三角,选择一种条件,或者自定义筛选的条件,即可按条件进行数据筛选。若不想破坏原有的数据表格,可先复制一个数据表的副本,在副本上进行操作即可保留原数据表。

1.2 使用“高级筛选”命令进行数据的复杂筛选

在数据表之外的一处单元格如H3中,输入要进行筛选的数据所在的字段名称,紧靠这个单元格下方H4中,输入筛选的条件。选择命令:数据—筛选—高级筛选,勾选“将筛选结果复制到其他位置”选项,设定“列表区域”为全部数据表,设定“条件区域”为“H3:H4”,设定“复制到”位置为H5单元格。按确定之后,按照指定条件筛选出来的数据项就会显示在H5单元格的右下方。如果有多个筛选条件并存,则筛选条件放在同一行上为“且”的关系,放在错开的行上为“或者”的关系。

1.3 使用“条件格式”命令为符合条件的单元格作标记

例:成绩

选中成绩表所有单元格,点“格式”—“条件格式”,条件设为:

单元格数值,小于,60。

选格式—图案,点击红色后点“确定”。

2 数据计算

2.1 对一列数据求和

在存放结果的单元格中输入公式:=SUM(开始格:结束格)

对开始格到结束格这一区域数值进行求和;也可以使用从开始格拖拽到结束格的方式输入求和的范围。

2.2 对一列数据求平均数

在存放结果的单元格中输入公式:=AVERAGE(开始格:结束格)

对开始格到结束格这一区域数值求平均数;也可以使用从开始格拖拽到结束格的方式输入求和的范围。若有几列数据求平均数,可以使用拖拽复制的方式实现。

2.3 对一列数据标志等级

在存放标志结果的第一个单元格中输入公式:=IF(格>=90,"优",IF(格>=80,"良",IF(格>=60,"及格", "不及格")))

最后使用拖拽复制的方式实现对这一列数据标志等级。

2.4 已知每位学生的“平时”、“实践”、“期末”三项成绩,计算学期总成绩

在存放结果的单元格中输入公式:=格1*0.3+格2*0.4+格3*0.3

假设格1列、格2列和格3列分别存放着学生的“平时”、“实践”、“期末”三项成绩。最后使用拖拽复制的方式实现对所有学生求学期总成绩。

2.5 求最高分

在存放结果的单元格中输入公式:=MAX(开始格:结束格)

则在该单元格中显示从开始格到结束格中的最高分数。

2.6 求最低分

在存放结果的单元格中输入公式:=MIN(开始格:结束格)

则在该单元格中显示从开始格到结束格中的最低分数。

2.7 对某一列数据按条件求和

在存放结果的单元格中输入公式: =SUMIF(性别列开始格:性别列结束格,"男",课时列开始格:课时列结束格)

假设性别列存放老师的性别,课时列存放老师的课时数,则此函数返回的结果为全部男老师的课时总数。

2.8 根据出生年月来计算年龄公式

在存放结果的单元格中输入公式:=TRUNC((DAYS360(出生日期格,NOW(),FALSE))/360,0)

在存放结果的单元格中返回值即为年龄。最后使用拖拽复制的方式实现多个计算。

2.9 根据18位身份证号码自动判断性别

在存放结果的单元格中输入公式:= IF(MOD(MID(身份证号码格,17,1),2)=1,"男”, "女”)

在存放结果的单元格中返回值即为性别。最后使用拖拽复制的方式实现多个判断。

2.10 判断单元格里是否包含指定文本

在存放结果的单元格中输入公式:=IF(COUNTIF(目标格,"张"&"*")=1,"是","否")

假定目标格中存放的是姓名,则在存放结果的单元格中返回值即为判断结果,即是否姓张。最后使用拖拽复制的方式实现多个判断。

3 数据统计

3.1 统计成绩表中各分数段人数

假设A1:A50存放的是学生成绩,则

1) 求A1到A50区域中成绩为100分的人数,在存放结果的单元格中输入公式为: =COUNTIF(A1:A50,"100");假设把结果存放于A52单元格。

2) 求A1到A50区域中成绩为90~99.5分的人数,在存放结果的单元格中输入公式为:=COUNTIF(A1:A50,">=90")-A52;假设把结果存放于A53单元格;

3) 求A1到A50区域中成绩为80~89.5分的人数,在存放结果的单元格中输入公式为:=COUNTIF(A1:A50,">=80")-SUM(A52:A53);假设把结果存放于A54单元格;

3.2 统计成绩表中男、女生人数

在存放结果的单元格中输入公式为:COUNTIF(开始格:结束格,"男"),则在存放结果的单元格中返回性别为男性的人数。

3.3 统计成绩表中的优秀率

在存放结果的单元格中输入公式为:=SUM(A52:A54)/总人数*100。

在存放结果的单元格中返回值即为优秀率,即80分以上学生所占的比例。

3.4 统计成绩表中的及格率

在存放结果的单元格中输入公式为:=SUM(A52:A56)/总人数*100。假设A55和A56单元格分别存放的是70分以上和60分以上的学生人数。

则存放结果的单元格中返回值即为及格率,即60分以上学生所占的比例。

3.5 统计成绩表中的学生成绩上下浮动情况,即求标准差

在存放结果的单元格中输入公式为:=STDEV(A1:A50)

则存放结果的单元格中返回值即表示成绩波动情况,数值越小,说明学生间的成绩差异较小,反之,说明学生的成绩存在两极分化。

3.6 统计成绩表中同时符合多重条件的人数

例:如果想统计成绩表中,性别为男,语文成绩在90分以上,数学成绩在80分以上的学生人数。假设A1-A50存放性别信息,B1-B50存放语文成绩,C1-C50存放数学成绩,则在要存放结果的单元格中输入公式:=SUM(IF((A1:A50="男")*( B1:B50〉90)*( C1:C50>80),1,0)),输入完公式后按Ctrl+Shift+Enter组合键,让它自动加上数组公式符号”{}”。

则在存放结果的单元格中返回值即为同时符合多重条件的人数。

在Excel中有很多功能强大的函数,例如各种概念分布统计函数、各种数学运算函数、

各种财务函数等等。在工作中根据工作性质的不同会用到不同的函数,我们在使用时可以通过查阅Excel帮助来学习函数的使用,通过Excel中的函数可以帮助我们轻松实现数据分析和管理。

参考文献:

[1] 冯博琴,姚普选.计算机文化基础教程[M].北京:清华大学出版社,2001.

[2] 韩小良.Excel高效办公应用大全[M].北京:中国铁道出版社,2008.

上一篇:计算机实验室的维护与管理 下一篇:浅析电子商务的网络安全