数据如何通过 Excel 进行传输

应用对象
Microsoft 365 专属 Excel

如果数据始终在旅途中，那么 Excel 就像中央车站。 假设数据是一列满载乘客的火车，它们定期进入 Excel，进行更改，然后离开。 可通过多种方式进入 Excel，Excel 会导入所有类型的数据，而且列表还在不断增多。 数据进入 Excel 后，即可使用 Power Query 按照你想要的方式更改形状。 和我们所有人一样，数据也需要“照顾和喂养”才能保持事情顺利运行。 这就是连接、查询和数据属性的用武之地。 最后，数据通过多种方式离开 Excel 火车站：由其他数据源导入、作为报表、图表和数据透视表共享，以及导出到 Power BI 和 Power Apps。  

Excel 概述，其中许多用于输入、处理和输出数据

可在 Excel 火车站中使用数据执行的主要操作

以下是数据位于 Excel 火车站中时可以执行的主要操作：

以下各节将详细介绍这个繁忙的 Excel 火车站的幕后情况。

连接和属性摘要

有连接、查询和外部数据范围属性。 连接和查询属性都包含传统的连接信息。 在对话框标题中， 连接属性表示 没有与之关联的查询，但 查询属性表示 存在。 外部数据区域属性控制数据的布局和格式。 所有数据源都有一个 外部数据属性对话框 ，但具有关联凭据和刷新信息的数据源使用较大的 外部区域数据属性 对话框。

以下信息总结了最重要的对话框、窗格、命令路径和相应的帮助主题。

对话框或窗格
命令路径		 选项卡和隧道 主要帮助主题
最近使用的源
数据>最近使用的源		 (无选项卡)
“隧道以 连接导航>器 ”对话框		 管理数据源设置和权限
连接属性
或
数据连接向导
数据>查询 & 连接>“连接”选项卡 > (右键单击连接) >“属性”		 “使用状况”选项卡
“定义”选项卡
选项卡中使用位置		 连接属性
查询属性
数据>现有连接> (右键单击连接) >编辑连接属性
或
数据>查询 & 连接|“ 查询” 选项卡 > (右键单击连接) >“属性”
或
查询>属性
或
数据>全部>刷新放置在加载的查询工作表上时的连接 ()		 “使用状况”选项卡
“定义”选项卡
选项卡中使用位置		 连接属性
查询 & 连接
数据>查询 & 连接		 “查询”选项卡
“连接”选项卡		 连接属性
现有连接
数据>现有连接		 “连接”选项卡
“表格”选项卡		 连接到外部数据
外部数据属性
或
外部数据区域属性
或
数据>如果 未放置在查询工作表上，则属性 (禁用)
 在“连接属性”对话框中的选项卡 (中使用)

右侧的“刷新”按钮 隧道以连接查询属性		 管理外部数据区域及其属性
连接属性>“定义”选项卡 >导出连接文件
或
查询>导出连接文件		 (无选项卡)
连接到
“文件”对话框
数据源 文件夹		 创建、编辑和管理到外部数据的连接

数据连接的基础知识

