Applies ToExcel for Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

您可以使用Microsoft查詢從外部來源擷取數據。 使用Microsoft查詢從公司資料庫和檔案擷取數據,就不需要在Excel中重新輸入要分析的數據。 每當資料庫更新為新資訊時,您也可以自動從原始源資料庫重新整理 Excel 報表和摘要。

使用Microsoft查詢,您可以連線至外部數據源、從這些外部來源選取數據、將該數據匯入工作表,並視需要重新整理數據,讓工作表數據與外部來源中的數據保持同步。

您可以存取的資料庫類型     您可以從數種資料庫類型擷取數據,包括 Microsoft Office Access、Microsoft SQL Server,以及 Microsoft SQL Server OLAP Services。 您也可以從 Excel 活頁簿和文字檔擷取數據。

Microsoft Office 提供的驅動程式可讓您從下列數據源擷取數據:

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

  • Microsoft Office Access

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • 悖論

  • 文本文件資料庫

您也可以使用其他製造商提供的 ODBC 驅動程式或數據源驅動程式,從此處未列出的數據源擷取資訊,包括其他類型的 OLAP 資料庫。 如需安裝此處未列出之 ODBC 驅動程式或數據源驅動程式的相關信息,請參閱資料庫的檔,或連絡您的資料庫廠商。

從資料庫選取數據     您可以建立查詢來擷取資料庫中的數據,這是關於外部資料庫中儲存數據的問題。 例如,如果您的數據儲存在 Access 資料庫中,您可能會想知道特定產品的銷售數位。 您可以只選取要分析之產品和區域的數據,來擷取部分數據。

使用Microsoft查詢],您可以選取所要的數據域,並僅將該數據匯入至 Excel。

在一次作業中更新工作表     Excel 活頁簿中有外部數據后,每當資料庫變更時,您就可以 重新整理 數據以更新分析,而不需要重新建立摘要報表和圖表。 例如,您可以建立每月銷售摘要,並在新的銷售數字出現時每月重新整理。

Microsoft查詢如何使用數據源     設定特定資料庫的數據源之後,每當您想要建立查詢以從該資料庫選取和擷取數據時,就可以使用該數據源,而不需要重新輸入所有連線資訊。 Microsoft查詢會使用數據源連線到外部資料庫,並顯示可用的數據。 建立查詢並將數據傳回 Excel 後,Microsoft查詢會提供同時提供查詢和數據源資訊的 Excel 活頁簿,讓您可以在想要重新整理數據時重新連線至資料庫。

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

使用Microsoft查詢匯入數據     若要使用Microsoft查詢將外部數據匯入 Excel,請遵循下列基本步驟,下列各節將詳細說明這些步驟。

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

若要使用Microsoft查詢來設定數據源:

  1. 在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]

    附註: Excel 365 已將 Microsoft查詢 移至 [ 舊版精靈] 功能表群組。  此功能表預設不會顯示。  若要啟用,請移至 [顯示舊版數據匯入精靈] 區段中的 [檔案]、[選項]、[數據] 和 [啟用]。

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

    • 若要指定資料庫、文本檔或 Excel 活頁簿的數據源,請按兩下 [ 資料庫] 索引標籤

    • 若要指定 OLAP Cube 數據源,請按兩下 [OLAP Cube] 索引 標籤。 只有當您從 Excel 執行 [查詢] Microsoft時,才能使用此索引標籤。

  3. 按兩下 <[新增數據源]>

    -或-

    按兩下 [<新數據源>],然後按兩下 [ 確定]

    隨即顯示 [ 建立新數據源 ] 對話框。

  4. 在步驟 1 中,輸入名稱以識別數據源。

  5. 在步驟 2 中,按下您要做為數據源之資料庫類型的驅動程式。

    附註: 

    • 如果使用 Microsoft Query 安裝的 ODBC 驅動程式不支援您想要存取的外部資料庫,則您必須從第三方廠商取得並安裝與 Office 相容的 ODBC 驅動程式,例如資料庫製造商,以取得並安裝該驅動程式Microsoft。 請連絡資料庫廠商以取得安裝指示。

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

  6. 按兩下 [ 連線],然後提供連線至數據源所需的資訊。 針對資料庫、Excel 活頁簿和文本檔,您提供的資訊取決於您所選取的數據源類型。 系統可能會要求您提供登入名稱、密碼、您正在使用的資料庫版本、資料庫位置,或資料庫類型的其他特定資訊。

    重要: 

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

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

  7. 輸入必要資訊后,按兩下 [ 確定 ] 或 [ 完成 ] 傳回 [ 建立新數據源 ] 對話方塊。

  8. 如果您的資料庫有數據表,而您想要在 [查詢精靈] 中自動顯示特定數據表,請按兩下步驟 4 的方塊,然後按下您要的數據表。

  9. 如果您在使用數據源時不想輸入登入名稱和密碼,請選取 [在 數據源定義中儲存我的使用者識別碼和密碼 ] 複選框。 已儲存的密碼未加密。 如果無法使用此複選框,請參閱資料庫系統管理員以判斷是否可以使用此選項。

    安全性附註: 線上至數據源時,請避免儲存登入資訊。 此資訊可能會儲存為純文本,惡意使用者可能會存取資訊以危害數據源的安全性。

