《辦公自動化案例教程》(賀鑫)852-6課件-項目七-使用Excel計算表格數(shù)據(jù)

辦公自動化案例教程Windows7+Office2010目錄計算水電費開支表數(shù)據(jù)計算學生成績一覽表

數(shù)據(jù)并設置級別計算員工考核表數(shù)據(jù)計算員工工資表數(shù)據(jù)任務一計算水電費開支表數(shù)據(jù)制作思路：新建工作簿后將工作表重命名，然后將其中的2個工作表成組，再在其中的一個工作表中輸入基礎數(shù)據(jù)并格式化，取消成組后修改另一個工作表數(shù)據(jù)，接著在“合計”工作表中制作表格，最后在各工作表中輸入公式計算工作表數(shù)據(jù)。

一、輸入工作表基本數(shù)據(jù)（1）新建“水電費開支”工作簿，然后分別將“Sheet1”，“Sheet2”和“Sheet3”重命名為8月、9月和合計。將8月和9月2個工作表成組，然后在8月工作表中輸入基礎數(shù)據(jù)并格式化。

（2）取消成組工作表狀態(tài)后將9月工作表中的數(shù)據(jù)進行修改。再在“合計”工作表中制作表格。

二、使用公式計算工作表數(shù)據(jù)（1）在“8月”工作表中單擊E4單元格，然后輸入公式“=D4-C4”，然后向下拖動E4單元格右下角的填充柄到E13單元格后釋放鼠標，計算出8月每個人的用電量。

（2）單擊F4單元格，根據(jù)第16行中的說明，輸入公式“=E4*$B$15”，按【Enter】鍵得到第1個租戶的電費金額。拖動F4單元格右下角的填充柄至F13單元格后釋放鼠標，得到其他租戶的電費金額。

（3）單擊I4單元格，輸入公式“=H4-G4”，然后向下拖動I4單元格右下角的填充柄到I13單元格后釋放鼠標，計算出8月的用水量。

（4）單擊J4單元格，然后輸入公式“=I4*$D$15”4，按【Enter】鍵得到第1個租戶的水費金額。拖動J4單元格右下角的填充柄至J13單元格后釋放鼠標，得到所有租戶的水費金額。

（5）分別單擊F14和J14單元格，然后單擊“開始”選項卡上“編輯”組中的“自動求和”按鈕，然后按【Enter】鍵計算出電費和水費合計值。（6）用同樣的方法計算“9月”工作表中的水費、電費及其合計值。

三、計算水電費合計值（1）在“合計”工作表的B2單元格單擊并輸入等號“=”，然后單擊“8月”工作表的F14單元格，輸入加號“+”后再單擊“9月”工作表中的F14單元格，然后按【Enter】鍵，計算出8～9月的電費合計。（2）在“合計”工作表的B3單元格單擊并輸入等號“=”，然后單擊“8月”工作表的J14單元格，輸入加號“+”后再單擊“9月”工作表中的J14單元格，然后按【Enter】鍵，計算出8～9月的水費合計。從編輯欄中可看到不同工作表中的單元格引用。

任務二計算學生成績一覽表數(shù)據(jù)

并設置級別制作思路：首先在工作表中輸入基礎數(shù)據(jù)，再格式化工作表，然后依次計算總分、最高分、平均分、綜合分和備注。

一、輸入學生成績一覽表基本數(shù)據(jù)新建“學生成績一覽表”工作簿，然后在“Sheet1”工作表中輸入數(shù)據(jù)并進行格式化。其中A4:A13單元格區(qū)域的數(shù)字格式為文本，灰色的空白填充區(qū)域是要進行計算的單元格。

二、計算總分、最高分和平均分（1）單擊G4單元格后單擊“開始”選項卡上“編輯”組中的“自動求和”按鈕，按【Enter】鍵得到排到第1位學生的總分。向下拖動G4單元格右下角的填充柄至G13單元格后釋放鼠標，得到其他學生的總分。

（2）單擊C14單元格，然后在“自動求和”列表中選擇“最大值”項，按【Enter】鍵得到結(jié)果。向右拖動C14單元格右下角的填充柄到G14單元格后釋放鼠標，計算其他科目的最高分。

（3）單擊C15單元格，然后在“自動求和”列表中選擇“平均值”項，再在工作表中重新選擇要進行求平均值的單元格區(qū)域，按【Enter】鍵得到結(jié)果，然后向右拖動C15單元格右下角的填充柄到G15單元格后釋放鼠標，計算其他科目的平均分。

