5.6工作表中的數據庫操作

Excel提供了較強的數據庫管理功能，不僅能夠通過記錄單來增加、刪除和移動數據，還能夠按照數據庫的管理方式對以數據清單形式存放的工作表進行各種排序、篩選、分類匯總、統計和建立數據透視表等操作。需要特別注意的是，對工作表數據進行數據庫操作，要求數據必須按“數據清單”存放。

5.6.1建立數據清單

1．數據清單

數據清單是指包含一組相關數據的一系列工作表數據行。Excel允許采用數據庫管理的方式管理數據清單。數據清單由標題行（表頭）和數據部分組成。數據清單中的行相當于數據庫中的記錄，行標題相當于記錄名；數據清單中的列相當于數據庫中的字段，列標題相當于字段名。2．使用記錄單建立數據清單

建立數據清單時，可以采用建立工作表的方式向行、列中逐個輸入數據，也可以使用“記錄單”建立和編輯數據清單。記錄單是數據清單的一種管理工具，通過將記錄單添加到“自定義快速訪問欄”可以更方便地在數據清單中輸入、修改、刪除和移動數據記錄。

將記錄單添加到“自定義快速訪問欄”的具體操作步驟：

（1）選擇“文件”選項卡，“選項/快速訪問工具欄”，在“從下列位置選擇命令”中選擇“所有命令”，選中下拉列表中的“記錄單”。

（2）單擊“添加”、“確定”按鈕。

使用“記錄單”按鈕輸入、修改、刪除和移動數據記錄具體操作步驟：

（1）選定工作表的數據區域，單擊“記錄單”按鈕

，彈出“記錄單”對話框。如圖5-46所示。

（2）單擊“新建”按鈕，依次在字段名右側的輸入框中輸入數據，再次單擊“新建”或單擊“關閉”，記錄增加完成（只能在記錄尾部增加記錄）。

如想修改、刪除數據記錄，只需定位在要修改、刪除的記錄，直接修改、刪除即可。

5.6.2數據排序

1．使用“升序”按鈕

（“降序”按鈕

）`排序

使用“升序”按鈕

（“降序”按鈕

）`排序的具體操作步驟：

（1）選定要排序的單元格區域。

（2）單擊“升序”（“降序”）按鈕，彈出“排序提醒”對話框，選擇“以當前選定區域排序”單選框，單擊“排序”。2．使用自定義排序

使用自定義排序的具體操作步驟：

（1）選定要排序的單元格區域。

（2）選擇“數據”/排序和篩選”，單擊“排序”，彈出“排序”對話框如圖5-47所示。在“列”選項組中“主要關鍵字”下拉列表中選擇要排序的列。“排序依據”下拉列表中，可根據需要，對于文本值、數值、日期或時間值排序選擇“數值”；對于格式排序選擇“單元格顏色”、“字體顏色”和“單元格圖標”。在“次序”下拉列表中選擇排序方式。

（3）當排序關鍵字不止一個時，單擊“添加條件”，對話框顯示“次要關鍵字”，如步驟

（2），重復設置。

若要刪除作為排序依據的列，需選擇該條目，然后單擊“刪除條件”。若要復制作為排序依據的列，需選擇該條目，然后單擊“復制條件”。若要更改列的排序順序，需選擇一個條目，然后單擊

或更改順序

（4）單擊“確定”按鈕。提示：

對列進行排序時，隱藏的列不會移動；對行進行排序時，隱藏的列也不會移動。在對數據進行排序之前，最好先取消隱藏已隱藏的列和行。

當選取數據包含標題時勾選“數據包含標題”以免排序時出現錯誤。

單擊“排序”對話框中“選項”按鈕，彈出“排序選項”對話框，如圖5-48所示。選中“方向”下的“按行排序”可以對行進行排序。

在Excel2010中，排序條件最多可以支持64個關鍵字。3．排序數據區域選擇

如果選定的數據清單內容沒有包含所有的列，Excel會彈出“排序提醒”對話框，可選擇“擴展選定區域”或“以當前選定區域排序”，如果選中“擴展選定區域”，Excel自動選定數據清單的全部內容，如果選中“以當前選定區域排序”，Excel將只對已選定的區域排序，未選定的區域不變（有可能引起數據錯誤）。4．恢復排序

