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

1、32”等等。（2）VLOOKUP函数在表格或数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）Lookup value为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。col_index_num为table_array 中待返回的匹配值的列序号。col_index_num 为 1 时，返回 table_array 第一列的数值，col

2、_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 将查找精确匹

3、配值，如果找不到，则返回错误值 #N/A。二、创建员工工资核算系统1建立工资明细表（1）新建Excel工作薄，将其命名为“工资核算系统”。然后双击工作表标签Sheet1将其重命名为“工资明细表”。（2）输入标题及各个工资项目，并对输入的内容格式化，如图1所示.（3）为了防止输入错误，对“所属部门”列进行“数据有效性”控制。选中单元格C4，单击“数据”、“有效性”命令，弹出“数据有效性”对话框。（4）在“允许”下拉框选中“序列”，然后在“来源”文本框中输入“企划部,财务部,销售部,生产部”，注意各个部门名称应用英文状态下的逗号隔开。（5）单击“确定”按钮，返回工作表，然后拖曳C4的填充柄将有效性

4、控制填充到该列的其他单元格。（6）输入员工的其他相关信息，最终结果如图2所示。图1 工资明细表图2 输入其他相关信息2.统计部门员工人数使用COUNTIF函数统计部门人数（1）双击“工资核算系统”工作簿中的工作表标签sheet2，将其重命名为“基本资料表”。（2）打开“工资明细表”，将其中的主要数据复制到基本资料表中并补充相应信息，按部门排序，并输入其他信息，结果如图3所示。图3 输入基本资料表中其他信息（4）选择单元格区域A4:G7，单击“插入”、“名称”、“定义”命令，在弹出的“定义名称”对话框中的“在当前工作簿中的名称”文本框中输入“财务部”，单击“添加”按钮，已选中的区域就被定义为“财

5、务部”了。如图4所示。图4 定义财务部（4）在该对话框的“在当前工作簿中的名称”文本框中输入“企划部”，单击下方的“引用位置”文本框右边的按钮，在基本资料表中选择区域A8：G11，再次单击该按钮，回到“定义名称”对话框。（5）单击“添加”按钮，以选中的区域被定义为“企划部”，用同样的方式分别定义A12：G14为“生产部”，A15：G18为“销售部”，单击“确定”按钮。这样就定义好了四个部门。（6）在“工资核算系统”工作簿中新建“部门统计”工作表，如图6所示。图6 “部门统计”工作表（7）单击单元格C5，单击工具栏中的按钮fx，弹出“插入函数”对话框，在“或选择类别”下拉列表框中选择“统计”选项

6、，在“选择函数”列表框中选择COUNTIF函数，。（8）单击“确定”按钮，弹出“函数参数”对话框，在“Range”文本框中输入“财务部”，在“Criteria”文本框中输入“男” ，如图7所示。（9）单击“确定”按钮，单元格C5中就会显示出财务部男员工的人数0。（10）按同样的方法统计出财务部女员工的人数，只需在“函数参数”对话框中将“Criteria”文本框中的“男”改为“女”即可。图7 统计财务部女员工人数（11）选中单元格D5，在编辑栏中输入公式“=COUNTIF（基本资料表！E4：E8，“财务部”） ”。按回车键，单元格D5即可显示财务部的总人数4。统计财务部的总人数也可以用SUM函数

7、计算，即在编辑栏中输入公式“=C5+C6”。（12）按照同样的方法，统计其他部门的人数。（13）选中单元格D13，在编辑栏输入公式“=SUM（D5：D12）”，按回车键，公司总人数就计算出来了。结果如图8所示。图8 统计公司人数3.统计员工年假假设该公司规定，任职满一年的员工，年假为15天，以后工龄每增加一年，年假增加一天，满6-10年年假均为20天，满10年后均为30天。（1）在“工资核算系统”工作簿中新建一张工作表，并将其重命名为“年假规则”，以方便统计年假，如图9所示。图9 “年假规则”工作表（2）选中作为规则的单元格区域，在插入“定义名称”对话框中将其定义为“年假规则”，如图10所示。

8、图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，

9、用同样的方法将公式填充到该列的其他单元格中，即可自动显示其他员工的年假天数，如图15所示。图14 输入年假天数计算公式图15 计算其他员工的年假天数4.自动更新基本工资每个员工都会有调整基本工资的时候，但是个人情况不同，其基本工资调整的时间和幅度也不一样，每月计算工资时，不可能把所有人的调薪纪录都查找一遍，因此就有必要建立一个自动更新的数据库，以方便、准确、及时地更新数据。在EXCEL中可以用列查找函数VLOOKUP来自动更新每位员工的基本工资。 （1）在“工资核算系统”工作簿中创建一张新的工作表，命名为“工资调整”。 （2）输入标题，然后切换到“基本资料表”，将其中的部分信息复制到“工资调整

10、”工作表，其中包括员工编号、姓名、性别、入职时间、所属部门和职工类别。 （3）在该表中的G3：I18单元格中添加如图16所示的项目和内容。图16 添加工资调整数据（4）全部选中将“工资调整”工作表进行排序，以“员工编号”为主要关键字按升序排序，“调整年”为次要关键字按降序排序，“调整月”为第三关键字按降序排序，排序结果如图17所示。图17 对工作表进行排序（5）单击“插入”、“名称”、“定义”命令，在弹出的“定义名称对话框中定义单元格区域A3：I18为“工资调整”，如图18所示。图18 定义单元格区域（6）切换到“工资明细表”，删除原基本工资数据。选中单元格D4，输入公式“=VLOOKUP（A

11、4,工资调整,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 计算其他员工加班费第一次课讲到这里为止!里面的数据已更新，后面的还没更新