三、計算綜合分并判斷成績（1）根據(jù)比例系數(shù)，在H4單元格輸入公式，計算第1位學生的綜合分。向下拖動H4單元格右下角的填充柄到H13單元格后釋放鼠標，計算出所有學生的綜合分。（2）假設綜合分大于等于60分為及格，否則為不及格。在I4單元格輸入公式，按【Enter】鍵判斷出排在第1位的學生的成績是否及格。向下拖動I4單元格右下角的填充柄至I13單元格的釋放鼠標，判斷出其他學生的成績及格與否。

任務三計算員工考核表數(shù)據(jù)制作思路：首先制作出勤量統(tǒng)計表和績效表，然后根據(jù)這兩個工作表數(shù)據(jù)利用函數(shù)INDEX和直接引用單元格的方法計算各季度的考核情況，再計算出年度考核情況表，根據(jù)年度總成績進行排名，最后根據(jù)排名確定獎金額，并利用LOOKUP函數(shù)計算員工的獎金額。

一、制作出勤量統(tǒng)計表、績效表及其他季度和年度考核表（1）制作“出勤量統(tǒng)計表”。新建“員工考核表”工作簿，將“Sheet1”工作表重命名為“出勤量統(tǒng)計表”。（2）在“出勤量統(tǒng)計表”的A1單元格輸入表格標題文本“出勤量統(tǒng)計表”，接著在A2和B2單元格輸入列標題——編號和姓名。（3）在C2單元格輸入“第一季度”文本后按【Alt+Enter】組合鍵換行，接著輸入“出勤量”文本。用同樣的方法輸入其他列的列標題。

（4）自定義單元格區(qū)域的數(shù)字格式。選中A3:A11單元格區(qū)域，然后單擊“開始”選項卡上“數(shù)字”組右下角的對話框啟動器按鈕，打開“設置單元格格式”對話框，在“分類”列表選擇“自定義”項，然后在“類型”編輯框中輸入“0000”，最后單擊“確定”按鈕。

（5）在A3單元格輸入“0001”，然后拖動A3單元格右下角的填充柄至A11單元格，釋放鼠標，單擊“粘貼選項”按鈕，在展開的列表中選擇“填充序列”項。

（6）在其他單元格中輸入數(shù)據(jù)，然后設置表格的格式，出勤量統(tǒng)計表制作完畢。（7）制作“績效表”。將“Sheet2”工作表重命名為“績效表”，然后用制作“出勤量統(tǒng)計表”的方法在其中輸入數(shù)據(jù)并設置格式。

（8）制作季度和年度考核表。將“Sheet3”工作表重命名為“第一季度考核表”，然后再新建4個工作表，依次命名為“第二季度考核表”、“第三季度考核表”、“第四季度考核表”和“年度考核表”。（9）單擊“第一季度考核表”工作表標簽，然后按住【Shift】鍵的同時單擊“第二季度考核表”、“第三季度考核表”、“第四季度考核表”和“年度考核表”工作表標簽，將它們成組。然后在“第一季度考核表”工作表中輸入數(shù)據(jù)并設置格式。可看到在其他4個工作表的相同位置也輸入了同樣的數(shù)據(jù)。

（10）單擊任意工作表標簽，取消成組工作表狀態(tài)，然后分別修改其他工作表中A1單元格的季度期數(shù)。（11）在“年度考核表”修改F2單元格數(shù)據(jù)為“年度總成績”，再在其后添加2列數(shù)據(jù)。其中“年度總成績”列的小數(shù)位數(shù)為3位。

二、使用公式和函數(shù)計算季度考核表數(shù)據(jù)使用公式和函數(shù)計算季度、年度考核表數(shù)據(jù)。其中有兩種方法：第一種是利用函數(shù)，第二種是直接引用單元格。（1）計算“第一季度考核表”數(shù)據(jù)。分別在C3，D3，E3，F(xiàn)3單元格輸入公式，計算出編號為“0001”的員工第一季度的出勤量、工作態(tài)度、工作能力和季度總成績表數(shù)據(jù)，其中“季度總成績表”列中數(shù)據(jù)的計算依據(jù)是“出勤量的20%、工作態(tài)度的30%、工作能力的50%之和”。

（2）向下拖動C3:F3單元格區(qū)域右下角的填充柄至F11單元格后釋放鼠標，計算出其他員工第一季度考核數(shù)據(jù)。這是第一種計算方法。

（2）計算“第二季度考核表”。在“第二季度考核表”的C3，D3，E3，F(xiàn)3單元格中輸入公式，計算出編號為“0001”的員工第二季度的出勤量、工作態(tài)度、工作能力和季度總成績表數(shù)據(jù)。（3）向下拖動C3:F3單元格區(qū)域右下角的填充柄至F11單元格后釋放鼠標，計算出其他員工第二季度考核數(shù)據(jù)。這是第二種計算方法。最后用第二種方法計算出第三季度和第四季度的考核數(shù)據(jù)。