如果希望將已經過多次排序的數據清單恢復到排序前的狀況，可以在數據清單中設置“記錄號”字段，內容為順序數字1、2、3、4…，無論何時，只要按“記錄號”字段升序排列即可恢復為排序前的數據清單。

5.6.3數據篩選

數據篩選是在工作表的數據清單中快速查找具有特定條件的記錄，篩選后數據清單中只包含符合篩選條件的記錄，以便于瀏覽。

在單元格區域或Excel表中選中至少一個單元格，選擇“數據”選項卡，“排序和篩選”組，單擊“篩選”按鈕

。啟用篩選如圖5-49所示。

單擊標題旁下拉箭頭

顯示篩選器選擇列表如圖5-50所示。1．自動篩選1）單字段條件篩選

篩選條件只涉及一個字段內容稱為單字段條件篩選。

單字段自動篩選的具體操作步驟：

（1）選定要篩選的數據。

（2）選擇選擇“數據/排序和篩選”組，單擊“篩選”按鈕。

（3）單擊列標題中的下拉箭頭

，在彈出的篩選器選擇列表中，按所需可選擇

①在“搜索”框輸入要搜索的字符，單擊“確定”。

②在數據列中，取消不需顯示的數值選項前的復選框。

③根據列中的數據類型，可選擇“數字篩選”如圖5-51所示或“文本篩選”如圖5-52所示。按所需，數字類型可選擇“等于”、“不等于”、“大于”、“大于等于”和“自定義篩選”等條件進行篩選，文本類型可選擇“等于”、“不等于”、“開頭是”、“結尾是”和“自定義篩選”等條件進行篩選。

（4）單擊“確定”。列標題中的

變為表示此列已應用篩選。

（5）單擊列標題中的

，在彈出的“篩選器選擇列表”中，單擊“從**中清除篩選”可以取消此列數據的篩選。2）多字段條件篩選

多字段自動篩選的具體操作步驟：

（1）選定要篩選的數據。

（2）選擇選擇“數據/排序和篩選”，單擊“篩選”按鈕。

（3）單擊列標題中的下拉箭頭

，在彈出的“篩選器選擇列表”中選擇“文本篩選/自定義篩選”（或“數字篩選/自定義篩選”），彈出“自定義自動篩選方式”對話框，如圖5-53所示。在右側一個或多個框中，輸入文本（或數字）或從下拉列表中選擇文本值（或數字）

（4）單擊“確定”。列標題中的

變為

。

（5）在第一次篩選出的結果中，打開下一次要篩選的列標題下拉箭頭

，重復（3）、（4）步驟。

（6）單擊列標題中的

，在彈出的“篩選器選擇列表”中，單擊“從**中清除篩選”可以取消此列數據的篩選。選擇“數據”選項卡，“排序和篩選”組，單擊“清除”按鈕

可以清除當前數據范圍的篩選和排序。2．高級篩選Excel提供高級篩選方式，主要用于多字段條件的篩選。使用高級篩選必須先建立一個條件區域，用來編輯篩選條件。條件區域的第一行是所有作為篩選條件的字段名，這些字段名必須與數據清單中的字段名完全一樣。條件區域的其他行輸入篩選條件，“與”關系的條件必須出現在同一行內，“或”關系的條件不能出現在同一行內。條件區域與數據清單區域不能連接，須用空行隔開。

【例題5-14】對工作表“公司人員薪金表”數據清單的內容進行高級篩選，如圖5-54所示。必須同時滿足兩個條件：條件1，基本薪金大于等于4000并且小于等于5000；條件2，學歷為碩士或博士。

高級篩選的具體操作步驟：

（1）在工作表的第一行前插入四行作為高級篩選的條件區域。

（2）在條件區域（A1:D3）區域輸入篩選條件，選擇工作表的數據清單區域（A6:G26）。

（3）選擇“數據/排序和篩選”，單擊“高級”按鈕

，彈出“高級篩選”對話框，選擇“在原有區域顯示篩選結果”（也可以選擇“將篩選結果復制到其他位置”），利用下拉按鈕

