輕松辦公系列進階課堂-OfficeExcel數據透視表和數據透視圖

（二十三）

使用數據透視表和數據透視圖向導

（2）

為數據透視表優化内存

隻能為連接到開放式數據庫連接 (ODBC) 數據源的數據透視表優化内存。必須使用數據透視表和數據透視圖向導完成以下過程。

1. 要啟動數據透視表和數據透視圖向導，請按 Alt D P。

要将“數據透視表和數據透視圖向導”添加到快速訪問工具欄，請執行下列操作：

1. 單擊工具欄旁邊的箭頭，然後單擊“其他命令”。
2. 在“從下列位置選擇命令”下，選擇“所有命令”。
3. 在列表中選擇“數據透視表和數據透視圖向導”，單擊“添加”，然後單擊“确定”。
4. 在向導的“步驟 1”頁面上，選擇“外部數據源”，然後單擊“下一步”。
5. 在向導的“步驟 2”頁面上，單擊“獲取數據”。
6. 連接到數據源。

有關如何連接到 ODBC 數據源的更多信息，請參閱下面的：

使用 Microsoft Query 檢索外部數據

1. 在向導的“步驟 3”頁面上，單擊“選項”。
2. 在“數據透視表和數據透視圖向導選項”對話框中選中“優化内存”複選框。

使用 Microsoft Query 檢索外部數據

可以使用 Microsoft Query 檢索來自外部源的數據。通過使用 Microsoft Query 從企業數據庫（數據庫：與特定主題或用途相關的數據的集合。在數據庫内，關于特定實體的信息（如雇員或訂單）分類歸納到表、記錄和字段中。）和文件中檢索數據，可以避免在 Excel 中重新鍵入要分析的數據。另外，隻要原始源數據庫更新了新的信息，你就可以自動從該數據庫中刷新 Excel 報表和摘要。

一、了解有關 Microsoft Query 的詳細信息

使用 Microsoft Query，可以連接到外部數據源，從那些外部數據源選擇數據，将該數據導入到工作表中，以及根據需要刷新數據以使工作表數據與外部源中的數據保持同步。

• 可以訪問的數據庫類型

你可以從多種類型的數據庫中檢索數據，包括 Microsoft Office Access、Microsoft SQL Server 和 Microsoft SQL Server OLAP Services。你還可以從 Excel 工作簿和文本文件中檢索數據。

Microsoft Office 提供可用于從下列數據源（數據源：用于連接數據庫的一組存儲的“源”信息。數據源包含數據庫服務器的名稱和位置、數據庫驅動程序的名稱以及在登錄到數據庫時所需的信息。）檢索數據的驅動程序：

• Microsoft SQL Server Analysis Services（OLAP 提供程序 （OLAP 提供程序：對特定類型的 OLAP 數據庫提供訪問功能的一組軟件。該軟件包括數據源驅動程序以及與數據庫連接所必需的其他客戶端軟件。））
• Microsoft Office Access
• dBASE
• Microsoft FoxPro
• Microsoft Office Excel
• Oracle
• Paradox
• 文本文件數據庫

你還可以使用來自其他制造商的 ODBC 驅動程序 （開放式數據庫連接 (ODBC) 驅動程序：用來連接到特定數據庫的程序文件。每個數據庫程序（如 Access 或 dBASE）或數據庫管理系統（如 SQL Server）需要不同的驅動程序。）或數據源驅動程序 （數據源驅動程序：用于連接指定數據庫的程序文件。每個數據庫程序或管理系統需要不同的驅動程序。），從未在此處列出的數據源（包括其他類型的 OLAP 數據庫）檢索信息。有關安裝此處未列出的 ODBC 驅動程序或數據源驅動程序的信息，請查看數據庫文檔或與數據庫供應商聯系。

• 從數據庫中選擇數據

你通過創建查詢從數據庫中檢索數據，查詢是你提出的有關存儲在外部數據庫中的數據的問題。例如，如果數據存儲在 Access 數據庫中，你可能想要知道某種特定産品在各個地區的銷售數字。你可以隻選擇要分析的産品和地區的數據，從而檢索部分數據。

使用 Microsoft Query，你可以選擇所需的數據列，并隻将這些數據導入 Excel。

• 通過一個操作更新工作表

