Преобразование / преобразование суммы (A1:D1) в A1+B1+C1+D1 в Excel

Итак, ситуация такова, я хочу знать, есть ли способ преобразовать сумму диапазона ячеек в сумму, полученную в результате добавления каждой ячейки отдельно, как показано в заголовке. Например, скажем, у одного есть это вычисление в ячейке 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, хотя я знаю основы и попробую сам.

Источник

2 ответа

Минимальный рабочий пример с функцией VBA unroll(), который принимает ссылку на ячейку с одной функцией (например, sum, count, min) и развертывает свой аргумент (список диапазонов) в виде списка отдельных ячеек.

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
Источник

Хотя вы пометили это функцией 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

Просто помните, что отмены нет, поэтому сначала сделайте резервную копию.

Как добавить VBA в MS Office?

Пример с А1:D1

Пример с A1:D2

Согласно комментариям в коде, если вы обновляете с

Range("B5").Value = ""

в

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

Вы закончите с (в B5)

B4 = A1 + B1 + C1 + D1
Источник
Другие вопросы по тегам