Excel如何调用其他表格数据

在数据处理和分析工作中，Excel 经常需要与存储在外部数据库（如 SQL Server, MySQL, Oracle, Access, PostgreSQL 等）或大型数据表格（如另一个大型 Excel 工作簿、CSV 文件）中的数据打交道，直接将海量数据复制粘贴到 Excel 不仅效率低下，而且容易出错，更无法实现数据的动态更新。如何在 Excel 中高效、动态地调用其他数据库或表格中的数据呢？

以下是几种常用且强大的方法,适用于不同场景和技术水平：

🛠 方法一：使用 Power Query (推荐首选 – 最强大、最灵活、最易用)

Power Query (在 Excel 2016 及更高版本中称为“获取和转换数据”) 是微软为 Excel 提供的革命性数据连接、转换和加载 (ETL) 工具，它提供了直观的图形化界面,无需编程即可连接各种数据源并执行复杂的数据清洗和整合操作。

操作步骤 (以连接 SQL Server 数据库为例)

1. 打开“数据”选项卡： 在 Excel 功能区，找到并点击 “数据” 选项卡。
2. 选择“获取数据”： 在“获取和转换数据”区域，点击 “获取数据”。
3. 选择“自数据库”：
   • 如果你的数据源是关系型数据库 (如 SQL Server, MySQL, Oracle, Access)，选择 “自数据库”。
   • 如果你的数据源是文件 (如另一个 Excel 工作簿 .xlsx/.xls、文本/CSV 文件)，选择 “自文件”。
   • 如果你的数据源是 Web API 或其他类型，选择相应的选项 (如 “自其他源”)。
4. 选择具体数据库类型： 选择 “从 SQL Server 数据库”。
5. 输入连接信息：
   • 服务器： 输入数据库服务器的名称或 IP 地址 (localhost, 168.1.100, myserverinstance)，如果是本地安装的 SQL Server Express，通常是 localhost 或 。
   • 数据库(可选)： 输入你要连接的具体数据库名称。
   • 数据连接模式：
     • 导入： 将数据导入 Excel 工作簿，这是最常用模式，数据量受 Excel 行数限制 (约104万行)。
     • DirectQuery (如果支持)： 不导入数据，查询时实时连接数据库，适合超大数据库或需要实时性的场景,但功能可能受限且依赖网络连接。
   • 高级选项： 可设置 SQL 语句直接查询特定数据 (更灵活)。
6. 身份验证：
   • Windows： 使用当前 Windows 登录账户访问数据库 (推荐，更安全)。
   • 数据库： 输入数据库特定的用户名和密码。
   • 勾选 “记住密码” 需谨慎,特别是在共享文件时。
7. 导航和选择数据： 连接成功后，Power Query 导航器会显示数据库中的对象 (表、视图)。
   • 勾选你需要导入的表或视图。
   • 可以点击 “转换数据” 进入 Power Query 编辑器进行数据清洗、筛选、合并列等操作 再加载。
   • 也可以直接点击 “加载” 将数据导入 Excel 工作表。
8. 在 Power Query 编辑器中处理数据 (可选但推荐)：
   • 筛选行、删除列、更改数据类型、添加自定义列、合并/拆分列、透视/逆透视等。
   • 这些操作会记录为步骤,以后刷新数据时自动重新应用。
9. 加载数据： 在编辑器左上角点击 “关闭并上载” 或 “关闭并上载至…” (可选择加载到工作表、数据模型或仅创建连接)。
10. 刷新数据： 当数据库中的数据更新后，在 Excel 中：
    • 右键点击结果表内的任意单元格 -> 选择 “刷新”。
    • 或到 “数据” 选项卡 -> 点击 “全部刷新”。

优点

• 强大的数据转换能力： 图形化界面完成复杂清洗和整合。
• 支持多种数据源： 几乎涵盖所有常见数据库、文件、Web、API 等。
• 可重复性： 查询步骤被保存,刷新即可获取最新数据。
• 性能优化： 对导入的数据进行压缩处理。
• 易于维护： 界面友好,逻辑清晰。

注意事项

• Excel 版本： Power Query 在 Excel 2016 及以后版本内置，Excel 2010/2013 需要单独下载安装 Microsoft Power Query for Excel 插件。
• 数据量限制： “导入”模式受限于 Excel 单表最大行数 (~104万行)。“DirectQuery” 无此限制但功能受限。
• 数据库权限： 需要具有读取目标数据库/表的权限。
• 连接信息： 连接字符串可能包含服务器地址、数据库名、用户名密码，需妥善保管,共享工作簿时注意安全。

