实例6 销售业绩统计分析表——公式及函数应用
【学习目标】
·掌握公式定义方法与单元格引用
·掌握Excel 常用运算符
·掌握Excel 公式格式
·掌握常用函数
【任务描述】
本学期的学习已经结束,学校决定为各班发放奖学金。 现在需要统计本期各班的得奖情况,并根据得奖情况计算出每班的得奖比例,然后根据比例发放奖学金。
【操作步骤】
1.计算班级得奖人数、班级总人数、各类别奖项得奖总人数。
Excel2010 用求和函数对所选单元格数据进行求和,只需选中要计算的区域,自动计算结果即能得到,其余各项的合计可以利用自动填充完成。 选中C16 单元格,然后单击“开始”选项卡中“编辑”组中的“自动求和”,选择常用函数“sum()”,如图6-1 所示。 最后选择求和数值范围“C5:C15”进行求和,如图6-2 所示。
图6-1 选择求和函数
图6-2 自动填充求和
2.利用公式求班级得奖人数比例和班级占全校得奖比例。
班级得奖人数比例是指每个班级的得奖人数占班级总人数的比例。 在表格中就是G列/C 列,也是公式应用中相对引用的问题,把光标定位在H5 单元格,输入“=”,再用鼠标单击G5 单元格,输入符号“/”,最后,将鼠标单击C5 单元格,这时,在编辑栏中可以看见输入完毕的公式“=G5/C5”,如图6-3 所示。
图6-3 比例计算公式
单击编辑栏中的
,在H5 单元格中得到计算结果“18.4%”。 如果看到的数据不是百分比,则可以先选中H5 单元格,单击“开始”选项卡下“数字”组中的“百分比”按钮
,就能将计算结果置换成百分比的样式。
第6 行到15 行的数据,可以利用前面学过的自动填充柄功能来计算完成。 当利用自动填充柄把H5 单元格中的公式拖动到H6 单元格时,可以看到此时编辑栏中的公式变成了“=G6/C6”,如图6-4 所示。
图6-4 自动填充柄功能
3.计算班级得奖人数占全校得奖人数的比例。
班级得奖人数占全校得奖人数的比例,是指每个班级里得到奖励的人数占全校得奖总人数的百分比。 在表格中,就是把每个班级的G 列数据,与全校得奖人数的G16 单元格中的数据相比较。 每个班级的得奖人数是不同的,而全校的总的得奖人数是固定的,即G16 单元格中现在的数据“85”是固定的。 这就是公式应用中的绝对引用。
把光标定位在I5 单元格,在编辑栏中输入“=G5/$ G $16”,如图6-5 所示,这里的两个“$”符号表示G16 单元格式的绝对引用,它不会随着自动填充柄的移动而改变。
单击编辑栏前面的
,即可在I5 单元格中得到I5 单元格的计算结果“8.2%”。 对于第6 行到第15 行的数据,可以用自动填充柄的功能来完成。 可以看到,公式中的单元格位置变成“G7/$G$16”“G8/$G$16”“G9/$G$16”……分母中的G16 是不变动的,这就是绝对引用的效果。 如图6-6 所示。
图6-5 绝对引用的输入
图6-6 公式绝对引用的效果
【知识链接】
1.公式的定义。
在Excel 中输入公式时首先输入“=”,简单的公式有加、减、乘、除等运算,复杂一些的公式可能包含函数、引用、运算符和常量等。 在编辑栏或单元格中输入“=”及数据后按Enter 键或鼠标单击编辑栏左侧的“√”完成公式定义;按Esc 键或单击编辑栏的“×”按钮取消公式。
公式定义完成后,可通过公式复制来实现所有数据的计算。 复制公式的方式如下:
(1)通过复制、粘贴实现公式复制。
(2)通过“ctrl+D”向下复制公式或按“Ctrl+R”向右复制公式。
(3)通过自动填充复制公式。
2.公式中的运算符。
(1)算术运算符。
常见的算术运算符如表6-1 所示,用于完成基本的数学运算,返回的是一个数值。 算术运算的结果如图6-7 所示。
表6-1 算数运算符
图6-7 算术运算的结果
(2)比较运算符(如表6-2 所示)。
表6-2 比较运算符
比较运算符返回的是TRUE(真值)或FALSE(假值),如图6-8 所示。
图6-8 比较运算的结果
(3)文本连接运算符。
文本连接运算符是把多个文本连成一个文本,如图6-9 所示。
图6-9 文本连接运算符
3.单元格的引用。
在Excel 中第一个公式完成后,可以利用公式复制来完成其他同样的计算。 公式复制后,计算的结果不一定正确,关键是看公式中单元格的引用格式是否正确。 以A1 单元格为例看公式中单元格引用的格式,如表6-3 所示。
表6-3 单元格引用的含义
4.常用函数。
函数格式:=函数名称(函数)。 注意“=”是必不可少的,参数一般采用单元格或区域的表示法,按Enter 键结束。
例如,用函数输入法计算所有学生的总成绩。 单击G3 单元格(计算王小英),输入“=SUM(C3:F3)”,按Enter 键。 其中“C3:F3”是求和函数SUM 的参数,表示从C3 单元格到F3 单元格的区域。
Excel 计算中经常用到函数,如表6-4 所示。
表6-4 常用函数
【课后习题】
联谊科技有限公司要发放12 月份员工的工资,现在请大家为公司制作工资发放表。具体要求如下:
1.利用sum 函数计算“应发合计”,包含基本工资、奖金、工龄工资和各项补助。
2.计算“养老保险”,为“基本工资”的8%。
3.计算“医疗保险”,为“基本工资”的2%。
4.计算“住房公积金”,为“基本工资”的12%。
5.计算“其他扣缴”,为“基本工资”的1%。
6.计算“实发工资”,为“应发合计”减去“养老保险”“医疗保险”“住房公积金”“其他扣缴”。
7.完成其他格式设置,本任务制作的效果如图6-10 所示。
图6-10 工资表效果图