如何使用vba将excel自动筛选并对筛选的数据列求均值？
如果我有一列数，要求是将这一列数先降序排列，然后将前80%的数据标红，之后将这些标红的数据筛选出来，计算均值
Sub macro1()
'1、在对话框中输入列标，如果出现错误就就行处理
c = InputBox("请输入需要操作的列标。如“A”", , "A")
If c = "" Then Exit Sub
If Right(c, 1) = "0" Or Right(c, 1) = "1" Or Right(c, 1) = "2" Or _
Right(c, 1) = "3" Or Right(c, 1) = "4" Or Right(c, 1) = "5" Or _
Right(c, 1) = "6" Or Right(c, 1) = "7" Or Right(c, 1) = "8" Or _
Right(c, 1) = "9" Then MsgBox "列表输入错误！": Exit Sub
cel = c & 2
On Error Resume Next
Set dyg = Range(cel)
If Err.Number <> 0 Then MsgBox "列表输入错误！": Exit Sub
cel1 = c & 1
qy1 = "$" & c & "$" & "1:" & cel
qy2 = c & ":" & c
Set lie = Range(dyg, dyg.End(xlDown))
liehao = dyg.Column + 1
'2、升序排列（有表头）
lie.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range(cel1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(dyg, dyg.End(xlDown))
.Apply
End With
'3、设置条件格式，标注从上到下总数的80%的数字为填充色是绿色
gs1 = "=ROUND(100*COUNT(" & qy1 & ")/COUNT(" & qy2 & "),0)<=80"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=gs1
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Interior.Color = 5287936
Selection.FormatConditions(1).StopIfTrue = False
'4、求这些数字的平均值及其个数
Columns(liehao).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(1, liehao) = c & "列表注绿色的平均值"
gs2 = "=AVERAGE(INDIRECT(""" & cel & ":" & c & """&ROUND(COUNT(" & qy2 & ")*0.8,)+1))"
Cells(2, liehao) = gs2
Cells(4, liehao) = c & "列表注绿色的个数"
Cells(5, liehao) = "=ROUND(COUNT(" & qy2 & ")*0.8,)"
Columns(liehao).EntireColumn.AutoFit
Cells(2, liehao).Select
End Sub
录制宏，然后修改一下就可以了。