🔍 方法二：使用 Microsoft Query (较传统，适合简单 SQL 查询)

Microsoft Query 是一个较老的但依然可用的工具，它提供了一个向导界面来构建 SQL 查询或直接连接 ODBC 数据源。

操作步骤

1. 打开“数据”选项卡： 点击 “数据” 选项卡。
2. 选择“获取数据” -> “自其他源” -> “来自 Microsoft Query”：
   • 在较新版本中，这个路径可能较深，有时在 “获取数据” -> “自其他源” -> “自 ODBC” 或 “自 OLEDB” 也能启动类似流程。
3. 选择数据源： 在“选择数据源”对话框中：
   • 选择已有的 ODBC 数据源 (DSN)。
   • 或点击 “新建数据源” 创建新的 DSN (需要数据库驱动)。
4. 连接数据库： 根据向导输入服务器、数据库、用户名、密码等信息。
5. 查询向导：
   • 选择列： 从数据库表中选择需要的字段。
   • 筛选数据： 设置简单的筛选条件 (如 Country = 'China')。
   • 排序顺序： 设置结果排序方式。
6. 完成向导： 选择将数据 “返回 Microsoft Excel”。
7. 导入数据： 选择数据在工作表中的放置位置。
8. 刷新： 右键点击结果区域 -> “刷新”。

优点

• 内置在 Excel 中，无需额外插件 (旧版本)。
• 对于简单的连接和筛选足够用。
• 可以查看和编辑生成的 SQL 语句 (在向导最后一步选择 “在 Microsoft Query 中查看数据或编辑查询”)。

缺点

• 界面相对陈旧，功能不如 Power Query 强大灵活。
• 复杂的数据转换和清洗比较困难。
• 对现代数据源的支持不如 Power Query 广泛。

🧩 方法三：使用 ODBC 或 OLEDB 连接 (底层驱动，通常被前两种方法封装)

ODBC (Open Database Connectivity) 和 OLEDB (Object Linking and Embedding, Database) 是微软制定的数据库访问标准接口，Power Query 和 Microsoft Query 底层通常都是通过这些接口与数据库通信的。

何时需要直接使用？

• 当 Power Query 或 Microsoft Query 没有为你的特定数据库提供直接连接器，但该数据库提供了 ODBC 或 OLEDB 驱动程序时。
• 在 VBA 编程中直接连接数据库 (见方法四)。

基本步骤 (以创建系统 DSN 为例)

1. 安装驱动程序： 确保目标数据库的 ODBC 或 OLEDB 驱动程序已安装在你的电脑上 (通常由数据库厂商提供)。
2. 创建数据源名称 (DSN)：
   • 打开 Windows 的 “ODBC 数据源管理器” (64位) 或 “ODBC 数据源(32位)” (搜索 odbcad32.exe，注意区分32/64位，需与你的 Excel 版本匹配)。
   • 在 “系统 DSN” 或 “用户 DSN” 选项卡，点击 “添加”。
   • 选择对应的数据库驱动程序。
   • 按照提示配置 DSN：输入数据源名称、服务器地址、数据库名、用户名、密码等。
   • 测试连接，成功后保存 DSN。
3. 在 Excel 中使用 DSN：
   • 在 “数据” 选项卡 -> “获取数据” -> “自其他源” -> “自 ODBC” (或类似路径)。
   • 选择你创建好的 DSN。
   • 可能需要再次输入用户名密码。
   • 后续步骤与 Power Query / Microsoft Query 类似，选择表/视图或输入 SQL 语句,然后加载数据。

优点

• 通用性强,几乎所有数据库都支持。
• 是连接数据库的底层标准。

缺点

• 配置相对复杂，需要管理员权限创建系统 DSN。
• 在 Excel 中直接使用体验不如 Power Query 友好。
• 连接信息 (尤其密码) 在 DSN 或连接字符串中存储需注意安全。

⚙ 方法四：使用 VBA (编程方式，适合高级用户和自动化)

对于需要高度自动化、定制化查询或复杂逻辑的场景，可以使用 Excel VBA (Visual Basic for Applications) 编程来连接数据库。

核心概念

• ADO (ActiveX Data Objects) 或 DAO (Data Access Objects)： VBA 中用于访问数据库的库，ADO 更现代、更通用,推荐使用。
• Connection 对象： 管理与数据库的连接。
• Recordset 对象： 存储从数据库查询返回的结果集。
• SQL 语句： 使用标准的 SQL 语言 (SELECT, INSERT, UPDATE, DELETE) 与数据库交互。

简单示例 (使用 ADO 连接 SQL Server)

