Excel の計算式の間違いは、関数の知識が足りないことより、表の形が数式を壊しやすくなっていることから起きる場合が多くあります。行を消したら参照が切れる、行を足しても合計の範囲が広がらない、数字が文字として入っていて計算から漏れる、数式の中に税率や単価を直接書いていて変更し忘れる。どれも、数式を書いたときは正しかったのに、後から表が変わって誤りになったものです。
防ぎ方は 2 段です。まず、入力・前提値・計算を分け、テーブルと名前で参照が位置に左右されない表にすること。そのうえで、Excel に備わっているエラー チェックとトレースで、定期的に数式を点検することです。操作は Microsoft の公式サポートで確かめた内容に沿って整理します。
計算式が間違う 5 つの仕組み
参照がセルの位置で決まっている
=B2+C2+D2 のような数式は、「B2 というセル」を位置で指しています。Microsoft のサポートによると、#REF! のエラーは、数式から参照されているセルが削除されたり、上に貼り付けられたりしたときに出ます。同じサポートは、個々のセルを並べる代わりに =SUM(B2:D2) のような範囲の参照を使えば、範囲の中の列を削除しても Excel が数式を自動で調整する、と説明しています。
行を足しても合計の範囲が広がらない
=SUM(C2:C100) の表の 101 行目に新しい行を足すと、その行は合計に入りません。エラーは出ず、数字が少し小さいだけなので、気づきにくい誤りです。
数字が文字として入っている
ほかの仕組みから書き出したデータや、「1,000円」のように単位ごと打ち込んだ数字は、見た目は数字でも文字列として扱われることがあります。Microsoft のサポートは、数式で扱う数値に通貨記号や区切り記号を付けて入力せず、数字だけを入れて表示は書式で整えるよう案内しています。
数式の中に数字が直接書かれている
=C2*1.1 のように、税率や単価を数式に直接書くと、その値が変わったときに、同じ数字を含む数式をすべて探して直す必要があります。直し漏れた 1 か所は、エラーにならずに古い値で計算を続けます。
検索する関数の既定の動きを知らない
Microsoft のサポートによると、VLOOKUP の 4 番目の引数（検索方法）を省略すると TRUE、つまり近似一致として扱われ、表の先頭の列が並べ替えられている前提で、最も近い値を探します。並べ替えていない表で省略すると、エラーにならずに違う行の値を返すことがあります。完全一致で探すときは、4 番目の引数に FALSE か 0 を指定します。また、列番号が範囲の列数より大きいと #REF! になります。同じページは、既定で完全一致を返す XLOOKUP 関数も案内しています。
自分の表を点検する表
次の 7 つを順に確かめます。当てはまるものがあれば、次の章の形に直します。
| 点検すること | 見つけ方 | 直す形 |
|---|---|---|
| 数式に数字が直接書かれていないか | 数式を表示して目で追う | 前提値を 1 か所に集め、名前を付けて参照する |
| 合計や参照の範囲が表の最後まで届いているか | 最終行のセルで参照先のトレース | 入力の一覧をテーブルにする |
| 同じ列の数式が途中から変わっていないか | エラー チェックの「領域内の数式と矛盾する数式」 | テーブルの集計列で 1 つの数式にそろえる |
| 数字が文字列になっていないか | エラー チェックの「文字列として書式設定されている数値」 | 数値に直し、単位は見出しに書く |
| VLOOKUP の 4 番目の引数を省略していないか | 数式を表示して確かめる | FALSE を明示するか、XLOOKUP を使う |
| エラーを隠していないか | 検索で「IFERROR」を探す | 表示を消す前に原因を直す |
| 入力する欄と計算する欄が混ざっていないか | 目で確かめる | 入力欄以外をロックしてシートを保護する |
壊れにくい表の形にする
1. 入力・前提値・計算を分ける
1 つのブックの中を、次の 3 つに分けます。
- 入力の一覧：1 行 1 件で、人が打ち込む欄だけを置く
- 前提値：税率、単価、締め日のように、計算に使う決まった値を 1 か所に置く
- 計算・集計：入力と前提値を参照して結果を出す。ここには手で数字を打たない
入力の一覧を 1 行 1 件・結合なしの形にする方法は、Excel 方眼紙を集計できる表に直す手順で扱っています。
2. 入力の一覧をテーブルにし、構造化参照で指す
入力の一覧をテーブルにすると、数式でセル番地の代わりに列の名前を使えます。Microsoft のサポート「Excel テーブルでの構造化参照の使い方」によると、=SUM(DeptSales[Sales Amount]) のような構造化参照の中の名前は、テーブルにデータを追加したり削除したりするたびに調整されます。行を足しても合計の範囲を直す必要がなくなります。
テーブルの列に数式を 1 つ入れると、その数式が列全体に自動で入る「集計列」になります（Microsoft サポート「Excel のテーブルの集計列を使用する」）。列の途中だけ別の数式になる、という誤りが起きにくくなります。
3. 前提値に名前を付ける
税率のセルを選び、数式バーの左の名前ボックスに「税率」と入れて Enter キーを押すと、数式で =C2*(1+税率) のように使えます。Microsoft のサポートは、名前を使うと数式の理解と管理が大幅に容易になると説明しています。付けた名前は、「数式」タブの「名前の管理」で一覧にでき、編集や削除もできます。
4. 入力欄に入力規則を付ける
「データ」タブの「データの入力規則」で、整数だけ、日付だけ、一覧から選ぶだけ、といった制限を付けると、文字の混入や打ち間違いを入口で防げます。ただし Microsoft のサポートによると、入力規則のメッセージが出るのはセルに直接入力したときだけで、コピーやオートフィルで入ったデータには出ません。貼り付けで入った誤りは、「データの入力規則」の横の矢印から「無効データのマーク」を選ぶと、赤い丸で囲まれて見つかります。
5. 計算の欄を保護する
入力してよいセルを選び、「セルの書式設定」の「保護」タブで「ロック」を外してから、「校閲」タブの「シートの保護」を掛けます。計算の欄を誤って上書きすることがなくなります。Microsoft のサポートは、シートの保護はセキュリティのための機能ではなく、ロックしたセルを変更できなくするためのものだと注意しています。見せたくない情報を守る目的には使いません。
数式をコピーしたときに参照がずれないよう、固定したいセルは絶対参照（$C$2）にします。F4 キーで切り替える方法はファンクションキーの使い方で扱っています。
数式を点検する手順
表を直した後も、月に 1 回や、表の形を変えたときには、次の順で点検します。
- 数式を表示する。 Ctrl＋`（グレーブ アクセント）で、セルの値と数式の表示を切り替えます（Microsoft サポート「Excel のキーボード ショートカット」）。同じ列の数式がそろっているか、数字が直接書かれていないかを目で追います。
- エラー チェックの印を見る。 Excel は「領域内の数式と矛盾する数式」「領域内のセルを除いた数式」「文字列として書式設定されている数値」「テーブル内の一貫性のない集計列の数式」などのルールで数式を点検しています（Microsoft サポート「Excel で数式のエラーを検出する」）。印の付いたセルは、1 つずつ理由を確かめます。
- 参照元をたどる。 合計のセルを選んで「数式」タブの「参照元のトレース」を押すと、どのセルを参照しているかが矢印で出ます。Microsoft のサポートによると、青い矢印はエラーのないセル、赤い矢印はエラーの原因のセル、黒い矢印は別のシートやブックのセルを示します。
- 長い数式を 1 段ずつ確かめる。 「数式」タブの「数式を評価する」（版によっては「数式の検証」）で、入れ子の数式の途中の結果を順に見られます。
- 大事な合計を見張る。 「ウォッチ ウィンドウ」に合計のセルを登録しておくと、別のシートを触っているときも値の変化を確かめられます。
- 別の方法で検算する。 合計したい列を選ぶと、ウィンドウ右下のステータス バーに合計と個数が出ます。数式の合計と突き合わせ、ずれていれば範囲か文字列の数字を疑います。
個人でできることと、職場で決めること
表の形を直すことと点検は、1 人でも始められます。一方で、請求や給与のように誤りの影響が大きい表は、作った人以外が点検する、表の形を変えたら日付と内容を記録する、といった決まりを職場で持たないと、作った人の注意力だけに頼ることになります。1 人しか中身を知らない表やマクロの引き継ぎ方はマクロの棚卸しと引き継ぎの手順、表そのものを別の仕組みに移すかどうかの考え方は脱エクセルが止まる理由と進め方で扱っています。
実行時の落とし穴
IFERROR でエラーを 0 や空白にしてしまう。 見た目は整いますが、参照切れや検索の失敗まで隠れ、合計が静かに小さくなります。IFERROR を使うのは、「見つからないのが正常」と分かっている場面に限ります。
数式のセルに、手で数字を上書きする。 「今月だけ」の調整で数式を数字に置き換えると、翌月もその数字が残ります。調整は別の列に「調整額」として入れ、計算に足します。
値の貼り付けで数式を消したことを忘れる。 外に渡すために値に直したシートを、翌月もそのまま使うと、元のデータを直しても結果が変わりません。渡す用のシートは別に作ります。
点検を締め切りの直前にだけする。 締め切り前は直す時間がありません。表の形を変えたときと、月初など決まった日に点検します。
エクセルと資料作成のほかの記事は、業務効率・IT の一覧から探せます。
参考資料
- Microsoft サポート「エラー値 #REF! を修正する方法」
- Microsoft サポート「Excel で壊れた数式のエラーを回避する方法」
- Microsoft サポート「VLOOKUP 関数」
- Microsoft サポート「Excel テーブルでの構造化参照の使い方」
- Microsoft サポート「Excel のテーブルの集計列を使用する」
- Microsoft サポート「数式で名前を定義して使用する」
- Microsoft サポート「セルにデータの入力規則を適用する」
- Microsoft サポート「ワークシートを保護する」
- Microsoft サポート「Excel で数式のエラーを検出する」
- Microsoft サポート「数式とセルの関係を表示する」
