資料如何透過 Excel 傳輸

套用到
Microsoft 365 Excel

如果資料一直在旅程中，那麼 Excel 就像中央車站一樣。 假設資料是一列滿載乘客的火車，會定期進入 Excel、進行變更，然後離開。 有數十種方式可以進入 Excel，因為它會匯入所有類型的資料，且清單會持續增加。 資料一旦在 Excel 中，就可以使用 Power Query 以您想要的方式變更圖形。 數據和我們所有人一樣，也需要“照顧和餵養”才能讓事情順利進行。 這就是連線、查詢和資料屬性派上用場的地方。 最後，資料會以許多方式離開 Excel 火車站：由其他資料來源匯入、以報表、圖表和樞紐分析表的形式共用，以及匯出至 Power BI 和 Power Apps。  

Excel 的概述是輸入、處理和輸出數據

您可以在 Excel 火車站中使用資料執行的主要動作

以下是資料在 Excel 火車站中時您可以執行的主要動作：

下列各節將提供這個繁忙的 Excel 火車站幕後運作的更多詳細資料。

連線和屬性摘要

有連線、查詢和外部資料範圍屬性。 連線和查詢屬性都包含傳統的連線資訊。 在對話方塊標題中， [連線屬性] 表示沒有與其相關聯的查詢，但 [查詢屬性 ] 表示有。 外部資料範圍屬性可控制資料的版面配置和格式。 所有資料來源都有 [外部資料屬性 ] 對話方塊，但具有相關聯認證和重新整理資訊的資料來源會使用較大的 [外部範圍資料屬性 ] 對話方塊。

下列資訊摘要說明最重要的對話方塊、窗格、命令路徑，以及對應的說明主題。

對話方塊或窗格
命令路徑		 索引標籤和通道 主要說明主題
最近的來源
資料>最近的來源		 (沒有索引標籤)
[連接>通道] 對話方塊 [導覽員] 對話方塊		 管理資料來源設定和權限
連線內容
或者
資料連線精靈
資料>查詢 & 連線>[連線] 索引標籤 > (以滑鼠右鍵按一下連線) > [內容]		 [使用狀況 ] 索引標籤
[定義] 索引標籤
用於索引標籤		 連線屬性
查詢屬性
資料>現有連線> (以滑鼠右鍵按一下連線) > [編輯連線屬性]
或者
資料>查詢 & 連線|[查詢] 索引標籤 > (以滑鼠右鍵按一下連線) > [內容]
或者
查詢>[內容]
或者
資料>全部>重新整理當連線放置在載入的查詢工作表上時 ()		 [使用狀況 ] 索引標籤
[定義] 索引標籤
用於索引標籤		 連線屬性
查詢 & 連線
資料>查詢 & 連線		 [查詢] 索引標籤
[連線] 索引標籤		 連線屬性
現有連線
資料>現有連線		 [連線] 索引標籤
[表格 ] 索引標籤		 連線至外部資料
外部資料屬性
或者
外部資料範圍屬性
或者
資料>如果不在查詢工作表上，則 (停用屬性)
 用於[連線屬性 ] 對話方塊的索引標籤 ()

右側的 [重新整理] 按鈕 通道到查詢屬性		 管理外部資料範圍及其內容
連線屬性>[定義] 索引標籤 >匯出連線檔案
或者
查詢>匯出連線檔案		 (沒有索引標籤)
通道到
[檔案 ] 對話方塊
資料來源 資料夾		 建立、編輯及管理外部資料的連線

資料連線的基本概念

Excel 活頁簿中的資料可能來自兩個不同的位置。 資料可以直接儲存在活頁簿中，也可以儲存在外部資料來源，例如文字檔、資料庫或線上分析處理 (OLAP) Cube。 此外部資料來源會透過資料連線連線到活頁簿，資料連線是描述如何尋找、登入及存取外部資料來源的一組資訊。

連接到外部資料的主要好處是您可以定期分析這些數據，而無需重複將資料複製到工作簿，這是一項非常耗時且容易出錯的操作。 連線到外部資料之後，您也可以在資料來源更新為新資訊時，自動重新整理 (或從原始資料來源更新) Excel 活頁簿。

