Das Problem

Das Problem

Ich habe eine sehr komplizierte Excel-Tabelle (Formeln, die auf Zellen mit anderen Formeln in mehreren Arbeitsblättern zugreifen), die am Ende eine einzelne Zelle (Ausgabe) mit Eingaben aus mehreren anderen Zellen (Parameter) berechnet. Gibt es eine automatisierte Methode (ein Excel-Makro oder ein eigenständiges Tool), die bei der Ausgabezelle beginnen und die Berechnungen rekursiv abwickeln kann, bis die Formel direkt in Form von Parameterzellen ausgedrückt wird?

Klärung

In den Kommentaren gab es einen Vorschlag für das Tool „Formel auswerten“. Ich konnte zwar nicht herausfinden, wie man es in Excel 2008 für Mac aktiviert, aber vonseine Beschreibunges klingt, als ob es dem Benutzer erlaubt, die Auswertung einer Zelle schrittweise durchzuführen. Das ist nicht, was ich brauche. Was ich brauche, ist eine Möglichkeit, die Formel in einer bestimmten Zelle, die auf andere Zellen mit Formeln verweisen kann, in eine äquivalente Formel umzuwandeln, die in Form von Endzellen ausgedrückt wird (solche, die Werte, aber keine Formeln enthalten).

Hier ist ein einfaches Beispiel. Lassen Sie

  • A1enthalten= B1 + C1
  • B1enthalten= B2 * B2
  • C1enthalten= C2 * C2
  • B2enthalten1
  • C2enthalten2

Mit der Funktion „Formel auswerten“ könnte ich die Berechnung von schrittweise durchgehen, A1um zum Endwert von zu gelangen . Was ich brauche, ist ein Tool, das die Formel 5entfaltet, ohne sie tatsächlich auszuwerten.A1= B2 * B2 + C2 * C2

Antwort1

Das Problem

Dies ist mit Formel auswerten nicht möglich, da dies nicht der Zweck der Funktion ist. Deshalb heißt sieauswerten, es dient zur Auswertung der Formeln. Was Sie wollen, ist eine Art Entpacken. Dies ist ein etwas spezieller Bedarf, daher ist es nicht als Tool in Excel implementiert, aber es gibt Lösungen, wenn Sie einige Visual Basic-Funktionen/Makros erstellen.

Erstellen Sie ein VBA-Codemodul (Makro), wie Sie in sehen könnendieses Tutorial.

  1. Drücken Sie Alt+F11
  2. Klicken Sie Moduleauf Insert.
  3. Code einfügen.
Function CellFormula(Target As Range) As String
   CellFormula = Target.Formula
End Function

Geben Sie dann Folgendes in eine Zelle ein:=CellFormula(A1)

Dadurch wird die Formel der Zelle angegeben. Das einzige Problem mit diesem Code ist, dass er nur für eine Ebene funktioniert. Wenn Sie auch die enthaltenen Zellformeln entpacken möchten, benötigen Sie einen komplexeren Code mit Rekursion.

Die Lösung

Es war ein langer Weg, aber ich habe für Sie ein VBA-Makro erstellt, das diese Funktion implementiert. Ich behaupte nicht, dass dieser Code für jede Formel funktioniert, aber er funktioniert bei den meisten/einigen davon. Außerdem behaupte ich nicht, dass dieser Code Formeln generiert, die dem ursprünglich eingegebenen Code entsprechen oder dasselbe Ergebnis wie das Original liefern.

Quellcode

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

Damit es funktioniert, erstellen Sie einfach ein Makro (wie ich es am Anfang der Antwort beschrieben habe) und kopieren Sie den Code. Danach können Sie es mit =CellFormula(A1)jeder A1Art von 1x1-Zelle verwenden.

Fälle, in denen es funktioniert

Ich habe einige Beispiele erstellt, damit Sie es in Aktion sehen können. In diesem Fall demonstriere ich die Verwendung mit Zeichenfolgen. Sie können sehen, dass es perfekt funktioniert. Der einzige kleine Fehler besteht darin, dass der Algorithmus die Semikolons irgendwie in Kommas ändert. Nachdem Sie sie ersetzt haben (wie ich es in diesem Beispiel getan habe), erhalten Sie die richtige Ausgabe. Mit Saiten arbeiten

Hier können Sie sehen, wie es mit Zahlen funktioniert. Jetzt stehen wir vor dem ersten Problem, dass der Algorithmus sich nicht um die mathematische Operationsfolge kümmert, weshalb die rote Zahl 6 ist, obwohl sie 10 sein sollte. Wenn wir die sensiblen Operationen (wie Addition und Subtraktion) in Klammern setzen, dann gibt die angegebene Formel, wenn sie zurückgegeben wird, dasselbe Ergebnis aus, wie Sie an der grünen Zahl unten sehen können, die 10 lautet. Mit Zahlen arbeiten

Fälle, in denen es nicht funktioniert

Dieser Algorithmus ist nicht perfekt. Ich habe nur versucht, die gängigsten Verwendungen zu implementieren, daher kann er durch Hinzufügen weiterer Funktionen verbessert werden, die andere Fälle wie Bereiche behandeln.
Wie Sie in diesem Beispiel sehen können, habe ich SUM()einen Bereich als Parameter verwendet. Da der Algorithmus den Inhalt der Zellen von oben nach unten entschlüsselt, beginnt er mit dem Ersetzen der SUM()Parameter und ersetzt sie später mit allem anderen. Daher :bleibt die Zelle an ihrem Platz, während alles um sie herum ersetzt wird, sodass neue Zellen in der Nähe ersetzt werden, was die Bedeutung ändert. Daher wird die Ausgabe falsch sein. In diesem Fall können Sie dieses Makro also nur verwenden, um die ursprüngliche Formel zu untersuchen. Arbeiten mit Bereichen

Antwort2

Angesichts der Verwendung des Wortes „Trace“ in Ihrer Frage würde ich davon ausgehen, dass Sie mit den Funktionen Trace Precendentsund Trace Dependentsim Abschnitt „Formelüberwachung“ von Excel vertraut sind?

Hier ist eine kurze Erklärung zu jedem der Formel-Auditing-Tools von dummies.com:

Grundlegendes zu den Formelüberwachungstools von Excel 2010

Antwort3

Heute würden wir einfach die ziemlich naheliegende Art verwenden FORMULATEXT(), aber der damals übersehene Trick besteht – und das gilt auch heute noch für diejenigen mit früheren Versionen (wie meine Schwiegereltern mit 2007) – darin, GET()innerhalb eines benannten Bereichs einen anderen Excel 4-Makrobefehl zu verwenden.

Eine der VIELEN Möglichkeiten war, eine Formel als Text zu lesen, genau wie FORMULATEXT()es jetzt der Fall ist. Dann muss man nur noch Zeichenfolgen kombinieren. Ein bisschen IF()reingeworfen, um zu testen, ob es etwas zu erhalten gibt, und schon ist man auf der sicheren Seite.

verwandte Informationen