苗木清单 |
编码 | 名称 | 规格 | 计量
单位 | 数量 | 实际单价 | 评估金额 | 迁移补偿比% | 迁移补偿金额 |
胸径(CM) | 地径(CM) | 苗高(CM) | 冠幅(CM) |
茶5 | 茶花 | 5 | | | | 株 | 22 | 27.82 | 612 | 30 | 183.6 |
茶6 | 茶花 | 6 | | | | 株 | 13 | 27.5 | 357.5 | 30 | 107.25 |
| | | | | | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | | |
| | | | | | | | | | | |
求助:按序时帐中“A列编码”和“J列实际单价”归类汇总“数量”、“评估金额”、“迁移补偿金额”, | |
如序时帐中第4行与第6行虽然编码都是“茶5”、但实际单价经树型调整系数调整后,两者不一样,不要汇总在一起 |
最终汇总表中结果如下图 | | | | | | | | |
编码 | 名称 | 规格 | 计量
单位 | 数量 | 实际单价 | 评估金额 | 迁移补偿比% | 迁移补偿金额 |
胸径(CM) | 地径(CM) | 苗高(CM) | 冠幅(CM) |
茶5 | 茶花 | 5 | | | | 株 | 8 | 24 | 192 | 30 | 57.6 |
茶6 | 茶花 | 6 | | | | 株 | 13 | 27.5 | 357.5 | 30 | 107.25 |
茶5 | 茶花 | 5 | | | | 株 | 14 | 30 | 420 | 30 | 126 |
下面代码运行结果是第4、5行,烦请帮我修改下面代码最终结果如第15、16、17行一样 | | | |
Private Sub Worksheet_Activate() | | | | | | | | |
Dim rng As Range | | | | | | | | | | |
Set DIC = CreateObject("scripting.dictionary") '数量汇总 | | | | | |
Set eic = CreateObject("scripting.dictionary") '评估金额汇总 | | | | | |
Set fic = CreateObject("scripting.dictionary") '迁移补偿金额汇总 | | | | |
With Sheets("序时帐") | | | | | | | | | |
a = .Range("a65536").End(xlUp).Row | | | | | | | |
For Each rng In .Range("a4:a" & a) | | | | | | | |
DIC(rng.Value) = DIC(rng.Value) + .Cells(rng.Row, "h") | | | | |
eic(rng.Value) = eic(rng.Value) + .Cells(rng.Row, "K") | | | | |
fic(rng.Value) = fic(rng.Value) + .Cells(rng.Row, "M") | | | | |
i = DIC.items | | | | | | | | | |
ii = eic.items | | | | | | | | | |
iii = fic.items | | | | | | | | |
j = DIC.KEYS | | | | | | | | | |
Next rng | | | | | | | | | | |
Me.Range("a4").Resize(DIC.Count) = Application.Transpose(j) | | | | |
Me.Range("h4").Resize(DIC.Count) = Application.Transpose(i) | | | | |
Me.Range("J4").Resize(DIC.Count) = Application.Transpose(ii) | | | |
Me.Range("L4").Resize(DIC.Count) = Application.Transpose(iii) | | | |
For b = 1 To DIC.Count | | | | | | | | |
nn = Sheets("汇总表").Cells(b + 3, "a") | | | | | | |
Set fd = .UsedRange.Find(nn) | | | | | | | |
.Range("b" & fd.Row & ":g" & fd.Row).Copy Sheets("汇总表").Cells(b + 3, "b") '规格计量单位复制 |
Sheets("汇总表").Cells(b + 3, "i") = Application.WorksheetFunction.Round(Sheets("汇总表").Cells(b + 3, "j") / Sheets("汇总表").Cells(b + 3, "h"), 2) |
Sheets("汇总表").Cells(b + 3, "k") = Application.WorksheetFunction.Round(Sheets("汇总表").Cells(b + 3, "l") / Sheets("汇总表").Cells(b + 3, "j") * 100, 0) |
Next b | | | | | | | | | | |
End With | | | | | | | | | | |
End Sub | | | | | | | | | | | |