Excel中SQL函数如何使用？

Excel 本身并不是一个传统意义上的关系型数据库管理系统（如 MySQL、SQL Server），但它提供了强大的数据处理能力，并且可以通过特定的功能来执行类似 SQL（结构化查询语言）的操作，理解如何在 Excel 中应用 SQL 思维和使用相关功能,可以极大地提升你处理和分析复杂数据的效率。

核心概念：Excel 中的“SQL”

在 Excel 中执行类似 SQL 的操作,主要有两种方式：

1. 使用 Excel 内置的“数据库”函数： 这些函数名称通常以 D 开头（如 DSUM, DAVERAGE, DCOUNT, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP），它们允许你基于指定的条件（类似于 SQL 的 WHERE 子句）对数据列表（类似于数据库表）进行汇总计算。
2. 使用 Power Query（获取和转换数据）： 这是现代 Excel（2016 及以后版本，或作为加载项）中更强大、更灵活的方式，Power Query 拥有一个功能丰富的图形化界面，其底层使用了一种名为 M 语言 的查询语言，更重要的是，当你连接到外部数据源（如 SQL Server, Access, Oracle 等）或 Excel 工作簿本身时，Power Query 允许你直接编写 SQL 语句 来查询数据源，然后将结果导入 Excel 进行处理和分析，对于 Excel 表格或命名区域作为“数据源”，Power Query 提供了类似 SQL 的转换操作（筛选、排序、分组、连接等），虽然不直接写 SQL,但逻辑高度一致。

本文将重点介绍这两种方式的具体应用：

使用 Excel 数据库函数 (D-Functions)

这些函数非常适合在单个工作表内对结构化的数据列表（确保你的数据有清晰的列标题）执行条件汇总。

基本语法：

=Dfunction(database, field, criteria)

• database (必需)： 包含数据列表的单元格区域。必须包含列标题。
• field (必需)： 指定要对哪一列进行汇总计算，可以是：
  • 的单元格引用（推荐，更清晰）。
  • 表示列在数据库中位置的数字（1 表示第一列，2 表示第二列，依此类推），数字方式在列位置变化时容易出错,不推荐。
• criteria (必需)： 包含指定条件的单元格区域。必须包含至少一个列标题和标题下方至少一个条件单元格，这是实现 WHERE 子句逻辑的关键。

关键点 – 条件区域 (criteria)：

• 结构： 条件区域必须包含与数据库区域中要应用条件的列相对应的列标题,不需要包含所有列标题。
• 条件规则：
  • 等于 (=)： 直接在条件标题下方的单元格中输入值，在 Region 标题下输入 North 表示 Region = "North"。
  • 比较运算符 (>, <, >=, <=, <>): 使用带引号的字符串，在 Sales 标题下输入 ">1000" 表示 Sales > 1000。
  • 通配符： 使用 (匹配任意字符序列) 和 (匹配单个字符)，在 Product 标题下输入 "*Widget*" 表示产品名称包含 “Widget”。
  • 多条件（AND）： 将条件放在同一行的不同列标题下。
    | Region | Sales |
    | :—– | :——– |
    | North | “>1000” |
    表示 Region = "North" AND Sales > 1000。
  • 多条件（OR）： 将条件放在不同行的相同或不同列标题下。
    | Region |
    | :—– |
    | North |
    | East |
    表示 Region = "North" OR Region = "East"。
    | Region | Sales |
    | :—– | :——– |
    | North | |
    | | “>1000” |
    表示 Region = "North" OR Sales > 1000。

示例场景：

假设你有一个销售数据表（A1:D11），列标题为 Region, Salesperson, Product, Sales。

• 计算“North”地区的总销售额：
  • 在某个地方（F1:G2）设置条件区域：
    | Region |
    | :—– |
    | North |
  • 公式：=DSUM(A1:D11, "Sales", F1:G2) 或 =DSUM(A1:D11, 4, F1:G2) (假设 Sales 是第4列)
• 计算“John”销售的“Widget”产品的平均销售额：
  • 条件区域 (F1:H3)：
    | Salesperson | Product |
    | :———- | :—— |
    | John | Widget |
  • 公式：=DAVERAGE(A1:D11, "Sales", F1:H3)
