Excel辦公中的數(shù)據(jù)分析與處理課件

1、Excel-辦公中的數(shù)據(jù)分析與處理1第第 3 3 章章Excel-辦公中的數(shù)據(jù)分析與處理2【本章內容介紹】n在辦公業(yè)務實踐中，經常需要對大量的業(yè)務數(shù)據(jù)進行分析和處理。如職工工資表中工資的計算、匯總、分析，公司產品銷售表中銷售額的統(tǒng)計、匯總、合并計算以及人事考勤表中職工請假、曠工、出勤等情況的分析統(tǒng)計等。n本章采用實例形式，以Excel軟件操作為例介紹辦公業(yè)務實踐中常見的數(shù)據(jù)分析與處理方法，主要包括數(shù)據(jù)的排序、篩選、分類匯總、數(shù)據(jù)透視、圖表制作、公式與函數(shù)運用以及數(shù)據(jù)關聯(lián)表格的操作。Excel-辦公中的數(shù)據(jù)分析與處理3【本章主要知識點】nExcel表格中內容填充的常用技巧nExcel數(shù)據(jù)庫表格的

2、特殊格式設置nExcel表格中數(shù)據(jù)有效性的設置nExcel數(shù)據(jù)庫表格中的排序與篩選nExcel數(shù)據(jù)庫表格中的分類匯總操作nExcel數(shù)據(jù)庫表格中的數(shù)據(jù)透視分析nExcel中的數(shù)據(jù)圖表制作nExcel表格中公式和函數(shù)的使用nExcel中的關聯(lián)表格操作Excel-辦公中的數(shù)據(jù)分析與處理44.1 EXCEL數(shù)據(jù)分析與處理概述n在辦公業(yè)務中，除了文字和表格之外，還需要經常進行有關數(shù)據(jù)處理，有時候還需要將處理的數(shù)據(jù)結果用一種圖表來進行表現(xiàn)。Excel-辦公中的數(shù)據(jù)分析與處理51Excel中的數(shù)據(jù)庫表格簡介n數(shù)據(jù)庫就是與特定主題相聯(lián)系的信息集合。在Excel中，數(shù)據(jù)庫實際上就是工作表中的一個區(qū)域，是一個二

3、維表。Excel-辦公中的數(shù)據(jù)分析與處理62Excel中的數(shù)據(jù)分析與處理nExcel中有很多可以進行的數(shù)據(jù)分析和數(shù)據(jù)處理功能，包括數(shù)據(jù)排序、數(shù)據(jù)篩選、分類匯總、數(shù)據(jù)透視、合并計算、關聯(lián)表格處理等。n本章將對上一章的實例進一步進行介紹，包括對報名匯總表進行數(shù)據(jù)分析，例如：按照某個主題排序、按照指定條件篩選、按照指定字段進行分類匯總、對整個表格進行數(shù)據(jù)透視分析等；同時，本章還將利用Excel中的公式和函數(shù)進行筆試成績的計算、篩選、查詢，與面試成績進行關聯(lián)表格的綜合處理等。Excel-辦公中的數(shù)據(jù)分析與處理73Excel中的數(shù)據(jù)圖表制作n利用Excel，可以輕松、快捷地制作各種數(shù)據(jù)圖表。n這些數(shù)據(jù)圖

4、表可將枯燥的數(shù)字形象化，并且通過數(shù)據(jù)圖表可以了解到數(shù)據(jù)之間的相互關系和變化趨勢。n數(shù)據(jù)圖表的類型很多，在辦公實踐中，使用較多的主要有柱形圖、條形圖、折線圖、餅圖、散點圖等。Excel-辦公中的數(shù)據(jù)分析與處理84.2 “報名信息匯總表”數(shù)據(jù)庫的建立4.2.1 建立“報名匯總表”數(shù)據(jù)庫框架4.2.2 “編號”列的自動序列填充4.2.3 定義單元格數(shù)據(jù)的有效性4.2.4 使用數(shù)據(jù)清單添加記錄Excel-辦公中的數(shù)據(jù)分析與處理9制作效果Excel-辦公中的數(shù)據(jù)分析與處理104.3 對“報名匯總表”進行數(shù)據(jù)分析n本節(jié)介紹利用Excel中的有關功能，對“報名信息匯總表”進行數(shù)據(jù)分析的操作方法，主要包括數(shù)據(jù)

