Преобразование / преобразование суммы(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.

EDIT: кто-то предложил мне использовать VBA, и я тоже подумал об этом. Если у кого-то есть какие-то предложения по этому поводу, я буду очень признателен (я не очень хорошо программирую на VBA, хотя я знаю основы, и я попробую сам.

16
задан Dave
20.05.2023 17:42 Количество просмотров материала 2488
Распечатать страницу

2 ответа

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

пример с A1: D1

enter image description here

пример с A1: D2

enter image description here

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

Range("B5").Value = ""

to

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

вы получите (в B5)

B4 = A1 + B1 + C1 + D1
2
отвечен Dave 2023-05-22 01:30

минимальный рабочий пример с функцией 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

enter image description here

Примечание: как минимальный пример, он не обрабатывает двухбуквенные ссылки столбцов правильно.

* Edit *

добавил Function ColumnNoToName для обработки ссылок на ячейки с колоннами > 26.

enter image description here

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
2
отвечен g.kov 2023-05-22 03:47

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Мы используем cookie. Это позволяет нам анализировать взаимодействие посетителей с сайтом и делать его лучше. Продолжая пользоваться сайтом, вы соглашаетесь с использованием файлов cookie. Политика конфиденциальности
Согласен
Вверх