使用 Microsoft Query 來檢索外部資料

您可以使用 Microsoft Query 來檢索來自外部來源的資料。 您可以使用 Microsoft Query 來從公司資料庫和檔案中取得資料,而不需要重新輸入您想要在 Excel 中分析的資料。 您也可以在使用新資訊更新資料庫時,自動重新整理原始來源資料庫的 Excel 報表及摘要。

使用 Microsoft Query,您可以連線至外部資料源、從這些外部來源選取資料、將資料匯入至您的工作表,以及根據需要重新整理資料,以使工作表資料與外部來源中的資料保持同步。

您可以存取的資料庫類型     您可以從多種類型的資料庫中檢索資料,包括 Microsoft Office Access、Microsoft SQL Server 及 Microsoft SQL Server OLAP 服務。 您也可以從 Excel 活頁簿和文字檔中檢索資料。

Microsoft Office 提供的驅動程式可讓您用來從下列資料來源中取得資料:

  • Microsoft SQL Server Analysis Services (OLAP 提供者)

  • Microsoft Office Access

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Paradox

  • 文字檔資料庫

您也可以使用來自其他製造商的 ODBC 驅動程式或資料來源驅動程式,來從此處未列出的資料來源(包括其他類型的 OLAP 資料庫)中取得資訊。 如需安裝未在此處列出之 ODBC 驅動程式或資料來源驅動程式的相關資訊,請檢查資料庫的相關檔,或與您的資料庫廠商聯繫。

從資料庫選取資料     您可以建立查詢來從資料庫中取得資料,這是您詢問儲存在外部資料庫中之資料的問題。 例如,如果您的資料儲存在 Access 資料庫中,您可能會想要瞭解特定產品的銷售資料(依地區)。 您可以只選取您想要分析之產品和區域的資料,即可檢索部分資料。

您可以使用 Microsoft Query,選取您想要的資料行,然後只將資料匯入 Excel。

在單一作業中更新工作表     一旦您在 Excel 活頁簿中有外部資料,只要資料庫變更,您就可以 重新整理 資料來更新您的分析,而不需要重新建立摘要報表及圖表。 例如,您可以建立每月銷售摘要,並在每個月的新銷售數位開始時重新整理。

Microsoft Query 如何使用資料來源     在您設定特定資料庫的資料來源之後,只要想要建立查詢來選取並從該資料庫中檢索資料,就可以使用該資料來源,而不必重新輸入所有的連線資訊。 Microsoft Query 會使用資料來源連線至外部資料庫,並向您顯示哪些資料可供使用。 在您建立查詢並將資料傳回 Excel 之後,Microsoft Query 會提供包含查詢和資料來源資訊的 Excel 活頁簿,以便在您想要重新整理資料時重新連線至資料庫。

Query 如何使用資料來源的圖表

使用 Microsoft Query 匯入資料     若要使用 Microsoft Query 將外部資料匯入到 Excel,請遵循下列基本步驟,以下各節將會詳細說明其中的每個步驟。

什麼是資料來源?     資料來源是一組儲存的資訊,可讓 Excel 和 Microsoft Query 連線至外部資料庫。 當您使用 Microsoft Query 設定資料來源時,您會提供資料來源名稱,然後提供資料庫或伺服器的名稱和位置、資料庫的類型,以及您的登入和密碼資訊。 此資訊也包含 OBDC 驅動程式或資料來源驅動程式的名稱,也就是建立特定類型資料庫連線的程式。

若要使用 Microsoft Query 設定資料來源:

  1. 在 [資料] 索引標籤上,按一下 [取得外部資料] 群組中的 [從其他來源],然後按一下 [從 Microsoft Query]。

  2. 執行下列其中一個動作:

    • 若要指定資料庫、文字檔或 Excel 活頁簿的資料來源,請按一下 [資料庫] 索引標籤。

    • 若要指定 OLAP 多維資料集資料來源,請按一下 [ Olap 多維資料集] 索引標籤。 只有當您從 Excel 執行 Microsoft Query 時,才能使用此索引標籤。

  3. 按兩下 [ <新的資料來源>]。

    -或-

    按一下 [ <新的資料來源>],然後按一下[確定]

    [建立新資料來源] 對話方塊隨即出現。

  4. 在步驟1中,輸入名稱來識別資料來源。

  5. 在步驟2中,針對您要用來做為資料來源的資料庫類型,按一下該驅動程式。

    附註: 

    • 如果與 Microsoft Query 一起安裝的 ODBC 驅動程式不支援您想要存取的外部資料庫,您必須從協力廠商廠商取得並安裝 Microsoft Office 相容的 ODBC 驅動程式,例如資料. 請與資料庫廠商聯繫,以取得安裝指示。

    • OLAP 資料庫不需要 ODBC 驅動程式。 當您安裝 Microsoft Query 時,系統會針對使用 Microsoft SQL Server Analysis Services 建立的資料庫安裝驅動程式。 若要連線至其他 OLAP 資料庫,您必須安裝資料來源驅動程式和用戶端軟體。

  6. 按一下[連線],然後提供連線至資料來源所需的資訊。 針對資料庫、Excel 活頁簿及文字檔,您所提供的資訊會根據您所選取的資料來源類型而定。 您可能會要求您提供登入名稱、密碼、您所使用的資料庫版本、資料庫位置,或特定于資料庫類型的其他資訊。

    重要: 

    • 請使用結合大小寫字母、數字和符號的強式密碼。 弱式密碼未結合這些元素。 強式密碼:Y6dh!et5。 弱式密碼:House27。 密碼的長度應該是 8 個字元以上。 使用 14 個字元以上的複雜密碼較佳。

    • 您必須記住密碼。 若忘記了密碼,Microsoft 亦無法擷取該密碼。 請將您寫下的密碼儲存在安全之處,不要將所保護的資訊存放在同一處。

  7. 輸入必要資訊後,請按一下[確定][完成],回到 [建立新資料來源] 對話方塊。

  8. 如果您的資料庫有資料表,而您想要在 [查詢] 嚮導中自動顯示特定的資料表,請按一下步驟4的方塊,然後按一下所要的表格。

  9. 如果您在使用資料來源時不想輸入登入名稱和密碼,請選取 [在資料來源定義中儲存我的使用者識別碼和密碼] 核取方塊。 儲存的密碼並未加密。 如果核取方塊無法使用,請向您的資料庫管理員確認此選項是否可供使用。

    安全性附註: 避免在連線至資料來源時儲存登入資訊。 此資訊可能會儲存為純文字,惡意使用者可能會存取訊號來損害資料來源的安全性。

