在excel中使用sum函数中超过30个参数 [英] use more than 30 arguments in sum function in excel

问题描述

我想在excel的总和公式中使用超过30个参数，我使用下面的公式： -

I want to use more than 30 arguments in sum formula for excel, I am using below formula:-

=SUM(IF(AND(ISNUMBER($F$73),ISNUMBER($J$73)),PRODUCT($F$73,$J$73/100),0),IF(AND(ISNUMBER($G$74),ISNUMBER($J$74)),PRODUCT($G$74,$J$74),0))

对于30个argumnets将会正常工作，但是超过30个argumemts将会返回错误（ #VALUE ）

Above formula will work fine for 30 argumnets, but for more than 30 argumemts excel will return error(#VALUE)

推荐答案

也许您可以在公式中保存更多字符：

Perhaps you could save further characters in your formula like this:

=(N(F73)*N(J73)+N(G74)*N(J74))/100

可以进一步扩展，但为了清楚起见，我建议您编写一个自定义UDF：

which could be extended much further, but for clarity, I'd suggest writing a custom UDF:

=SPRange((F73,G74),(J73,J74))%

SPRange可以像SUMPRODUCT一样工作，但可以在多个范围内运行： p>

SPRange would work like SUMPRODUCT but operates on multiple ranges:

Function SPRange(Range1 As Range, Range2 As Range) As Double

Dim i As Long, n As Long, rng As Range, Arr() As Double

n = Range1.Count
ReDim Arr(1 To n) As Double

i = 1
For Each rng In Range1
    If IsNumeric(rng.Value2) Then Arr(i) = rng.Value2
    i = i + 1
Next rng

i = 1
SPRange = 0
For Each rng In Range2
    If IsNumeric(rng.Value2) Then SPRange = SPRange + Arr(i) * rng.Value2
    i = i + 1
Next rng

End Function

如果你想扩展到许多范围，您可以通过代码定义名称：

If you want to extend to many ranges you can define names through code:

names.Add "Range1",Range("f73,g74")
names.Add "Range2",Range("j73,j74")

允许超过1000细胞在我的测试。然后只需输入公式：

which allows for over 1000 cells in my tests. Then just enter the formula as:

=SPRange(Range1,Range2)%

这篇关于在excel中使用sum函数中超过30个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！