如果你的 Excel 工作簿中包含外部數據，那麼，隻要數據庫發生更改，就可以刷新（刷新：更新源于外部數據源的數據。每次刷新數據後，查看到的都是數據庫中信息的最新版本，其中包括對數據所做的任何更改。）數據以更新你的分析，而不必重新創建彙總報表和圖表。例如，你可以創建每月銷售彙總，并在每個月的新銷售數字出來後刷新它。

• Microsoft Query 如何使用數據源

為特定數據庫設置數據源以後，隻要想創建查詢以從該數據庫中選擇并檢索數據，就可以使用該數據源，而不必重新鍵入所有連接信息。Microsoft Query 使用該數據源連接到外部數據庫并顯示可用的數據。創建查詢并将數據返回到 Excel 以後，Microsoft Query 會為 Excel 工作簿提供查詢和數據源信息，以便你可以在需要刷新數據時重新連接到數據庫。

• 使用 Microsoft Query 導入數據

要使用 Microsoft Query 将外部數據導入到 Excel 中，請執行下列基本步驟，其中每個步驟都在後面各節中進行了詳細介紹：

1. 連接數據源。
2. 使用查詢向導定義查詢。
3. 在 Excel 中處理數據。

二、連接數據源

• 什麼是數據源？

數據源是存儲起來的一組信息，它允許 Excel 和 Microsoft Query 連接到外部數據庫。使用 Microsoft Query 設置數據源時，可為數據源指定名稱，然後提供數據庫或服務器的名稱和位置、數據庫的類型，以及你的登錄和密碼信息。該信息還包括 OBDC 驅動程序或數據源驅動程序的名稱，驅動程序是連接到特定類型的數據庫的程序。

• 使用 Microsoft Query 設置數據源：

1. 在“數據”選項卡上的“獲取外部數據”組中，單擊“自其他來源”，然後單擊“來自 Microsoft Query”。
2. 請執行下列操作之一：

• 若要為數據庫、文本文件或 Excel 工作簿指定數據源，請單擊“數據庫”選項卡。
• 要指定 OLAP 多維數據集（多維數據集定義：由“多維數據集向導”存儲在一個 .oqy 文件中的信息，該信息定義了如何通過由關系數據庫檢索到的數據在内存中構建 OLAP 多維數據集。）數據源，請單擊“OLAP 多維數據集”選項卡。隻有當從 Excel 運行 Microsoft Query 時，該選項卡才可用。

1. 雙擊“<新數據源>”或單擊“<新數據源>”，然後單擊“确定”。

“創建新數據源”對話框即會顯示。

1. 在步驟 1 中，鍵入名稱來标識數據源。
2. 在步驟 2 中，單擊要用作數據源的數據庫類型的驅動程序。

如果随 Microsoft Query 一起安裝的 ODBC 驅動程序不支持你要訪問的外部數據庫，則你需要獲取并安裝由第三方供應商（如數據庫制造商）提供的與 Microsoft Office 兼容的 ODBC 驅動程序。有關安裝說明，請與數據庫供應商聯系。

注釋：OLAP 數據庫不需要 ODBC 驅動程序。安裝 Microsoft Query 時，會為使用 Microsoft SQL Server Analysis Services 創建的數據庫安裝驅動程序。要連接到其他 OLAP 數據庫，需要安裝數據源驅動程序和客戶端軟件。

1. 單擊“連接”，然後提供連接到數據源所需的信息。對于數據庫、Excel 工作簿和文本文件，你提供的信息取決于你選擇的數據源的類型。你可能需要提供登錄名、密碼、所使用的數據庫的版本、數據庫位置或其他特定于該數據庫類型的信息。

使用由大寫字母、小寫字母、數字和符号組合而成的強密碼。弱密碼不混合使用這些元素。例如，Y6dh!et5 是強密碼；House27 是弱密碼。密碼長度應大于或等于 8 個字符。最好使用包括 14 個或更多個字符的密碼。有關詳細信息，請參閱使用強密碼有助于保護個人信息。

記住密碼很重要。如果忘記了密碼，Microsoft 将無法找回。最好将密碼記錄下來，保存在一個安全的地方，這個地方應該盡量遠離密碼所要保護的信息。

