Excel 公式/VBA 代码将仅对仅包含值的单元格求和（跳过任何包含公式的单元格）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4503077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Formula/VBA code that will sum only cells containing values only (skip any cells containing formulas)?
提问by Hyman
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 列中，我混合了输入值和返回值的公式。如果我在最后使用求和公式，它自然会对所选数组中的所有数字求和，无论它们是输入值还是由公式产生的值。（也许某种 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).
回答by Dick Kusleika
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，则可以执行此操作。SUBTOTAL 将忽略范围内的任何其他 SUBTOTAL 函数。在 A2
=SUBTOTAL(9,B1:B3)
In A3
在 A3
=SUBTOTAL(1,B1:B3)
In A6
在 A6
=SUBTOTAL(2,B1:B3)
In A7
在 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.
A7 将是 700（这就是我假设你的意思）。如果您的公式不是 SUBTOTAL 中的一个选项，那么它将不起作用。
回答by Dr. belisarius
To clarify Martin's answer.
澄清马丁的答案。
There is no way to know if a cell contains a formula using Excel formulas.
无法知道单元格是否包含使用 Excel 公式的公式。
You have to define an UDF (user defined function). Tutorial here.. From the tutorial:
您必须定义一个 UDF（用户定义函数）。教程在这里。. 从教程：
- 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)
- 打开一个新的工作簿。
- 进入 VBA（按 Alt+F11）
- 插入一个新模块（插入 > 模块）
- 复制并粘贴 Excel 用户定义函数示例
- 退出 VBA（按 Alt+Q）
- 使用函数（它们将出现在“用户定义”类别下的“粘贴函数”对话框中，Shift+F3）
Your UDF will look something like:
您的 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
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)
其中大括号 {} 表示 ARRAY 公式（通过 Ctrl-Shift-Enter 输入）
HTH!
哼！
回答by Martin Broadhurst
There is a HasFormula Propertywhich you may be able to combine with SUMIF to do what you want.
有一个HasFormula 属性，您可以将它与 SUMIF 结合起来做您想做的事。
回答by Alex P
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.
这会奏效，但不知何故感觉很草率，肯定有更好的方法。通过一些额外的工作，您可以将其变成 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
