こんにちは。Excel事務研究員です。
先週、経理部の後輩から相談を受けました。
「先輩、この売上表のSUM関数、なんでか合計が合わないんです」
数式を見せてもらっても、範囲の指定に間違いはありません。セル参照もズレていない。それなのに、電卓で足し算した数字と、SUM関数が返す数字が一致しないというのです。
原因を一緒に探っていくと、答えは意外なところにありました。
同じ「売上」という列の中に、数値として入力されたセルと、文字列として入力されたセルが混ざっていたのです。見た目はどちらも「50000」のような数字ですが、Excelの内部ではまったく別の種類のデータとして扱われていました。
このシリーズでは、Excel初心者がやりがちなミス100を一つずつ研究しています。今回の研究テーマはこちらです。
列ごとにデータ形式が違う
同じ列の中に、数値・文字列・日付が混在してしまっている状態のことです。
パッと見ただけでは気づきにくいのに、集計・並び替え・検索関数のすべてに影響を及ぼします。今回は、この現象がなぜ起きるのか、どう見分けるのか、そしてどう直せばよいのかを、順番に研究していきます。
まずは症状をチェック
本題に入る前に、次のような症状に心当たりがないか確認してみてください。
- SUM関数の合計が、手計算した数字よりなぜか少ない
- 数字の順に並び替えたはずなのに、順番がバラバラになる
- VLOOKUPで「#N/A」が出るのに、探しているデータは表の中に確かに存在している
- 日付の引き算や「〇日後の日付」を求める計算がエラーになる、あるいは一部の行だけ動かない
このうちひとつでも当てはまるなら、列の中でデータ形式が混在している可能性を疑ってみてください。
なぜデータ形式が混ざってしまうのか
現場でこの状態がよく発生する場面は、だいたい4つに絞られます。
ひとつ目は、複数人で入力している表です。
入力ルールを決めていないと、ある人は「50000」と入れ、別の人は「50,000円」と入れます。どちらも本人としては「正しく」入力しているつもりなので、誰も違和感に気づかないまま表が育っていきます。
ふたつ目は、前任者から引き継いだファイルです。
前任者の入力スタイルがそのまま残っていることが多く、「なぜこの形式で入力されているのか」を確認する手段がないまま、そのファイルを使い続けることになります。
三つ目は、CSVや他システムからデータを取り込んだときです。
システムの仕様によっては、本来は数値のはずのデータが、文字列として書き出されることがあります。取り込んだ時点で、すでに形式が混在した状態になっているケースです。
四つ目は、過去のテンプレートを使い回すときです。
以前のテンプレートには単位付きで入力する欄があったのに、途中から単位を付けない運用に変えた、といったケースです。過去分と新しい分が同じ列に同居し、気づかないうちに形式が二重構造になります。
数値か文字列かを見分ける3つの方法
原因が分かったところで、次は「今このセルはどちらの形式なのか」を見分ける方法を押さえておきましょう。
方法① 寄せの向きを見る
Excelはセルに入力された値を、自動的に「数値」「文字列」「日付」などに分類して処理します。もっとも手軽な見分け方は、セルの中身が右揃えになっていれば数値、左揃えになっていれば文字列というルールです。「50000」と入力すると自動で右揃えになり、「50,000円」のように文字が混じると左揃えになります。
方法② セル左上の緑の三角マークを見る
数値のはずのセルが文字列として認識されていると、セルの左上に小さな緑色の三角マークが表示されることがあります。これは「このセルの内容は数値に見えますが、実際は文字列として保存されています」という、Excelからの控えめな警告サインです。見落としがちですが、混在チェックの手がかりになります。
方法③ ISNUMBER関数で確認する
大量のデータを一つずつ目視するのは現実的ではありません。そこで役立つのが判定用の関数です。空いている列に次の式を入れると、そのセルが数値かどうかをTRUE／FALSEで返してくれます。
=ISNUMBER(A2)TRUEが返ってくれば数値、FALSEが返ってくれば文字列（または空白）と判断できます。これをオートフィルで列全体にコピーすれば、どの行に文字列が紛れ込んでいるかが一目で分かります。
実務でよくある3つの混在パターン
現場で実際によく見かける混在パターンを、3つに整理します。
パターン① 数値に単位や記号を入れてしまう
同じ売上列なのに、これだけ表記がバラバラなことがあります。「50,000円」も「¥45,000」も、人間にとっては同じ意味ですが、Excelにとっては「文字列」「文字列」「数値」というまったくの別物です。
パターン② 全角数字と半角数字が混在する
「50000」（半角）と「５００００」（全角）が同じ列に混ざるパターンです。全角数字はExcelに文字列として認識されるため、見た目は数字でも計算には使えません。
全角・半角の混在そのものについては、数値の形式が統一されていないときの見分け方という記事でさらに詳しく研究していますので、あわせて確認しておくと理解が深まります。
パターン③ 日付の形式がバラバラ
どれも「2024年4月1日」を表しているつもりでも、Excelが日付として認識できるのは特定の形式だけです。認識できない形式は文字列として扱われ、日付を使った計算ができなくなります。
データ形式の混在が引き起こす4つのトラブル
トラブル① VLOOKUPが「#N/A」になる
VLOOKUP関数を使っているのに「#N/A」エラーが出て、データが見つからない。でも表を見ると、確かにそのデータは存在している。
このトラブルの原因のひとつが、検索値と表側のデータで形式が一致していないことです。検索値が数値の「101」でも、表の中の「101」が文字列として入力されていれば、VLOOKUPはヒットしません。数値の「101」と文字列の「101」は、Excelにとっては別物として扱われるからです。逆のパターン（検索値が文字列で、表側が数値）でも同じことが起こります。
なお、VLOOKUPの#N/Aエラーには、今回のような形式の不一致以外にも原因が複数あります。VLOOKUPで#N/Aが消えない本当の理由では、スペースの混入や引数の設定ミスなど、実務で多いパターンをまとめて解説していますので、合わせて確認すると原因の切り分けが早くなります。
トラブル② SUM・SUMIF関数が正しく動かない
最も見かける頻度が高いのが、この集計関数の誤作動です。SUM関数は数値のみを合計対象とするため、文字列として認識されているセルは、集計から静かに除外されます。
例えば、次のような売上列があるとします。
この列にSUM関数をかけると、計算されるのは数値の「50000」と「60000」だけです。文字列の「45000円」「¥30000」はゼロ扱いとなり、合計は「110,000」になります。本来の合計は「185,000」のはずですが、エラーは一切出ず、静かに「75,000円分」が抜け落ちています。
この「エラーが出ないのに合計が合わない」という状況こそが、一番発見が遅れやすく、実務でも深刻なミスにつながりやすいパターンです。SUMIF関数でも考え方は同じで、条件に一致するはずの行が文字列になっていると、集計対象から外れてしまいます。
トラブル③ 並び替えの結果がおかしくなる
数値と文字列が混在した列を並び替えると、意図しない結果になります。
数値の「2」「10」「30」を昇順に並べると「2・10・30」という順番になります。しかし文字列の「2」「10」「30」を昇順に並べると、辞書順（文字コード順）で「10・2・30」という並びになります。「1」で始まる「10」が「2」より前に来るのが、辞書順のルールだからです。
数値と文字列が混在している場合、列の中で一部は数値順、一部は辞書順に並び替えられるため、全体として「なんとなくバラバラ」という結果になります。「数字の順に並べたつもりが、なぜかそろわない」というトラブルの多くが、ここに原因があります。
トラブル④ 日付の計算ができない
日付の計算は、Excelが日付として認識しているセル同士でなければ正しく動きません。
「2024/4/1」から「2024/3/1」を引けば「31」という日数が返ってきます。しかし「2024年4月1日」という文字列のセルからは、日付計算ができずエラーになります。
実務では「〇日後の日付を出したい」「期間の日数を計算したい」という場面が頻繁にあります。日付列の形式がバラバラだと、この計算がセルによって動いたり動かなかったりして、原因の特定に余計な時間がかかってしまいます。
直し方・対策
対策① 入力ルールを先に決めて共有する
もっとも効果が高い対策は、表を作る前にルールを決めておくことです。
- 数値列：数値のみを入力し、単位（円・個）はセルの「表示形式」で表示する
- 日付列：「2024/4/1」のような形式に統一する
- 文字列列：全角・半角の使い方を統一する
このルールを事前に共有しておくだけで、後からの修正作業がほとんど発生しなくなります。
対策② データの入力規則機能を使う
Excelには「データの入力規則」という機能があります。列ごとに「整数のみ入力可能」「日付形式のみ入力可能」といった制限をかけることができます。
設定手順は次の通りです。
制限をかけたい列を選択 → 「データ」タブ → 「データの入力規則」
これを設定しておくと、ルール外の形式で入力しようとしたときにエラーメッセージが表示されます。複数人で使う表では特に効果を発揮します。
対策③ すでに混在してしまったデータを修正する
VALUE関数で変換する
文字列になっている数値を、数値に変換します。
=VALUE(A2)「45000円」のように単位まで含む文字列には直接使えませんが、「45000」という純粋な文字列には有効です。
区切り位置機能を使う
列を選択し、「データ」タブ→「区切り位置」を開いて、そのまま「完了」を押すだけで、文字列として認識されていた数値が数値に変換されることがあります。
貼り付け形式で変換する
空いているセルに「1」と入力してコピーし、文字列数値のセルを選択したうえで「形式を選択して貼り付け」→「乗算」を選ぶと、文字列が数値に変換されます。
COUNTIF・SUMPRODUCTで混在チェックをする
修正の前に「そもそもどれだけ混在しているか」を数で把握しておくと、作業の見通しが立てやすくなります。次の式は、対象範囲の中で文字列になっているセルの数を数える一例です。
=SUMPRODUCT(--ISTEXT(A2:A100))この式で件数が把握できれば、「全部で何件直せばよいか」が分かり、修正作業の見積もりが立てやすくなります。
研究員メモ
このミスで印象に残っているのは、繁忙期にパート社員3人で同じ在庫表への入力をお願いしたときのことです。
一人目は数字だけをそのまま入力し、二人目は個数の後ろに「個」を付けて入力し、三人目は全角数字で入力していました。誰も間違ったことをしているつもりはなく、それぞれが「読みやすいように」と工夫してくれた結果でした。
月末にSUMIFで在庫の集計をかけたところ、明らかに数字が合わず、原因を探すのに丸一日かかりました。結局、入力前に「数字だけを半角で入力してください」という一行のルールを共有するだけで、翌月からはぴったり合うようになりました。
複数人で入力する表を作るときは、作業を始める前の数分間で「この列には何を、どんな形式で入れるか」を決めておく。この一手間が、後の自分やチームを助けてくれます。
今日の研究まとめ
集計がうまくいかない理由㉔
列ごとにデータ形式が違う
チェックポイント
- 売上列に「円」「¥」「,」などの記号が混ざっている
- セルの値が左揃えになっている（文字列のサイン）
- セルの左上に緑の三角マークが出ている
- SUM関数の合計が、なんとなく少ない気がする
- 数字の順に並び替えできない
- VLOOKUPで#N/Aエラーが出るのに、データは存在する
- 日付の計算が一部の行だけエラーになる
解決方法
✔ 数値列には数値のみ入力し、単位は表示形式で表す
✔ 日付は「2024/4/1」のような形式に統一する
✔ 全角・半角を列ごとに統一する
✔ 入力前にルールを決めて共有する
✔ データの入力規則機能で入力形式を制限する
✔ ISNUMBER関数やSUMPRODUCT関数で混在状況を先に把握する
✔ 既存データはVALUE関数・区切り位置・貼り付け乗算で修正する
次回の研究予告
Excel初心者がやりがちなミス研究、次回はこちらです。
表の作り方のミス㉕「表の途中に小計を入れる」
「集計しやすいように」と入れた小計行が、かえって集計を複雑にしてしまう現象です。SUM関数の二重計算や、フィルターとの相性の悪さについて研究していきます。
次回も一緒に研究していきましょう。
コメント