5、的排序、篩選、分類匯總與數(shù)據(jù)透視等功能。Excel-辦公中的數(shù)據(jù)分析與處理114.3.1 對“報名信息匯總表”內容進行排序1排序的依據(jù)2按單個字段排序3按多級字段排序4特殊排序方式Excel-辦公中的數(shù)據(jù)分析與處理124.3.2 對“報名信息匯總表”內容進行篩選n1自動篩選n2高級篩選n3高級篩選時的注意事項Excel-辦公中的數(shù)據(jù)分析與處理134.3.3 對“報名信息匯總表”中數(shù)據(jù)分類匯總n使用分類匯總，能夠在數(shù)據(jù)庫適當位置加上統(tǒng)計結果，使數(shù)據(jù)庫變得清晰易懂。n對于本例，根據(jù)需要，可以對“性別”數(shù)據(jù)進行分類匯總，以便了解不同性別報名情況；也可以按照“學歷”進行分類匯總，以便了解不同學歷的報名

6、人數(shù)；可以對“報考職位”進行匯總，以便了解各個不同職位的報名情況。n在執(zhí)行分類匯總命令之前，首先應該對數(shù)據(jù)庫按照待匯總字段進行排序。Excel-辦公中的數(shù)據(jù)分析與處理144.3.4 對“報名信息匯總表”進行數(shù)據(jù)透視分析n1數(shù)據(jù)透視表（圖）的概念n2數(shù)據(jù)透視表的創(chuàng)建n3創(chuàng)建數(shù)據(jù)透視圖n4數(shù)據(jù)透視表的更新n5刪除數(shù)據(jù)透視表Excel-辦公中的數(shù)據(jù)分析與處理154.4 利用數(shù)據(jù)圖表表現(xiàn)數(shù)據(jù)之間關系nExcel數(shù)據(jù)圖表能將工作表中的數(shù)據(jù)變?yōu)榉浅Ｖ庇^的圖形格式，并且從圖表上很容易看出結果，以及數(shù)據(jù)之間的關系和數(shù)據(jù)的發(fā)展趨勢。Excel-辦公中的數(shù)據(jù)分析與處理164.4.1 利用圖表向導創(chuàng)建條形圖n利用圖

7、表向導方法可以創(chuàng)建嵌入到原工作表中的柱形圖表，該圖表可以用來反應各地報考各種不同職位的人數(shù)情況，其樣式效果如圖4-27所示。 Excel-辦公中的數(shù)據(jù)分析與處理174.4.2 數(shù)據(jù)圖表的格式設置與內容更新1圖表的組成2圖表的格式設置3數(shù)據(jù)圖表的內容更新Excel-辦公中的數(shù)據(jù)分析與處理184.4.3 利用快捷鍵建立單獨的圖表并進行格式設置n在上面的向導方法制作圖表外的最后一個步驟中選擇“作為新工作表插入”，即可制作一個單獨的圖表工作表。除此之外，Excel中還可以利用F11或Alt+ F1功能鍵快速制作單獨的柱狀圖表，然后再進行三維效果以及其他項目的格式設置和修飾， Excel-辦公中的數(shù)據(jù)分

8、析與處理194.4.4 選取不連續(xù)區(qū)域制作表現(xiàn)各職位報考人數(shù)比例的餅圖n對不連續(xù)區(qū)域的數(shù)據(jù)，也可以制作圖表，這在餅狀圖制作中非常普遍。圖4-36的餅圖就是如此制作的，它用來反應各個崗位報名人數(shù)在總人數(shù)中的比例。 圖4-36 選取不連續(xù)數(shù)據(jù)區(qū)域制作的餅圖Excel-辦公中的數(shù)據(jù)分析與處理204.5 筆試成績的數(shù)據(jù)處理n上面的報名情況經過審核、分析和匯總之后，下一個環(huán)節(jié)就是要對合格考生進行統(tǒng)一設置準考生編號，并舉行統(tǒng)一考試，包括筆試和面試。在本節(jié)重點介紹考試成績的處理方法，主要用到了Excel中的數(shù)據(jù)庫表格設置、內容錄入技巧、公式和函數(shù)使用以及關聯(lián)表格操作等。Excel-辦公中的數(shù)據(jù)分析與處理21

9、4.5.1 筆試成績處理表的框架建立n招錄過程的第一步是筆試，為此先建立筆試成績數(shù)據(jù)庫表格框架。n建立好的數(shù)據(jù)庫格式框架如下圖所示，還需要進行數(shù)據(jù)輸入和格式設置工作。 Excel-辦公中的數(shù)據(jù)分析與處理224.5.2 成績處理表中原始數(shù)據(jù)的輸入表格框架建立后，下一步就是要輸入原始數(shù)據(jù)，輸入時候需要注意以下幾點：（1）“編號”字段可利用自動序列輸入，為了使A4 單元格中顯示出“001”效果，需要在輸入時現(xiàn)以單引號（）開頭，否則輸入的“001”將只是顯示為“1”。（2）“性別”字段可以設置有效性，或者利用列表選擇輸入。（3）三門考試課程對應的三個字段，需要先設置數(shù)據(jù)有效性（整數(shù)，范圍0100），然

