导入工资数据计算薪金生成工资条.docx

导入工资数据计算薪金生成工资条.docx

《导入工资数据计算薪金生成工资条.docx》由会员分享，可在线阅读，更多相关《导入工资数据计算薪金生成工资条.docx（11页珍藏版）》请在冰点文库上搜索。

导入工资数据计算薪金生成工资条

课程名称：

《Excel在会计中的应用》

授课班级

会计1、2、3、4、5、6班

授课日期

课题

薪酬管理系统——导入工资数据

学时

2

教

学

目

标

知识目标了解企业职工薪酬的结构

熟悉查找引用函数的运用

熟悉条件判断函数的运用

技能目标能正确计算职工工资

素质目标培养学生运用Excel管理企业职工薪酬的能力

教学重点难点及突破

查找引用函数的运用

条件判断函数的运用

职工工资结算单的创建

教学资源

教材、多媒体、Excel帮助文件、参考书

教

学

环

节

安

排

一、回顾上节课内容

二、新课引入

三、本节内容讲解

四、课堂知识总结

五、师生互动，学生提问，教师答疑

六、布置课后作业——复习、预习

教

学

后

记

重庆财经职业学院教案

教学步骤与内容

教学随记

1、回顾上节课内容

薪酬管理系统的结构及创建步骤

名称的定义

2、新课引入——薪酬管理系统

工作情景与分析

李娜是山东丰源公司财务人员，现需设计公司的薪酬管理系统。

山东丰源公司有5个部门：

企划部、设计部、财务部、销售部、生产部，员工12人。

每个员工的工资包括基本工资、岗位工资、职务津贴、奖金、事假扣款、病假扣款、住房公积金和个人所得税。

员工的基本信息情况、考勤、业绩情况见表。

（见课件，在此补展示）

根据该公式具体情况及职工工资的组成部门分析薪酬管理系统的结构及创建步骤：

任务一、薪酬系统基本表格的建立

职工基本情况表、职工基本工资表、职工福利表、职工考勤表、职工社会保险表、职工业绩考核表。

表格结构见课件。

任务二、计算薪金、生成工资条

Ø本任务需要确定各类计算比率

Ø创建工资结算单，导入工资数据

Ø职工工资结算单是由职工基本工资表、职工福利表、职工社会保险表、职工考勤表、职工业绩考核表中的各项数据组合而成，如果采用逐一填入数据，工作会非常繁琐，而且容易出错。

因此可以利用Excel提供的“定义数据名称”功能和“函数”功能从各个表中提取数据，简化操作。

Ø生成工资条

Ø

三、知识点的讲解

常用函数—查找和引用函数

MATCH函数

函数功能：

用于确定查找值在指定单元格区域中的位置序号，该函数主要是对查找数据进行定位，当数据存在时，则返回具体的位置序号，否则返回“#N/A”错误。

因此也可以用于判断数据是否存在。

语法格式：

MATCH（lookup_value,lookup_array,match_type）

说明：

Lookup_value代表要在数据表中查找的数值Lookup_array表示可能包含所要查找的数值的连续单元格区域；Match_type表示查找方式（-1、0或1）

如果为-1，则查找大于或等于lookup_value的最小数值，Lookup_array必须按降序排列；如果为1，则查找小于或等于lookup_value的最大数值，Lookup_array必须按升序排列；如果为0，查找等于lookup_value的第一个数值，Lookup_array可以按任何顺序排列；如果省略match_type，则默认为1。

LOOKUP函数

功能：

返回向量或数组中的数值。

函数LOOKUP（向量形式）

功能：

在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。

语法格式：

LOOKUP（lookup_value,lookup_vector,result_vector）

Lookup_value（参数一）   为函数在第一个区域中所要查找的数值。

该参数可以为数字、文本、逻辑值或包含数值的名称、引用。

Lookup_vector（参数二）   为包含查找值的一行或一列单元格区域。

如希望查找一个明确的值要求该单元格区域必须按升序排序，否则不能返回正确的结果，但当要查找一个不确定的值，不需要排序。

Result_vector （参数三）  为行或列单元格区域，其大小必须与参数二相同。

说明：

如果函数LOOKUP找不到lookup_value（查找值），则查找lookup_vector（参数二）中小于或等于lookup_value的最大值。

如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。

应用举例：

查询申购基金的申购手续费

下例中模拟了一份基金购买的申购费率表，其中左表为购买基金的费率标准，右表D1：

D4区域为根据标准转换得到的数值关系表，下面的公式将根据购买者的申购金额来计算应付的申购基金手续费。

函数LOOKUP（数组形式）

功能：

在数组的第一列（或第一行）中查找指定数值，然后返回最后一列（或最后一行）中相同位置处的数值。

语法格式：

LOOKUP（lookup_value,array）

Lookup_value  为要查找的数值。

该参数可以为数字、文本、逻辑值或包含数值的名称或引用。

Array 为一单元格区域。

区域中的数值必须按升序排序

应用举例：

从成绩表中查询学员的总成绩

下图展示了某次考试的成绩表，下面使用LOOKUP函数来查询学员的总成绩。

公式说明：

假设F12单元格为所选择的学员姓名“钱洋”,则在F13单元格中将显示该学员的成绩，在F13单元格中的公式为：

=LOOKUP（$F$12,$B$3:

$I$10）

