Excel VBA“分组依据”之类的选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9226201/
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 VBA "Group by"-like selection
提问by maephisto
I want to get data from an Excel worksheet in an SQL-like mode. Meaning, I have following columns: code, type, name, product_code, price, quantity.
我想以类似 SQL 的模式从 Excel 工作表中获取数据。意思是，我有以下列：code, type, name, product_code, price, quantity.
What I want to do is get the data from this sheet like it was a db table and I execute the following:
我想要做的是从这张表中获取数据，就像它是一个 db 表一样，我执行以下操作：
select code, type, name, product_code, sum(price), sum(quantity) from table
group by code, type, name, product_code
How can I do this in VBA?
我怎样才能在 VBA 中做到这一点？
回答by MatBailie
Order your data by code, type, name, product_code, then write a loop that iterates through every row on the sheet, aggregating as it goes. When any of those four fields changes value, you're in a new group, so write out your current aggregate and start again on the current row.
按 对您的数据进行code, type, name, product_code排序，然后编写一个循环，该循环遍历工作表上的每一行，并进行聚合。当这四个字段中的任何一个更改值时，您就在一个新组中，因此写出您当前的聚合并从当前行重新开始。
There is not, to my knowledge, any way to write SQL against an Excel sheet in VBA; you just write the loop yourself.
据我所知，没有任何方法可以在 VBA 中针对 Excel 工作表编写 SQL；您只需自己编写循环。
Personally, I'd avoid VBA and use a pivot table.
就个人而言，我会避免使用 VBA 并使用数据透视表。
回答by onedaywhen
You can query the worksheet using Access SQL e.g. using ADO classic. However, because the data types are determined at run-time using the current state of the data and registry keys (which may result in values coerced to the wrong type and/or nulls where values cannot be coerced), it is perhaps better to import the data into a table in a SQL database so that data types can be specified at design-time, for which SQL Server is well suited.
您可以使用 Access SQL 查询工作表，例如使用 ADO classic。但是，因为数据类型是在运行时使用数据和注册表项的当前状态确定的（这可能导致值被强制转换为错误类型和/或值不能被强制转换的空值），所以导入可能更好将数据放入 SQL 数据库中的表中，以便可以在设计时指定数据类型，SQL Server 非常适合这种类型。
回答by klausnrooster
SQLite for excelwill let you do it in-memory. I'm not exactly sure what-all LINQ provides to the .NET crowd, but it seems you can emulate some of it using SQLite. I used it just enough to verify it works, but haven't gotten around to building a utility class around it.
SQLite for excel可以让你在内存中完成。我不确定 LINQ 为 .NET 人群提供了什么，但似乎您可以使用 SQLite 模拟其中的一些。我用它来验证它是否有效，但还没有开始围绕它构建实用程序类。
