Excel 函数对工作表数据进行类似 SQL 的查询? [英] Excel function to make SQL-like queries on worksheet data?

问题描述

我在 Excel 工作表中有一个较大的表格:

I have a largish table in an Excel worksheet:

Column_1 | Column_2 | Column_3

ValueA       ValueB     ValueC
....

我需要的是一个函数，它将范围和类似 SQL 的查询字符串作为输入并返回与查询匹配的行范围，例如:

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_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?

推荐答案

您可以使用位于 Excel 2010 的数据"选项卡中的 Get External Data(尽管它的名称)来设置工作簿中的 connection 用于从自身查询数据.使用From Other Sources From Microsoft Query连接Excel

You can use Get External Data (dispite its name), located in the 'Data' tab of Excel 2010, to set up a connection in a workbook to query data from itself. Use From Other Sources From Microsoft Query to connect to Excel

设置后，您可以使用 VBA 操作 connection 以查看和修改驱动查询的 SQL 命令等.此查询确实引用内存工作簿，因此不需要保存即可刷新最新数据.

Once set up you can use VBA to manipulate the connection to, among other thing, view and modify the SQL command that drives the query. This query does reference the in memory workbook, so doen't require a save to refresh the latest data.

这是一个快速的Sub来演示访问连接对象

Here's a quick Sub to demonstrate accessing the connection objects

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

这篇关于Excel 函数对工作表数据进行类似 SQL 的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！