Excel 函数对工作表数据进行类似 SQL 的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8756802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel function to make SQL-like queries on worksheet data?
提问by Richard H
I have a largish table in an excel worksheet:
我在 Excel 工作表中有一个较大的表格：
Column_1 | Column_2 | Column_3
ValueA ValueB ValueC
....
What I need is a function that will take as input the range and an SQL-like query String and return a range of rows that match the query, e.g:
我需要的是一个函数，它将范围和类似 SQL 的查询字符串作为输入，并返回与查询匹配的行范围，例如：
=SQL_SELECT(A1:C1000, "SELECT * WHERE Column_1 = ValueH AND Column_3 = blah")
Does something like this exist? Or what would be the best way to implement myself?
这样的东西存在吗？或者什么是实现自己的最佳方式？
Thanks
谢谢
采纳答案by chris neilsen
You can use Get External Data(dispite its name), located in the 'Data' tab of Excel 2010, to set up a connectionin a workbook to query data from itself. Use From Other SourcesFrom Microsoft Queryto connect to Excel
您可以使用Get External Data（尽管它的名称）位于 Excel 2010 的“数据”选项卡中，connection在工作簿中设置一个以从其自身查询数据。使用From Other SourcesFrom Microsoft Query连接到Excel
Once set up you can use VBAto manipulate the connectionto, among other thing, view and modify the SQL command that drives the query. This query doesreference the in memory workbook, so doen't require a save to refresh the latest data.
设置完成后，您可以使用它VBA来操作connection、查看和修改驱动查询的 SQL 命令。此查询确实引用了内存工作簿，因此不需要保存即可刷新最新数据。
Here's a quick Subto demonstrate accessing the connection objects
这是一个快速Sub演示访问连接对象的过程
Sub DemoConnection()
Dim c As Connections
Dim wb As Workbook
Dim i As Long
Dim strSQL As String
Set wb = ActiveWorkbook
Set c = wb.Connections
For i = 1 To c.Count
' Reresh the data
c(i).Refresh
' view the SQL query
strSQL = c(i).ODBCConnection.CommandText
MsgBox strSQL
Next
End Sub
回答by Jon Egerton
If you can save the workbook then you have the option to use ADO and Jet/ACE to treat the workbook as a database, and execute SQL against the sheet.
如果您可以保存工作簿，那么您可以选择使用 ADO 和 Jet/ACE 将工作簿视为数据库，并对工作表执行 SQL。
The MSDN information on how to hit Excel using ADO can be found here.
可以在此处找到有关如何使用 ADO 访问 Excel 的 MSDN 信息。
回答by Nguy?n Duy Tuan
If you want run formula on worksheet by function that execute SQL statement then use Add-in A-Tools
如果要按执行 SQL 语句的函数在工作表上运行公式，请使用 Add-in A-Tools
Example, function BS_SQL("SELECT ..."):
例如，function BS_SQL("SELECT ...")：
回答by Nasorenga
Sometimes SUM_IF can get the job done.
有时 SUM_IF 可以完成工作。
Suppose you have a sheet of product information, including unique productIDin column A and unit price in column P. And a sheet of purchase order entries with product IDs in column A, and you want column T to calculate the unit price for the entry.
假设您有一张产品信息表，包括productIDA 列中的唯一信息和 P 列中的单价。A 列中有一张带有产品 ID 的采购订单条目，并且您希望 T 列计算条目的单价。
The following formula will do the trick in cell Entries!T2 and can be copied to the other cells in the same column.
以下公式将在单元格 Entries!T2 中发挥作用，并且可以复制到同一列中的其他单元格。
=SUMIF(Products!$A:$A99,Entries!$A2, Products!$P:99)
Then you could have another column with number of items per entry and multiply it with the unit price to get total cost for the entry.
然后，您可以使用另一列包含每个条目的项目数，并将其与单价相乘以获得条目的总成本。
回答by user4681810
One quick way to do this is to create a column with a formula that evaluates to true for the rows you care about and then filter for the value TRUE in that column.
一种快速的方法是创建一个带有公式的列，该公式对于您关心的行计算为真，然后过滤该列中的值为真。
