创建参数查询

在 Excel 中查询数据时,可能需要使用输入值-a 参数来指定有关查询的内容。 若要执行此操作,请创建参数查询。 创建参数查询的方式以及它们的行为方式取决于您使用的是 Microsoft Query 还是 Power Query。

提示: Power Query 参数与基于 SQL 的查询中使用的参数有很大区别。 此外,如果只需要筛选数据,则可以使用查询(而不是实际参数)。 请考虑阅读 Power query 示例部分,然后再在 Power Query 中创建参数。

Microsoft Query

Power Query

参数如何影响查询

在查询的 WHERE 子句中使用参数–它们始终用作检索数据的筛选器。

可以在任何查询步骤中使用参数。 除了用作数据筛选器,参数还可用于指定文件路径或服务器名称之类的功能。

参数输入选项

当运行或刷新查询时,参数可以提示用户输入值,使用常量作为输入值,或将指定单元格的内容用作输入值。

参数不提示输入。 可以改为使用 Power Query 编辑器更改其值。 或者,可以使用 bona fide 参数,而不是使用可轻松编辑的值引用外部位置的查询。

参数作用域

参数是它修改的查询的一部分,并且不能在其他查询中重复使用。

参数与查询不同-创建后,您可以根据需要向查询添加参数。

  1. 单击 "数据" >获取 & 转换数据> 从Microsoft Query> 的其他来源获取数据>。

  2. 按照 "查询向导" 中的步骤操作。 在 "查询向导-完成" 屏幕上,选择 "在 Microsoft Query 中查看数据或编辑查询",然后单击 "完成"。 "Microsoft Query" 窗口随即打开,并显示您的查询。

  3. 单击 "查看> SQL"。 在出现的 SQL 对话框中,找到 WHERE 子句-一行(通常在 SQL 代码的结尾处)以 word 开头。 如果没有 WHERE 子句,请通过在查询末尾的新行键入一个位置来添加一个子句。

  4. 在 "位置" 下,键入字段名称、比较运算符(=、<、>、LIKE 等)和下列内容之一:

    • 对于泛型参数提示,请键入问号(?)。 运行查询时出现的提示中不显示任何有用的短语。

      MS Query 的 SQL 视图,强调 WHERE 子句

    • 对于帮助用户提供有效输入的参数提示,请键入括在方括号中的短语。 当运行查询时,该短语将显示在参数提示中。

      MS Query 的 SQL 视图,强调 WHERE 子句

  5. 将带有参数的条件添加到 WHERE 子句之后,单击"确定"以运行查询。 Excel 会提示你为每个参数提供一个值,然后 Microsoft Query 将显示结果。

  6. 准备好加载数据时,关闭 "Microsoft 查询" 窗口以将结果返回到 Excel。 将打开 "导入数据" 对话框。

    Excel 中的 "导入数据" 对话框

  7. 若要查看参数,请单击 "属性"。 然后在 "连接属性" 对话框的 "定义" 选项卡上,单击 "参数"。

    "连接属性" 对话框

  8. "参数" 对话框显示查询中使用的参数。 在 "参数名称" 下选择一个参数以查看或更改参数值的获取方式。 你可以更改参数提示、输入特定值或指定单元格引用。

    "MS 查询参数" 对话框

  9. 单击"确定"保存所做的更改并关闭 "参数" 对话框,然后在 "导入数据" 对话框中单击"确定"以在 Excel 中显示查询结果。

现在,你的工作簿具有参数查询。 当运行查询或刷新其数据连接时,Excel 将检查参数以完成查询的 WHERE 子句。 如果参数提示输入值,Excel 将显示 "输入参数值" 对话框以收集输入-您可以键入一个值或单击包含该值的单元格。 您也可以指定所提供的值或引用应始终使用,如果使用单元格引用,您可以指定在指定单元格的值发生更改时,Excel 应自动刷新数据连接(即,再次运行查询)。

注意: 本主题假定你了解如何使用 Power Query 创建到 Access 数据库的连接。 有关详细信息,请参阅连接到 Access 数据库

您可以在更多 Power Query 方案中使用参数,而不仅仅是筛选数据-Power Query 查询的任何步骤都可以有参数。 例如,你可以使用参数指定源步骤中的连接字符串部分,例如文件名。

Power Query 参数具有名称。 若要使用参数,请在公式中按名称引用步骤。 例如,假设您要查看有关您维护的网页的数据,并且希望按发布日期筛选数据。 虽然您始终可以在查询预览中使用内置筛选器,但使用参数为筛选提供日期可节省时间并为您提供更多灵活性。 我们来看看此示例。