線上資訊會儲存在活頁簿中，也可以儲存在連線檔案中，例如 Office 資料連線 (ODC) 檔案 (.odc) 或資料來源名稱檔案 (.dsn) 。

若要將外部資料匯入 Excel，您必須有資料的存取權。 如果您要存取的外部資料來源不在本機電腦上，您可能需要連絡資料庫系統管理員以取得密碼、使用者權限或其他連線資訊。 如果資料來源是資料庫，請確定資料庫不是在獨佔模式下開啟的。 如果資料來源是文字檔或試算表，請確定其他使用者未將其開啟為獨佔存取。

許多資料來源也需要 ODBC 驅動程式或 OLE DB 提供者來協調 Excel、連線檔案與資料來源之間的資料流程。

連接至外部資料來源  

下圖摘要說明資料連線的要點。

1. 您可以連線到各種資料來源：Analysis Services、SQL Server、Microsoft Access、其他 OLAP 和關聯式資料庫、試算表和文字檔。

2. 許多資料來源都有相關聯的 ODBC 驅動程式或 OLE DB 提供者。

3. 連線檔案會定義存取資料來源並擷取資料所需的所有資訊。

4. 連線資訊會從連線檔案複製到活頁簿中，並可以輕鬆編輯連線資訊。

5. 資料會複製到活頁簿中，以便您可以像使用直接儲存在活頁簿中的資料一樣使用它。

尋找連線

若要尋找連線檔案，請使用 [現有連線] 對話方塊。 (選取 [現有連線資料>]。) 使用此對話方塊，您可以看到下列連線類型：

  • 活頁簿中的連線 
    此清單會顯示活頁簿中所有目前的連線。 此清單會從您已定義的連線、使用 [資料連線精靈] 的 [ 選取資料來源 ] 對話方塊建立的連線，或從您先前在此對話方塊選取做為連線的連線建立。
  • 電腦上的連線檔案 
    此清單是從通常儲存在 [文件] 資料夾中的 [我的資料來源] 資料夾所建立。
  • 網路上的連線檔案 
    您可以從區域網路上的一組資料夾建立清單，其位置可以作為部署 Microsoft Office 群組原則或 SharePoint 文件庫的一部分透過網路部署。 

編輯連線屬性

您也可以使用 Excel 做為連線檔案編輯器，以建立和編輯儲存在活頁簿或連線檔案中的外部資料來源的連線。 如果找不到您想要的連線，您可以按一下 [瀏覽 更多] 以顯示 [ 選取資料來源 ] 對話方塊，然後按一下 [新增來源 ] 以啟動 [資料連線精靈] 來建立連線。

建立連線之後，您可以使用 [連線內容] 對話方塊 (選取 [資料>查詢] & [Connections> 連線] 索引標籤>， (以滑鼠右鍵按一下連線) > [內容]) ，以控制外部資料來源連線的各種設定，以及使用、重複使用或切換連線檔案。

備註當有先前稱為 [取得轉換] 的查詢與其相關聯Power Query (建立時，[連線屬性] 對話方塊有時會命名為 [查詢屬性] 對話方塊 &) 。