该公式利用LOOKUP在二维区域中的查找原理，在B3：

I10区域中最左列进行姓名查找，并返回二维区域中最后一列相同位置的总成绩。

VLOOKUP,HLOOKUP使用频率最高的函数。

利用它们，可以从员工资料表中查找一员工所属部门，电话等信息。

共同点：

第1个参数：

支持通配符（“？

”“*”）进行查询，但不能是数组。

第2个参数：

可以是单元格引用区域，还有数组。

第4个参数，决定函数的查找方式，为0或FLASE精确查找。

同时支持无序查找，为1或TURE模糊匹配方式查找。

函数VLOOKUP

函数功能：

在指定数据区域的首列中搜索满足条件的数据，并返回当前行中指定列号对应的值。

语法格式：

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）

参数说明：

Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值……）；

Range_lookup为逻辑值，如果为TRUE或省略，则必须按升序排列区域中第一列的值，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，不需要排序，则返回精确匹配值，如果有多个使用第一个找到的值，如果找不到，则返回错误值#N/A。

函数HLOOKUP

函数功能：

在指定数据区域的首行中搜索满足条件的数据，并返回当前列中指定行号所对应的值。

语法格式：

HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）

使用说明：

Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Row_index_num为在table_array区域中待返回的匹配值的行序号（当Row_index_num为2时,返回table_array第2行中所对应的数值，为3时，返回第3行中所对应的值……）；Range_lookup为逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。

应用举例：

利用HLOOKUP函数查询产品月销售量，下图展示了某公式年度产品销售情况表，下面的公式将查询“产品D”在某月的销售量。

B3单元格为查询月份“8”，B4单元格公式为：

=HLOOKUP（B3,$D$2:

$P$8,5,0）

如果在B7单元格输入文本类型的数值10，即使查询公式不变，结果却返回“#N/A”错误，这是因为B7单元格的查找值（文本）与查找范围中首行的数据（数值）的数据类型不一致，因此，可将公式进行修改，以使其返回正确的结果=HLOOKUP（B7*1,$D$2:

$P$8,5,0）

注：

某些情况下，可能忽略了查找值与查找范围的数据类型不一致的情况，从而导致查询失败，因此，在使用些类查询函数进行查询时，查找条件与查找范围的首列或首行的数字格式必须保持一致，如使用了文本函数（LEFT、MID、RIGHT等）来提取查找条件中的字符串，其结果为文本，也需注意数据类型的转换。

LOOKUP函数与HLOOKUP和VLOOKUP的区别:

函数LOOKUP的数组形式与函数HLOOKUP和函数VLOOKUP非常相似。

不同之处在于函数HLOOKUP在第一行查找lookup_value，函数VLOOKUP在第一列查找，而函数LOOKUP则按照数组的维数查找。

如果数组所包含的区域宽度大，高度小（即列数多于行数），函数LOOKUP在第一行查找lookup_value。

如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数LOOKUP在第一列查找lookup_value。

函数HLOOKUP和函数VLOOKUP允许按行或按列索引，而函数LOOKUP总是选择行或列的最后一个数值。

函数名称：

OFFSET

功能：

以指定的引用为参照系，通过给定偏移量得到新的引用。

返回的引用可以为一个单元格或单元格区域。

并可以指定返回的行数或列数。

语法格式：

OFFSET（reference,rows,cols,height,width）

Reference   作为偏移量参照系的引用区域。

Reference必须为对单元格或相连单元格区域的引用；否则，函数OFFSET返回错误值#VALUE!

。

Rows   相对于偏移量参照系的左上角单元格，上（下）偏移的行数。

如果使用5作为参数Rows，则说明目标引用区域的左上角单元格比reference低5行。

行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。

Cols   相对于偏移量参照系的左上角单元格，左（右）偏移的列数。

如果使用5作为参数Cols，则说明目标引用区域的左上角的单元格比reference靠右5列。

列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

Height   高度，即所要返回的引用区域的行数。

Height必须为正数。

Width   宽度，即所要返回的引用区域的列数。

Width必须为正数。

如果省略参数高和宽，则系统认为其高和宽度与引用基点相同。

IS类函数

功能：

IS类函数，可以检验数值的类型并根据参数取值返回TRUE或FALSE。

例如，如果数值为对空白单元格的引用，函数ISBLANK返回逻辑值TRUE，否则返回FALSE。

语法格式：

ISBLANK（value）ISERR（value）ISERROR（value）ISLOGICAL（value）

ISNA（value）ISNONTEXT（value）ISNUMBER（value）ISREF（value）

ISTEXT（value）

说明：

Value 为需要进行检验的数值。

分别为：

空白（空白单元格）、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。

函数

如果参数值为下面的内容，则返回TRUE

ISBLANK

值为空白单元格。

ISERR

值为任意错误值（除去#N/A）。

ISERROR

值为任意错误值（#N/A、#VALUE!

、#REF!

、#DIV/0!

、#NUM!

、#NAME?

或#NULL!

）。

ISLOGICAL

值为逻辑值。

ISNA

值为错误值#N/A（值不存在）。

ISNONTEXT

值为不是文本的任意项（注意此函数在值为空白单元格时返回TRUE）。

ISNUMBER

值为数字。

ISREF

值为引用。

ISTEXT

值为文本。

四、课堂知识总结

五、师生互动，学生提问，教师答疑

六、布置课后作业——复习、预习