1、Excel合并計算應用實驗目的掌握使用Excel進行考勤核算和獎懲核算的基本方法。掌握條件判斷函數IF()的使用方法。掌握首列查找和引用函數VLOOKUP()的使用方法。掌握排位函數RANK()的使用方法。掌握Excel的合并運算功能。實驗內容假設公司每個季度進行考核評比，計算評比分數，在年終評比時作匯總工作，依評比分數作Excel進行考勤年終考核排名，依評比等級發給年終獎金、禮品并調整工資作為獎懲。我們使用核算和獎懲核算。6-1所示。一、建立員工資料表1.首先我們建立“員工資料表”工作表，該工作表存放員工的基本信息。如圖ABCDEF*員工資料表2.員工編號姓名部門職務基本工資職務工資食AF0

2、001趙明德總經理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林志勝

3、研發部研究員圖6-1員工資料表2.為以后書寫方便，定義“員工資料表”A3:F16單元格區域名稱為“員工資料表”。00A3:F16單元格區域。單擊菜單命令【插入】叮名稱叮【定義】。在打開的“定義名稱”對話框中輸入名稱和引用位置。如圖6-2所示。圖6-2定義名稱“員工資料表”二、建立缺勤記錄表便年終評比參考使用。如圖6-3所示。ABDEF12006年度員工缺勤記錄表2員工編號姓名一孝度缺勒天數二爭度缺勤天數三垂度缺勒天數四垂度缺勤天數3AF0001趙明德22024AF0002錢家寶口0.205AF0003砂文直44536AF0004李彥娟5.107AF0005周盈翔5.24gAF0006吳利敏30

4、2邈gAF0007鄭意恵2.5Q2210AF0008王穆354011AF0009馮忠欲312.212AF0010陳家慶口：1U113AF0011楚巧瑛002014AF0012魏巧急501015AF001?方錦玉J0Q001&-.AF0014林志勝1題0-11.建立“缺勤記錄表”工作表，該工作表記錄每個員工一季度至四季度中缺勤的天數，以圖6-3缺勤記錄表2.為以后書寫方便，定義“缺勤記錄表”A3:F16單元格區域名稱為“缺勤記錄表”。00A3:F16單元格區域。單擊菜單命令【插入叮【名稱】叮定義】。在打開的“定義名稱”對話框中輸入名稱和引用位置。如圖6-4所示。圖6-4定義名稱“缺勤記錄表”三、

5、建立季度考勤核算表建立季度考勤核算表時，我們預設公司的出勤總天數為當缺勤超過5天時將自動顯示“警告”兩個字；若缺勤天數超過70天。若請假或曠工一天就扣1，70天時，將自動顯示“開除”兩個字，否則就顯示抵扣后的出勤天數。如圖6-5所示。圖6-5輸入第一個職工的記錄1.輸入查詢姓名的公式B3單元格公式：=VLOOKUP（A3,員工資料表，2）說明：以員工編號字段為關鍵字在“員工資料表”中查詢，返回查找到的“員工資料表”中的該行的第2列的值，即“姓名”字段。2.輸入計算缺勤天數的公式C3單元格公式：=VL00KUP(A3,缺勤記錄表，3)說明：以員工編號字段為關鍵字在“缺勤記錄表”中查詢，返回查找到

