这是sheet1的
现在我要处理成sheet2,有一些项目是几个人一起做的,用“/”间隔开的怎样才可以求除表2???紧急
excel特殊数据处理
答案:2 悬赏:50
解决时间 2021-02-28 02:28
- 提问者网友:逐野
- 2021-02-27 20:26
最佳答案
- 二级知识专家网友:余生继续浪
- 2021-02-27 20:51
VBA代码如下:
Sub test()
Dim d1 As Object, d2 As Object, mAryA, mAryB(1 To 65535, 1 To 3), mAryC
Dim k As Long, i As Long, j As Long
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
mAryA = [a1].CurrentRegion
k = 0
For i = 2 To UBound(mAryA, 1)
mAryC = Split(mAryA(i, 1), "/")
For j = LBound(mAryC) To UBound(mAryC)
k = k + 1
mAryB(k, 1) = mAryC(j)
mAryB(k, 2) = mAryA(i, 2)
mAryB(k, 3) = mAryA(i, 3)
Next j
Next i
For i = 1 To k
d1(mAryB(i, 1)) = d1(mAryB(i, 1)) + mAryB(i, 2)
d2(mAryB(i, 1)) = d2(mAryB(i, 1)) + mAryB(i, 3)
Next i
With Worksheets("Sheet2")
.UsedRange.ClearContents
.[a1].Resize(1, 3) = mAryA
.[a2].Resize(d1.Count, 3) = Application.Transpose(Array(d1.keys, d1.items, d2.items))
End With
End Sub
Sub test()
Dim d1 As Object, d2 As Object, mAryA, mAryB(1 To 65535, 1 To 3), mAryC
Dim k As Long, i As Long, j As Long
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
mAryA = [a1].CurrentRegion
k = 0
For i = 2 To UBound(mAryA, 1)
mAryC = Split(mAryA(i, 1), "/")
For j = LBound(mAryC) To UBound(mAryC)
k = k + 1
mAryB(k, 1) = mAryC(j)
mAryB(k, 2) = mAryA(i, 2)
mAryB(k, 3) = mAryA(i, 3)
Next j
Next i
For i = 1 To k
d1(mAryB(i, 1)) = d1(mAryB(i, 1)) + mAryB(i, 2)
d2(mAryB(i, 1)) = d2(mAryB(i, 1)) + mAryB(i, 3)
Next i
With Worksheets("Sheet2")
.UsedRange.ClearContents
.[a1].Resize(1, 3) = mAryA
.[a2].Resize(d1.Count, 3) = Application.Transpose(Array(d1.keys, d1.items, d2.items))
End With
End Sub
全部回答
- 1楼网友:滚出爷的世界
- 2021-02-27 21:20
将标准按下图输入,数据如图:
将公式
=if(d2="","",vlookup(d2,if({0,1},a2:a6,b2:b6),2,1))
复制后粘贴到"e2"单元格,如图:
改一个数据后如图:
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