数据量太大？计算太复杂？Excel的难题，数据库说太简单

吴明课堂陈晓

已于 2022-11-30 13:27:56 修改

阅读量850

点赞数 1

分类专栏：数据分析 Excel技能文章标签： excel 数据库数据分析 sql mysql

于 2022-11-30 13:25:10 首次发布

本文链接：https://blog.csdn.net/wmktcw/article/details/128113341

版权

Excel技能同时被 2 个专栏收录

10 篇文章 1 订阅

订阅专栏

数据分析

1 篇文章 0 订阅

订阅专栏

经常使用Excel的同学，往往会遇到一个经典难题：多对多查询。需要根据多个查询条件，查出多个符合条件的结果。

来看一个多对多查询的例子。

六一儿童节当天，公司将给有小孩的同事放半天假陪孩子，但咨询部因工作性质，需除外。需要找出符合条件的员工姓名和所属部门，再邮件通知到个人。

1. 已有员工信息数据如下图：

公司员工信息列表

2. 要求的查询结果如下图：

要求查询结果图示

3. 传统的函数方式解决，需要用到数组公式。

(1). 公式方案1：使用Index+ROW函数，如下图：

公式方案1图示

(2). 公式方案2，结合365订阅版Excel支持的Filter函数，如下图：

公式方案2图示

公式方案总结：

即使是第二种相对较为简单的公式组合，也需要很好的Excel使用基础才可以看懂并复用到自己的应用场景中。

4. 将数据导入到数据库中，则只需要用到最基础的SQL语句。

【Ps. 以下案例演示均是在mysql数据库上实现】

(1). 导入表《部门人员信息》如下图：

部门人员信息表图示

(2). 查询的SQL和执行结果如下图：

SQL及执行结果图示

数据库方案总结：

相比于Excel中需要相当扎实的公式使用基础才可以实现复用，数据库则只需要最基础的SQL使用技能即可轻松独立解决多对多查询问题。

数据库强大的函数库和计算能力支持我们一次解决数据量大和计算复杂的问题。

接下来看一个同时兼具数据量大和计算复杂两个特点的案例：

1. 已有数据如下图：

成绩数据表图示

2. 需求说明：

上图中共有13万1393条数据，需要从这些数据中找出每个职位代码总成绩（两科合计+笔试成绩）排名前三的准考证号和成绩信息，并算出其对应的排名。

3. Excel公式方案问题分析：

公式方案图示

公式方案的思路是先算出每个职位每条数据的排名，然后筛选出所有排名<=3的数据行。公式复杂并不是最大的阻碍，因为这个表有13万多条数据，数组公式批量填充后，会卡住，无法计算出所有的结果，并可能会导致Excel应用程序的崩溃。

4. 数据库解决方案：

将数据导入数据库表中后，执行SQL如下图所示：

SQL及执行结果图示

如上图所示：数据库只用了不到1秒就查出了所有的结果。

Excel VBA编程+数据库实现一键获取结果

如果我们有一定的VBA编程基础，可以使用ADO编程，结合Excel的便捷、通用性和数据库强大的算力，在Excel中通过控件实现一键快速获取结果。

动态效果如下图：

ExcelVBA+数据库方案效果动态演示图

为避免篇幅太长引起的阅读困境，此处仅贴出部分主要代码：

Option Explicit
Const startCell As String = "A3"

Private Sub CommandButton1_Click()
   '创建链接
   Dim conn As ADODB.Connection
   Set conn = createConn
   
   '创建查询语句
   Dim sql As String
   sql = "select * from " _
           & "(select temp.职位代码,temp.准考证号,temp.两科合计,temp.笔试成绩,temp.两科合计+temp.笔试成绩 as 总成绩," _
           & "rank() over(partition by temp.职位代码 order by (temp.两科合计+temp.笔试成绩) desc) as 排名 " _
           & "from `安徽省2022年度考试录用公务员笔试达到合格分数线人员成绩` as temp) temp1 " _
           & "where 排名<=3"
   
   '创建游标
   Dim rs As New ADODB.Recordset
   Set rs = RecordSetInfo.createRecordSet(conn, sql)
   
   '取查询结果放到单元格内
   Call getDatafromRs(rs)
   
   MsgBox "计算完毕"
           
End Sub

在文章最后会贴上上文出现过的所有截图内容所在的源文件。