使用 Microsoft Query 检索外部数据

可以使用 Microsoft Query 从外部源检索数据。 通过使用 Microsoft Query 从公司数据库和文件中检索数据,不必重新键入想要在 Excel 中分析的数据。 每当使用新信息更新数据库时,您也可以从原始源数据库自动刷新 Excel 报表和摘要。

使用 Microsoft Query,可以连接到外部数据源,从这些外部源选择数据,将数据导入工作表,并根据需要刷新数据,使工作表数据与外部源中的数据保持同步。

可以访问的数据库类型     可以从多种类型的数据库检索数据,包括 Microsoft Office Access、Microsoft SQL Server 和 Microsoft SQL Server OLAP 服务。 您也可以从 Excel 工作簿和文本文件中检索数据。

Microsoft Office提供了可用于从以下数据源检索数据的驱动程序:

  • Microsoft SQL Server Analysis Services (OLAP 提供程序 )

  • Microsoft Office Access

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • 小队

  • 文本文件数据库

也可使用 ODBC 驱动程序或其他制造商的数据源驱动程序从此处未列出的数据源(包括其他类型的 OLAP 数据库)检索信息。 有关安装此处未列出的 ODBC 驱动程序或数据源驱动程序的信息,请查看数据库的文档,或联系数据库供应商。

从数据库选择数据     通过创建查询从数据库检索数据,这是有关存储在外部数据库中的数据的问题。 例如,如果数据存储在 Access 数据库中,可能需要了解特定产品(按区域)的销售数据。 通过仅选择要分析的产品和地区的数据,可以检索数据的一部分。

使用 Microsoft Query,可以选择需要的数据列,并仅将数据导入到 Excel 中。

通过一个操作更新工作表     在 Excel 工作簿中拥有外部数据后,每当数据库发生更改时, 刷新 这些数据来更新分析 , 而无需重新创建摘要报表和图表。 例如,可以创建每月销售摘要,每月当新的销售数据进入时刷新它。

Microsoft Query 如何使用数据源     为特定数据库设置数据源后,每当想要创建查询以从该数据库选择和检索数据时,都可以使用它 , 而无需重新键入所有连接信息。 Microsoft Query 使用数据源连接到外部数据库,并展示哪些数据可用。 创建查询并返回 Excel 后,Microsoft Query 会向 Excel 工作簿提供查询和数据源信息,以便当您要刷新数据时可以重新连接到数据库。

Query 如何使用数据源的图示

使用 Microsoft Query 导入数据     若要使用 Microsoft Query 将外部数据导入 Excel,请按照以下基本步骤操作,以下各节将更详细地介绍每个步骤。

什么是数据源?     数据源是一组存储的信息,可让 Excel 和 Microsoft Query 连接到外部数据库。 使用 Microsoft Query 设置数据源时,需要为数据源提供一个名称,然后提供数据库或服务器的名称和位置、数据库的类型以及登录和密码信息。 此信息还包括 OBDC 驱动程序或数据源驱动程序的名称,该驱动程序是一种连接到特定数据库类型的程序。

若要使用 Microsoft Query 设置数据源,

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"来自其他源",然后单击"从Microsoft 查询"。

  2. 执行下列操作之一:

    • 若要指定数据库、文本文件或 Excel 工作簿的数据源,请单击"数据库 " 选项卡。

    • 若要指定OLAP 多维数据集,请单击 "OLAP 多维数据集" 选项卡。 只有从 Excel 运行 Microsoft Query 时,此选项卡才可用。

  3. 双击"<数据源">。

    - 或 -

    单击<数据源">,然后单击"确定"。

    将显示 "新建数据源 "对话框。

  4. 在步骤 1 中,键入用于标识数据源的名称。

  5. 在步骤 2 中,单击用作数据源的数据库类型的驱动程序。

    注意: 

    • 如果要访问的外部数据库不受随 Microsoft Query 一起安装的 ODBC 驱动程序的支持,则需要从第三方供应商(例如数据库制造商)获取并安装 Microsoft Office 兼容的 ODBC 驱动程序。 有关安装说明,请与数据库供应商联系。

    • OLAP 数据库不需要 ODBC 驱动程序。 安装 Microsoft Query 时,会为使用 Microsoft SQL Server Analysis Services 创建的数据库安装Microsoft SQL Server Analysis Services。 若要连接到其他 OLAP 数据库,需要安装数据源驱动程序和客户端软件。

  6. 单击 "连接",然后提供连接到数据源所需的信息。 对于数据库、Excel 工作簿和文本文件,您提供的信息取决于所选的数据源类型。 系统可能会要求你提供登录名、密码、使用的数据库版本、数据库位置或其他特定于数据库类型的信息。

    重要: 

    • 使用由大写字母、小写字母、数字和符号组合的强密码。 弱密码不混合使用这些元素。 强密码:Y6dh!et5。 弱密码:House27。 密码应至少包含 8 个字符。 最好使用包含 14 个或更多字符的密码。

    • 记住密码是非常重要的。 如果您忘记了密码,Microsoft 无法为您找回。 请将记好的密码保存在安全位置,远离密码所要保护的信息。

  7. 输入所需信息后,单击"确定"或"完成"返回到"新建数据源"对话框。

  8. 如果数据库包含表,并且您希望特定表自动显示在查询向导中,请单击步骤 4 的框,然后单击要显示的表。

  9. 如果不想在使用数据源时键入登录名和密码,请选中"在数据源定义中保存我的用户 ID 和密码"复选框。 保存的密码未加密。 如果该复选框不可用,请与数据库管理员联系以确定此选项是否可用。

    安全说明: 在连接到数据源时避免保存登录信息。 此信息可能以纯文本格式存储,恶意用户可以访问该信息,以损害数据源的安全性。

