Excel_第七課_數(shù)據(jù)庫管理

1、授課教師：龍劍授課教師：龍劍第七課 數(shù)據(jù)管理和分析 Excel具有數(shù)據(jù)庫的特點，可以實現(xiàn)數(shù)據(jù)的排序、篩選、分類匯總。統(tǒng)計和查詢等操作，具有數(shù)據(jù)庫的組織、管理和處理數(shù)據(jù)的功能。因此Excel數(shù)據(jù)清單也稱為Excel數(shù)據(jù)庫 數(shù)據(jù)清單數(shù)據(jù)清單：一個典型的二維表，它是由工作表中單元格構一個典型的二維表，它是由工作表中單元格構成的一個矩形區(qū)域成的一個矩形區(qū)域矩形區(qū)域的每一列叫一個矩形區(qū)域的每一列叫一個“字段字段”，每個字段包含相同類，每個字段包含相同類型的數(shù)據(jù)。型的數(shù)據(jù)。第一行為各字段標題，叫字段名。字段名不能相同。以后第一行為各字段標題，叫字段名。字段名不能相同。以后每一行的數(shù)據(jù)表示一條記錄。每一行的

2、數(shù)據(jù)表示一條記錄。所以數(shù)據(jù)清單是由多條記錄組成的。所以數(shù)據(jù)清單是由多條記錄組成的。 數(shù)據(jù)清單數(shù)據(jù)清單 正確創(chuàng)建正確創(chuàng)建規(guī)則規(guī)則列名唯一列名唯一純數(shù)據(jù)（杜絕空行、非法數(shù)據(jù)）純數(shù)據(jù)（杜絕空行、非法數(shù)據(jù)）同列數(shù)據(jù)的數(shù)據(jù)類型和格式應完全相同同列數(shù)據(jù)的數(shù)據(jù)類型和格式應完全相同避免在一張工作表上放置多份數(shù)據(jù)清單避免在一張工作表上放置多份數(shù)據(jù)清單數(shù)據(jù)清單可以是整個工作表，也可以是工作表的一部數(shù)據(jù)清單可以是整個工作表，也可以是工作表的一部分。數(shù)據(jù)清單的處理功能只能在一個數(shù)據(jù)清單中使用分。數(shù)據(jù)清單的處理功能只能在一個數(shù)據(jù)清單中使用數(shù)據(jù)清單的作用：數(shù)據(jù)清單的作用：用戶可以通過用戶可以通過“數(shù)據(jù)清單數(shù)據(jù)清單”對表中

3、的數(shù)據(jù)進行排序、篩對表中的數(shù)據(jù)進行排序、篩選、分類和匯總等操作，以實現(xiàn)日常所需要的統(tǒng)計工作，選、分類和匯總等操作，以實現(xiàn)日常所需要的統(tǒng)計工作，是是數(shù)據(jù)管理的基礎數(shù)據(jù)管理的基礎。建立數(shù)據(jù)清單 直接在單元格中輸入 記錄單（“數(shù)據(jù)-記錄單”）創(chuàng)建數(shù)據(jù)清單注意事項 不能在數(shù)據(jù)清單中留空行或列：當數(shù)據(jù)清單中的數(shù)據(jù)被一個空行或列隔開時，excel會將它當作兩個部分看待。 在數(shù)據(jù)清單的第一行建立字段名稱：excel將使用字段名建立報告以及進行排序等操作。 字段名最好用文字：字段名只能使用255個字符以內的文字或文字公式，且要準確地表示出字段的含義。 字段名只能有一個：如果在數(shù)據(jù)清單中打開了多個相應的字段名，

