Excelで日付や数字の表示がおかしいとき｜####・シリアル値・指数表示・先頭のゼロの原因と直し方

先頭のゼロが消える：見かけのゼロと本物のゼロ

消える理由

「00125」は、数値としては125と同じです。Excelは数値に変換すると、意味のない先頭のゼロを捨てます。郵便番号の「0600001」や、電話番号の「09012345678」が崩れるのも、このためです。

二つの直し方の違い

直し方は二つありますが、結果の意味が違います。

見かけのゼロ（ユーザー定義の表示形式）

列を選んでCtrl+1を押し、「ユーザー定義」で 00000 と入れます。「125」が「00125」と見えるようになります。中身は125のままなので、計算はできますが、CSVに書き出したときや別のファイルへ貼り付けたときに、元の125に戻ることがあります。

本物のゼロ（文字列に変換）

=TEXT(A2,"00000") と入れて下へコピーし、結果を「値」として貼り付けます。中身が「00125」という文字列になります。別のシステムへ渡すコードなら、こちらが安全です。

照合で失敗しやすい点

VLOOKUPやXLOOKUPで顧客コードを照合すると、見た目は同じなのに一致しないことがあります。よくある原因は、片方が数値の125、もう片方が文字列の「00125」や「125」という型の違いです。

• どちらの列がどの型かをISNUMBERで確かめる
• 両方を文字列に統一してから照合する
• 桁数を =LEN(A2) で確かめる（数値の125は3、「00125」は5）

桁数の確認は、余分なスペースが入っている場合の発見にも役立ちます。

また、桁が失われたデータで先頭のゼロを補うときには、注意が必要です。番号の桁数がすべて同じだとわかっている場合だけ、ゼロを足して戻せます。桁数が決まっていない番号では、いくつゼロがあったのか判別できません。

数字なのに計算できない：文字列の数字を見つけて直す

文字列になる主な原因

SUMの結果が0になる、合計の値が小さい、並べ替えが「1, 10, 2」の順になる。このようなときは、数字が文字列として入っています。Webページからの貼り付け、会計や販売のシステムからの書き出し、文字列の形式のセルへの入力などで起こります。

セルの左上に緑の小さな三角が出ていれば、Excelが「文字列として保存された数値」と教えてくれている印です。セルを選ぶと出る注意のアイコンから、「数値に変換する」を選ぶと直せます。

まとめて変換する方法

範囲が広いときは、次の方法を使います。

区切り位置を使う：列を選び、「データ」タブの「区切り位置」を開いて、そのまま「完了」を押します。列を読み直す過程で数値に変換されます。

形式を選択して貼り付けを使う：

1. 空いたセルに1を入れてコピーします。
2. 変換したい範囲を選び、右クリックから「形式を選択して貼り付け」を選びます。
3. 「乗算」を選んでOKを押します。

1を掛ける計算をすると、文字列が数値に変わります。

関数を使う：=VALUE(A2) または =A2*1 と入れて、下へコピーします。

変換できないときの原因

上の方法で直らなければ、数字以外の文字が混じっています。代表的なものと対処は次のとおりです。

複数が重なっている場合は、関数を組み合わせます。

=VALUE(TRIM(SUBSTITUTE(ASC(A2),CHAR(160),"")))

式がそのまま文字で表示されるとき

=A2+B2 と書いたのに、計算結果ではなく式の文字がそのまま表示されるときは、セルの表示形式が「文字列」になっています。表示形式を「標準」に変えてから、セルをダブルクリックしてEnterを押すと計算されます。シート全体で式が見えているなら、「数式」タブの「数式の表示」がオンになっている可能性があります。

合計が1円合わない、時間の足し算がおかしいとき

表示の四捨五入と、値の端数

小数点以下を表示しない設定にしても、中身の端数は残っています。たとえば、1.4、1.4、1.4を足すと4.2ですが、整数で表示すると「1, 1, 1」の合計が「4」となり、一見合わないように見えます。

請求書のように、表示された数字が合っていなければならない場合は、行ごとに =ROUND(A2*B2,0) のように関数で実際に丸めてから合計します。端数を切り捨てるならROUNDDOWN、切り上げならROUNDUPです。

小数の計算で出る小さなずれ

コンピューターは内部で数を2進数で扱うため、0.1のような小数を正確に表せません。そのためExcelでも、ごくわずかな誤差が残ることがあります。たとえば =1*(0.5-0.4-0.1) は、0になるはずなのに、-2.78E-17のような非常に小さな値を返します。普段は見えなくても、=IF(A2=0,…) のように「ちょうど0か」を調べる式が、思ったとおりに動かない原因になります。比較の前に =ROUND(A2,2) のように丸めておくと防げます。

24時間を超える合計

勤務時間の合計が、30時間のはずなのに6:00と表示されたことはありませんか。「h:mm」の表示では、24時間ごとに繰り上がって消えてしまいます。表示形式を [h]:mm にすると、30:00と正しく表示されます。

時間を給与の計算に使うときは、シリアル値が「一日を1」としていることに注意します。8時間は0.333…という値なので、そのまま時給に掛けると金額が小さくなります。=A2*24*時給 のように24を掛けて、時間の単位に直してから計算します。

受け取ったデータの点検

Excelで作業したファイルを定期的に更新するなら、原本と加工後を分けて保存しておくと、問題が起きても戻れます。保存の考え方はバックアップと同期の違いを参考にしてください。

まとめ

Excelの表示の崩れは、セルの「値」と「見せ方」が別だと知っていれば、多くが落ち着いて解決できます。####は幅、日付の数字化は表示形式の問題で、設定で戻せます。文字列の数字は変換すれば計算できます。一方で、16桁目以降の番号と消えた先頭のゼロは、値そのものが変わっているので、元のデータからやり直すしかありません。

最初の一歩として、よく扱う表の番号の列を一つ選び、=ISNUMBER(A2) で数値か文字列かを確かめてみてください。そして、計算に使わない番号は文字列にする、と決めておくと、次に受け取るデータでの事故が減ります。