EXCEL基本操作方法.doc

精品EXCEL應用項目 該部分由毛德梅老師執(zhí)筆。一、項目名稱*公司員工檔案及工資表的編輯與管理二、項目內容EXCEL電子表格目前已經得到廣泛應用，如財務部門的賬務單、員工工資單、學生成績單等。本項目要求學生運用Excel的基本功能完成*公司員工檔案及工資表的編輯與管理，通過完成項目任務讓學生掌握本軟件的基本技能。 三、項目目標l 了解Excel表格中常用術語（工作簿工作表、單元格、名稱框、編輯欄等）；l 掌握在Excel中輸入數(shù)據(jù)的各類方法；l 掌握數(shù)據(jù)及表格結構的格式化方法；l 掌握公式與函數(shù)的應用；l 掌握圖表的創(chuàng)建方法；l 熟練掌握數(shù)據(jù)排序、篩選、分類匯總；l 掌握數(shù)據(jù)透視表的使用。四、項目重點l Excel中數(shù)據(jù)的輸入和表格格式化；l 公式與函數(shù)的應用；l 圖表的創(chuàng)建方法；l 數(shù)據(jù)排序、篩選、分類匯總。五、項目難點l 表格格式化；l 數(shù)據(jù)排序、篩選、分類匯總。六、教學方法項目教學法七、教學過程根據(jù)項目內容進行介紹(教師)分析、指出各項任務（教師）簡單演示講解項目中各個任務點相關操作(根據(jù)項目規(guī)模分階段講解)學生完成項目任務、教師指導 (學生分階段實施)學生提交項目教師驗收、記錄登記學生項目效果成績八、時間安排制定項目、資料收集項目完成過程項目驗收評分項目內容分析及演示講解任務一二學生實施任務一二項目內容分析及演示講解任務三四學生實施任務三四課外時間20分鐘70分鐘15分鐘75分鐘課外時間九、項目要求根據(jù)已給的初始Excel文件“員工檔案及工資表.xlsx”，按照項目任務要求，完成工作表的重命名、刪除、數(shù)據(jù)填充、格式化等操作，同時根據(jù)項目任務完成“排序與篩選表”、“部門實發(fā)工資分類匯總表”和“數(shù)據(jù)透視表”的制作。具體任務要求見“十、項目任務分析與實施”，通過該綜合項目訓練，使學生能夠掌握Excel的基本操作技能。十、項目任務分析與實施（一）項目的需求分析 由教師授課時分析（二）項目任務分解任務一、工資表的編輯（20分，每小題34分）1. 打開“員工檔案及工資表.xlsx” 文件，在“員工編號”列A3單元格中輸入序號“DF001”。使用自動填充功能將員工編號填充到A22。2. 合并A1：O1單元格，并設置為標題居中，設置單元格標題樣式為“60%強調文字顏色6”。3. 將A2：O22單元格區(qū)域設置為水平對齊和垂直對齊都為“居中”。將2到22行行高設置為18。4. 將A2：O22單元格區(qū)域邊框設置為：“外邊框”為“粗線”，“內邊框”為“細線”。將A2：O22單元格區(qū)域樣式設置為：“表樣式中等淺色13”。5將工作表“sheet1”改名為“員工檔案及工資表”，標簽顏色為紅色，將“sheet2” 的工作表名改為“基礎數(shù)據(jù)”，將“sheet3”刪除。6采用條件格式的設置方法，將 “基本工資”列中大于 “10000”的單元格設置為“淺紅填充色深紅色文本”。任務二、工資表數(shù)據(jù)的計算（30分，27題各5分）1. 打開Excel文件“員工檔案及工資表.xlsx”。2. 從身份證號中提取員工出生日期，并填充到“出生日期”列。工作表中的第F列已事先輸入了員工的身份證號，身份證號的第7位第14位為出生年月日。首先通過截取字符串函數(shù)MID依次提取出年月日，再通過文本合并函數(shù)CONCATENATE或連接運算符&將他們連接在一起形成出生日期。然后采用嵌套函數(shù)“=CONCATENATE(MID(F3,7,4),年,MID(F3,11,2),月,MID(F3,13,2),日)”或者采用公式“=MID(F4,7,4)&年&MID(F4,11,2)&月&MID(F4,13,2)&日”。 3. 計算所有員工年齡：年齡列中需要填入員工的周歲，首先通過當前日期函數(shù)TODAY獲取當前日期，然后減去該員工的生日日期，余額除以365天得到年限，再通過向下取整函數(shù)INT向下取整，得到員工的周歲年齡。如計算某一個年齡：在“年齡”列的H3單元格中輸入函數(shù)“=INT(TODAY()-G3)/365)”，按enter鍵確認。4. 在L3單元格中計算工資總額。5. 在交稅列中計算交稅數(shù)據(jù)。交稅的標準是“工資總額”不超過（含）10000的不交稅，超過10000的交超過部分的5%，個人所得稅稅率表及減除標準在“基礎數(shù)據(jù)”工作表中。6. “公積金”列中計算公積金：公積金扣除標準是“基本工資”的10%。7. 計算實發(fā)工資：實發(fā)工資=工資總額-交稅-公積金。效果如圖1所示：圖1任務三、工資表圖表的建立（10分，部分不對酌情減分）1.根據(jù)“員工檔案及工資表”中姓名和基本工資列建立二維簇狀柱形圖，效果如圖2所示：圖22. 將圖表中“基本工資”標題框修改為“東方公司員工基本工資”。3. 給圖標添加上坐標軸，橫坐標軸 標題為“姓名”，縱坐標軸 標題為“工資”。效果如圖3所示：圖3任務四、工資表數(shù)據(jù)的分析（40分，每小題10分）1. 數(shù)據(jù)排序（1）在“員工檔案及工資表.xlsx”中插入一張新工作表，命名為“排序與篩選表”。（2）將“員工檔案及工資表”工作表中的數(shù)據(jù)拷貝到“排序與篩選表”工作表中。（3）根據(jù)“實發(fā)工資”列將“員工檔案及工資表”降序排列。2. 數(shù)據(jù)篩選（1）利用“自動篩選”功能，篩選出“研發(fā)”部門“實發(fā)工資”大于或等于 “6000”，所有人員的工資信息。（2）利用“高級篩選”功能，篩選出“研發(fā)”部門“實發(fā)工資”大于或等于 “6000”，所有人員的工資信息。3.數(shù)據(jù)匯總（1）在“員工檔案及工資表.xlsx”中插入一張新工作表，命名為“部門實發(fā)工資分類匯總表”。（2）將“員工檔案及工資表”工作表中的數(shù)據(jù)拷貝（不帶格式復制）到“部門實發(fā)工資分類匯總表”。（3）根據(jù)“部門”匯總每個部門實發(fā)工資的平均值。結果如圖10所示：圖104.在一張新工作表中創(chuàng)建“數(shù)據(jù)透視表”，統(tǒng)計每個部門男女職工實發(fā)工資情況，效果如圖11所示。 圖11（三）操作步驟任務一具體步驟：1. 雙擊Excel原始數(shù)據(jù)文件“員工檔案及工資表.xlsx”，在“員工編號”所在A列A3單元格中輸入第一個序號“DF001”，把光標移至A3單元格右下處，待光標變成實心黑色“十”字形（填充柄），按住鼠標向下拉至A22，完成自動填充員工編號。2. 合并A1：O1單元格，把標題居中，設置單元格標題樣式。（1）選中A1：O1單元格區(qū)域，單擊“開始”選項卡下的“對齊方式”組中“合并后居中”按鈕； （2）選中A1單元格，單擊“開始”選項卡下的“樣式”組中“單元格樣式”按鈕，選擇“60%強調文字顏色6”和“標題”。3. 選中A2：O22單元格區(qū)域，單擊右鍵，選中“設置單元格格式”按鈕，選擇“對齊”選項卡，水平對齊和垂直對齊選擇都“居中”。選中2到22行，設置行高為18。4. 選中A2：O22單元格區(qū)域，單擊右鍵，選中“設置單元格格式”按鈕，選擇“邊框”選項卡，在“線條樣式” 中，單擊“粗線”，在預置中單擊“外邊框”，在“線條樣式” 中，單擊“細線”，在預置中單擊“內邊框”。選中A2：O22單元格區(qū)域，單擊“開始”選項卡下的“樣式”組中“套用表格樣式”按鈕，選擇“表樣式中等淺色13”（也可選其它）。5. 將工作表“sheet1”的標簽改為紅色并改名為“員工檔案及工資表”，將“sheet2” 的工作表名改為“基礎數(shù)據(jù)”，將“sheet3”刪除。6. 條件格式的設置：選中“基本工資”列J3：J22，單擊“開始”選項卡下的“樣式”組中“條件格式”按鈕，選擇“突出顯示單元格規(guī)則”，選擇“大于”，在對話框中輸入“10000”，設置為“淺紅填充色深紅色文本”（也可選其它）。任務二具體步驟：1. 雙擊打開Excel文件“員工檔案及工資表.xlsx”。2.在“出生日期”列的G3單元格中單擊，然后輸入嵌套函數(shù)“=CONCATENATE(MID(F3,7,4),年,MID(F3,11,2),月,MID(F3,13,2),日)”或者輸入公式“=MID(F4,7,4)&年&MID(F4,11,2)&月&MID(F4,13,2)&日”，按enter鍵確認（公式必須以等號“=”開始，公式中的運算符以西文半角字符輸入），然后向下填充公式到最后一個員工。3.在“年齡”列的H3單元格中輸入函數(shù)“=INT(TODAY()-G3)/365)”，按enter鍵確認，然后向下填充公式到最后一個員工。4.在“工資總額”列的L3單元格中輸入函數(shù)“=SUM(J3:K3)”或者輸入公式“=J3+K3”，按enter鍵確認，然后向下填充公式到最后一個員工。5.在“交稅”列的M3單元格中輸入函數(shù)“=IF(L310000,L3-基礎數(shù)據(jù)!$F$7,0)*基礎數(shù)據(jù)!$F$5”，按enter鍵確認，然后向下填充公式到最后一個員工。6.在“公積金”列的N3單元格中輸入公式“=J3*10%”，按enter鍵確認，然后向下填充公式到最后一個員工。7.在“實發(fā)工資”列的O3單元格中輸入公式“=L3-M3-N3”，按enter鍵確認，然后向下填充公式到最后一個員工。任務三具體步驟：1. 選定“員工檔案及工資表”工作表的單元格區(qū)域B2：B22，按住CTRL鍵選定J2：J22單元格區(qū)域；單擊“插入”選項卡下“圖表”組中“柱形圖”按鈕；彈出“二維柱形圖”對話框，單擊“二維柱形圖簇狀柱形圖”。2. 選中圖表中“基本工資”標題框，把標題修改為“東方公司員工基本工資”。3. 單擊“圖表工具布局”中“標簽”組中的“坐標軸標題”按鈕；彈出的對話框中選擇“主要橫坐標軸按鈕”，單擊“坐標軸下方標題”，在圖表中下方的橫坐標標題框中輸入“姓名”；單擊“圖表工具布局”中“標簽”組中的“坐標軸標題”按鈕；彈出的對話框中選擇“主要縱坐標軸按鈕”，單擊“豎排標題”，在圖表中下方的縱坐標標題框中輸入“工資”。任務四具體步驟： 1. 數(shù)據(jù)排序（1）雙擊Excel數(shù)據(jù)文件“員工檔案及工資表.xlsx”；插入一張新工作表，命名為“排序與篩選表”。（2）選定“員工檔案及工資表”工作表的單元格區(qū)域A1：O22，單擊鼠標右鍵，單擊“復制”，單擊“排序與篩選表”工作表，選中A1后單擊鼠標右鍵，單擊“粘貼選項”下的“保留原格式”按鈕。（3）將光標停留在有效數(shù)據(jù)區(qū)域內，單擊“開始”選項卡下的“編輯”組中的“排序和篩選”按鈕，在彈出的對話框中單擊“自定義排序”，彈出“排序”對話框，在“主關鍵字”列表框中選擇“實發(fā)工資”，在“次序”列表框中選擇“降序”，單擊“確定”按鈕，結果如圖4所示：圖42. 數(shù)據(jù)篩選（1）將光標停留在有效數(shù)據(jù)區(qū)域內，單擊“開始”選項卡下的“編輯”組中的“排序和篩選”按鈕，在彈出的對話框中單擊“篩選”；在每個字段的右側出現(xiàn)“篩選”按鈕，點擊“部門”字段的篩選按鈕，在出現(xiàn)的對話框中只勾選“研發(fā)”，單擊“確認”按鈕，點擊“實發(fā)工資”字段的篩選按鈕，在出現(xiàn)的對話框中選擇“數(shù)字篩選”，單擊“大于或等于”，在對話框中“大于或等于”后面填入“6000”。結果如圖5所示：圖5（2）單擊“開始”選項卡下的“編輯”組中的“排序和篩選”按鈕，在彈出的對話框中單擊“篩選”，取消自動篩選。在空白單元格內輸入篩選條件，如圖6所示：圖6然后將光標停留在有效數(shù)據(jù)區(qū)域內，單擊“數(shù)據(jù)”選項卡“排序和篩選”組中“高級”按鈕，彈出“高級篩選”對話框，在“方式”欄中選擇“將篩選結果復制到其他位置”， “列表區(qū)域”編輯框保持不動，單擊“條件區(qū)域”編輯框，選中圖6的條件區(qū)，單擊“復制到”編輯框，選中A30。如圖7所示：單擊“確認”按鈕即可。圖73. 數(shù)據(jù)匯總（1）雙擊Excel數(shù)據(jù)文件“員工檔案及工資表.xlsx”。插入一張新工作表，命名為“部門實發(fā)工資分類匯總表”。（2）選定“員工檔案及工資表”工作表的單元格區(qū)域A2：O22，單擊鼠標右鍵，單擊“復制”，單擊“部門實發(fā)工資分類匯總表”工作表，選中A1后單擊鼠標右鍵，單擊“粘貼選項”下的“值”按鈕（不帶格式復制）。（3）光標定位在“部門”列的任一有效數(shù)據(jù)單元格中，單擊“開始”選項卡中“編輯”組中的“排序和篩選”按鈕，單擊“升序”或“降序”，將相同的記錄放在一起。然后將光標定位在數(shù)據(jù)區(qū)域內，單擊“數(shù)據(jù)”選項卡中“分級顯示”組中的“分類匯總”按鈕，彈出“分類匯總”對話框，在“分類字段”中選擇“部門”，在“匯總方式”下拉列表框中選擇“平均值”，在“選定項匯總”列表框中選擇“實發(fā)工資”，如圖8所示：單擊“確定”按鈕即可。圖84數(shù)據(jù)透視表的創(chuàng)建（1）雙擊Excel數(shù)據(jù)文件“員工檔案及工資表.xlsx”，將光標定位在“員工檔案及工資表”工作表的