使用 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

  • Paradox

  • 文本文件数据库

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

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

通过 Microsoft Query,你可以选择所需的数据列,并仅将数据导入 Excel。

在一个操作中更新工作表     在 Excel 工作簿中有外部数据后,无论何时更改数据库,您都可以 刷新 数据来更新分析,而无需重新创建汇总报表和图表。 例如,您可以创建每月销售汇总,并在每月的新销售数字到来时进行刷新。

Microsoft Query 如何使用数据源     为特定数据库设置数据源后,只要要创建查询以从该数据库中选择和检索数据,而无需重新输入所有连接信息,就可以使用该数据源。 Microsoft Query 使用数据源连接到外部数据库,并显示哪些数据可用。 创建查询并将数据返回到 Excel 后,Microsoft Query 将为 Excel 工作簿提供查询和数据源信息,以便您可以在需要刷新数据时重新连接到数据库。

Query 如何使用数据源的图示

使用 Microsoft Query 导入数据     若要将外部数据导入 Excel 和 Microsoft Query,请执行以下基本步骤,这些基本步骤将在以下部分中更详细地介绍。

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

要使用 Microsoft Query 设置数据源,请执行以下操作:

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

  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 创建的数据库安装驱动程序。 若要连接到其他 OLAP 数据库,您需要安装数据源驱动程序和客户端软件。

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

    重要: 

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

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

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

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

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

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

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

对大多数查询使用 "查询向导"     "查询向导" 使您可以轻松地从数据库中的不同表和字段中选择和收集数据。 使用 "查询向导",可以选择要包含的表和字段。 内部联接(指定两个表中的行基于相同的字段值组合的查询操作)在向导识别一个表中的主键字段和另一个表中具有相同名称的字段时自动创建。

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

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

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

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

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

    -或者-

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

直接在 Microsoft Query 中处理其他类型的查询     如果要创建比 "查询向导" 允许的查询更复杂的查询,可以直接在 Microsoft Query 中进行处理。 你可以使用 Microsoft Query 查看和更改你在 "查询向导" 中创建的查询,也可以在不使用向导的情况下创建新查询。 如果要创建执行下列操作的查询,请直接在 Microsoft Query 中工作:

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

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

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

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

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

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

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

    -或者-

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

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

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

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

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

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

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

如果双击 .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 支持专员。

×