10、后再進行數(shù)據(jù)的輸入，這樣才能較好地避免數(shù)據(jù)輸入時的錯誤。Excel-辦公中的數(shù)據(jù)分析與處理234.5.3 數(shù)據(jù)庫表格的格式設置n1數(shù)據(jù)庫標題格式設置n2字段標題格式設置n3條件格式化設置n4表格邊框線設置n5間隔底紋效果的設置Excel-辦公中的數(shù)據(jù)分析與處理244.5.4 用公式計算筆試成績n筆試成績的最后結果需要使用公式來進行運算。n公式的有關知識：n公式是對工作表中的數(shù)據(jù)進行計算和分析的一種等式，它可以對工作表數(shù)值進行算術運算、關系運算和邏輯運算，同樣也可以對字符數(shù)據(jù)以及其他數(shù)據(jù)進行運算。公式可以引用同一工作表中的其他單元格、同一工作簿不同工作表中的單元格，或者其他工作簿的工作表中的單元

11、格。Excel-辦公中的數(shù)據(jù)分析與處理25公式輸入應該遵循的基本規(guī)則（1）公式輸入時必須以“=”開頭，公式中間不能包括空格。（2）公式中的單元格引用，可以在編輯區(qū)直接輸入，也可以通過單擊該單元格或拖動單元格區(qū)域從而實現(xiàn)自動填入。（3）用算術運算符號：+（加）、（減）、*（乘）、/（除）、（乘方），字符運算符號&(連接字符串)以及關系運算符號（大于）、（小于）、=（等于）等來說明公式的操作類型。（4）在公式中可以使用圓括號，也只能使用圓括號，并且允許圓括號嵌套，但是應該注意嵌套時前后括號的匹配關系，計算順序是先內層再外層。（5）單元格中的公式內容顯示在編輯欄中，計算結果則顯示在單元格內，

12、如果希望直接在單元格中顯示為公式，可以使用Ctrl+鍵（在Tab鍵的上面），再次鍵入，則取消公式顯示，重新顯示結果。Excel-辦公中的數(shù)據(jù)分析與處理264.5.5 利用RANK函數(shù)確定筆試名次n在Excel 中，名次的確認最好使用RANK函數(shù)，該函數(shù)是專門進行排名次的函數(shù)，其結果是返回一個數(shù)值在一組數(shù)值中的排位。nRANK函數(shù)的語法格式為： RANK(number,ref,order)。其中：Number 為需要找到排位的數(shù)字；Ref 為包含一組數(shù)字的數(shù)組或引用；Order 為一數(shù)字，指明排位的方式，為 0 或省略，按降序排列排位，不為零，按升序排列進行排位。 n對本例中筆試成績的名次排列，

13、使用RANK函數(shù)并進行函數(shù)拖動復制即可。同樣先選擇H4單元格，在編輯欄中輸入公式“=RANK(G4, $G$4:$G$33)”，然后按回車鍵，即可得到第一個考生的名次“4”，采用與上面同樣的方法拖動復制得到每個考生的名次。Excel-辦公中的數(shù)據(jù)分析與處理274.5.6 利用IF函數(shù)確定考生是否通過筆試考試在Excel 中，利用IF函數(shù)可以非常方便地進行條件判斷。本實例中，名次確認完成后，根據(jù)考試規(guī)則要求，假設只能選取前40%（即前12名考生進入面試階段。則“是否通過”一列可以按照如下步驟進行操作。（1）將鼠標定位到I4 單元格。（2）在I4 單元格中輸入：=IF(H4=12,可以,)。（3）

14、單擊編輯欄上的確定按鈕，則I4 單元格中顯示出計算結果“可以”。（4）選取I4 單元格，拖動單元格右下角的拖動柄一直到I33。（5）松開鼠標，則I5到I33 中部分單元格將會顯示出“可以”樣式，如圖4-46所示，說明這些人都在前12名之內，順利通過了筆試考試階段。Excel-辦公中的數(shù)據(jù)分析與處理284.5.7 利用自動篩選獲取通過筆試人員的情況n對于通過筆試考試的考生，還需要進行面試，為此需要將他們的記錄從數(shù)據(jù)庫中篩選出來。將筆試成績合格者篩選出來，可以利用自動篩選功能實現(xiàn)。 Excel-辦公中的數(shù)據(jù)分析與處理294.5.8 綜合成績表的創(chuàng)建n將Sheet3工作表更名為“綜合成績表”，并制作

