計算式でエラー発生時にエラー値に応じたメッセージを表示する計算式

計算式でエラーが発生した場合にエラータイプに基づいて特定のメッセージを表示し、エラーがない場合は計算式の結果をそのまま返す方法について解説します。

基本的なエラーハンドリングの概念

基本ルール

Excelでのエラーハンドリングは、主に IFERROR 関数を用いて行います。IFERROR 関数は、指定した式がエラーを返す場合に代替の値を返すことができます。これにより、エラーが発生した場合の処理を効率的に管理できます。

ただしIFERROR 関数単体ではエラーの原因がわかりません。エラーの原因により、返す値をさらに分岐させる場合には次に紹介するERROR.TYPE関数とCHOOSE 関数を組み合わせます。

詳細なエラーハンドリングの式の構築

計算式でエラーが発生した場合に、エラーの種類に応じて特定のメッセージを表示するためには、IFERROR 関数と CHOOSE 関数とERROR.TYPE関数を組み合わせて使用することが効果的です。以下がその式の基本的な形です。式の構造についてはのちほど解説します。

=IFERROR(計算式, CHOOSE(ERROR.TYPE(計算式), "エラーメッセージ1", "エラーメッセージ2", ..., "エラーメッセージN"))

ここで、計算式 はエラーをチェックしたいExcelの数式、エラーメッセージ1, エラーメッセージ2, …, エラーメッセージN はそれぞれのERROR.TYPE関数が返すエラータイプ番号に応じたカスタムメッセージです。

例えば交差部がない場合に発生する「#NULL!」では「1」、0で割った場合に発生する「#DIV/0!」では「2」を返します。わかりやすくメッセージを登録すると以下のような例が考えられます。

=IFERROR(計算式, CHOOSE(ERROR.TYPE(計算式), "#NULL!…交差部なし", "#DIV/0!…0で除算", "#VALUE!…式やデータ型の誤用", "#REF!…無効な参照", "#NAME?…無効な名前", "#NUM!…無効な数値引数", "#N/A…データ不明"))

ERROR.TYPE関数はエラーの種類によって番号を返します。返す番号については下記やリンク先を参照してください。

実際の応用例

例えば、A1で計算式を作り、エラーの管理をする場合は次のような式を使用します。

=IFERROR(A1, CHOOSE(ERROR.TYPE(A1), "#NULL!…交差部なし", "#DIV/0!…0で除算", "#VALUE!…式やデータ型の誤用", "#REF!…無効な参照", "#NAME?…無効な名前", "#NUM!…無効な数値引数", "#N/A…データ不明"))

この式では、A1の計算式を評価し、何らかのエラーが発生した場合には、ERROR.TYPE関数 に基づいて適切なエラーメッセージを表示します。エラーがなければ、式の結果をそのまま返します。メッセージはご自身で調整してください。

例えばA1に「=25/0」を入れて上記の計算式をA2へ入れれば「#DIV/0!…0で除算」が返ります。

A1に「=SUM(売上)」を入れて、範囲「売上」がなければ「#NAME?…無効な名前」が返ります。

関数の動作と構造の確認

ERROR.TYPE関数の動作：エラータイプと対応するメッセージの例

ERROR.TYPE関数は、その引数として指定されたセルのエラー値に基づいて特定の数値を返します。この関数が返す数値は、特定のエラータイプに対応しており、以下のように振り分けがあります。

  • #NULL!（範囲が交差しない）-> 1: “指定された範囲が交差していません。”
  • #DIV/0!（0での除算）-> 2: “0で除算しました。値を確認してください。”
  • #VALUE!（型の不一致）-> 3: “データ型が一致しません。”
  • #REF!（無効な参照）-> 4: “参照が無効になっています。”
  • #NAME?（名前エラー）-> 5: “数式に誤った名前が使用されています。”
  • #NUM!（数値エラー）-> 6: “数値引数が無効です。”
  • #N/A（データが見つからない）-> 7: “必要なデータが見つかりませんでした。”

ERROR.TYPE関数は、指定されたセルがエラーを含まない場合、#N/A エラーを返します。これは、エラーが存在しない場合には適用外であることを意味します。

CHOOSE関数の動作

CHOOSE 関数は、第一引数として指定された数値に基づいて、リストから特定の値を選択して返す関数です。この数値は、リスト内の位置を指示し、1から始まります。CHOOSE 関数は、ERROR.TYPE関数からの出力を活用して、特定のエラーに応じたカスタムメッセージを選択するのに使うことができます。

IFERROR関数の動作

IFERROR 関数は、指定された式がエラーを返す場合に、代わりに別の値を返すExcelの関数です。この関数は特に、エラーが発生する可能性のある計算式に使用され、エラー時にユーザー定義の値やメッセージを表示するために役立ちます。簡単に言うと、IFERROR 関数はエラーをキャッチし、それをカスタムの応答で置き換えるために使用されます。

=IFERROR(値, エラーの場合の値)
  • 値 : チェックする値または式。
  • エラーの場合の値 : 値 がエラーを返した場合に表示する値。

使用例

=IFERROR(A1/B1, "0では割れません")

この式では、A1をB1で割った結果を返しますが、もしB1が0であるなどの理由でエラーが発生した場合は、「0では割れません」と表示します。これにより、エラーが発生した場合でも、シート上に意味不明なエラーコードを表示させずに、より理解しやすいメッセージに置き換えることができます。

関数の連携

以下のようにERROR.TYPE関数 と CHOOSE 関数を組み合わせることで、エラーが発生した際に特定のエラーメッセージを表示させることができます。ただし計算式の結果がエラー値でないのなら「#N/A」が帰ります。

=CHOOSE(ERROR.TYPE(計算式), "エラーメッセージ1", "エラーメッセージ2", ..., "エラーメッセージN")

上記の式でエラーがない場合に元の計算式の結果を返したいならば、IFERROR 関数で包みます。具体的な使用例は次のようになります。

=IFERROR(計算式, CHOOSE(ERROR.TYPE(計算式), "エラーメッセージ1", "エラーメッセージ2", ..., "エラーメッセージN"))

ここで、計算式 がエラーを返すと、ERROR.TYPE関数がエラーのタイプに応じた数値を返します。この数値を CHOOSE 関数が受け取り、対応するカスタムメッセージを返します。これにより、ユーザーはエラーの内容を具体的に理解し、適切な対応をとることが容易になります。

コメント