查询Excel表格中G列数字的中位数,最大值和最小值,然后弹窗提示数据。
代码如下:
Sub CalculateStatistics()
Dim rng As Range
Dim medianValue As Double
Dim maxValue As Double
Dim minValue As Double
' 定义G列的范围
Set rng = ActiveSheet.Range("G:G")
' 计算中位数,最大值和最小值
medianValue = Application.WorksheetFunction.Median(rng)
maxValue = Application.WorksheetFunction.Max(rng)
minValue = Application.WorksheetFunction.Min(rng)
' 使用消息框显示结果
MsgBox "中位数: " & medianValue & vbCrLf & _
"最大值: " & maxValue & vbCrLf & _
"最小值: " & minValue, vbInformation, "计算结果"
End Sub
查询0-50,51-100,101-800之间的数字有多少个
代码如下,根据自己是实际情况修改数字即可:
Sub CountRanges()
Dim rng As Range
Dim cell As Range
Dim count0to50 As Long
Dim count50to100 As Long
Dim count100to800 As Long
' 定义G列的范围
Set rng = ActiveSheet.Range("G:G")
' 初始化计数器
count0to50 = 0
count50to100 = 0
count100to800 = 0
' 遍历G列的每个单元格
For Each cell In rng
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
Select Case cell.Value
Case 0 To 50
count0to50 = count0to50 + 1
Case 51 To 100
count50to100 = count50to100 + 1
Case 101 To 800
count100to800 = count100to800 + 1
End Select
End If
Next cell
' 使用消息框显示结果
MsgBox "0-50之间的数字数量: " & count0to50 & vbCrLf & _
"51-100之间的数字数量: " & count50to100 & vbCrLf & _
"101-800之间的数字数量: " & count100to800, vbInformation, "统计结果"
End Sub