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

1、辦公中的數(shù)據(jù)分析與處理,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,【本章內(nèi)容介紹】,在辦公實踐中，經(jīng)常需要對大量的數(shù)據(jù)進行分析和處理。如職工工資表中工資的計算、匯總、分析，公司銷售表中銷售額的統(tǒng)計、匯總、合并計算以及人事考勤表中職工請假、曠工、出勤等情況的分析統(tǒng)計等。 本章采用實例形式，以Excel軟件操作為例介紹辦公實踐中常見的數(shù)據(jù)分析與處理方法，包括數(shù)據(jù)的排序、篩選、分類匯總、公式與函數(shù)運用以及數(shù)據(jù)關(guān)聯(lián)表格的操作等。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,【本章主要知識點】,Excel數(shù)據(jù)庫表格的基本要求 Excel表格中內(nèi)容填充的常

2、用技巧 Excel數(shù)據(jù)庫表格的特殊格式設(shè)置 Excel表格中數(shù)據(jù)有效性的設(shè)置 Excel數(shù)據(jù)庫表格中的排序 Excel數(shù)據(jù)庫表格中的篩選 Excel數(shù)據(jù)庫表格的分類匯總 Excel表格中公式和函數(shù)的使用 Excel中的關(guān)聯(lián)表格操作 Excel公式使用中的常見錯誤信息,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,1Excel中的數(shù)據(jù)庫表格簡介 所謂數(shù)據(jù)庫就是與特定主題和目標相聯(lián)系的信息集合。 如圖4-1示，Excel中數(shù)據(jù)庫實際上就是工作表中的一個區(qū)域，是一個二維表。,Excel數(shù)據(jù)分析與處理概述,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,Exc

3、el 2003 的窗口組成,Excel 的啟動、退出、新建文件、保存文件、打開文件等基本操作與Word相似。啟動Excel 2003 后，系統(tǒng)自動打開一個空工作簿（book1.xls）,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,幾個重要的術(shù)語,1.工作簿（Book）： 每個Excel文檔都是一個工作簿，每個工作簿由若干張工作表（Sheet）所構(gòu)成，最多可包含255個表。默認情況下，新工作簿名稱為Book1.xls。 2.工作表（sheet） ： 是工作簿里的一頁，由單元格組成。每個工作表的網(wǎng)格由65536行（165536） 、 256列（AIV）構(gòu)成。 3.單元格：

4、工作表中行列交叉形成的格，是Excel中操作的基本單位。 4.單元格地址： 單元格地址由列坐標行坐標（例：A3）構(gòu)成。 5.活動單元格： 正在使用的單元格（黑色粗邊框）稱為活動單元格。要輸入、處理數(shù)據(jù)的單元格必須是活動單元格。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,幾個重要的術(shù)語,文本：中文、西文的單詞、句子和符號。 數(shù)值：數(shù)字、小數(shù)點、數(shù)學符號和貨幣符號等。 日期的時間：表示日期和時間的數(shù)字、符號和分隔符等。 公式：以等號（=）開頭由單元格名、運算符和數(shù)字組成的字符串。如果單元格中的數(shù)據(jù)是公式時，則選中該單元格時，其公式在編輯欄中出現(xiàn)，而在單元格中仍是數(shù)值，是公

5、式的計算結(jié)果 鼠標指針：在Excel工作表中，指針通常是“ 空心大十字 ”；雙擊某單元格而進入編輯狀態(tài)時，指針呈“ I ”形；當指針移到選中單元格的右下角時，變?yōu)椤?小十字 ”形。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,工作表的操作,工作表的含義 插入、刪除工作表 右鍵單擊某一工作表標簽，選定該工作表，并彈出下面菜單； 點“插入”|“工作表”|“確定”，則在該工作表前插下一新的工作表，名為Sheet4. 若刪除工作表，則點擊 菜單中的“刪除”,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,工作表的操作,重命名工作表 右鍵單擊某一工作表標簽，選

