Excel でカスタム関数で予期される値が計算されない場合があります

  • [アーティクル]
  • 適用対象:
    Microsoft Excel

現象

ワークシートを計算すると、一部のセルが間違った値を計算しているように見える場合があります。

原因

この問題は、以下の条件に該当する場合に発生します。

  • ワークシートのセルには、カスタム関数が含まれています。

  • カスタム関数には、ワークシート上のセル範囲を参照する 1 つ以上の引数が含まれています。

  • カスタム関数の結果は、直接参照するセルよりも多くのセルに依存します。

この動作は、Microsoft Excel の設計によるものです。 Excel は、カスタム関数を含むセルを計算すると、カスタム関数に引数として渡されるすべてのセル範囲を再計算します。 関数の結果が、関数によって明示的に参照されていないセルに依存している場合、それらのセルは再計算されない可能性があります。

回避策

この問題を回避するには、次のいずれかの方法を使用します。

方法 1: 関連するすべてのセル範囲が渡されるように関数を変更する

関数の結果を計算するために必要なすべてのセルを引数として受け入れるように関数を変更します。

方法 2: カスタム関数を揮発性にする

Microsoft は、例示のみを目的としてプログラミング例を提供しており、明示または黙示にかかわらず、いかなる責任も負わないものとします。 これには、市販性または特定の目的との適合性についての黙示の保証も含まれますが、これに限定はされません。 この記事は、説明されているプログラミング言語、手順を作成およびデバッグするために使用されているツールに読者が精通していることを前提にしています。 Microsoft サポート エンジニアは、特定のプロシージャの機能を説明するのに役立ちますが、これらの例を変更して、特定の要件を満たすために追加の機能を提供したり、プロシージャを構築したりすることはできません。カスタム関数を揮発性にした場合、この問題は発生しません。 カスタム関数を揮発性にするには、次のコード行を関数に追加します。

Application.Volatile

注:

カスタム関数を揮発性にした場合、値を変更するか、開いているブックを再計算するたびに再計算されます。 これは、ワークシート モデルのパフォーマンスに影響する可能性があります。

方法 3: Excel で開いているすべてのブックを強制的に再計算する

Ctrl キーを押しながら Alt キーを押しながら F9 キーを押して、開いているすべてのブックの値を再計算します。 Microsoft Office Excel 2007 で、Ctrl キーを押しながら Alt キーを押しながら Shift キーを押しながら F9 キーを押して、開いているすべてのブックの値を再計算します。

詳細

問題の例

この問題を説明するには、次の手順に従います。

  1. 開いているブックを閉じて保存し、新しいブックを開きます。

  2. Visual Basic エディターを起動します (Alt キーを押しながら F11 キーを押します)。

  3. [挿入] メニューの [モジュール] をクリックします。

  4. 次のコードをモジュール シートに入力します。

    ' This function counts the number of blank cells by starting from the cell 
' referred to by the rngStartCell argument and moving up the column.

Function FindTextUp(rngStartCell As Range) As Single
    Dim iIndex As Integer

For iIndex = 0 To 100
        If rngStartCell.Value <> "" Then
            FindTextUp = iIndex
            Exit Function
        Else
            Set rngStartCell = rngStartCell.Offset(-1, 0)
        End If
    Next iIndex
End Function

  5. Alt キーを押しながら F11 キーを押して Excel に戻ります。

  6. セル A2 に「Test」と入力し、Enter キーを押します。

  7. セル A10 に次の数式を入力し、Enter キーを押します。

    =FindTextUp(A9)

    数式は、値 7 を返します。

  8. セル A5 に「Another test」と入力し、Enter キーを押します。

数式は、値 4 が予想される場合でも値 7 を返します。 この例では、FindTextUp 関数はセル A9 を明示的に参照します。 ただし、関数は、ワークシートに入力されたデータに応じて、セル A1:A8 に依存する場合があります。

この記事の「回避策」セクションでメソッド 1 を実装すると、関数は予想される結果を計算します。 次のコード行は、期待される結果が計算されるように、この例の関数を変更する方法を示しています。

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single

ここで、手順 7 の関数呼び出しを次の関数呼び出しに置き換えると、関数は常に期待される結果を返します。

=FindTextUp(A9,A1:A8)