在空工作簿中,我们将创建一个与具有所需 web 流量记录的 Access 数据库的连接,包括指示每个页面最初发布时间的字段。 加载到 Power Query 中,其外观如下所示:

Power Query 编辑器描述加载的数据

由于我们希望按日期进行筛选,因此我们将更改所使用的列的数据类型 FirstPublishDate。 它是源中的日期/时间数据,但我们不关心每天发布的时间,必须指定它可能会获得 tiresome-因此我们将其更改为日期数据类型。

Power Query 编辑器显示结果

接下来,我们将创建一个用于根据最初发布页面的日期限制结果的参数。 单击 "家庭>参数" > "管理参数" 以打开 "参数" 对话框。

Power Query 参数对话框

单击 "新建",窗体将显示名为 Parameter1 的新参数,无其他信息。

我们会更改一些参数属性:

  • 名称更改为FirstPubD

  • 说明更改为首次发布页面的日期。

  • 将 "类型" 更改为 "日期",以便参数仅接受日期值

  • 设置当前值,以便在我们未提供输入时,该参数不筛选出所有行-我们使用1/1/2010。

提示: 名称和说明应提供足够的上下文,以帮助用户了解如何使用该参数以及为什么要使用该参数。 即使你是唯一将使用该参数的人员,你也可能需要一段时间的提醒。

单击"确定"以创建该参数,并在 Power Query 编辑器中查看该参数。

Power Query 编辑器显示参数

现在,我们的参数在 "查询" 面板中列出-我们可以选择它以在主面板中显示它,也可以右键单击它以查看更多选项。 选择某个参数后,可以编辑主面板中的当前值,或单击 "管理参数" 以更改其其他设置。

现在,我们可以在原始查询中使用此参数。 我们在 "查询" 面板中单击原始查询以显示它。 我们希望使用参数基于第一个发布的日期筛选结果,接下来,我们将选择 " FirstPublishDate " 列,单击列标题右边缘的 "筛选/排序" 箭头,指向 "日期筛选",然后单击 "之后 ..."。

Power Query 编辑器显示日期筛选器菜单

在 "筛选行" 对话框中,从筛选器的选项列表中选择 "参数"。

"筛选行" 对话框

输入或选择值将替换为可用参数列表。 只有一种刚刚创建的 FirstPubD。

显示所选参数的 "筛选行" 对话框

选中它,然后单击"确定"。 Power Query 编辑器使用新参数作为筛选器加载查询。

Power Query 编辑器显示筛选结果

若要测试该参数,请将其值更改为1/1/2018

Power Query 编辑器显示参数

我们刷新查询,该查询现在仅显示1/1/2018 之后具有 FirstPublishDate 的行。

Power Query 编辑器显示筛选结果

现在,我们有一个使用参数按日期筛选的查询。 若要通过 FirstPublishDate 筛选结果,我们不再需要查找该域,请单击 "筛选/排序" 箭头,选择 "之后 ..." 筛选器类型,然后输入日期值-我们只需更改 FirstPubD 的值并刷新查询即可。 此外,我们可以重复使用新参数,例如,如果我们决定将另一组字段从原始数据源提取到新工作表中,但仍希望包含 FirstPubDate 并使用它来筛选结果。

参数非常有用,但我们仍然必须使用 Power Query 编辑器更改参数的值。 我们希望能够在不打开 Power Query 编辑器的情况下更改筛选值。 若要执行此操作,我们将在工作表上创建一个表,在其中加载查询以及与该表建立新的 Power Query 连接,然后使用新查询筛选主查询。

在加载查询的工作表上,在导入的数据上方插入一些行。 然后,创建一个包含一行的 Excel 表来保存参数值。

显示参数表和从 Power Query 加载的数据的 Excel 工作簿

若要使用新表筛选查询,需要在 Power Query 中连接到查询。 通过选择表,然后单击 "数据" 选项卡上的 "从表/区域",创建与表的连接。 新连接随即打开,并在 Power Query 编辑器中显示新表。

Power Query 编辑器中加载的 Excel 表格数据

由于作为 "日期/时间" 数据类型加载的数据,我们需要将其更改为日期数据类型,以便它与我们的参数匹配,因此我们单击 "开始>转换" >数据类型>日期

将鼠标移到 Power Query 编辑器功能区的 "开始" 选项卡上 "转换" 组中的 "数据类型" 命令上。

我们还会将查询重命名为比 Table2 更有意义的内容。 为此,我们将其命名为 FirstPubDate。

突出显示 "名称" 框的 Power Query 编辑器

由于我们希望传递值,而不是表本身,因此我们需要向下钻取到 date 值。 若要执行此操作,请右键单击预览数据中的值,然后单击 "向下钻取"。

字段值的 Power Query 编辑器上下文菜单

