EXCEL插入VB代码段

noodleboy

于 2024-04-18 17:44:04 发布

阅读量167

点赞数 3

文章标签： excel

本文链接：https://blog.csdn.net/noodleboy/article/details/137930441

版权

本文介绍了一个Excel函数，用于计算给定范围内数值的平均值和标准差，同时考虑了空单元格的处理。函数首先检查每个单元格是否为空，然后进行求和、计数和方差计算。

摘要由CSDN通过智能技术生成

函数：返回均方差，并能判断单元格为空

先插入模块，然后再编写代码

Function AverageRange(startCell As Range, endCell As Range) As Double
    Dim rng As Range
    Dim sum As Double
    Dim avg As Double
    Dim std_err As Double
    Dim count As Integer
    
    Set rng = Range(startCell, endCell)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not IsEmpty(cell) Then
            sum = sum + cell.Value
            count = count + 1
        End If
    Next cell
    
    If count > 0 Then
        avg = sum / count
        
        sum = 0
        For Each cell In rng
            If Not IsEmpty(cell) Then
               sum = sum + (cell.Value - avg) ^ 2
            End If
        Next cell
            
        AverageRange = Sqr(sum / count)
    Else
        avg = 0
    End If
    
End Function