實訓(xùn)五-Excel綜合實訓(xùn)-制作教工情況表(原版)

實訓(xùn)五Excel綜合實訓(xùn)——制作教工情況表情景描述下表是廣美學院藝術(shù)系教工6月份的工資表的局部數(shù)據(jù)，請你協(xié)助該系財務(wù)處的會計根據(jù)每位教工的職稱輸入補貼、應(yīng)發(fā)工資及實發(fā)工資，然后根據(jù)工資表作數(shù)據(jù)分析統(tǒng)計及制作圖表。圖4-5-1教工工資表選擇輸入教工的補貼，計算應(yīng)發(fā)工資及實發(fā)工資。圖4-5-2在下拉菜單中選擇輸入補貼設(shè)置工資表的行高、列寬、對齊方式、字體、邊框及底紋等，結(jié)果如下圖4-5-3。圖4-5-3格式化工資表統(tǒng)計出實發(fā)工資在1000以下、1200以下、1400以下及1400以上的人數(shù)，然后根據(jù)人數(shù)制作餅圖，如下圖4-5-3。圖4-5-4實發(fā)工資分布圖圖4-5-3各職稱的平均實發(fā)工資任務(wù)分析為了順利完本錢次工作任務(wù)，我們除了需要穩(wěn)固復(fù)習工作表的編輯與格式化及工作表的管理、公式與函數(shù)的使用、圖表的建立與編輯、數(shù)據(jù)分析與管理等根本知識外，還需要具備以下知識。條件格式的使用。設(shè)置輸入數(shù)據(jù)的有效性。使用自定義序列排序。工作表的保護。任務(wù)分解將本次工作任務(wù)分為以下4個子任務(wù)。子任務(wù)1將工資表內(nèi)容補充完整并格式化工資表子任務(wù)2工作表的復(fù)制與管理子任務(wù)3制作實發(fā)工資分布情況圖子任務(wù)4統(tǒng)計各職稱平均實發(fā)工資并保護工作表操作步驟1、將工資表內(nèi)容補充完整并格式化工資表〔1〕翻開EXLX05.xls文件，在“水電費〞列前增加“補貼〞和“應(yīng)發(fā)工資〞列，在最后增加“科室〞列，在第一行前增加標題行。提示1：選擇F~G列可同時插入兩列；提示2：插入行后，合并A1:J1；〔2〕為“補貼〞列設(shè)置數(shù)據(jù)有效性并輸入，補貼發(fā)放標準為：教授為110元，副教授為90元，講師為70元，助講為50元，無職稱為10元。單元格提供下拉菜單，供選擇輸入；輸入信息標題及輸入信息如下圖4-5-2；出錯警告樣式為“停止〞、出錯信息為“不是“補貼〞值！〞，如圖4-5-4所示。圖4-5-4數(shù)據(jù)有效性的輸入出錯的提示信息步驟1：選擇F3:F15區(qū)域，執(zhí)行“數(shù)據(jù)〞菜單中的“有效性〞命令；步驟2：按照圖4-5-5、4-5-6、4-5-7所示分別設(shè)置有效性條件、輸入信息及出錯警告；圖4-5-5數(shù)據(jù)有效性的有效性條件圖4-5-6數(shù)據(jù)有效性的輸入信息圖4-5-7數(shù)據(jù)有效性的出錯警告步驟3：按照圖4-5-2所示選擇輸入每位教工的補貼值?！?〕使用公式計算應(yīng)發(fā)工資和實發(fā)工資，公式如下：應(yīng)發(fā)工資=根本工資+補貼實發(fā)工資=應(yīng)發(fā)工資-水電費，然后并使用函數(shù)根據(jù)編號計算科室，編號的第一個字母為“A〞即為“根底室〞、為“B〞即為“計算機室〞、為“C〞即為“電子室〞。提示：在J3單元格輸入如下公式，計算科室：=IF(LEFT(A3,1)="A","根底室",IF(LEFT(A3,1)="B","計算機室","電子室"))然后將公式向下填充?！?〕按照以下要求設(shè)置工資表格式〔1~3步驟略〕：1、標題行行高30、表頭行行高20、其余行高18；所有列調(diào)整為最適合的列寬；2、字體：標題華文中宋、18號、加粗，表頭楷體、12號、加粗；3、邊框與底紋：外邊框藍色最粗實線，內(nèi)豎線紅色虛線、內(nèi)橫線黑色實線，第1行下邊線雙實線；標題底紋茶色；4、條件格式：實發(fā)工資中大于等于1500的單元格底紋設(shè)置為黃色。步驟1：選擇I3:I15區(qū)域，執(zhí)行“格式〞菜單中的“條件格式〞命令；步驟2：如下圖4-5-8設(shè)置條件，然后單擊“格式〞按鈕，設(shè)置“圖案〞選項卡中的底紋顏色為黃色，確定。圖4-5-8設(shè)置條件格式2、工作表的復(fù)制與管理將Sheet1工作表中的數(shù)據(jù)復(fù)制到Sheet2、Sheet3中，并分別將三個工作表標簽重命名為“工資表〞、“數(shù)據(jù)統(tǒng)計〞、“數(shù)據(jù)分析〞。3、制作實發(fā)工資分布情況圖在“數(shù)據(jù)統(tǒng)計〞工作表的以A16為左上角的區(qū)域建立如下圖4-5-9的實發(fā)工資分段統(tǒng)計數(shù)據(jù)表，用學過的函數(shù)統(tǒng)計各分段的人數(shù)，結(jié)果如下圖，然后根據(jù)分段情況和人數(shù)建立如圖4-5-4所示的嵌入式別離型三維餅圖，格式要求如下：圖表標題：楷體、16號、加粗；數(shù)據(jù)標簽值：TimesNewRoman，12號、加粗；圖例：宋體、12號；扇形系列顏色：海綠、玫瑰紅、象牙色、天藍。圖4-5-9實發(fā)工資分段統(tǒng)計提示1：使用FREQUENCY函數(shù)計算各分段人數(shù)，分段點如下圖；提示2：選擇A16:A20,C16:C20區(qū)域作為圖表數(shù)據(jù)源。4、統(tǒng)計各職稱平均實發(fā)工資并保護工作表〔1〕在“數(shù)據(jù)分析〞工作表中以“職稱〞為關(guān)鍵字按照教授、副教授、講師、助講、無職稱的順序排序步驟1：執(zhí)行“工具〞菜單中的“選項〞命令，在“自定義序列〞選項卡中輸入如下圖4-5-10的序列，然后單擊“添加〞，確定；圖4-5-10在自定義序列選項卡中添加序列步驟2：單擊數(shù)據(jù)清單中任意單元格，執(zhí)行“數(shù)據(jù)〞菜單中的“排序〞命令；步驟3：單擊“排序〞對話框中的“選項〞按鈕，彈出“排序選項〞對話框，在“自定義排序次序〞下拉菜單中選擇剛剛定義的新序列，確定；步驟4：在主關(guān)鍵字中選擇“職稱〞，方式為“升序〞，確定；〔2〕分職稱匯總實發(fā)工資的平均值。提示：分類字段為“職稱〞，匯總方式為“平均值〞，匯總項為“實發(fā)工資〞?！?〕保護工作表“數(shù)據(jù)分析〞，設(shè)定取消保護密碼為1324。步驟1：執(zhí)行“工具〞菜單中“保護〞子菜單的“保護工作表〞命令；步驟2：在“取消工作表保護時使用的密碼〞下輸入“1324〞。實訓(xùn)小結(jié)在本次實訓(xùn)中我們將制作教工情況表這項工作分解為4個子任務(wù)，通過完善工資表的內(nèi)容并修飾工作表，將工資表內(nèi)容復(fù)制到新的工作表并對三個工作表進行管理，根據(jù)統(tǒng)計出的實發(fā)工資各段人數(shù)制作實發(fā)工資分布情況圖表，最后根據(jù)職稱排列統(tǒng)計各職稱的平均實發(fā)工資，完成了本次任務(wù)。通過這次實訓(xùn)，穩(wěn)固復(fù)習了工作表的編輯與格式化、工作表管理、圖表的建立與編輯、數(shù)據(jù)管理與分析等知識，同時也學習了使用條件格式、設(shè)置數(shù)據(jù)有效性、使用自定義序列排序及保護工作表等新知識。課后拓展練習仿照這次實訓(xùn)的內(nèi)