在Excel单元格中求和数字（长字符串和短字符串）

Question

我正在进行与频率相关的研究。

我想要将每个单元格中的所有数字相加，并将它们缩减为一个单一的数字。

有些单元格有2个数字，而另一些单元格则有13个数字。就像这些一样...

24.0542653897891
25.4846064424057
27
28.6055035477009

我尝试了几个公式来实现这个目标。最好的公式给我一个两位数，但是我不能将它们相加得到一个单一的结果。

就像下面这些公式：

=SUMPRODUCT(MID(SUBSTITUTE(B5,".",""),ROW(INDIRECT("1:"&LEN(B5)-1)),1)+0)
=SUMPRODUCT(1*MID(C5,ROW(INDIRECT("1:"&LEN(C5))),1))

有什么建议吗？ 非常感谢。

- Saher Al-Sous

---

也许可以使用“文本分列”将数字拆分为单独的单元格？ - Darren Bartrup-Cook

你能否添加期望的结果，以增加清晰度？ - JvdV

同意@JvdV的观点。尽管您的评论“最好的那些有两位数字，我无法将它们相加以获得单个结果。”和您提供的公式让我想到您正在寻找一些公式来保持添加单元格中所有数字并得出结果，直到您获得一个数字。否则，JvdV的公式非常棒。不需要使用VBA。 - Naresh

@NareshBhople 没错...我需要不断添加数字直到得到一个个位数的结果...4或5或6... - Saher Al-Sous

你需要像我下面的答案一样使用UDF。 - Naresh

Answer 1

编辑

根据您的说明和评论，似乎您想要的是所有数字（不包括小数点）的数字根。换句话说，反复求和直到得到一个单独的数字。

这可以通过比加总数字更简单的公式来计算。

=1+(SUBSTITUTE(B5,".","")-1)-(INT((SUBSTITUTE(B5,".","")-1)/9)*9)

对于较长的数字，我们可以将其分成两部分并分别处理。例如：

=1+MOD(1+MOD(LEFT(SUBSTITUTE(B5,".",""),INT(LEN(SUBSTITUTE(B5,".",""))/2))-1,9)+1+MOD(RIGHT(SUBSTITUTE(B5,".",""),LEN(SUBSTITUTE(B5,".",""))-INT(LEN(SUBSTITUTE(B5,".",""))/2))-1,9)-1,9)

然而，数字应该存储为文本。当数字以数字形式存储时，我们看到的可能并不是实际存储的内容以及公式（还有UDF）将要处理的内容。

长公式版本将更正电子表格上除B104之外的所有错误。B104似乎具有值5226.9332653096000，但Excel实际上存储值为5226.9333265309688。由于Excel的精度限制，这将被处理为5226.93332653097。因此会出现不一致现象。

另一种可能有效的方法是将列B中的所有结果舍入到15位数字（例如：）。结合使用长公式版本，应该可以得到您显示的所有值的一致性。

解释：

- 如果一个数字可被9整除，它的数字根将为9，否则数字根将为n MOD 9 - 通用公式为=1+Mod(n-1,9) - 在您的情况下，由于我们正在处理超过使用MOD函数计算的数字的数字，我们需要删除点，并使用等价于mod的内容，即n-(int(n/9)*9) - 注意： - 这将最好与存储为文本的数字一起使用。由于Excel可能会以不同于预期的方式显示和/或转换大型数字或具有许多小数位的数字，因此使用数字的文本串是最稳定的方法。 - 此方法对超过15位的数字不可靠。

如果您有超过15位的数字，则建议使用VBA用户定义函数：

Option Explicit
Function digitalRoot(num As String) As Long
    Dim S As String, Sum As Long, I As Long
S = num
Do While Len(S) > 1
    Sum = 0
    For I = 1 To Len(S)
        Sum = Sum + Val(Mid(S, I, 1))
    Next I
    S = Trim(Str(Sum))
Loop

digitalRoot = CLng(S)

End Function

- Ron Rosenfeld

---

谢谢您的回复。实际上，我想让结果是一个个位数，比如说不是得到73，而是得到1（7+3=10 = 1+0=1）。 - Saher Al-Sous

@SaherAl-Sous，这可以使用更简单的公式来完成。请查看我的编辑。 - Ron Rosenfeld

@NareshBhople 将数字处理为文本字符串。 - Ron Rosenfeld

@NareshBhople 是的，这个数字肯定必须作为字符串输入到Excel表格中，否则你将受限于Excel的数值精度限制，大约只能达到15位数字。 - Ron Rosenfeld

对于公式问题，请参见我的答案中的“注释”部分。您的数字很长，它们是数字而不是文本表示。对于UDF问题，您已经给模块和函数取了相同的名称。这会让Excel感到困惑，因此它不知道您所指的“digitalRoot”，因此出现“#NAME?”错误。同样，由于您没有将数字作为文本字符串传递，而是作为未舍入的数字，因此结果可能出乎意料。 - Ron Rosenfeld

Answer 2

我希望您正在寻找类似以下 UDF 的东西。

