如何使用列作为参数在Excel中使用参数化查询？

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列，它可能会显示表中包含该值的行数。就像是：

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?

... 等等。所以，我以为我会使用参数化查询（其中IDVALUE =？），但这会提示我输入参数值，而不是使用左边单元格中的值。有什么方法可以做到这一点吗？

2 个解决方案

#1

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

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

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

根据您提到的我正在使用MS Query，您需要按照以下步骤操作

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

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

1. First go to the Data tab in Excel and Select MS Query under External Data Sources
2. 首先转到Excel中的Data选项卡，然后选择External Data Sources下的MS Query

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

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. 然后会出现一个弹出窗口，要求您暂时添加表格选择任何表格。最好选择行数较少的表。

4. Then a Query window appears with the table you have selected and its data
5. 然后会出现一个查询窗口，其中包含您选择的表及其数据

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. 然后单击视图菜单并取消选中表。这将删除查询窗口中的图形表示，并使您更容易更改您的SQL查询

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. 单击查询窗口中格式菜单下方的SQL按钮。弹出窗口可以根据需要编辑查询。确保为所需的参数添加问号。

7. Then a pop up appears ask you for parameter value(s) enter some valid value for now
8. 然后出现一个弹出窗口，要求您输入参数值，现在输入一些有效值

8. Then the query window shows the result of query based on the parameter entered
9. 然后查询窗口根据输入的参数显示查询结果

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. 单击查询窗口中视图菜单下方的退出按钮。然后关闭查询窗口，弹出导入数据，询问excel中显示结果的位置。相应地选择

10. Then click on Properties button on left before you click OK. A new pop up appears with the default usage tab selected.
11. 然后单击左侧的“属性”按钮，然后单击“确定”。将出现一个新弹出窗口，其中选择了默认用法选项卡。

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. 在此选项卡中进行所需的任何更改，然后单击定义选项卡。单击编辑查询按钮旁边弹出窗口底部的参数按钮

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.

此方法适用于没有VBA经验的人员。如果您了解VBA，请参考此答案以使用VBA实现类似的功能。

#2

So to do this I would create a UDF and have the backend of that function connecting to your access database. Something like this:

所以要做到这一点，我会创建一个UDF，并将该函数的后端连接到您的访问数据库。像这样的东西：

Public Function countMyTable(IDValue As Double) As Long
'Requires:// Microsoft Access 16.0 Object Library
'Requires:// Microsoft Office 16.0 Access database engine Object Library
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.DBEngine.Workspaces(0).OpenDatabase(DBFilePath, False, False)
Set rs = db.OpenRecordset("SELECT COUNT(1) FROM MyTable WHERE IDValue = " & IDValue, dbOpenSnapshot)
rs.MoveLast
countMyTable = rs(0)
db.close
End Function

Hope this helps, TheSilkCode

希望这有助于TheSilkCode