Excel合并计算应用

Excel合并计算应用实验目的1. 掌握使用Excel进行考勤核算和奖惩核算的基本方法。2. 掌握条件判断函数IF()的使用方法。3. 掌握首列查找和引用函数VLOOKUP()的使用方法。4. 掌握排位函数RANK()的使用方法。5. 掌握Excel的合并运算功能。实验内容假设公司每个季度进行考核评比，计算评比分数，在年终评比时作汇总工作，依评比分数作Excel进行考勤年终考核排名，依评比等级发给年终奖金、礼品并调整工资作为奖惩。我们使用核算和奖惩核算。6-1所示。一、建立员工资料表1.首先我们建立“员工资料表”工作表，该工作表存放员工的基本信息。如图ABCDEF*员工资料表2.员工编号姓名部门职务基本工资职务工资食AF0001赵明德总经理6；S00-2,0004AF0002:钱家宝副总经理5,0001,0005AF0003孙文直力養室秘书3；-2006AF0004李彦娟财雰部吕1氏jni-.rm4,000007AF0005.周盈翔财务部会计2,3008AF0006吴利敏财务部出纳2,2005009AF0007郑意惠业务部经理3,50010AF0008-王穆火业务部副经理2,0011AF0009业务部业务员2；00012-AF0010陈家庆业务部业勢员;0001AFG011楚巧瑛业务部业务员2?10014AF0017-魏巧慧业务部业务员貞叩AF0012r.；-方锦玉研发部研究员4；200lfcAF0014林志胜研发部研究员图6-1员工资料表2.为以后书写方便，定义“员工资料表”A3:F16单元格区域名称为“员工资料表”。(1) 00A3:F16单元格区域。(2) 单击菜单命令【插入】叮名称叮【定义】。(3) 在打开的“定义名称”对话框中输入名称和引用位置。如图6-2所示。图6-2定义名称“员工资料表”二、建立缺勤记录表便年终评比参考使用。如图6-3所示。ABDEF12006年度员工缺勤记录表2员工编号姓名一孝度缺勒天数二爭度缺勤天数三垂度缺勒天数四垂度缺勤天数3AF0001赵明德22024AF0002钱家宝口0.205AF0003砂文直44536AF0004李彦娟5.107AF0005周盈翔5.24gAF0006吴利敏302邈gAF0007郑意恵2.5Q2210AF0008王穆354011AF0009冯忠欲312.212AF0010陈家庆口：1U113AF0011楚巧瑛002014AF0012魏巧急501015AF001?方锦玉J0Q001&-.AF0014林志胜1題0-11.建立“缺勤记录表”工作表，该工作表记录每个员工一季度至四季度中缺勤的天数，以图6-3缺勤记录表2.为以后书写方便，定义“缺勤记录表”A3:F16单元格区域名称为“缺勤记录表”。(1) 00A3:F16单元格区域。(2) 单击菜单命令【插入叮【名称】叮定义】。(3) 在打开的“定义名称”对话框中输入名称和引用位置。如图6-4所示。图6-4定义名称“缺勤记录表”三、建立季度考勤核算表建立季度考勤核算表时，我们预设公司的出勤总天数为当缺勤超过5天时将自动显示“警告”两个字；若缺勤天数超过70天。若请假或旷工一天就扣1，70天时，将自动显示“开除”两个字，否则就显示抵扣后的出勤天数。如图6-5所示。图6-5输入第一个职工的记录1.输入查询姓名的公式B3单元格公式：=VLOOKUP（A3,员工资料表，2）说明：以员工编号字段为关键字在“员工资料表”中查询，返回查找到的“员工资料表”中的该行的第2列的值，即“姓名”字段。2.输入计算缺勤天数的公式C3单元格公式：=VL00KUP(A3,缺勤记录表，3)说明：以员工编号字段为关键字在“缺勤记录表”中查询，返回查找到的“缺勤记录表”中的该行的第3列的值，即“缺勤天数”字段。3. 输入计算出勤天数的公式D3单元格公式：=IF(C35,70-C3,IF(C3=50,开除,警告)5天时，自动显示说明：当缺勤天数不足5天时，显示扣抵后的出勤天数；当缺勤天数超过“警告”；当缺勤天数超过50天时，自动显示“开除”。4. 复制以上公式到其他员工记录B16:D16。如图6-6所示。选中B3:D3单元格区域，使用填充柄向下复制到图6-6复制公式完毕5. 计算考核成绩季度考核成绩由出勤天数(占20%)和主管评分(占80%)两部分组成。(1) F3单元格公式：=(70-C3)*0.2+E3*0.8(2) 000000F3中的公式复制到该列其下单元格。如图6-7所示。图6-7复制后的F列6-8、图6-9、图6-10.G3氏二VL00KUP(A3,.缺勤记录表,盘ABDEF6.用类似的步骤建立第二、三、四季度的“季度考勤核算表”。如图所示。二季度员工考勤核算员工编号姓名缺勤天数出勒天数主管评分考核成绩AF0C01赵明德|還.芬.81.6AF0002钱家宝,0.r:to92&7.6AF0003，-孙文直466S2-78.:&AF0004李彦娟169908.8-AF0005?周盈翔3677573.4AF0006吴利敏07080.磁AF0007郑意惠0.707775.6AFOOOS-王穆火.5磐告9286.6AF0C09冯忠欲.169817.6AF0010陈家庆A697271.4AF0011楚巧瑛070S9:57AF0012-.魏巧慧0708884.4AF0013方锦玉07094.89.2AF0014林志胜2唸86加啜一季度核算二季度核算(三季度核算/四季度核算图6-8二季度考勤核算表ABCDEF三季度员工考勤核算员工编号姓名缺勤天数出勤天数主管评分“考核战绩AF0001赵明德1(70圈.84.4AF0002钱家宝2S6辺.4AFOOO孙文真.5警告37826AF0004李彦娟0/70雜呂4.4AF0005周盈翔26S922AF0006吴利敏26SS0776AF0007郑意惠：2：弦7373:6AF000&-王穆火466豳呂4.4AF0009冯忠欲；26S90AF0010陈家庆0.冗7?71.6AF0011楚巧瑛：2：弦327911AF000912AFOO1O1$AFOO1114AF0012.15AFOOlt-16AF0014H血兰季度核算/四季度核算年度考核表/评比奖励/图6-11年度考勤核算表2. 查询员工资料(1) 输入查询姓名的公式B3单元格公式：=VLOOKUP(A3,员工资料表，2)说明：以员工编号字段为关键字在“员工资料表”中查询，返回查找到的“员工资料表”中的该行的第2列的值，即“姓名”字段。(2) 输入查询基本工资的公式C3单元格公式：=VLOOKUP(A3,员工资料表,5)说明：以员工编号字段为关键字在“员工资料表”中查询，返回查找到的“员工资料表”中的该行的第5列的值，即“基本工资”字段。所示。(3) 00B3:C3单元格区域，使用填充柄复制B3和C3中的公式到其下单元格。如图6-123.计算四个季度的平均考核分数我们使用Excel的“合并计算”功能，计算四个季度的季度成绩平均值。6-13所示的“合并(1) 选择D3单元格，选择菜单命令【数据叮【合并计算】，打开如图计算”对话框。(2) 在“函数”下拉列表框中选择“平均值”函数。如图6-13所示。F3:F16单元(3) 单击“引用位置”右边的折叠对话框按钮，选取“一季度核算”工作表中的格区域后，单击折叠对话框按钮返回“合并计算”对话框。如图6-13所示。图6-14在计算区域中添加二、三、四季度的数据图6-14在计算区域中添加二、三、四季度的数据图6-13“合并计算”对话框(4) 单击“添加”按钮，将“一季度核算！$F$3:$F$16”单元格区域添加到计算区域中。“一季度核算！$F$3:$F$16”即显示在“所有引用区域”列表框中。6-14所示。(5) 依步骤(3)和步骤(4)，将二、三、四季度的数据加入到计算区域中。如图图6-14在计算区域中添加二、三、四季度的数据所示。(6)四个季度的数据全部加入后，单击“确定”按钮，即可完成合并计算。计算结果如图6-15图6-14在计算区域中添加二、三、四季度的数据4.5-.6Y_BgT0ll1213T41516AE0:DEFGHI20断年度员工考核核算表员工编号姓名基本工资爭平均|考稜排名评比级别年终奖金年谿L品调整工资AF0001赵明德6S00134.1JAF0002:钱家宝5000S6.3AF0003.：.孙文真320079.2AF0004李彦娟4000=90,优，IF(D3=85,良，IF(D3=80,中，差)(2)000000F3中的公式复制到其下单元格。计算结果如图6-18所示。胆戏二IF(D3二9(V优笃IF(D3二弧良笃IF(D3n(V中笃差)ABDEFGHI.TL20吋年度员工考核核算表2员工编号基本工資幸平均.考核排名|评比级别年终奖金年终礼品调整工資3AFOOO1赵明德6800.34.15I中一一!4AF0003.钱家宝500086.32良5AF0003.：.孙文直;320079:211差feAFOOO4李彦媚4000S3.96中7AFOOO5;-周盈翔230079.910差-AF0006吴利敏22003中.9AF0007郑意惠3500癒戏:12差10AFOOOS?王穆曉:200.82.27中AF00092000中頤13差ifAFOO1O陈家庆-200075.514差13AFOO11楚巧瑛2100就14艮14AFOO1?.魏巧急.20005.3?.3艮15AFOOB方锦玉420090.41优16：AFOO14林志胜鋼6，阚衍入1评比级?1”公式中1卜h/一季嗟核算/二牽度梅2圧率度核算年播考核表/评比奖赃i斗1五、查询评比奖励数据1. 建立如图6-19所示的“评比奖励”工作表ABCD1评比级别年终桨金年终礼品调整工费2优1/2香港嵌游5%3良1/3国内旅游3%4中1/45机0%5差1/5200元礼卷-3%图6-19“评比奖励”工作表评比奖励说明：若员工的评比级别为“优”，年终奖金=基本工资*(1+1/2)，年终礼品=香港旅游，调整工资=基本工资*(1+5%)。其余依次类推。2. 计算年终奖金G3单元格公式=C3*(1+VLOOKUP(F3,评比奖励!$A$2:$D$5,2,0)3. 计算年终礼品H3单元格公式=VLOOKUP(F3,评比奖励!$A$2:$D$5,3,0)4. 计算调整工资13单元格公式=C3*(l+VL00KUP(F3,评比奖励!$A$2:$D$5,4,0)5. 使用填充柄将G3、H3、I3中的公式复制到其下单元格。计算结果如图6-20所示。ABCDEFGHI20时年度员工考核檢算表2员工爭平均老檢挂名评比级别年线奖全年线礼品诵莖工资3AFaaOl6800&4.15中sjaoGD机68004AFOOLQ钛寡宝5X086.32良命66.6俞7国向施游J1JU5AFOOLB孙文直23：079：2113340颇元礼柱310-1GAFaaw率彦娟4M083.96中jaaoGD机4000TAFOOOZ3X79.91037tiOZOOjf；礼*2231含FQQ06吴利敏2300S1.B5S中27J0GD机2200AFaao?郑意思35：078.(575124200烦元礼卷339510AFOQLE王穆火為Tl7中3JOO匚D机230U11AFaatp冯忠欲200073.613差2400颇元礼卷194012AF001LI3：0：|75.51424002D0M；礼善194013含FQQll2100S5.14良2300国向施沸21d314AF00123X085.33艮2(566.6俞7国内戏游2i：ifU15AFDD13方棉玉jmE0.4E53DD普港旅游441D16AFaaw林店胜35B0S0.39中4475GD机3J3O图6-20输入“年终奖金”、“年终礼品”和“调整工资”三列的公式下载本实验中用到的文件：实验6.xls习题6.xls思考与练习打开“习题6.xls”,其中包含以下成绩表：ACDEF物理F4物理9102009E858568858679747378979578968985Hjm.a9102014学年成绩旷总俶绩单/11121314垣1634567891023678910111213145678910黄锦玉张志胜王文直234567896993859486848486869?797889858568859767页75588587869585967576978?807878767989865567958?8?翌8977798879788595958467696982唾77?868474968969938594868484868984848669938595846769698282718681807885967576678?755885878695厉876781758585688585688597678?758596757697 亘807889828271958467556795亘8?83897795bT897755?j95可K84瓦868莎92帀78A81sT瓦2L7瓦瓦sTR_C8282tI2186847496RQ83sT瓦瓦sTQ5_DB第一次期中考ABCDE第二次期中考15 910201016 910201117 9102012199102014九三年度学习成绩汇总機玄簧浚软学丨聽密历兌I半悔Q券金奖辱金徐巧瑛历史历史B第三次期中考1891020139102001491020021.将每一位学生9102001蔡盈翔张利敏黄意惠109102008王穆为陈忠欲臨家庆9102011洪巧慧2学号语文数学910200191020029102003910200491020059102006910200791020085学号语文数学910200191020029102003910200491020059102006910200791020089102003910200491020059102006910200791020083次考试的成绩，利用平均均函数合并计算至“学年叫汇总”工作表中。AB:&DEF910200291020039102004林彦娟9102005910200691020079102009910201091020129102013央丈央丈学号语文数学物理历史119102009央又平均分90-100分80-89分70-79分5.建立如下图所示的“成绩单”排名及奖学金的数据。2.在“学年成绩汇总”工作表中，使用函数计算每个学生的总分和平均分。3. 在“学年成绩汇总”工作表中，按总分计算每个学生的排名。4. 参照以下条件，计算符合条件的同学应该领取的奖学金金额。奖学金2万元1万元3千元工作表，其功能是输入学生的学号，即可得到该生的成绩、成绩单学号|9102。015955586_y859总井逐甑10平均蓊77.811排名1212-奖学金J,000府/卜、!|学年成绩汇总、成绩单/