☰

数式置換エクセル

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
数式置換エクセル

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 からその数式への矢印が描画されます。

関連情報