Excel VBA“按组别”选择 [英] Excel VBA "Group by"-like selection

问题描述

我想以类似SQL的方式从Excel工作表获取数据。意思是，我有以下列：代码，类型，名称，产品代码，价格，数量。

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.

要做的是从这个表格中获取数据，就像是一个db表，我执行以下操作：

What I want to do is get the data from this sheet like it was a db table and I execute the following:

select code, type, name, product_code, sum(price), sum(quantity) from table
group by code, type, name, product_code

如何在VBA中执行此操作？

How can I do this in VBA?

推荐答案

通过，然后编写一个循环，循环遍历工作表上的每一行，汇总。当这四个字段中的任何一个更改值时，您将在一个新的组中，因此请写出当前的汇总，并在当前行重新开始。

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.

没有，据我所知，任何方式可以在VBA中针对Excel表单编写SQL;你只需自己写循环。

There is not, to my knowledge, any way to write SQL against an Excel sheet in VBA; you just write the loop yourself.

个人而言，我会避免使用VBA一个枢轴表。

Personally, I'd avoid VBA and use a pivot table.

这篇关于Excel VBA“按组别”选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！