Excel VBA: Scripting.Dictionary 计算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17206419/
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: Scripting.Dictionary Calculations
提问by r1pster
I have the following values in a spreadsheet:
我在电子表格中有以下值：
Printer Name | Pages | Copies
HP2300 | 2 | 1
HP2300 | 5 | 1
Laser1 | 2 | 2
Laser1 | 3 | 4
HP2300 | 1 | 1
How can I get the total number of pages printed (pages * copies) on each printer like this:
如何像这样在每台打印机上打印总页数（页数 * 份数）：
Printer Name | TotalPages |
HP2300 | 8 |
Laser1 | 16 |
I managed to create a list counting the number of times a printer was used to print:
我设法创建了一个列表来计算打印机用于打印的次数：
Sub UniquePrints()
Application.ScreenUpdating = False
Dim Dict As Object
Set Dict = CreateObject("scripting.dictionary")
Dim varray As Variant, element As Variant
varray = Sheets("Prints").Range("E:E").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
Sheets("Stats").Range("D6").Resize(Dict.Count, 1).Value = _
WorksheetFunction.Transpose(Dict.keys)
Sheets("Stats").Range("E6").Resize(Dict.Count, 1).Value = _
WorksheetFunction.Transpose(Dict.items)
Application.ScreenUpdating = True
End Sub
How can I calculate the total pages for each print (row) (pages*copies) and save that in the dictionary instead of just adding 1?
如何计算每次打印（行）（页*份）的总页数并将其保存在字典中而不是仅加 1？
Thank you for your help
感谢您的帮助
采纳答案by Dick Kusleika
Read in the columns E:G rather than just E and use the second dimension of that array to add pages * copies, rather than adding 1.
读入列 E:G 而不仅仅是 E 并使用该数组的第二个维度来添加 pages * 副本，而不是添加 1。
Sub UniquePrints()
Dim Dict As Object
Dim vaPrinters As Variant
Dim i As Long
Set Dict = CreateObject("scripting.dictionary")
vaPrinters = Sheets("Prints").Range("E2:G6").Value
For i = LBound(vaPrinters, 1) To UBound(vaPrinters, 1)
If Dict.exists(vaPrinters(i, 1)) Then
Dict.Item(vaPrinters(i, 1)) = Dict.Item(vaPrinters(i, 1)) + (vaPrinters(i, 2) * vaPrinters(i, 3))
Else
Dict.Add vaPrinters(i, 1), vaPrinters(i, 2) * vaPrinters(i, 3)
End If
Next i
Sheets("Stats").Range("D6").Resize(Dict.Count, 1).Value = _
WorksheetFunction.Transpose(Dict.keys)
Sheets("Stats").Range("E6").Resize(Dict.Count, 1).Value = _
WorksheetFunction.Transpose(Dict.items)
End Sub
回答by Jüri Ruut
It's possible to use an array formula to get cells populated:
可以使用数组公式来填充单元格：
={SUMPRODUCT(IF($A:$A=$F2;1;0);$B:$B;$C:$C)}
The formula is inserted from formula window with Ctrl-Shift-Enter. Curled brackets are inserted by excel, not by a user. The formula can be copied elsewhere.
使用 Ctrl-Shift-Enter 从公式窗口插入公式。花括号由 excel 插入，而不是由用户插入。公式可以复制到别处。
