|
本帖最后由 micch 于 2019-10-18 16:05 编辑
透视表可以多个工作表生成一个透视表,多字段透视表;不太会,只好代码做一个了,函数一步到位怕是做不到了,函数需要通过辅助来完成
- Sub test()
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- For Each sh In Sheets
- If sh.Name <> "排名" Then
- arr = Intersect(sh.UsedRange, sh.[a:c])
- For i = 1 To UBound(arr)
- If InStr("语文@数学@英语", arr(i, 2)) Then
- d(sh.Name & "@" & arr(i, 1)) = arr(i, 3) + d(sh.Name & "@" & arr(i, 1))
- End If
- Next
- End If
- Next
- With Sheets("排名")
- .[b2].Resize(d.Count, 3).ClearContents
- .[b2].Resize(d.Count) = Application.Transpose(d.keys)
- .[d2].Resize(d.Count) = Application.Transpose(d.items)
- .[a2].Resize(d.Count).Formula = "=row()-1"
- .[b2].Resize(d.Count).TextToColumns OtherChar:="@"
- .[b2].Resize(d.Count, 3).Sort .[d1], 2
- End With
- Application.DisplayAlerts = True
- End Sub
复制代码 |
|