確定“列表區域”（數據清單區域）和“條件區域”（篩選條件區域），單擊“確定”按鈕即可完成高級篩選，如圖5-55所示。

5.6.4數據分類匯總

分類匯總是對數據內容進行分析的一種方法。Excel分類匯總是對工作表中數據清單的內容進行分類，然后統計同類記錄的相關信息，包括求和、計數、平均值、最大值、最小值等，由用戶進行選擇。

分類匯總只能對數據清單進行，數據清單的第一行必須有列標題。在進行分類匯總前，必須根據分類匯總的數據類對數據清單進行排序。1．創建分類匯總

創建分類匯總的具體操作步驟：

（1）按分類字段進行排序。例如：按“部門”分類，

（2）選擇“數據/分級顯示”組，單擊“分類匯總”按鈕

，彈出“分類匯總對話框”如圖5-56所示。

（3）在“分類字段”、“匯總方式”的下拉列表中以及“選定匯總項”復選框中，選擇所需選項。例如：匯總方式為“平均值”，匯總項為“基本薪金”。

若本次匯總前，已經進行過某種分類匯總，根據需要決定是否替換原匯總數據，選中（或清除）“替換當前分類匯總”。

根據需要決定每類匯總是否獨占一頁，選中（或清除）“每組數據分頁”。

（4）單擊“確定”。分類匯總后的結果如圖5-57所示。2．刪除分類匯總

如果要刪除已經創建的分類匯總，可在“分類匯總”對話框中單擊“全部刪除”按鈕，即可刪除分類匯總。3．隱藏分類匯總數據

若要只顯示分類匯總和總計的匯總，請單擊行編號旁邊的分級顯示符號

。使用

和符號來顯示或隱藏各個分類匯總的明細數據行。5.6.5數據合并

數據合并可以把來自不同源數據區域的數據進行匯總，并進行合并計算。不同源數據區包括同一工作表中、同一工作簿的不同工作表中、不同工作簿中的數據區域。數據合并是通過建立合并表的方式來進行的。

【例題5-15】現有在同一工作簿中的“1分店”和“2分店”4種型號的產品一月、二月、三月的“銷售數量統計表”數據清單，位于工作表“銷售單1”和“銷售單2”中，如圖5-58所示。現需新建工作表，計算出兩個分店4種型號的產品一月、二月、三月每月銷售量總和。數據合并的具體操作步驟：

（1）在本工作簿中新建工作表“合計銷售單”數據清單，數據清單字段名與源數據清單相同，第一列輸入產品型號，選定用于存放合并計算結果的單元格區域B3:D6，如圖5-59所示。

（2）選擇“數據/數據工具”，單擊“合并計算”按鈕

彈出“合并計算”對話框，在“函數”下拉列表框中選擇“求和”，在“引用位置”下拉按鈕下選取“銷售單1”的B3:D6單元格區域，單擊“添加”按鈕，再選取“銷售單2”的B3:D6單元格區域，單擊“添加”按鈕（此時，單擊“瀏覽”按鈕可以選取不同工作表或工作簿中的引用位置），選中“創建指向源數據的鏈接”復選框，如圖5-60所示。計算結果如圖5-61所示。

（3）合并計算結果以分類匯總的方式顯示，單擊合計銷售單工作表左側的“+”號，可以顯示源數據信息。

5.6.6建立數據透視表

數據透視表從工作表的數據清單中提取信息，它可以對數據清單進行重新布局和分類匯總，還能立即計算出結果。在建立數據透視表時，需考慮如何匯總數據。

【例題5-16】現有如圖5-62所示工作表中的數據清單，現建立數據透視表，顯示各分店各型號產品銷售量的和、總銷售額的和以及匯總信息。具體操作步驟：

（1）選定工作表中的一個單元。

（2）選擇“插入/表格”組，單擊“數據透視表”按鈕

，彈出“創建數據透視表”對話框，如圖5-63所示。確認“表/區域”框內為所需數據區域，“選擇放置數據透視表的位置”選中“現有工作表”單選框，單擊

，在現有工作表指定放置數據透視表的單元格區域