如何快速统计汇总多级别数据

csconly · 发表于 2024-7-18 17:39

因工作缘故，每月都会不定期收到待处理的风险处理单，虽已建立表格记录，但在分析统计时发现较多问题。如通过count函数只能统计某类型数据，无法进一步分析，WPS和Excel的数据汇总功能都试过，但页面不太理想，而且不能实时更新，希望能录入数据后能自动更新（统计表为独立子表，数据关联其他子表），自动统计分析某地-类型-设备的汇总统计表。多谢各位出手相助的大神们

hugeinfo · 发表于 2024-7-18 17:48

什么情况下是故障

part98 · 发表于 2024-7-18 18:28

=LET(s,DROP(GROUPBY(HSTACK(REDUCE(B2:C2,B3:B22,LAMBDA(x,y,VSTACK(x,IF(y<>"",OFFSET(y,,,,2),TAKE(x,-1))))),D2:D22),HSTACK(D2:D22,E2:E22),HSTACK(COUNTA,COUNT),0,0,-1),1),HSTACK(s,BYROW(TAKE(s,,-2)*{1,-1},SUM)))

quqiyuan · 发表于 2024-7-18 19:38

3190496160 · 发表于 2024-7-19 09:11

Sub 数据分析()
Dim ar As Variant
Dim d As Object, dc As Object
Dim br(), cr()
Set d = CreateObject("scripting.dictionary")
Set dc = CreateObject("scripting.dictionary")
With Sheets("sheet1")
r = .Cells(Rows.Count, 4).End(xlUp).Row
If r < 2 Then MsgBox "数据源为空！": End
ar = .Range("a1:e" & r)
End With
ReDim br(1 To UBound(ar), 1 To 6)
For i = 2 To UBound(ar)
If ar(i, 2) = "" Then ar(i, 2) = ar(i - 1, 2)
If ar(i, 3) = "" Then ar(i, 3) = ar(i - 1, 3)
zd = ar(i, 2) & "|" & ar(i, 3) & "|" & ar(i, 4)
t = d(zd)
If t = "" Then
      k = k + 1
      d(zd) = k
      t = k
      For j = 2 To 4
         br(k, j - 1) = ar(i, j)
      Next j
End If
br(t, 4) = br(t, 4) + 1
If ar(i, 5) <> "" Then
      br(t, 5) = br(t, 5) + 1
      br(t, 6) = br(t, 6) + 0
Else
      br(t, 6) = br(t, 6) + 1
      br(t, 5) = br(t, 5) + 0
End If
Next i
With Sheets("分析")
.[a1].CurrentRegion.Offset(1).Borders.LineStyle = 0
.[a1].CurrentRegion.Offset(1) = Empty
.[a2].Resize(k, UBound(br, 2)) = br
.[a2].Resize(k, UBound(br, 2)).Borders.LineStyle = 1
.[a1].CurrentRegion.Sort .[a1], 2, , , , , , 1 '第一个数据，表示升序还是降序，1为升序，2为降序，第二个数据表示标题行数
End With
MsgBox "ok!"
End Sub

3190496160 · 发表于 2024-7-19 09:12

3190496160 · 发表于 2024-7-19 09:13

数据统计分析.rar (20.99 KB, 下载次数: 2)

3190496160 · 发表于 2024-7-19 09:26

Sub 合并单元格()
Application.DisplayAlerts = False
With Sheets("分析")
For i = k + 1 To 2 Step -1
      zd = .Cells(i, 1) & .Cells(i, 2)
      zf = .Cells(i - 1, 1) & .Cells(i - 1, 2)
      If zd = zf Then
         .Range(.Cells(i - 1, 1), .Cells(i, 1)).Merge
         .Range(.Cells(i - 1, 2), .Cells(i, 2)).Merge
         .Range(.Cells(i - 1, 1), .Cells(i, 1)).Select
         With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MergeCells = True
         End With
         .Range(.Cells(i - 1, 2), .Cells(i, 2)).Select
         With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MergeCells = True
         End With
      End If
Next i
End With
Application.DisplayAlerts = True
End Sub

3190496160 · 发表于 2024-7-19 09:27

增加了合并单元格功能

数据统计分析.rar (23.26 KB, 下载次数: 1)

csconly · 发表于 2024-7-19 09:32

hugeinfo 发表于 2024-7-18 17:48
什么情况下是故障

故障是一个状态，代表需要处理，真实数据涉及ZF内部数据不方便透露，这里用故障来替代

		自动登录	找回密码
密码			免费注册

[求助] 如何快速统计汇总多级别数据

评分

评分