Excel使用宏获取计数

Question

我有很多行日志，如下所示，需要根据'ServiceReq‘从所有行中获取'tid’的计数。以下为tid 123的示例计数为5，对tid 678的计数为2。

有人能帮我使用excel宏吗？

​

​

示例- Sheet1中的示例日志，A列

INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”678”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”:”tid”:”abc”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”:”tid”:”abc”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”:”tid”:”abc”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”678”

预期产出

总人数123 5 678 2

Answer 1

使用SQL函数获取计数。

您可以更改代码以满足您的需要。

Option Explicit
Dim WB1 As Workbook
Dim ws1 As Worksheet


Sub Test()

Dim MyConnection As ADODB.Connection
Dim MyRecord As ADODB.Recordset
Dim UnionLastRow As Long
Dim CurrentPointer As Long

Set WB1 = ThisWorkbook
Set ws1 = WB1.Worksheets("Sheet5")
Set MyConnection = New ADODB.Connection
Set MyRecord = New ADODB.Recordset

' This is the Excel 97-2003 connection string. It should also work with
' Excel 2007 onwards worksheets as long as they have less than 65536
' rows
With MyConnection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0;"
    .Open
End With

MyRecord.Open "SELECT COUNT (*) AS MyResult FROM [Sheet5$] WHERE [Logs] LIKE '%“ServiceReq”:”tid”%' GROUP BY [Logs]", MyConnection

ws1.Cells(5, 8).CopyFromRecordset MyRecord
MyRecord.Close

MyRecord.Open "SELECT DISTINCT [Logs] FROM [Sheet5$] WHERE [Logs] LIKE '%“ServiceReq”:”tid”%' GROUP BY [Logs]", MyConnection

ws1.Cells(5, 7).CopyFromRecordset MyRecord

MyRecord.Close
MyConnection.Close

End Sub

以下是结果的打印屏幕：

​

​

Answer 2

您可以使用Instr来解决问题。

Option Explicit
Dim wb1 As Workbook
Dim ws1 As Worksheet

Sub Test()
Dim Count As Long
Dim Count2 As Long
Dim Pointer As Long
Dim TotalRow As Long
Dim LetterPosition As Long
Dim LetterPosition2 As Long

Count = 0
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Sheet5")

TotalRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

For Pointer = 1 To TotalRow
LetterPosition = InStr(ws1.Range("A" & Pointer).Value, "“ServiceReq”:”tid”:”123”")
LetterPosition2 = InStr(ws1.Range("A" & Pointer).Value, "“ServiceReq”:”tid”:”678”")
If LetterPosition > 0 Then
Count = Count + 1
LetterPosition = 0
End If

If LetterPosition2 > 0 Then
Count2 = Count2 + 1
LetterPosition2 = 0
End If

Next

ws1.Range("J2").Value = Count
ws1.Range("J3").Value = Count2

End Sub