☰

公式替换 excel

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
公式替换 excel

是否可以轻松地在 Excel 中执行公式替换？

我有一个与以下示例类似的工作表（尽管更复杂，因此需要 Excel 中的某些函数（如果存在的话））

我想要做的是用实际输入值而不是公式来替换总销售额函数。

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

在此处输入图片描述

希望你明白我所追求的

答案1

我编写了一个快速代码，它可以完成你想要做的事情。

我基本上是检查一个单元格是否包含公式，如果包含，则用该公式替换引用该单元格的所有实例。

一旦它遍历完表格，如果发现任何替换项，它就会再次循环（实际上不确定是否需要这样做，但这样做更容易）。我也不知道这在复杂的电子表格上运行速度有多快。

请注意，它将 $A$1、$A1、A$1 和 A1 视为相同的，它无法确定引用中是否应该有一些冻结单元格。

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

答案2

您可能必须手动完成，但有一项功能可以帮助您：追踪先例（在 Excel 2013 的“公式”选项卡中）。

这将添加箭头显示您选择的公式的来源，因此如果您在示例中选择了 J 列中的总销售额并单击了跟踪先例，那么它将从 F9 和 L9 绘制到它的箭头。

相关内容