根据多个条件计算不同值的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25198949/
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
VBA code to count distinct values based on multiple criteria
提问by user3921129
I have a data sheet "orders" which has data in the format
我有一个数据表“订单”，其中包含以下格式的数据
order no Customer Sales Executive Order Status Order Date
211 nokia john cancelled 23-May-13
643 panasonic andrew fulfilled 23-May-13
209 samsung john fulfilled 4-Apr-14
453 philips andrew fulfilled 4-Apr-14
311 dell mary fulfilled 16-Apr-14
865 panasonic andrew fulfilled 16-Apr-14
201 apple john fulfilled 3-May-14
453 hp mary cancelled 3-May-14
205 nokia john fulfilled 4-May-14
643 philips andrew fulfilled 4-May-14
312 lenovo mary fulfilled 22-May-14
204 apple john fulfilled 7-Jun-14
432 hp mary fulfilled 7-Jun-14
214 nokia john pending 25-Jun-14
754 panasonic andrew fulfilled 25-Jun-14
Above are the columns that matter out of the many columns in the order sheet.
以上是订单中众多列中重要的列。
I have another worksheet where I have the "Sales Executives" listed and want to know how many unique customers they had orders fulfilled by month
我有另一个工作表，其中列出了“销售主管”，并想知道他们按月完成了多少唯一客户的订单
Sales Executive Apr-14 May-14 Jun-14
john <value> <value> <value>
mary <value> <value> <value>
andrew <value> <value> <value>
I want to code to read the sales executive name in the row and month in the column and then give the answer like below
我想编码以读取行中的销售主管姓名和列中的月份，然后给出如下答案
Sales Executive Apr-14 May-14 Jun-14
john 1 2 1
andrew 2 2 1
mary 1 1 1
I am looking for a vba code that can run this on a monthly basis. The above example is a sample set of the actual data.
我正在寻找一个可以按月运行的 vba 代码。上面的例子是实际数据的样本集。
I am relatively new to VBA and need help with the code.
我对 VBA 比较陌生，需要代码方面的帮助。
It would be helpful if I get an explanation on the code as how it work as I need similar code to find how many products and total revenue generated by each sales executive for the months.
如果我得到有关代码如何工作的解释会很有帮助，因为我需要类似的代码来查找每个销售主管在几个月内产生的产品数量和总收入。
Thanks for your help in advance
提前感谢您的帮助
EDIT(code from OP comment below):
编辑（来自下面OP评论的代码）：
Sub UniqueReport()
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim varray As Variant, element As Variant
Dim lastrow As Long
lastrow = Sheets("Orders").Range("N" & Rows.Count).End(xlUp).Row varray = Sheets("Orders").Range("N2:N" & lastrow).Value
For Each element In varray
If dict.exists(element) Then
dict.Item(element) = dict.Item(element) + 1
Else
dict.Add element, 1
End If
Next
ActiveSheet.Range("P2").Value = dict.Count
End Sub
回答by Our Man in Bananas
You can do this without VBA just using Worksheet Formulas and Functions.
您可以在没有 VBA 的情况下仅使用工作表公式和函数来完成此操作。
On your main data sheet, add a column next to the date column and in the cell for row 2 paste this formulal:
在主数据表上，在日期列旁边添加一列，并在第 2 行的单元格中粘贴以下公式：
=TEXT(E2,"mmm yyyy")
this will make the month and year available for a comparison
这将使月份和年份可供比较
Next on your Sales Executivesworksheet, enter this formula in the cell B2
接下来在您的销售主管工作表上，在单元格 B2 中输入此公式
=COUNTIFS(Orders!$C2:$C,$A2,Orders!$F:$F,B)
so what is this formula doing?
那么这个公式在做什么呢？
In the first criteria we specify that we want to compare the name in A2 against all the values in column C:C, ANDwe want to compare the month in ROW A against the TEXT month we have placed in column F on the Orders sheet.
在第一个条件中，我们指定我们要将 A2 中的名称与 C:C 列中的所有值进行比较，并且我们要将 ROW A 中的月份与我们放置在订单表 F 列中的 TEXT 月份进行比较。
Then you can drag the formula across for the months that you need, and down for each Sales Executive
然后，您可以将公式拖到您需要的月份，然后向下拖到每个销售主管
Also, see:
另请参阅：
EDIT:
编辑：
You could also do this programmatically:
您也可以以编程方式执行此操作：
- Add a dummy column with
TEXTformula to give a Month and Year date for comparison on the Ordersworksheet. - Add the formulas on the Sales Executivessheet
- Copy paste values over the formulas
- delete the dummy column
- 添加一个带有
TEXT公式的虚拟列，以提供月和年日期以在订单工作表上进行比较。 - 在销售主管工作表上添加公式
- 在公式上复制粘贴值
- 删除虚拟列
try this:
尝试这个：
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
With Sheets("Orders")
.Columns("E:E").Insert Shift:=xlToRight
.Range("E2:E" & .Range("D2").End(xlDown).Row).FormulaR1C1 = "=TEXT(RC[1],""mmm yyyy"")"
End With
With Sheets("Sales Executives")
.Range("B2:D" & .Range("A2").End(xlDown).Row).FormulaR1C1 = "=COUNTIFS(Orders!C3,RC1,Orders!C5,R1C)"
.Range("B2:D" & .Range("A2").End(xlDown).Row).Copy
.Range("B2:D" & .Range("A2").End(xlDown).Row).PasteSpecial Paste:=xlPasteValues
End With
Sheets("Orders").Columns("E:E").Delete Shift:=xlToLeft
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
So all you need to do is step through the code using F8 to see what it does.
因此，您需要做的就是使用 F8 单步执行代码以查看它的作用。
Another option of course is to make the calculations in your SQL Server query that retrieves teh data using the SQL PIVOT function.
当然，另一种选择是在使用 SQL PIVOT 函数检索数据的 SQL Server 查询中进行计算。
Hope that helps
希望有帮助
