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

1、查找引用函数的运用条件判断函数的运用职工工资结算单的创建教学资源教材、多媒体、Excel帮助文件、参考书环节安排一、回顾上节课内容二、新课引入三、本节内容讲解四、课堂知识总结五、师生互动，学生提问，教师答疑六、布置课后作业复习、预习后记重庆财经职业学院教案教学步骤与内容教学随记1、回顾上节课内容薪酬管理系统的结构及创建步骤名称的定义2、新课引入薪酬管理系统工作情景与分析李娜是山东丰源公司财务人员，现需设计公司的薪酬管理系统。山东丰源公司有5个部门：企划部、设计部、财务部、销售部、生产部，员工12人。每个员工的工资包括基本工资、岗位工资、职务津贴、奖金、事假扣款、病假扣款、住房公积金和个人所得税

2、。员工的基本信息情况、考勤、业绩情况见表。（见课件，在此补展示）根据该公式具体情况及职工工资的组成部门分析薪酬管理系统的结构及创建步骤：任务一、薪酬系统基本表格的建立职工基本情况表、职工基本工资表、职工福利表、职工考勤表、职工社会保险表、职工业绩考核表。表格结构见课件。任务二、计算薪金、生成工资条 本任务需要确定各类计算比率 创建工资结算单，导入工资数据 职工工资结算单是由职工基本工资表、职工福利表、职工社会保险表、职工考勤表、职工业绩考核表中的各项数据组合而成，如果采用逐一填入数据，工作会非常繁琐，而且容易出错。因此可以利用Excel提供的“定义数据名称”功能和“函数”功能从各个表中提取数据

3、，简化操作。 生成工资条三、知识点的讲解常用函数查找和引用函数MATCH函数函数功能：用于确定查找值在指定单元格区域中的位置序号，该函数主要是对查找数据进行定位，当数据存在时，则返回具体的位置序号，否则返回“#N/A”错误。因此也可以用于判断数据是否存在。语法格式：MATCH（lookup_value,lookup_array,match_type） 说明：Lookup_value代表要在数据表中查找的数值 Lookup_array表示可能包含所要查找的数值的连续单元格区域； Match_type表示查找方式（-1、0或1）如果为-1，则查找大于或等于 lookup_value的最小数值，Lo

4、okup_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（参数一） 为函数

5、在第一个区域中所要查找的数值。该参数可以为数字、文本、逻辑值或包含数值的名称、引用。Lookup_vector（参数二） 为包含查找值的一行或一列单元格区域。如希望查找一个明确的值要求该单元格区域必须按升序排序，否则不能返回正确的结果，但当要查找一个不确定的值，不需要排序。Result_vector（参数三） 为行或列单元格区域，其大小必须与 参数二相同。如果函数 LOOKUP 找不到 lookup_value（查找值），则查找 lookup_vector （参数二）中小于或等于 lookup_value 的最大值。 如果 lookup_value 小于 lookup_vector 中的最小值

6、，函数 LOOKUP 返回错误值 #N/A。应用举例：查询申购基金的申购手续费下例中模拟了一份基金购买的申购费率表，其中左表为购买基金的费率标准，右表D1：D4区域为根据标准转换得到的数值关系表，下面的公式将根据购买者的申购金额来计算应付的申购基金手续费。函数LOOKUP（数组形式）在数组的第一列（或第一行）中查找指定数值，然后返回最后一列（或最后一行）中相同位置处的数值。LOOKUP（lookup_value,array） Lookup_value为要查找的数值。该参数可以为数字、文本、逻辑值或包含数值的名称或引用。Array为一单元格区域。区域中的数值必须按升序排序 从成绩表中查询学员的总

7、成绩下图展示了某次考试的成绩表，下面使用LOOKUP函数来查询学员的总成绩。公式说明：假设F12单元格为所选择的学员姓名“钱洋” ,则在F13单元格中将显示该学员的成绩，在F13单元格中的公式为：=LOOKUP（$F$12,$B$3:$I$10）该公式利用LOOKUP在二维区域中的查找原理，在B3：I10区域中最左列进行姓名查找，并返回二维区域中最后一列相同位置的总成绩。VLOOKUP,HLOOKUP使用频率最高的函数。利用它们，可以从员工资料表中查找一员工所属部门，电话等信息。共同点：第1个参数：支持通配符（“？”“*”）进行查询，但不能是数组。第2个参数：可以是单元格引用区域，还有数组。第

8、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时

9、，返回第3列的值）；Range_lookup为逻辑值，如果为TRUE或省略，则必须按升序排列区域中第一列的值，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，不需要排序，则返回精确匹配值，如果有多个使用第一个找到的值，如果找不到，则返回错误值#N/A。函数HLOOKUP在指定数据区域的首行中搜索满足条件的数据，并返回当前列中指定行号所对应的值。HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）使用说明：Table_array代表需要在其中查找数据的单元格区域；

10、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单元格公式为：=HLOOK

11、UP（B3,$D$2:$P$8,5,0）如果在B7单元格输入文本类型的数值10，即使查询公式不变，结果却返回“#N/A”错误，这是因为B7单元格的查找值（文本）与查找范围中首行的数据（数值）的数据类型不一致，因此，可将公式进行修改，以使其返回正确的结果=HLOOKUP（B7*1,$D$2:注：某些情况下，可能忽略了查找值与查找范围的数据类型不一致的情况，从而导致查询失败，因此，在使用些类查询函数进行查询时，查找条件与查找范围的首列或首行的数字格式必须保持一致，如使用了文本函数（LEFT、MID、RIGHT等）来提取查找条件中的字符串，其结果为文本，也需注意数据类型的转换。LOOKUP函数与HL

12、OOKUP和VLOOKUP的区别:函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找 lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。如果数组所包含的区域宽度大，高度小（即列数多于行数），函数 LOOKUP 在第一行查找 lookup_value。如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数 LOOKUP 在第一列查找 lookup_value。函数 HLOOKUP 和函数 VLOOKUP 允许按行或按列索引，而函数 LOOK

13、UP 总是选择行或列的最后一个数值。函数名称：OFFSET以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。OFFSET（reference,rows,cols,height,width）Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表

14、在起始引用的下方）或负数（代表在起始引用的上方）。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height 高度，即所要返回的引用区域的行数。Height 必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。如果省略参数高和宽，则系统认为其高和宽度与引用基点相同。IS类函数IS 类函数，可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。例如，如果数值为对空白单

15、元格的引用，函数 ISBLANK 返回逻辑值 TRUE，否则返回 FALSE。ISBLANK（value） ISERR（value） ISERROR（value） ISLOGICAL（value）ISNA（value） ISNONTEXT（value） ISNUMBER（value） ISREF（value）ISTEXT（value）Value为需要进行检验的数值。分别为：空白（空白单元格）、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。函 数如果参数值为下面的内容，则返回 TRUEISBLANK值为空白单元格。ISERR值为任意错误值（除去 #N/A）。ISERROR值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL值为逻辑值。ISNA值为错误值 #N/A（值不存在）。ISNONTEXT值为不是文本的任意项（注意此函数在值为空白单元格时返回 TRUE）。ISNUMBER值为数字。ISREF值为引用。ISTEXT值为文本。