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

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

2、001趙明德總經(jīng)理6；S00-2,0004AF0002:錢家寶副總經(jīng)理5,0001,0005AF0003孫文直力養(yǎng)室秘書3；-2006AF0004李彥娟財(cái)雰部呂1氏jni-.rm4,000007AF0005.周盈翔財(cái)務(wù)部會(huì)計(jì)2,3008AF0006吳利敏財(cái)務(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】。在打開的“定義名稱”對(duì)話框中輸入名稱和引用位置。如圖6-2所示。圖6-2定義名稱“員工資料表”二、建立缺勤記錄表便年終評(píng)比參考使用。如圖6-3所示。ABDEF12006年度員工缺勤記錄表2員工編號(hào)姓名一孝度缺勒天數(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.建立“缺勤記錄表”工作表，該工作表記錄每個(gè)員工一季度至四季度中缺勤的天數(shù)，以圖6-3缺勤記錄表2.為以后書寫方便，定義“缺勤記錄表”A3:F16單元格區(qū)域名稱為“缺勤記錄表”。00A3:F16單元格區(qū)域。單擊菜單命令【插入叮【名稱】叮定義】。在打開的“定義名稱”對(duì)話框中輸入名稱和引用位置。如圖6-4所示。圖6-4定義名稱“缺勤記錄表”三、

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

6、的“缺勤記錄表”中的該行的第3列的值，即“缺勤天數(shù)”字段。輸入計(jì)算出勤天數(shù)的公式D3單元格公式：=IF(C35,70-C3,IF(C3=50,開除,警告)5天時(shí)，自動(dòng)顯示說明：當(dāng)缺勤天數(shù)不足5天時(shí)，顯示扣抵后的出勤天數(shù)；當(dāng)缺勤天數(shù)超過“警告”；當(dāng)缺勤天數(shù)超過50天時(shí)，自動(dòng)顯示“開除”。復(fù)制以上公式到其他員工記錄B16:D16。如圖6-6所示。選中B3:D3單元格區(qū)域，使用填充柄向下復(fù)制到圖6-6復(fù)制公式完畢計(jì)算考核成績季度考核成績由出勤天數(shù)(占20%)和主管評(píng)分(占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.用類似的步驟建立第二、三、四季度的“季度考勤核算表”。如圖所示。二季度員工考勤核算員工編號(hào)姓名缺勤天數(shù)出勒天數(shù)主管評(píng)分考核成績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三季度員工考勤核算員工編號(hào)姓名缺勤天數(shù)出勤天數(shù)主管評(píng)分“考核戰(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血蘭季度核算/四季度核算年度考核表/評(píng)比獎(jiǎng)勵(lì)/圖6-11年度考勤核算表查詢員工資料輸入查詢姓名的公式B3單元格公式：=VLOOKUP(A3,員工資料表，2)說明：以員工編號(hào)字段為關(guān)鍵字在“員工資料表”中查詢，返回查找到的“員工資料表”中的該行的第2列的值，即“姓名”字段。輸入查詢基本工資的公式C3單元格公式：=VLOOKUP(A3,員工資料

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

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

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

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

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

16、3單元格公式=C3*(l+VL00KUP(F3,評(píng)比獎(jiǎng)勵(lì)!$A$2:$D$5,4,0)使用填充柄將G3、H3、I3中的公式復(fù)制到其下單元格。計(jì)算結(jié)果如圖6-20所示。ABCDEFGHI20時(shí)年度員工考核檢算表2員工爭平均老檢掛名評(píng)比級(jí)別年線獎(jiǎng)全年線禮品誦莖工資3AFaaOl6800&4.15中sjaoGD機(jī)68004AFOOLQ鈦寡寶5X086.32良命66.6俞7國向施游J1JU5AFOOLB孫文直23：079：2113340頗元禮柱310-1GAFaaw率彥娟4M083.96中jaaoGD機(jī)4000TAFOOOZ3X79.91037tiOZOOjf；禮*2231含F(xiàn)QQ06吳利敏2300S

17、1.B5S中27J0GD機(jī)2200AFaao?鄭意思35：078.(575124200煩元禮卷339510AFOQLE王穆火為Tl7中3JOO匚D機(jī)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機(jī)3J3O圖6-20輸入“年終獎(jiǎng)金”、“年終禮品”和“調(diào)整工資

18、”三列的公式下載本實(shí)驗(yàn)中用到的文件：實(shí)驗(yàn)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í)成績匯總機(jī)玄簧浚軟學(xué)丨聽密歷兌I半悔Q券金獎(jiǎng)辱金徐巧瑛歷史歷史B第三次期中考1891020139102001491020021.將每一位學(xué)生9102001蔡盈翔張利敏黃意惠109102008王穆為陳忠欲臨家慶9102011洪巧慧2學(xué)號(hào)語文數(shù)學(xué)9102001910200291020039102004910200591