三、使用公式和函數(shù)計算年度考核表數(shù)據(jù)（1）計算員工的年度考核數(shù)據(jù)。分別在“年度考核表”的C3，D3，E3，F(xiàn)3單元格中輸入公式，計算出計算編號為“0001”員工的年度考核數(shù)據(jù)。（2）向下拖動C3:F3單元格右下角的填充柄到單元格F11后釋放鼠標，計算其他員工的年度考核數(shù)據(jù)。

（3）根據(jù)年度總成績進行排名。在G3單元格輸入公式，然后向下拖動G3單元格右下角的填充柄至G11單元格后釋放鼠標，計算出所有員工的年度總成績排名。（4）根據(jù)排名確定獎金額標準。在F14:G17單元格區(qū)域輸入排名和獎金標準數(shù)據(jù)。

（5）在H3單元格輸入公式，計算編號為0001的員工的獎金額。然后向下拖動H3單元格右下角的填充柄到H11單元格后釋放鼠標，計算出其他員工的獎金額。

任務四計算員工工資表數(shù)據(jù)制作思路：首先制作考勤表和基本工資表，然后根據(jù)基本工資表數(shù)據(jù)計算出獎金表，根據(jù)獎金表和基本工資表數(shù)據(jù)計算出補貼表數(shù)據(jù)，根據(jù)補貼表數(shù)據(jù)計算工資發(fā)放明細表中的部分數(shù)據(jù)，再根據(jù)公式和函數(shù)計算出工資發(fā)放明細表中的其他數(shù)據(jù)，最后制作工資條。

一、輸入考勤表、基本工資表數(shù)據(jù)（1）新建“員工工資”工作簿，分別將“Sheet1”～“Sheet3”工作表重命名為考勤表、基本工資表和獎金表，然后新建3個工作表：補貼表、工資發(fā)放明細表和工資條。（2）在“考勤表”和“基本工資表”中輸入數(shù)據(jù)并設置格式。（3）根據(jù)“考勤表”中的提示，在F3單元格輸入公式“=E3*100”，然后向下拖動F3單元格右下角的填充柄至F11單元格后釋放鼠標，計算出考勤扣款。

二、計算獎金表和補貼表數(shù)據(jù)（1）制作并計算獎金表數(shù)據(jù)。在“獎金表”制作表格，然后分別在A3，B3，C3，D3單元格輸入公式，將“基本工資表”中的數(shù)據(jù)引用到“獎金表”中。

（2）假設以所屬部門確定獎金額，即部門為辦公室的獎金額為1100，部門為財務部的為1000，部門為車間的為900，部門為供應部的為800，其他部門的為850。根據(jù)獎金標準，在E3單元格輸入公式，計算出編號為“0001”的員工的獎金額。（3）向下拖動A3:E3單元格區(qū)域右下角的填充柄至E11單元格后釋放鼠標，引用基本工資表數(shù)據(jù)并計算出所有員工的獎金額。

（4）制作并計算補貼表數(shù)據(jù)。在“補貼表”制作表格，然后分別在A3，B3，C3，D3單元格輸入公式，將“獎金表”中的數(shù)據(jù)引用到“補貼表”中。

（4）計算補貼。假設補貼是根據(jù)其所屬部門和基本工資進行確定的，即所屬部門為辦公室的，其補貼為其基本工資的16%，財務部的為其基本工資的15%，車間的為其基本工資的14%，供應部的為其基本工資的13%，其他部門的為11%。根據(jù)補貼標準，在E3單元格輸入公式，計算編號為“0001”員工的補貼額。（5）向下拖動A3:E3單元格區(qū)域右下角的填充柄至E11單元格后釋放鼠標，引用獎金表中的數(shù)據(jù)并計算所有員工的補貼額。

三、計算工資發(fā)放明細表數(shù)據(jù)（1）在制作工資發(fā)放明細表。在“工資發(fā)放明細表”制作表格。（2）分別在A4，B4，C4，D4，E4，F(xiàn)4，G4單元格輸入公式，將補貼表中的數(shù)據(jù)引用到“工資發(fā)放明細表”中，并根據(jù)“編號”列數(shù)據(jù)填充基本工資、獎金和補貼列數(shù)據(jù)。然后向下拖動A4:G4單元格右下角的填充柄到G12單元格后釋放鼠標，引用并計算數(shù)據(jù)。

（2