如果資料總是在旅途中，那麼 Excel 就像中央車站一樣。 想像資料是一列載滿乘客的火車，定期進入 Excel，做修改後離開。 有數十種輸入方式，Excel 匯入各種類型的資料，且清單不斷增加。 資料一旦進入 Excel，就可以用 Power Query 依你想要的方式改變形狀。 數據，和我們所有人一樣，也需要「照顧與餵養」來維持運作順暢。 這時連線、查詢和資料屬性就派上用場了。 最後，資料會以多種方式離開 Excel 火車站：由其他資料來源匯入、以報告、圖表和樞紐分析表形式分享，並匯出到 Power BI 和 Power Apps。
你可以在 Excel 火車站中處理資料的主要功能
以下是資料在 Excel 火車站時可以做的主要事項：
- 進口 你可以從許多不同的外部資料來源匯入資料。 這些資料來源可以存在於你的電腦上、雲端，或是地球另一端。 如需詳細資訊，請參閱從外部資料來源匯入資料。
- Power Query 你可以使用Power Query (之前稱為 Get & Transform) 來建立查詢，以多種方式塑造、轉換和結合資料。 你可以將作品匯出成 Power Query 範本，在 Power Apps 中定義資料流操作。 你甚至可以建立一個資料型別來補充 連結型態。 如需詳細資訊，請參閱 [適用於 Excel 的 Power Query 說明]。
- 安全性 資料隱私、憑證與認證始終是持續關注的議題。 欲了解更多資訊，請參閱 管理資料來源設定與權限 及 設定隱私等級。
- 刷新 匯入資料通常需要重新整理操作，將變更（如新增、更新與刪除）帶入 Excel。 欲了解更多資訊，請參閱 「在 Excel 中刷新外部資料連線」。
- 連結/屬性 每個外部資料來源都有各種連接和財產資訊，有時會根據你的情況需要調整。 欲了解更多資訊，請參閱 「管理外部資料範圍及其屬性」、「 建立、編輯及管理外部資料連線」以及 「連線屬性」。
- 遺產 傳統方法如 Legacy Import Wizards 與 MSQuery 仍可使用。 欲了解更多資訊，請參閱 資料匯入與分析選項 ，並 使用 Microsoft Query 擷取外部資料。
以下章節將提供更多關於這家繁忙 Excel 火車站幕後運作的細節。
連接與性質摘要
有連線、查詢和外部資料範圍屬性。 連線屬性與查詢屬性都包含傳統連接資訊。 在對話框標題中， Connection Properties 表示沒有相關查詢，但 Query Properties 則表示有查詢。 外部資料範圍屬性控制資料的版面與格式。 所有資料來源都有 外部資料屬性 對話框，但包含憑證與刷新資訊的資料來源則使用較大的 外部範圍資料屬性 對話框。
以下資訊總結了最重要的對話框、面板、指令路徑及相應的說明主題。
| 對話框或面板 指令路徑 |
Tab 與 Tunnel | 主要說明主題 |
|---|---|---|
|
近期資料來源 資料>近期資料來源 |
(沒有分頁) 連接>隧道導航員對話框 |
管理資料來源設定與權限 |
|
連線內容 或者 資料連線精靈 資料>查詢 & 連結>連接 分頁 > (右鍵點擊連結) >屬性 |
使用分 頁 定義 標籤頁 在分頁中使用 |
連接性質 |
|
查詢屬性 資料>現有連結> (右鍵點擊連線) >編輯連線屬性 或者 資料>查詢 & Connectionss | 查詢 分頁 > (右鍵點擊連結) >屬性 或者 查詢>性質 或者 資料>全部重新整理>連結 (放在負載查詢工作表上) |
使用分 頁 定義 標籤頁 在分頁中使用 |
連接性質 |
|
查詢 & 連結 資料>查詢 & 連結 |
查詢 標籤 連接 分頁 |
連接性質 |
|
現有連結 資料>現有連結 |
連接 分頁 表格標籤 |
連線至外部資料 |
|
外部資料屬性 或者 外部資料範圍特性 或者 資料>如果不在查詢工作表中，屬性 (停用) |
在連接屬性對話框的分頁 (中使用) 右側通道的刷新按鈕指向查詢屬性 |
管理外部資料範圍及其內容 |
|
連接性質>定義 標籤 >匯出連接檔案 或者 查詢>匯出連接檔案 |
(沒有分頁) 通往 檔案 對話框 資料來源 資料夾 |
建立、編輯及管理外部資料的連線 |
資料連接的基本原理
Excel 工作簿中的資料可以來自兩個不同的位置。 資料可以直接儲存在工作簿中，或儲存在外部資料來源中，例如文字檔、資料庫，或線上分析處理 (OLAP) 立方體。 這個外部資料來源透過資料連線與工作簿連結，該連線是一組描述如何定位、登入及存取外部資料來源的資訊。
連接外部資料的主要好處是你可以定期分析這些資料，而不必反覆複製到工作簿，而這種操作可能既耗時又容易出錯。 連接外部資料後，只要資料來源更新新資訊，你也能自動刷新 (或更新 Excel 工作簿) 原始資料來源。
連線資訊儲存在工作簿中，也可以儲存在連接檔案中，例如Office Data Connection (ODC) 檔案 (.odc) ，或資料來源名稱檔案 (.dsn) 。
要將外部資料帶入 Excel，你需要存取這些資料。 如果你想存取的外部資料來源不在本地電腦上，可能需要聯絡資料庫管理員，索取密碼、使用者權限或其他連線資訊。 如果資料來源是資料庫，請確保資料庫不是以獨佔模式開啟。 如果資料來源是文字檔或試算表，請確保沒有其他使用者開啟該資料供專屬存取。
許多資料來源也需要 ODBC 驅動程式或 OLE 資料庫提供者來協調 Excel、連線檔案與資料來源之間的資料流動。
下圖總結了關於資料連接的關鍵點。
1. 你可以連接多種資料來源：Analysis Services、SQL Server、Microsoft Access、其他 OLAP 與關聯式資料庫、試算表以及文字檔案。
2. 許多資料來源都有相關的 ODBC 驅動程式或 OLE DB 提供者。
3. 連線檔案定義了存取及檢索資料來源所需的所有資訊。
4. 連線資訊會從連線檔案複製到工作簿中，連線資訊也可以輕鬆編輯。
5。 資料會被複製到工作簿中，讓你能像使用直接儲存在工作簿中的資料一樣使用它。
尋找連結
要找到連線檔案，請使用 現有連線 對話框。 (選擇 資料>現有連線。) 使用此對話框，您可以看到以下連線類型：
-
工作簿中的連結
此清單顯示工作簿中所有目前的連線。 該清單是從你已定義的連線建立，或是你透過資料連線精靈的 選擇資料來源 對話框建立的，或是你先前在此對話框中選擇的連線。 -
你電腦上的連線檔案
此清單是從通常存放在文件資料夾中的「我的資料來源」資料夾建立的。 -
網路上的連線檔案
此清單可由本地網路上的一組資料夾建立，資料夾位置可作為 Microsoft Office 群組政策部署的一部分，或是 SharePoint 函式庫部署。
編輯連線屬性
你也可以使用 Excel 作為連線檔案編輯器，建立和編輯儲存在工作簿或連線檔案中的外部資料來源連線。 如果找不到想要的連線，你可以點擊「 瀏覽更多」 以顯示 「選擇資料來源 」對話框，然後點擊 「新來源 」啟動資料連線精靈來建立連線。
建立連線後，你可以使用連線 屬性 對話框 (選擇 資料>查詢 & 連結>分頁> (右鍵點擊) >屬性) 的連線，控制與外部資料來源的各種連線設定，並使用、重用或切換連線檔案。
註有時候，當有在 Power Query (建立的查詢（前身為 Get & Transform) ）時，連線屬性對話框會被命名為查詢屬性對話框。
如果你用連接檔案連接資料來源，Excel 會把連接檔案的連結資訊複製到 Excel 工作簿。 當你透過連線屬性對話框進行變更時，你編輯的是目前 Excel 工作簿中儲存的資料連線資訊，而非原始資料連線檔案，該檔案名稱可能由定義標籤) 的連線檔案屬性顯示的原始資料連接檔案 (。 當你編輯連線資訊 (（除了 連線名稱 和 連線描述 屬性外）) 後，連接檔案的連結會被移除，連線 檔案 屬性也會被清除。
為了確保在資料來源刷新時，連線檔案會被使用，請在定義標籤頁點擊「總是嘗試使用此檔案來刷新此資料」。選擇此勾選框可確保所有使用該連接檔案的工作簿都會使用該連線檔案的更新，且該工作簿也必須設定此屬性。
管理連接
透過使用 連接 對話框，您可以輕鬆管理這些連線，包括建立、編輯和刪除 (選擇 資料>查詢 & 連接>連接標籤> (右鍵點擊) >屬性 ) 你可以利用這個對話框來做以下操作：
- 建立、編輯、重新整理並刪除工作簿中正在使用的連線。
- 確認外部資料的來源。 如果連線是由其他使用者定義的，你可能會想這麼做。
- 顯示目前工作簿中每個連接點的使用位置。
- 診斷有關外部資料連線的錯誤訊息。
- 將連線重新導向到其他伺服器或資料來源，或替換現有連線的連線檔案。
- 讓建立和分享連線檔案變得簡單。
檔案中共享 ODC 與查詢連線
連線檔案特別適合穩定分享連線，使連線更易被發現，有助於提升連線安全性，並促進資料來源管理。 分享連線檔案的最佳方式是將它們放在安全且值得信賴的地方，例如網路資料夾或 SharePoint 函式庫，讓使用者可以閱讀檔案，但只有指定使用者能修改檔案。 欲了解更多資訊，請參閱 與ODC分享資料。
使用 ODC 檔案
您可以透過 選擇資料來源 對話框連接外部資料，或使用資料連線精靈 ( (.odc) ，建立 Office 資料連線 ODC) 檔案。 ODC 檔案使用自訂的 HTML 和 XML 標籤來儲存連線資訊。 你可以輕鬆地在 Excel 中查看或編輯檔案內容。
你可以和其他人分享連線檔案，讓他們擁有你對外部資料來源的存取權限。 其他使用者不需要設定資料來源來開啟連線檔案，但他們可能需要安裝 ODBC 驅動程式或 OLE DB 提供者，才能存取電腦上的外部資料。
ODC 檔案是連接資料與分享資料的推薦方法。 你可以輕鬆將其他傳統連線檔案 (DSN、UDL，以及查詢檔案) 轉換成 ODC 檔案，方法是打開連線檔案，然後點擊連線屬性對話框定義標籤中的「匯出連線檔案」按鈕。
使用 query 檔案
查詢檔案是包含資料來源資訊的文字檔，包括資料所在伺服器名稱以及你建立資料來源時提供的連線資訊。 查詢檔案是與其他 Excel 使用者分享查詢的傳統方式。
使用 .dqy 查詢檔案 你可以使用 Microsoft Query 儲存包含關聯式資料庫或文字檔查詢資料的 .dqy 檔案。 當你在 Microsoft Query 開啟這些檔案時，可以查看查詢回傳的資料，並修改查詢以取得不同的結果。 你可以為任何查詢儲存 .dqy 檔案，無論是使用查詢嚮導或直接在 Microsoft Query 中。
使用 .oqy 查詢檔案 你可以儲存 .oqy 檔案，連接到 OLAP 資料庫中的資料，無論是在伺服器上，還是離線立方體檔案 (.cub) 。 當你使用 Microsoft Query 中的多維連接精靈建立 OLAP 資料庫或立方體的資料來源時，會自動建立一個 .oqy 檔案。 因為 OLAP 資料庫不是以紀錄或表格組織，你無法建立查詢或 .dqy 檔案來存取這些資料庫。
使用 .rqy 查詢檔案 Excel 可開啟 .rqy 格式的查詢檔案，以支援使用此格式的 OLE DB 資料來源驅動程式。 欲了解更多資訊，請參閱駕駛人文件。
使用 .qry 查詢檔案 Microsoft Query 可以開啟並儲存 .qry 格式的查詢檔案，以便早期版本無法開啟 .dqy 檔案的 Microsoft Query 使用。 如果你有一個 .qry 格式的查詢檔案想在 Excel 中使用，請在 Microsoft Query 中開啟該檔案，然後儲存為 .dqy 檔案。 關於儲存 .dqy 檔案的資訊，請參閱 Microsoft 查詢說明。
使用 .iqy 網頁查詢檔案 Excel 可以開啟 .iqy 網頁查詢檔，從網路上擷取資料。 更多資訊請參閱 從 SharePoint 匯出至 Excel。
使用外部資料屬性
外部資料範圍 (也稱為查詢表) 是一個定義的名稱或資料表名稱，用來定義資料帶入工作表的位置。 當你連接到外部資料時，Excel 會自動建立一個外部資料範圍。 唯一的例外是連接到資料來源的樞紐分析表報告，該報告不會產生外部資料範圍。 在 Excel 中，你可以格式化並排版外部資料範圍，或像其他資料一樣用於計算。
Excel 會自動為外部資料範圍命名如下：
- 外部資料範圍從 Office Data Connection (ODC) 檔案名稱與檔案相同。
- 來自資料庫的外部資料範圍會以查詢名稱命名。 預設情況下，Query_from_source 是你用來建立查詢的資料來源名稱。
- 來自文字檔的外部資料範圍以文字檔名稱命名。
- 來自網路查詢的外部資料範圍會以資料擷取的網頁名稱命名。
如果你的工作表有多個來自同一來源的外部資料範圍，這些範圍會被編號。 例如，MyText、MyText_1、MyText_2 等等。
外部資料範圍還有其他屬性 (不可與可用來控制資料的連線屬性混淆，例如保留儲存格格式和欄位寬度) 。 你可以透過在「資料」分頁的「連線」群組中點選「屬性」，然後在「外部資料範圍屬性」或「外部資料屬性」對話框中進行修改，來更改這些外部資料範圍屬性。
|
|
|---|
Excel Services 中的資料來源支援
有幾個資料物件 (，例如外部資料範圍和樞紐分析表報告) ，可以用來連接不同的資料來源。 不過，每個資料物件之間，你能連接的資料來源類型都不一樣。
你可以在 Excel Services 中使用並刷新已連接的資料。 和任何外部資料來源一樣，你可能需要驗證你的存取權限。 欲了解更多資訊，請參閱「在 Excel 中刷新外部資料連線」。如需更多關於憑證的資訊，請參閱 Excel Services 認證設定。
下表總結了 Excel 中每個資料物件所支援的資料來源。
|
Excel 資料 物件 |
創造 External 資料 範圍？ |
OLE DB |
ODBC |
文字 檔案 |
HTML 檔案 |
XML 檔案 |
SharePoint 清單 |
|
|---|---|---|---|---|---|---|---|---|
| 匯入文字精靈 | 是 | 否 | 否 | 是 | 否 | 否 | 否 | |
| 樞紐分析表 (非 OLAP) |
否 | 是 | 是 | 是 | 否 | 否 | 是 | |
| 樞紐分析表 (OLAP) |
否 | 是 | 否 | 否 | 否 | 否 | 否 | |
| Excel 表格 | 是 | 是 | 是 | 否 | 否 | 是 | 是 | |
| XML 映射 | 是 | 否 | 否 | 否 | 否 | 是 | 否 | |
| 網頁查詢 | 是 | 否 | 否 | 否 | 是 | 是 | 否 | |
| 資料連線精靈 | 是 | 是 | 是 | 是 | 是 | 是 | 是 | |
| Microsoft 查詢 | 是 | 否 | 是 | 是 | 否 | 否 | 否 |
注意
這些檔案包括透過匯入文字嚮導匯入的文字檔、透過 XML 映射匯入的 XML 檔案，以及透過網頁查詢匯入的 HTML 或 XML 檔案，皆不使用 ODBC 驅動程式或 OLE 資料庫提供者來連接資料來源。
Excel Services 針對 Excel 表格與命名範圍的替代方案
如果你想在 Excel Services 中顯示 Excel 工作簿，你可以連接並刷新資料，但必須使用樞紐分析表報告。 Excel Services 不支援外部資料範圍，這表示 Excel Services 不支援連接到資料來源、網頁查詢、XML 地圖或 Microsoft 查詢的 Excel 表格。
不過，你可以透過使用樞紐分析表連接資料來源，然後將樞紐分析表設計並排版為沒有層級、群組或子總的二維表格，這樣就能顯示所有想要的列和欄值。
ODBC 與 OLE DB 資料存取元件
讓我們一起回顧一下資料庫的回憶之旅。
關於 MDAC、OLE DB 和 OBC
首先，抱歉用了這麼多縮寫。 Microsoft 資料存取元件 (MDAC) 2.8 隨 Microsoft Windows 一同包含。 透過 MDAC，你可以連接並使用來自各種關聯式與非關聯式資料來源的資料。 你可以透過使用 Open Database Connectivity (ODBC) 驅動程式或 OLE DB 供應商，連接許多不同的資料來源，這些服務可能是由 Microsoft 建置與發佈，或由各第三方開發。 安裝 Microsoft Office 後，電腦會新增額外的 ODBC 驅動程式和 OLE DB 提供者。
若要查看電腦上安裝的完整 OLE DB 提供者清單，請從資料連結檔案中顯示 資料連結屬性 對話框，然後點擊 提供者 標籤。
若要查看電腦上安裝的完整 ODBC 服務提供者清單，請顯示 ODBC 資料庫管理員 對話框，然後點擊 驅動程式 標籤。
你也可以使用其他廠商的 ODBC 驅動程式和 OLE DB 提供者，從 Microsoft 以外的資料來源取得資訊，包括其他類型的 ODBC 和 OLE DB 資料庫。 如需安裝這些 ODBC 驅動程式或 OLE DB 提供者的相關資訊，請查看資料庫的說明文件，或洽詢您的資料庫廠商。
在 ODBC 架構中，應用程式 (如 Excel) 連接至 ODBC 驅動程式管理器，而 ODBC 驅動程式管理員則使用特定的 ODBC 驅動程式 (，如 Microsoft SQL ODBC 驅動程式) 來連接資料來源 (，如Microsoft SQL Server資料庫) 。
要連接 ODBC 資料來源，請執行以下步驟：
- 確保在包含資料來源的電腦上安裝了適當的 ODBC 驅動程式。
- 可透過使用 ODBC 資料來源管理員 將連線資訊儲存在登錄檔或 DSN 檔案中，或在 Visual Basic 程式碼中設定連接字串， (Microsoft 定義 DSN) 的資料來源名稱，直接將連線資訊傳送至 ODBC 驅動管理員。
要定義資料來源，在 Windows 中點選開始按鈕，然後點選控制台。 點選 系統與維護，然後點選 管理工具。 點選 「效能與維護」，再點「 管理工具」。 然後點擊 ODBC) (的資料來源 。 欲了解更多不同選項資訊，請點擊每個對話框中的 說明 按鈕。
機器資料來源
機器資料來源將連線資訊儲存在登錄檔中，存放在特定電腦上，並以使用者定義的名稱。 您只能在定義該資訊的電腦上使用該機器資料來源。 機器資料來源分為使用者和系統兩種。 使用者資料來源僅可由目前的使用者使用，並且只有該使用者看得到。 系統資料來源可被電腦上的所有使用者使用，且對所有使用者皆可見。
機器資料來源在提供額外安全性時特別有用，因為它確保只有登入的使用者能查看機器資料來源，且遠端使用者無法將機器資料來源複製到其他電腦。
檔案資料來源
檔案資料來源 (也稱為 DSN 檔案) 以文字檔形式儲存連線資訊，而非登錄檔，且通常比機器資料來源更靈活。 例如，你可以將檔案資料來源複製到任何安裝正確 ODBC 驅動程式的電腦，讓您的應用程式能依賴所有電腦的連線資訊，保持一致且準確。 或者，您可以將檔案資料來源置於單一伺服器，然後在網路上的多部電腦間共用，就能輕易地在單一位置維護連線資訊。
檔案資料來源也可以是不可共用的。 不可共用的檔案資料來源位於單一電腦上，並指向機器資料來源。 您可以使用不可共用的檔案資料來源，以從其中存取現有的機器資料來源。
在 OLE DB 架構中，存取資料的應用程式稱為資料消費者 (，如 Excel) ，而允許原生存取資料的程式稱為資料庫提供者 (，例如 Microsoft OLE DB Provider for SQL Server) 。
Universal Data Link 檔案 (.udl) 包含資料使用者透過 OLE DB 提供者存取資料來源的連線資訊。 您可以透過以下其中一項方式建立連結資訊：
- 在資料連線精靈中，使用 資料連結屬性 對話框來定義 OLE DB 提供者的資料連結。
- 建立一個空白文字檔，副檔名為 .udl，然後編輯該檔案，顯示 資料連結屬性 對話框。