☰

在 Excel 中从 SQL Server 导入数据之前动态过滤数据

tag-icon • tag-icon microsoft-excel database spreadsheet sql microsoft-excel-2013
在 Excel 中从 SQL Server 导入数据之前动态过滤数据

我想知道是否有办法在将数据导入 Excel 之前过滤来自 SQL Server 连接的数据。初始导入无关紧要（建立连接时），但我需要 Excel 在随后打开时几乎没有数据，并且仅在选择参数后才导入相关数据。SQL 表可能包含大量数据，性能是一个问题。

因此，如果我将数据导入表格，应用过滤器，例如，国家/地区为英国，关闭文件并在下周再次打开，理想情况下它应该只查询和导入英国的数据。当我选择另一个国家时，它应该只导入该国的数据。我需要从表格中枚举选项。

现在，我不相信表格具有这种功能。但是切片器、数据透视表、PowerView 或 PowerPivot 怎么样？

欢迎提出任何想法。

答案1

您绝对可以使用表格以及 Powerpivot 和数据资源管理器来执行此操作。最简单且目前我最喜欢的方法是在 Excel 中创建参数化查询。为此，请使用查询向导，并在最后为要传递的每个参数添加约束。完成后，您可以编辑连接属性并分配要作为静态值、对话框或电子表格单元格返回的参数。

  1. 使用 MS Query 选择您的数据源 - 这是将参数传回 SQL 所必需的；Data>Other Sources>Microsoft Query。
  2. 选择Data Source，在此处选择您的 SQL 数据源。我通常会将所有这些选项留空，直到进入窗口Finish，然后我会选择View data or edit query in MS Query.
    • 注意：我通常会将我想要的信息从 SQL 预处理到可以在此处连接的单个视图中，但通常可以传递任何选择语句。我还没有尝试调用存储过程，所以我不确定这会如何工作。
  3. 在 MS Query 窗口中，单击Show/Hide Criteria button并选择条件字段。这将调用您的参数并传回 SQL。
    • 示例：如果您有一个日期字段并在条件字段中选择它，则添加值 Between [start] and [end]。这将创建一个参数，MS Query 将要求您填写两个值并立即返回相应的数据集。如果您检查它创建的 SQL 语句，它将添加一个 WHERE(datasource.date Between ? and ?) 子句。
  4. 选择文件 > 将数据返回到 Excel，然后选择表格。
  5. 要让表自动提取查询参数，请选择表中的一个单元格，然后转到数据>属性>连接属性>定义>参数。
  6. 对于每个参数，您可以选择
    1. 提示您提供一个值；或
    2. 使用特定值；或
    3. 从电子表格单元格中获取一个值，并可以选择在该单元格值发生变化时自动更新表格数据。
      • 作为附加选项，您可以在为参数选择的单元格上使用数据验证，从而限制可供选择的值。您甚至可以让验证单元格来自一个由数据库中的值提供的表，从而确保只能选择数据库中保存的值。
  7. 设置每个参数的参数值，然后在所有对话框中单击“确定”。
  8. 最后，为了最小化文件大小，您可以在“连接属性”>“使用情况”选项卡中选择“打开文件时刷新数据”和“保存工作簿前从外部范围删除数据”。这将在文件未使用时转储连接的数据，从而最小化文件大小。

我经常根据参数化查询提供的表创建数据透视表，以返回自动更新的特定日期范围（例如前 6 个月）的值。

编辑： 要返回用于 SQL IN 子句的多个值，您只需修改 MS Query 框中的“条件”窗口。例如，如果您的字段名称为“国家/地区”，请在“条件字段”中选择它，然后在“值”中输入 IN([First],[Second],[Third])。选择“将数据返回到 Excel”，MS Query 将要求手动输入值。返回 Excel 后，您可以再次修改参数，并将它们指向电子表格中的三个单元格，每个单元格都可以通过数据验证进行约束。虽然您可以像这样传递的参数数量可能有限制，但 Excel 可以轻松处理三个，因此再多几个参数也并非不可能。

答案2

如果您不介意使用 VBA，那么一种“轻松”的方法就是动态生成 SQL 查询。

我广泛使用类似下面的工具来生成临时仪表板，这些仪表板不需要像 QlikView 或 Crystal 那样复杂的东西

例如

隐藏工作表“变量”包含一个表“Table_SQL_SALES_EMPLOYEES”。该表由 SQL 填充，仅返回参数选择所需的最小值。类似于SELECT DISTINCT AccountDirector FROM InvoicedSales ORDER BY AccountDirector。

然后，我有一个名为“Sales_Employees”的范围，其引用为=Table_SQL_SALES_EMPLOYEES[AccountDirector]。这映射到一个组合框，该组合框的输入范围设置为“Sales_Employees”。这会将一个数字发送到您选择的列表中所选项目的单元格（在我的情况下为 E5）。要根据原始 SQL 提取返回实际值，您需要使用=OFFSET(Table_SQL_SALES_EMPLOYEES[[#Headers],[AccountDirector]],$E$5,0)。这被命名为“Slp”。

.: 我们现在有一个下拉选择框，其中包含所有可能的帐户主管。还有另一个用于财务季度的下拉选择框，但原理相同 :.

当组合框中的选择发生变化时，它会调用宏“changeFilter()”

Sub changeFilter()
    Dim wb As Workbook
    Set wb = Excel.ActiveWorkbook
    Dim ws As Worksheet
    Set ws = Excel.ActiveWorkbook.Sheets("Lookups")
    Dim conn As Excel.WorkbookConnection
    Dim slp As Name
    Set slp = wb.Names("Slp")
    Dim qtr As Integer
    qtr = wb.Names("qtr").RefersToRange.Value2
    Dim query As String

    ' Adjusted Sales Consolidated
    Set conn = wb.Connections("SQL_ADJUSTEDSALES_CONSOLIDATED")
    query = "SELECT * FROM InvoicedSales WHERE AccountDirector=" & "'" & slp.RefersToRange.Value2 & "'"
    conn.OLEDBConnection.CommandText = query
    conn.Refresh

我基本上是通过将表/数据透视表用硬编码变量连接到数据库来预先配置它们，并选择选项“打开文件时刷新数据”和“保存工作簿之前从外部数据范围中删除数据” - 由于实际上没有保存任何内容，因此这可以使文件大小保持很小。

然后，过滤器更改会使用所选参数动态生成的 SQL 查询覆盖硬编码变量，然后刷新表。

相关内容