如何在Excel中通过指定SQL查询从现有表生成新表？

问题描述 #

我有Excel表t1，包含列c1、c2、c3，想要实现类似如下SQL的逻辑生成目标表：

SELECT t1.c1, t1.c2, min_c3
FROM 
  t1,
  (SELECT c1, MIN(c3) AS min_c3 GROUP BY c1) AS t2
WHERE
  t1.c1 = t2.c1
  AND t1.c3 = t2.min_c3;

要求无需导出到专业数据库管理系统（DBMS），用Excel自身功能实现，优先支持动态更新，可使用VBA或插件。

示例原表

目标结果表

解决方案 #

方法1：Power Query（推荐，支持动态更新）

Power Query是Excel自带工具，可视化处理数据且支持刷新更新，步骤如下：

1. 选中原表t1任意单元格，点击数据选项卡 → 从表格/区域（Excel 2016及以后版本），确认表范围和"我的表格有标题"，进入Power Query编辑器。
2. 分组计算每个c1的最小c3：点击转换 → 分组依据，设置：
   • 分组依据：c1
   • 新列名：min_c3
   • 操作：最小值
   • 列：c3
     点击确定得到分组表。
3. 合并原表与分组表：点击合并查询 → 合并查询作为新查询，选择原表和分组表，匹配列选c1，合并类型选"完整外部"。
4. 展开合并列：点击合并列的展开按钮，仅勾选min_c3，取消"使用原始列名作为前缀"。
5. 筛选匹配行：添加筛选条件c3 = min_c3，删除多余的c3列，调整列顺序为c1、c2、min_c3。
6. 上载结果：点击主页 → 关闭上载，选择上载至新工作表。原表数据变化时，右键结果表→刷新即可更新。

方法2：公式法（无插件，支持动态更新）

适合Excel 365/2021（动态数组）或旧版本：

1. 提取不重复c1：在目标表c1列首单元格（如E2）输入：

   =UNIQUE(t1[c1])
   

   旧版本用=INDEX(t1[c1], MATCH(0, COUNTIF(E$1:E1, t1[c1]), 0))，按Ctrl+Shift+Enter作为数组公式输入，下拉填充。
2. 计算对应min_c3：在F2输入：

   =MINIFS(t1[c3], t1[c1], E2)
   

3. 匹配c2值：在G2输入：

   =XLOOKUP(1, (t1[c1]=E2)*(t1[c3]=F2), t1[c2])
   

   旧版本用=INDEX(t1[c2], MATCH(1, (t1[c1]=E2)*(t1[c3]=F2), 0))，按Ctrl+Shift+Enter输入。
   动态数组公式无需手动下拉，原表数据变化时自动更新结果。

方法3：VBA法（一次性生成，手动触发更新）

适合批量或自定义逻辑，步骤：

1. 按Alt+F11打开VBA编辑器，插入新模块，粘贴代码：

   Sub GenerateTargetTable()
       Dim wsSource As Worksheet, wsTarget As Worksheet
       Dim lastRow As Long, i As Long, j As Long
       Dim dict As Object
       Dim minC3 As Double, currentC1 As String
       
       ' 设置源表和目标表
       Set wsSource = ThisWorkbook.Worksheets("t1") ' 替换为你的源表名称
       On Error Resume Next
       Set wsTarget = ThisWorkbook.Worksheets("Target")
       On Error GoTo 0
       If wsTarget Is Nothing Then
           Set wsTarget = ThisWorkbook.Worksheets.Add
           wsTarget.Name = "Target"
       End If
       
       ' 清空目标表（保留表头）
       wsTarget.Cells.Clear
       wsTarget.Range("A1:C1") = Array("c1", "c2", "min_c3")
       
       ' 用字典存储每个c1的最小c3
       Set dict = CreateObject("Scripting.Dictionary")
       lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
       
       ' 第一次遍历：获取最小c3
       For i = 2 To lastRow
           currentC1 = wsSource.Cells(i, "A").Value
           If Not dict.Exists(currentC1) Then
               dict(currentC1) = wsSource.Cells(i, "C").Value
           Else
               If wsSource.Cells(i, "C").Value < dict(currentC1) Then
                   dict(currentC1) = wsSource.Cells(i, "C").Value
               End If
           End If
       Next i
       
       ' 第二次遍历：筛选匹配行
       j = 2
       For i = 2 To lastRow
           currentC1 = wsSource.Cells(i, "A").Value
           minC3 = dict(currentC1)
           If wsSource.Cells(i, "C").Value = minC3 Then
               wsTarget.Cells(j, "A").Value = currentC1
               wsTarget.Cells(j, "B").Value = wsSource.Cells(i, "B").Value
               wsTarget.Cells(j, "C").Value = minC3
               j = j + 1
           End If
       Next i
       
       wsTarget.Columns("A:C").AutoFit
       MsgBox "目标表已生成！", vbInformation
   End Sub
   

2. 修改代码中wsSource的表名为实际名称，运行宏生成目标表。原表数据变化时，重新运行宏即可更新。

内容的提问来源于stack exchange，提问作者einpoklum