Sub ConnectToSQLServer()
    Dim conn As Object ' ADODB.Connection
    Dim rs As Object ' ADODB.Recordset
    Dim sConnString As String
    Dim sSQL As String
    ' 创建连接和记录集对象
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    ' 构建连接字符串 (替换为你的实际信息)
    sConnString = "Provider=SQLOLEDB;" & _
                  "Data Source=你的服务器名称或IP;" & _
                  "Initial Catalog=你的数据库名;" & _
                  "Integrated Security=SSPI;" ' 使用Windows身份验证
                  ' 或者使用数据库身份验证:
                  ' "User ID=你的用户名;Password=你的密码;"
    ' 打开数据库连接
    conn.Open sConnString
    ' 构建 SQL 查询
    sSQL = "SELECT * FROM 你的表名 WHERE 条件;"
    ' 执行查询并将结果存入记录集
    rs.Open sSQL, conn
    ' 将记录集的第一条记录开始的数据复制到当前工作表的 A2 单元格
    If Not rs.EOF Then
        Sheets("Sheet1").Range("A2").CopyFromRecordset rs
    Else
        MsgBox "没有检索到数据!"
    End If
    ' 清理对象
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

优点

• 高度灵活和强大： 可实现任何复杂的数据库交互逻辑。
• 自动化： 可以编写宏自动执行数据提取、更新等任务。
• 定制化： 完全控制数据处理和呈现方式。

缺点

• 需要编程知识： 必须熟悉 VBA 和 SQL。
• 开发维护复杂： 比图形化工具更耗时。
• 安全性： VBA 代码中硬编码连接字符串（尤其是密码）是重大安全风险，务必避免，考虑使用 Windows 身份验证或安全存储凭据的方法。
• 错误处理： 需要编写健壮的错误处理代码。

📌 总结与建议

1. 首选 Power Query (获取和转换数据)： 对于绝大多数用户和场景，这是最推荐的方法，它功能强大、界面友好、易于维护，能处理从简单连接到复杂ETL的各种任务。强烈建议学习和掌握此工具。
2. 简单查询或旧版 Excel： 如果任务非常简单，或者使用的是不支持 Power Query 的旧版 Excel (2010/2013 需装插件)，Microsoft Query 可以作为备选。
3. 特定驱动或底层需求： 当 Power Query 没有直接连接器时，配置 ODBC/OLEDB DSN 是必要的桥梁。
4. 高级自动化和定制： 如果你精通 VBA 和 SQL，并且需要开发高度自动化、定制化的数据库交互解决方案，VBA + ADO 是最终手段,但务必重视代码安全和维护性。

重要通用注意事项

• 权限： 确保你的 Windows 账户或数据库账户拥有读取目标数据库/表的足够权限。
• 连接安全： 尽量避免在连接字符串、DSN 或 Excel 文件中明文存储数据库密码，优先使用 Windows 集成身份验证 (Integrated Security=SSPI)，如果必须使用数据库账号密码,需格外注意文件保密和共享安全。
• 数据刷新： 理解不同方法下数据刷新的机制 (手动刷新、打开文件时刷新、定时刷新)。
• 数据量： 清楚 Excel 单表行数限制 (~104万行)，处理超大数据集时，考虑使用“DirectQuery”(如支持)、仅导入汇总数据、或使用 Power Pivot 数据模型。
• 版本兼容性： 注意不同 Excel 版本对 Power Query 和连接功能的支持差异，共享包含数据连接的工作簿时，确保接收方也有相应的访问权限和驱动（如果需要）。

通过选择合适的方法，你可以轻松突破 Excel 本身存储的限制，高效、动态地整合和分析来自各种数据库和外部表格的宝贵数据,显著提升工作效率和洞察力。

引用说明：

• 本文核心方法基于 Microsoft Excel 官方功能（Power Query / Get & Transform Data, Microsoft Query, ODBC/OLEDB Connectivity, VBA），具体操作步骤和界面描述来源于 Microsoft Excel 软件本身（版本 365 / 2021 / 2019 / 2016）。
• ADO (ActiveX Data Objects) 是 Microsoft 提供的数据访问组件模型。
• ODBC (Open Database Connectivity) 和 OLEDB (Object Linking and Embedding, Database) 是 Microsoft 制定的数据库访问接口标准。
• 关于数据库连接字符串的构造，参考了 Microsoft OLE DB Provider for SQL Server 等驱动程序的文档惯例，安全建议（如避免明文密码、使用 Windows 身份验证）遵循通用的数据库访问安全最佳实践。
• Excel 行数限制等产品规格信息来源于 Microsoft 官方文档。