6、定該工作表并彈出一菜單； 點“重命名”，則該工作表名稱處于選擇狀態(tài)，改名即可. 標簽顏色 選中工作表、右擊、選“工作表標簽顏色”、選擇“顏色” 點擊“確定” 隱藏工作表 選中工作表 “格式”|“工作表”|“隱藏” 取消隱藏：“格式”|“工作表”|“取消隱藏”，選擇一工作表，點擊“確定”,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,工作表的操作,復制和移動工作表 打開接收工作表的工作簿 選擇要復制或移動的工作表，右擊、選擇“移動或復制工作表”，出現(xiàn)右圖； 在“工作簿”列表框中選擇目標工作簿， 在“下列選定工作表之前確定位置 如果是移動而非復制，選中“建立副本” 單擊“確定

7、”按鈕，完成。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,單元格的操作,單元格及單元格區(qū)域的表示方法 單元格地址：列號行號； B2如右圖 單元格區(qū)域：“左上角單元格：右下角單元格” B2：D3如右圖,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,選定操作,選定連續(xù)單元格區(qū)域。 首先將鼠標箭頭指向要選定區(qū)域的第一個單元格，按住鼠標左鍵，拖動至要選定區(qū)域的最后一個單元格， 選定的區(qū)域會變成藍色，表明這一塊區(qū)域被選中的狀態(tài)。釋放鼠標左鍵，一個連續(xù)的區(qū)域就選定了。 選定不連續(xù)單元格區(qū)域。 按住【Ctrl】鍵的同時，逐個單擊所要選取的單元格。,現(xiàn)代教育技術(shù)

8、中心 岳經(jīng)偉 TEL：89714158 E-mail：,選定操作,選定整行（列） 選定單行（列）：在工作表上單擊該行（列）的行（列）號。 選定連續(xù)行（列）區(qū)域： 在工作表上單擊該區(qū)域的第一行（列）行號（列號），然后按住【Shift】鍵，再單擊最末一行（列）的行號（列號）； 或者拖動鼠標。 選定整個工作表。 單擊工作表左上角行號與列號相交處的全選按鈕，即可選定整個工作表。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,復制或移動單元格,復制和移動操作與Word類似，可以使用菜單命令、工具欄按鈕、快捷鍵、快捷菜單中的命令、鼠標拖動。 選定要移動或復制的單元格 將鼠標指向選定區(qū)

9、域邊框，鼠標指針成“十字箭頭” 移動則拖動鼠標到目的區(qū)域 復制則按Ctrl同時拖動鼠標到目的區(qū)域 相鄰單元格的復制，可以直接拖動“填充柄”,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,插入或刪除單元格,清除單元格內(nèi)容 選擇單元格、行、列，按Delete鍵 清除單元格格式 選擇 點擊菜單“編輯”|“清除”|“全部”、“格式”、“內(nèi)容” 刪除單元格 選擇 點擊菜單“編輯”|“刪除” 確定刪除選項，點“確定”,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,設(shè)置單元格格式,“格式”|“單元格”,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mai

10、l：,文本數(shù)據(jù)的輸入,除了數(shù)值和日期型數(shù)據(jù)外，Excel將其余輸入的數(shù)據(jù)均作為文本數(shù)據(jù)，文本指當作字符串處理的數(shù)據(jù)，由字母、數(shù)字或其他字符組成。 如果要輸入的文本數(shù)據(jù)全部由數(shù)字組成（如序號、郵政編碼、身份證號碼、電話號碼等），輸入時應在數(shù)字前加一個西文單引號，如序號“00123”輸入時鍵入“00123”，以區(qū)別數(shù)值格式的數(shù)據(jù)。 如果希望在輸入這類數(shù)據(jù)時不鍵入，又不會將該數(shù)據(jù)作為數(shù)值格式的數(shù)據(jù)對待，可以在輸入數(shù)據(jù)之前，定義單元格數(shù)據(jù)的格式為文本格式。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,數(shù)字、日期、時間的輸入,負數(shù)-10：-10或（10） 分數(shù)3/5：0 空格 3

