第3章 Excel數(shù)據(jù)處理與分析

第3章

Excel數(shù)據(jù)處理與分析第3章Excel數(shù)據(jù)處理與分析目錄員工信息表的制作工資表的計(jì)算管理與分析工資表工資圖表設(shè)計(jì)3.13.23.33.43.1員工信息表效果圖3.1員工信息表的制作3.1.1數(shù)據(jù)的編輯窗口元素名稱功能全選按鈕單擊可以選中當(dāng)前工作表中的所有單元格名稱欄顯示當(dāng)前活動(dòng)單元格的地址或名稱，如圖4-1中的E6就是名稱欄編輯欄用來顯示或編輯當(dāng)前單元格中的數(shù)據(jù)或公式等行標(biāo)用數(shù)字表示。行標(biāo)和列標(biāo)一起用來表示單元格的地址列標(biāo)用字母表示拆分條分水平拆分條和垂直拆分條，拖動(dòng)拆分條可以將窗口拆分成四個(gè)部分，要取消拆分，則在拆分線上雙擊鼠標(biāo)。活動(dòng)單元格當(dāng)前正在編輯的單元格，如上圖E6為活動(dòng)單元格工作表標(biāo)簽工作簿底端的標(biāo)簽，用于顯示工作表的名稱，在工作簿窗口中單擊某個(gè)工作表標(biāo)簽，則該工作表成為當(dāng)前工作表，可以對(duì)它進(jìn)行編輯。任務(wù)實(shí)施步驟1 新建Excel工作簿，命名為員工信息表.xlsx。步驟2：在sheet1工作表中，選擇單元格A1，輸入標(biāo)題“匯通科技有限公司員工信息表”，按Enter鍵。步驟3：在單元格A2中，輸入“員工編號(hào)”，按向右方向鍵→或Tab鍵，使B2單元格成為當(dāng)前單元格，輸入“姓名”。步驟4：用相同的方式依次輸入其他列標(biāo)題。任務(wù)實(shí)施（續(xù)）步驟5：單擊單元格A3，輸入“’001”按回車后，鼠標(biāo)指針指向A3單元格的“填充柄”（位于單元格右下角的小黑塊），此時(shí)鼠標(biāo)指針變?yōu)楹谑?，按住鼠?biāo)向下拖動(dòng)填充柄，拖動(dòng)至目標(biāo)單元格時(shí)釋放鼠標(biāo)自動(dòng)填充文本數(shù)據(jù)任務(wù)實(shí)施（續(xù)）步驟6：用相同的方式輸入“姓名”、“職務(wù)”、“學(xué)歷”列中的其他數(shù)據(jù)。步驟7：輸入“性別”列，選中C3單元格，輸入“男”，使用填充柄，將所有員工的性別都輸入“男”，在按住【ctrl】鍵的同時(shí)選中性別為“女”的單元格，輸入一個(gè)“女”，同時(shí)按【ctrl+enter】組合鍵，那么選中的所有單元格中都輸入“女”。步驟8：輸入工作日期，選擇單元格G3，輸入“2002/6/5”或“2002-6-5”，用相同的方式輸入其他日期數(shù)據(jù)。3.1員工信息表的制作3.1.2

數(shù)據(jù)的修飾設(shè)置數(shù)字格式設(shè)置對(duì)齊方式設(shè)置字體格式設(shè)置邊框調(diào)整行高和列寬自動(dòng)套用單元格樣式設(shè)置條件格式3.1.2數(shù)據(jù)的修飾-設(shè)置數(shù)字格式

