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