Excel 数据源的多表合并和 SQL 查询

作者: , 共 1375 字
系列:办公自动化

查看该系列所有文章

Excel 多表合并和查询是一个应用很广泛的问题。下面是一个简单的例子,我们需要从两张数据表里,得出每个行业的股票波动率平均值。第一个数据表保存了股票和行业的对应关系,有两列,第一列为股票名,第二列为每只股票对应的行业。第二张表保存了各个股票在各个交易日的收盘价和前收盘价,有四列,第一列是股票名,第二列为交易日,第三列和第四列分别为股票在这个交易日的前收盘价和收盘价。

上面例子的一个简单且常用的方法是使用增加辅助列、averageifs、数据透视表之类的函数。但当数据比较大或者逻辑结构比较复杂时,这些函数计算的速度比较慢。杨文博在博客上提到类似的问题,并给出了一个基于 Python 的解决方案。但这样需要安装 Python ,同时修改起来也比较麻烦。下面以上述案例为例子演示 Excel 内部如何支持基于多个表格数据进行查询。

1、单击菜单 数据-自其它来源-来自 Microsoft Query ,之后弹出一个窗口

choose data source

2、选择 Excel Files*点击确定后,进入选择工作簿界面。此处选择包含数据的文件。它可以是你正在编辑的这个文件。再次点击确定后你将进入到以下界面。在该界面上点击左下方的「选项」,在弹出的表选项勾选上「系统表」。确定后可以看到查询向导左侧会出来一些数据表。每一行都是一个数据表其中后缀为'\$'的表为 Excel 文件中的工作表,其它为 Excel 文件中的名称所定义的数据区域。点击左侧的「+」号可以展开看这个数据表的列名。

choose column

3、接下来有几种方式,一种方式是利用查询向导可视化地建立数据库查询语句,另一种方式是直接输入数据库查询语句。我一般使用后者。这时候在上面的「查询向导-选择列」中随意勾选上一列,不断下一步,直到点击完成确定。此时会弹出以下方框:

choose position

4、此时可以选择数据显示为表格形式或者数据透视表 ,并选择数据放置位置。激活刚才选择的位置,点击菜单「数据-属性」,在弹出的窗口再次选择连接属性,进入下图的界面:

connection properties

在命令文本区域输入新的 SQL 查询语句,点击确定,再刷新数据区域即可。

SQL 查询语句是整个操作的核心。这里不谈具体 SQL 语句怎么写,而是介绍下如何引用各种数据表。有以下几种方法:

  • 通过名称引用。比如如果定义一个数据区域为 Industry ,那么select * from industry这样是可行的。这种方法最多支持 65535 行数据,当数据行数过多时, Excel 会提示找不到该数据表。同一张工作表里可以有多个数据表,通过定义不同的名称去引用。
  • 通过工作表名引用。比如一个工作表名为 Quotes ,那么select * from `Quotes$`这样是可行的。这里工作表名后面的$号表示这是一个工作表。工作表可以包含高达 100 万行数据。但同一个工作表内只能有一个数据表。
  • 可以通过数据表的地址进行引用。比如select * from `Quotes$A1:B10000`
  • 上面的引号可以用中括号代替。比如select * from [Quotes$A1:B10000]
  • 如果数据表不在目前工作的文件内,需要在上面的数据表名前添加数据文件的路径和文件名,比如select * from `D:\test.xlsx`.`Quotes$`

上述例子的演示文件

Q. E. D.

系列: 办公自动化 »
编程 » vbs, 办公自动化
工作中或多或少有些琐碎的事情,比如每天要发送和接受数据,很多是通过邮件的方式传递的。之前写过如何在 Outlook 里自动保存附件,这里再发一个如何更方便地发送带附件的邮件。
以前发过一个编辑和发送 Outlook 邮件的 Excel/VBA 脚本。最近公司不让用 Outlook ,强制使用 IBM Lotus Notes ,我又写了一个编辑和发送 Lotus 邮件的 VBA 脚本。
类似文章:
2014-03-25 更新:我已经将该类修改成函数形式,并增加新功能,参见更新 Excel 的数据库查询函数库
编程 » Excel, 数据库
在前面的文章里,我已经提到Excel 数据本身可以当做一张 SQL 查询的数据表,并在 Excel 内进行数据库运算操作。数据库查询函数可以用我之前写的Excel 数据库操作函数类。我们可以用以下方式
Excel 有一个很有用的功能是直接导入外部数据库或者使用外部数据源建立数据透视表和数据透视图。但比较可惜的是,这个数据源的查询语句是静态的,它无法根据日期自动修改(比如在应用中,我们希望每天获取的外部数据都是当天最新的数据),下面两个函数是修改外部数据源的 VBA 代码,调用它们就可以建立动态的数据源。
更新一下之前写的Excel 的数据库类,将其改成函数的形式,调用更简单(省却了生成类实例的步骤)。现在这个代码在工作中用了一年多,已经比较健壮。若有问题,请留言指出或与我联系。
Excel、Matlab 在与数据库交互数据时,通常需要事先配置好 ODBC 数据源,这导致一个文件换到另一台机器还需要重新配置 ODBC。手工配置太麻烦,从国外一个网站看到一种解决方案:
编程 » Excel
在编辑 Excel 文件时经常遇到的一个问题是,我这边用得好好地,换台机器就变了个样,或者根本用不了。下面是我在日常工作中总结的一些避免这些情况的小技巧。
编程 » Excel, VBA
效果图:
之前对普通 Excel 文件,我都保存为默认的 xlsx 格式,带 vba 的保存为 xlsm 格式。最近从同事那里见到一个高大上的 excel 文件格式: xlsb。我学习了一下,觉得甚为强大,在此推荐一下。
如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查找、比较的效率。而如果用数字来表示日期,在不同的系统之间差别就大了。
Python 数据分析工具 pandas 中以 DataFrame 和 Series 作为主要的数据结构。
前一篇:
经济金融 » CFA, FRM
今年我幸运地通过 CFA 和 FRM 的最后一次考试,顺利结束 CFA 和 FRM 的考试之旅。下面是对这两个考试的介绍和我的一些想法。
以前发过一个编辑和发送 Outlook 邮件的 Excel/VBA 脚本。最近公司不让用 Outlook ,强制使用 IBM Lotus Notes ,我又写了一个编辑和发送 Lotus 邮件的 VBA 脚本。