5.6工作表中的數(shù)據(jù)庫操作

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

5.6.1建立數(shù)據(jù)清單

1．?dāng)?shù)據(jù)清單

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

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

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

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

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

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

（1）選定工作表的數(shù)據(jù)區(qū)域，單擊“記錄單”按鈕

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

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

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

5.6.2數(shù)據(jù)排序

1．使用“升序”按鈕

（“降序”按鈕

）`排序

使用“升序”按鈕

（“降序”按鈕

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

（1）選定要排序的單元格區(qū)域。

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

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

（1）選定要排序的單元格區(qū)域。

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

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

（2），重復(fù)設(shè)置。

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

或更改順序

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

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

當(dāng)選取數(shù)據(jù)包含標(biāo)題時勾選“數(shù)據(jù)包含標(biāo)題”以免排序時出現(xiàn)錯誤。

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

在Excel2010中，排序條件最多可以支持64個關(guān)鍵字。3．排序數(shù)據(jù)區(qū)域選擇

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

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

5.6.3數(shù)據(jù)篩選

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

在單元格區(qū)域或Excel表中選中至少一個單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，“排序和篩選”組，單擊“篩選”按鈕

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

單擊標(biāo)題旁下拉箭頭

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

篩選條件只涉及一個字段內(nèi)容稱為單字段條件篩選。

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

（1）選定要篩選的數(shù)據(jù)。

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

（3）單擊列標(biāo)題中的下拉箭頭

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

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

②在數(shù)據(jù)列中，取消不需顯示的數(shù)值選項(xiàng)前的復(fù)選框。

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

（4）單擊“確定”。列標(biāo)題中的

變?yōu)楸硎敬肆幸褢?yīng)用篩選。

（5）單擊列標(biāo)題中的

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

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

（1）選定要篩選的數(shù)據(jù)。

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

（3）單擊列標(biāo)題中的下拉箭頭

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

（4）單擊“確定”。列標(biāo)題中的

變?yōu)?/p>

。

（5）在第一次篩選出的結(jié)果中，打開下一次要篩選的列標(biāo)題下拉箭頭

，重復(fù)（3）、（4）步驟。

（6）單擊列標(biāo)題中的

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

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

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

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

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

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

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

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

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

5.6.4數(shù)據(jù)分類匯總

分類匯總是對數(shù)據(jù)內(nèi)容進(jìn)行分析的一種方法。Excel分類匯總是對工作表中數(shù)據(jù)清單的內(nèi)容進(jìn)行分類，然后統(tǒng)計(jì)同類記錄的相關(guān)信息，包括求和、計(jì)數(shù)、平均值、最大值、最小值等，由用戶進(jìn)行選擇。

分類匯總只能對數(shù)據(jù)清單進(jìn)行，數(shù)據(jù)清單的第一行必須有列標(biāo)題。在進(jìn)行分類匯總前，必須根據(jù)分類匯總的數(shù)據(jù)類對數(shù)據(jù)清單進(jìn)行排序。1．創(chuàng)建分類匯總

創(chuàng)建分類匯總的具體操作步驟：

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

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

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

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

若本次匯總前，已經(jīng)進(jìn)行過某種分類匯總，根據(jù)需要決定是否替換原匯總數(shù)據(jù)，選中（或清除）“替換當(dāng)前分類匯總”。

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

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

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

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

。使用

和符號來顯示或隱藏各個分類匯總的明細(xì)數(shù)據(jù)行。5.6.5數(shù)據(jù)合并

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

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

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

（2）選擇“數(shù)據(jù)/數(shù)據(jù)工具”，單擊“合并計(jì)算”按鈕

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

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

5.6.6建立數(shù)據(jù)透視表

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

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

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

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

，彈出“創(chuàng)建數(shù)據(jù)透視表”對話框，如圖5-63所示。確認(rèn)“表/區(qū)域”框內(nèi)為所需數(shù)據(jù)區(qū)域，“選擇放置數(shù)據(jù)透視表的位置”選中“現(xiàn)有工作表”單選框，單擊

，在現(xiàn)有工作表指定放置數(shù)據(jù)透視表的單元格區(qū)域