数値が思い通りに入力されないケース7つと対処法

2018.01.24基礎・入門文字列操作書式関数

セルに「数値」や「数式」を入力し確定した時に、表示が「数値」ではないものになる場合があります。
一度そうなったセルは、何度クリアして繰り返し入力しても、この不具合が再現してしまい、いつまでも思った入力にならないことが多いです。
これがなぜ起きるのかと、その対処法について解説します。

セルに数値を入力すると日付になってしまう

セルに「数値」を入力して確定した時に、「日付」のデータに変わってしまう時があります。
[セルの書式設定]で、「日付」の表示形式になっているケースです。

日付に変わる

 

[セルの書式設定]で設定しなくても、誤ってセルに「1-2」と入力すると、Excelは「1月2日」という日付と認識し、セルの表示形式を「日付」にします。
一度、セルに日付のデータを入力すると、そのセルには「日付」の表示形式が強制的に設定されています。

セルに入力したものが表示されない

セルに入力したときに、入力しているはずなのに何も表示されない場合はいくつかのケースがあります。
セルにユーザー定義の表示形式「;」や「;;」や「;;;」が設定されていると、入力したものが表示されません。

表示されない

 

また、セルの[背景色]と[フォントの色]が同じ色だと、見かけ上、表示されません。
入力する数値の前に、[Alt+Enterキー]による[セル内の改行]が挿入されている場合で、[行の高さ]が設定されていると、設定されているセル内の高さでは表示されず、1行目の「空白行」が表示されている場合があります。このような文字が入っていないかどうかチェックしましょう。また、[セル内の改行]が挿入されているセルでは四則計算の計算元の値、VLOOKUP関数の検索値として、正しく判断されないので注意しましょう。

セル内改行

入力された数式がそのまま表示されてしまう

「数式」を入力し確定した時に、「答え」ではなく「数式」が表示される原因として考えられることは三つあります。

数式が表示される

 

一番考えられるのは、「数式」が表示される設定になっていることです。
[数式]タブの[数式の表示]が設定されていると、シート内の「数式」はすべて「答え」ではなく「数式」が表示されます。
この時、[数式]タブの[数式の表示]のボタンは少し暗いグレーになっているので確認できます。
[数式]タブの[数式の表示]ボタンをクリックすることで解除できます。

数式の表示

 

二つ目は「数式」の入力の仕方が間違っている場合です。
例えば「数式」の初めの「=」の前に「空白」が一つ入力されていると、それは「数式」として認識されず「文字列」となります。このような一見見えないような文字が「=」の前に入っていないかチェックしましょう。また、数式の途中や最後に[Alt+Enterキー]による[セル内の改行]が挿入されている場合も文字列となり計算されません。
Excelは、計算式として成立するように自動的に計算式内の空白を削除する場合があります。
「=A1[空白]+A2」のような演算子の前後の空白を削除してくれるほか、「=A[空白]1+1[空白]2」のように、値の中の空白も警告をしたうえで自動で削除します。
しかし、「=」の前の空白だけは削除せず、文字データになります。

=の前の空白

 

また、「=」の前に「’」が入力されていると、Excelは文字列と判断し、セル上では「’」は表示されず、その後の文字は入力したそのままで表示されます。
三つ目は[セルの表示形式]が[文字列]形式になっている場合です。下に書いてある対処法で対処しましょう。

小数点以下のゼロが消えてしまう

Excelは[標準の表示形式]では意味のある桁までしか表示しません。
「1.00」と入力すれば「1」という表示になります。

意味のある数字までしか表示しない

 

小数点以下を揃えて入力したい時は、予め小数点以下の桁数を表示する[表示形式]をセルに設定しておきましょう。
小数点以下の桁数の表示は、[ホーム]タブの[小数点以下の表示桁数を増やす]、[小数点以下の表示桁数を減らす]で設定します。

小数点以下の表示の操作

数値を入力すると#が連続した文字になる

[数値の表示形式]が設定されているセルに、セルの列幅で収まり切れない桁数の数値を入力すると、「#######」という表示になります。

#で表示される

これは、列幅を広げるだけできちんと表示しますが、さらに多くの桁数を入力した時には、また「#######」の表示になります。
あらかじめ列幅を大きく設定しておく、それでも「#######」の表示になる時はフォントサイズを小さくするという対処が必要です。
自動的にフォントサイズを調整する設定もあります。
設定したいセルをクリックして、[Ctrlキー+1]のショートカットキーを押し、[セルの書式設定]ダイアログボックスを表示します。
[配置]タブの[縮小して全体を表示する]のチェックを入れ、[OK]ボタンをクリックします。

縮小して全体を表示する

 

入力した数値は、指定した表示形式通りにセル内に表示されるようになります。
ただし、桁数が多い場合は数値が見えにくくなります。
これは桁数だけではなく、フォントサイズが大きい場合や、セルにインデントが多く設定されている場合でも起きます。

数値が四捨五入された小数点以下表示になりE+がついた

Excelにセルの幅を超えるような大きな数値を入力したとき、少し足りないだけなら、Excelは自動で列幅を広げてくれます。
その後にもっと多くの桁の数値を入力した場合、「E+」がつく、「指数」表示になります。

指数表示

 

「1234567890」と入力すると、セルの幅が広がり、「1234567890」が表示されます。
そのセルに「1234567890123」と入力すると、「1.23457E+12」という表示になります。
「1.23457E+12」は、「1.23457×10の12乗」という意味で、「1234567890123」との「7」以降が四捨五入され「1.23457」という数値になっています。

数値を入力したのに左揃えで入力される

