☰

公式代換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 到它的箭頭。

相關內容