Excel中的对数平均预设功能-使用范围作为输入值 [英] Logarithmic averaging preset function in excel - using ranges as input values

问题描述

我需要添加自己的对数平均函数至excel，但我不确定如何将值范围作为输入值或如何计算给定范围内的值数.我有少量的编程经验.

I need to add my own function for logarithmic averaging to excel but i'm not sure how to have a range of values as an input value or how to make count the number of values in a given range. I have a small amount of experience with programming.

我通常在excel中使用并希望实现为预设函数的公式如下:

The formula i usually use in excel and am looking to implement as a pre-set function is the following:

= 10 * LOG(SUM(10 ^('取值范围'/10)/'取值范围内的数值'))

=10*LOG(SUM(10^('range of values'/10)/'number of values in the range'))

有人可以帮我吗?

推荐答案

您可以尝试执行此操作，您可能需要进行调整以解决空白单元格或范围内的非文本

You can try this, you may need to adjust to account for blank cells or non-text in the range

Function TestUDF(rngValues As Range) As Double

    Dim lSumofValues As Long
    Dim lCountofValues As Long
    Dim rngLoop As Range

    lSumofValues = 0
    lCountofValues = rngValues.Count 'Get count of values in items in range

'Add up the values in the range
    For Each rngLoop In rngValues
        lSumofValues = lSumofValues + rngLoop.Value
    Next
'Perform Calculation
    TestUDF = 10 * Log((10 ^ (lSumofValues / 10) / lCountofValues))

End Function

然后只需在一个单元格中输入= TestUDF(A1:A18)即可使用它.

And then simply enter =TestUDF(A1:A18) in a cell to use it.

这篇关于Excel中的对数平均预设功能-使用范围作为输入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！