☰

Excel：单元格内用新行分隔的所有数字相加

tag-icon • tag-icon microsoft-excel worksheet-function cells newlines
Excel：单元格内用新行分隔的所有数字相加

在此处输入图片描述

如何创建一个新单元格，其值等于单元格中以新行分隔的所有数字的总和？

我可能不会使用文本到列，因为那很混乱，并且 Excel 中的文本到列中没有新行分隔符，这会增加额外的开销。

答案1

在评论中，您问“这对苹果有用吗”。

Mac电脑没有该FILTERXML功能。

但是，我们可以替换：

=SUM(IFERROR(--MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),IF(seq=1,1,(seq-1)*99),99),0))

或者，如果你有以下NUMBERVALUE功能：

=SUM(NUMBERVALUE(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),IF(seq=1,1,(seq-1)*99),99)))

其中seq已定义姓名指：

=ROW(INDEX(Sheet16!$A:$A,1):INDEX(Sheet16!$A:$A,255))

在此处输入图片描述

在上面我们

  • lf用99空格替换每个
  • 使用该MID函数返回单元格中的值数组
  • NUMBERVALUE使用函数或双元运算符 将这些值转换为其等价的数值。
    • 由于双元运算符在应用于空字符串时会出现 => 错误，因此我们需要使用 进行测试IFERROR。
    • NUMBERVALUE如果我们将其0应用于空字符串时返回，那就不是问题。
  • SUM结果数组中的值

答案2

我认为正确计算这些值的唯一方法是先将“m 到单元格”与“文本到列”合并。我找到了一个不错的方法来做到这一点

  • 选择您的单元格并选择[数据]>文本到列
  • 选择“分隔”并点击下一步
  • 选择“其他”，然后在空文本框中按 CTRL + J（不会出现任何内容）
  • 点击完成，您的数据将水平分布在各个单元格中。

现在你可以做一个像 =SUM(2:2) 这样的求和

答案3

下面的宏将获取您想要的总和。
只需选择您想要获取总和的单元格下方的单元格。然后调用此宏。它会将总和写入选定的单元格中。请参阅下面的示例：

在此处输入图片描述

Sub getsum_fromcell()
Dim newarr As Variant
mycell = Cells(ActiveCell.Row - 1, ActiveCell.Column)

newarr = Split(mycell, Chr(10))
                    mysum = 0
                    ubnd = UBound(newarr)
                    For i = 0 To ubnd
                        mysum = mysum + newarr(i)
                    Next i
ActiveCell.Value = mysum
End Sub

我还粘贴了函数版本，您可以在其中获得提供给该函数的任何单元格的总和。

Function getsum_fromcell_func(mycell)
Dim newarr As Variant
newarr = Split(mycell, Chr(10))
                    mysum = 0
                    ubnd = UBound(newarr)
                    For i = 0 To ubnd
                        mysum = mysum + newarr(i)
                    Next i
getsum_fromcell_func = mysum
End Function

答案4

实际上，您可以在一个单元格内实现此目的，且无需任何宏。

假设您的源数据位于单元格 A2 中，如您的屏幕截图所示：

=SUM(FILTERXML("<x><y>" & SUBSTITUTE(A2, CHAR(10), "</y><y>") & "</y></x>", "//y"))

这是一个数组公式，因此您必须完成输入ctrl + shift + enter才能使其正常工作。

这个公式如何运作？用一个小技巧：

  1. 首先，它用字符串替换数据中的所有换行符</y><y>。
  2. <x><y>它用和括起新字符串</x></y>，从而创建一个有效的 xml 表。
  3. FILTERXML将 xml 表转换为数据数组。
  4. 最后它将数组中的所有数字相加。

有了它，你应该会很满意。你的数据甚至可以包含文本行，公式将忽略所有文本。

编辑：根据文档，FILTERXML 函数目前在 Excel for web 和 Excel for Mac 中不可用。

相关内容