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 + C1B1enthalten= B2 * B2C1enthalten= C2 * C2B2enthalten1C2enthalten2
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.
- Drücken Sie Alt+F11
- Klicken Sie
ModuleaufInsert. - 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.
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.
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.
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:
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.