4、則影響篩選等操作。 工作表中只建立一個數(shù)據(jù)清單：雖然在excel中允許同時存在多個工作表，但是會顯得比較混亂，而且容易影響篩選等操作，因此建議最好在一個工作表中建立一個數(shù)據(jù)清單。 使用使用“數(shù)據(jù)記錄單數(shù)據(jù)記錄單”菜單命令，打開某數(shù)據(jù)清單菜單命令，打開某數(shù)據(jù)清單的記錄單。的記錄單。利用記錄單可以進行下列操作：利用記錄單可以進行下列操作：8 添加記錄添加記錄8 修改記錄修改記錄8 刪除記錄刪除記錄8 查找記錄查找記錄使用數(shù)據(jù)清單使用數(shù)據(jù)清單 單擊單擊“條件條件”按鈕，輸入相關的查找條件。例如：列出按鈕，輸入相關的查找條件。例如：列出“平時成績平時成績” “80” 的學生。最后單擊的學生。最后單擊“

5、上一條上一條”或或 “下一條下一條” 按鈕，可查看到符合給定條件的所有記錄按鈕，可查看到符合給定條件的所有記錄數(shù)據(jù)排序數(shù)據(jù)排序排序： 依據(jù)某列或幾列的值，按照一定的順序將表中的記錄重新排列稱為排序。排序使表格數(shù)據(jù)更有條理，使用戶觀察數(shù)據(jù)更方便。 排序方式：升序和降序 排序規(guī)則 數(shù)字按照大小 文本按首字母英文順序/筆畫 日期按先后（早晚） 文本和數(shù)字一起，數(shù)字在前文本在后 空格始終排在最后 排序方法： 工具欄中的“ ”按鈕 “數(shù)據(jù)” 排序注意：第一種方法只能采用單獨排序方式，第二種可以采用兩種排序方式同時進行1 1、按單字段字排序、按單字段字排序 工具欄按鈕 數(shù)據(jù)排序2 2、按多關鍵字排序、按多

6、關鍵字排序主關鍵字、次關鍵字第三關鍵字選擇表格列區(qū)域，打開排序顯示排序警告勾選以當前選定區(qū)域排序，系統(tǒng)將默認以選擇的列單獨排序升序：從小到大降序：從大到小數(shù)據(jù)排序步驟光標位于要排序的列光標位于數(shù)據(jù)清單中，單擊單擊“數(shù)數(shù)據(jù)排序據(jù)排序”命令命令，最多最多3個字段排個字段排序序 選定范圍選定范圍 “ “數(shù)據(jù)排序數(shù)據(jù)排序” ” 確定排序關鍵字和順序確定排序關鍵字和順序 設置兩個以設置兩個以上的排序約上的排序約束條件。束條件。 數(shù)據(jù)排序操作例題數(shù)據(jù)排序操作例題數(shù)據(jù)篩選數(shù)據(jù)篩選篩選：篩選： 實現(xiàn)在數(shù)據(jù)清單中提出滿足篩選條件的數(shù)據(jù)，不滿足條件的數(shù)據(jù)暫時被隱藏起來，從數(shù)據(jù)庫中選一部分數(shù)據(jù)使用。篩選方法篩選方法

7、： 自動篩選 高級篩選篩選基本操作步驟：篩選基本操作步驟：選中數(shù)據(jù)區(qū)中的一單元格數(shù)據(jù)篩選自動篩選、高級篩選大表大表明晰的小表明晰的小表 自動篩選：自動篩選：“數(shù)據(jù)數(shù)據(jù) / 篩選篩選 / 自動篩選自動篩選” 設定條件設定條件條條 件件 數(shù)據(jù)篩選數(shù)據(jù)篩選 常用于簡單條件的篩選常用于簡單條件的篩選 自動篩選自動篩選 自定義篩選自定義篩選 “自動篩選”的“自定義”選項提供了對同一字段進行篩選的兩個條件，并且這兩個條件可以是“與與”或“或或”的關系，同時，該對話框還提供“等于”、“大于”、“起始于”等許多條件選項。 注意：篩選只是暫時將不要的數(shù)據(jù)進行隱藏，沒有刪除;排序和篩選必須為數(shù)據(jù)清單格式的表;“自