普通「数値」を入力したときは自動で[右揃え]になりますが、[左揃え]になる場合があります。
セルに[左揃え]の設定がされている場合は、どんな文字を入力しても「左」に揃います。
または、「数値」ではなく「文字データ」として認識されている場合は、[左揃え]になります。セルに[文字列の表示形式]が設定されている場合は、どんな文字を入力しても[文字データ]として認識されます。
「数値」の最後に、うっかり「半角スペース」などが入力されていても「文字データ」として認識されてしまいます。これは、手入力の時は自動で「数値」になりますが、ネット上のデータやWordのデータを貼り付けした時には、どんな文字が混ざっているかわかりませんので、起きる可能性があります。
「’」が入力されていると、「’」は表示されずに文字列と判断します。

文字列として数字が入っている

 

Excelは「数値」に見えるものを「文字列」として入力されている場合、そのセルの左上に緑の三角で[エラー表示]をします。

セルに入力されている値が数値かどうか確認する方法

表示がおかしいセルや、範囲内のデータをExcelが数値として認識しているかどうかをチェックする方法があります。
[ホーム]タブの[検索と選択]をクリックし、その中の[条件を指定してジャンプ]をクリックすると、[条件を指定してジャンプ]のダイアログボックスを表示できます。[条件を指定してジャンプ]のダイアログボックスは、キーボードで[Alt][H][F][D][S]の順番で押すことで表示することもできます。
このダイアログボックスで[定数]をチェックし、[数値][文字][論理値][エラー値]のうちの、[数値]のみチェックを入れ[OK]ボタンをクリックすれば、数値のセルが選択されます。

数値のセルを選択する

 

文字のみチェックを入れれば、文字列のセルが選択できます。

全体で使える対処法

全体的に使える対処法は、簡単です。
[数式バー]でセルの入力内容を確認し、表示形式を「標準」に戻せば良いのです。
セルを「標準」の表示形式に戻すには、表示形式がおかしくなっているセルをクリックまたは範囲選択し、[Ctrlキー]と[Shiftキー]を押したまま、「~」のキーを押します。
もしこのショートカットキーが効かない場合は、[ホーム]タブの[数値の書式]から「標準」を選びます。

標準スタイルに戻す

 

背景色とフォントの色が同じということや、左右揃えが原因といった書式が原因であれば、[ホーム]タブの[クリア]から[書式]のクリアを選択します。
[書式のクリア]をキーボードでの操作をするのであれば、[Alt][H][E][F]のキーを順番に押していきます。

書式のクリア

VALUE関数の活用

一度セルに文字データとして入力された文字は、表示形式を「標準」や「数値」に変更しても「文字データ」のままです。
大量に「文字データ」のまま入力していたら、再度入力し直しするのも大変です。
そこで、あらかじめ、表示形式を「標準」にした後に、「数値」に見える「文字」は「数値」に変換する関数、「VALUE関数」が使えます。
使い方は簡単で、次の通りです。
=VALUE(数値に見える文字列)
これで「文字」を「数値」に変換した値を、近くの空欄のセルに求め、その求めた範囲をコピーし、もともとのデータがあった範囲に上書きする形で[値として貼り付け]すれば解決します。
ただし、元のデータ範囲に「abc」などの文字データがあった場合[#VALUE!]エラーとなり、また、空白の箇所は「0」に変換されるので、次のように空白の場合とエラー回避の計算式を使うとよいでしょう。
数値が文字列として入力されているセルがA1の場合、
=IF(A1="","",IFERROR(VALUE(A1),A1))
となります。

文字列となった数値や計算式を変換できる計算式

置換の活用

VALUE関数での対処では、もしも計算式があった場合は対応できません。
計算式は、置換機能を使います。
Excelは置き換えた瞬間に、再度計算式として成立する内容なのかをチェックしているのです。
手順は、あらかじめ、表示形式を「標準」にした後に、[ホーム]タブの[検索と選択]をクリックし、[置換]をクリックします。

置換

[置換前の文字列]に「'=」、[置換後の文字列]に「=」を入力し、[すべて置換]ボタンをクリックします。

'=を=に

これで、「'」によって文字列として入力されているセルを計算式にすることができます。
さらに、[置換前の文字列]に「=」、[置換後の文字列]にも「=」を入力し、[すべて置換]ボタンをクリックします。

=を=に

これで、書式設定を文字列にしたことによって文字列として入力されているセルを計算式にすることができます。
表示形式を標準にしないでこの作業をすると、数値データなのに左揃えになってしまうような正常ではない設定が残ることがあります。

区切り位置の活用

実は、数値も計算式も一回の操作で変換する方法があります。
[区切り位置]機能を使います。
[区切り位置]機能が実行されたセルは、特に指定しなければ、自動的に、「文字列」は「文字列」のまま、「数値」に見えるものは「数値」に、「日付」に見えるものは「日付」に、「=」で始まっていれば「計算式」にするので、データの変換にも使えるのです。
あらかじめ、表示形式を「標準」にした後に、[データ]タブの[区切り位置]ボタンをクリックし、そのまま[完了]ボタンをクリックします。

区切り位置

 

[置換]を使って変換する時と同様、表示形式を「標準」にしないでこの作業をすると、正常ではない設定が残ることがあります。

最後に

入力してあるデータは正しく入力されていても、きちんと表示しないことがあるということがお分かり頂けたかと思います。
本当に正しく入力されているかどうかを確認するには、[数式バー]を確認しましょう。
[数式バー]には、そのセルに入力されているデータの正体が表示されています。
しかし、「日付」の書式が設定されていると、数式バー上も「日付」の表示がされてしまいますので注意が必要です。表示形式を「標準」に戻すことで日付ではなく、「数値」として表示されます。

Back to top