3.1.2數(shù)據(jù)的修飾-設(shè)置對(duì)齊方式3.1.2數(shù)據(jù)的修飾-設(shè)置邊框任務(wù)實(shí)施步驟1 選中A1：G1區(qū)域。步驟2：單擊“開始”選項(xiàng)卡的“對(duì)齊方式”組中的“合并后居中”按鈕，然后單擊“開始”選項(xiàng)卡的“樣式”組中的“單元格樣式”按鈕，在打開的下拉列表框中選擇“標(biāo)題”樣式。步驟3：選中A2:G2區(qū)域，單擊“開始”選項(xiàng)卡的“字體”組中的“字體”按鈕，在打開的下拉列表框中選擇“楷體”選項(xiàng)；單擊“字號(hào)”下拉列表按鈕選擇“10”選項(xiàng)，對(duì)齊方式為居中對(duì)齊，填充顏色為綠色。步驟4：選中A2：G34單元格區(qū)域，單擊“開始”選項(xiàng)卡的“樣式”組中的“套用表格”按鈕，在打開的列表框中選擇“表樣式淺色16”樣式。任務(wù)實(shí)施(續(xù))步驟5：單擊“數(shù)據(jù)”選項(xiàng)卡的“排序和篩選”組中的“篩選”按鈕，關(guān)閉單元格篩選。步驟6：單擊“開始”選項(xiàng)卡的“對(duì)齊方式”組中的“居中”按鈕，設(shè)置水平和垂直居中對(duì)齊。步驟7：選中F3：F34單元格區(qū)域。步驟8：單擊“開始”選項(xiàng)卡“樣式”組中的“條件格式”按鈕，彈出“條件格式”下拉列表，選擇“突出顯示單元格規(guī)則”子菜單中的”文本包含”命令，彈出“文本中包含”對(duì)話框，如下圖所示，選擇設(shè)置為下拉列表中的自定義格式，設(shè)置字體為“紅色”，填充效果為“黃色”。步驟9：選中A2：G34單元格區(qū)域，右鍵單擊選擇“設(shè)置單元格樣式”，然后點(diǎn)擊“邊框”選項(xiàng)卡，選擇左邊的樣式為實(shí)現(xiàn)，預(yù)置“外邊框”，然后選擇樣式的虛線樣式，預(yù)置“內(nèi)框線”，效果如下圖所示。步驟10：雙擊工作表“sheet1”的標(biāo)簽，標(biāo)簽出現(xiàn)反白（黑底白字）時(shí)，輸入新的工作表名稱“員工信息表”。3.1.3任務(wù)拓展：制作考勤表【拓展目的】①掌握工作表的基本操作方法，掌握數(shù)據(jù)的輸入格式。②掌握工作表中文本的修飾。3.2工資表的計(jì)算3.2.1任務(wù)描述本任務(wù)主要是通過Excel自帶的計(jì)算功能，計(jì)算出每個(gè)員工的相應(yīng)數(shù)據(jù)如工齡、獎(jiǎng)金、應(yīng)發(fā)工資、實(shí)發(fā)工資等各項(xiàng)數(shù)據(jù)一月份獎(jiǎng)金表效果圖員工信息表效果圖一月份應(yīng)發(fā)工資效果圖一月份實(shí)發(fā)工資總表效果圖3.2工資表的計(jì)算3.2.2使用公式運(yùn)算符使用公式函數(shù)單元格的引用使用函數(shù)任務(wù)實(shí)施步驟1：用最大值函數(shù)MAX，在“一月份獎(jiǎng)金表”中，計(jì)算公司員工所得的“最高獎(jiǎng)金額”①選擇單元格J2。②單擊“開始”選項(xiàng)卡“編輯”組中的“自動(dòng)求和”按鈕右側(cè)的下三角符號(hào)，從彈出的下拉菜單中選擇“最大值”命令。此時(shí)單元格中出現(xiàn)了求最大值函數(shù)“MAX（）”，并用鼠標(biāo)在工作表選擇參數(shù)范圍F3:F34，單擊Enter鍵或單擊編輯欄的“輸入”按鈕確認(rèn)。在單元格J2中顯示出計(jì)算結(jié)果。步驟2：用最小值函數(shù)MIN、求和函數(shù)SUM、求平均值函數(shù)AVERAGE，在“一月份獎(jiǎng)金表”中，計(jì)算出公司員工的“最低獎(jiǎng)金額”、“總獎(jiǎng)金額”、“平均獎(jiǎng)金額”①選擇單元格J3。②單擊“開始”選項(xiàng)卡“編輯”組中的“自動(dòng)求和”按鈕右側(cè)的下三角符號(hào)，從彈出的下拉菜單中選擇“最小值”命令，直接用鼠標(biāo)在工作表中重新選擇參數(shù)范圍“F3:F34”，單擊Enter鍵，在J3單元格中顯示計(jì)算結(jié)果。③選擇單元格J4，重復(fù)步驟②，注意將“最小值”命令改為“求和”，就可以計(jì)算出“總獎(jiǎng)金額”。④選擇單元格J5，重復(fù)步驟②，注意將“最小值”命令改為“平均值”，就可以計(jì)算出“平均獎(jiǎng)金額”。步驟3：用排序函數(shù)RANK，計(jì)算出公司員工所得獎(jiǎng)金的“名次”①選中G3單元格。②單擊編輯欄左側(cè)的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對(duì)話框，在“搜索函數(shù)”框中輸入“rank”，單擊“轉(zhuǎn)到”按鈕，Excel2010自動(dòng)搜索相關(guān)函數(shù)，如圖3-45所示。選擇“RANK”函數(shù)，單擊“確定”按鈕，打開“函數(shù)參數(shù)”對(duì)話框。③定位到第一個(gè)參數(shù)“Number”框，選擇F3單元格；定位到第二個(gè)參數(shù)”Ref”框，選擇區(qū)域“F3：F34”，如圖3-46所示。④單擊“確定”按鈕，在G3單元格中，計(jì)算出第1個(gè)員工的“名次”。拖動(dòng)G3單元格中的填充柄至單元格G34，計(jì)算出所有員工所獲獎(jiǎng)金的名次。步驟4:使用日期函數(shù)TODAY、YEAR計(jì)算出每個(gè)員工的“工齡”計(jì)算“工齡”的公式為：工齡=當(dāng)前年份-參加工作年份。其中在計(jì)算“當(dāng)前年份”時(shí)，先使用TODAY函數(shù)獲取系統(tǒng)日期，再用YEAR函數(shù)獲取當(dāng)年的年份，即當(dāng)前年份=YEAR(TODAY())；參加工作年份=YEAR（工作日期）。①切換到“員工信息”表。②單擊單元格H3。③單擊“公式”選項(xiàng)卡“函數(shù)庫”組中的“日期和時(shí)間”按鈕右側(cè)的下三角符號(hào)，從彈出的下拉菜單中選擇“TODAY”命令，此時(shí)H3單元格中顯示當(dāng)前系統(tǒng)的日期，編輯欄中顯示“=TODAY()”。④選中“TODAY()”，按Ctrl+X鍵，將選定內(nèi)容剪切到剪貼板上。⑤在“日期和時(shí)間”按鈕的下拉列表中選擇“YEAR”命令，打開“函數(shù)參數(shù)”對(duì)話框。⑥將插入點(diǎn)放置到參數(shù)處，按Ctrl+V鍵，將剪貼板中的內(nèi)容粘貼到該處，如圖3-48所示。⑦單擊“確定”按鈕。將插入點(diǎn)放置到編輯欄中“=YEAR(TODAY())”的后面，輸入“-YEAR(G3)”，按Enter鍵，完成計(jì)算“工齡”公式的輸入。⑧設(shè)置H3單元格的數(shù)據(jù)類型為“常規(guī)”，再雙擊H3單元格右下角的填充柄，計(jì)算出其他員工的“工齡”。3.3管理與分析工資表3.3管理與分析工資表3.3.1排序簡單排序多關(guān)鍵字排序3.3.2篩選1.自定篩選自動(dòng)篩選是指按照某一條件進(jìn)行的數(shù)據(jù)篩選。2.高級(jí)篩選自動(dòng)篩選可以實(shí)現(xiàn)同字段之間的“或”、“與”運(yùn)算，也可以實(shí)現(xiàn)不同字段之間的“與”運(yùn)算，但是不能實(shí)現(xiàn)多字段之間的“或”運(yùn)算。當(dāng)篩選出滿足字段間條件為“或”關(guān)系的記錄時(shí)，必須使用高級(jí)篩選才能實(shí)現(xiàn)。3.3.3分類匯總分類匯總在做分類匯總之前，需先按照某個(gè)關(guān)鍵字排序，然后按照該字段進(jìn)行分類匯總。嵌套分類匯總所謂嵌套分類匯總，是指用兩個(gè)或兩個(gè)以上關(guān)鍵字段作為分類字段的多次匯總方式。3.3.4數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互式的數(shù)據(jù)透視表，可以對(duì)工作表照片那個(gè)的數(shù)據(jù)進(jìn)行匯總和分析，主要用于深入分析數(shù)據(jù)。任務(wù)實(shí)施步驟1：將“一月份應(yīng)該工資表”創(chuàng)建6個(gè)副本，分別重命名為“簡單排序”、“多關(guān)鍵字排序”、“自動(dòng)篩選”、“高級(jí)篩選”、“分類匯總”和“嵌套分類匯總”。步驟2：切換到“簡單排序”工作表中，將鼠標(biāo)置于“應(yīng)發(fā)工資”列的任一單元格。步驟3：單擊【開始】選項(xiàng)卡【編輯】組中的【排序和篩選】按鈕，從彈出的列表中，選擇【降序】命令，設(shè)置所有數(shù)據(jù)按應(yīng)發(fā)工資降序排序。步驟4：切換到“多關(guān)鍵字排序”工作表中，將光標(biāo)定位于數(shù)據(jù)區(qū)域，再次單擊【開始】選項(xiàng)卡【編輯】組中的【排序和和篩選】按鈕，在彈出的下拉菜單中，選擇【自定義排序】命令，打開【排序】對(duì)話框，參數(shù)設(shè)置如圖3-66所示。步驟5：切換到“自動(dòng)篩選”表中，定位數(shù)字區(qū)域，單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組中的【篩選】按鈕，單擊“姓名”列標(biāo)題旁的下拉箭頭，在彈出的下拉框中，選擇【文本篩選】子列表中的【開頭是】命令，彈出【自定義自動(dòng)篩選方式】對(duì)話框，參數(shù)設(shè)置如圖3-67所示。步驟6：在“工齡”列標(biāo)題的下拉列表，在打開的下拉列表中選擇“數(shù)字篩選”子列表中的【小于或等于】命令，參數(shù)設(shè)置如圖3-68所示。步驟7：切換到“高級(jí)篩選”工作表中，篩選出“開發(fā)部”獎(jiǎng)金大于等于500或者“客服部”的“應(yīng)發(fā)工資”大于等于10000，具體的條件設(shè)置如圖3-69所示，篩選對(duì)話框如圖3-70所示。步驟8：切換到“分類匯總”工作表中，使用分類匯總求出各部門“工齡工資”、“獎(jiǎng)金”的最大值。將光標(biāo)定位于數(shù)字區(qū)域，單擊單擊【數(shù)據(jù)】選項(xiàng)卡【分級(jí)顯示】組中的【分類匯總】按鈕，打開【分類匯總】對(duì)話框，參數(shù)設(shè)置如圖3-71所示。步驟9：切換到“嵌套分類匯總”工作表，匯總各部門男女職工的工齡工資和獎(jiǎng)金的最小值。先按“部門”和“性別”排序，“部門”的分類和上圖一樣，再次打開【分類匯總】按鈕，打開【分類匯總】對(duì)話框，參數(shù)設(shè)置如圖3-72所示。3.4工資圖表設(shè)計(jì)3.4.1創(chuàng)建圖表圖表圖表可以更好地使所處理的數(shù)據(jù)以更加直觀、生動(dòng)、清晰地顯示不同數(shù)據(jù)間的差異，更好地顯示出發(fā)展趨勢(shì)和分布狀況，便于用戶更好地理解各種數(shù)據(jù)之間的相互關(guān)系，使用戶一目了然地看清數(shù)據(jù)的大小、差異和變化趨勢(shì)。同時(shí)當(dāng)工作表區(qū)域的數(shù)據(jù)發(fā)生變化時(shí)，圖表中對(duì)應(yīng)的數(shù)據(jù)也自動(dòng)更新，可以同步顯示數(shù)據(jù)。創(chuàng)建圖表Excel2016中的圖表有兩種放置方式，一種是嵌入圖，即將數(shù)據(jù)和圖表顯示在一張工作表中；另外一種是將圖表單獨(dú)放置在一張工作表中。3.4.2修改圖表調(diào)整圖表的大小和位置要調(diào)整圖表的大小，先選中圖表，然后將鼠標(biāo)光標(biāo)移到圖表的淺藍(lán)色邊框的控制點(diǎn)上，當(dāng)鼠標(biāo)光標(biāo)變成可調(diào)整光標(biāo)時(shí)，拖動(dòng)鼠標(biāo)左鍵即可調(diào)整圖表的大小。更改圖表類型用戶需要更改已經(jīng)創(chuàng)建好的圖表的類型，可以單擊“設(shè)計(jì)”選項(xiàng)卡的“類型”組中的“更改圖表類型”按鈕，彈出“更改圖表類型”對(duì)話框編輯圖表數(shù)據(jù)源圖表創(chuàng)建好后，可以根據(jù)需要隨時(shí)更新圖表所使用的數(shù)據(jù)源。選中圖表，單擊“設(shè)計(jì)”選項(xiàng)卡的“數(shù)據(jù)”組中的“選擇數(shù)據(jù)”按鈕，彈出“選擇數(shù)據(jù)源”對(duì)話框，單擊“圖表數(shù)據(jù)區(qū)域”右側(cè)的折疊按鈕，返回到工作表中重新選擇數(shù)據(jù)源區(qū)域，選擇完畢，確認(rèn)無誤，單擊“確定”按鈕，即可完成數(shù)據(jù)源的更新。交換圖表的行與列創(chuàng)建圖表后，橫坐標(biāo)和縱坐標(biāo)可以進(jìn)行調(diào)整。單擊“設(shè)計(jì)”選項(xiàng)卡的“切換行/列”按鈕即可。設(shè)置坐標(biāo)軸與標(biāo)題用戶可以設(shè)置圖表中的坐標(biāo)軸的顯示方式，從而使水平和垂直坐標(biāo)的內(nèi)容更加豐富，步驟也是單擊“布局”選項(xiàng)卡“標(biāo)簽”組中的“坐標(biāo)軸標(biāo)題”按鈕，在打開的下拉列表框中選擇“主要橫坐標(biāo)軸標(biāo)題”或“主要縱坐標(biāo)軸標(biāo)題”選項(xiàng)，再從其子菜單中選擇設(shè)置項(xiàng)。任務(wù)實(shí)施步驟1：將圖3-79的圖表，右鍵單擊，選擇“移動(dòng)圖表”，選擇“新工作表”按鈕，并將新的工作