Excel合并計算應(yīng)用

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

2、001趙明德總經(jīng)理6；S00-2,0004AF0002:錢家寶副總經(jīng)理5,0001,0005AF0003孫文直力養(yǎng)室秘書3；-2006AF0004李彥娟財雰部呂1氏jni-.rm4,000007AF0005.周盈翔財務(wù)部會計2,3008AF0006吳利敏財務(wù)部出納2,2005009AF0007鄭意惠業(yè)務(wù)部經(jīng)理3,50010AF0008-王穆火業(yè)務(wù)部副經(jīng)理2,0011AF0009業(yè)務(wù)部業(yè)務(wù)員2；00012-AF0010陳家慶業(yè)務(wù)部業(yè)勢員;0001AFG011楚巧瑛業(yè)務(wù)部業(yè)務(wù)員2?10014AF0017-魏巧慧業(yè)務(wù)部業(yè)務(wù)員貞叩A(chǔ)F0012r.；-方錦玉研發(fā)部研究員4；200lfcAF0014林志勝

3、研發(fā)部研究員圖6-1員工資料表2.為以后書寫方便，定義“員工資料表”A3:F16單元格區(qū)域名稱為“員工資料表”。00A3:F16單元格區(qū)域。單擊菜單命令【插入】叮名稱?！径x】。在打開的“定義名稱”對話框中輸入名稱和引用位置。如圖6-2所示。圖6-2定義名稱“員工資料表”二、建立缺勤記錄表便年終評比參考使用。如圖6-3所示。ABDEF12006年度員工缺勤記錄表2員工編號姓名一孝度缺勒天數(shù)二爭度缺勤天數(shù)三垂度缺勒天數(shù)四垂度缺勤天數(shù)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.建立“缺勤記錄表”工作表，該工作表記錄每個員工一季度至四季度中缺勤的天數(shù)，以圖6-3缺勤記錄表2.為以后書寫方便，定義“缺勤記錄表”A3:F16單元格區(qū)域名稱為“缺勤記錄表”。00A3:F16單元格區(qū)域。單擊菜單命令【插入?！久Q】叮定義】。在打開的“定義名稱”對話框中輸入名稱和引用位置。如圖6-4所示。圖6-4定義名稱“缺勤記錄表”三、

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

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

7、所示。圖6-8二季度考勤核算表圖6-7復(fù)制后的F列6-8、圖6-9、圖6-10.G3氏二VL00KUP(A3,.缺勤記錄表,盤ABDEF6.用類似的步驟建立第二、三、四季度的“季度考勤核算表”。如圖所示。二季度員工考勤核算員工編號姓名缺勤天數(shù)出勒天數(shù)主管評分考核成績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三季度員工考勤核算員工編號姓名缺勤天數(shù)出勤天數(shù)主管評分“考核戰(zhàn)績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)說明：以員工編號字段為關(guān)鍵字在“員工資料表”中查詢，返回查找到的“員工資料表”中的該行的第2列的值，即“姓名”字段。輸入查詢基本工資的公式C3單元格公式：=VLOOKUP(A3,員工資料

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

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

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

13、EFGHI20斷年度員工考核核算表員工編號姓名基本工資爭平均|考稜排名評比級別年終獎金年谿L品調(diào)整工資AF0001趙明德6S00134.1JAF0002:錢家寶5000S6.3AF0003.：.孫文真320079.2AF0004李彥娟4000=90,優(yōu)，IF(D3=85,良，IF(D3=80,中，差)(2)000000F3中的公式復(fù)制到其下單元格。計算結(jié)果如圖6-18所示。膽戲二IF(D3二9(V優(yōu)篤IF(D3二弧良篤IF(D3n(V中篤差)ABDEFGHI.TL20吋年度員工考核核算表2員工編號基本工資幸平均.考核排名|評比級別年終獎金年終禮品調(diào)整工資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優(yōu)16：AFOO14林志勝鋼6，闞衍入1評比級?1”公式中1卜h/一季嗟核算/二牽度梅2圧

15、率度核算年播考核表/評比獎贓i斗1五、查詢評比獎勵數(shù)據(jù)建立如圖6-19所示的“評比獎勵”工作表ABCD1評比級別年終槳金年終禮品調(diào)整工費2優(yōu)1/2香港嵌游5%3良1/3國內(nèi)旅游3%4中1/45機0%5差1/5200元禮卷-3%圖6-19“評比獎勵”工作表評比獎勵說明：若員工的評比級別為“優(yōu)”，年終獎金=基本工資*(1+1/2)，年終禮品=香港旅游，調(diào)整工資=基本工資*(1+5%)。其余依次類推。計算年終獎金G3單元格公式=C3*(1+VLOOKUP(F3,評比獎勵!$A$2:$D$5,2,0)計算年終禮品H3單元格公式=VLOOKUP(F3,評比獎勵!$A$2:$D$5,3,0)計算調(diào)整工資1

16、3單元格公式=C3*(l+VL00KUP(F3,評比獎勵!$A$2:$D$5,4,0)使用填充柄將G3、H3、I3中的公式復(fù)制到其下單元格。計算結(jié)果如圖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含F(xiàn)QQ06吳利敏2300S

17、1.B5S中27J0GD機2200AFaao?鄭意思35：078.(575124200煩元禮卷339510AFOQLE王穆火為Tl7中3JOO匚D機230U11AFaatp馮忠欲200073.613差2400頗元禮卷194012AF001LI3：0：|75.51424002D0M；禮善194013含F(xiàn)QQll2100S5.14良2300國向施沸21d314AF00123X085.33艮2(566.6俞7國內(nèi)戲游2i：ifU15AFDD13方棉玉jmE0.4E53DD普港旅游441D16AFaaw林店勝35B0S0.39中4475GD機3J3O圖6-20輸入“年終獎金”、“年終禮品”和“調(diào)整工資

18、”三列的公式下載本實驗中用到的文件：實驗6.xls習(xí)題6.xls思考與練習(xí)打開“習(xí)題6.xls”,其中包含以下成績表：ACDEF物理F4物理9102009E858568858679747378979578968985Hjm.a9102014學(xué)年成績曠總俶績單/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九三年度學(xué)習(xí)成績匯總機玄簧浚軟學(xué)丨聽密歷兌I半悔Q券金獎辱金徐巧瑛歷史歷史B第三次期中考1891020139102001491020021.將每一位學(xué)生9102001蔡盈翔張利敏黃意惠109102008王穆為陳忠欲臨家慶9102011洪巧慧2學(xué)號語文數(shù)學(xué)9102001910200291020039102004910200591