Автоматическое объединение формулы нескольких ячеек в одну ячейку

Question

У меня есть лист Excel, который использует много ячеек для расчета конечного результата. Я вижу, что эти промежуточные результаты могли быть полезны при создании формулы. Теперь я хотел бы избавиться от промежуточных результатов и иметь только одну формулу в одной ячейке.

Я знаю, как сделать это вручную, например, как описано в этом вопросе и ответе. Тем не менее, я хотел бы знать, есть ли автоматический способ сделать это.

Кажется, Excel уже знает, откуда берутся значения, когда я использую функцию трассировки:

Обычно я просто скрывал строки, чтобы сделать их невидимыми. В этом случае я не могу сделать это, потому что в других столбцах есть значения, которые важны.

Answer 1

Ответ Чарльза Претория был полезен. После исправления двух проблем Макрос работал на меня. Я добавил комментарии, где я изменил сценарий.

Обратите внимание, что макрос нужно запускать так часто, как вы хотите, чтобы были сделаны замены. Можно было бы выполнить все замены, но, возможно, должна остаться последняя ссылка на ячейку.

Sub CombineFormula()

    Dim FormulaCell As Range
    Set FormulaCell = ActiveCell

    Dim StrTemp As String
    StrTemp = FormulaCell.Formula

    'Do not replace the $ sign in absolute addresses.
    'It may be used in text, e.g. in a formula like ="US$"&A1
    'Instead, use different adressing modes in the replacement loop
    '! StrTemp = Replace(StrTemp, "$", "")

    Dim RangeRef As Range

    On Error Resume Next

    Dim ReplaceBy As String
    For Each RangeRef In FormulaCell.Precedents.Cells
        'Add in parentheses to maintain correct order of evaluation, e.g. in cases of addition before multiplication
        ReplaceBy = "(" + Replace(RangeRef.Formula, "=", "") + ")"
        'The order of the following is important
        'Replace absolute ranges first, because A$1 is also contained in $A$1
        StrTemp = Replace(StrTemp, RangeRef.Address(True, True), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(True, False), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(False, False), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(False, True), ReplaceBy)
    Next

    FormulaCell.Value2 = StrTemp
End Sub

Answer 2

Я не уверен, что вы счастливы использовать макросы в своих таблицах, но ниже приведен простой макрос для достижения того, чего вы хотите. Просто скопируйте код на свой VBA, выберите ячейку, которую вы хотите очистить, и запустите макрос, вы даже можете создать для него комбинацию клавиш, если у вас много работы. Только будьте осторожны, если вы запустите макрос, вся ваша история отмен будет удалена.

Sub CombineFormula()

Dim FormulaCell As Range
Set FormulaCell = ActiveCell

Dim StrTemp As String
StrTemp = FormulaCell.Formula
StrTemp = Replace(StrTemp, "$", "")


Dim RangeRef As Range

On Error Resume Next

For Each RangeRef In FormulaCell.Precedents.Cells
    StrTemp = Replace(StrTemp, RangeRef.Address(False, False), Replace(RangeRef.Formula, "=", ""))
Next


FormulaCell.Value2 = StrTemp

End Sub

Answer 3

Прямого подхода к этому нет, насколько я знаю, но я думаю, что функция FORMULATEXT может пригодиться вам. функция FORMULATEXT(номер ячейки) вернет вам формулу в виде текста, который можно использовать для некоторых модификаций, таких как

A3 = FORMULATEXT(A1)+FORMULATEXT(A2) или что-то в этом роде для достижения вашей цели.