☰

問題

tag-icon • tag-icon microsoft-excel worksheet-function
問題

非常に複雑な Excel スプレッドシート (複数のワークシートにまたがる他の数式を含むセルにアクセスする数式) があり、最終的には他の複数のセル (パラメーター) からの入力を使用して 1 つのセル (出力) を計算します。出力セルから開始し、パラメーター セルで数式を直接表現するまで計算を再帰的に展開できる自動化された方法 (Excel マクロまたはスタンドアロン ツール) はありますか?

説明

コメントには数式評価ツールの提案がありました。Excel 2008 for Macでそれをアクティブにする方法は見つかりませんでしたが、その説明ユーザーがセルの評価をステップ実行できるようにしているようです。これは私が必要としているものではありません。必要なのは、数式を含む他のセルを参照する可能性のある特定のセルの数式を、最終セル (値を含むが数式を含まないセル) で表現される同等の数式に変換する方法です。

簡単な例を挙げてみましょう。

  • A1含む= B1 + C1
  • B1含む= B2 * B2
  • C1含む= C2 * C2
  • B2含む1
  • C2含む2

A1数式を評価すると、 の計算をステップ実行して の最終値を得ること ができます。必要なのは、実際に評価せずに数式を5展開するツールです。A1= B2 * B2 + C2 * C2

答え1

問題

これはEvaluate Formula関数の目的ではないため、Evaluate Formula関数では実行できません。評価する、それは数式を評価するためのものです。必要なのは、ある種の展開です。これは少し特殊なニーズなので、Excel ではツールとして実装されていませんが、Visual Basic 関数/マクロをいくつか作成すれば解決策があります。

VBAコードモジュール（マクロ）を作成します。このチュートリアル。

  1. Alt+を押すF11
  2. クリックしModuleてInsert。
  3. コードを貼り付けます。
Function CellFormula(Target As Range) As String
   CellFormula = Target.Formula
End Function

次に、セルに次の内容を入力します。=CellFormula(A1)

これにより、セルの数式がわかります。このコードの唯一の問題は、1 つのレベルでしか機能しないことです。含まれているセルの数式も展開する場合は、再帰を含むより複雑なコードが必要です。

ソリューション

長い道のりでしたが、この関数を実装する VBA マクロを作成しました。このコードがすべての数式で機能するとは言いませんが、ほとんど/一部の数式では機能します。また、このコードによって、最初に入力したコードと同等の数式が生成されたり、元のコードと同じ結果が得られたりするとは言いません。

ソースコード

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

=CellFormula(A1)これを機能させるには、マクロを作成し（回答の冒頭で説明したように）、コードをコピーして貼り付けるだけです。その後は、A1任意の種類の 1x1 セルで使用できます。

効果があったケース

実際に動作しているところがわかるように、いくつか例を作成しました。この例では、文字列での使用法を示します。完璧に動作することがわかります。唯一の小さなバグは、何らかの理由でアルゴリズムがセミコロンをコンマに変更することです。これを置き換えると (この例で行ったように)、正しい出力が得られます。 文字列を操作する

ここで、数値でどのように動作するかを確認できます。ここで、アルゴリズムが数学的演算シーケンスを考慮しないという最初の問題に直面します。そのため、赤い数字は 10 であるべきところ 6 になっています。敏感な演算 (加算や減算など) を括弧内に入れると、入力された式は、下部の緑色の数字 10 でわかるように、同じ出力になります。 数字を扱う

うまくいかないケース

このアルゴリズムは完璧ではありません。最も一般的な使用法のみを実装しようとしたため、範囲などの他のケースを処理する機能を追加することで改善できます。
この例でわかるように、SUM()範囲をパラメーターとして使用しました。アルゴリズムはセルの内容を上から下に復号化するため、SUM()他のものよりもパラメーターの置き換えから始めます。したがって、:周囲のすべてが置き換えられても はその場所に留まり、その近くの新しいセルが置き換えられ、その意味が変わります。したがって、出力は間違っています。したがって、この場合、このマクロを使用して元の数式を調べることしかできません。 範囲を操作する

答え2

質問の中で「トレース」という言葉が使われていることから、Excel の数式監査セクションのTrace Precendentsおよび関数についてよくご存知だと思いますが、Trace Dependents

dummies.com の各数式監査ツールについて簡単に説明します。

Excel 2010 の数式監査ツールを理解する

答え3

今日では、かなり明白な方法で を使用しますFORMULATEXT()が、当時は見逃されていたトリックであり、今日では、以前のバージョン (2007 を使用している私の義理の両親など) を使用している場合は、GET()名前付き範囲内で別の Excel 4 マクロ コマンド を使用します。

実行できる多くの機能の 1 つは、現在とまったく同じように、数式をテキストとして読み取ることですFORMULATEXT()。その後は、文字列を組み合わせるだけです。取得できるものがあるかどうかをテストするために少しIF()追加すれば、完了です。

関連情報