一个序列中，去掉3个最大值去掉4个最小值，其余数值再求平均值

fruitcake

请问如何写函数？求助！

huaichuan1

1. =(SUM(A1:A10)-SUM(SMALL(A1:A10,{1,2,3,4}))-SUM(LARGE(A1:A10,{1,2,3})))/(COUNT(A1:A10)-7)

复制代码

笨方法

13570449347

假设需要识别的数据为A列Sub CalculateAverageAfterRemovingExtremeValues()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 请根据实际情况修改工作表名称

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:A" & lastRow)

    Dim dataArray() As Double
    dataArray = dataRange.Value

    Dim i As Long
    Dim temp As Double

    ' 去掉3个最大值
    For i = 1 To 3
        For j = LBound(dataArray, 1) To UBound(dataArray, 1) - 1
            If dataArray(j, 1) > dataArray(j + 1, 1) Then
                temp = dataArray(j, 1)
                dataArray(j, 1) = dataArray(j + 1, 1)
                dataArray(j + 1, 1) = temp
            End If
        Next j
    Next i

    ' 去掉4个最小值
    For i = 1 To 4
        For j = LBound(dataArray, 1) To UBound(dataArray, 1) - 1
            If dataArray(j, 1) < dataArray(j + 1, 1) Then
                temp = dataArray(j, 1)
                dataArray(j, 1) = dataArray(j + 1, 1)
                dataArray(j + 1, 1) = temp
            End If
        Next j
    Next i

    ' 计算其余数值的平均值
    Dim sum As Double
    Dim count As Long
    sum = 0
    count = 0

    For i = 4 To UBound(dataArray, 1) - 3
        sum = sum + dataArray(i, 1)
        count = count + 1
    Next i

    Dim average As Double
    If count > 0 Then
        average = sum / count
    Else
        average = 0
    End If

    ' 输出结果
    MsgBox "去掉3个最大值和4个最小值后的平均值是: " & average
End Sub

剑指E

把arr换成你的序列

1. =AVERAGE(DROP(DROP(SORT(TOCOL(arr)),4),-3))

复制代码

ww87725244

1. =(SUM(B2:B12)-SUM(LARGE(B2:B12,ROW(1:3)))-SUM(SMALL(B2:B12,ROW(1:4))))/(COUNT(B2:B12)-7)

复制代码