Excel公式/ VBA代码,仅对仅包含值的单元格求和(跳过包含公式的任何单元格)?

是否有一个公式只对包含值的单元格求和(不是带公式的单元格)?例如,在电子表格的A列中,我有输入值和返回值的公式的混合。如果我在末尾使用求和公式,它将自然地对所选数组中的所有数字求和,无论它们是输入值还是由公式得到的值。 (也许某种SUMIF和VBA代码组合......)如果我的描述不清楚,这里是一个假设的电子表格设置,我需要这个公式:
      A
1|  400
2|  =SUM(B1:B3)
3|  =AVERAGE(B1:B3)
4|  200
5|  100
6|  =COUNT(B1:B3)
7|  I want the sum Formula in this cell (A7) to return the value 700 (the sum of the values above).
    
已邀请:
澄清马丁的答案。 无法使用Excel公式知道单元格是否包含公式。 您必须定义UDF(用户定义的函数)。这里的教程。 。 从教程: 打开一个新的工作簿。 进入VBA(按Alt + F11) 插入新模块(插入>模块) 复制并粘贴Excel用户定义的函数示例 退出VBA(按Alt + Q) 使用这些功能(它们将出现在“用户定义”类别下的“粘贴功能”对话框中,Shift + F3) 您的UDF将如下所示:
Public Function isformula(rng As Range) As Variant()
    Dim aryIn() As Variant
    Dim a As Variant
    Dim i As Integer, j As Integer
    Dim temp() As Variant

    aryIn = rng.Value
    ReDim temp(LBound(aryIn) To UBound(aryIn), _
               LBound(aryIn, 2) To UBound(aryIn, 2))
    For i = LBound(aryIn) To UBound(aryIn)
        For j = LBound(aryIn, 2) To UBound(aryIn, 2)
            If (Left(rng(i, j).Formula, 1) = "=") Then
               temp(i, j) = True
            Else
               temp(i, j) = False
            End If
        Next j
    Next i
    isformula = temp()
End Function
然后你可以在你的代码中使用它。就像是:
{=SUM(IF(NOT(isformula(A1:A6)),A1:A6,0))}
大括号{}表示ARRAY公式(通过Ctrl-Shift-Enter输入) HTH!     
如果您对所有功能使用SUBTOTAL,则可以执行此操作。 SUBTOTAL将忽略该范围内的任何其他SUBTOTAL函数。在A2
=SUBTOTAL(9,B1:B3)
在A3
=SUBTOTAL(1,B1:B3)
在A6
=SUBTOTAL(2,B1:B3)
在A7
=SUBTOTAL(9,A1:A6)
A7将是700(这是我认为你的意思)。如果您的公式不是SUBTOTAL中的选项,那么它将无效。     
有一个HasFormula属性,您可以将其与SUMIF结合起来,做您想做的事情。     
这将有效,虽然不知何故它感觉草率,肯定有一个更好的方法。通过一些额外的工作,您可以将其转换为UDF。
Sub SumNumbersOnly()
    Dim sumAllCells As Long
    Dim sumFormulaCells As Long
    Dim sumNumberCells As Long

    sumAllCells = Application.Sum(Selection)
    sumFormulaCells = Application.Sum(Selection.Cells.SpecialCells(xlCellTypeFormulas))

    sumNumberCells = sumAllCells - sumFormulaCells

    Debug.Print sumNumberCells //Returns 700 (400 + 200 + 100 as in your example)

End Sub
    

要回复问题请先登录注册