1. 輸入所需的信息之後，單擊“确定”或“完成”，返回到“創建新數據源”對話框。
2. 如果數據庫包含表 （表：關于特定主題的一組數據的集合，以記錄（行）和字段（列）的形式存儲。）并且你希望某個特定表在“查詢向導”中自動顯示，請單擊步驟 4 的框，然後單擊所需的表。
3. 如果不想在使用數據源時鍵入登錄名和密碼，請選中“在數據源定義中保存我的用戶标識符和密碼”複選框。保存的密碼未進行加密。如果無法使用該複選框，請詢問數據庫管理員以确定此選項是否可用。

安全性：請避免在連接到數據源時保存登錄信息。該信息可能以純文本方式進行存儲，惡意用戶可以訪問該信息，這樣将危及數據源的安全。

完成這些步驟後，數據源的名稱會顯示在“選擇數據源”對話框中。

三、使用查詢向導定義查詢

• 将“查詢向導”用于大多數查詢

使用“查詢向導”，可以很輕松地從數據庫中的不同表和字段中選擇數據并将數據放在一起。使用“查詢向導”，可以選擇要包括的表和字段。當向導識别一個表中的一個主要字段和另一個表中的一個同名字段時，會自動創建内部聯接（一種查詢操作，它指定兩個表中的行基于相同的字段值進行合并）。

你還可以使用該向導為結果集排序以及執行簡單的篩選。在向導的最後一步，可以選擇将數據返回到 Excel 或是在 Microsoft Query 中進一步細調查詢。創建查詢後，可以在 Excel 或 Microsoft Query 中運行它。

要啟動查詢向導，請執行下列步驟：

1. 在“數據”選項卡上的“獲取外部數據”組中，單擊“自其他來源”，然後單擊“來自 Microsoft Query”。
2. 在“選擇數據源”對話框中，确保選中“使用‘查詢向導’創建/編輯查詢”複選框。
3. 雙擊要使用的數據源或單擊要使用的數據源，然後單擊“确定”。

• 直接在 Microsoft Query 中處理其他類型的查詢

如果還要創建查詢向導不允許的複雜查詢，可以直接在 Microsoft Query 中進行。你可以使用 Microsoft Query 來查看并更改在“查詢向導”中開始創建的查詢，也可以在不使用向導的情況下創建新查詢。要創建執行以下操作的查詢時，請直接在 Microsoft Query 中進行：

• 從字段中選擇特定數據

在大型數據庫中，可能需要選擇一個字段中的某些數據并省略不需要的數據。例如，如果需要兩種産品的數據，而該數據位于包含許多産品的信息的字段中，則可以使用條件 （條件：所指定的限制查詢或篩選的結果集中包含哪些記錄的條件。）來隻選擇所需的兩種産品的數據。

• 每次運行查詢時根據不同的條件檢索數據

如果需要使用相同的外部數據為多個區域創建相同的 Excel 報表或彙總（如為每個地區創建一份單獨的銷售報表），可以創建參數查詢 （參數查詢：一種查詢類型，當運行參數查詢時，将提示輸入用于為結果集選擇記錄的值（條件），這樣同一個查詢就可用于檢索不同的結果集。）。運行參數查詢時，系統會提示你輸入值以用作查詢選擇記錄的條件。例如，參數查詢可能會提示你輸入特定區域，并且你可以再次使用該查詢創建每一份地區銷售報表。

• 以不同的方式聯接數據

“查詢向導”創建的内部聯接是創建查詢時使用的最常見的聯接類型。但是，有時需要使用不同的聯接類型。例如，如果你有一個産品銷售信息表和一個客戶信息表，則内部聯接（“查詢向導”創建的類型）将禁止檢索未進行購買的客戶的客戶記錄。使用 Microsoft Query，可以聯接這些表，以便除了可以檢索已進行購買的客戶的銷售數據外，還可以檢索所有客戶記錄。

• 要啟動 Microsoft Query，請執行下列步驟：

1. 在“數據”選項卡上的“獲取外部數據”組中，單擊“自其他來源”，然後單擊“來自 Microsoft Query”。
2. 在“選擇數據源”對話框中，确保清除“使用‘查詢向導’創建/編輯查詢”複選框。
3. 雙擊要使用的數據源或單擊要使用的數據源，然後單擊“确定”。

• 重用和共享查詢

在“查詢向導”和 Microsoft Query 中，可以将查詢保存為可以修改、重用和共享的 .dqy 文件。Excel 可以直接打開 .dqy 文件，這樣，你或其他用戶便可以通過同一查詢創建其他外部數據區域。

