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

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

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

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

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

6、人數(shù)；可以對(duì)“報(bào)考職位”進(jìn)行匯總，以便了解各個(gè)不同職位的報(bào)名情況。n在執(zhí)行分類(lèi)匯總命令之前，首先應(yīng)該對(duì)數(shù)據(jù)庫(kù)按照待匯總字段進(jìn)行排序。Excel-辦公中的數(shù)據(jù)分析與處理144.3.4 對(duì)“報(bào)名信息匯總表”進(jìn)行數(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ù)之間關(guān)系nExcel數(shù)據(jù)圖表能將工作表中的數(shù)據(jù)變?yōu)榉浅Ｖ庇^的圖形格式，并且從圖表上很容易看出結(jié)果，以及數(shù)據(jù)之間的關(guān)系和數(shù)據(jù)的發(fā)展趨勢(shì)。Excel-辦公中的數(shù)據(jù)分析與處理164.4.1 利用圖表向?qū)?chuàng)建條形圖n利用圖

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

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

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

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

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

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

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

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

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

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

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

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