こんにちは。実務Excel研究員です。
今回は、実際に職場で起きた「数式は正しいのに金額が合わない」事件を、調べた順番どおりに書き残しておきます。原因は、表の中に同じ意味の列が3本も並んでいたことでした。
先に結論だけお伝えすると、Excelは「どの列が正しいデータか」を一切判断してくれません。「金額」「金額（修正後）」「確認用金額」が並んでいても、Excelにとってはどれもただの1列です。どれを使うかはすべて人間が決めていて、その判断が1回ずれただけで、集計は静かに間違い続けます。
この記事では、事件の調査記録をたどりながら、「どの列が本物かの見極め方」「3列を1列にまとめる手順」「列を消しても表が壊れない順番」「二度と増やさないためのルール」までを順番に解説します。似た名前の列が並んだ表を今まさに開いている方は、手元のファイルと見比べながら読み進めてみてください。
事件の記録｜交通費精算が18,640円合わなかった朝
9:10 「数式は合ってるはずなんです」
月末締めの朝、経理の同僚から相談を受けました。部署の交通費精算表の合計が、経理システムに登録された支払額より18,640円少ないというのです。
表は1か月分、212行。SUM関数で「金額」列を合計しているだけのシンプルなものでした。同僚は数式を3回見直して、範囲の指定漏れも、非表示の行もないことを確認済み。「Excelが壊れてるんですかね」と、半分本気で困っていました。
9:25 数式ではなく「列」がおかしいと気づく
私も数式を確認しましたが、=SUM(C2:C213) で、範囲は最終行まできちんと入っています。エラー表示もありません。
そこで視線を表全体に移すと、C列「金額」のすぐ右に、D列「金額（修正後）」、E列「確認用金額」が並んでいることに気づきました。列幅が狭められていて、ぱっと見では目立たない状態でした。
9:40 D列の中身を見て原因が判明
D列「金額（修正後）」は、ほとんどが空白でした。値が入っていたのは212行中17行だけ。話を聞くと、申請内容に誤りがあった行だけ、修正後の金額をD列に書き込んでいたそうです。C列の元の金額はそのまま残して。
つまり、合計の数式が見ていたC列には「修正前の金額」が入り続けていて、正しい金額が入ったD列は、どの数式からも参照されていなかったのです。17行分の修正差額を足し合わせると、ちょうど18,640円。これで数字の謎は解けました。
10:05 E列は誰も使っていない「化石」だった
では、E列「確認用金額」は何だったのか。入力履歴をたどると、月の途中に別の担当者が「念のため」とC列をコピーして作った列でした。150行目までしか値がなく、それ以降は空白。作った本人も、もう存在を忘れていたそうです。
この表は3週間、誰も違和感を持たないまま使われていました。エラーが出ず、合計欄にはそれらしい数字が表示され続けていたからです。ここが、重複列のいちばん怖いところです。
3本の「金額」列、それぞれの正体
調査が終わった時点の状態を整理すると、次のようになります。
| 列 | 列名 | 入っていたデータ | 数式からの参照 | 実態 |
|---|---|---|---|---|
| C列 | 金額 | 212行すべて（ただし修正前の値） | SUMが参照 | 集計に使われているが中身が古い |
| D列 | 金額（修正後） | 17行だけ | どこからも参照なし | 正しい値があるのに集計に入っていない |
| E列 | 確認用金額 | 150行目まで（C列のコピー） | どこからも参照なし | 役目を終えた化石 |
3本とも「金額」を表しているのに、正しい合計を出せる列は1本もありませんでした。C列とD列を「組み合わせて」初めて正しい金額になる構造です。これは人間が頭の中で補正していないと成り立たない表で、担当者が変わった瞬間に確実に壊れます。
重複列は「丁寧な人」ほど作ってしまう
この事件で印象的だったのは、3本の列を作った人たちに悪気がまったくなかったことです。むしろ、全員が慎重に仕事をしようとした結果でした。重複列が生まれるきっかけは、だいたい次の3つに分かれます。
元の値を消すのが怖い。 金額を上書きすると、修正前にいくらだったかがわからなくなる。だから修正後の値を別の列に書く。今回のD列がまさにこれです。記録を残したいという気持ちは正しいのですが、残し方を間違えると集計が壊れます。
確認作業のために列を足す。 ダブルチェックのために列をコピーして、見比べながら作業する。作業が終わったあと、その列を消す人はあまりいません。E列はこのパターンでした。
「消して困るくらいなら残しておこう」が積み重なる。 使っていない気がするけれど、誰かが使っているかもしれない。その不安から削除を先送りにする。1回1回の判断は小さくても、半年もすれば「どれが現役の列か誰も説明できない表」が完成します。
どれも慎重さから生まれるミスなので、「気をつけましょう」では防げません。仕組みで止める必要があります。その方法は記事の後半で紹介します。
放置した表で起きること
今回の事件は「集計が古い列を見ていた」というパターンでしたが、同じ意味の列が並んだ表では、ほかにも次のようなトラブルが起きます。
集計が静かに間違い続ける
SUMやSUMIF、COUNTIFが参照している列が1本ずれるだけで、結果は丸ごと信用できなくなります。しかもエラーは出ません。今回の18,640円も、経理システムという「別の答え」と突き合わせる機会がなければ、翌月以降もずっと気づかれなかったはずです。
VLOOKUPの列番号がずれる
VLOOKUPの第3引数は「範囲の左から何番目の列か」を数字で指定します。重複列を途中で削除したり、並び順を入れ替えたりすると、この番号がずれて、まったく別の列の値を返すようになります。今回の表でも、別シートにあった集計用のVLOOKUPが「4列目」を指定していて、E列を消した瞬間に参照先が変わってしまうところでした。列番号の数え方と、ずれを防ぐ書き方はVLOOKUPの基本と列番号がズレる仕組みで詳しく解説しています。
更新が片方にしか入らない
同じ情報が2列に分かれていると、修正するたびに「どっちを直したか」を覚えておく必要があります。1回でも片方を忘れれば、2つの列に違う値が入った状態になり、どちらが正しいのかを調べ直す手間が発生します。
引き継ぎのたびに列が増える
表を引き継いだ人は、まず「どの列を使えばいいのか」から推理しなければなりません。確信が持てないと、自分用の確認列をもう1本足してしまう。こうして列は世代をまたいで増えていきます。
手を動かす前に｜どの列が「本物」かを見極める3つの確認
いきなり列を消すのは危険です。まずは次の3つを確認して、どの列を残すかを決めます。今回の事件でも、この順番で調べました。
確認1 数式から参照されている列を洗い出す
合計欄などのセルを選んで、「数式」タブの「参照元のトレース」をクリックすると、そのセルがどこを参照しているかが青い矢印で表示されます。今回はC列にだけ矢印が伸び、D列とE列には何もつながっていませんでした。
逆方向の確認も大事です。D列やE列のセルを選んで「参照先のトレース」を実行すると、その列を使っている数式があるかどうかがわかります。別シートから参照されている場合は、点線の矢印とシートのアイコンが表示されます。ここで矢印が出た列は、消すと別の場所で #REF! エラーが出るので要注意です。
確認2 件数を数えて、列の「埋まり具合」を比べる
各列にいくつ値が入っているかを、COUNTA関数で数えます。表の下の空いたセルに次のように入力します。
=COUNTA(C2:C213)
=COUNTA(D2:D213)
=COUNTA(E2:E213)今回は、C列が212、D列が17、E列が149でした（1行目は見出しなので、E列は2行目から150行目までの149件）。件数がバラバラな時点で、「どれか1列だけで完結していない」ことがわかります。
確認3 中身が本当に同じかを数式で比べる
コピーで作られた列は、見た目が同じでも途中から値が変わっていることがあります。目で見比べるのではなく、数式で「違う行の数」を数えます。
=SUMPRODUCT((E2:E150<>C2:C150)*1)この数式は、C列とE列の値が一致しない行の数を返します。今回は0でした。つまりE列は、C列を丸写ししたまま一度も更新されていない列だと確定できます。消しても情報は失われません。
一方、D列は「値が入っている17行はC列と違う値」で、それ以外は空白という構造でした。こちらは消してはいけない列です。正しい金額はD列にしかないからです。
3列を1列にまとめる手順｜壊さない順番で進める
見極めが終わったら、いよいよ整理です。ポイントは「数式の参照先を付け替える」のではなく、「正しい値を、今参照されている列に集める」こと。こうすれば既存の数式をいじらずに済み、作業ミスが起きにくくなります。
手順1 ファイルごと別名で保存する
作業前に、ファイルをまるごと別名で保存します。「20260930_交通費精算_整理前.xlsx」のように日付を入れておくと、あとで探しやすくなります。バックアップを列ではなくファイルで取るのは、今回の事件の教訓そのものです。
手順2 作業用の列で「正しい金額」を作る
表の右端の空いている列（ここではF列）に、次の数式を入れて最終行までコピーします。
=IF(D2="",C2,D2)D列に修正後の金額が入っていればその値を、空白ならC列の元の金額を使う、という意味です。これで、全212行ぶんの「正しい金額」が1列にそろいます。
念のため、差額を確認しておきます。
=SUM(F2:F213)-SUM(C2:C213)今回はここが18,640になり、経理システムとの差額と一致しました。数字が合わない場合は、D列に数値ではなく文字列が混ざっていないかなどを先に確認します。
手順3 F列の値をC列に「値として」貼り付ける
F列をコピーして、C列の2行目を選び、「形式を選択して貼り付け」→「値」で貼り付けます。普通に貼り付けると数式ごと移ってしまい、D列を消したときに壊れるので、必ず値貼り付けにしてください。
これで、もともとSUMが参照していたC列に正しい金額が入りました。合計欄の数字が18,640円増えていれば成功です。
手順4 不要になった列は、すぐ消さずに名前を変える
D列、E列、F列は、この時点ではまだ削除しません。列名の末尾に「_old」を付けて、「金額（修正後）_old」「確認用金額_old」のように変えておきます。列を非表示にするのではなく、あえて見える状態で名前を変えるのがコツです。非表示にすると、存在自体が忘れられて、また化石になるからです。
手順5 1回分の締め作業を回してから削除する
次の締め作業を一度回して、どこにも #REF! が出ないこと、合計が経理システムと一致することを確認してから、「_old」の列を削除します。今回は翌週の中間締めで問題がなかったので、その時点で3列を削除しました。
削除した後に、最初の確認2で使った件数チェックをもう一度走らせて、C列が212件のままであることも確かめておくと安心です。
二度と増やさないための仕組み
整理が終わっても、仕組みを変えなければ重複列はまた生まれます。今回の職場で実際に取り入れたルールは次の3つです。
修正の記録は「列」ではなく「別シート」に残す
「修正前の金額を残したい」という気持ちは大事にしつつ、残す場所を変えました。表とは別に「修正履歴」シートを作り、「修正日・行番号（または申請番号）・修正前・修正後・修正した人」の5項目を1行ずつ記録します。本体の表は常に最新の値1列だけ。履歴が見たいときは履歴シートを見る、という役割分担です。
こうすると、本体の表の列数が増えないうえ、「いつ・誰が・いくらからいくらに直したか」が以前より正確に残るようになりました。
列を足す前に、見出しを検索する
新しい列を追加したくなったら、まず見出し行を「Ctrl+F」で検索して、同じ意味の列がないかを確かめます。「金額」で検索すれば、「金額（修正後）」も「確認用金額」もすべてヒットします。数秒の確認ですが、これだけで重複列の大半は防げます。
見出しに「この列の使い方」を書いておく
見出しセルを右クリックして「新しいメモ」を選ぶと、そのセルに補足説明を付けられます。「この列が正。修正時は上書きし、修正履歴シートに記録」と書いておけば、次に表を触る人が迷わなくなります。引き継ぎ資料を別に作るより、表そのものに説明が付いている方が確実に読まれます。
よくある質問
Q. 非表示にしている列も確認した方がいいですか？
はい、むしろ非表示列こそ要注意です。重複列は「邪魔だから」という理由で非表示にされやすく、存在が忘れられがちです。列番号のアルファベットが飛んでいる箇所（CのあとがFになっているなど）があれば、そこに非表示列があります。列全体を選択して右クリック→「再表示」で、一度すべて表示させてから確認しましょう。
Q. 列を消したら別シートの数式が #REF! になりました。
削除した列を参照していた数式が残っていたケースです。すぐに「元に戻す（Ctrl+Z）」で戻せれば問題ありません。戻せない場合は、手順1で保存しておいた整理前のファイルを開き、#REF! になった数式が元々どの列を見ていたかを確認して、残した列を参照するように書き直します。削除前に「参照先のトレース」を確認し、「_old」期間を設けるのは、この事故を防ぐためです。
Q. 修正後の列の方に全件そろっている場合はどうすればいいですか？
その場合はもっと簡単で、修正後の列が「本物」です。集計の数式の参照先をその列に付け替えるか、今回と同じように、修正後の列の値を元の列に値貼り付けしてから、不要な列を「_old」にして様子を見ます。どちらの場合も、「確認2」と「確認3」で件数と中身を数式で確かめてから進めてください。
あわせて確認したい、エラーが出ないのに合わない原因
今回の重複列のように、エラーは一切出ないのに合計だけがずれるトラブルは、表の作り方に原因があることがほとんどです。同じ系統の原因をまとめて確認しておくと、次に数字が合わなかったときの調査がぐっと早くなります。
1本の列の中で、途中から別の意味のデータが入っていたケースは「備考」列が途中から別物になっていた表の記事で調査しています。重複列とは逆方向の「1列に2つの意味」問題です。
同じ列の中で「千円」と「円」が混ざっていたケースは、単位がバラバラな列の見つけ方と直し方で扱っています。
表の途中に小計行を挟んでいて合計が2倍になったケースは、経費の合計がちょうど2倍に…表に「小計」を挟むと起きる二重計算の正体をご覧ください。
研究員メモ
今回の事件で、同僚は最初「私のSUMの使い方が悪いんでしょうか」と言っていました。でも、数式には何の問題もありませんでした。問題だったのは、正しいデータが2つの列に分かれて置かれていたことです。
Excelは、人間が決めた場所を正確に計算してくれます。その代わり、「どこが正しい場所か」までは決めてくれません。だからこそ、正しいデータは1か所だけに置く。これだけ守れば、今回のような「静かなズレ」はほぼ起きなくなります。
整理作業そのものは、確認から削除まで合わせて40分ほどでした。3週間気づかれなかったズレが、40分の作業で解消できたわけです。似た名前の列が並んでいる表があったら、まずは確認2のCOUNTA3行だけでも試してみてください。列の埋まり具合を数字で見るだけで、どの列が怪しいかはかなり見えてきます。
重複列のほかにも、引き継いだ表には気づきにくい問題がいくつも紛れていることがあります。受け取った表を集計の前にひととおり点検する手順は、もう一歩踏み込みたい方向けにnoteの「表の健康診断」12項目にまとめています。
今日の研究まとめ
こんな表は要注意
- 「金額」「金額（修正後）」「確認用金額」のように、似た名前の列が並んでいる
- 修正した行だけ、別の列に新しい値を書き込んでいる
- どの列を数式で参照しているか、すぐに説明できない
- 列幅が極端に狭い列や、非表示の列がある
片付けの手順
- 「参照元のトレース」「参照先のトレース」で、使われている列を確認する
- COUNTAで各列の件数を数え、SUMPRODUCTで中身の違いを数える
=IF(修正後="",元の列,修正後)で正しい値を1列にそろえ、値貼り付けで元の列に集める- 不要な列はすぐ消さず「_old」を付け、1回分の締めを回してから削除する
再発防止
- 修正履歴は列ではなく別シートに記録する
- 列を足す前に、見出しをCtrl+Fで検索する
- 見出しセルにメモで「この列が正」と書いておく
コメント