• 计算“East”或“West”地区销售额大于 500 的交易笔数：
  • 条件区域 (F1:H4)：
    | Region | Sales |
    | :—– | :——– |
    | East | “>500” |
    | West | “>500” |
  • 公式：=DCOUNT(A1:D11, "Sales", F1:H4) (DCOUNT 统计包含数字的单元格)

优点： 相对简单，无需离开工作表，结果动态更新（如果源数据或条件改变）。
缺点： 功能有限（主要是聚合计算），处理复杂多表关联或大型数据集效率较低,条件区域设置需要细心。

使用 Power Query（直接编写 SQL 或模拟 SQL 操作）

这是处理更复杂数据、连接多表、清理和转换数据的首选方法,尤其当数据量较大或来自外部源时。

A. 对 Excel 表格/区域使用 Power Query（模拟 SQL 操作）

1. 将数据转换为表格： 选中数据区域 (A1:D11)，按 Ctrl+T 或转到 插入 -> 表格，确认包含标题并创建表，给表起一个有意义的名称（如 tblSales），在 表格工具-设计 -> 属性 -> 表名称 中修改。
2. 打开 Power Query 编辑器：
   • 选中表格内任意单元格。
   • 转到 数据 选项卡 -> 获取和转换数据 组 -> 从表/区域，这将打开 Power Query 编辑器窗口。
3. 执行类似 SQL 的操作（图形化界面）：
   • 筛选 (WHERE): 点击列标题旁边的下拉箭头，选择文本筛选器、数字筛选器等设置条件（如 Region = "North"）。
   • 选择列 (SELECT): 按住 Ctrl 键点击需要保留的列标题，或使用 选择列 按钮，右键点击列标题选择 删除其他列 更快。
   • 排序 (ORDER BY): 点击列标题旁边的排序按钮 (A->Z 或 Z->A)。
   • 分组聚合 (GROUP BY): 选中要分组的列（如 Region），转到 转换 或 开始 选项卡 -> 分组依据，选择分组列，然后为聚合添加新列（如操作：求和，列：Sales，新列名：TotalSales），这相当于 SELECT Region, SUM(Sales) AS TotalSales FROM ... GROUP BY Region。
   • 连接表 (JOIN):
     • 确保你有另一个表（如客户信息表 tblCustomers，包含 Salesperson 和 Email）。
     • 在 Power Query 编辑器中处理 tblSales 时，转到 开始 选项卡 -> 合并查询。
     • 选择要连接到的另一个表 (tblCustomers)。
     • 在 tblSales 中选择连接键列 (Salesperson)，在 tblCustomers 中选择对应的连接键列 (Salesperson)。
     • 选择连接类型（如 左外部 – 保留所有销售记录并匹配客户信息，类似 SQL LEFT JOIN）。
     • 点击确定，新列会以 Table 形式添加，点击该列标题旁边的扩展按钮，选择需要合并过来的具体列（如 Email）。
   • 派生列 (SELECT ... AS / 计算列): 转到 添加列 选项卡 -> 自定义列，输入新列名和公式（如 [Sales] * 1.1 计算含税销售额）。
4. 应用并加载： 完成所有转换步骤后，点击 开始 选项卡 -> 关闭并上载，结果将作为一个新表加载回 Excel 工作表，Power Query 会记录所有步骤（在右侧“查询设置”窗格的“应用的步骤”中）,方便修改和刷新。

B. 对外部数据库使用 Power Query（直接编写 SQL）

1. 获取数据： 转到 数据 选项卡 -> 获取数据 -> 自数据库 -> 选择你的数据库类型（如 从 SQL Server 数据库）。
2. 输入连接信息： 提供服务器名称、数据库名称、身份验证方式（Windows 或数据库账号密码）。
3. 导航器窗口： 连接成功后，会显示数据库中的对象（表、视图）。
4. 编写 SQL 语句：
   • 在导航器窗口中，不要直接勾选表，找到并点击底部的 高级选项。
   • 勾选 启用 SQL 语句 选项（有时标签可能是 输入 SQL 语句 或类似）。
   • 在出现的文本框中，直接输入你的 SQL 查询语句。

     SELECT Region, SUM(Sales) AS TotalSales, COUNT(*) AS OrderCount
     FROM SalesData
     WHERE OrderDate >= '2025-01-01'
     GROUP BY Region
     ORDER BY TotalSales DESC;

   • 点击 确定。