11、/5 日期2006年11月12日：2006-11-12，2006/11/12 插入當前日期：Ctrl+ ; 時間5時32分：5：32 插入當前日期：Ctrl+ ：,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,“報名信息匯總表”建立,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,“報名匯總表”數(shù)據(jù)庫框架,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,“編號”列的自動序列填充,對于“編號”列，因為輸入的數(shù)據(jù)是有一定規(guī)律的，輸入時可以考慮使用自動序列的填充方法。操作時，只要在圖4-3所示表格的A3、A4分別輸入1和2，然后選取這兩個

12、單元格，再拖動右下角的拖動柄到需要的位置即可。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,定義單元格數(shù)據(jù)的有效性,在Excel中有些單元格中輸入的數(shù)據(jù)具有有效范圍。例如：圖4-3的報名匯總表中，“性別”列只能輸入“男”、“女”之一；“年齡”列按照報名規(guī)定只能輸入1635之間的數(shù)字；而“唱法類型”列只能輸入“通俗”、“民族”、“美聲”中的一種。 可以使用Excel提供的“有效性”來為單元格設(shè)置條件。 下面僅以設(shè)置“唱法類型”一列的數(shù)據(jù)有效性為例，說明設(shè)置數(shù)據(jù)有效性的操作方法。操作步驟如下：,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,（1）選定需

13、要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域E3：E62（假設(shè)報名人數(shù)只有60人）。 （2）選擇“數(shù)據(jù)”|“有效性”命令，打開圖4-4所示的“數(shù)據(jù)有效性”對話框。,定義單元格數(shù)據(jù)的有效性,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,（3）選擇“設(shè)置”選項卡，在“允許”下拉列表中，選擇“序列”項。 （4）在隨之出現(xiàn)“來源”文本框中輸入“通俗，民族，美聲”自定義序列。注意：序列中各項中間的逗號必須為英文狀態(tài)符號。 （5）數(shù)據(jù)有效性設(shè)置之后，單擊“唱法類型”列中單元格，則會出現(xiàn)如圖4-5所示的列表框，從而可以實現(xiàn)快速的選取錄入。,定義單元格數(shù)據(jù)的有效性,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89

14、714158 E-mail：,在報名匯總表中，各個參賽選手的資料一般是按照其報名的先后順序輸入的。為了提高查找效率，有時需要對輸入的數(shù)據(jù)進行排序。 1排序的依據(jù) 在按升序排序時， Excel 使用如下順序： （1）數(shù)字從最小的負數(shù)到最大的正數(shù)排序； （2）文本以及包含數(shù)字的文本，按下列順序排序：,對“報名信息匯總表”進行數(shù)據(jù)分析,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,（3）空格總是排在最后； （4）漢字的排序可以按筆畫，也可按字典順序排序，這可以通過有關(guān)操作設(shè)置。,對“匯總表”進行數(shù)據(jù)排序,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,2按單

15、個字段排序 按單個字段排序就是只根據(jù)某一個字段的內(nèi)容進行排序。 操作方法為：將光標置于待排序列中；單擊工具欄上“升序”或“降序”按鈕即可。 說明：按數(shù)據(jù)庫某一列數(shù)據(jù)排序時，只需單擊列中任一單元格而不用全選該列。,對“匯總表”進行數(shù)據(jù)排序,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,3按多級字段排序,按多級字段排序就是根據(jù)兩列或三列的內(nèi)容對數(shù)據(jù)庫排序，它主要適用于第一或第二排序字段出現(xiàn)相同內(nèi)容時，為了排序合理再加上第二或第三排序字段。 （1）將光標定位在待排序數(shù)據(jù)庫的任一單元格中，之后選擇“數(shù)據(jù)”|“排序”命令，打開如圖4-7所示對話框。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL

