如何在 Excel 中獲取外部數(shù)據(jù)源？
使用 SQL 查詢數(shù)據(jù)，首先需要數(shù)據(jù)源。如果我們用 Excel 來呈現(xiàn)這些數(shù)據(jù)的話，就需要先從外部導入數(shù)據(jù)源。這里介紹兩種直接導入的方式：
- 通過 OLE DB 接口獲取外部數(shù)據(jù)源；
- 通過 Microsoft Query 導入外部數(shù)據(jù)源。
通過 OLE DB 接口獲取外部數(shù)據(jù)源
OLE 的英文是 Object Link and Embedding，中文意思是對象連接與嵌入，它是一種面向?qū)ο蟮募夹g。DB 代表的就是數(shù)據(jù)庫。OLE DB 的作用就是通向不同的數(shù)據(jù)源的程序接口，方便獲取外部數(shù)據(jù)，這里不僅包括 ODBC，也包括其他非 SQL 數(shù)據(jù)類型的通路，你可以把 OLE DB 的作用理解成通過統(tǒng)一的接口來訪問不同的數(shù)據(jù)源。
如果你想要在 Excel 中通過 OLE DB 接口導入數(shù)據(jù)，需要執(zhí)行下面的步驟：
第一步，選擇指定的文件。方法是通過“數(shù)據(jù)” → “現(xiàn)有連接”按鈕選擇連接。這里選擇“瀏覽更多”，然后選擇指定的 xls 文件。
第二步，選擇指定的表格，勾選數(shù)據(jù)首行包含列標題，目的是將第一行的列名也加載進來。
第三步，通過“屬性” → “定義”中的命令文本來使用 SQL 查詢，選擇我們想要的數(shù)據(jù)，也可以將整張表直接導入到指定的位置。
如果我們顯示方式為“表”，導入全部的數(shù)據(jù)到指定的 A1（代表 A1 單元格），那么在 Excel 中就可以導入整個數(shù)據(jù)表，如下圖所示：
通過 Microsoft Query 獲取外部數(shù)據(jù)源
第二種方式是利用 Microsoft Query 功能導入外部數(shù)據(jù)源，具體步驟如下：
第一步，選擇指定的文件。方法是通過“數(shù)據(jù)” → “獲取外部數(shù)據(jù)”按鈕選擇數(shù)據(jù)庫，這里我選擇了“Excel Files”，然后選擇我們想要導入的 xls 文件。
第二步。選擇可用的表和列，在左側面板中勾選我們想要導入的數(shù)據(jù)表及相應的列，點擊 （>） 按鈕導入到右側的面板中，然后點擊下一步。
最后我們可以選擇“將數(shù)據(jù)返回 Microsoft Excel”還是“在 Microsoft Query 中查看數(shù)據(jù)或編輯查詢”。這里我們選擇第一個選項。
 當我們選擇“將數(shù)據(jù)返回到 Microsoft Excel”后，接下來的操作和使用 OLE DB 接口方式導入數(shù)據(jù)一樣，可以對顯示方式以及屬性進行調(diào)整：