• 從 Excel 打開所保存的查詢：
• 在“數據”選項卡上的“獲取外部數據”組中，單擊“自其他來源”，然後單擊“來自 Microsoft Query”。“選擇數據源”對話框即會顯示。
• 在“選擇數據源”對話框中，單擊“查詢”選項卡。
• 雙擊要打開的已保存查詢。該查詢即會顯示在 Microsoft Query 中。

如果要打開已保存的查詢并且 Microsoft Query 已經打開，請單擊 Microsoft Query 的“文件”菜單，然後單擊“打開”。

如果雙擊 .dqy 文件，Excel 會打開，運行查詢，然後将結果插入到新工作表中。

如果要共享基于外部數據的 Excel 彙總或報表，可為其他用戶提供一個包含外部數據區域的工作簿，也可以創建一個模闆 （模闆：創建後作為其他相似工作簿基礎的工作簿。可以為工作簿和工作表創建模闆。工作簿的默認模闆名為 Book.xlt，工作表的默認模闆名為 Sheet.xlt。）。通過使用模闆，可以保存彙總或報表，而不必保存外部數據，這樣文件便會較小。在用戶打開報表模闆時會檢索外部數據。

四、在 Excel 中處理數據

使用“查詢向導”或 Microsoft Query 創建查詢以後，可以将數據返回到 Excel 工作表。之後，數據會變為外部數據區域 （外部數據區域：從 Excel 的外部（如，數據庫或文本文件）導入工作表的數據區域。在 Excel 中，可為外部數據區域中的數據設置格式或用其進行計算，就如同對其他任何數據一樣。）或數據透視表 （數據透視表：一種交互的、交叉制表的 Excel 報表，用于對多種來源（包括 Excel 的外部數據）的數據（如數據庫記錄）進行彙總和分析。），你可以對其進行格式設置和刷新。

• 為檢索到的數據設置格式

在 Excel 中，可以使用各種工具（如圖表或自動分類彙總）來呈現并彙總 Microsoft Query 檢索的數據。你可以設置數據格式，刷新外部數據時會保留你的格式。你可以使用自己的列标簽來替代字段名稱，并自動添加行号。

Excel 可以自動為你在區域末尾鍵入的新數據設置格式以與前面的行匹配。Excel 還可以自動複制在前面的行中重複的公式并将它們擴展到其他行。

注釋：為了擴展到區域中的新行，格式和公式必須在前面五行中的至少三行中顯示。

• 你可以随時啟用（或再次禁用）此選項：

單擊“Office 按鈕”按鈕圖像

，單擊“Excel 選項”，然後單擊“高級”類别。

在“編輯”部分中，選中“擴展數據區域格式及公式”複選框以關閉此選項。要再次關閉自動數據區域格式設置，請清除此複選框。

• 刷新外部數據

刷新外部數據時，請運行查詢來檢索與你指定的條件匹配的、所有新的或更改過的數據。你可以在 Microsoft Query 和 Excel 中刷新查詢。Excel 提供了許多用于刷新查詢的選項，包括每次打開工作簿時刷新數據和定期自動刷新數據。在刷新數據時，你可以繼續在 Excel 中工作，并且還可以在刷新數據的同時檢查狀态。

單元格區域、Excel 表、數據透視表、數據透視圖、文本文件和 Web 查詢均可以連接到外部數據源 （數據源：用于連接數據庫的一組存儲的“源”信息。數據源包含數據庫服務器的名稱和位置、數據庫驅動程序的名稱以及在登錄到數據庫時所需的信息。）。可以刷新數據以更新此外部數據源的數據。每次刷新數據後，你都會看到數據源中最新版本的信息，包括對數據所做的任何更改。

不過，鑒于安全性考慮，你的計算機可能禁用了與外部數據的連接。若要在打開工作簿時刷新數據，必須使用“信任中心”欄以啟用數據連接，或者将工作簿放置在受信任位置。

下一節：《輕松辦公系列進階課堂-OfficeExcel數據透視表和數據透視圖（二十四）使用數據透視表和數據透視圖向導（3）》

更多精彩内容将在以後的章節分享給朋友們，請添加好友并收藏，請點贊并歡迎關注後期更新！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!