Excel宏_实现对动态数据求均值和落在特定范围的数据的个数

5月16号晚，同学叫我帮忙写一个Excel宏，实现对动态数据的求均值和落在特定范围的数据的个数。由于我在大二学过VB（那时学VB主要是为了应付学校的计算机二级考试，过后就没动过了），但对VBA从没碰过，匆忙间也就在Microsoft 的在线帮助中看看，搞到早上5点左右，终于搞定，今天在此记下代码，以后如果有用，就方便了，如果他人觉得有用，请注明出处，谢谢！

 

Sub QIU_ZHI()

'要求：选中你要处理的数据，数据请不要放在前4列中

'功能：对数据求均值、0.9*均值、1.1*均值和落在这个范围内的数据的个数。

'注明：均值在3行3列，0.9*均值在4行3列，1.1*均值在5行3列，落在这个范围内的数据的个数在6行3列

'edit by Hsxzhe 080517

 

  Dim mycell As Range                    '声明mycell是一个区域变量

 

  '声明各变量，由于对VBA常用函数、属性不熟悉，故此宏中定义了大量变量来存储需要处理的数据

  Dim data_sum As Double                 '总和

  Dim pin_jun As Double                  '均值

  Dim zero_p_nine_pinjun As Double       '0.9*均值

  Dim one_p_one_pinjun As Double         '1.1*均值

  Dim fanwei_feshu As Integer            '落在这个范围内的数据的个数

 

  Dim row_start As Integer               '标记所选区域的开始行号

  Dim row_end As Integer                 '标记所选区域的结束行号

  Dim column_start As Integer            '标记所选区域的开始列号

  Dim column_end As Integer              '标记所选区域的结束列号

  Dim aero_num As Integer                '标记所选区域的总个数

 

  Dim row_temp As Integer

  Dim column_temp As Integer

 

 

  '初始化变量

  data_sum = 0

  pin_jun = 0

  zero_p_nine_pinjun = 0

  one_p_one_pinjun = 0

  fanwei_geshu = 0

 

 

  '求出总和

  aero_num = 0

  For Each mycell In Selection

      If aero_num = 0 Then

         row_start = mycell.Row

         column_start = mycell.Column

      End If

      data_sum = data_sum + mycell.Value

      aero_num = aero_num + 1

      row_end = mycell.Row

      column_end = mycell.Column

  Next

 

  '求出个数据

  pin_jun = data_sum / aero_num

  zero_p_nine_pinjun = 0.9 * pin_jun

  one_p_one_pinjun = 1.1 * pin_jun

 

 

  row_temp = 0

  column_temp = 0

  '求出落在这个范围内的数据的个数

  For row_temp = row_start To row_end

    For column_temp = column_start To column_end

      If Cells(row_temp, column_temp).Value > zero_p_nine_pinjun And Cells(row_temp, column_temp).Value < one_p_one_pinjun Then

         fanwei_geshu = fanwei_geshu + 1       '落在范围内，则统计个数

      End If

    Next

  Next

 

  Cells(3, 3) = pin_jun

  Cells(4, 3) = zero_p_nine_pinjun

  Cells(5, 3) = one_p_one_pinjun

  Cells(6, 3) = fanwei_geshu

 

 

 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''下面的是垃圾，只是格式处理''''''''''''''''''''''''''

Cells(3, 2) = "均值"

Cells(4, 2) = "0.9*均值"

Cells(5, 2) = "1.1*均值"

Cells(6, 2) = "范围个数"

Range(Cells(3, 2), Cells(6, 3)).Select

Range(Cells(3, 2), Cells(6, 3)).Font.Bold = True

With Selection.Borders(xlEdgeLeft)

    .LineStyle = xlContinuous

    .Weight = xlMedium

    .ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeTop)

    .LineStyle = xlContinuous

    .Weight = xlMedium

    .ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeBottom)

    .LineStyle = xlContinuous

    .Weight = xlMedium

    .ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeRight)

    .LineStyle = xlContinuous

    .Weight = xlMedium

    .ColorIndex = xlAutomatic

End With

 

End Sub