excel宏编一个统计最大值、最小值、平均值,并且统计各个区单的数量和比例
时间: 2023-07-10 14:07:22 浏览: 109
以下是一个可以实现统计最大值、最小值、平均值,并且统计各个区单的数量和比例的Excel宏编程代码:
```
Sub Statistics()
Dim i As Long, lastRow As Long
Dim maxVal As Double, minVal As Double, sumVal As Double, avgVal As Double
Dim district As String
Dim districtCount As Long, totalCount As Long
'获取数据的最后一行
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'初始化变量
maxVal = Cells(2, 2).Value
minVal = Cells(2, 2).Value
sumVal = 0
avgVal = 0
districtCount = 0
totalCount = 0
For i = 2 To lastRow
'计算最大值
If Cells(i, 2).Value > maxVal Then
maxVal = Cells(i, 2).Value
End If
'计算最小值
If Cells(i, 2).Value < minVal Then
minVal = Cells(i, 2).Value
End If
'计算总和
sumVal = sumVal + Cells(i, 2).Value
'计算区单数量和比例
district = Cells(i, 1).Value
If district <> "" Then
districtCount = districtCount + 1
totalCount = totalCount + 1
Range("D" & districtCount + 1).Value = district
Range("E" & districtCount + 1).Value = Range("E" & districtCount + 1).Value + 1
Else
Range("E" & districtCount + 1).Value = Range("E" & districtCount + 1).Value + 1
End If
Next i
'计算平均值
avgVal = sumVal / (lastRow - 1)
'输出结果
Range("I2").Value = maxVal
Range("I3").Value = minVal
Range("I4").Value = avgVal
Range("I5").Value = totalCount
For i = 2 To districtCount + 1
Range("F" & i).Value = Range("E" & i).Value / totalCount
Next i
End Sub
```
该宏的逻辑是遍历数据表格,依次计算最大值、最小值、平均值,并且统计各个区单的数量和比例。在代码中,我们假设数据表格的第一列为区单列,第二列为数值列,最终结果输出在I列和F列。在运行宏之前,需要先选中数据表格。