Option Explicit
Function is_exists(name As String)
Dim sht As Worksheet
For Each sht In Worksheets
If sht.name = name Then
is_exists = True
Exit Function
End If
Next
is_exists = False
End Function
Sub 分组统计()
Dim LastRow, LastCol As Long
Dim Sh As Worksheet
'Sh指代当前活动页
Set Sh = Sheets("data")
'当前活动页的最后一行
LastRow = Sh.Cells(Rows.Count, 1).End(xlUp).row
'当前活动页的最后一列
LastCol = Sh.Cells(1, Columns.Count).End(xlToLeft).Column
'定义D为字典
Dim D As Object
Set D = CreateObject("Scripting.Dictionary")
Dim row, i As Integer
Dim key, value As String
For i = 2 To LastRow
key = Sh.Cells(i, 3).value
value = Sh.Cells(i, 4).value
'如果在字典里
If Not D.exists(key) Then
D.Add key, Array(0, 0, 0)
End If
row = D(key)
If value = "A区" Then
row(0) = row(0) + 1
ElseIf value = "B区" Then
row(1) = row(1) + 1
ElseIf value = "C区" Then
row(2) = row(2) + 1
End If
D(key) = row
Next
'调试输出字典存储的内容
For Each key In D.keys()
Debug.Print key & "," & Join(D(key), ",")
Next
Dim sht As Worksheet
If is_exists("result") Then
Sheets("result").Delete
End If
'在最后的位置增加一个sheet作为结果表
Sheets.Add After:=Sheets(Sheets.Count)
Set sht = Sheets(Sheets.Count)
sht.name = "result"
'屏幕刷新=false
Application.ScreenUpdating = False
'下面写出数据到结果表中,首先写出标题行
sht.Range("A1").Resize(1, 4) = Application.Transpose(Array("deal_date", "A区", "B区", "C区"))
sht.Range("A2").Resize(D.Count, 1) = Application.Transpose(D.keys)
i = 2
For Each row In D.items()
sht.Cells(i, 2).Resize(1, 3) = row
i = i + 1
Next
Application.ScreenUpdating = True
End Sub
运行前:
点击按钮运行后:
立即窗口和工作表都看到了正确的结果输出,立即窗口看到重复2次的输出是因为我连续运行了两次。
Python实现分组计数
实现代码:
import csv
from collections import namedtuple
result ={}
columns =["A区","B区","C区"]
areas_map =dict(zip(columns,range(len(columns))))withopen("data.csv", encoding="gb18030")as f:
f_csv = csv.reader(f)
headers =next(f_csv)
resultSet = namedtuple("resultSet", headers)for r in f_csv:
row = resultSet(*r)
areas = result.setdefault(row.deal_date,[0,0,0])
areas[areas_map[row.area]]+=1
result