doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

Excelの数式エラーまとめ

スポンサーリンク

Excelを使いこなし、関数を使うようになってくると、それだけ多くのエラーに遭遇するようになってきます。

イライラしますが、数式エラーは何かが間違っていることを我々に教えてくれています。エラーについての知識を持っていることが重要です。

数式エラーが発生しても、イラッとしたり、パニックにならないでください。落ち着いて調査してみましょう。経験を積むにつれて、多くのエラーを回避し、発生したエラーをより迅速に修正できるようになります。

エラーの修正

以下がエラーに遭遇した場合に、そのエラーを解決すまでに実行するステップになります。 1つのエラーが別のエラーの起因となり、数式エラーがワークシートを埋め尽くすことがよくあります。 根本的な問題を見つけて修正すると、一気に解決することも多いです。

1.エラーを見つけます。 2.エラーをその関数まで遡ります。 3.エラーの原因を特定します。必要に応じて、分かりやすい単位に数式を分割していきます。 4.エラーを修正します。

エラーのトラップ

エラーのトラップは、エラーを「キャッチ」して、そもそも表示されないようにする方法です。これは、特定のエラーが発生する可能性が高く、エラーメッセージが表示されないようにする場合に役立ちます。2つの基本的なアプローチがあります。

  1. IFERRORまたは ISERRORでエラーをトラップします。 このアプローチでは、エラーを監視し、エラーが検出されたときにはそのエラーへの別の手段を準備しておきます。
  2. 必要な値が利用可能になるまで計算をしない。 この場合、エラーを監視する代わりに、関数を利用していく大元の値をチェックし、エラーが発生しないように調整します。

Excelのエラーコード

Excelの関数を使用していると、9つのエラーに遭遇する可能性があります。

此処から先は、各数式エラーを、エラーの修正方法に関する情報とともに説明します。

#DIV / 0!エラー

よく合うのは、関数を作成したが、データがまだ揃っていない場合に表示されるエラーです。たとえば、データが入力されていないか、まだ利用できないため、ワークシートのセルが空白のものを参照しようとしている場合などです。また、基準が範囲内のどのセルにも一致しない場合、AVERAGEIFおよびAVERAGEIFS関数でゼロ除算エラーが表示される場合があります。#DIV / 0!は名前が示すように、数式がゼロ、またはゼロに相当する値で除算しようしたときに表示されるエラーでもあります。

たとえば、次のワークシートでは、D4が空のため、DIVエラーがセルE4に表示されます。これは、空(空白)のセルはExcelによってゼロとして評価され、C4はゼロで除算できません。

f:id:doodle-on-web:20191002130937p:plain

多くの場合、空のセルは避けられません。IFERRROR関数を使用して、#DIV / 0をトラップし、必要に応じて、よりわかりやすいメッセージを表示したほうがいいでしょう。

#NAME?エラー

NAME?エラーは、Excelが入力したものを認識していないことを示します。これは、関数名のスペルを間違っていたり、存在しない名前付き範囲を指定してしまっていたり、セル参照が誤って入力されているということを表現しています。たとえば、次の画面では、F3 のVLOOKUP関数のスペルが間違っています「VLOKUP」。VLOKUPは有効な名前ではないため、式は#NAME?を返します。

f:id:doodle-on-web:20191002130851p:plain

#NAMEを修正するには、エラー箇所を特定し、スペル確認・構文の修正が必要です。

#N / Aエラー

#N / Aエラーは、数式が何をが見つけられない場合に表示されます。元データに不足があるか、検索対象と文字が異なっているかということが考えられます。多くの場合、#N / Aエラーは、余分なスペース文字、スペルミス、lookupの指定範囲ミスが原因です。主に#N / Aエラーの影響を受ける関数は、VLOOKUP、HLOOKUP、LOOKUP、MATCHなどのルックアップ系の関数です。

たとえば、次の画面では、「ホットコーヒー」がルックアップテーブルにないため、F3の数式は#N / Aを返します。

f:id:doodle-on-web:20191002131002p:plain