如果您使用連線檔案連線至資料來源，Excel 會將連線檔案中的連線資訊複製到 Excel 活頁簿中。 當您使用 [連線屬性] 對話方塊進行變更時，您編輯的是儲存在目前 Excel 活頁簿中的資料連線資訊，而不是可能已用於建立連線的原始資料連線檔案，該檔案名稱顯示為 [定義] 索引標籤) 的 [連線檔案] 屬性所顯示的連線 (。 編輯連線資訊 (之後，除了 [連線名稱 ] 和 [ 連線描述 ] 屬性) 之外，連線檔案的連結會遭到移除，並清除 [ 連線檔案] 屬性。

若要確保在重新整理資料來源時一律使用連線檔案，請按一下 [定義] 索引標籤上的 [一律嘗試使用此檔案來重新整理此資料]。選取此核取方塊可確保使用該連線檔案的所有活頁簿一律使用連線檔案的更新，該活頁簿也必須設定此屬性。

管理連線

您可以使用 [連線] 對話方塊輕鬆管理這些連線，包括建立、編輯和刪除連線， (選取 [資料>查詢] & [Connections> 連線] 索引標籤 > (以滑鼠右鍵按一下連線) >[內容]。) 您可以使用此對話方塊執行下列動作：

  • 建立、編輯、重新整理及刪除活頁簿中使用的連線。
  • 確認外部資料來源。 如果連線是由其他使用者定義，您可能需要執行此操作。
  • 顯示每個連線在目前活頁簿中的使用位置。
  • 診斷有關外部資料連線的錯誤訊息。
  • 將連線重新導向至不同的伺服器或資料來源，或取代現有連線的連線檔案。
  • 讓您輕鬆建立並與使用者共用連線檔案。

在檔案中共用 ODC 和查詢連線

連線檔案對於一致地共享連線、使連線更容易被發現、有助於提高連線的安全性以及促進資料來源管理特別有用。 共用連線檔案的最好方式是將其放在安全且受信任的位置，例如網路資料夾或 SharePoint 文件庫，使用者可以讀取檔案，但只有指定的使用者可以修改檔案。 如需詳細資訊，請參閱 與 ODC 共用資料。

使用 ODC 檔案

您可以透過 [ 選取資料來源 ] 對話方塊連線到外部資料，或使用 [資料連線精靈] 連線到新資料來源，以建立 Office 資料連線 (ODC) 檔案 (.odc) 。 ODC 檔案使用自訂 HTML 和 XML 標籤來儲存連線資訊。 您可以在 Excel 中輕鬆檢視或編輯檔案內容。

您可以與其他人共用連線檔案，讓他們擁有與您對外部資料來源相同的存取權。 其他使用者不需要設定資料來源即可開啟連線檔案，但可能需要安裝 ODBC 驅動程式或 OLE DB 提供者，才能存取其電腦上的外部資料。

ODC 檔案是連線至資料及共用資料的建議方法。 您可以開啟連線檔案，然後按一下 [連線屬性] 對話方塊 [定義] 索引標籤上的 [匯出連線檔案] 按鈕， (輕鬆地將其他傳統連線檔案) DSN、UDL 和查詢檔案轉換為 ODC 檔案。

使用查詢檔案

查詢檔案是包含資料來源資訊的文字檔，包括資料所在的伺服器名稱，以及您在建立資料來源時所提供的連線資訊。 查詢檔案是與其他 Excel 使用者共用查詢的傳統方式。

使用 .dqy 查詢檔案 您可以使用 Microsoft Query 來儲存 .dqy 檔案，其中包含對關聯式資料庫或文字檔資料的查詢。 當您在 Microsoft Query 中開啟這些檔案時，您可以檢視查詢傳回的資料，並修改查詢以擷取不同的結果。 您可以使用查詢精靈或直接在 Microsoft Query 中，為您建立的任何查詢儲存 .dqy 檔案。

使用 .oqy 查詢檔案 您可以儲存 .oqy 檔案，以連線至 OLAP 資料庫中的資料，不論該資料庫位於伺服器上，或是離線 Cube 檔案 (.cub) 。 當您使用 Microsoft Query 中的多維度連線精靈建立 OLAP 資料庫或 Cube 的資料來源時，會自動建立 .oqy 檔案。 由於 OLAP 資料庫不是以記錄或資料表形式組織，因此您無法建立查詢或 .dqy 檔案來存取這些資料庫。

使用 .rqy 查詢檔案 Excel 可以開啟 .rqy 格式的查詢檔案，以支援使用此格式的 OLE DB 資料來源驅動程式。 如需詳細資訊，請參閱驅動程式文件。

使用 .qry 查詢檔案 Microsoft Query 可以開啟並儲存 .qry 格式的查詢檔案，以用於無法開啟 .dqy 檔案的舊版 Microsoft Query。 如果您有想在 Excel 中使用的 .qry 格式查詢檔案，請在 Microsoft Query 中開啟檔案，然後將其儲存為 .dqy 檔案。 如需儲存 .dqy 檔案的資訊，請參閱 Microsoft 查詢說明。

使用 .iqy Web 查詢檔案 Excel 可以開啟 .iqy Web 查詢檔案，以從 Web 擷取資料。 如需詳細資訊，請參閱 從 SharePoint 匯出至 Excel。

使用外部資料屬性

外部資料範圍 (也稱為查詢資料表) 是定義的名稱或資料表名稱，定義帶入工作表之資料的位置。 當您連接到外部資料時，Excel 會自動建立外部資料範圍。 唯一的例外是連線至資料來源的樞紐分析表，它不會建立外部資料範圍。 在 Excel 中，您可以設定外部資料範圍的格式並配置，或在計算中使用它，就像處理任何其他資料一樣。

Excel 會自動命名外部資料範圍，如下所示：

  • Office 資料連線 (ODC) 檔案的外部資料範圍會提供與檔案名稱相同的名稱。
  • 資料庫的外部資料範圍會以查詢的名稱命名。 根據預設，Query_from_source 是您用來建立查詢的資料來源名稱。
  • 文字檔的外部資料範圍會以文字檔名稱命名。
  • Web 查詢的外部資料範圍會以擷取其資料的網頁名稱命名。

如果您的工作表有來自相同來源的多個外部資料範圍，則範圍會加上編號。 例如 MyText、MyText_1、MyText_2 等等。

外部資料範圍還有其他屬性， (請勿與可用來控制資料的連線屬性) 混淆，例如保留儲存格格式設定和欄寬。 您可以按一下 [資料] 索引標籤上 [連線] 群組中的 [內容]，然後在 [外部資料範圍屬性] 或 [外部資料屬性] 對話方塊中進行變更，以變更這些外部資料範圍屬性。

[外部資料範圍屬性] 對話方塊範例 [外部範圍屬性] 對話方塊範例

Excel Services 中的資料來源支援

您可以使用外部資料範圍和樞紐分析表) 等數個資料物件 (來連線至不同的資料來源。 不過，每個資料物件之間您可以連線的資料來源類型不同。

您可以在 Excel Services 中使用並重新整理連線的資料。 與任何外部資料來源一樣，您可能需要驗證您的存取權。 如需詳細資訊，請參閱 在 Excel 中重新整理外部資料連線。F或有關認證的詳細資訊，請參閱 Excel Services 驗證設定。

下表摘要說明 Excel 中的每個資料物件支援哪些資料來源。

Excel
資料
物件		 建立
External
資料
範圍？		   OLE
DB		 ODBC 文字
檔案		 HTML
檔案		 XML
檔案		 SharePoint
清單
匯入文字精靈 是 否 否 是 否 否 否
樞紐分析表
(非 OLAP)		 否 是 是 是 否 否 是
樞紐分析表
(OLAP)		 否 是 否 否 否 否 否
Excel 表格 是 是 是 否 否 是 是
XML 對應 是 否 否 否 否 是 否
Web 查詢 是 否 否 否 是 是 否
資料連線精靈 是 是 是 是 是 是 是
Microsoft 查詢 是 否 是 是 否 否 否

注意

這些檔案，包括使用 [匯入文字精靈] 匯入的文字檔、使用 XML 對應匯入的 XML 檔案，以及使用 Web 查詢匯入的 HTML 或 XML 檔案，不會使用 ODBC 驅動程式或 OLE DB 提供者來連線至資料來源。

Excel 表格和具名範圍的 Excel Services 因應措施

如果您想要在 Excel Services 中顯示 Excel 活頁簿，您可以連線並重新整理資料，但必須使用樞紐分析表。 Excel Services 不支援外部資料範圍，這表示 Excel Services 不支援連線到資料來源、Web 查詢、XML 對應或 Microsoft 查詢的 Excel 表格。

不過，您可以使用樞紐分析表連線到資料來源，然後將樞紐分析表設計並版面配置為沒有層級、群組或小計的 2 維表格，以便顯示所有想要的列和欄值，以解決此限制。 

ODBC 和 OLE DB 資料存取元件

讓我們回顧一下資料庫記憶體。

關於 MDAC、OLE DB 和 OBC

首先，對於所有首字母縮略詞表示歉意。 Microsoft Data Access Components (MDAC) 2.8 隨附於 Microsoft Windows。 使用 MDAC，您可以連線到並使用來自各種關聯式和非關聯式資料來源的資料。 您可以使用開放式資料庫連接 (ODBC) 驅動程式或 OLE DB 提供者來連線到許多不同的資料來源，這可能是由 Microsoft 建置和提供，或是由各種協力廠商開發。 安裝 Microsoft Office 時，系統會在您的電腦中新增其他 ODBC 驅動程式和 OLE DB 提供者。

若要查看電腦上安裝的 OLE DB 提供者完整清單，請從資料連結檔案顯示 [ 資料連結屬性 ] 對話方塊，然後按一下 [ 提供者] 索引標籤。

若要查看電腦上安裝的 ODBC 提供者完整清單，請顯示 [ODBC 資料庫系統管理員 ] 對話方塊，然後按一下 [驅動程式] 索引標籤。

您也可以使用其他製造商的 ODBC 驅動程式和 OLE DB 提供者，從 Microsoft 資料來源以外的來源取得資訊，包括其他類型的 ODBC 和 OLE DB 資料庫。 如需安裝這些 ODBC 驅動程式或 OLE DB 提供者的相關資訊，請查看資料庫的說明文件，或洽詢您的資料庫廠商。

使用 ODBC 連線至資料來源

在 ODBC 架構中，應用程式 (（例如 Excel) ） 會連接到 ODBC 驅動程式管理員，再由其使用特定的 ODBC 驅動程式 (，例如 Microsoft SQL ODBC 驅動程式) 來連線到資料來源 (例如 Microsoft SQL Server 資料庫) 。

若要連線至 ODBC 資料來源，請執行下列動作：

  1. 確定在包含該資料來源的電腦上已安裝適當的 ODBC 驅動程式。
  2. 使用 ODBC 資料來源系統管理員 將連線資訊儲存在登錄或 DSN 檔案中，或使用 Visual Basic 程式碼中的連線字串 (定義 DSN) Microsoft名稱，以將連線資訊直接傳遞至 ODBC 驅動程式管理員。
    若要定義資料來源，請在 Windows 中按一下 [開始] 按鈕，然後按一下 [控制台]。 按一下 [ 系統及維護]，然後按一下 [系統管理工具]。 按一下 [效能與維護]，按一下 [系統管理工具]。 然後按一下 ODBC ([資料來源]) 。 如需不同選項的詳細資訊，請按一下每個對話方塊中的 [ 說明 ] 按鈕。

機器資料來源

機器資料來源會使用使用者定義的名稱，將連線資訊儲存在特定電腦的登錄中。 您只能在定義該資訊的電腦上使用該機器資料來源。 機器資料來源分為使用者和系統兩種。 使用者資料來源僅可由目前的使用者使用，並且只有該使用者看得到。 系統資料來源可以由電腦上的所有使用者使用，且電腦上的所有使用者都可以看見。

當您想要提供額外的安全性時，機器資料來源特別有用，因為它有助於確保只有已登入的使用者才能檢視機器資料來源，而且遠端使用者無法將機器資料來源複製到另一部電腦。

檔案資料來源

檔案資料來源 (也稱為 DSN 檔案，) 將連線資訊儲存在文字檔案而非登錄中，使用起來會比機器資料來源更有彈性。 例如，您可以將檔案資料來源複製到任何具有正確 ODBC 驅動程式的電腦，讓您的應用程式在它使用的所有電腦上都能依賴一致且正確的連線資訊。 或者，您可以將檔案資料來源置於單一伺服器，然後在網路上的多部電腦間共用，就能輕易地在單一位置維護連線資訊。

檔案資料來源也可以是不可共用的。 不可共用的檔案資料來源位於單一電腦上，並指向機器資料來源。 您可以使用不可共用的檔案資料來源，以從其中存取現有的機器資料來源。

使用 OLE DB 連線至資料來源

在 OLE DB 架構中，存取資料的應用程式稱為資料取用者 (，例如 Excel) ，而允許原生存取資料的程式稱為資料庫提供者 (，例如 Microsoft OLE DB Provider for SQL Server) 。

通用資料連結檔案 (.udl) 包含資料取用者透過資料來源的 OLE DB 提供者存取資料來源的連線資訊。 您可以執行下列其中一個動作來建立連線資訊：

  • 在 [資料連線精靈] 中，使用 [資料連結屬性 ] 對話方塊來定義 OLE DB 提供者的資料連結。 
  • 建立副檔名為 .udl 的空白文字檔案，然後編輯檔案，其中會顯示 [資料連結屬性 ] 對話方塊。

另請參閱

適用於 Excel 的 Power Query 說明