6、的“缺勤記錄表”中的該行的第3列的值，即“缺勤天數”字段。輸入計算出勤天數的公式D3單元格公式：=IF(C35,70-C3,IF(C3=50,開除,警告)5天時，自動顯示說明：當缺勤天數不足5天時，顯示扣抵后的出勤天數；當缺勤天數超過“警告”；當缺勤天數超過50天時，自動顯示“開除”。復制以上公式到其他員工記錄B16:D16。如圖6-6所示。選中B3:D3單元格區域，使用填充柄向下復制到圖6-6復制公式完畢計算考核成績季度考核成績由出勤天數(占20%)和主管評分(占80%)兩部分組成。F3單元格公式：=(70-C3)*0.2+E3*0.8000000F3中的公式復制到該列其下單元格。如圖6-7

7、所示。圖6-8二季度考勤核算表圖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馮忠欲.169

8、817.6AF0010陳家慶A697271.4AF0011楚巧瑛070S9:57AF0012-.魏巧慧0708884.4AF0013方錦玉07094.89.2AF0014林志勝2唸86加啜一季度核算二季度核算(三季度核算/四季度核算圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。ABCDEF三季度員工考勤核算員工編號姓名缺勤天數出勤天數主管評分“考核戰績AF0001趙明德1(70圈.84.4AF0002錢家寶2S6辺.4AFOOO孫文真.5警告37826AF0004李彥娟0/70雜呂4.4AF0005周盈翔26S922AF0006吳利敏26SS0776AF0007鄭意惠

9、：2：弦7373:6AF000&-王穆火466豳呂4.4AF0009馮忠欲；26S90AF0010陳家慶0.冗7?71.6AF0011楚巧瑛：2：弦327911AF000912AFOO1O1$AFOO1114AF0012.15AFOOlt-16AF0014H血蘭季度核算/四季度核算年度考核表/評比獎勵/圖6-11年度考勤核算表查詢員工資料輸入查詢姓名的公式B3單元格公式：=VLOOKUP(A3,員工資料表，2)說明：以員工編號字段為關鍵字在“員工資料表”中查詢，返回查找到的“員工資料表”中的該行的第2列的值，即“姓名”字段。輸入查詢基本工資的公式C3單元格公式：=VLOOKUP(A3,員工資料

10、表,5)說明：以員工編號字段為關鍵字在“員工資料表”中查詢，返回查找到的“員工資料表”中的該行的第5列的值，即“基本工資”字段。圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。所示。00B3:C3單元格區域，使用填充柄復制B3和C3中的公式到其下單元格。如圖6-123.計算四個季度的平均考核分數圖6-14在計算區域中添加二、三、四季度的數據圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。我們使用Excel的“合并計算”功能，計算四個季度的季度成績平均值。6-13所示的“合并選

11、擇D3單元格，選擇菜單命令【數據叮【合并計算】，打開如圖計算”對話框。在“函數”下拉列表框中選擇“平均值”函數。如圖6-13所示。F3:F16單元單擊“引用位置”右邊的折疊對話框按鈕，選取“一季度核算”工作表中的格區域后，單擊折疊對話框按鈕返回“合并計算”對話框。如圖6-13所示。圖6-14在計算區域中添加二、三、四季度的數據圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。圖6-14在計算區域中添加二、三、四季度的數據圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。圖6-13“合并計算”對話框單擊“添加”按鈕，將“一季度核算！$F$3:$F$16

12、”單元格區域添加到計算區域中。“一季度核算！$F$3:$F$16”即顯示在“所有引用區域”列表框中。6-14所示。依步驟(3)和步驟(4)，將二、三、四季度的數據加入到計算區域中。如圖圖6-14在計算區域中添加二、三、四季度的數據圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。所示。(6)四個季度的數據全部加入后，單擊“確定”按鈕，即可完成合并計算。計算結果如圖6-15(2)使用填充柄將E3中的公式復制到其下單元格。計算結果如圖6-17所示。圖6-10四季度考勤核算表1.建立如圖6-11所示的“年度考勤核算表”。4.5-.6Y_BgT0ll1213T41516AE0:D

13、EFGHI20斷年度員工考核核算表員工編號姓名基本工資爭平均|考稜排名評比級別年終獎金年谿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.15

14、I中一一!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圧

15、率度核算年播考核表/評比獎贓i斗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%)。其余依次類推。計算年終獎金G3單元格公式=C3*(1+VLOOKUP(F3,評比獎勵!$A$2:$D$5,2,0)計算年終禮品H3單元格公式=VLOOKUP(F3,評比獎勵!$A$2:$D$5,3,0)計算調整工資1

16、3單元格公式=C3*(l+VL00KUP(F3,評比獎勵!$A$2:$D$5,4,0)使用填充柄將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吳利敏2300S

17、1.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輸入“年終獎金”、“年終禮品”和“調整工資

18、”三列的公式下載本實驗中用到的文件：實驗6.xls習題6.xls思考與練習打開“習題6.xls”,其中包含以下成績表：ACDEF物理F4物理9102009E858568858679747378979578968985Hjm.a9102014學年成績曠總俶績單/11121314垣1634567891023678910111213145678910黃錦玉張志勝王文直234567896993859486848486869?797889858568859767頁75588587869585967576978?807878767989865567958?8?翌8977798879788595958467

19、696982唾77?868474968969938594868484868984848669938595846769698282718681807885967576678?755885878695厲876781758585688585688597678?7585967576亙807889828271958467556795亙8?83897795bT897755?j95可K84瓦868莎92帀78A81sT瓦2L7瓦瓦sTR_C8282tI2186847496RQ83sT瓦瓦sTQ5_DB第一次期中考ABCDE第二次期中考910201091020119102012199102014九三年度學習成績匯總機玄簧浚軟學丨聽密歷兌I半悔Q券金獎辱金徐巧瑛歷史歷史B第三次期中考1891020139102001491020021.將每一位學生9102001蔡盈翔張利敏黃意惠109102008王穆為陳忠欲臨家慶9102011洪巧慧2學號語文數學9102001910200291020039102004910200591