こんにちは。実務Excel研究員です。
今回は、私がこれまでに一番時間を溶かした「エラーが出ないミス」の話から始めさせてください。
事件のはじまり：なぜか増えている交通費
7月の頭、第1四半期の支店別経費をまとめていたときのことです。8つの支店から毎月上がってくる経費を1枚のシートに貼り付けていく表で、4月から6月までの3か月分、行数にしておよそ480行ありました。
合計を出してみると、交通費が前年同期の138%。
出張が増えたという話は聞いていません。むしろ「今期はオンライン会議を増やそう」と言っていたくらいです。数式を見直しても、SUM関数の範囲はきちんと480行を指していて、おかしなところは見当たりません。エラーの表示もゼロ。
それでも数字だけが、どう考えても多すぎました。
原因にたどり着くまでにかかった時間は、2時間40分です。
答えは拍子抜けするほど単純でした。この表は経理部のルールで「千円単位」で入力することになっていたのですが、4月に着任した名古屋支店の新しい担当者が、それを知らずに「円単位」で入力していたのです。交通費3,200円を「3.2」ではなく「3,200」と入れていた。表の上では3,200千円、つまり320万円の交通費として扱われていたことになります。
該当したのは43行。数字そのものは1つも間違っていません。間違っていたのは、その数字が「何の単位なのか」という前提だけでした。
単位の混在はExcelの目には見えない
同じ列の中に「円」「千円」「万円」のように違う単位の数字が混ざっている状態を、この記事では単位混在と呼びます。
Excelにとって、セルに入っている「3,200」はただの3200という数です。それが円なのか千円なのか、あるいは個数なのか分なのか、Excelは知りませんし、知ろうともしません。電卓に「3200」と打ち込んでも、電卓が「これは千円単位ですか？」と聞き返してこないのと同じです。
だから単位混在は、エラーという形では絶対に表に出てきません。SUM関数も、SUMIFS関数も、ピボットテーブルも、何事もなかったかのように計算を終えます。出てくる合計が「それらしい数字」であればあるほど、誰も疑わずにそのまま資料に載ってしまいます。
単位混在が生まれる経緯は、私の経験ではだいたい次の3つのどれかです。
1つ目は、入力する人によって単位の「当たり前」が違うケース。名古屋支店の件はまさにこれで、前任者から引き継ぎを受けていない人にとって、金額を円で書くのは自然なことでした。
2つ目は、別のシステムから出力したデータを貼り付けたケース。基幹システムが千円単位でCSVを出す設計なのに、それを円単位の表に貼ってしまう。貼った直後は見た目に違和感がないため、気づくのはたいてい集計のときです。
3つ目は、途中でルールを変えたのに過去分を直さなかったケース。「来月から円単位に統一します」と決めても、それ以前の行が千円単位のまま残っていれば、ある行を境に上下で単位が変わる表ができあがります。月ごとの集計では目立たず、年間でまとめた瞬間に数字が崩れます。
ちなみに、セルに「100円」「3個」のように数字と単位をまとめて打ち込んでしまうミスは、また別の症状（合計が0になる、など）として現れます。こちらは数字に単位を付けて入力した表の直し方で詳しく扱っているので、「そもそも合計が出ない」という方はそちらから読んでみてください。今回の記事は、合計は出るけれど数字がおかしい、というタイプの話です。
捜査その1：まずは「怪しい列」を30秒で絞り込む
2時間40分もかかった反省から、今の私は同じような違和感を覚えたら、まず次の順番で確認するようにしています。いきなり1行ずつ目で追うのは、一番時間のかかるやり方です。
最初にやるのは、疑わしい列の最大値と最小値を見ることです。空いているセルに =MAX(C2:C481) と =MIN(C2:C481) を入れるだけで構いません。交通費の列で最大値が3,200、最小値が0.8だったら、その差は4,000倍です。同じ費目で4,000倍の開きがあるのは、実務ではまずありえません。この時点で「単位が混ざっているかもしれない」と当たりがつきます。
次に、その列で並び替えをしてみます。昇順に並べると、単位ごとに数字の「群れ」ができるのが分かります。小数点のついた小さな数字の群れと、4桁の大きな数字の群れがくっきり分かれていたら、ほぼ間違いなく単位混在です。
症状別に、どこを疑えばいいかを表にまとめておきます。
| 気づいた症状 | 疑うべきポイント | 最初に試す確認方法 |
|---|---|---|
| 合計が前年や予算と比べて極端に多い・少ない | 一部の行だけ大きい単位（または小さい単位）で入っている | MAXとMINの比率を見る |
| 並び替えると重さや金額の順番が実感と合わない | g と kg、円と千円などが混在 | 昇順で並べて数字の「群れ」を見る |
| グラフの棒が1本だけ極端に長い・短い | その項目だけ単位が違う | 元データのその項目だけフィルターで抽出 |
| ピボットテーブルの支店別合計に桁違いの支店がある | 支店（入力者）ごとに単位ルールが違う | 支店別の平均値を並べて比較 |
最後の行の「支店別の平均値を並べる」は、私のケースで一番効いた方法です。ピボットテーブルで行に支店名、値に交通費の「平均」を置いたところ、名古屋支店だけ4月以降の平均が他支店の約1,000倍になっていました。ちょうど千円と円の差です。ここまで分かれば、あとは名古屋支店の行を見るだけで済みます。
捜査その2：どの行が犯人かを数式で洗い出す
怪しい列が分かったら、次は具体的にどの行が違う単位なのかを特定します。480行程度なら目視でもなんとかなりますが、数千行になると現実的ではありません。数式に手伝ってもらいましょう。
方法A：中央値との比率を出す
隣の空いている列（ここではE列）に、次の数式を入れて下までコピーします。
=IFERROR(C2/MEDIAN($C$2:$C$481),"")
これは「その行の値が、列全体の真ん中の値の何倍か」を計算する式です。平均ではなく中央値（MEDIAN）を使うのは、桁違いの数字が混ざっていると平均そのものが引っ張られて、基準として役に立たなくなるからです。
結果を見て、100を超えている行や0.01を下回っている行があれば、それが単位違いの候補です。千円単位の表に円で入った行なら、だいたい数百倍から千倍前後の数字が並びます。
方法B：整数部分の桁数を数える
もう少し直感的に見たいときは、桁数を数える方法もあります。
=LEN(INT(C2))
INT関数で小数点以下を切り捨て、LEN関数で文字数を数えています。千円単位の交通費ならせいぜい1〜2桁に収まるはずなのに、4桁や5桁の行があれば目立ちます。フィルターで桁数の列を絞り込めば、該当行だけを一覧にできます。
方法C：条件付き書式で色を付ける
数式の列を増やしたくない場合は、条件付き書式で怪しいセルに色を付けてしまうのが手軽です。
C2からC481を選択し、「ホーム」タブの「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。数式の欄に次のように入れて、塗りつぶしの色を設定します。
=OR(C2>MEDIAN($C$2:$C$481)*30,C2<MEDIAN($C$2:$C$481)/30)
中央値の30倍を超える、または30分の1を下回るセルに色が付きます。30という数字は目安なので、費目の性質に合わせて調整してください。交通費のようにばらつきが小さい費目なら10でもいいですし、設備費のように本当に金額の幅が広い費目なら100くらいにしておかないと、正しいデータまで色が付いてしまいます。
注意したいのは、どの方法も「異常に見える行」を拾っているだけだということです。本当に高額な出張があった行も同じように引っかかります。最終的には、入力した人に確認を取るのが確実です。私のケースでも数式が拾った候補は45行で、そのうち2行は本当に高額な新幹線代でした。残りの43行が、円で入力されていた行です。数式で当たりをつけて、人に確認する。この順番が一番早くて確実です。
修正：状況に合わせて3つの直し方から選ぶ
犯人の行が分かったら、いよいよ直します。直し方は、データをどこまで触ってよいかによって変わります。
直し方1：「形式を選択して貼り付け」の乗算で一気に換算する
一番手っ取り早いのがこの方法です。数式を書く必要がありません。
たとえば千円単位の表に円で入ってしまった行を千円に直したいなら、どこか空いているセルに「0.001」と入力し、そのセルをコピーします。次に、直したいセル（名古屋支店の該当行）を選択し、右クリックから「形式を選択して貼り付け」を開いて、「演算」の欄の「乗算」を選んでOKを押します。ショートカットなら Ctrl + Alt + V で同じ画面が開きます。
これで選択したセルがすべて1000分の1になります。逆に万円を円に直したいなら10000を、千円を円に直したいなら1000をコピーして同じ操作をします。
作業が終わったら、最初に「0.001」と入力したセルは消しておきましょう。そして大事なことですが、この操作は元の値を上書きします。実行前に必ずファイルを別名で保存しておいてください。私は名古屋支店の件でこれを忘れ、対象範囲を1行ずらして選んでしまい、正しかった大阪支店の1行まで1000分の1にしてしまいました。バックアップがあったので事なきを得ましたが、なかったらもう一度480行を見比べるところでした。
直し方2：単位の列を足して、換算用の列で計算する
元のデータを書き換えたくない場合や、今後も単位が混ざる可能性がある表の場合は、「単位」の列を別に作って、換算は数式に任せる方法が向いています。
まず、金額の隣に「単位」列（D列）を作り、各行に「円」「千円」「万円」のどれで入っているかを書きます。次に、シートの空いている場所（H2:I4など）に小さな換算表を作ります。
| 単位 | 円への倍率 |
|---|---|
| 円 | 1 |
| 千円 | 1000 |
| 万円 | 10000 |
そのうえで、E列に「円換算額」として次の数式を入れます。
=C2*XLOOKUP(D2,$H$2:$H$4,$I$2:$I$4)
XLOOKUP関数が使えないバージョンのExcelなら、VLOOKUP関数で同じことができます。
=C2*VLOOKUP(D2,$H$2:$I$4,2,FALSE)
集計はこのE列に対して行います。元の数字は入力されたまま残るので、「誰がどの単位で入れたか」という記録も消えません。後から確認が必要になったときに、元の状態をたどれるのがこの方法の強みです。
単位列を作る考え方は、同じ列に数値と文字列が混ざってしまう問題とも共通しています。「1つの列には1種類の情報だけ」という原則については、列ごとにデータ形式が違うと集計できない原因と直し方でも詳しく解説しています。
直し方3：元の表には一切触らず、集計式の中で換算する
「この表は他部署の管理だから勝手に触れない」ということも、実務ではよくあります。単位列だけは追加できる、という前提なら、集計式の中で単位ごとに合計して倍率をかけてしまう方法があります。
=SUMIFS(C2:C481,D2:D481,"円")+SUMIFS(C2:C481,D2:D481,"千円")*1000+SUMIFS(C2:C481,D2:D481,"万円")*10000
円・千円・万円ごとに合計してから、それぞれに倍率をかけて足しています。見た目は長いですが、やっていることは単純です。答えは円単位で出てきます。
3つの直し方を比べると、次のようになります。
| 直し方 | 元データ | 手間 | 向いている場面 |
|---|---|---|---|
| 1：乗算で貼り付け | 書き換える | 最小（数分） | 一度きりの修正、行が特定できている |
| 2：単位列＋換算列 | 残る | 中（列と換算表を追加） | 今後も入力が続く表、記録を残したい |
| 3：集計式で換算 | 残る | 中（式が長い） | 元の表を編集できない、集計側だけ直したい |
私の名古屋支店の件では、まず直し方1で43行を修正し、その後の再発防止として直し方2の「単位」列を表に追加しました。
金額以外の単位混在で気をつけたいこと
ここまで金額を例にしてきましたが、重さや時間でも同じことが起きます。ただ、それぞれ少しだけクセがあります。
重さ（g と kg）や容量（ml と L）は、考え方は金額と同じで、1000倍か1000分の1にすれば揃います。並び替えたときに「0.3kg」が「500g」より下に来てしまう、という形で見つかることが多いです。
厄介なのは時間です。時間の列には「90（分）」「1.5（時間）」「1:30（時刻形式）」という3種類が混ざることがあります。特に最後の「1:30」は、見た目は時間でも、Excelの中では「1日を1とした小数」、つまり0.0625という数字として保存されています。これを分に直すには1440（24時間×60分）をかけます。
- 「分」の数字をそのまま使う
- 「時間」の小数（1.5など）は60をかけて分にする
- 「1:30」のような時刻形式は1440をかけて分にする
残業時間の集計でこれが混ざると、給与計算にそのまま響きます。以前、ある部署の残業集計を手伝ったとき、12人分の月間残業のうち3人分だけが時刻形式で入っていて、その3人の残業が合計で0.3時間程度として計算されかけていたことがありました。締め日の前日に気づけたのは本当に運が良かったと思っています。
再発防止：「見た目」と「中身」を分けて考える
直したあとは、同じことが起きない仕組みを作ります。ここで私が一番大事だと思っているのは、「見た目の単位」と「中身の単位」を分けて考えることです。
列名に単位を書く
基本中の基本ですが、効果は大きいです。「交通費」ではなく「交通費（千円）」と書いておくだけで、入力する人が一瞬立ち止まれます。名古屋支店の件も、列名に単位が書いてあれば、おそらく起きていませんでした。表を見直したら、見出しはただの「交通費」だったのです。
中身は円のまま、表示だけ千円にする
これは意外と知られていないのですが、とても便利な方法です。
資料の見た目は千円単位にしたいけれど、入力はみんなが慣れている円単位で受け付けたい。そんなときは、中身を円のまま保存して、表示形式だけを千円単位にします。
対象のセルを選んで Ctrl + 1 で「セルの書式設定」を開き、「表示形式」タブの「ユーザー定義」を選びます。種類の欄に次のように入力します。
#,##0,
最後のカンマがポイントで、これがあると末尾の3桁を省略して表示してくれます。セルの中身が 3200 なら、画面には「3」と表示されます（端数は四捨五入されます）。中身は3200のままなので、計算はすべて円単位で正確に行われます。万円単位で見せたい場合は、カンマを増やす方法ではうまくいかないので、素直に別の列で10000で割るのがおすすめです。
入力する人は円で入れる、見る人は千円で見る。この形にしておけば、「千円で入れてください」というルールそのものが不要になります。ルールを覚えてもらうより、ルールがなくても間違えない形にするほうが、長い目で見ると確実です。
入力規則で「ありえない数字」をはじく
入力規則というとドロップダウンのリストを思い浮かべる方が多いと思いますが、金額の列に効くのは「数値の範囲」の制限です。
対象の列を選び、「データ」タブの「データの入力規則」を開きます。「入力値の種類」で「小数点数」を選び、「次の値の間」で最小値と最大値を設定します。千円単位の交通費なら、たとえば0から500くらいにしておけば、うっかり円で「3200」と入れた瞬間にエラーメッセージが出ます。
「エラーメッセージ」タブで、「この列は千円単位です。3,200円なら3.2と入力してください」のように、具体的な直し方を書いておくと親切です。私が今使っている表ではこの設定を入れてから、単位の入力ミスは3か月で1件もありません。
取り込みの手順書に「単位確認」の1行を加える
システムから出力したデータを貼り付ける運用がある場合は、手順書やチェックリストに「貼り付け前に、出力データの単位を確認する」という1行を加えておきます。たった1行ですが、担当者が替わったときに効いてきます。
よくある質問
Q. 単位混在かどうか、数式を使わずに一番早く確かめる方法は？
A. 列を昇順で並び替えて、上と下を見比べるのが一番早いです。数字の大きさが極端に違う「群れ」が2つ以上あれば、単位混在を疑ってください。並び替えが元の表を崩すのが心配なら、フィルターの並び替えを使ってから元に戻すか、シートをコピーしてから試しましょう。
Q. 千円単位の資料を作るとき、数字を1000で割って入力し直すべきですか？
A. おすすめしません。割り算した値を入力すると端数が出たり、別の資料で円単位が必要になったときにまた掛け直すことになったりします。上で紹介したユーザー定義の表示形式 #,##0, を使えば、中身は円のまま見た目だけ千円にできます。
Q. 他の人が作った表で、単位のルールがどこにも書かれていません。どう判断すればいいですか？
A. まず作った人に確認するのが一番です。それが難しい場合は、請求書や領収書など、元になった証憑と数行だけ突き合わせてみてください。3行ほど確認すれば、その表がどの単位で作られているかはたいてい分かります。
Q. 条件付き書式で色が付いたセルは、すべて単位ミスですか？
A. いいえ。本当に金額が大きい取引や、特殊な事情のある行も引っかかります。色は「確認すべき候補」の目印と考えて、最終判断は元資料や入力者への確認で行ってください。
研究員の事件簿メモ
名古屋支店の件で一番こたえたのは、2時間40分という時間そのものよりも、その間ずっと「どこかで数式を間違えたのではないか」と自分の作った式ばかり疑っていたことでした。
数式は1つも間違っていませんでした。間違っていたのは、表の中に書かれていないルールのほうでした。「この列は千円単位」という約束は、経理部の人たちの頭の中にしかなかったのです。
それ以来、集計が合わないときには「式」より先に「前提」を疑うようにしています。この列は何の単位で入っているのか。誰が入力しているのか。途中で入力者が替わっていないか。この3つを確認するだけで、解決までの時間はずいぶん短くなりました。
Excelは、渡された数字を忠実に計算してくれます。でも、その数字に込められた意味までは汲み取ってくれません。意味を伝える役目は、列名や表示形式や入力規則、つまり表を作る側に残されています。
事件のまとめ
単位がバラバラな列は、エラーを出さずに「それらしい間違った合計」を作ります。見つけ方と直し方をもう一度整理しておきます。
見つけるときは、MAXとMINの比率を見る、並び替えて数字の群れを確認する、支店や担当者ごとの平均を比べる、という順番で怪しい列を絞り、中央値との比率や条件付き書式で該当行を洗い出します。
直すときは、一度きりなら「形式を選択して貼り付け」の乗算、今後も続く表なら単位列と換算表、元の表を触れないなら集計式の中で換算、と状況で使い分けます。
防ぐときは、列名に単位を書き、中身と見た目を分けるユーザー定義の表示形式を使い、入力規則でありえない数字をはじきます。
あわせて読みたい関連記事
単位の混在に気づいた表は、ほかの「作り方のクセ」も抱えていることが多いです。次の記事もあわせて確認してみてください。
月ごとに列が右へ右へと増えていく表は、単位混在と同じく集計を難しくする原因になります。実際に154列あった表を整理した手順は右スクロールが終わらない表を縦に直した記事で紹介しています。
「金額」「金額（修正後）」のように同じ意味の列が増えてしまっている場合は、同じ項目の列が複数あると集計がズレる原因と直し方が参考になります。
システムからコピーしたデータで、単位は合っているのにVLOOKUPが一致しないという場合は、コピペで紛れ込む「見えない文字」の正体と消し方をどうぞ。
単位の混在が見つかる表では、空白行や文字列化した数字など、別の問題も一緒に見つかることが多いものです。集計の前に表全体を点検する12項目と、項目ごとの件数をまとめて数える診断シートの数式はnoteでまとめています。一歩踏み込んで点検を習慣にしたい方はこちらからどうぞ → 表の健康診断12項目と診断シート（note）
コメント