Проблема

Question

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

Разъяснение

В комментариях было предложение инструмента Evaluate Formula. Пока я не смог найти, как активировать его в Excel 2008 для Mac, изего описаниепохоже, что он позволяет пользователю пошагово оценивать ячейку. Это не то, что мне нужно. Мне нужен способ преобразовать формулу в заданной ячейке, которая может ссылаться на другие ячейки, содержащие формулы, в эквивалентную формулу, выраженную в терминах конечных ячеек (тех, которые содержат значения, но не формулы).

Вот простой пример. Пусть

• A1содержать= B1 + C1
• B1содержать= B2 * B2
• C1содержать= C2 * C2
• B2содержать1
• C2содержать2

Вычислить формулу позволит мне пройти по шагам вычисления, A1чтобы получить окончательное значение 5. Мне нужен инструмент, который развернул бы A1формулу = B2 * B2 + C2 * C2без ее фактической оценки.

Answer 1

Проблема

Вы не можете сделать это с помощью Evaluate Formula, потому что это не цель функции. Вот почему она называетсяоценивать, он для оценки формул. То, что вам нужно, это своего рода распаковка. Это немного особая необходимость, поэтому она не реализована как инструмент в Excel, но есть решения, если вы создадите некоторые функции/макросы Visual Basic.

Создайте модуль кода VBA (макрос), как вы можете видеть вэтот урок.

1. Нажмите Alt+F11
2. Нажмите, чтобы Moduleвойти Insert.
3. Вставьте код.

Function CellFormula(Target As Range) As String
   CellFormula = Target.Formula
End Function

Затем введите в ячейку следующее:=CellFormula(A1)

Это расскажет формулу ячейки. Единственная проблема с этим кодом в том, что он работает только для одного уровня. Если вы хотите распаковать также содержащиеся в ячейках формулы, то вам нужен более сложный код с рекурсией.

Решение

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

Исходный код

Option Explicit

Function isChar(char As String) As Boolean
    Select Case char
        Case "A" To "Z"
            isChar = True
        Case Else
            isChar = False
    End Select
End Function

Function isNumber(char As String, isZero As Boolean) As Boolean
    Select Case char
        Case "0"
            If isZero = True Then
                isNumber = True
            Else
                isNumber = False
            End If
        Case "1" To "9"
            isNumber = True
        Case Else
            isNumber = False
    End Select
End Function

Function CellFormulaExpand(formula As String) As String
    Dim result As String
    Dim previousResult As String
    Dim cell As Range
    Dim stringArray() As String
    Dim arraySize As Integer
    Dim n As Integer
    Dim trimmer As String
    
    Dim c As Integer 'character number
    Dim chr As String 'current character
    Dim tempcell As String 'suspected cell's temporaly result
    Dim state As Integer 'state machine's state:
    Dim stringSize As Integer

    result = formula
    previousResult = result
    state = 0
    stringSize = 0

    For c = 0 To Len(formula) Step 1
        chr = Mid(formula, c + 1, 1)
        Select Case state
            Case 0
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 5
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 1
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell & chr
                ElseIf isChar(chr) Then
                    state = 2
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 2
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf isChar(chr) Then
                    state = 3
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 3
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 4
                If isNumber(chr, True) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    stringSize = stringSize + 1
                    ReDim Preserve stringArray(stringSize - 1)
                    stringArray(stringSize - 1) = tempcell
                    tempcell = ""
                End If
            Case 5
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 6
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case Else
                state = 0
                tempcell = ""
        End Select
    Next c
    If stringSize = 0 Then
        CellFormulaExpand = result
    Else
        arraySize = UBound(stringArray)
        For n = 0 To arraySize Step 1
            Set cell = Range(stringArray(n))
            If Mid(cell.formula, 1, 1) = "=" Then
                trimmer = Mid(cell.formula, 2, Len(cell.formula) - 1)
                If trimmer <> "" Then
                    result = Replace(result, stringArray(n), trimmer)
                End If
            End If
        Next
        If previousResult <> result Then
            result = CellFormulaExpand(result)
        End If
    End If
    CellFormulaExpand = result
End Function

Function CellFormula(rng As Range) As String
    CellFormula = CellFormulaExpand(rng.formula)
End Function

Чтобы это заработало, просто создайте макрос (как я описал его в начале ответа) и скопируйте-вставьте код. После этого вы можете использовать его с =CellFormula(A1)любым A1типом ячейки 1x1.

Случаи, когда это работает

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

Здесь вы можете увидеть, как это работает с числами. Теперь мы сталкиваемся с первой проблемой, что алгоритм не заботится о последовательности математических операций, поэтому красное число — это 6, когда должно быть 10. Если мы поместим чувствительные операции (такие как сложение и вычитание) в скобки, то введенная формула даст тот же результат, что вы можете видеть в зеленом числе внизу, которое означает 10.

Случаи, когда это не работает

Этот алгоритм не идеален. Я только пытался реализовать наиболее распространенные варианты использования, поэтому его можно улучшить, добавив больше функций, которые обрабатывают другие случаи, такие как диапазоны.
Как вы можете видеть в этом примере, я использовал SUM()диапазон в качестве параметра. Поскольку алгоритм расшифровывает содержимое ячеек сверху вниз, он начинает с замены параметров, SUM()а затем с чего-либо еще. Поэтому остается :на своем месте, в то время как вокруг него все заменяется, поэтому рядом с ним заменяются новые ячейки, которые изменят его значение. Таким образом, вывод будет неверным. Поэтому в этом случае вы можете использовать этот макрос только для изучения исходной формулы.

Answer 2

Учитывая использование вами слова «трассировка» в вашем вопросе, я предполагаю, что вы знакомы с функциями Trace Precendentsи Trace Dependentsв разделе «Аудит формул» Excel?

Вот краткое объяснение каждого из инструментов аудита формул с сайта dummies.com:

Понимание инструментов аудита формул Excel 2010

Answer 3

Сегодня мы бы просто использовали FORMULATEXT()довольно очевидный способ, но тогда этот трюк был упущен, и сегодня для тех, у кого более ранние версии (например, у моих родственников с 2007 года), нужно использовать другую макрокоманду Excel 4, GET()внутри именованного диапазона.

Одной из МНОГИХ вещей, которые он мог делать, было чтение формулы как текста, точно так же, как FORMULATEXT()это происходит сейчас. Затем это вопрос простого объединения строк. Немного IF()подброшено, чтобы проверить, есть ли что-то, что можно получить, и вы свободны от дома.