完成這些步驟之後,數據源的名稱會出現在 [ 選擇數據源 ] 對話框中。

針對大多數查詢使用 [查詢精靈]     [查詢精靈] 可讓您輕鬆地選取並彙集資料庫中不同數據表和字段的數據。 您可以使用 [查詢精靈] 選取要包含的數據表和欄位。 內部連接 (查詢作業,指定兩個數據表的數據列是根據相同的域值合併,) 會在精靈辨識一個數據表中的主鍵欄位,以及在第二個數據表中具有相同名稱的欄位時自動建立。

您也可以使用精靈來排序結果集,並執行簡單的篩選。 在精靈的最後一個步驟中,您可以選擇將數據傳回 Excel,或進一步調整Microsoft查詢中的查詢。 建立查詢之後,您可以在 Excel 或Microsoft查詢中執行查詢。

若要啟動查詢精靈,請執行下列步驟。

  1. 在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]

  2. 在 [ 選擇數據源 ] 對話框中,確定已選取 [ 使用查詢精靈建立/編輯查詢] 複選框。

  3. 按兩下您要使用的數據來源。

    -或-

    按兩下您要使用的數據源,然後按下 [ 確定]

直接在Microsoft查詢中處理其他類型的查詢     如果您想要建立比 [查詢精靈] 允許更複雜的查詢,您可以直接在Microsoft查詢中工作。 您可以使用 [Microsoft查詢] 來檢視及變更您在 [查詢精靈] 中開始建立的查詢,或者您可以建立新查詢而不使用精靈。 當您要建立執行下列動作的查詢時,請直接在Microsoft查詢中工作:

  • 從欄位選取特定數據     在大型資料庫中,您可能會想要選擇字段中的一些數據,並省略不需要的數據。 例如,如果您需要包含許多產品資訊欄位中兩個產品的數據,您可以使用 準則 僅針對您要的兩個產品選取數據。

  • 每次執行查詢時,根據不同的準則擷取數據     如果您需要針對相同外部數據中的多個區域建立相同的 Excel 報表或摘要,例如每個地區的個別銷售報表,您可以建立 參數查詢。 當您執行參數查詢時,系統會提示您在查詢選取記錄時使用值做為準則。 例如,參數查詢可能會提示您輸入特定地區,而您可以重複使用此查詢來建立每個地區銷售報表。

  • 以不同方式聯結數據     查詢精靈建立的內部聯結是用於建立查詢的最常見聯結類型。 不過,有時候您會想要使用不同類型的聯結。 例如,如果您有產品銷售資訊數據表和客戶資訊數據表,則由查詢精靈建立的類型 (內部聯結) 會防止尚未購買的客戶擷取客戶記錄。 使用Microsoft查詢,您可以加入這些數據表,以便擷取所有客戶記錄,以及購買客戶的銷售數據。

若要開始Microsoft查詢,請執行下列步驟。

  1. 在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]

  2. 在 [ 選擇數據源 ] 對話框中,確定已清除 [ 使用查詢精靈建立/編輯查詢] 複選框。

  3. 按兩下您要使用的數據來源。

    -或-

    按兩下您要使用的數據源,然後按下 [ 確定]

重複使用和共享查詢     在 [查詢精靈] 和 [Microsoft查詢] 中,您可以將查詢儲存為 .dqy 檔案,以便修改、重複使用及共用。 Excel 可以直接開啟 .dqy 檔案,讓您或其他使用者從相同的查詢建立其他外部數據範圍。

若要從 Excel 開啟儲存的查詢:

  1. 在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]。 隨即顯示 [ 選擇數據源 ] 對話框。

  2. 在 [ 選擇數據源 ] 對話框中,按兩下 [ 查詢] 索引標籤

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

如果您想要開啟已儲存的查詢,Microsoft查詢已開啟,請按兩下 [Microsoft查詢 檔案] 選單,然後按下 [ 開啟]

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

如果您想要共用以外部數據為基礎的 Excel 摘要或報表,您可以為其他使用者提供包含外部數據範圍的活頁簿,或建立 範本。 範本可讓您儲存摘要或報表,而不需要儲存外部數據,讓檔案變小。 當用戶開啟報表範本時,會擷取外部數據。

在 [查詢精靈] 或 [Microsoft查詢] 中建立查詢之後,您可以將數據傳回 Excel 工作表。 數據隨即變成可設定格式並重新整理的 外部資料範圍 或 樞紐分析表 。

格式設定擷取的數據     在 Excel 中,您可以使用圖表或自動小計等工具來呈現及摘要Microsoft查詢所擷取的數據。 您可以設定數據的格式,並保留重新整理外部資料時的格式設定。 您可以使用自己的欄卷標,而非功能變數名稱,並自動新增列號。

Excel 可以自動格式化您在範圍結尾輸入的新數據,以符合前一列。 Excel 也可以自動複製前一列重複的公式,並將它們延伸至其他列。

附註: 若要延伸至範圍中的新列,格式和公式必須至少出現在前五列的三列中。

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

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

  2. 在 [ 編輯選項] 區段中,選取 [ 延伸數據範圍格式和公式] 檢查。 若要再次關閉自動數據範圍格式設定,請清除此複選框。

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

頁面頂端

Need more help?

Want more options?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。