Excel 工作簿中的数据可能来自两个不同的位置。 数据可以直接存储在工作簿中，也可以存储在外部数据源中，例如文本文件、数据库或 OLAP) 多维数据集 (联机分析处理。 此外部数据源通过数据连接连接到工作簿，数据连接是一组描述如何查找、登录和访问外部数据源的信息。

连接到外部数据的主要好处是可以定期分析这些数据，而无需重复将数据复制到工作簿，这是一项可能非常耗时且容易出错的操作。 连接到外部数据后，每当数据源更新为新信息时，还可以自动刷新 (或从原始数据源更新) Excel 工作簿。

连接信息存储在工作簿中，也可以存储在连接文件中，例如 Office 数据连接 (ODC) 文件 (.odc) 或数据源名称文件 (.dsn) 。

若要将外部数据导入 Excel，你需要有权访问这些数据。 如果要访问的外部数据源不在本地计算机上，则可能需要与数据库管理员联系，以获取密码、用户权限或其他连接信息。 如果数据源是数据库，请确保数据库不是以独占模式打开的。 如果数据源是文本文件或电子表格，请确保其他用户未打开该数据源以进行独占访问。

许多数据源还需要 ODBC 驱动程序或 OLE DB 提供程序来协调 Excel、连接文件和数据源之间的数据流。

连接到外部数据源  

下图总结了有关数据连接的要点。

1. 可以连接到各种数据源：Analysis Services、SQL Server、Microsoft Access、其他 OLAP 和关系数据库、电子表格和文本文件。

2. 许多数据源都有关联的 ODBC 驱动程序或 OLE DB 提供程序。

3. 连接文件定义从数据源访问和检索数据所需的所有信息。

4. 连接信息从连接文件复制到工作簿中，并且可以轻松编辑连接信息。

5. 数据将被复制到工作簿中，以便您可以像使用直接存储在工作簿中的数据一样使用它。

查找连接

要查找连接文件，请使用 “现有连接” 对话框。 (Select Data>Existing Connections.) 使用此对话框，可以看到以下类型的连接：

  • 工作簿中的连接数 
    此列表显示工作簿中的所有当前连接。 该列表将根据您已经定义的连接、使用“数据连接向导”的 “选择数据源 ”对话框创建的连接或您之前在此对话框中选择作为连接的连接创建。
  • 计算机上的连接文件 
    此列表是从通常存储在“文档”文件夹中的“我的数据源”文件夹创建的。
  • 网络上的连接文件 
    可以根据本地网络上的一组文件夹创建此列表，这些文件夹的位置可以作为 Microsoft Office 组策略或 SharePoint 库部署的一部分跨网络部署。 

编辑连接属性

还可将 Excel 用作连接文件编辑器，以创建和编辑与存储在工作簿或连接文件中的外部数据源的连接。 如果找不到所需的连接，可以通过单击“ 浏览查找更多” 以显示 “选择数据源 ”对话框，然后单击“ 新建源 ”以启动数据连接向导来创建连接。

创建连接后，可以使用“连接属性”对话框 (选择“数据>查询”&“连接>”选项卡> ， (右键单击连接) >“属性”) ，以控制与外部数据源的连接的各种设置，以及使用、重用或切换连接文件。

备注有时，当在 Power Query (中创建的查询（以前称为“获取 & 转换) ”与之关联时，“连接属性”对话框被命名为“查询属性”对话框。

如果使用连接文件连接到数据源，则 Excel 会将连接文件中的连接信息复制到 Excel 工作簿中。 使用“连接属性”对话框进行更改时，你编辑的是存储在当前 Excel 工作簿中的数据连接信息，而不是可能用于创建连接的原始数据连接文件，该 (由“定义”选项卡) “连接文件”属性中显示的文件名指示。 编辑连接信息 (后，除了 “连接名称 ”和“ 连接说明 ”属性) 外，将删除指向连接文件的链接，并清除 “连接文件 ”属性。

要确保在刷新数据源时始终使用连接文件，请单击“定义”选项卡上的始终尝试使用此文件来刷新此数据。选中此检查框可确保使用该连接文件的所有工作簿将始终使用对连接文件的更新，这些工作簿也必须设置了此属性。

管理连接

通过使用 “连接” 对话框，您可以轻松管理这些连接，包括创建、编辑和删除连接， (选择 数据>查询 &“连接>” 选项卡 > (右键单击连接) >“属性”。) 可以使用此对话框执行下列操作：

  • 创建、编辑、刷新和删除工作簿中正在使用的连接。
  • 验证外部数据的源。 如果连接是由另一个用户定义的，您可能需要执行此操作。
  • 显示每个连接在当前工作簿中使用的位置。
  • 诊断有关与外部数据的连接的错误消息。
  • 将连接重定向到其他服务器或数据源，或替换现有连接的连接文件。
  • 轻松创建连接文件并与用户共享。

在文件中共享 ODC 和查询连接

连接文件对于一致地共享连接、使连接更易于发现、有助于提高连接的安全性以及促进数据源管理特别有用。 共享连接文件的最佳方式是将它们放置在安全且受信任的位置，例如网络文件夹或 SharePoint 库中，用户可以在其中读取文件，但只有指定的用户才能修改文件。 有关详细信息，请参阅 与 ODC 共享数据。

使用 ODC 文件

可以通过“ 选择数据源 ”对话框连接到外部数据，或使用“数据连接向导”连接到新数据源，) 创建 Office 数据连接 (ODC) ( 文件。 ODC 文件使用自定义的 HTML 和 XML 标记来存储连接信息。 可以在 Excel 中轻松查看或编辑文件内容。

您可以与其他人共享连接文件，以向他们授予您对外部数据源相同的访问权限。 其他用户无需设置数据源即可打开连接文件，但可能需要安装访问计算机上的外部数据所需的 ODBC 驱动程序或 OLE DB 提供程序。

ODC 文件是用于连接到数据和共享数据的推荐方法。 通过打开连接文件，然后单击“连接属性”对话框的“定义”选项卡上的“导出连接文件”按钮，可以轻松地将其他传统连接文件 (DSN、UDL 和查询文件) 转换为 ODC 文件。

使用查询文件

查询文件是包含数据源信息的文本文件，其中包括数据所在服务器的名称以及您在创建数据源时提供的连接信息。 查询文件是与其他 Excel 用户共享查询的传统方式。

使用 .dqy 查询文件 可以使用 Microsoft Query 保存 .dqy 文件，其中包含对关系数据库或文本文件中的数据进行查询。 在 Microsoft Query 中打开这些文件时，可以查看查询返回的数据，并修改查询以检索不同的结果。 可以使用查询向导或直接在 Microsoft Query 中为创建的任何查询保存 .dqy 文件。

使用 .oqy 查询文件 可以保存 .oqy 文件以连接到 OLAP 数据库中的数据，既可以在服务器上，也可以是脱机多维数据集文件 (.cub) 。 使用 Microsoft Query 中的多维连接向导为 OLAP 数据库或多维数据集创建数据源时，会自动创建一个 .oqy 文件。 由于 OLAP 数据库不是按记录或表组织的，因此无法创建查询或 .dqy 文件来访问这些数据库。

使用 .rqy 查询文件 Excel 可以打开 .rqy 格式的查询文件，以支持使用此格式的 OLE DB 数据源驱动程序。 有关详细信息，请参阅驱动程序文档。

使用 .qry 查询文件 Microsoft Query 可以打开和保存 .qry 格式的查询文件，以便与无法打开 .dqy 文件的早期版本的 Microsoft Query 一起使用。 如果想要在 Excel 中使用 .qry 格式的查询文件，请在 Microsoft Query 中打开该文件，然后将其另存为 .dqy 文件。 有关保存 .dqy 文件的信息，请参阅 Microsoft 查询帮助。

使用 .iqy Web 查询文件 Excel 可以打开 .iqy Web 查询文件以从 Web 检索数据。 有关详细信息，请参阅 从 SharePoint 导出到 Excel。

使用外部数据属性

外部数据区域 (也称为查询表) 是定义的名称或表名称，用于定义导入工作表中的数据的位置。 连接到外部数据时，Excel 会自动创建外部数据区域。 唯一的例外是连接到数据源的数据透视表，它不创建外部数据区域。 在 Excel 中，可以设置外部数据区域的格式和布局，也可以在计算中使用它，就像处理任何其他数据一样。

Excel 自动命名外部数据区域，如下所示：

  • 外部数据范围（从 Office 数据连接 (ODC) 文件的名称与文件名相同。
  • 数据库的外部数据区域使用查询的名称命名。 默认情况下，Query_from_source 是用于创建查询的数据源的名称。
  • 文本文件中的外部数据范围使用文本文件名命名。
  • Web 查询中的外部数据范围使用从中检索数据的网页的名称命名。

如果工作表具有多个来自同一源的外部数据区域，则对这些区域进行编号。 例如，MyText、MyText_1、MyText_2 等。

外部数据区域具有其他属性， (不要与可用于控制数据的连接属性) 混淆，例如保留单元格格式和列宽。 可以通过单击“数据”选项卡上“连接”组中的“属性”，然后在“外部数据区域属性”或“外部数据属性”对话框中进行更改来更改这些外部数据区域属性。

“外部数据区域属性”对话框的示例 “外部范围属性”对话框的示例

Excel Services 中的数据源支持

有多个数据对象 (，例如外部数据区域和数据透视表) ，可用于连接到不同的数据源。 但是，每个数据对象之间可以连接到的数据源类型不同。

可以在 Excel Services 中使用和刷新连接的数据。 与任何外部数据源一样，可能需要对访问权限进行身份验证。 有关详细信息，请参阅刷新 Excel 中的外部数据连接。有关凭据的详细信息，请参阅 Excel Services 身份验证设置。

下表汇总了 Excel 中的每个数据对象支持哪些数据源。

Excel
表
对象		 创建
外部
表
范围？		   OLE
DB		 ODBC 文本
文件		 HTML
文件		 XML
文件		 SharePoint
列表
导入文本向导 是 否 否 是 否 否 否
数据透视表
(非 OLAP)		 否 是 是 是 否 否 是
数据透视表
(OLAP)		 否 是 否 否 否 否 否
Excel 表格 是 是 是 否 否 是 是
XML 映射 是 否 否 否 否 是 否
Web 查询 是 否 否 否 是 是 否
数据连接向导 是 是 是 是 是 是 是
Microsoft 查询 是 否 是 是 否 否 否

注意

这些文件（使用“导入文本向导”导入的文本文件、使用 XML 映射导入的 XML 文件以及使用 Web 查询导入的 HTML 或 XML 文件）不使用 ODBC 驱动程序或 OLE DB 提供程序来建立与数据源的连接。

Excel 表格和命名范围的 Excel Services 解决方法

如果要在 Excel Services 中显示 Excel 工作簿，可以连接并刷新数据，但必须使用数据透视表。 Excel Services 不支持外部数据区域，这意味着 Excel Services 不支持连接到数据源的 Excel 表格、Web 查询、XML 映射或 Microsoft 查询。

但是，可以通过使用数据透视表连接到数据源来解决此限制，然后将数据透视表设计和布局为不带级别、组或分类汇总的二维表，以便显示所有所需的行和列值。 

ODBC 和 OLE DB 数据访问组件

让我们深入了解数据库内存。

关于 MDAC、OLE DB 和 OBC

首先，为所有首字母缩略词道歉。 Microsoft 数据访问组件 (MDAC) 2.8 包含在 Microsoft Windows 中。 使用 MDAC，您可以连接并使用来自各种关系和非关系数据源的数据。 你可以使用开放式数据库连接 (ODBC) 驱动程序或 OLE DB 提供程序（由 Microsoft 构建和交付或由各种第三方开发）连接到许多不同的数据源。 安装 Microsoft Office 时，系统会向计算机添加其他 ODBC 驱动程序和 OLE DB 提供程序。

若要查看计算机上安装的 OLE DB 提供程序的完整列表，请显示数据链接文件中的 “数据链接属性 ”对话框，然后单击“ 提供程序 ”选项卡。

若要查看计算机上安装的 ODBC 提供程序的完整列表，请显示“ ODBC 数据库管理器 ”对话框，然后单击“ 驱动程序 ”选项卡。

你还可以使用来自其他制造商的 ODBC 驱动程序和 OLE DB 提供程序从 Microsoft 数据源以外的来源获取信息，包括其他类型的 ODBC 和 OLE DB 数据库。 有关安装这些 ODBC 驱动程序或 OLE DB 提供程序的信息，请查阅数据库文档或与数据库供应商联系。

使用 ODBC 连接到数据源

在 ODBC 体系结构中，应用程序 (（如 Excel) ）连接到 ODBC 驱动程序管理器，而 ODBC 驱动程序管理器又使用特定的 ODBC 驱动程序 (（如 Microsoft SQL ODBC 驱动程序) ）连接到数据源 (如Microsoft SQL Server数据库) 。

若要连接到 ODBC 数据源，请执行下列操作：

  1. 确保在包含数据源的计算机上安装了相应的 ODBC 驱动程序。
  2. 通过使用 ODBC 数据源管理器 将连接信息存储在注册表或 DSN 文件中，或使用 Visual Basic 代码中的连接字符串来定义 DSN) (Microsoft名称连接信息以将连接信息直接传递给 ODBC 驱动程序管理器。
    若要定义数据源，请在 Windows 中单击“开始”按钮，然后单击“控制面板”。 单击 “系统和维护”，然后单击 “管理工具”。 单击“ 性能和维护”，单击 “管理工具”。 ，然后单击“ ODBC) ” (“数据源 ”。 有关不同选项的详细信息，请单击每个对话框中的“ 帮助 ”按钮。

机器数据源

计算机数据源使用用户定义的名称将连接信息存储在特定计算机上的注册表中。 只能在定义机器数据源的计算机上使用机器数据源。 机器数据源分为两种类型，用户和系统。 用户数据源只能由当前用户使用，并且只对该用户可见。 系统数据源可由计算机上的所有用户使用，并且对计算机上的所有用户可见。

当您想要提供增强的安全性时，计算机数据源特别有用，因为它有助于确保只有登录的用户才能查看计算机数据源，并且远程用户无法将计算机数据源复制到另一台计算机。

文件数据源

文件数据源 (也称为 DSN 文件，) 将连接信息存储在文本文件（而不是注册表）中，并且通常比计算机数据源使用更灵活。 例如，您可以将文件数据源复制到具有正确 ODBC 驱动程序的任何计算机，以便您的应用程序可以依赖到它使用的所有计算机的一致且准确的连接信息。 也可以将文件数据源置于一台服务器上，在网络上的多个计算机之间共享，并轻松地将连接信息保留在一个位置。

文件数据源也可以是不可共享的。 不可共享的文件数据源驻留在一台计算机上，并指向计算机数据源。 可以使用不可共享的文件数据源访问来自文件数据源的现有机器数据源。

使用 OLE DB 连接到数据源

在 OLE DB 体系结构中，访问数据的应用程序称为数据使用者 (（如 Excel) ），允许本机访问数据的程序称为数据库提供程序 (（如 Microsoft OLE DB Provider for SQL Server) ）。

通用数据链接文件 (.udl) 包含数据使用者通过该数据源的 OLE DB 提供程序访问数据源时使用的连接信息。 可以通过执行下列操作之一来创建连接信息：

  • 在“数据连接向导”中，使用 “数据链接属性 ”对话框为 OLE DB 提供程序定义数据链接。 
  • 创建一个文件扩展名为 .udl 的空白文本文件，然后编辑该文件，该文件将显示 “数据链接属性” 对话框。

另请参阅

Microsoft Power Query for Excel 帮助