8、定義”可以設置兩個條件。 與：兩個條件必須同時滿足 或：滿足一個條件即可對某個列應用兩個以上的篩選條件，或者對多個列應用篩選條件，最好使用高級篩選。 篩選數(shù)據(jù)-高級篩選條件區(qū)域條件區(qū)域 高級篩選的功能 實現(xiàn)對某個列或者多個列應用多個篩選條件。 把滿足條件的記錄復制到當前表的其他位置，原數(shù)據(jù)清單保持不變。要使用高級篩選功能，必須在數(shù)據(jù)清單的上方或下方放置一個條件區(qū)域。該區(qū)域用來指定篩選出的數(shù)據(jù)必須滿足的條件。條件區(qū)域應當遵守的規(guī)則是： 條件區(qū)域與數(shù)據(jù)清單之間至少要留一個空行。 條件區(qū)域中條件的邏輯關系是：寫在同一行的條件互為寫在同一行的條件互為“與與”關系；寫在不同行的條件互為關系；寫在不同行的

9、條件互為“或或”關系；關系；條件區(qū)域中不能有條件區(qū)域中不能有空行空行。下圖的條件可以理解為：系別等于“自動控制” 并且考試成績70，或者系別等于“計算機”并且上機成績17的學生名單。需要注意的是，由于系別是文字型數(shù)據(jù)，在寫條件是，應寫成：=“自動控制自動控制”、=“計算機計算機”，即自動控制和計算機必須用引號括起來。 “高級篩選高級篩選”功能完成功能完成篩選那些需要匹配計算條件篩選那些需要匹配計算條件或篩選條件中含復雜的或篩選條件中含復雜的和和關系的數(shù)據(jù)關系的數(shù)據(jù)（1）確定篩選條件，并創(chuàng)建條件區(qū)（注意：注意：要與數(shù)據(jù)清單用空行隔開）（2） “數(shù)據(jù)/篩選” “高級篩選”命令 高級篩選高級篩選(

10、復雜條件 ）高級篩選高級篩選 數(shù)據(jù)篩選高級篩選數(shù)據(jù)區(qū)域步驟： 1.先在空白區(qū)域寫上篩選條件，條件必須為兩行; 2.數(shù)據(jù)-篩選-高級篩選; 3.將數(shù)據(jù)區(qū)域和條件區(qū)域選相應的框里; 4. 列表區(qū)域列表區(qū)域：選擇表格區(qū)域; 5. 條件區(qū)域條件區(qū)域：將工作表中定義的條件區(qū)域選中; 6. 勾選勾選在原有區(qū)域中篩選在原有區(qū)域中篩選- 系統(tǒng)將自動在當前表格區(qū)域 中篩選出結果; 7.勾選勾選將結果復制到其它位置將結果復制到其它位置- 系統(tǒng)將結果自動復制到所 定位的單元格上; 使用使用“高級篩選高級篩選”，必須先建立篩選，必須先建立篩選條件區(qū)域條件區(qū)域。篩選條件區(qū)域類似于一個只包含條件的數(shù)據(jù)清單，由兩部分構成：

11、條件列標題和具體篩選條件，其中首行包含的列標題必須拼寫正確，與數(shù)據(jù)清單中的對應列標題一模一樣，具體條件區(qū)域中至少要有一行篩選條件。 高級篩選注意高級篩選注意取消篩選取消篩選自動篩選：定位于清單內，數(shù)據(jù)篩選去除“自動篩選”高級篩選：定位于清單內，數(shù)據(jù)篩選全部顯示(指在原表格下篩選)高級篩選案例高級篩選案例條件區(qū)域條件區(qū)域條件區(qū)域條件區(qū)域篩選結果篩選結果條件區(qū)域條件區(qū)域篩選結果篩選結果 原則：先分類原則：先分類(排序排序) “數(shù)據(jù)數(shù)據(jù) / 分類匯總分類匯總” 按類匯總按類匯總 對對已排序已排序的數(shù)據(jù)，將相同類別的數(shù)據(jù)，進行求和、計數(shù)、求平的數(shù)據(jù)，將相同類別的數(shù)據(jù)，進行求和、計數(shù)、求平均值及最大、最

12、小值等類的運算稱為匯總運算。均值及最大、最小值等類的運算稱為匯總運算。 作用：作用：分類匯總可以對數(shù)據(jù)清單中的各個字段分類逐級進行數(shù)據(jù)的匯總計算，并分類匯總可以對數(shù)據(jù)清單中的各個字段分類逐級進行數(shù)據(jù)的匯總計算，并將結果自動顯示出來，另外還具有分級顯示功能，常用于的總結報告將結果自動顯示出來，另外還具有分級顯示功能，常用于的總結報告分類匯總分類匯總 (指定分類字段、匯總方式等指定分類字段、匯總方式等)注：分級符：3是最明細的數(shù)據(jù) 2是上一級的匯總數(shù)據(jù) 1就是最上級的匯總數(shù)據(jù)數(shù)據(jù)匯總的相關概念分類字段：在該下拉列表中可以指定進行分類匯總的字段選定匯總項：在該列表中可以選擇多個要執(zhí)行匯總操作的字段。

13、匯總方式：在該下拉列表中可以選擇的匯總方式包括：求平均值、計數(shù)、求和等。替換當前分類匯總：如果已經執(zhí)行過分類匯總操作，選中該復選框，會以本次操作的分類匯總結果替換上一次的結果。每組數(shù)據(jù)分頁：如果選中該復選框，則分類匯總的結果按照不同的分類匯總結果分頁，在打印的時也將分頁打印。匯總結果顯示在數(shù)據(jù)的下方：如果選中該復選框，則分類匯總的每一類會顯示在本數(shù)據(jù)的下方，否則會顯示在本類數(shù)據(jù)的上方。全部刪除：單擊該按鈕，可以將當前的分類匯總結果清除。創(chuàng)建分類匯總創(chuàng)建分類匯總 要插入分類匯總，要先對數(shù)據(jù)清單進行要插入分類匯總，要先對數(shù)據(jù)清單進行排序排序，以便將進，以便將進行分類匯總的數(shù)據(jù)行組合在一起。行分類匯

14、總的數(shù)據(jù)行組合在一起。 按照需要進行分類匯總的字段排序；按照需要進行分類匯總的字段排序； 單擊數(shù)據(jù)清單中的任一數(shù)據(jù)單元格，選擇單擊數(shù)據(jù)清單中的任一數(shù)據(jù)單元格，選擇【數(shù)據(jù)數(shù)據(jù)】【分類匯總分類匯總】命令，打開對話框；命令，打開對話框； 在在【分類字段分類字段】下拉列表框中選擇分類匯總的字段名；下拉列表框中選擇分類匯總的字段名； 選擇匯總方式和匯總項，下方復選框采用默認，點選擇匯總方式和匯總項，下方復選框采用默認，點【確確定定】。分級顯示數(shù)據(jù)分級顯示數(shù)據(jù) 在創(chuàng)建分類匯總后，可以分級顯示數(shù)據(jù)清單中的數(shù)據(jù)。在創(chuàng)建分類匯總后，可以分級顯示數(shù)據(jù)清單中的數(shù)據(jù)。每一級的數(shù)據(jù)都被分為若干組。每一級的數(shù)據(jù)都被分為若

15、干組。 單擊代表級數(shù)的數(shù)字，可指定顯示到該級的明細數(shù)據(jù)。單擊代表級數(shù)的數(shù)字，可指定顯示到該級的明細數(shù)據(jù)。 每一組分類數(shù)據(jù)的左側都有一個隱藏（或顯示）明細數(shù)每一組分類數(shù)據(jù)的左側都有一個隱藏（或顯示）明細數(shù)據(jù)按鈕。單擊隱藏明細數(shù)據(jù)按鈕據(jù)按鈕。單擊隱藏明細數(shù)據(jù)按鈕 ，即可隱藏該組的明細，即可隱藏該組的明細數(shù)據(jù)；單擊顯示明細數(shù)據(jù)按鈕數(shù)據(jù)；單擊顯示明細數(shù)據(jù)按鈕 ，即可顯示。，即可顯示。清除分類匯總清除分類匯總 選擇選擇【數(shù)據(jù)數(shù)據(jù)】【分類匯總分類匯總】，打開對話框，單擊，打開對話框，單擊【全全部刪除部刪除】即可。即可。 分級顯示分級顯示 例題例題 需要創(chuàng)建兩層以上的分類匯總，稱為需要創(chuàng)建兩層以上的分類匯總

16、，稱為嵌套匯總。嵌套匯總。 嵌套匯總命令的使用前提嵌套匯總命令的使用前提：數(shù)據(jù)已按兩個以上關鍵字排序（這里要：數(shù)據(jù)已按兩個以上關鍵字排序（這里要對對“分公司分公司”和和“部門部門”進行排序）。進行排序）。 嵌套分類匯總步驟：嵌套分類匯總步驟： 1）首先針對兩列或多列數(shù)據(jù)進行排序。）首先針對兩列或多列數(shù)據(jù)進行排序。 2）在要分類匯總的數(shù)據(jù)清單中，單擊任一單元格，選定該數(shù)據(jù)清）在要分類匯總的數(shù)據(jù)清單中，單擊任一單元格，選定該數(shù)據(jù)清單，使用單，使用“數(shù)據(jù)分類匯總數(shù)據(jù)分類匯總”命令。命令。 3）在）在“分類字段分類字段”框中，選擇需要用來分類匯總的數(shù)據(jù)列?？蛑校x擇需要用來分類匯總的數(shù)據(jù)列。 4）顯示

17、出對第一列的自動分類匯總后，對下一列進行分類設置）顯示出對第一列的自動分類匯總后，對下一列進行分類設置 5）清除）清除“替換當前分類匯總替換當前分類匯總”復選框，接著單擊復選框，接著單擊“確定確定”按鈕。按鈕。 嵌套分類匯總嵌套分類匯總如：查看每分公司的人工費用支出情況和查看每一部門中最高薪如：查看每分公司的人工費用支出情況和查看每一部門中最高薪水額，我們應使用水額，我們應使用“嵌套分類匯總嵌套分類匯總”命令。命令。不選錯誤警告錯誤警告數(shù)據(jù)有效性 就是允許對單元格中所輸入的值進行設置限制，稱為數(shù)據(jù)有效性。在默認情況下，單元格中可以輸入任何值。有效性設置方法：有效性設置方法：“數(shù)據(jù)數(shù)據(jù)”菜單菜單

18、 有效性有效性 設置條件設置條件有效性案例：有效性案例：北京時代股份有限公司職工情況簡表北京時代股份有限公司職工情況簡表 職工號職工號姓名姓名性別性別部門部門基本工資基本工資出生日期出生日期 001 劉建軍 男 辦公室 002 王濤 男 辦公室 003 李利 女 銷售部 004 許維恩 男 辦公室 005 馬民 男 業(yè)務科 006 叢林 女 業(yè)務科 007 張文 男 銷售部 008 江水 男 業(yè)務科 用數(shù)據(jù)有效性使基本工資滿足1000并且2000的整數(shù)；1955年6月1日出生日期分列-分隔符號-下一步后選擇相應的符號 添加分列線 數(shù)據(jù) -分列-固定寬度-添加分列線分分 列列合并計算合并計算 不

19、同的工作表間、工作表不的不同區(qū)域、工作表中相同性質的數(shù)據(jù)區(qū)的函數(shù)計算，不需要在合并之前排序不需要在合并之前排序技巧技巧 選中結果區(qū)“數(shù)據(jù)”菜單 合并計算 對話框選取按位置進行合并（不包含標題行列）按分類合并（包含行列標題） 數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的交數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的交互式表格?；ナ奖砀?。數(shù)據(jù)透視表是一種特殊的工作表。它從工作表中抽取數(shù)據(jù)透視表是一種特殊的工作表。它從工作表中抽取需要的信息，經處理后再匯總成新的交叉表。它廣泛應用于數(shù)據(jù)需要的信息，經處理后再匯總成新的交叉表。它廣泛應用于數(shù)據(jù)分析。分析。數(shù)據(jù)透視表和數(shù)據(jù)數(shù)據(jù)透視圖報表數(shù)據(jù)透

20、視表和數(shù)據(jù)數(shù)據(jù)透視圖報表建立數(shù)據(jù)透視表建立數(shù)據(jù)透視表 “數(shù)據(jù)”菜單“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”根據(jù)向導進行布局，拖動數(shù)據(jù)字段進行設置，雙擊匯總方式可以修改它。數(shù)據(jù)透視表：數(shù)據(jù)透視表：可實現(xiàn)按多個字段分類匯總?？蓪崿F(xiàn)按多個字段分類匯總。 匯總方匯總方式式 行、列、頁行、列、頁為分類的字為分類的字段段 數(shù)據(jù)清數(shù)據(jù)清單字段單字段創(chuàng)建好了的數(shù)據(jù)透視表。單擊創(chuàng)建好了的數(shù)據(jù)透視表。單擊系別、數(shù)據(jù)、性別系別、數(shù)據(jù)、性別旁的箭頭可以旁的箭頭可以更改顯示內容（全部顯示還是顯示部分內容）。更改顯示內容（全部顯示還是顯示部分內容）。 例：例：在在“職工登記表職工登記表”中建立數(shù)據(jù)透視表來查詢各部門各職稱的中建立數(shù)據(jù)透

21、視表來查詢各部門各職稱的平均年齡平均年齡（即指定布局以即指定布局以“所屬部門所屬部門”為為行行字段，以字段，以“職稱職稱”為為列列字段，以字段，以“年齡年齡”為為平均值平均值項項） 5）雙擊數(shù)據(jù)區(qū)域內的數(shù)據(jù)項“年齡”，選擇“平均值” 1）“數(shù)據(jù)/數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令 選擇“生成數(shù)據(jù)透視表” ；2）在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導”對話框中，單擊“選定區(qū)域”右側的按鈕 在“數(shù)據(jù)源”工作表中選定數(shù)據(jù)區(qū)域；3）單擊右側的按鈕，按“下一步”鈕，進行布局 4）單擊“布局”鈕，將所要字段拖到數(shù)據(jù)透視表中適當位置（將所屬部門拖到“行”、職稱拖到“列”、年齡拖到“數(shù)據(jù)”）；6）單擊“確定”和“完成”鈕

22、數(shù)據(jù)透視表編輯數(shù)據(jù)透視表編輯單擊鼠標右鍵修改透視表：字段設置、表格選項、顯示字段列表通過字段設置修改字段名稱、匯總方式通過表格選項確定是否需要行、列總計通過字段列表增加頁、行、列的分類字段；刪除分類字段（直接拖走）修改數(shù)據(jù)透視表修改數(shù)據(jù)透視表可在透視表內直接拖動字段名定位于透視表內，數(shù)據(jù)數(shù)據(jù)透視表和圖表布局更新數(shù)據(jù)更新數(shù)據(jù) 可將光標定位于透視表內 數(shù)據(jù)更新數(shù)據(jù)或單擊“數(shù)據(jù)透視表”工具欄中/右擊 “ ！”按鈕創(chuàng)建數(shù)據(jù)透視圖創(chuàng)建數(shù)據(jù)透視圖單擊“數(shù)據(jù)透視圖”工具欄的圖表向導按鈕 數(shù)據(jù)透視表編輯數(shù)據(jù)透視表編輯批注批注 批注：是附加在單元格中，對單元格內容進行解釋說明;添加批注添加批注右鍵插入批注插入批注Shift+F2 有批注的單