公式熟悉第1节Word文件下载.docx

公式熟悉第1节Word文件下载.docx

《公式熟悉第1节Word文件下载.docx》由会员分享，可在线阅读，更多相关《公式熟悉第1节Word文件下载.docx（12页珍藏版）》请在冰豆网上搜索。

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计算其他员工加班费

第一次课讲到这里为止!

里面的数据已更新，后面的还没更新