7.1 制作员工培训成绩统计表
利用Excle工 作表.除了可进行数据录入与储存外,还可对录入的数据进行运算。
下面以制作员工培训成绩表为例,介绍如何运用“公式”与“函数”功能来计符和统计
数据。
7.1.1 使用公式输入数据
用户在输人数据时,可适当利用公式来输入。下面介绍操作方法。
1.根据身份证号输入员工性别
身份证号码的倒数第二位数字代表着人们的性别, 当数字为奇数时,性别为男;当
数字为偶数时,性别为女。下面将利用函数计算出员工性别。
(1) 指定结果单元格
打开“员工成绩表xlsx" 素材文件,选中C2 单元格,如下图所示。
(2) 插入函数
切换至“公式”选项卡,在“函数库”选项组中单击“插入函数”按钮,如下图所示,
打开“插入函数”对话框。
(3)选择函数
在“选择函数”列表框中,输入函数选项"If", 如下图所示。
(4)输入函数参数
在“函数参数”对话框中,将Logical_test 设为"isodd (mid (F'3 , 18, 1) )", 将
Value_if_true 设为“男",将Value_if_false 设为“女”,如下图所示。
知识加油站
ISODD 函数概述
ISODD 函数主要用来测试参数的奇偶性。ISODD 语法表达式为ISODD
( number ) 。其中, number 表示需要进行检验的数值, 该数值可以是具体的数字,
也可以是指定单元格。当数值为奇数,函数返回结果TRUE , 否则返回FALSE.
当单元格为空白,则当做0 检验,函数返回TRUE; 当参数是非数值类型,函
数将返回错误值#VALUEL 。当ISODD 函数和IF 函数结合使用时,还可以提供
—种检验公式中错误的方法。
(5) 完成计算操作
输入完成后,单击“确定”按钮,此时在结果单元格C2 中,可显示计算结果,如
下图所示。
知识加油站
引用单元格公式的操作
所谓引用,是指引用相应的单元格或单元格区域中的数据, 而不是具体的
数值。需注意的是,使用引用单元格地址后,当单元格中数据发生变化时,无
须更改公式, 因为公式会自动根据用户改变后的数据重新进行计算。
(6)填充公式
选择C2 : C22 单元格区域,在“开始”选项卡的“编辑”组中,单击“填充“下拉
按钮,选择“ 向下”选项,如下图所示。
(7) 完成其他单元格公式填充
此时C2 单元格中的公式已经被引用至被选单元格中了,如下图所示。
2.根据身份证号输入员工出生年月
身份证号的第7 - 14 位显示的是公民出生年月日,想要将这些数据快速转换成所需
日期,可通过MID 函数进行操作,方法如下。
(1) 插入“日期与时间”函数
选中E2 单元格,在“插入函数”对话框中,将”或选择类别”设为“日期与时
间”选项,在“选择函数”列表中选择函数DATE, 如上图所示。
(2) 设置函数参数
在“函数参数”对话框中,将Year 设为"mid (f'3, 7, 4)" , 将Month 设为"mid ( f'3,
11 , 2)", 将Day 设为"mid (13 , 13 , 2)",如下图所示。
(3)查看计佯结果
单击“确定”按钮,此时在结果单元格E3 中即可显示计符结果, 如下图所示。
( 4 ) 复制公式
选择E2:E22 单元格区域,单击“向下”
填充按钮,完成公式的复制操作,如上图所示。
(5)查看单元格公式
单击计算结果单元格,此时在表格上方的公式编辑框中,会显示该单元格所引用的
公式,如下图所示。
(6) 修改公式
双击所需单元格,在公式编辑框中修改引用的公式,按[ Enter ] 键确认修改。
3.运用公式输入员工年龄
输入员工出生年月后,可使用IF 函数快速输入员工年龄,具体操作如下。
( 1 ) 插入日期与时间函数
选中02 结果单元格,在“插入函数”对话框中,将”或选择类别”设为“日期
与时间” 选项,将“选择函数”设为函数YEAR, 如下图所示。
(2) 设置函数参数
将Serial_number 设为"工ODAY ()" ,如下图所示。
(3) 显示当前年份
单击“确定”按钮, 此时D2 单元格中显示当前年份,如下图所示。
(4) 在公式栏中输入减号
在公式编辑栏中当前公式后输入减号"-", 如下图所示
(5) 再插入日期与时间函数
再次单击“插人函数”按钮,在打开的“插入函数”对话框中同样插入“日期与时间”
函数,并选择函数YEAR。
(6) 输入函数参数
在“函数参数”对话框中,将Serial_number 设为"e3", 如下图所示。
(7) 设置数值格式
单击“确定”按钮,然后在“开始”选项卡的“数字”组中,单击“数字格式”下
拉按钮,选择“文本”选项,如下图所示。
(8)完成计算操作
设置完成后,在D2 单元格中即可显示员工年龄,如下图所示。
(9) 复制引用公式
选中D2:D22 单元格区域,单击“向下”填充按钮,将公式引用至剩余单元格中,如
下图所示。
疑难解答
问:如何直接输入公式?
答:用户可使用“插入函数”功能进行计算,也可直接在结果单元格中输
入相关公式。需要注意的是,输入公式前,务必先愉入“=’。
7.1.2 使用基本公式进行计算
在一些统计表格中,用户经常会遇到对表格数据进行简单运算的问题,例如求和运
算、平均值运算等。
1. 计算平均值
在Excel 2010 中求平均值的运算有两种方法,下面分别进行介绍。
(1) 选择平均值函数
选中L2 结果单元格,切换至“公式”选项卡, 在“函数库”组中单击“自动求和“
下拉按钮,选择“平均值”选项,如下图所示。
(2) 选择引用单元格
此时在L2 单元格中已自动显示平均值公式,然后选择好单元格区域,这里为默认
选择,如下图所示。
(3) 查看计算结果
按[ Enter] 键,此时在L2 单元格中已显示结果,如下图所示。
(4) 插入平均值函数
选中L3 结果单元格,单击“插入函数”按钮,在“插入函数”对话框中,将”或选
择类别' 设为“常用函数”,在“选择函数”列表中,选择函数AVERAGE, 如下图所示。
(5) 单击选取按钮
在“函数参数”对话框中,单击Numbed 文本框右侧的选取按钮,如下图所示。
(6) 选择参数
在表格中选择参数区域,这里选择G3:K3 单元格区域,如下图所示
(7) 完成计并操作
再次单击文本框右侧的选取按钮, 返回"函数参数”对话框,此时在Numbed 文本
框中已显示了参数区域,单击“确定”按钮即可完成计算,如下图所示。
(8) 复制公式
选中I3: I22 单元格区域,单击“向下”填充按钮,复制求平均值公式至其他单元格
内,如下图所示。
2.计算求和值
在Excel 2010 中,对数据进行求和的方法与求平均值的方法类似,操作如下。
(1) 选择自动求和功能
选择M3 结果单元格,在“公式”选项卡的“函数库”组中,单击“自动求和”按钮,
如下图所示。
(2) 选择引用单元格
此时需重新选择区域,在此选择G3:K3 单元格区域,如下图所示。
(3) 复制公式
按[ Enter ] 键,然后将求和公式复制到其他单元格中,如下图所示
(4) 打开''Excel 选项”对话框
单击“文件”标签,选择“选项”命令,打开"Excel 选项”对话框,如下图所示。
(5) 选择相关选项
在左侧列表中选择“高级”选项,在“此
工作表的显示选项”中, 取消选择“在具有零值的单元格中显示零”选项,如上图所示。
(6) 隐藏零数值
选择完成后, 单击“ 确定”按钮, 此时该工作表中所有值为零的单元格, 其零将被
隐藏, 如下图所示。
3.计算最大值、最小值
想要快速对表格数据中的最大值、最小值进行统计, 用户可使用Excel 中的MAX 、
MIN 函数进行操作,方法如下。
(1)启动攸大值函数
选中J24 单元格, 在“公式”选项卡的"函数库” 组中,单击“自动求和“ 下拉按钮,
选择“最大值” 选项,如下图所示。
(2) 选择引用单元格
在该工作表中,选择M3 : M22 单元格区域, 如下图所示。
知识加油站
#DIV/ 0 ! 错误提示
当在结果单元格中出现字符#DIV/0 !时, 表示除数为0 , 结果无意义。此时
需查看该公式引用单元格的数据是否有误。
( 3) 完成计算
按【Enter 】键,此时在J24 单元格中即可显示计算结果, 如下图所示
(4) 启动最小值函数
选中J25 结果单元格,单击“自动求和“下拉按钮,选择“最小俏”选项,如下图
所示。
(5) 选择引用单元格
在工作表中按住[ Ctrl ] 键选择M3 :M7 , M9: M10, M12 , M14: M16 , M18:
M22 单元格区域,如下图所示。
(6) 完成计并
选择完成后,按[ Enter ] 键,完成计算操作,结果如下图所示。
7.1.3 计算名次
如果想要将表格中的数据进行排名,可使用PANK 函数进行操作,具体方法如下。
(1) 启动RANK 函数
选中N3 结果单元格,单击“插入函数”按钮,在打开的对话框中选择函数RANK,
如下图所示。
(2) 设置函数参数
单击“确定”按钮,在“函数参数”对话框中,将Number 设为"M3", 将Ref 设为
"$M$3 : $M$22", 如下图所示。
(3)完成计算
输入完毕后,按[ Enter 】键完成计算,然后选中N3: N22 单元格区域,并单击“向
下”填充按钮,将公式复制到其他单元格中,如下图所示。.
7.1.4 统计员工参考人数
有时统计表格中的数据时,用户需使用统计函数。下面将介绍具体操作。
(1)插入COUNTA函数
选中N24结果单元格,在“公式’选项卡的“函数库”组中.单击“其他函数”
按钮.选择“统计>COUNTA”选项,如下图所示。
(2) 设置函数参数
在“函数参数”对话框中,将ValueI 设为"M3: M22", 如下图所示,单击“确定”
按钮,在N24 单元格中即可显示计算结果。
(3) 插入COUN工BLANK 函数
选中N25 结果单元格,在“其他函数”列表中,选择“统计> COUNTBLANK " 选项,
如下图所示。
(4)设置函数参数
在打开的对话框中,将Range 设置为"K3: K22", 如下图所示。
(5) 完成计算
选择完成后即可完成计算,结果如下图所示。