您可以使用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 活頁簿,讓您可以在想要重新整理數據時重新連線至資料庫。
使用Microsoft查詢匯入數據 若要使用Microsoft查詢將外部數據匯入 Excel,請遵循下列基本步驟,下列各節將詳細說明這些步驟。
什麼是數據源? 數據源是一組儲存的資訊,可讓 Excel 和 Microsoft Query 連線到外部資料庫。 當您使用Microsoft查詢來設定數據源時,請為數據源命名,然後提供資料庫或伺服器的名稱和位置、資料庫類型,以及您的登入和密碼資訊。 這些資訊也包含 OBDC 驅動程式或數據源驅動程式的名稱,後者是建立特定資料庫類型連線的程式。
若要使用Microsoft查詢來設定數據源:
-
在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]。
附註: Excel 365 已將 Microsoft查詢 移至 [ 舊版精靈] 功能表群組。 此功能表預設不會顯示。 若要啟用,請移至 [顯示舊版數據匯入精靈] 區段中的 [檔案]、[選項]、[數據] 和 [啟用]。
-
執行下列其中一個動作:
-
若要指定資料庫、文本檔或 Excel 活頁簿的數據源,請按兩下 [ 資料庫] 索引標籤 。
-
若要指定 OLAP Cube 數據源,請按兩下 [OLAP Cube] 索引 標籤。 只有當您從 Excel 執行 [查詢] Microsoft時,才能使用此索引標籤。
-
-
按兩下 <[新增數據源]>。
-或-
按兩下 [<新數據源>],然後按兩下 [ 確定]。
隨即顯示 [ 建立新數據源 ] 對話框。
-
在步驟 1 中,輸入名稱以識別數據源。
-
在步驟 2 中,按下您要做為數據源之資料庫類型的驅動程式。
附註:
-
如果使用 Microsoft Query 安裝的 ODBC 驅動程式不支援您想要存取的外部資料庫,則您必須從第三方廠商取得並安裝與 Office 相容的 ODBC 驅動程式,例如資料庫製造商,以取得並安裝該驅動程式Microsoft。 請連絡資料庫廠商以取得安裝指示。
-
OLAP 資料庫不需要 ODBC 驅動程式。 當您安裝Microsoft查詢時,會針對使用 SQL Server Analysis Services Microsoft建立的資料庫安裝驅動程式。 若要連線至其他 OLAP 資料庫,您必須安裝資料源驅動程式和客戶端軟體。
-
-
按兩下 [ 連線],然後提供連線至數據源所需的資訊。 針對資料庫、Excel 活頁簿和文本檔,您提供的資訊取決於您所選取的數據源類型。 系統可能會要求您提供登入名稱、密碼、您正在使用的資料庫版本、資料庫位置,或資料庫類型的其他特定資訊。
重要:
-
請使用結合大小寫字母、數字和符號的強式密碼。 弱式密碼未結合這些元素。 強式密碼:Y6dh!et5。 弱式密碼:House27。 密碼的長度應該是 8 個字元以上。 使用 14 個字元以上的複雜密碼較佳。
-
您必須記住密碼。 若忘記了密碼,Microsoft 亦無法擷取該密碼。 請將您寫下的密碼儲存在安全之處,不要將所保護的資訊存放在同一處。
-
-
輸入必要資訊后,按兩下 [ 確定 ] 或 [ 完成 ] 傳回 [ 建立新數據源 ] 對話方塊。
-
如果您的資料庫有數據表,而您想要在 [查詢精靈] 中自動顯示特定數據表,請按兩下步驟 4 的方塊,然後按下您要的數據表。
-
如果您在使用數據源時不想輸入登入名稱和密碼,請選取 [在 數據源定義中儲存我的使用者識別碼和密碼 ] 複選框。 已儲存的密碼未加密。 如果無法使用此複選框,請參閱資料庫系統管理員以判斷是否可以使用此選項。
安全性附註: 線上至數據源時,請避免儲存登入資訊。 此資訊可能會儲存為純文本,惡意使用者可能會存取資訊以危害數據源的安全性。
完成這些步驟之後,數據源的名稱會出現在 [ 選擇數據源 ] 對話框中。
針對大多數查詢使用 [查詢精靈] [查詢精靈] 可讓您輕鬆地選取並彙集資料庫中不同數據表和字段的數據。 您可以使用 [查詢精靈] 選取要包含的數據表和欄位。 內部連接 (查詢作業,指定兩個數據表的數據列是根據相同的域值合併,) 會在精靈辨識一個數據表中的主鍵欄位,以及在第二個數據表中具有相同名稱的欄位時自動建立。
您也可以使用精靈來排序結果集,並執行簡單的篩選。 在精靈的最後一個步驟中,您可以選擇將數據傳回 Excel,或進一步調整Microsoft查詢中的查詢。 建立查詢之後,您可以在 Excel 或Microsoft查詢中執行查詢。
若要啟動查詢精靈,請執行下列步驟。
-
在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]。
-
在 [ 選擇數據源 ] 對話框中,確定已選取 [ 使用查詢精靈建立/編輯查詢] 複選框。
-
按兩下您要使用的數據來源。
-或-
按兩下您要使用的數據源,然後按下 [ 確定]。
直接在Microsoft查詢中處理其他類型的查詢 如果您想要建立比 [查詢精靈] 允許更複雜的查詢,您可以直接在Microsoft查詢中工作。 您可以使用 [Microsoft查詢] 來檢視及變更您在 [查詢精靈] 中開始建立的查詢,或者您可以建立新查詢而不使用精靈。 當您要建立執行下列動作的查詢時,請直接在Microsoft查詢中工作:
-
從欄位選取特定數據 在大型資料庫中,您可能會想要選擇字段中的一些數據,並省略不需要的數據。 例如,如果您需要包含許多產品資訊欄位中兩個產品的數據,您可以使用 準則 僅針對您要的兩個產品選取數據。
-
每次執行查詢時,根據不同的準則擷取數據 如果您需要針對相同外部數據中的多個區域建立相同的 Excel 報表或摘要,例如每個地區的個別銷售報表,您可以建立 參數查詢。 當您執行參數查詢時,系統會提示您在查詢選取記錄時使用值做為準則。 例如,參數查詢可能會提示您輸入特定地區,而您可以重複使用此查詢來建立每個地區銷售報表。
-
以不同方式聯結數據 查詢精靈建立的內部聯結是用於建立查詢的最常見聯結類型。 不過,有時候您會想要使用不同類型的聯結。 例如,如果您有產品銷售資訊數據表和客戶資訊數據表,則由查詢精靈建立的類型 (內部聯結) 會防止尚未購買的客戶擷取客戶記錄。 使用Microsoft查詢,您可以加入這些數據表,以便擷取所有客戶記錄,以及購買客戶的銷售數據。
若要開始Microsoft查詢,請執行下列步驟。
-
在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]。
-
在 [ 選擇數據源 ] 對話框中,確定已清除 [ 使用查詢精靈建立/編輯查詢] 複選框。
-
按兩下您要使用的數據來源。
-或-
按兩下您要使用的數據源,然後按下 [ 確定]。
重複使用和共享查詢 在 [查詢精靈] 和 [Microsoft查詢] 中,您可以將查詢儲存為 .dqy 檔案,以便修改、重複使用及共用。 Excel 可以直接開啟 .dqy 檔案,讓您或其他使用者從相同的查詢建立其他外部數據範圍。
若要從 Excel 開啟儲存的查詢:
-
在 [ 數據] 索 引標籤的 [ 取得外部數據 ] 群組中,按兩下 [ 從其他來源],然後按兩下 [ 從Microsoft查詢]。 隨即顯示 [ 選擇數據源 ] 對話框。
-
在 [ 選擇數據源 ] 對話框中,按兩下 [ 查詢] 索引標籤 。
-
按兩下您要開啟的已儲存查詢。 查詢會顯示在Microsoft查詢中。
如果您想要開啟已儲存的查詢,Microsoft查詢已開啟,請按兩下 [Microsoft查詢 檔案] 選單,然後按下 [ 開啟]。
如果您按兩下 .dqy 檔案,Excel 會開啟、執行查詢,然後將結果插入新的工作表。
如果您想要共用以外部數據為基礎的 Excel 摘要或報表,您可以為其他使用者提供包含外部數據範圍的活頁簿,或建立 範本。 範本可讓您儲存摘要或報表,而不需要儲存外部數據,讓檔案變小。 當用戶開啟報表範本時,會擷取外部數據。
在 [查詢精靈] 或 [Microsoft查詢] 中建立查詢之後,您可以將數據傳回 Excel 工作表。 數據隨即變成可設定格式並重新整理的 外部資料範圍 或 樞紐分析表 。
格式設定擷取的數據 在 Excel 中,您可以使用圖表或自動小計等工具來呈現及摘要Microsoft查詢所擷取的數據。 您可以設定數據的格式,並保留重新整理外部資料時的格式設定。 您可以使用自己的欄卷標,而非功能變數名稱,並自動新增列號。
Excel 可以自動格式化您在範圍結尾輸入的新數據,以符合前一列。 Excel 也可以自動複製前一列重複的公式,並將它們延伸至其他列。
附註: 若要延伸至範圍中的新列,格式和公式必須至少出現在前五列的三列中。
您可以隨時 (或再次) 開啟此選項:
-
按一下 [檔案] > [選項] > [進階]。
-
在 [ 編輯選項] 區段中,選取 [ 延伸數據範圍格式和公式] 檢查。 若要再次關閉自動數據範圍格式設定,請清除此複選框。
<c0>重新整理外部資料</c0>。 當您重新整理外部資料時,您會執行查詢以擷取符合您規格的任何新資料或已變更的數據。 您可以在 [查詢] 和 [Excel] 中重新整理Microsoft查詢。 Excel 提供數個重新整理查詢的選項,包括每當您開啟活頁簿時重新整理數據,並會在時間間隔自動重新整理。 您可以在重新整理數據時繼續在 Excel 中工作,也可以在重新整理數據時檢查狀態。 如需詳細資訊,請參閱 在 Excel 中重新整理外部數據連線。