Excel 公式/VBA 代码仅对包含值的单元格求和（跳过任何包含公式的单元格）？

发布于 2024-10-08 08:59:54 字数 394 浏览 7 评论 0原文

是否有一个公式只能对包含值的单元格（而不是具有公式的单元格）求和？例如，假设在电子表格的 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).

原文

Is there a formula that will sum ONLY cells that contain a value (not cells with a formula)? For example, say in column A of a spreadsheet I have a mixture of entered values and formulas that return values. If I use a sum formula at the end it will naturally sum all of the numbers in the selected array regardless of whether they are entered values or values resulting from a formula. (Maybe some kind of SUMIF & VBA code combo..) In case my description wasn't clear, here is a hypothetical spreadsheet set-up where i would need this formula:

      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).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

极度宠爱 2024-10-15 08:59:54

如果您对所有函数都使用 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 中不存在，那么它将不起作用。

If you use SUBTOTAL for all of your functions, you can do it. SUBTOTAL will ignore any other SUBTOTAL functions in the range. In A2

=SUBTOTAL(9,B1:B3)

In A3

=SUBTOTAL(1,B1:B3)

In A6

=SUBTOTAL(2,B1:B3)

In A7

=SUBTOTAL(9,A1:A6)

A7 will be 700 (which is what I assume you meant). If you have formulas that aren't an option in SUBTOTAL, then it won't work.

回复收藏 0 原文

夜清冷一曲。 2024-10-15 08:59:54

澄清马丁的答案。

使用 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！

To clarify Martin's answer.

There is no way to know if a cell contains a formula using Excel formulas.

You have to define an UDF (user defined function). Tutorial here. .
From the tutorial:

Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
Copy and Paste the Excel user defined function examples
Get out of VBA (Press Alt+Q)
Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

Your UDF will look something like:

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

Then you may use it in your code. Something like:

{=SUM(IF(NOT(isformula(A1:A6)),A1:A6,0))}

Where the braces {} indicate an ARRAY formula (entered by Ctrl-Shift-Enter)

HTH!

回复收藏 0 原文

雨巷深深 2024-10-15 08:59:54

您可能有一个 HasFormula 属性能够与 SUMIF 结合做你想做的事。

回复收藏 0 原文

暗地喜欢 2024-10-15 08:59:54

这会起作用，尽管不知何故感觉很草率，而且肯定有更好的方法。通过一些额外的工作，您可以将其变成 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

This will work, though somehow it feels sloppy and there must surely be a better way. With a little extra work you could make this into a 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

回复收藏 0 原文

~没有更多了~