如何在 Excel 中获取外部数据源？
使用 SQL 查询数据，首先需要数据源。如果我们用 Excel 来呈现这些数据的话，就需要先从外部导入数据源。这里介绍两种直接导入的方式：
- 通过 OLE DB 接口获取外部数据源；
- 通过 Microsoft Query 导入外部数据源。
通过 OLE DB 接口获取外部数据源
OLE 的英文是 Object Link and Embedding，中文意思是对象连接与嵌入，它是一种面向对象的技术。DB 代表的就是数据库。OLE DB 的作用就是通向不同的数据源的程序接口，方便获取外部数据，这里不仅包括 ODBC，也包括其他非 SQL 数据类型的通路，你可以把 OLE DB 的作用理解成通过统一的接口来访问不同的数据源。
如果你想要在 Excel 中通过 OLE DB 接口导入数据，需要执行下面的步骤：
第一步，选择指定的文件。方法是通过“数据” → “现有连接”按钮选择连接。这里选择“浏览更多”，然后选择指定的 xls 文件。
第二步，选择指定的表格，勾选数据首行包含列标题，目的是将第一行的列名也加载进来。
第三步，通过“属性” → “定义”中的命令文本来使用 SQL 查询，选择我们想要的数据，也可以将整张表直接导入到指定的位置。
如果我们显示方式为“表”，导入全部的数据到指定的 A1（代表 A1 单元格），那么在 Excel 中就可以导入整个数据表，如下图所示：
通过 Microsoft Query 获取外部数据源
第二种方式是利用 Microsoft Query 功能导入外部数据源，具体步骤如下：
第一步，选择指定的文件。方法是通过“数据” → “获取外部数据”按钮选择数据库，这里我选择了“Excel Files”，然后选择我们想要导入的 xls 文件。
第二步。选择可用的表和列，在左侧面板中勾选我们想要导入的数据表及相应的列，点击 （>） 按钮导入到右侧的面板中，然后点击下一步。
最后我们可以选择“将数据返回 Microsoft Excel”还是“在 Microsoft Query 中查看数据或编辑查询”。这里我们选择第一个选项。
 当我们选择“将数据返回到 Microsoft Excel”后，接下来的操作和使用 OLE DB 接口方式导入数据一样，可以对显示方式以及属性进行调整：
这里，我们同样选择显示方式为“表”，导入全部的数据到指定的 A1(代表 A1 单元格），同样会看到如下的结果：
使用数据透视表和数据透视图做分析
通过上面的操作你也能看出来，从外部导入数据并不难，关键在于通过 SQL 控制想要的结果集，这里我们需要使用到 Excel 的数据透视表以及数据透视图的功能。
我简单介绍下数据透视表和数据透视图：
数据透视表可以快速汇总大量数据，帮助我们统计和分析数据，比如求和，计数，查看数据中的对比情况和趋势等。数据透视图则可以对数据透视表中的汇总数据进行可视化，方便我们直观地查看数据的对比与趋势等。
假设我想对主要角色（role_main）的英雄数据进行统计，分析他们平均的最大生命值（hp_max），平均的最大法力值 (mp_max)，平均的最大攻击值 (attack_max)，那么对应的 SQL 查询为：
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+ 数据透视表
现在我们使用 SQL 查询，通过 OLE DB 的方式来完成数据透视表。我们在第三步的时候选择“属性”，并且在命令文本中输入相应的 SQL 语句，注意这里的数据表是 [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
然后我们在右侧面板中选择“数据透视表字段”，以便对数据透视表中的字段进行管理，比如我们勾选 num，role_main，平均最大生命，平均最大法力，平均最大攻击力。
最后会在 Excel 中呈现如下的数据透视表：
使用 SQL+ 数据透视图
数据透视图可以呈现可视化的形式，方便我们直观地了解数据的特征。这里我们使用 SQL 查询，通过 Microsoft Query 的方式来完成数据透视图。我们在第三步的时候选择在 Microsoft Query 中查看数据或编辑查询，来看下 Microsoft Query 的界面：
然后我们点击“SQL”按钮，可以对 SQL 语句进行编辑，筛选我们想要的结果集，可以得到：
然后选择“将数据返回 Microsoft Excel”，在返回时选择“数据透视图”，然后在右侧选择数据透视图的字段，就可以得到下面这张图：
你可以看到使用起来还是很方便。
让 Excel 与 MySQL 进行数据交互
刚才我们讲解的是如何从 Excel 中导入外部的 xls 文件数据，并在 Excel 实现数据透视表和数据透视图的呈现。实际上，Excel 也可以与 MySQL 进行数据交互，这里我们需要使用到 MySQL for Excel 插件：
下载 mysql-for-excel 并安装，地址：https://dev./downloads/windows/excel/
下载 mysql-connector-odbc 并安装，地址：https://dev./downloads/connector/odbc/
这次我们的任务是给数据表增加一个 last_name 字段，并且使用 Excel 的自动填充功能来填充好英雄的姓氏。
第一步，连接 MySQL。打开一个新的 Excel 文件的时候，会在“数据”面板中看到 MySQL for Excel 的插件，点击后可以打开 MySQL 的连接界面，如下：
第二步，导入 heros 数据表。输入密码后，我们在右侧选择想要的数据表 heros，然后选择 Import MySQL Data 导入数据表的导入，结果如下：
第三步，创建 last_name 字段，使用 Excel 的自动填充功能来进行姓氏的填写（Excel 自带的“自动填充”可以帮我们智能填充一些数据），完成之后如下图所示：
第四步，将修改好的 Excel 表导入到 MySQL 中，创建一个新表 heros_xls。选中整个数据表（包括数据行及列名），然后在右侧选择“Export Excel Data to New Table”。这时在 MySQL 中你就能看到相应的数据表 heros_xls 了，我们在 MySQL 中使用 SQL 进行查询：
mysql > SELECT * FROM heros_xls
运行结果（69 条记录）：
需要说明的是，有时候自动填充功能并不完全准确，我们还需要对某些数据行的 last_name 进行修改，比如“夏侯惇”的姓氏应该改成“夏侯”，“百里守约”改成“百里”等。
|
