Converter / transformar soma (A1: D1) em A1 + B1 + C1 + D1 no Excel

Question

Então, a situação é essa, eu quero saber se há uma maneira de converter a soma de um intervalo de células na soma resultante da adição de cada célula, separadamente, como mostrado no título. Por exemplo, digamos que uma pessoa tenha esse cálculo na célula E1 , E1 = SUM(A1:D1) e queira dividi-la na soma das células componentes separadas, como em E1 = A1 + B1 + C1 + D1 .

Além disso, seria ótimo se alguém pudesse indicar como fazer isso para um intervalo de células, como no caso: E1 = SUM (A1:D2) sendo convertido em E1 = A1 + B1 + C1 + D1 + A2 + B2 + C2 + D2.

EDIT: Alguém sugeriu que eu use o VBA, e eu pensei sobre isso também. Se alguém tiver alguma sugestão sobre o assunto, seria muito apreciado (eu não sou muito bom em programar em VBA, embora eu saiba o básico e eu vou tentar por conta própria.

    

Answer 1

Embora você tenha marcado isso com a função de planilha, você fala sobre o uso do VBa na pergunta. Este VBa faz os dois exemplos que você deu

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

Apenas lembre-se que não há desfazer, então faça um backup primeiro.

Como eu adiciono o VBA no MS Office?

Exemplo com A1: D1

ExemplocomA1:D2

Deacordocomoscomentáriosnocódigo,sevocêatualizarde

Range("B5").Value = ""

para

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

Você vai acabar com (em B5)

B4 = A1 + B1 + C1 + D1

    

Answer 2

Exemplo de trabalho mínimo com a função VBA unroll() , que leva uma referência a uma célula com uma única função (como sum , count , min ) e desenrola seu argumento (uma lista de intervalos) como uma lista de células únicas.

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

Nota:comoumexemplomínimo,elenãomanipulareferênciasdecolunadeduasletrascorretamente.

*Editar*

AdicionadaFunctionColumnNoToNameparalidarcomreferênciasdecélulascomcolunas>26.

OptionExplicitFunctionrangeText(sAsString)AsStringDimiAsInteger,jAsIntegeri=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