Excel在信息管理方面的应用

摘要:伴随着计算机的普及,办公方式正在向无纸化办公方向发展,Excel是Microsoft Office系列办公软件的一个重要组成部分,通过Excel可以进行各种复杂的数据处理、统计分析和辅助决策。广泛应用于管理、统计、金融等多领域。本文介绍了Excel在人事资源管理、学校学生成绩管理方面的应用。

Abstract: As the popularity of computer, office work way is developing into Paperless direction. Excel is an important part of Microsoft Office software. Excel's functions of complex data handling, statistical analysis, decision making aid have be widely used in the field of management, statistics and Finance. Excel's applications in Human resources management, student's Achievement Management in school are introduced.

关键词:Excel;应用;数据;管理

Key words: Excel;application;data;management

中图分类号:TP31 文献标识码:A文章编号:1006-4311(2010)03-0152-01

0引言

利用Excel的数据筛选、查找、排序,数据清单、数据透视表、数据透视图,公式、函数、图表等功能,解决档案、人事、成绩、财务、销售等身边最常遇到的问题,通过对例子的认真解读、剖析和实践,可以举一反三,来解决现实生活中实际存在的问题。

1员工基本情况数据分析

1.1 查询所有女员工(男员工)的信息:用筛选的方法。选择明细表中任一有数据的单元格,然后选择“数据”选项卡,单击“排序和筛选”组中的“筛选”图标按钮,在明细表标题行的每个单元格中都会出现一个下拉按钮,单击下拉按钮,在弹出的下拉菜单中选择相应的筛选条件中后,在明细表中将只显示符合条件的数据记录。单击“性别”所在单元格中的下拉按钮,在弹出的下拉菜单中先取消“全部”复选框的选中状态,然后选择“女”(“男”)复选框。

1.2 统计公司中所有女员工(男员工)的人数:使用COUNTIF函数。选择表格中的空白单元格,选择“公式”选项卡,单击“函数库”组中的“其他函数”按钮,在弹出的下拉菜单中选择“统计”“COUNTIF”命令,打开“函数参数”窗口,在Range文本框中选择引用的表格的区域,在Criteria文本框中输入“”女“”(“男”),即在单元格中显示女员工(男员工)的总人数。

1.3 制作员工生日提醒表:用MONTH函数。先使用COUNTIF函数统计每个月过生日的员工人数。选择空白单元格,输入公式:=COUNTIF($F$3:$F$32,“*01月*”),其中3为表中”出生日期“字段的起始单元格,32为表中“出生日期”字段的终止单元格,此公式计算出在F3:F32单元格区域中字符串中包含“01月”的单元格个数。用同样的方法能计算出2月-12月每个月过生日的员工人数。然后将当月过生日的员工记录突出显示。

选择整个表格区域,再选择“开始”选项卡中的“条件格式”“新建规则”命令,打开“新建格式规则”窗口,在“选择规则类型”列表框中选择“使用公式确定要设置格式”文本框中输入如下公式:=MONTH(TODAY())=NONTH($F3)公式表示F列中的月份与当前的月份相同。单击“格式”按钮,打开“设置单元格格式”窗口,选择“填充”选项卡,在“背景色”区域中选择一种颜色。单击“确定”按钮,回到“新建格式规则”窗口后再次单击“确定”按钮,设置完成后如果“出生日期”列中的月份与当前的月份相同,则对应的员工记录将被填充为所设置的颜色。

1.4 计算员工加班费、事病假扣款:用ROUND函数。选择计算加班费的空白单元格,输入公式:=ROUND($E3/30/8,0)*1.5*$F3,其中$E3为月基本工资,30为每月30天,8为每天8小时工作日,1.5为每小时的工资,$F3是加班时间。同样计算员工“事假扣款”输入公式:ROUND($E3/30,0)*$F3。

2学生成绩分析

2.1 根据学生编号查询考试成绩:使用VLOOKUP函数。选择某学生姓名单元格,输入公式:=VLOOKUP($B$2,学生成绩表!$A$3:$K$32,2,0)公式表示在“学生成绩表”的A3:K32单元格区域中查找与B2单元格匹配的单元格,然后返回该单元格所在行的第2列单元格中的数值。按键,即可返回B2单元格中学生编号所对应的“学生姓名”。

2.2 统计各学科的优秀率、及格率及差分率:用COUNTIF函数。其中“优秀率”:考试成绩在85分以上的学生人数占总人数的比例;“及格率”考试成绩在60分以上的学生人数占总人数的比例;“差分率”考试成绩在60分以下的学生人数占总人数的比例。统计优秀率:选择空白单元格,输入:=COUNTIF(C$4:C$33,“>=85”)/COUNT(C$4:C$33)计算出1门学科的优秀率。及格率和差分率的统计方法和优秀率的方法相同。

2.3 计算某科各分数段的人数:(1)使用FREQUENCY函数统计各分数段的人数。选择空白单元格区域,输入公式:=FREQUENCY(软件工程,分数段),表中必须有“分数段”字段,输入完按下组合键进行确认,在选择的空白单元格中显示出计算的结果。(2)使用COUNTIF函数统计各分数段的人数。使用COUNTIF函数进行计算时所设置的区间分割点与FREQUENCY函数有所不同,其中“99.9”表示考试成绩为满分100的学生数,“89.9”表示考试成绩在90-99之间的学生数,其余的依次类推。任选一科如:高等数学中“人数”空白单元格C4,输入公式:=COUNTIF(高等数学,“>”&B4)公式表示在“高等数学”单元格区域中统计大于B4(指区间分割点字段)单元格的个数。按键,可统计出高等数学分数为100分的人数。选择C5单元格,输入以下公式:COUNTIF (高等数学,“>”&B5)COUNTIF(高等数学,“>”&B4)公式表示“高等数学”单元格区域中大于B5单元格的个数,减去大于B4单元格的个数。按键,可以统计出高等数学分数在99-90分之间的人数。然后将C5单元格中的公式复制到C6:C12单元格区域中,即可统计出各分数段的人数。(3)使用SUMPRODUCT函数统计各分数段的人数。任选一科如:高等数学中“人数”空白单元格C4,输入:=SUMPRODUCT((高等数学=100)*(高等数学>0))公式表示“高等数学”中等于100的单元格与“高等数学”大于0的单元格相乘。按键,可以统计出高等数学分数在100分的学生人数。统计分数在99-90之间的人数和上面的统计方法类似。

2.4 统计某人在班级或年级的成绩排名:使用RANK函数。选择“名次”字段的空白单元格,输入RANK(M4,$M$4:$M$28,0),M4:需要计算排位的第一个数字;$M$4:$M$28:范围,0:按降序排列的数据清单进行排位;如果此项不为0,则按升序排列的数据清单进行排位。在名次字段的空白单元格中计算出该学生的班级或年级的排名。然后再应用自动填充功能就能准确地统计出所有同学的班级或年级排名了。

参考文献:

[1]傅靖,李冬.Excel2007公式、函数与图表宝典[M].北京:电子工业出版社,2008.