在 Excel 中将 sum(A1:D1) 转换/转换为 A1+B1+C1+D1

Question

因此，情况是这样的，我想知道是否有办法将单元格范围的总和转换为分别添加每个单元格的结果，如标题所示。例如，假设在单元格中有这样的计算E1，E1 = SUM(A1:D1)并希望将其拆分为单独的组件单元的总和，例如E1 = A1 + B1 + C1 + D1。

此外，如果有人能指出如何对一系列单元格执行此操作，那就太好了，比如在这种情况下：E1 = SUM (A1:D2)转换成E1 = A1 + B1 + C1 + D1 + A2 + B2 + C2 + D2。

编辑：有人建议我使用 VBA，我也考虑过。如果有人对此有任何建议，我将不胜感激（我不太擅长使用 VBA 编程，尽管我知道基础知识，我会自己尝试一下。

Answer 1

虽然您已将其标记为 Worksheet 函数，但您在问题中谈到了使用 VBa。此 VBa 可执行您给出的两个示例

Option Explicit
Sub EeekPirates()

Dim formula As String
formula = Range("B4").formula

Dim split1() As String
split1 = Split(formula, "(")

Dim temp As String
temp = Replace(split1(1), ")", "")

Dim splitty() As String
splitty = Split(temp, ":")

Dim firstCol As Integer
firstCol = AscW(Left(splitty(0), 1))

Dim secondCol As Integer
secondCol = AscW(Left(splitty(1), 1))

Dim firstRow As Integer
firstRow = Right(splitty(0), 1)

Dim secondRow As Integer
secondRow = Right(splitty(1), 1)

Range("B5").Value = ""   ' this could be updated to `B4 = ` 
Dim i As Integer
Dim j As Integer

For j = firstRow To secondRow
    For i = firstCol To secondCol
        Range("B5").Value = Range("B5").Value & Chr(i) & j & "+"
    Next i
Next j

Dim length As Integer
length = Len(Range("B5").Value) - 1
Range("B5").Value = Left(Range("B5").Value, length)

End Sub

请记住，没有撤消，因此请先备份。

如何在 MS Office 中添加 VBA？

以 A1:D1 为例

以 A1:D2 为例

根据代码中的注释，如果你从

Range("B5").Value = ""

到

Range("B5").Value = "B4 = "

你最终会得到（在 B5 中）

B4 = A1 + B1 + C1 + D1

Answer 2

使用 VBA 函数的最小工作示例，该函数引用具有单一函数（如、、 ）unroll()的单元格并将其参数（范围列表）展开为单个单元格列表。sumcountmin

Option Explicit

Function rangeText(s As String) As String
Dim i As Integer, j As Integer
i = Excel.WorksheetFunction.Find("(", s)
j = Excel.WorksheetFunction.Find(")", s)
rangeText = Mid(s, i + 1, j - i - 1)
End Function

Function rangeToList(s As String)
Dim rg As Range: Set rg = Range(s)
Dim i, j As Integer: Dim c As String
For j = 0 To rg.Rows.Count - 1
  For i = 0 To rg.Columns.Count - 1
    c = c + IIf(c <> "", ",", "") + Chr(64 + rg.Column() + i) + Format(rg.Row() + j)
  Next i
Next j
rangeToList = c
End Function

Function unroll(x As Range) As String
  Dim s As String: Dim i, j As Integer: Dim list() As String
  If Not x.HasFormula Then
   s = "Not a formula"
  Else
  s = rangeText(x.Formula)
  list = Split(s, ",")
  s = ""
  For i = 0 To UBound(list)
    s = s + IIf(i > 0, ",", "") + rangeToList(list(i))
  Next i
End If
unroll = s
End Function

注意：作为一个最小示例，它不能正确处理双字母列引用。

* 编辑 *

添加Function ColumnNoToName以处理列数 > 26 的单元格引用。

Option Explicit

Function rangeText(s As String) As String
Dim i As Integer, j As Integer
i = Excel.WorksheetFunction.Find("(", s)
j = Excel.WorksheetFunction.Find(")", s)
rangeText = Mid(s, i + 1, j - i - 1)
End Function

Function ColumnNoToName(colNo As Integer) As String
  Dim lo, hi As Integer: Dim s As String
  lo = (colNo - 1) Mod 26
  If colNo > 26 Then
    hi = (colNo - 1 - lo) \ 26
    s = Chr(64 + hi)
  End If
  s = s + Chr(64 + lo + 1)
  ColumnNoToName = s
End Function

Function rangeToList(s As String)
Dim rg As Range: Set rg = Range(s)
Dim i, j As Integer: Dim c As String
For j = 0 To rg.Rows.Count - 1
  For i = 0 To rg.Columns.Count - 1
    c = c + IIf(c <> "", ",", "") _
      + ColumnNoToName(rg.Column() + i) _
      + Format(rg.Row() + j)
  Next i
Next j
rangeToList = c
End Function

Function unroll(x As Range) As String
  Dim s As String: Dim i, j As Integer: Dim list() As String
  If Not x.HasFormula Then
   s = "Not a formula"
  Else
  s = rangeText(x.Formula)
  list = Split(s, ",")
  s = ""
  For i = 0 To UBound(list)
    s = s + IIf(i > 0, ",", "") + rangeToList(list(i))
  Next i
End If
unroll = s
End Function

Function cellFormula(x As Range) As String
  cellFormula = x.Formula
End Function