E3の値が「コーヒー」、「ケーキ」などに変更された場合、VLOOKUPは正常に機能し、アイテムのコストを取得します。

#N / Aエラーを防ぐ最善の方法は、ルックアップ値とルックアップテーブルが正しく、最新のデータであることを確認することです。必要に応じて、IFERRORを使用して#N / Aエラーをトラップし、よりわかりやすいメッセージを表示するか、まったく表示しないことができます。

詳細: #N / Aエラーの修正方法。

www.doodle-office.work

#NUM!エラー

#NUM!エラーは、数値が大きすぎるか小さい場合、または計算が不可能な場合に発生します。たとえば、負の数の平方根を計算しようとすると、#NUMエラーが表示されます。

f:id:doodle-on-web:20191002131023p:plain

上の画面では、列Bの平方根数を計算するために使用されるSQRT関数があります。C5の式は#NUM!を返します。B5の値が負であり、負の数の平方根を計算することができないため。

DATEDIF関数内で開始日と終了日を逆にすると、#NUMエラーが発生する場合があります。

一般的に、#NUM!は、計算を再度可能にするために必要に応じて入力を調整必要があります。

VALUE!エラー

VALUE!エラーは、入力値のタイプ(表示形式、つまり、日付、時刻、数値、テキストなど)が有効でない場合、エラーが表示されます。これは、セルが空白のままの場合、または、日付がExcelによってテキストとして評価された際に発生しやすいです。

たとえば、次の画面では、セルC3にテキスト「NA」が含まれており、F2の数式は#VALUE!を返します。

f:id:doodle-on-web:20191002131043p:plain

以下では、MONTH関数は「apple」は日付ではないため、「apple」から月の値を抽出できません。

f:id:doodle-on-web:20191002131101p:plain

注:#VALUE!も表示される場合があります。配列数式を作成し、Ctrl + Shift + Enterで数式を入力するのを忘れるとエラーになります。

#VALUE!を修正するには エラーの場合、問題のある値を追跡し、適切なタイプの値を指定する必要があります。

#REF!エラー

#REF!エラーは、Excelの関数式で最も目にする可能性があるエラーでしょうか。参照が無効になると発生します。多くの場合、これはシート、行、または列が削除されたり、相対参照を持つ数式が参照が無効な新しい場所にコピーされた時などに発生します。

たとえば、次の画面では、C8の数式がE4にコピーしました。この新しい場所では、範囲C3:C7が相対的であるため無効になり、式は#REF!を返します。

f:id:doodle-on-web:20191002131119p:plain

#REF!元のセル参照が永久になくなるため、エラーを修正するのは多少難しい場合があります。行または列を削除して#REF!を表示した場合 エラーが発生した場合は、すぐに操作を取り消して、削除する前に式を調整する必要があります。

#NULL!エラー

#NULL!エラーはExcelでは非常にまれであり、通常、2つのセル参照の間にコンマ(、)またはコロン(:)の代わりにスペース文字が使用されるタイプミスの結果です。たとえば、F3の数式の下の画面では、#NULLエラーが返されます。

f:id:doodle-on-web:20191002131134p:plain

技術的には、スペース文字は「範囲の交差」演算子であり、#NULL!エラーは、2つの範囲(C3とC7)が交差しないことを報告しています。ほとんどの場合、必要に応じてスペースをコンマまたはコロンに置き換えることにより、NULLエラーを修正できます。

####エラー

技術的にはエラーではありませんが、通常の結果ではなくハッシュ文字列(###)を表示する式が表示される場合があります。たとえば、次の画面では、C3の式は列Bの日付に5日を追加しています。

f:id:doodle-on-web:20191002131154p:plain

この場合、列Cの日付は長い形式でフォーマットされ、列に収まらないため、ハッシュ文字またはポンド文字(###)が表示されます。このエラーを修正するには、列を広くするだけ大丈夫です。

注:Excelは負の日付を表示しません。数式が負の日付値を返す場合、Excelは#####を表示します。