5. 预览与加载： Power Query 会尝试执行 SQL 语句并显示预览结果，确认无误后，点击 加载 或 转换数据（进入 Power Query 编辑器进行进一步处理）将结果导入 Excel。

优点 (Power Query)：

• 功能极其强大： 支持复杂的数据清洗、转换、合并（多表连接）、聚合、透视/逆透视等。
• 处理大数据： 比工作表函数更高效处理大量数据（Excel 本身的行列限制依然存在，但 Power Query 处理过程更优）。
• 可重复性： 步骤可记录、修改和刷新（数据源更新后，一键刷新即可更新结果）。
• 直接 SQL 支持： 连接外部数据库时，可直接发挥 SQL 的全部威力进行查询。
• 更好的数据模型： 结果可加载到 Excel 数据模型，用于高级透视表(Power Pivot)和 Power BI。

缺点： 学习曲线比 D-Functions 稍陡峭，需要理解 M 语言基础或 SQL 知识（对于直接编写 SQL）。

重要注意事项与最佳实践

1. 数据源结构化： 无论是 D-Functions 还是 Power Query，清晰、一致、无合并单元格、无空行/列标题的数据结构是成功的基础，使用 Excel 表格 (Ctrl+T) 是最佳实践。
2. D-Functions 条件区域：
   • 确保条件区域的列标题完全匹配数据库区域的列标题（包括空格）。
   • 条件区域与数据库区域不要重叠。
   • 修改条件后，包含 D-Functions 的公式会自动重算。
3. Power Query：
   • 命名： 为查询、步骤、列起有意义的名称,提高可读性和可维护性。
   • 数据类型： 检查并确保 Power Query 正确识别了列的数据类型（日期、文本、数字等），错误的类型会导致计算或筛选错误,在编辑器中可以更改数据类型。
   • 刷新： 如果数据源（外部数据库或原始 Excel 表）更新了，记得在 Excel 中右键点击结果区域 -> 刷新，或使用 数据 选项卡 -> 全部刷新。
   • M 语言： 虽然图形界面很强大，了解基础的 M 语言可以解锁更高级的自定义转换，高级编辑器允许查看和修改生成的 M 代码。
4. 性能： 对于非常大的数据集或复杂查询，Power Query 通常比 D-Functions 性能更好，尤其是当结果加载到数据模型时，D-Functions 在数据量很大时可能变慢。
5. 选择哪种方法？
   • 需要快速在当前工作表内做简单的条件汇总 -> D-Functions。
   • 需要进行复杂的数据清洗、转换、合并多个表、处理大数据 -> Power Query。
   • 需要直接查询外部数据库 -> Power Query (使用 SQL 语句)。

虽然 Excel 不是 SQL 数据库，但它提供了强大的工具（数据库函数 D-Functions 和 Power Query）让你能够应用 SQL 的核心思想来查询、筛选、聚合和连接数据，掌握这些工具，特别是功能全面的 Power Query，能让你在 Excel 中处理数据的效率和能力提升到一个新的水平，对于简单的条件计算，D-Functions 方便快捷；对于复杂的数据处理任务和连接外部数据库，Power Query 是无可争议的首选，并且它支持直接编写 SQL 语句来充分利用数据库引擎的能力。

引用说明：

• 本文核心功能描述基于 Microsoft Excel (Microsoft 365 Apps 及较新版本) 的官方功能和广泛认可的最佳实践。
• SQL 语法标准参考遵循 ANSI SQL 的核心概念。
• Power Query 功能细节参考 Microsoft 官方文档：
  • Microsoft Support: Power Query for Excel Help (提供入门和概念指导)
  • Microsoft Learn: Power Query documentation (提供更深入的技术参考和 M 语言指南)
• 数据库函数 (D-Functions) 用法参考 Microsoft Support: Database functions (reference)