16、：89714158 E-mail：,3按多級字段排序,（2）在“主要關(guān)鍵字”、“次要關(guān)鍵字”和“第三關(guān)鍵字”下拉列表框中，分別選擇需要排序的字段，并且設(shè)置“遞增”或者“遞減”即可。 （3）選定所需的其他排序選項，然后單擊“確定”按鈕。 說明：按數(shù)據(jù)庫多列數(shù)據(jù)排序時，只需單擊數(shù)據(jù)庫中任一單元格而不用全選整個數(shù)據(jù)庫表格，否則會引起數(shù)據(jù)的混亂。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,除上述基本排序功能外，Excel還提供了一些特殊的排序功能。它們的操作方法為： （1）將光標放在所要排序的數(shù)據(jù)區(qū)域中的任一位置； （2）選擇“數(shù)據(jù)”菜單中的“排序”項；在打開的對話框中選擇“

17、選項”按鈕；,4特殊排序方式,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,4特殊排序方式,（3）在打開如圖4-8所示的“選項”對話框中，分別設(shè)置：自定義排序次序、排序方向（按行或按列排序）、排序方法（按字母或按筆畫排序） （4）按“確定”按鈕完成操作。 說明：在“排序選項”對話框中，若不選擇“方向”和“方法”，系統(tǒng)默認的排序方向為“按列排序”，默認的排序方式為“字母排序”。如選擇“筆畫排序”方法，就可實現(xiàn)在開會代表名單、教材編寫人員名單中經(jīng)常看到的按姓氏筆畫排序效果。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,報名信息匯總表制作好之后，有時還需

18、要根據(jù)指定條件從眾多數(shù)據(jù)中篩選特定的記錄，比如：篩選職業(yè)是“教師”的報名人員，篩選年齡在30以上的人員等等。 Excel中提供了兩種篩選方法：“自動篩選”和“高級篩選”，它們可以將那些符合條件的記錄顯示在工作表中，而將其他不滿足條件的記錄從視圖中隱藏起來；或者將篩選出來的記錄送到指定位置存放，而原數(shù)據(jù)不動。,對“報名信息匯總表”內(nèi)容進行篩選,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,1自動篩選,利用自動篩選，可以很方便地從報名匯總表中篩選出職業(yè)是“教師”的報名人員信息。下面以此為例，說明自動篩選的操作。操作步驟如下： （1）將鼠標定位到需要篩選的數(shù)據(jù)庫中任一單元格。

19、（2）選擇“數(shù)據(jù)”|“篩選”|“自動篩選”，使“自動篩選”項為選中狀態(tài)（打上對號），這時在每個字段名旁出現(xiàn)篩選器箭頭，如圖4-9所示。 （3）本例要篩選出來職業(yè)是“教師”的報名人員的情況，需要單擊“職業(yè)”字段名旁的篩選器箭頭，從彈出的菜單中選擇“教師”，然后單擊鼠標左鍵即可。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,自動篩選,說明：在該菜單中，選擇“升序排列”和“降序排列”，則篩選結(jié)果將按照指定順序排列；選擇具體學歷名稱，將只顯示對應學歷的記錄；單擊“全部”顯示所有記錄；單擊“前10個”可以顯示最高或最低的一些（默認10個，可以自行設(shè)置）記錄。,現(xiàn)代教育技術(shù)中心 岳

20、經(jīng)偉 TEL：89714158 E-mail：,（4）在圖4-9中，如果下拉列表太長，不容易找到“教師”項時，還可以選擇“自定義”項，從出現(xiàn)圖4-10所示的對話框中，按照圖中樣式設(shè)置本例的篩選條件。,自動篩選,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,（5）按“確定”按鈕，完成操作，篩選結(jié)果如圖4-11所示。,自動篩選,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,自動篩選,說明：取消數(shù)據(jù)篩選的操作方法： 如要取消對某一列的篩選，單擊該列篩選器箭頭，然后再單擊“全部”。 如要取消對所有列的篩選，可選擇“數(shù)據(jù)”|“篩選”|“全部顯示”命令。 如要撤