15、如圖4-49樣式表格。該表格中 “編號”、“姓名”、“筆試成績”三列下面對應的內容從“筆試通過”工作表復制過來即可，“面試成績”一列根據(jù)實際情況輸入。圖4-49 綜合成績表的基本樣式Excel-辦公中的數(shù)據(jù)分析與處理304.5.9 綜合成績計算可以采用與前面計算筆試成績一樣的方法計算筆試和面試的綜合成績。步驟如下：（1）將鼠標定位到“綜合成績表”工作表的F2單元格中。（2）在F2 單元格中輸入公式“=D2*0.7+E2*0.3”（此處假設在綜合成績計算中，筆試部分占70%，面試部分占30%）。（3）單擊編輯欄上的確定按鈕，則F2 單元格中顯示出計算結果“84.97”。（4）選取F2 單元格，拖

16、動單元格右下角的拖動柄一直到F13。（5）松開鼠標，則F3到F13 的所有單元格均計算出了對應的綜合成績。Excel-辦公中的數(shù)據(jù)分析與處理314.5.10 綜合名次以及最后結果的確定n按照與4.5.5和4.5.6中類似的方法，分別利用RANK函數(shù)和IF 函數(shù)，確定每個考生的“綜合名次”并給出“是否進入體檢階段”的判斷（假設綜合名次前50%的可以進入）。n綜合成績表的最后處理結果如圖4-50所示。n圖4-50 綜合成績表的最終效果Excel-辦公中的數(shù)據(jù)分析與處理32小小 結結n在辦公業(yè)務實踐中，經常需要對大量的業(yè)務數(shù)據(jù)進行分析和處理。這在Excel中通常通過建立數(shù)據(jù)庫表格來實現(xiàn)。利用有關功能

17、可以對Excel數(shù)據(jù)庫表格進行數(shù)據(jù)分析，包括數(shù)據(jù)排序、數(shù)據(jù)篩選、分類匯總以及數(shù)據(jù)透視分析等。利用數(shù)據(jù)清單可以制作精美的數(shù)據(jù)圖表，利用公式和函數(shù)能夠進行各種復雜、繁瑣的數(shù)據(jù)計算和數(shù)據(jù)處理。n對于數(shù)據(jù)的排序操作，需要掌握排序依據(jù)，單個字段排序和多個關鍵字段排序的操作方法。數(shù)據(jù)篩選有兩種操作方法：自動篩選與高級篩選，要注意二者的適用情況，對于高級篩選，還必須掌握如何正確設置篩選條件區(qū)域。Excel-辦公中的數(shù)據(jù)分析與處理33n數(shù)據(jù)分類匯總可以將數(shù)據(jù)庫中的數(shù)值，按照某一項內容進行分類核算，使得匯總結果清晰明了，操作時注意必須首先按照匯總字段排序。數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的動態(tài)工

18、作表，而數(shù)據(jù)透視圖是形象生動的圖表，還可以根據(jù)數(shù)據(jù)透視表制作不同格式的數(shù)據(jù)透視報告。n數(shù)據(jù)計算主要通過使用Excel的公式和函數(shù)進行，操作時需要注意以下問題：函數(shù)多層嵌套時的括號匹配；公式中的符號必須使用英文狀態(tài)下的符號；各個函數(shù)的正確使用；單元格引用方式的正確選擇；關聯(lián)表格之間數(shù)據(jù)引用的正確表達方法。n通過本章學習，要求讀者掌握Excel操作數(shù)據(jù)庫表格的快速創(chuàng)建方法，能進行特殊格式設置，熟練掌握數(shù)據(jù)排序、篩選、分析、匯總以及公式、函數(shù)計算，數(shù)據(jù)圖表制作。小小 結（續(xù)）結（續(xù)）Excel-辦公中的數(shù)據(jù)分析與處理34習習 題題一、填空一、填空1Excel對數(shù)據(jù)表中數(shù)據(jù)進行分類匯總前首先應該_。2對數(shù)據(jù)庫表格中的數(shù)據(jù)進行條件格式化時，最多可以設置_個不同條件。3使用Rank函數(shù)時，如最后一個參數(shù)為“1”，則排列名次為按照_排列。4高級篩選時，條件區(qū)域中同一個條件行的不同單元格中的條件之間互為_的邏輯關系；不同條件行的單元格條件之間互為_的邏輯關系。Excel-辦公中的數(shù)據(jù)分析與處理35二、判斷題二、判斷題1Excel中對數(shù)據(jù)庫按照某列進行排序時必須選定該列。 （ ） 2Excel中對數(shù)據(jù)庫進行按幾個關鍵字排序時必須選定整個表格區(qū)域。 （ ） 3Excel中自動篩選可以實現(xiàn)多個字段之間的或運算。 （ ）4Excel中排序時，無論是遞增還是遞減排序，空