您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

Excel 整合 SQL Server 資料輸出報表自動化實作

編輯：關於SqlServer

企業普遍部署 SQL Server 儲存核心業務資料，但業務人員習慣使用 Excel 進行分析、報表製作與資料探勘。本文示範如何將 SQL Server 資料無縫整合至 Excel，建立動態連結的報表模型，減少對 DBA 的依賴並提升業務自助分析能力。

整合架構選項

方法	技術基礎	資料鮮度	技術門檻	適用場景
Excel 資料連結 (舊版)	ODBC/OLE DB + .dqy/.iqy	手動重新整理	低	靜態報表、單機使用
Power Query (Get & Transform)	M 語言 + 資料模型	排程/手動重新整理	中	現代 Excel (2016+)、自我服務 BI
Power Pivot + DAX	記憶體內分析引擎	模型重新整理	中高	大量資料、複雜計算、多表關聯
Analysis Services 表格式模型	企業級語義模型	伺服器端排程	高	多人共用、治理需求、單一真相來源

方法一：傳統資料連結 (Excel 2003/XP 相容)

步驟 1：建立 ODBC 系統 DSN

控制台 → 系統管理工具 → 資料來源 (ODBC) → 系統 DSN → 新增
選 SQL Server → 名稱：SQL_Northwind → 伺服器：(local)
驗證：Windows 整合或 SQL 驗證 → 預設資料庫：Northwind

步驟 2：Excel 匯入資料

Excel → 資料 → 匯入外部資料 → 新增資料庫查詢
選擇 SQL_Northwind → 輸入認證
查詢精靈：選擇資料表/欄位 → 篩選/排序 → 完成
選擇「將資料放入現有工作表」或「建立樞紐分析表」

步驟 3：設定自動重新整理

右鍵資料區域 → 資料範圍屬性 → 勾選「開啟檔案時重新整理」/「每 N 分鐘重新整理」
儲存為 .xlsx (含連結資訊) 或 .iqy (查詢檔可分享)

方法二：Power Query 現代化做法 (Excel 2016+ / Office 365 推薦)

連接 SQL Server

資料 → 取得資料 → 從資料庫 → 從 SQL Server 資料庫
伺服器：prod-sql01、資料庫：Northwind
進階選項：可直接輸入 T-SQL 查詢 (如 SELECT * FROM Orders WHERE OrderDate > '2024-01-01')

Power Query 編輯器轉換範例

let
    Source = Sql.Database("prod-sql01", "Northwind"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    Filtered = Table.SelectRows(Orders, each [OrderDate] >= #date(2024,1,1)),
    AddedCustom = Table.AddColumn(Filtered, "YearMonth", each Date.ToText([OrderDate], "yyyy-MM")),
    Grouped = Table.Group(AddedCustom, {"YearMonth"}, {{"TotalAmount", each List.Sum([Freight]), type number}})
in
    Grouped

載入至 Excel

關閉並載入 → 選擇「僅建立連接」+「加入資料模型」 (Power Pivot) 或「工作表」
查詢屬性 → 設定排程重新整理 (需發佈至 SharePoint/Power BI 服務)

方法三：Power Pivot 建立企業級報表模型

資料模型設計

Power Query 依序載入：Customers、Orders、Order Details、Products、Categories
全部勾選「加入資料模型」
切換至「圖表檢視」建立關聯：
Customers[CustomerID] 1:* Orders[CustomerID]
Orders[OrderID] 1:* Order Details[OrderID]
Order Details[ProductID] *:1 Products[ProductID]
Products[CategoryID] *:1 Categories[CategoryID]

DAX 度量值範例

Total Sales := SUMX('Order Details', [UnitPrice] * [Quantity] * (1 - [Discount]))

YoY Growth := 
VAR CurrentYear = CALCULATE([Total Sales], 'Date'[Year] = YEAR(TODAY()))
VAR PriorYear = CALCULATE([Total Sales], 'Date'[Year] = YEAR(TODAY()) - 1)
RETURN DIVIDE(CurrentYear - PriorYear, PriorYear, BLANK())

Top 5 Products := 
TOPN(5, 
    SUMMARIZE('Order Details', Products[ProductName], "Sales", [Total Sales]),
    [Sales], DESC)