【Excel】エクセルでDIV/0!やVALUE!エラーを非表示にする方法
Excelで計算式を入力すると、DIV/0!やVALUE!、N/Aなどのエラーが表示されることがあります。
エラーは数式や元データの問題を知らせる大切な表示ですが、集計表、請求書、グラフ用データなどでは、空欄や見やすい案内に置き換えたい場面も少なくありません。
エラーを隠すときは、原因を確認してからIFERROR関数やIF関数を使うことが基本です。
DIV/0!はゼロまたは空白で割ったとき、VALUE!は文字列など計算できない値を使ったときに発生します。
この記事では、DIV/0!やVALUE!を非表示にする数式、空白表示の注意点、グラフへの影響まで順に解説します。
DIV/0!やVALUE!を非表示にするIFERROR関数
それではまず、もっとも手軽にエラー表示を非表示にするIFERROR関数について解説していきます。
| 商品名 | 売上 | 数量 | 単価 |
|---|---|---|---|
| りんご | 600 | 3 | =B2/C2 |
| みかん | 0 | 0 | =B3/C3 |
| ぶどう | 900 | 3 | =B4/C4 |
上の表では、みかんの数量が0のため、単価を求めるC列の割り算でDIV/0!が発生します。
基本形はIFERROR(計算式,エラー時に表示する値)です。
単価を空欄にする式は、=IFERROR(B2/C2,””)となります。
空白へ置き換える数式
IFERROR関数で空文字列を指定すると、エラーが表示されるセルを見た目上は空白にできます。
先頭のデータ行が2行目なら、D2に=IFERROR(B2/C2,””)と入力しましょう。
数式中の二重引用符だけを指定した部分は、何も表示しないという意味です。
空欄のセルを含む帳票では、DIV/0!をそのまま残すよりも、利用者が判断しやすい表になります。
入力後はD2右下の小さな四角を下へドラッグし、D4までオートフィルします。
【操作のポイント】IFERRORで空欄にしても、割る数が0である原因そのものは解決していません。
ゼロや文字列を表示する数式
続いては、空白以外の表示を指定する方法を確認していきます。
単価が計算できない行に0を表示したい場合は、=IFERROR(B2/C2,0)を使用します。
ただし、実際には金額が0円ではなく未計算という意味なら、0を表示すると集計の解釈を誤るかもしれません。
利用者へ理由を伝えるなら、=IFERROR(B2/C2,”入力待ち”)のように文字列を返す方法もあります。
数値として集計する列には0、判断を促す一覧表には空欄や「未入力」を使うなど、用途で表示を分けることが重要です。
文字列を返したセルは計算対象にならないため、後続の数式との関係も確認しましょう。
【操作のポイント】表示だけを整える列と、再計算に使う数値列を分けると管理しやすくなります。
オートフィル時の参照確認
続いては、数式を複数行へコピーするときのセル参照を確認していきます。
D2に入力した=IFERROR(B2/C2,””)を下方向へコピーすると、D3ではB3/C3、D4ではB4/C4へ自動調整されます。
このように行ごとに変化させたい参照は相対参照のままで問題ありません。
一方、消費税率など固定セルを掛ける式では、$F$1のように絶対参照を使います。
オートフィル後は、先頭行だけでなくエラーが出る行と通常計算できる行の両方を確認しましょう。
数式バーでセル参照を確認すれば、意図しない列や行を参照するミスを早期に見つけられます。
【操作のポイント】1行目に見出しがある表では、通常は2行目を起点に数式を作成します。
ゼロ除算を防ぐIF関数
続いては、割り算の前に条件を確認してDIV/0!を防ぐIF関数について解説していきます。
| 担当者 | 実績 | 目標 | 達成率 |
|---|---|---|---|
| 田中 | 80 | 100 | =B2/C2 |
| 佐藤 | 0 | 0 | =B3/C3 |
| 鈴木 | 120 | 100 | =B4/C4 |
IFERRORは幅広いエラーに対応できますが、ゼロ除算だけを対象にするならIF関数で原因を明示する方法が向いています。
達成率を空欄にする基本式は、=IF(C2=0,””,B2/C2)です。
先に目標セルC2が0かどうかを判定し、0以外のときだけ割り算を実行します。
分母がゼロか判定する手順
それではまず、分母だけを確認するIF関数の書き方を解説していきます。
D2へ=IF(C2=0,””,B2/C2)と入力すると、C2が0の場合は空欄、それ以外の場合は実績を目標で割った値が表示されます。
この数式は、エラーが起きてから隠すのではなく、割り算を行う条件を先に設定する考え方です。
DIV/0!の原因が分母の0だと明確な場合は、IF関数を使うと数式の意図を読み取りやすくなります。
達成率のセルにはパーセント表示を設定すると、0.8が80パーセントとして見やすく表示されます。
【操作のポイント】判定するC2と実際に割るC2が同じセルであることを必ず確認します。
空白セルを含めて判定する式
続いては、未入力の空白を含む表での判定方法を確認していきます。
Excelでは空白セルを分母にして割り算を行うと、実質的に0として扱われDIV/0!になる場合があります。
=IF(OR(C2=0,C2=””),””,B2/C2)とすれば、0と空白のどちらにも対応できます。
ただし、C2=0だけでも空白を判定できるケースはあります。
入力規則や外部データの取り込みなどで空文字列が返る表では、OR関数を使って条件を明示すると安心です。
未入力と数値の0を業務上区別する必要がある場合は、同じ空欄表示にせず、別のメッセージを返す設計も有効です。
【操作のポイント】空白に見えるセルでも、数式による空文字列かどうかで判定結果が変わることがあります。
IFERRORとIF関数の使い分け
続いては、二つの関数を使い分ける基準を確認していきます。
IFERRORはDIV/0!だけでなくVALUE!、REF!、N/Aなどもまとめて処理します。
そのため、簡潔に見せたい完成表には便利ですが、本来は修正すべき参照ミスまで隠れる可能性があります。
IF関数は条件を細かく示せるため、ゼロ除算の理由を管理したい計算表に適しています。
原因が想定どおりかを確認できる場面ではIF関数、複数のエラーを表示上だけ処理したい場面ではIFERROR関数が実用的です。
【操作のポイント】エラーを非表示にした後も、元データや計算ロジックを定期的に点検しましょう。
VALUE!エラーの原因確認と数式修正
続いては、VALUE!エラーの原因を確認し、適切な数式へ修正する方法を解説していきます。
| 商品 | 単価 | 数量 | 金額 |
|---|---|---|---|
| ノート | 120 | 2 | =B2*C2 |
| ペン | 百円 | 3 | =B3*C3 |
| 付箋 | 250 | 4 | =B4*C4 |
VALUE!は、計算できない文字列、余分な空白、日付形式の不一致などが数式に含まれると発生します。
たとえばB3に「百円」という文字が入力されていると、=B3*C3は数値計算できずVALUE!になります。
VALUE!を隠す前に、数値にすべきセルへ文字列が混ざっていないかを調べることが優先です。
文字列と数値の混在確認
それではまず、数値列に混ざった文字列を見つける方法について解説していきます。
対象列を選択し、ホームタブの数値表示形式や左寄せ表示を確認すると、文字列として保存された数値を見つける手掛かりになります。
セル左上の緑色の三角が表示される場合は、エラーインジケーターから数値へ変換できることがあります。
「1,200円」や「百円」のように単位まで入力した値は文字列なので、掛け算や合計の元データには使えません。
単価列には1200のような数値だけを入力し、円という単位はセルの表示形式で付ける方法が正確です。
【操作のポイント】見た目が数字でも、文字列として保存されている値はSUM関数や四則演算で期待どおりに扱えません。
VALUE関数とNUMBERVALUE関数
続いては、文字列の数値を変換する関数を確認していきます。
文字列の「1200」を数値に変換するなら、=VALUE(B2)を使えます。
区切り記号を含むデータや海外形式の数値では、=NUMBERVALUE(B2,”.”,”,”)のように小数点記号と桁区切り記号を指定する方法があります。
「1,200」を数値化して数量C2と掛ける式は、=NUMBERVALUE(B2,”.”,”,”)*C2です。
外部システムから貼り付けたデータでは、半角スペースや改行コードが原因になることもあります。
その場合はTRIM関数やCLEAN関数を組み合わせ、不要な文字を除いてから変換しましょう。
【操作のポイント】変換用の補助列を作り、元データを残したまま結果を検証すると安全です。
詳細イメージ図による数式入力
続いては、数式バーからVALUE!対策の式を入力する画面を確認していきます。
この例では、B3の「1,200」をNUMBERVALUE関数で数値へ変換し、C3の数量と掛け算しています。
IFERRORを外側に置くことで、変換できないデータが残っていても完成表にVALUE!を表示しない構成です。
【操作のポイント】数式バーの赤枠部分を確認し、引数の区切り記号と参照セルを誤らないようにします。
N/AやREF!を含むエラー表示の整え方
続いては、DIV/0!やVALUE!以外のエラーを含む一覧表の整え方について解説していきます。
| 商品コード | 検索結果 | 在庫数 |
|---|---|---|
| A001 | =XLOOKUP(A2,F:F,G:G) | 12 |
| A999 | =XLOOKUP(A3,F:F,G:G) | N/A |
| A003 | =XLOOKUP(A4,F:F,G:G) | 8 |
検索値が見つからないとN/A、削除されたセルを参照するとREF!が発生します。
表示の統一だけを目的にするならIFERRORが便利ですが、エラーの種類で処理を分けたいときは専用関数も検討しましょう。
IFNA関数による検索エラー処理
それではまず、検索関数で発生するN/Aへの対処を解説していきます。
XLOOKUP、VLOOKUP、MATCHなどで検索対象がない場合、N/Aが返ることがあります。
=IFNA(XLOOKUP(A2,F:F,G:G),”該当なし”)と入力すると、検索不能な場合だけ「該当なし」と表示できます。
IFNAはN/Aだけを処理するため、REF!など別の重大なエラーを見落としにくい点が特徴です。
商品マスタに未登録なのか、コード入力を間違えたのかを確認する運用にも向いています。
【操作のポイント】検索結果が存在しないことを正常な状態として扱うなら、IFNAが読みやすい選択です。
参照エラーを隠しすぎない判断
続いては、REF!エラーを扱うときの注意点を確認していきます。
REF!は、数式が参照していた行、列、シートなどを削除したときに表示されます。
IFERRORで空欄にできても、集計結果が欠けた状態で資料を配布してしまう危険があります。
REF!は表示を消すより、数式を修正して正しい参照先へ戻すべきエラーです。
数式タブの参照元のトレースや、数式バーを使い、どの参照が失われたのかを確認してください。
【操作のポイント】予期しないエラーまで一括で非表示にしていないか、重要な集計列を点検します。
エラー値を使う後続計算の設計
続いては、エラーを空欄にした後の合計や平均の考え方を確認していきます。
IFERRORで””を返すセルは見た目が空欄でも、厳密には空文字列を返す数式セルです。
SUM関数は多くの場合に空文字列を無視しますが、AVERAGE関数、COUNT関数、ピボットテーブルでは結果の見え方を確認する必要があります。
集計対象を明示したい場合は、=SUMIF(D2:D100,”<>”,D2:D100)のように空欄以外を条件にする方法があります。
非表示のエラーが「0」なのか「未入力」なのかで、平均値や件数の意味は変わります。
【操作のポイント】見た目の整形と、集計に使う値の意味を別々に確認することが大切です。
グラフと印刷で見やすくする表示設定
続いては、エラーを含む表をグラフや印刷へ使うときの表示設定について解説していきます。
| 月 | 売上 | 前年比 |
|---|---|---|
| 4月 | 120000 | 105% |
| 5月 | 130000 | DIV/0! |
| 6月 | 140000 | 110% |
グラフの元データにエラー値があると、線が途切れたり、意図しない表示になったりします。
帳票として印刷する前には、セルの表示、グラフ、集計式を一緒に確認しましょう。
グラフ用データの空欄表示
それではまず、グラフへ渡すデータを整える方法について解説していきます。
前年比を算出できない月をグラフから除きたい場合、=IFERROR(B2/C2-1,NA())のようにNA関数を使う考え方があります。
NA()はN/Aを返しますが、グラフでは欠損値として扱いやすい場合があります。
表で空欄に見せる式と、グラフで欠損として扱う式は、目的に応じて分けると見栄えが安定します。
グラフのデータ選択や非表示と空白のセル設定も確認し、空白を間隔、ゼロ、データ要素を線で結ぶのどれとして扱うかを選びます。
【操作のポイント】グラフ専用の補助列を作ると、表の表示を崩さずに調整できます。
条件付き書式による入力漏れの可視化
続いては、エラーを消しつつ入力漏れを見逃さない方法を確認していきます。
エラー表示を空欄へ変えるだけでは、入力待ちの行と意図的に空欄にした行を判別しにくくなります。
条件付き書式を使い、必要な入力セルが空白なら淡い色を付けると、作業者が補完すべき場所を把握できます。
たとえば数量列C2:C100を選び、「数式を使用して、書式設定するセルを決定」で=C2=””を条件にできます。
完成資料ではエラーを見せず、作業用シートでは入力漏れを目立たせるという役割分担も効果的です。
【操作のポイント】条件付き書式は元データを変えず、視覚的な注意だけを加えられます。
印刷前の数式と表示値の確認
続いては、印刷やPDF化の前に行う確認について解説していきます。
ホームタブの検索と選択から数式を選択すると、計算式が入ったセルを確認しやすくなります。
また、数式タブのエラーチェックを使えば、非表示になっていないエラーや不整合を探せます。
印刷プレビューでは、空欄が多すぎて情報が欠けて見えないか、エラー用の案内文がページ幅を乱していないかも確認しましょう。
資料提出前は、通常データ、分母が0の行、空白の行、文字列が混ざる行を用意して式を試すことが確実です。
【操作のポイント】エラーが出ないことだけでなく、数値の意味を読み手が誤解しない表示かを確認します。
まとめ エクセルでVALUE!やDIV/0!エラーを非表示にする方法
ExcelでDIV/0!やVALUE!エラーを非表示にするには、まずエラーが起きる理由を確認することが出発点です。
ゼロ除算を見た目だけ整えるなら=IFERROR(B2/C2,””)が使えます。
分母が0の場合だけを明確に処理するなら、=IF(C2=0,””,B2/C2)が適しています。
VALUE!が出るときは、数値列に文字列、単位、余分な空白、異なる形式のデータが混ざっていないかを確認しましょう。
N/AはIFNA関数、検索不能以外の問題まで含めて処理する場合はIFERROR関数というように、エラーの種類で使い分ける方法が有効です。
エラーを非表示にする目的は表をきれいに見せることですが、誤った計算結果を隠すことではありません。
表示用の列、計算用の列、グラフ用の列を必要に応じて分け、空欄、0、該当なしの意味を明確にしたExcelファイルを作成しましょう。
