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

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