こんにちは。
Excel事務研究員です。
SUM関数を使ったのに、合計がどう見ても合わない。
エラーメッセージは出ていない。
数式も正しいはずなのに、答えが少ない。
こんな「静かなエラー」に困った経験はありませんか？
実はこのトラブル、関数の問題ではなく「列の中にデータ形式が混在している」ことが原因になっているケースがとても多いです。
このシリーズでは、Excel初心者がやりがちなミス100を一つずつ研究しています。
今回の研究テーマはこちらです。
列ごとにデータ形式が違う
同じ列なのに、数値・文字列・日付が混ざっている状態です。
見た目ではなかなか気づきにくいのに、集計・並び替え・検索関数のすべてに影響します。
今回は、その仕組みと解決策を丁寧に研究していきましょう。
なぜデータ形式が混在してしまうのか
データ形式の混在が起きやすいのは、主に次の3つの状況です。
一つ目は、複数人で入力している表です。
入力ルールを決めていないと、人によって「50000」と入れる人もいれば「50,000円」と入れる人もいます。どちらも正しいつもりで入力しているため、誰も気づきません。
二つ目は、引き継いだファイルです。
前任者の入力スタイルがそのまま残っていることが多く、「なぜこの形式で入力されているのか」を確認する手段がないまま使い続けることになります。
三つ目は、CSVや他のシステムからデータを取り込んだときです。
システムによっては、数値が文字列として出力されることがあります。
取り込んだ時点ですでに形式が混在している状態になっています。
Excelが形式をどう判断するか
Excelはセルに入力されたデータを、自動的に「数値」「文字列」「日付」などに分類して処理します。
初心者でも目で見分けられる簡単な方法があります。
セルの中の値が右揃えになっていれば数値、左揃えになっていれば文字列です。
例えば、「50000」と入力すると、Excelは自動で数値と判断して右揃えで表示します。
「50,000円」と入力すると、文字が含まれているため文字列と判断され、左揃えで表示されます。
この左揃え・右揃えの違いを見るだけで、そのセルが数値として使えるかどうかが分かります。
よくある混在パターン
実務でよく見かける混在パターンを3つ整理します。
パターン① 数値に単位や記号を入れてしまう
| 売上 |
|---|
| 50,000円 |
| ¥45,000 |
| 60000 |
| 三万円 |
同じ売上列なのに、これだけの表記が混在していることがあります。
「50,000円」も「¥45,000」も「60000」も、人間にとっては同じ意味ですが、Excelにとっては「文字列」「文字列」「数値」という別物です。
パターン② 全角数字と半角数字が混在する
「50000」（半角）と「５００００」（全角）が混在するパターンです。
全角数字はExcelに文字列として認識されます。
見た目は数字でも、計算には使えません。
パターン③ 日付の形式がバラバラ
| 日付 |
|---|
| 2024/4/1 |
| 2024年4月1日 |
| 4月1日 |
| 2024-04-01 |
これらはどれも「2024年4月1日」を表しているつもりですが、Excelが日付として認識できるのは特定の形式だけです。
認識できない形式は文字列として扱われ、日付計算ができなくなります。
よく起きるトラブル① SUM・SUMIF関数が正しく動かない
最も多く見かけるトラブルが、集計関数の誤作動です。
SUM関数は数値のみを合計します。
文字列として認識されているセルは、集計対象から静かに除外されます。
例えば、次のような売上列があるとします。
| 売上 |
|---|
| 50000（数値） |
| 45000円（文字列） |
| 60000（数値） |
| ¥30000（文字列） |
この列に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」より前に来るのが辞書順のルールです。
数値と文字列が混在している場合、列の中で一部は数値順、一部は辞書順で並び替えられるため、全体として「なんかバラバラ」という結果になります。
「数字の順に並べたつもりが、なぜかバラバラになった」というトラブルの原因の多くがここにあります。
よく起きるトラブル③ VLOOKUPがN/Aエラーになる
VLOOKUP関数を使っているのに「#N/A」エラーが出て、データが見つからない。
でも表を見ると、確かにそのデータは存在している。
このトラブルの原因の一つが、検索値と表のデータで形式が一致していないことです。
検索値が数値の「101」でも、表の中の「101」が文字列として入力されていると、VLOOKUPはヒットしません。
数値の「101」と文字列の「101」は、Excelには別物として扱われるからです。
逆のパターン（検索値が文字列で表が数値）でも同じことが起きます。
「データはあるのに見つからない」という状況に陥ったとき、形式の不一致を真っ先に疑うようにすると、原因にたどり着くのが早くなります。
よく起きるトラブル④ 日付の計算ができない
日付の計算は、Excelが日付として認識しているセル同士でしか正しく動きません。
「2024/4/1」から「2024/3/1」を引けば「31」という日数が返ってきます。
しかし「2024年4月1日」という文字列のセルからは、日付計算ができず、エラーになります。
実務では「〇日後の日付を出したい」「期間を計算したい」という場面が多くあります。
日付列の形式がバラバラだと、この計算がセルによって動いたり動かなかったりして、原因の特定に時間がかかります。
解決方法
対策① 入力ルールを先に決めて共有する
最も効果的な対策は、表を作る前にルールを決めておくことです。
- 数値列：数値のみを入力する。単位（円・個）はセルの「書式」で表示し、セルの中には入れない
- 日付列：「2024/4/1」形式で統一する
- 文字列列：全角・半角を統一する
このルールを共有しておくだけで、後からの修正作業がほぼ発生しなくなります。
対策② データの入力規則機能を使う
Excelには「データの入力規則」という機能があります。
列ごとに「整数のみ入力可能」「日付形式のみ入力可能」といった制限をかけることができます。
設定方法は次の通りです。
制限をかけたい列を選択 → メニューの「データ」タブ → 「データの入力規則」
これを設定しておくと、ルール外の形式でデータを入力しようとしたときにエラーメッセージが表示されます。
複数人で使う表では特に有効です。
対策③ 既存データを修正する方法
すでに形式が混在してしまったデータを修正する方法もあります。
VALUE関数を使う
文字列になっている数値を数値に変換します。
=VALUE(A2)「45000円」のような文字列には直接使えませんが、「45000」という文字列には有効です。
区切り位置機能を使う
列を選択して「データ」タブ → 「区切り位置」を開き、そのまま「完了」を押すだけで、文字列として認識されていた数値が数値に変換されることがあります。
貼り付け形式で変換する
空白セルに「1」と入力してコピーし、文字列数値のセルを選択して「形式を選択して貼り付け」→「乗算」を選ぶと、文字列が数値に変換されます。
研究員メモ
私がこのミスで一番苦労したのは、引き継ぎ直後の月次報告でした。
前任者が1年間入力した売上データのSUM関数の合計が、毎月の報告書の数字と微妙にずれていることに気づいたのは、引き継いでから3か月後のことでした。
調べてみると、前任者が途中から入力スタイルを変えていて、上半期は「50000」、下半期は「50,000円」という形式で入力していたことが分かりました。
その修正に、半日かかりました。
最初にルールを決めておくだけで、こんなことは起きなかったのです。
表を新しく作るとき、複数人で使う表を設計するとき、ぜひ「この列には何を入れるか」を先に決める習慣をつけてみてください。
後の自分が、きっと助かります。
今日の研究まとめ
集計がうまくいかない理由㉔
列ごとにデータ形式が違う
チェックポイント
- 売上列に「円」「¥」「,」などが混ざっている
- セルの値が左揃えになっている（文字列のサイン）
- SUM関数の合計がなんとなく少ない気がする
- 数字の順に並び替えできない
- VLOOKUPで#N/Aエラーが出るのにデータは存在する
- 日付の計算がエラーになる
解決方法
✔ 数値列には数値のみ入力し、単位は書式で表示する
✔ 日付は「2024/4/1」形式で統一する
✔ 全角・半角を列ごとに統一する
✔ 入力前にルールを決めて共有する
✔ データの入力規則機能で入力形式を制限する
✔ 既存データはVALUE関数・区切り位置・貼り付け乗算で修正する
次回の研究予告
Excel初心者がやりがちなミス研究。
次回はこちらです。
表の作り方のミス㉕「表の途中に小計を入れる」
「集計しやすいように」と入れた小計行が、逆に集計を複雑にしてしまいます。
SUM関数の二重計算や、フィルターとの相性の悪さについて研究していきます。
次回も一緒に研究していきましょう。
コメント