预览现在显示值而不是表格。

Power Query 编辑器,显示单个日期值

我们不需要将新查询的数据加载到任何位置-它的数据已在需要它的工作表上。 我们只需要连接,即可使用 Power Query 获取参数值。 因此,单击 "文件">关闭 & 加载到 ... "以打开" 导入数据 "对话框,然后选择"仅创建连接"。

"导入数据" 对话框,选中 "仅创建连接" 选项

现在,我们有一个名为 "FirstPubDate" 的查询,该查询将从工作表上的表中提取单个日期值,就像我们的主查询在哪里加载。 现在,我们只需将此查询用作筛选主查询的参数。 因此,我们打开主查询并使用 FirstPublishDate 列编辑筛选行的步骤。 展开编辑栏,然后选择先前创建的参数(FirstPubD)。 然后,我们在FirstPubD后键入 "a"-因为新查询的名称以与参数相同的字母开头,Power query 将其显示为要选择的选项。

Power Query 编辑器编辑栏已展开

选中它,然后单击编辑栏外部以应用该步骤。

加载了数据的 Power Query 编辑器

所有内容看起来都是正确的,因此我们退出 Power Query 编辑器并保存所做的更改。 若要测试参数,在报表工作表中,我们将顶部表中的单元格的值更改为5/4/2019,然后刷新连接以查看筛选的数据。

Excel 中筛选的数据

我们的新筛选器有效! 因此,我们保存并关闭工作簿。 现在,使用工作簿的任何人都可以将第一个发布的日期指定为查询筛选器-在该查询加载的同一工作表上。

  1. 单击 "数据" >获取 & 转换数据>获取数据>启动 Power Query 编辑器

  2. 在 Power Query 编辑器中,单击 "开始>参数" > "管理参数"。

  3. 在 "参数" 对话框中,单击 "新建"。

  4. 根据需要设置以下内容:

    • Name -这应该反映参数的函数,但尽量缩短它的长度。

    • 说明-这可能包含可帮助用户正确使用该参数的任何详细信息。

    • 必需-选择以使此参数需要值。

    • 类型-此参数指定参数所需的数据类型。

    • 建议的值-如果需要,请添加值列表或指定查询以提供输入建议。

    • 默认值-此选项仅在建议值设置为值列表时才出现,并且指定哪个列表项是默认值。

    • 当前值-根据使用该参数的位置,查询可能不会返回任何结果。 如果选中了 "必需",则 "当前值" 不能为空。

  5. 单击"确定"创建参数。

  1. 在 Power Query 编辑器中打开查询。

  2. 单击要用于筛选数据的列的标题右边缘的箭头,然后从显示的菜单中选择一个筛选器。

  3. 在 "筛选行" 对话框中,单击筛选条件右侧的按钮,然后执行下列操作之一:

    • 若要使用现有参数,请单击 "参数",然后从右侧显示的列表中选择所需的参数。

    • 若要使用新参数,请单击 "新建参数 ...",然后创建一个参数

  1. 在加载要筛选的查询的工作表上,创建包含两个单元格的表: "标题" 和 "值"。

  2. 单击 "值",然后单击 "数据>获取 &从表/区域转换 > 数据"。

  3. 在 Power Query 编辑器中,对表连接进行任何调整(例如,更改数据类型或名称),然后单击 "开始>关闭" >关闭 & 加载">关闭" & 加载到 ..."。

  4. 在 "导入数据" 对话框中,单击 "仅创建连接",选择 "添加到数据模型",然后单击"确定"

  5. 在 Power Query 编辑器中打开要筛选的查询。

  6. 单击要用于筛选数据的列的标题右边缘的箭头,然后从显示的菜单中选择一个筛选器。

  7. 请执行下列操作之一:

    • 从值的下拉列表中选择一个值(这些值来自查询的数据)。

    • 使用筛选条件右边缘的按钮选择一个值。

  8. 单击编辑栏右边缘的箭头以显示整个查询。

  9. 筛选条件遵循每个词:

    • 要筛选的列的名称显示在方括号中。

    • 比较运算符紧跟在列名称之后。

    • 筛选值紧跟在比较运算符之后,在右括号结束。 选择 "整个值"。

  10. 开始键入您刚创建的表连接的名称,然后从显示的列表中选择它。

  11. 单击 "开始" >关闭>关闭 & "加载"。

    查询现在使用您创建的表中的值来筛选查询结果。 若要使用新值,请编辑单元格内容,然后刷新查询。

另请参阅

创建下拉列表

注意:  本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 能否告知我们此信息是否有所帮助? 下面是该参考内容的英文版

需要更多帮助?

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

此信息是否有帮助?

谢谢您的反馈!

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

×