☰

Formelsubstitution Excel

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Formelsubstitution Excel

ist es möglich, Formeln in Excel einfach zu ersetzen?

Ich habe ein Arbeitsblatt ähnlich dem folgenden Beispiel (wenn auch komplexer, daher ist eine Funktion in Excel erforderlich, falls vorhanden).

Ich möchte die Gesamtumsatzfunktion durch die tatsächlichen Eingabewerte und nicht durch Formeln ersetzen.

F6+I6+L6+O6 = F9 + L9
F4+F5+I4+I5+L4+L5+O4+O5 = F6+I6+L6+O6

that is 
F9 + L9 becomes F4+F5+I4+I5+L4+L5+O4+O5

Bildbeschreibung hier eingeben

Ich hoffe, Sie verstehen, was ich will

Antwort1

Ich habe schnell einen Code geschrieben, der das tun sollte, was Sie möchten.

Grundsätzlich wird geprüft, ob eine Zelle eine Formel enthält. Wenn dies der Fall ist, werden alle Vorkommen, in denen auf diese Zelle verwiesen wird, durch diese Formel ersetzt.

Wenn das Blatt einmal durchgegangen ist und Ersetzungen gefunden wurden, wird die Schleife erneut ausgeführt (ich bin nicht sicher, ob das wirklich nötig ist, aber es war einfacher, das einzubauen). Ich habe auch keine Ahnung, wie schnell das bei komplexen Tabellenkalkulationen laufen wird.

Beachten Sie, dass $A$1,$A1,A$1 und A1 als gleich behandelt werden und es keine Möglichkeit gibt, zu bestimmen, ob in der Referenz eingefrorene Zellen enthalten sein sollten.

Sub replace_formulas()
Dim cell_count As Long, flag As Boolean

Do

flag = False

For Each c In ActiveSheet.UsedRange
    If c.HasFormula Then

        'count number of replacements
        cell_count = Application.WorksheetFunction.CountIf(Cells, c.Address) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "")) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "", 1, 1)) + _
            Application.WorksheetFunction.CountIf(Cells, "$" & Replace(c.Address, "$", ""))

        'If there is at least one replacement loop through all the cells after this one
        If cell_count > 0 Then flag = True

        'Replace cell references with and without $ ($A$1,$A1,A$1,A1)
        Cells.Replace What:=c.Address, Replacement:="c.formula", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", "", 1, 1), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:="$" & Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End If
Next

Loop While flag = True

End Sub

Antwort2

Möglicherweise müssen Sie dies manuell tun, es gibt jedoch eine Funktion, die Sie dabei unterstützt: Präzedenzfälle verfolgen (auf der Registerkarte „Formeln“ in Excel 2013).

Dadurch werden Pfeile hinzugefügt, die zeigen, wo die von Ihnen ausgewählte Formel ihren Ursprung hat. Wenn Sie in Ihrem Beispiel also den Gesamtumsatz in Spalte J ausgewählt und auf „Präzedenzfälle verfolgen“ geklickt haben, werden Pfeile von F9 und L9 dorthin gezeichnet.

verwandte Informationen