公式熟悉第1节.docx
- 文档编号:2468411
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:12
- 大小:1.54MB
公式熟悉第1节.docx
《公式熟悉第1节.docx》由会员分享,可在线阅读,更多相关《公式熟悉第1节.docx(12页珍藏版)》请在冰豆网上搜索。
公式熟悉第1节
工资管理
一、常用函数介绍
(1)COUNTIF函数
功能:
计算区域中满足给定条件的单元格个数。
语法:
COUNTIF(range,criteria)
说明:
range为需要计算其中满足条件的单元格数目的单元格区域。
Criteria为确定哪些单元格将被计算在内的条件,形式可以为数字、表达式或文本。
例如,条件可以为“32”、“>32”等等。
(2)VLOOKUP函数
功能:
在表格或数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。
语法:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
说明:
Lookupvalue为需要在数据表第一列中进行查找的数值。
Lookup_value可以为数值、引用或文本字符串。
Tablearray为需要在其中查找数据的数据表。
使用对区域或区域名称的引用。
col_index_num为table_array中待返回的匹配值的列序号。
col_index_num为1时,返回table_array第一列的数值,col_index_num为2时,返回table_array第二列的数值,以此类推。
如果col_index_num小于1,函数VLOOKUP返回错误值#VALUE!
;如果col_index_num大于table_array的列数,函数VLOOKUP返回错误值#REF!
。
Range_lookup为一逻辑值,指明函数VLOOKUP查找时是精确匹配,还是近似匹配。
如果为true或省略,则返回近似匹配值。
也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值。
如果range_lookup为false或0,函数VLOOKUP将查找精确匹配值,如果找不到,则返回错误值#N/A。
二、创建员工工资核算系统
1.建立工资明细表
(1)新建Excel工作薄,将其命名为“工资核算系统”。
然后双击工作表标签Sheet1将其重命名为“工资明细表”。
(2)输入标题及各个工资项目,并对输入的内容格式化,如图1所示.
(3)为了防止输入错误,对“所属部门”列进行“数据有效性”控制。
选中单元格C4,单击“数据”、“有效性”命令,弹出“数据有效性”对话框。
(4)在“允许”下拉框选中“序列”,然后在“来源”文本框中输入“企划部,财务部,销售部,生产部”,注意各个部门名称应用英文状态下的逗号隔开。
(5)单击“确定”按钮,返回工作表,然后拖曳C4的填充柄将有效性控制填充到该列的其他单元格。
(6)输入员工的其他相关信息,最终结果如图2所示。
图1工资明细表
图2输入其他相关信息
2.统计部门员工人数
使用COUNTIF函数统计部门人数
(1)双击“工资核算系统”工作簿中的工作表标签sheet2,将其重命名为“基本资料表”。
(2)打开“工资明细表”,将其中的主要数据复制到基本资料表中并补充相应信息,按部门排序,并输入其他信息,结果如图3所示。
图3输入基本资料表中其他信息
(4)选择单元格区域A4:
G7,单击“插入”、“名称”、“定义”命令,在弹出的“定义名称”对话框中的“在当前工作簿中的名称”文本框中输入“财务部”,单击“添加”按钮,已选中的区域就被定义为“财务部”了。
如图4所示。
图4定义财务部
(4)在该对话框的“在当前工作簿中的名称”文本框中输入“企划部”,单击下方的“引用位置”文本框右边的按钮,在基本资料表中选择区域A8:
G11,再次单击该按钮,回到“定义名称”对话框。
(5)单击“添加”按钮,以选中的区域被定义为“企划部”,用同样的方式分别定义A12:
G14为“生产部”,A15:
G18为“销售部”,单击“确定”按钮。
这样就定义好了四个部门。
(6)在“工资核算系统”工作簿中新建“部门统计”工作表,如图6所示。
图6“部门统计”工作表
(7)单击单元格C5,单击工具栏中的按钮fx,弹出“插入函数”对话框,在“或选择类别”下拉列表框中选择“统计”选项,在“选择函数”列表框中选择COUNTIF函数,。
(8)单击“确定”按钮,弹出“函数参数”对话框,在“Range”文本框中输入“财务部”,在“Criteria”文本框中输入“男”,如图7所示。
(9)单击“确定”按钮,单元格C5中就会显示出财务部男员工的人数0。
(10)按同样的方法统计出财务部女员工的人数,只需在“函数参数”对话框中将“Criteria”文本框中的“男”改为“女”即可。
图7统计财务部女员工人数
(11)选中单元格D5,在编辑栏中输入公式“=COUNTIF(基本资料表!
E4:
E8,“财务部”)”。
按回车键,单元格D5即可显示财务部的总人数4。
统计财务部的总人数也可以用SUM函数计算,即在编辑栏中输入公式“=C5+C6”。
(12)按照同样的方法,统计其他部门的人数。
(13)选中单元格D13,在编辑栏输入公式“=SUM(D5:
D12)”,按回车键,公司总人数就计算出来了。
结果如图8所示。
图8统计公司人数
3.统计员工年假
假设该公司规定,任职满一年的员工,年假为15天,以后工龄每增加一年,年假增加一天,满6-10年年假均为20天,满10年后均为30天。
(1)在“工资核算系统”工作簿中新建一张工作表,并将其重命名为“年假规则”,以方便统计年假,如图9所示。
图9“年假规则”工作表
(2)选中作为规则的单元格区域,在插入“定义名称”对话框中将其定义为“年假规则”,如图10所示。
图10定义年假规则
(3)在基本资料表中添加两列——“工龄”和“年假(天)”,如图11所示。
图11添加两列
(4)选中单元格H4,在编辑栏中输入公式“=YEAR(NOW())-YEAR(D4)”,在格式设置单元格中选择“数值”,小数点为“0”,按回车键后计算结果为12,如图12所示。
(5)选中单元格H4,拖曳鼠标填充柄,将公式填充到该列的其他单元格中,释放鼠标时,其他员工的工龄基就自动显示出来了,如图13所示。
图12计算工龄
图13计算其他员工工龄
(6)选中单元格I4,输入公式“=VLOOKUP(H4,年假规则,2,1)”,按回车键后即可得出对应的年假天数,结果如图14所示。
(7)选中单元格I4,用同样的方法将公式填充到该列的其他单元格中,即可自动显示其他员工的年假天数,如图15所示。
图14输入年假天数计算公式
图15计算其他员工的年假天数
4.自动更新基本工资
每个员工都会有调整基本工资的时候,但是个人情况不同,其基本工资调整的时间和幅度也不一样,每月计算工资时,不可能把所有人的调薪纪录都查找一遍,因此就有必要建立一个自动更新的数据库,以方便、准确、及时地更新数据。
在EXCEL中可以用列查找函数VLOOKUP来自动更新每位员工的基本工资。
(1)在“工资核算系统”工作簿中创建一张新的工作表,命名为“工资调整”。
(2)输入标题,然后切换到“基本资料表”,将其中的部分信息复制到“工资调整”工作表,其中包括员工编号、姓名、性别、入职时间、所属部门和职工类别。
(3)在该表中的G3:
I18单元格中添加如图16所示的项目和内容。
图16添加工资调整数据
(4)全部选中将“工资调整”工作表进行排序,以“员工编号”为主要关键字按升序排序,“调整年”为次要关键字按降序排序,“调整月”为第三关键字按降序排序,排序结果如图17所示。
图17对工作表进行排序
(5)单击“插入”、“名称”、“定义”命令,在弹出的“定义名称对话框中定义单元格区域A3:
I18为“工资调整”,如图18所示。
图18定义单元格区域
(6)切换到“工资明细表”,删除原基本工资数据。
选中单元格D4,输入公式“=VLOOKUP(A4,工资调整,9,0)”,按回车键即可显示该员工最新调整的基本工资。
(7)拖曳D4单元格右下角的填充柄,将公式填充到该列其他单元格,即可显示其他员工的最新基本工资,如图19所示。
图19显示最新基本工资
(8)选中单元格D4:
L18,在“单元格格式”对话框中设置包含2位小数位数的数值格式。
5.核算加班费
假设公司规定每天加班时间在2小时以内,加班费为40元;超过2小时,加班费为80元。
按照这个标准计算员工的加班费。
(1)在“工资核算系统”工作簿中创建一个新的工作表,并将其重命名为“加班记录”。
(2)在该表中输入包括员工编号、姓名、性别、所属部门、加班的起止时间等各项信息,并为该表添加边框,将单元格区域A3:
H18定义为“加班记录”,结果如图20所示。
图20“加班记录”工作表
(3)切换到“工资明细表”,在该表中选中单元格G4,输入公式“=IF(HOUR(加班记录!
H4-加班记录!
G4)<=2,40,80)”,按回车键,结果如图21所示。
图21计算加班费
(4)拖曳G4右下角的填充柄,将公式填充到该列的其他单元格,计算出其他员工的加班费,如图22所示。
图22计算其他员工加班费
第一次课讲到这里为止!
里面的数据已更新,后面的还没更新
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 公式 熟悉