高級辦公自動化4-辦公中的數(shù)據(jù)處理.ppt

第四章 辦公中的數(shù)據(jù)處理,主要內(nèi)容,數(shù)據(jù)庫創(chuàng)建與數(shù)據(jù)錄入 特殊格式與有效性設(shè)置 數(shù)據(jù)的計算和統(tǒng)計 數(shù)據(jù)的排序與篩選 分類匯總與透視分析 小結(jié)和作業(yè),實例1,實例2,數(shù)據(jù)庫創(chuàng)建與數(shù)據(jù)錄入,一、數(shù)據(jù)庫的創(chuàng)建 1、Excel中數(shù)據(jù)庫表格的概念和結(jié)構(gòu)： 概念：數(shù)據(jù)庫是與特定主題和目標相聯(lián)系的信息集合。是工作表中的一個區(qū)域或二維表，可看作一組數(shù)據(jù)清單。 結(jié)構(gòu)：列為字段，其下的行為記錄（數(shù)據(jù)）。 2、建庫準則： 一庫一表 一列一字段（1種信息類型） 一行一記錄 無空白行、列 無完全相同記錄 關(guān)鍵數(shù)據(jù)放中間 字段名與記錄（數(shù)據(jù)）格式相區(qū)分 庫名與字段名間應(yīng)空行 3、建庫實例： 學生成績處理、產(chǎn)品銷售匯總分析,二、數(shù)據(jù)的錄入,1、數(shù)據(jù)表直接輸入： 按字段輸入。應(yīng)先設(shè)置有效性和條件格式 2、記錄單輸入： 按記錄輸入。應(yīng)用 “記錄單”功能 。 “數(shù)據(jù)”-“記錄單” 3、注意： 記錄單輸入時，有效性設(shè)置可能不起作用。防錯法：先用數(shù)據(jù)表方法輸入一條記錄的所有字段（含公式單元格中的公式），再用記錄單法輸入基本數(shù)據(jù)即可，其它會自動算出填上。,特殊格式與有效性設(shè)置,一、特殊格式設(shè)置 一般格式（字體、字號、字形、字色、對齊等）設(shè)置略。 1、條件格式設(shè)置 選取-“格式” - “條件格式” （輸入條件）- “格式” （對滿足條件的單元格進行格式設(shè)置。注意：不能設(shè)置字體字號）-“添加” （最多可設(shè)3個條件）,2、底紋顏色間隔設(shè)置 （1）復制格式法（手動、機械） 先將前兩行設(shè)置成間隔底紋效果，再用格式刷或“選擇性粘貼”的方法將其余記錄設(shè)置成間隔底紋效果。新增記錄需要重新設(shè)置。 （2）條件格式法（自動、智能） 選定需要設(shè)置間隔底紋的整個數(shù)據(jù)區(qū)域-“格式”-“條件格式”-“公式”-“=MOD(ROW(),2)=0”(行號除以2的余數(shù)為0)-“格式”-“圖案”-選定所需顏色-“確定”。新增記錄也有同樣的效果。,二、有效性設(shè)置 為了保證數(shù)據(jù)庫中輸入的數(shù)據(jù)都在有效范圍內(nèi)，選擇單元格時給出提示信息，出錯是時給出錯誤提示，需要對其進行有效性設(shè)置。 選中-“數(shù)據(jù)”-“有效性”-“設(shè)置”（有效性條件）-“輸入信息”（鉤上“選定單元格時顯示輸入信息”）-“出錯警告”（鉤上“輸入無效數(shù)據(jù)時顯示出錯警告” ，一般為“中止”）-“選擇輸入法模式”（打開-中文，關(guān)閉-相反，隨意-不限）,數(shù)據(jù)的計算和統(tǒng)計,數(shù)據(jù)的計算和統(tǒng)計需要利用公式和函數(shù)進行處理 一、數(shù)據(jù)計算 常用的函數(shù)：SUM、 AVERAGE、RANK（排名）、ROUND（4舍5入）、IF、SUMIF、 MOD（求余）等 二、數(shù)據(jù)統(tǒng)計 常用的函數(shù)：MAX、MIN、COUNT（計數(shù)，參數(shù)類型不限）、COUNTA（計數(shù)，參數(shù)類型有限）、COUNTIF（條件計數(shù)）,IF(P,T,F) 其中，P為條件表達式 T為滿足條件需執(zhí)行的表達式 F為不滿足條件的表達式 最多可嵌套7層,ROUND(A1,A2) A1為需要取整的數(shù)或表達式 A2為保留的小數(shù)位數(shù),注意： 1、選用正確的函數(shù)；參見教材P90-91 2、正確使用函數(shù)的參數(shù)和格式 函數(shù)及公式利用中所有符號均為英文狀態(tài)的 公式中若用到其他工作簿或工作表的數(shù)據(jù)，應(yīng)加上相應(yīng)的工作簿或工作表的名稱：工作簿名工作表名！單元格地址。 引用方式（相對、絕對、混合）。,數(shù)據(jù)的排序與篩選,一、數(shù)據(jù)排序 依據(jù)：以單元格數(shù)值及所在列為依據(jù)。 種類：升序、降序 方式： 一級字段（只以一列排序）； 多級字段（最多3列，分主次。 數(shù)據(jù)-排序主、次、第三關(guān)鍵詞 ，多級字段適用于前面的關(guān)鍵字排序結(jié)果相同的情況。 ）； 其它（數(shù)據(jù)-排序-選項-自定義排序 字母或筆劃）。 注意：RANK函數(shù)與數(shù)據(jù)排序用其一,二、數(shù)據(jù)篩選 含義：從眾多數(shù)據(jù)中查找特定記錄需要進行數(shù)據(jù)篩選，顯示符合條件的，隱藏不符合的。實為檢索、查詢。 方法：自動篩選、高級篩選 步驟： 自動篩選：選中數(shù)據(jù)自動篩選篩選器自定義篩選條件；只能在原處顯示篩選結(jié)果，不能作多字段“或”運算。 高級篩選：選中數(shù)據(jù)高級篩選 源區(qū)域 條件區(qū)域 存放位置；可將結(jié)果復制到他處，能作多字段“或”運算。,注意： 1、需要專門設(shè)置條件區(qū)域，同行內(nèi)的條件之間為“與”關(guān)系，不同行為“或”關(guān)系。 2、若它與數(shù)據(jù)庫在同一工作表上，之間應(yīng)有空白行。 3、該區(qū)域與數(shù)據(jù)庫的字段名完全相同。 4、篩選結(jié)果與庫在同張表上，光標置于庫內(nèi)可在“數(shù)據(jù)區(qū)域”自動填上其地址，不放同一表中不能用此法，因篩選結(jié)果只能放活動表中。 5、只填目標位置的第一單元格。,分類匯總與數(shù)據(jù)透視,一、分類匯總 1、作用：按類別統(tǒng)計結(jié)果，使數(shù)據(jù)庫清晰易懂。應(yīng)先排序，集中相同關(guān)鍵詞的記錄，然后再分類匯總。 2、步驟：排序后，任意單元格，“數(shù)據(jù)”-“分類匯總”-選“分類字段”、“匯總方式”、“匯總項”-“確定”。 “數(shù)據(jù)”-“分類匯總”-“全部刪除”,二、數(shù)據(jù)透視,1、作用：數(shù)據(jù)透視表（圖）是能將數(shù)據(jù)排序、數(shù)據(jù)篩選、數(shù)據(jù)分類匯總這三種數(shù)據(jù)整理方法結(jié)合在一起的工具?？煞奖?、全面地重新組織、整理數(shù)據(jù)，以詳細反映數(shù)據(jù)間的關(guān)系。 2、相關(guān)概念： 透視表：對大量數(shù)據(jù)快速匯總建立交叉列表的動態(tài)工作表 透視圖：據(jù)表的圖表 透視報告：據(jù)透視表而作的報告（數(shù)據(jù)透視表工具欄） 三者結(jié)合可全面查看分析數(shù)據(jù)。,3、透視表： 創(chuàng)建步驟：任一單元格數(shù)據(jù)數(shù)據(jù)透視表和圖表報告選擇類型下一步輸入選定數(shù)據(jù)源區(qū)域下一步選擇顯示存放位置完成（再將透視表工具欄中的）字段拖放（到相應(yīng)位置）注意：拖放可逆向操作。 組成： 頁字段；頁字段項；數(shù)據(jù)字段；數(shù)據(jù)項；行字段；列字段；數(shù)據(jù)區(qū)域 注意：透視表中字段名的修改：選定-右鍵-字段設(shè)置,用Excel數(shù)據(jù)庫進行學生成績處理,數(shù)據(jù)庫框架的建立,數(shù)據(jù)庫建庫準則： 一庫一表 一列一字段（1種信息類型） 一行一記錄 無空白行、列 無完全相同記錄 關(guān)鍵數(shù)據(jù)放中間 字段名與記錄（數(shù)據(jù)）格式相區(qū)分 庫名與字段名間應(yīng)空行,數(shù)據(jù)庫表格中數(shù)據(jù)的輸入,1、選擇“數(shù)據(jù)”“記錄單” 2、在彈出的“記錄單”界面中，以次逐條輸入記錄,條件格式化設(shè)置,1、條件格式化最多可以設(shè)置3個條件； 2、選擇要進行格式化設(shè)置單元格區(qū)域（F4:L57）（假設(shè)有54個學生）； 3、選擇“格式”“條件格式”，打開“條件格式”對話框； 4、單擊“格式”按鈕，在彈出的界面中設(shè)置滿足條件的單元格的格式。,優(yōu)秀,不及格,條件格式化方法設(shè)置底紋顏色,1、選定整個數(shù)據(jù)表區(qū)域； 2、選擇“格式”“條件格式”命令； 3、在彈出的對話框左側(cè)第一個下拉列表中選擇“公式”文本框中設(shè)置公式為“MOD(ROW( ),2)=0”； 4、單擊上述界面中的“格式”按鈕，在彈出的界面中選擇需要的底紋顏色即可。,行號除以2，余數(shù)為“0”時就執(zhí)行設(shè)置的條件格式,數(shù)據(jù)有效性設(shè)置,1、選定要設(shè)置有效性的數(shù)據(jù)區(qū)域（F4:L57） 2、選擇“數(shù)據(jù)”“有效性”； 3、在彈出的“數(shù)據(jù)有效性”對話框中，選擇“設(shè)置”選擇、錄入相應(yīng)的有效范圍； 4、在“數(shù)據(jù)有效性”對話框中，選擇“輸入信息”輸入提示信息,設(shè)置“輸入提示信息”后，當用戶選擇對應(yīng)單元格，系統(tǒng)會自動出現(xiàn)提示信息。,數(shù)據(jù)庫中的排序操作,一級字段排序： 1、將光標置于待排列的某一單元格； 2、單擊工具欄上的“升序”或“降序”按鈕。,多級字段排序： 適用于前面的關(guān)鍵字內(nèi)容相同時。 1、將光標定位在任一單元格上； 2、選擇“數(shù)據(jù)”“排序”； 3、依次選擇“主關(guān)鍵字”、“次關(guān)鍵字”(最多可以設(shè)置3個關(guān)鍵字)及其排序方式。,自動篩選,以篩選“英語成績在8090分（包括80分）的記錄”為例： 選擇“數(shù)據(jù)”“篩選”“自動篩選”,選擇“自定義”,高級篩選,1、新建一工作表（名為：優(yōu)秀學生篩選），在其中輸入“優(yōu)秀學生”篩選條件: (1)“與”同行；(2)“或”不同行；(3)”與”、”或”不同行。 2、單擊“優(yōu)秀學生篩選”表中任一空白單元格； 3、選擇“數(shù)據(jù)”“篩選”“高級篩選”，彈出對話框,從“評定結(jié)果”表中選擇原始數(shù)據(jù),“高級篩選”條件所在的區(qū)域,指定：篩選結(jié)果存放的區(qū)域,產(chǎn)品銷售匯總分析,按“品名”匯總,“先物再人”的數(shù)據(jù)透視表,銷售數(shù)據(jù)的分類匯總,分類匯總前首先必須對所依據(jù)的字段排序,1、選定數(shù)據(jù)庫中的任一單元格； 2、選擇“數(shù)據(jù)”“分類匯總”，彈出對話框； 3、單擊對話框中“分類”字段下的“品名”作為分類匯總依據(jù)字段； 4、在“匯總方式”下拉列表中選擇需要的統(tǒng)計函數(shù)（本例選“求和”）。,銷售數(shù)據(jù)的透視分析,數(shù)據(jù)透視表的創(chuàng)建,1、將光標定位在銷售數(shù)據(jù)庫表格中的任一單元； 2、選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和圖表報告”,建立數(shù)據(jù)透視表所依據(jù)的數(shù)據(jù)源區(qū)域,頁字段：被指定為頁方向數(shù)據(jù)源的數(shù)據(jù)庫中的字段。本例選“銷售日期”,被指定為行方向數(shù)據(jù)源的字段。（先“業(yè)務(wù)員”，后“品名”）,被指定為列方向數(shù)據(jù)源的字段。（“客戶名稱”,含有數(shù)據(jù)的源數(shù)據(jù)庫中的字段。（“合計金額”）,使用數(shù)據(jù)透視表篩選數(shù)據(jù),只顯示1月9號的記錄,修改數(shù)據(jù)透視表的布局,行、列字段互換 在字段內(nèi)移動數(shù)據(jù)項 隱藏和顯示數(shù)據(jù) 添加字段 刪除字段 設(shè)置數(shù)據(jù)透視表選項 數(shù)據(jù)透視表數(shù)據(jù)的更新,將行字段拖到列字段位置處，列字段拖動到行字段位置處。,1、選中要移動的數(shù)據(jù)項； 2、鼠標移至選中的數(shù)據(jù)項上，當鼠標指針變?yōu)榘咨^時按下左鍵，拖動數(shù)據(jù)項到需要的位置。,隱藏： 1、光標定位在要隱藏的數(shù)據(jù)所在單元格； 2、選擇右鍵菜單中的“隱藏”即可。 顯示： 單擊字段名單元格右邊向下箭頭，將需要顯示的字段選中即可。,單擊“顯示域”按鈕（將顯示出所有字段名），將需要添加的字段拖動到適當?shù)奈恢谩?直接將要刪除的字段拖到透視表數(shù)據(jù)區(qū)以外即可。,選擇“數(shù)據(jù)透視表”工具欄“數(shù)據(jù)透視表”“表選項”，在彈出界面中設(shè)置。,必須執(zhí)行“數(shù)據(jù)更新”操作,制作數(shù)據(jù)透視報告,1、首先要制作好數(shù)據(jù)透視表； 2、單擊“數(shù)據(jù)透視表”工具欄“設(shè)置報表格式”,創(chuàng)建數(shù)據(jù)透視圖,1、數(shù)據(jù)透視表上單擊鼠標右鍵； 2、選擇右鍵菜單上的“數(shù)據(jù)透視圖”,本章小結(jié),掌握：數(shù)據(jù)庫表格的創(chuàng)建（記錄單錄入法）、數(shù)據(jù)庫表格的條件格式設(shè)置、數(shù)據(jù)有效性設(shè)置、數(shù)據(jù)排序、數(shù)據(jù)篩選、數(shù)據(jù)透視表、合并計算、分類匯總 重點和難點：使用條件格式、數(shù)據(jù)篩選、分類匯總,課堂作業(yè),1、數(shù)據(jù)庫的含義和建庫準則 2、記錄單錄入法的技巧 3、條件格式設(shè)置最多允許幾個條件同時設(shè)置？ 4、用復制格式法和條件格式法設(shè)置的間隔底紋效果有何不同？ 5、有效性設(shè)置的作用是什么？ 6、多級關(guān)鍵字排序在什么條件下才用？ 7、自定義自動篩選方式可以同時對幾個字段進行篩選？ 8、高級篩選中，制作條件區(qū)域應(yīng)注意什么？ 9、分類匯總前必須先進行什么操作？ 10、透視表有哪些功能？,