完成這些步驟後,您的資料來源名稱就會出現在 [選擇資料來源] 對話方塊中。

針對大部分查詢使用 [查詢嚮導]     [查詢嚮導] 能讓您輕鬆地從資料庫中不同的資料表和欄位中選取及收集資料。 您可以使用 [查詢嚮導],選取您要包含的資料表和欄位。 內部聯接(指定兩個數據表中的列是根據相同的欄位值來組合),當嚮導辨識一個資料表中的主鍵欄位,以及第二個數據表中有相同名稱的欄位時,就會自動建立。

您也可以使用此嚮導來排序結果集,並進行簡單的篩選。 在嚮導的最後一個步驟中,您可以選擇將資料傳回 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 開啟已儲存的查詢:

  1. 在 [資料] 索引標籤上,按一下 [取得外部資料] 群組中的 [從其他來源],然後按一下 [從 Microsoft Query]。 [選擇資料來源] 對話方塊隨即顯示。

  2. 在 [選擇資料來源] 對話方塊中,按一下 [查詢] 索引標籤。

  3. 按兩下您要開啟之已儲存的查詢。 查詢會顯示在 Microsoft Query 中。

如果您想要開啟已儲存的查詢,而且 Microsoft Query 已開啟,請按一下 [Microsoft Query 檔案] 功能表,然後按一下 [開啟]。

如果您按兩下 .dqy 檔案,Excel 會開啟並執行查詢,然後將結果插入新的工作表中。

如果您想要共用的是以外部資料為基礎的 Excel 摘要或報表,您可以為其他使用者提供包含外部資料範圍的活頁簿,或者您可以建立 範本。 範本可讓您儲存摘要或報表,而不儲存外部資料,因此檔案較小。 當使用者開啟報表範本時,就會檢索外部資料。

在 [查詢嚮導] 或 [Microsoft Query] 中建立查詢之後,您可以將資料傳回 Excel 工作表。 然後,資料就會變成 外部資料範圍 或您可以格式化和重新整理的 樞紐分析表 。

格式化檢索的資料     在 Excel 中,您可以使用圖表或自動分類匯總等工具來呈現及摘要 Microsoft Query 所檢索的資料。 您可以設定資料格式,當您重新整理外部資料時,會保留您的格式設定。 您可以使用自己的欄標籤(而非功能變數名稱),並自動新增列號。

Excel 會自動為您在範圍結尾輸入的新資料設定格式,以符合前面的列。 Excel 也可以自動複製前面列中重複的公式,並將它們延伸至其他列。

附註: 為了延伸至範圍中的新列,格式及公式必須至少出現在前面五行中的三個列中。

您可以隨時開啟此選項(或再次關閉):

  1. 按一下 [檔案] > [選項] > [進階]。

    在 Excel 2007中:按一下 [ Microsoft Office 按鈕] Office 按鈕影像 ,按一下 [ Excel 選項],然後按一下 [高級] 類別。

  2. 在 [編輯選項] 區段中,選取 [延伸資料範圍格式及公式] 複選。 若要再次關閉自動資料範圍的格式設定,請清除此核取方塊。

<c0>重新整理外部資料</c0>。     當您重新整理外部資料時,您會執行查詢,以取得符合您規格的任何新的或變更的資料。 您可以在 Microsoft Query 和 Excel 中重新整理查詢。 Excel 提供數個重新整理查詢的選項,包括在您開啟活頁簿時重新整理資料,並以固定的時間間隔自動重新整理。 在重新整理資料時,您可以繼續在 Excel 中工作,您也可以在重新整理資料時檢查狀態。 如需詳細資訊,請參閱在 Excel 中重新整理外部資料連線。

頁面頂端

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×