這里，我們同樣選擇顯示方式為“表”，導入全部的數(shù)據(jù)到指定的 A1(代表 A1 單元格），同樣會看到如下的結果：
使用數(shù)據(jù)透視表和數(shù)據(jù)透視圖做分析
通過上面的操作你也能看出來，從外部導入數(shù)據(jù)并不難，關鍵在于通過 SQL 控制想要的結果集，這里我們需要使用到 Excel 的數(shù)據(jù)透視表以及數(shù)據(jù)透視圖的功能。
我簡單介紹下數(shù)據(jù)透視表和數(shù)據(jù)透視圖：
數(shù)據(jù)透視表可以快速匯總大量數(shù)據(jù)，幫助我們統(tǒng)計和分析數(shù)據(jù)，比如求和，計數(shù)，查看數(shù)據(jù)中的對比情況和趨勢等。數(shù)據(jù)透視圖則可以對數(shù)據(jù)透視表中的匯總數(shù)據(jù)進行可視化，方便我們直觀地查看數(shù)據(jù)的對比與趨勢等。
假設我想對主要角色（role_main）的英雄數(shù)據(jù)進行統(tǒng)計，分析他們平均的最大生命值（hp_max），平均的最大法力值 (mp_max)，平均的最大攻擊值 (attack_max)，那么對應的 SQL 查詢?yōu)椋?/p>
SELECT role_main, avg(hp_max) AS `平均最大生命`, avg(mp_max) AS `平均最大法力`, avg(attack_max) AS `平均最大攻擊力`, count(*) AS num FROM heros GROUP BY role_main
使用 SQL+ 數(shù)據(jù)透視表
現(xiàn)在我們使用 SQL 查詢，通過 OLE DB 的方式來完成數(shù)據(jù)透視表。我們在第三步的時候選擇“屬性”，并且在命令文本中輸入相應的 SQL 語句，注意這里的數(shù)據(jù)表是 [heros$]，對應的命令文本為：
SELECT role_main, avg(hp_max) AS `平均最大生命`, avg(mp_max) AS `平均最大法力`, avg(attack_max) AS `平均最大攻擊力`, count(*) AS num FROM [heros$] GROUP BY role_main
然后我們在右側面板中選擇“數(shù)據(jù)透視表字段”，以便對數(shù)據(jù)透視表中的字段進行管理，比如我們勾選 num，role_main，平均最大生命，平均最大法力，平均最大攻擊力。
最后會在 Excel 中呈現(xiàn)如下的數(shù)據(jù)透視表：
使用 SQL+ 數(shù)據(jù)透視圖
數(shù)據(jù)透視圖可以呈現(xiàn)可視化的形式，方便我們直觀地了解數(shù)據(jù)的特征。這里我們使用 SQL 查詢，通過 Microsoft Query 的方式來完成數(shù)據(jù)透視圖。我們在第三步的時候選擇在 Microsoft Query 中查看數(shù)據(jù)或編輯查詢，來看下 Microsoft Query 的界面：
然后我們點擊“SQL”按鈕，可以對 SQL 語句進行編輯，篩選我們想要的結果集，可以得到：
然后選擇“將數(shù)據(jù)返回 Microsoft Excel”，在返回時選擇“數(shù)據(jù)透視圖”，然后在右側選擇數(shù)據(jù)透視圖的字段，就可以得到下面這張圖：
你可以看到使用起來還是很方便。
讓 Excel 與 MySQL 進行數(shù)據(jù)交互
剛才我們講解的是如何從 Excel 中導入外部的 xls 文件數(shù)據(jù)，并在 Excel 實現(xiàn)數(shù)據(jù)透視表和數(shù)據(jù)透視圖的呈現(xiàn)。實際上，Excel 也可以與 MySQL 進行數(shù)據(jù)交互，這里我們需要使用到 MySQL for Excel 插件：
下載 mysql-for-excel 并安裝，地址：https://dev./downloads/windows/excel/
下載 mysql-connector-odbc 并安裝，地址：https://dev./downloads/connector/odbc/
這次我們的任務是給數(shù)據(jù)表增加一個 last_name 字段，并且使用 Excel 的自動填充功能來填充好英雄的姓氏。
第一步，連接 MySQL。打開一個新的 Excel 文件的時候，會在“數(shù)據(jù)”面板中看到 MySQL for Excel 的插件，點擊后可以打開 MySQL 的連接界面，如下：
第二步，導入 heros 數(shù)據(jù)表。輸入密碼后，我們在右側選擇想要的數(shù)據(jù)表 heros，然后選擇 Import MySQL Data 導入數(shù)據(jù)表的導入，結果如下：
第三步，創(chuàng)建 last_name 字段，使用 Excel 的自動填充功能來進行姓氏的填寫（Excel 自帶的“自動填充”可以幫我們智能填充一些數(shù)據(jù)），完成之后如下圖所示：
第四步，將修改好的 Excel 表導入到 MySQL 中，創(chuàng)建一個新表 heros_xls。選中整個數(shù)據(jù)表（包括數(shù)據(jù)行及列名），然后在右側選擇“Export Excel Data to New Table”。這時在 MySQL 中你就能看到相應的數(shù)據(jù)表 heros_xls 了，我們在 MySQL 中使用 SQL 進行查詢：
mysql > SELECT * FROM heros_xls
運行結果（69 條記錄）：
需要說明的是，有時候自動填充功能并不完全準確，我們還需要對某些數(shù)據(jù)行的 last_name 進行修改，比如“夏侯惇”的姓氏應該改成“夏侯”，“百里守約”改成“百里”等。
|