完成这些步骤后,数据源的名称会显示在"选择数据源 " 对话框中。

对大多数查询使用查询向导     使用查询向导可以轻松选择和汇集数据库中不同表和字段的数据。 使用查询向导,可以选择要包括的表和字段。 内部联接 (查询操作,指定当向导识别一个表中的主键字段和第二个表中具有相同名称的字段时,会自动根据相同的字段值合并两个表中的行) 。

此外,还可使用向导对结果集和进行简单筛选。 在向导的最后一个步骤中,可以选择将数据返回到 Excel,或在 Microsoft Query 中进一步优化查询。 创建查询后,可以在 Excel 或 Microsoft Query 中运行它。

若要启动查询向导,请执行以下步骤。

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"来自其他源",然后单击"从Microsoft 查询"。

  2. "选择数据源 "对话框中,确保选中"使用查询向导创建 /编辑 查询"复选框。

  3. 双击想要使用的数据源。

    - 或 -

    单击想要使用的数据源,然后单击"确定"。

直接在 Microsoft Query 中处理其他类型的查询     如果要创建比查询向导允许的更复杂的查询,可以直接在 Microsoft Query 中工作。 可以使用 Microsoft Query 查看和更改在查询向导中开始创建的查询,或者无需使用向导即可创建新查询。 若要创建执行以下操作的查询,请直接在 Microsoft Query 中工作:

  • 从字段中选择特定数据     在大型数据库中,可能需要选择字段中的一些数据,并省略不需要的数据。 例如,如果在包含许多产品信息的字段中需要两个产品的数据,可以使用 条件 来仅选择所需的两个产品的数据。

  • 每次运行查询时,根据不同的条件检索数据     如果需要为同一外部数据中的多个区域创建相同的 Excel 报表或摘要(例如每个区域单独的销售报表),可以创建一个 参数查询 。 运行参数查询时,当查询选择记录时,系统会提示输入一个值作为条件。 例如,参数查询可能会提示输入特定区域,可以重复使用此查询来创建每个区域销售报表。

  • 以不同方式联接数据     查询向导创建的内部联接是用于创建查询的最常见联接类型。 但是,有时你想要使用不同类型的联接。 例如,如果您有产品销售信息表和客户信息表,则内部联接 (查询向导) 创建的类型将阻止为尚未进行购买的客户检索客户记录。 使用 Microsoft Query,可以联接这些表,以便检索所有客户记录,以及已购买的客户的销售数据。

若要启动 Microsoft Query,请执行以下步骤。

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"来自其他源",然后单击"从Microsoft 查询"。

  2. "选择数据源 "对话框中,确保清除"使用查询向导创建 /编辑 查询"复选框。

  3. 双击想要使用的数据源。

    - 或 -

    单击想要使用的数据源,然后单击"确定"。

重新使用和共享查询     在查询向导和 Microsoft Query 中,都可以将查询另存为可修改、重复使用和共享的 .dqy 文件。 Excel 可以直接打开 .dqy 文件,这样你或其他用户就可以从同一查询创建其他外部数据范围。

若要从 Excel 打开保存的查询,请执行:

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"来自其他源",然后单击"从Microsoft 查询"。 将显示 "选择数据源 "对话框。

  2. 在" 选择数据源 "对话框中,单击 "查询" 选项卡。

  3. 双击要打开的已保存查询。 该查询显示在 Microsoft Query 中。

如果要打开已保存的查询并且 Microsoft Query 已打开,请单击"Microsoft查询文件"菜单,然后单击"打开"。

如果双击 .dqy 文件,Excel 将打开,运行查询,然后将结果插入到新工作表中。

如果要共享基于外部数据的 Excel 摘要或报表,您可以为其他用户提供包含外部数据范围的工作簿,也可以创建一个 模板 。 使用模板可以保存摘要或报表,而无需保存外部数据,以便文件更小。 当用户打开报表模板时,将检索外部数据。

在查询向导或 Microsoft Query 中创建查询后,可以将数据返回到 Excel 工作表。 然后,数据将成为 外部数据区域 或 数据透视表 格式和刷新的数据。

设置检索到的数据的格式     在 Excel 中,可以使用图表或自动小计等工具来显示和汇总 Microsoft Query 检索的数据。 可以设置数据格式,刷新外部数据时将保留格式。 可以使用自己的列标签而不是字段名称,并自动添加行号。

Excel 可以自动设置在区域末尾键入的新数据的格式,以匹配前面的行。 Excel 还可以自动复制前面行中重复的公式,并扩展到其他行。

注意: 为了扩展到区域的新行,格式和公式必须至少出现在前五行中的三个行中。

你随时都可以 (或再次) 此选项:

  1. 单击“文件”>“选项”>“高级”。

    在 Excel 2007 :单击Microsoft Office 按钮 Office 按钮图像 ,单击 "Excel选项",然后单击"高级 " 类别。

  2. "编辑选项" 部分中,选择" 扩展数据区域格式和公式" 检查。 若要再次关闭自动数据区域格式,请清除此复选框。

刷新外部数据     刷新外部数据时,运行查询以检索与规范匹配的任何新数据或已更改的数据。 可以在 Microsoft Query 和 Excel 中刷新查询。 Excel 提供了多个用于刷新查询的选项,包括每次打开工作簿时刷新数据,以及定期自动刷新数据。 在刷新数据时,可以继续在 Excel 中工作,还可以在刷新数据时检查状态。 有关详细信息,请参阅"在 Excel 中刷新外部数据连接"。

返回页首

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×