Function SumToOneDigit(myNumber)
Dim temp: temp = 0
CalcLoop:

For i = 1 To Len(myNumber)
If IsNumeric(Mid(myNumber, i, 1)) Then temp = temp + Mid(myNumber, i, 1)
Next

If Len(temp) > 1 Then
myNumber = temp
temp = 0
GoTo CalcLoop
End If

SumToOneDigit = temp
End Function

UDF（用户定义函数）是VBA（Visual Basic for Applications）中的代码。

当您无法使用给定的Excel函数进行计算时，可以在Excel中的VBA模块中使用UDF。请参见此链接以获取UDF。如果您没有开发人员选项卡，请参见此链接，通过右键单击工作簿添加一个模块，并将上述代码粘贴到该模块中。请记住，此代码仅保留在此工作簿中。因此，如果您想在其他文件中使用此UDF，则必须在该文件中添加模块并将代码粘贴到其中。如果您经常使用此类UDF，则最好将其制作成插件，如此链接

- Naresh

---

@Gary'sStudent .. 谢谢 .. 但我仍然不确定这是否是OP正在寻找的答案 :) - Naresh

OP没有包含任何预期结果。 - Gary's Student

我从未想到我们可以在Excel中编写这些内容，但是我想要的是将单元格中的所有数字相加，并得到一个单个数字结果，例如1或2或3... 如果此函数可以实现此功能，那么我该如何在单元格中使用和实现它？ - Saher Al-Sous

当您无法使用类似于问题中提到的Excel函数进行计算时，可以在Excel中的Visual Basic for Applications模块中编写代码。请参见此链接以获取UDF。如果您没有开发人员选项卡，请参见此链接，通过右键单击工作簿添加一个VBA模块，并将上面的代码粘贴到该模块中。请记住，此代码仅保留在此工作簿中。 - Naresh

我会在我的电脑上检查这个（目前在Mac上），安装VBA并完成它。我会及时告诉你...非常感谢。 - Saher Al-Sous

为什么安装VBA后无法像上面提到的那样访问开发人员选项卡？请参见此链接。同时已编辑答案。 - Naresh

Answer 3

你可以使用这样的公式：

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"))

你可能需要一个额外的 SUBSTITUTE 函数来将 . 替换为 ,，如果这是你的小数分隔符：

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,".",",")," ","</s><s>")&"</s></t>","//s"))

然而，也许像其他人建议的那样使用UDF对您也是一种可能性。不过，有些东西告诉我我可能误解了您的问题...

- JvdV

---

感谢您的回复。我想让结果是一个单个数字，比如说不要得到73，而是得到1（7+3=10，1+0=1）。 - Saher Al-Sous

Answer 4

下面的UDF将返回作为参数传递给它的单元格中所有数字的总和。

Function SumCell(Cell As Range) As Double

    Dim Fun     As Double           ' function return value
    Dim Sp()    As String           ' helper array
    Dim i       As Integer          ' index to helper array

    Sp = Split(Cell.Cells(1).Value)
    For i = 0 To UBound(Sp)
        Fun = Fun + Val(Sp(i))
    Next i

    SumCell = Fun
End Function

将该函数安装在标准代码模块中，命名为Module1之类的名称。从工作表中使用语法调用它，例如=SumCell(A2)，其中A2是要求总和的单元格。像内置函数一样复制下去。

- Variatus

---

Excel在Mac上有UDF吗？ - Saher Al-Sous

为什么不试一下，然后告诉我们呢？ - Variatus

Answer 5

除了使用“文本分列”进行一次性转换外，通过创建一个接受数据字符串并将其拆分为由空格分隔的数组的用户函数，然后循环元素将它们相加，在VBA中实现这个功能也相对容易。请注意，保留HTML标签。

在新模块中添加以下VBA代码：

Function fSumData(strData As String) As Double
    On Error GoTo E_Handle
    Dim aData() As String
    Dim lngLoop1 As Long
    aData = Split(strData, " ")
    For lngLoop1 = LBound(aData) To UBound(aData)
        fSumData = fSumData + CDbl(aData(lngLoop1))
    Next lngLoop1
fExit:
    On Error Resume Next
    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "fSumData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

然后将此输入到 Excel 工作表中的单元格中：

=fSumData(A1)

致敬，

- Applecore

---

很确定当作为工作表函数使用时，错误消息框不会显示。请改用 CVERR - 将 MsgBox 行替换为 fSumData = CVERR(xlErrNum)，并将函数更改为返回 Variant 而不是 Double - 这将在函数失败时返回 #NUM!。 - Darren Bartrup-Cook

当您将MsgBox用作工作表函数时，它会出现。这取决于您是想将其仅用作工作表函数，还是希望在其他VBA函数中额外使用它的能力。 - Applecore

VBA = Visual Basic吗？ 我在Mac上有Excel，这是默认的吗？ 我该如何使用它？ 我想这个函数会将所有数字相加并在最后得到一个单个数字结果，对吗？ - Saher Al-Sous