access查询top10产品_Excel VBA 实战(12) - 通过SQL查询Excel工作表

自专栏开辟已经一年有余，与大家交流分享的过程促使我不断打磨自己的编程能力，总结自己在财务自动化过程中的经验与得失。得益于朋友们的留言，文章中的疏漏得以补充错误得以斧正。虽与我在专栏开篇所提到的理想中的教程还有相当大的差距，但是朝着这个目标，至少我们又前进了一小步。

距离上次更新已经过去有两个月时间。其间我尝试了Office JavaScript API，编程的感觉类似于VSTO, 但是实际操作的手感以及与Excel兼容的深度远远没有达到取代VBA的程度，再加上需要安装插件，要求全程联网，Bug较多以及对于稍大体积数据(>5Mb)的支持极度不友好，使我严重怀疑这项产品是否出自MS。官方推介视屏里诸多印度面孔以及咖喱味的英语口音证实了我的猜想。短期之内，Office API只可能是补充VBA的黑科技; 长期而言，此项技术总体方向没有太大问题，但细节实现方面仍有待观望。之后的文章里面，可能进行具体展示。

再来说一说刷知乎所看到的一文章，鼓吹Python完全取代VBA的文章可谓甚嚣尘上，其中一些观点完全不值一驳。关于VBA的优劣之前的文章中已经进行了详细的阐述，另外Python对于EXCEL的操作精细的粒度完全无法与VBA，不仅仅是对于格式的控制无法达到VBA那种完全掌控的程度，即使是数据操作，由于xml文档规范限制，也只能通过逐行遍历的方式来进行操作。存在即合理，只有深刻接触过两种解决方案的人方可做出客观公允的评价，而非盲目接受一些无良公众号的鼓吹。

---

回到本期话题

引入问题: 分析公司每周销售以及库存变动数据

1. 数据条目10万+，字段数目30+(产品名称，数量，销售员等等)，数据以Excel工作簿进行储存，工作簿大小为20Mb+
2. 不同销售区域数据以单一文件进行储存，各区域工作簿格式一致，其他属性如 1 所描述
3. 将全公司销售数据汇总，按不同维度获得各字段的公司TOP10 ，比如前10大畅销产品，销售额度前10的销售员

此问题是我2014年实践当中遇到的真实问题，进行诸多尝试之后我当时给出了如下答案

6234456/Excel-SheetQuery​github.com

分析:

1. 首先最容易的解决方案是直接通过SQL进行相应查询并导出文件。但是短期之内没有销售数据的查询权限，加上财务分析的需求始终变动此方案不可行
2. 数据透视表，虽然数据透视表也可以通过VBA进行操作 但是执行效率是一个严重的问题，特别是20M以上的工作簿，读取时间10秒+，再加上需要将所有数据读取到一个表格当中，Excel本身恐怕无所承受。
3. 通过VBA对数据进行逐行分类汇总处理，理论上可行，但是需求变更之后，所有分类逻辑也必须进行重新编码。

总结:

需要尽量与结构解耦，能以SQL模式批量处理数据的解决方案。

演示需要，附加Tableau示例工作表

https://community.tableau.com/servlet/JiveServlet/downloadBody/1236-102-2-15278/Sample%20-%20Superstore.xls6234456/Excel-SheetQueryhttps://community.tableau.com/servlet/JiveServlet/downloadBody/1236-102-2-15278/Sample%20-%20Superstore.xls​community.tableau.com

此示例工作表中含有宏命令，删除宏命令后放在代码文件的同一文件夹下。

此为表结构，演示需要，数据量仅为1万

要求: 获取2016年11月，购买Newell产品金额最大的前10位客户，按销售量降序排列。

Sub main()
    
   Dim q As New SQL
   
   ' connect to the excel sheet with location "datasrc.xlsx" by default
   ' different path could also be specified
   q.connect "Sample - Superstore.xls"
   

  ' the usage of query, condition and aggregate is similar to the Hibernate Framework
   q.aggregate "GROUP BY [Customer Name] ORDER BY SUM([Sales]) DESC"
   q.condition q.datumRng("Ship Date", DateSerial(2016, 11, 1), DateSerial(2016, 11, 30))
   q.condition "[Product Name] LIKE '%Newell%'"
  
   q.query "SELECT TOP 10 [Customer Name] as Customer, SUM([Sales]) as Total FROM - "
  
   
   ' write the result set to the first sheet
   ' if the set is empty, warning will be printed at the console
   Worksheets(1).Cells.clear
   
   q.write2Sht Worksheets(1).Name, Cells(1, 1)

   ' close connection
   q.closeCon
    

End Sub

SQL 类文件具体实现 请参考 GitHub, 如果没有引用相应的库文件而报错，只需要进行如下修改。

Private pRes As Object 'ADODB.Recordset

最后有如上结果

在此基础上进行进一步加工，已经容易了许多。

本期示例文件如下。

<待上传>

任何问题请随时留言。