如何使用列作为参数在Excel中使用参数化查询？ [英] How to use parameterized query in Excel using column as parameter?

问题描述

我正在尝试开发一个可以在外部数据源中找到相应记录的电子表格。所以，我们来说列A列有一个标识值。我想开发列B，这可能会显示表中具有该值的行数。如下：

  AB 
 758348= SELECT COUNT（*）FROM MYTABLE WHERE IDVALUE = $ A $ 1
 173483= SELECT COUNT（*）FROM MYTABLE WHERE IDVALUE = $ A $ 2
  

。 .. 等等。所以，我以为我会使用参数化查询（其中IDVALUE =？），但是提示我输入参数值，不要使用左边的单元格的值。有没有办法这样做？

解决方案

我将使用参数化查询（其中IDVALUE =？），但是提示
我输入参数值

从您所提到的， MS查询，您需要遵循以下步骤

在Excel中进行参数化查询的步骤使用单元格值作为参数

1. 首先转到Excel中的数据选项卡，然后选择外部数据源下的MS查询

2. 出现一个弹出窗口，要求您选择数据源。选择数据源或添加新的数据源并选择它。确保取消选中使用查询向导创建或编辑查询它在大多数情况下不是很有用

3. 然后出现一个弹出窗口，要求您现在添加表选择任何表。最好选择较少行的表。

4. 然后会出现一个查询窗口，并显示您选择的表格及其数据

5. 然后点击视图菜单并取消选中表。这将在查询窗口中删除图形表示，并使您更容易更改您的sql查询

6. 点击 SQL 按钮下方的格式菜单在查询窗口中。弹出窗口可以根据需要编辑查询。确保为所需的参数添加问号。

7. 然后出现一个弹出窗口，要求您输入参数值现在输入一些有效值

8. 然后查询窗口根据输入的参数显示查询结果

9. 点击视图下方的退出按钮菜单在查询窗口中。然后查询窗口关闭，导入数据弹出来询问excel中的哪里显示结果。选择相应的

10. 然后点击左侧的属性按钮，然后单击确定。出现新的弹出窗口，并选择默认使用选项卡。

11. 在此选项卡中进行任何更改，然后单击定义标签。点击按钮按钮按钮按钮按钮 / ol>
    
    
    
    

    12. 参数弹出显示查询中使用的参数。在这里，您必须选择从以下单元格单选按钮获取值，以选择单元格的值作为查询的参数。单击确定，您应该完成。

        
        
        

    
    

    
    
    

    ---

    
    
    

    此方法适用于没有VBA体验的人。如果你知道VBA，那么请参考这个 answer 来实现类似于VBA的东西。

    
    

    I am trying to develop a spreadsheet that can locate corresponding records in an external data source. So, let's say I have Column A with a list of identity values. I want to develop Column B, which perhaps shows a count of rows in the table with that value. Something like:

    A              B
    758348    "=SELECT COUNT(*) FROM MYTABLE WHERE IDVALUE=$A$1"
    173483    "=SELECT COUNT(*) FROM MYTABLE WHERE IDVALUE=$A$2"
    

    ... and so on. So, I thought I would use a parameterized query (where IDVALUE=?), but that prompts me to input the parameter value, not use the value from the cell to the left. Is there any way I can do this?

    解决方案

    I would use a parameterized query (where IDVALUE=?), but that prompts me to input the parameter value

    From what you mentioned I under stand you are using MS Query and you need to follow the following steps

    Steps to make a parameterized query in Excel use a cell value as parameter

    1. First go to the Data tab in Excel and Select MS Query under External Data Sources
    2. A pop up appears asking you to choose data source. Select the data source or add a new data source and select it. Make sure to uncheck Use the query wizard to create or edit queries it is not very useful in most cases
    3. Then a pop up appears asking you to add table select any table for now. It is better to select a table with less rows.
    4. Then a Query window appears with the table you have selected and its data
    5. Then click on the view menu and uncheck tables. This removes the graphically representation in query window and makes it easier to alter you sql query
    6. Click on the SQL button below the format menu in query window. A pop up comes where you can edit the query as you wish. Make sure to add question mark(s) for the parameter(s) you want.
    7. Then a pop up appears ask you for parameter value(s) enter some valid value for now
    8. Then the query window shows the result of query based on the parameter entered
    9. Click on the exit button below the view menu in query window. Then the query window closes and Import Data pop up comes asking where in excel to display the result. Choose accordingly
    10. Then click on Properties button on left before you click OK. A new pop up appears with the default usage tab selected.
    11. Make any changes needed in this tab and click on Definition tab. Click on Parameters button in bottom of the pop up next to edit query button

    12. Parameters pop up comes displaying the parameters used in the query. Here you must select the Get the value from following cell radio button to select the value of cell as the parameter for the query. Click OK and you should be done.

    ---

    This method is for people with no VBA experience. If you know VBA then refer to this answer to achieve the something similar using VBA.

    这篇关于如何使用列作为参数在Excel中使用参数化查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！