统计每分钟内不同ip之间各种连接状态的次数

yd250990477 · 发表于 2020-9-21 00:33

深夜求助,有没有大神帮帮忙。

liulang0808 · 发表于 2020-9-21 07:46

http://club.excelhome.net/thread-1385473-1-1.html
楼主可以参考下这个字典的案例汇总

3190496160 · 发表于 2020-9-21 08:51

Sub tj()
Dim d As Object
Dim br()
Set d = CreateObject("scripting.dictionary")
With Sheet1
r = .Cells(Rows.Count, 1).End(xlUp).Row
ar = .Range("a8:d" & r)
End With
y = 4: k = 1
ReDim br(1 To UBound(ar), 1 To UBound(ar))
br(1, 1) = "DATE": br(1, 2) = "连接次数": br(1, 3) = "中断次数": br(1, 4) = "超时数"
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
      t = d(Trim(ar(i, 1)))
      If t = "" Then
         k = k + 1
         d(Trim(ar(i, 1))) = k
         t = k
         br(k, 1) = ar(i, 1)
      End If
      If Not d.exists(Trim(ar(i, 4))) Then
         y = y + 1
         br(1, y) = ar(i, 4)
         br(t, y) = ar(i, 2)
         d(Trim(ar(i, 4))) = y
      ElseIf d.exists(Trim(ar(i, 4))) Then
         m = d(Trim(ar(i, 4)))
         br(t, m) = br(t, m) & "|" & ar(i, 2)
      End If
End If
Next i
For i = 2 To k
cs1 = 0: cs2 = 0: cs3 = 0
For j = 5 To y
      If InStr(br(i, j), "|") = 0 Then
         If InStr(br(i, j), "连接") > 0 Then
            cs1 = cs1 + 1
         ElseIf InStr(br(i, j), "中断") > 0 Then
            cs2 = cs2 + 1
         ElseIf InStr(br(i, j), "超时") > 0 Then
            cs3 = cs3 + 1
         End If
      ElseIf InStr(br(i, j), "|") > 0 Then
         rr = Split(br(i, j), "|")
         For s = 0 To UBound(rr)
            If InStr(rr(s), "连接") > 0 Then
                  cs1 = cs1 + 1
            ElseIf InStr(rr(s), "中断") > 0 Then
                  cs2 = cs2 + 1
            ElseIf InStr(rr(s), "超时") > 0 Then
                  cs3 = cs3 + 1
            End If
         Next s
      End If
Next j
br(i, 2) = cs1
br(i, 3) = cs2
br(i, 4) = cs3
Next i
With Sheets("sheet2")
.[a1].CurrentRegion = Empty
.[a1].Resize(k, y) = br
End With
End Sub

3190496160 · 发表于 2020-9-21 08:53

sum.zip (17.2 KB, 下载次数: 5)

3190496160 · 发表于 2020-9-21 08:53

为方便写代码，调整了一点表格的呈现方式，自己看看吧

yd250990477 · 发表于 2020-9-21 11:02

3190496160 发表于 2020-9-21 08:53
为方便写代码，调整了一点表格的呈现方式，自己看看吧

好的，多谢版主大大帮忙。
我先学习下，理解下思路和方法

yd250990477 · 发表于 2020-9-22 00:16

yd250990477 发表于 2020-9-21 11:02
好的，多谢版主大大帮忙。
我先学习下，理解下思路和方法

结贴了，再次感谢。
小白一枚，有些代码不去调试真不知道是怎么实现的，怎么得来的。
最终参照大神的思路，将自己真正的工程解决了。
果然，逻辑分析能力比代码能力更重要

。。。

haohu0406 · 发表于 2020-9-22 06:34

Sub sum()
Application.ScreenUpdating = False
Dim arr, qp(1 To 100000, 1 To 11), crr, i, r, c
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.[a1].CurrentRegion
Sheet2.[a1].CurrentRegion.Offset(1).ClearContents
For i = 2 To UBound(arr)
sr = Split(arr(i, 4), ".")(3)
c = InStr("ip1ip2ip3ip4ip5ip6ip7ip8", sr) / 3'得到qp的列标
If d.exists(arr(i, 1)) Then
r = d(arr(i, 1))'得到qp的列标
Select Case arr(i, 2)
Case Is = "超时"
qp(r, c) = arr(i, 2)
qp(r, 11) = qp(r, 11) + 1
Case Is = "连接"
If qp(r, c) = "" Then
qp(r, c) = arr(i, 2)
End If
qp(r, 9) = qp(r, 9) + 1
Case Is = "中断"
If qp(r, c) = "" Then
qp(r, c) = arr(i, 2)
End If
qp(r, 10) = qp(r, 10) + 1
End Select
Else
k = k + 1
d(arr(i, 1)) = k'得到qp的行标
Select Case arr(i, 2)
Case Is = "超时"
qp(k, c) = arr(i, 2)
qp(k, 11) = qp(k, 11) + 1
Case Is = "连接"
If qp(k, c) = "" Then
qp(k, c) = arr(i, 2)
End If
qp(k, 9) = qp(k, 9) + 1
Case Is = "中断"
If qp(k, c) = "" Then
qp(k, c) = arr(i, 2)
End If
qp(k, 10) = qp(k, 10) + 1
End Select
End If
Next
Sheet2.[a2].Resize(d.Count) = Application.Transpose(d.keys)
Sheet2.[b2].Resize(r, 11) = qp
Application.ScreenUpdating = True
End Sub

复制代码

haohu0406 · 发表于 2020-9-22 06:40

以下是附件，说明：已将数据表中数据置顶至第一行

yd250990477 · 发表于 2020-9-22 17:37

3190496160 发表于 2020-9-21 08:51
Sub tj()
Dim d As Object
Dim br()

y = 4: k = 1
ReDim br(1 To UBound(ar), 1 To UBound(ar))

我有好几万行数据，这样申请数组，这个会内存溢出怎么办啊

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

[求助] 统计每分钟内不同ip之间各种连接状态的次数

评分

评分