21、消數(shù)據(jù)清單中的篩選箭頭，可選擇“數(shù)據(jù)”|“篩選”|“自動篩選”命令。,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,從上面的講解可以看到，自動篩選可以實現(xiàn)同一字段之間的“與”運算和“或”運算，通過多次進行自動篩選也可以進行不同字段之間的“與”運算，但是它無法實現(xiàn)多個字段之間的“或”運算。這時就需要使用高級篩選。 需要說明的是：對于數(shù)據(jù)清單，如果要進行高級篩選，必須首先設(shè)置篩選條件區(qū)域。為此，必須在該數(shù)據(jù)清單上方留出若干空行，以便作為條件區(qū)域。 另外，為了使讀者可以更好地理解高級篩選，首先列出一個表格，如表4-1所示，該表用來對高級篩選條件區(qū)域的邏輯關(guān)系進行定義。,高級篩選

22、,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,高級篩選,下面以篩選出來職業(yè)為“教師”、唱法類型為“美聲”或者“民族”的報名選手情況操作為例，說明高級篩選的使用方法。操作步驟如下：,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,（1）在“報名信息匯總表”表格的上方插入幾個空行，建立高級篩選的條件區(qū)域，如圖4-12所示A1:B3區(qū)域。 （2）單擊“報名信息匯總表”數(shù)據(jù)庫表格中的任一單元格。,高級篩選,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,高級篩選,（3）選擇“數(shù)據(jù)”|“篩選”|“高級篩選”命令，出現(xiàn)“高級篩選”對話框。,現(xiàn)

23、代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,（4）如上圖所示，在 “方式”選項區(qū)中，單擊選中“將篩選結(jié)果復制到其他位置”按鈕；在“列表區(qū)域”框中，軟件自動輸入了要篩選的數(shù)據(jù)區(qū)域“$A$6:$H$66”，也就是整個數(shù)據(jù)清單區(qū)域 ； 在“條件區(qū)域”框中，輸入設(shè)置好的包含篩選條件的區(qū)域“$A$1:$B$3”（可直接在該文本框中輸入?yún)^(qū)域引用，也可用鼠標在工作表中選定條件區(qū)域）； 在“復制到”文本框中輸入時，首先將光標在其中定位，然后用鼠標在數(shù)據(jù)清單下方指定一個單元格（本例為“$A$71”），該單元格將作為放置篩選結(jié)果區(qū)域左上角的位置，篩選結(jié)果將在它的下方和右方進行排列。,高級篩選

24、,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,（5）單擊“確定”按鈕，高級篩選結(jié)果如圖4-14所示（原來數(shù)據(jù)我們選擇的“隱藏”操作，以便大家能夠看到篩選出來的效果）。,高級篩選,現(xiàn)代教育技術(shù)中心 岳經(jīng)偉 TEL：89714158 E-mail：,使用高級篩選時，需要注意以下幾個問題： （1）高級篩選必須指定一個條件區(qū)域，它可以與數(shù)據(jù)庫表格在一張工作表上， 但是必須與數(shù)據(jù)庫之間有空白行隔開；條件區(qū)域也可以與數(shù)據(jù)庫表格不在一張工作表上。 （2）條件區(qū)域中的字段名必須與數(shù)據(jù)庫中的完全一樣，最好通過復制得到。 （3）如果“條件區(qū)域”與數(shù)據(jù)庫表格在一張工作表上，在篩選之前，最好把光標放置到數(shù)據(jù)庫中某一單元格上，這樣數(shù)據(jù)區(qū)域就會自動填上數(shù)據(jù)庫所在位置，省去再次鼠標選擇或者重新輸入的麻煩。 （4）執(zhí)行“將篩選結(jié)果復制到其他位置”時，在“復制到”文本框中輸入或選取將來要放置位置的左上角單元格即可，不要指定某區(qū)域（因為事先無法確定篩選結(jié)果）。 （5）根據(jù)需要，條件區(qū)域可以定義多個條件，以便用來篩選符合多個條件的記錄。 這些條件可以輸入到條件區(qū)域的同一行上，也可以輸入到不同行上。但是必須記?。簝蓚€字段名下面的同一行中的各個條件之間為“與”的關(guān)系，也就是條件必須同時成立才算符合條件；兩個字段名下面的不同行中的各個條件之間為“或”的關(guān)系，也就是條件只要有一個成立就算