【Excel】入力した数値に対する表示形式について

質問

仮名や漢字、アルファベットなどの「文字」。また「数字」や「記号」と表示形式との関係を教えてください。

以下、具体的な数値を入力する必要がある日付、時刻を除いた表示形式すべてを対象とした結果を述べる。指数についてはよくわからないため除外。

(1)入力した数値は入力どおり、設定した表示形式も設定どおりの場合
例）冒頭に0のない数字「123」を入力（表示形式「標準」の場合）
➡「123」と表示された

(2)入力した数値も設定した表示形式も入力、設定どおりではない場合
例）冒頭に0のある数字「0123」を入力（表示形式「分数」の場合）
➡「123」と表示された

(3)入力した数値が入力どおりだが、表示形式が設定どおりではない場合
例）冒頭に0のない数字「123」を入力（表示形式「分数」の場合）
➡「123」と表示された

(4)入力した数値が入力どおりではないが、表示形式は設定どおりの場合
例）冒頭に0のない数字「123」を入力（表示形式「数値」の場合）
➡「123」と表示された

他の組み合わせ結果も、入力数値と表示形式が不規則にでます。

たとえば分数を文字列で設定して「1/2」と入力すると分数になるのに、「標準」だと「1月2日」とでます。

同様、「標準」に対し「1/2」や「1-2」と入力すると双方とも「1月2日」となりますがこれはなぜでしょうか？

この場合「標準」「通貨」「会計」「パーセンテージ」で日付表示となります（「標準」は分かる気がしますが「通貨」「会計」「パーセンテージ」までもが日付扱いになります）。

特に（入力した数値が違う結果になるのは）冒頭に0がつく数字の場合は、他のすべての表示形式に対しても同様の結果となり違いが顕著です。

冒頭に0を置いた数字を示す場合を例外的に、原則は標準にしたほうがよいそうですが、上で述べたように「標準」でも「1/2」と分数を入力したいのに「1月2日」というように日付がでてしまいます。

(1)なぜこのようになるのでしょうか？

(2)添付した画像は表示形式と入力した文字種との対応結果を示した表ですがこのように規則性がないためそれぞれの文字種に対しどの形式が適しているのかわかりません。

文字と形式を使い分ける考え方や法則性などがあれば教えてください。

(3)「指数」「その他」はどのような文字種や目的に使えばよいのでしょうか？

回答No.3

noname#231016

>たとえば分数を文字列で設定して「1/2」と入力すると分数になるのに、「標準」だと「1月2日」とでます。
表示形式を文字列にして 1/2 と入力すると分数の 1/2 になるのではなく文字列の"1/2"になりますので 1/2 = 0.5 という数値ではありません。

>同様、「標準」に対し「1/2」や「1-2」と入力すると双方とも「1月2日」となりますがこれはなぜでしょうか？
文字列以外の表示形式では数値と解釈できる文字列（数字のみ、1/2、1-2など）は自動的に数値化されます。
従って、1/2 や 1-2 は日付の1月2日と読み替えて今年の1月2日（2018/1/2は1900/1/1から43102日経過）と解釈されるようにプログラムされています。
分数は数値ですが 1/2（0.5）という値を入力するには 0.5 と入力してから表示形式を分数にするか、先に表示形式を分数にしてから 1/2 と入力すればよいでしょう。（数式バーに表示される値は 0.5 です）

>この場合「標準」「通貨」「会計」「パーセンテージ」で日付表示となります（「標準」は分かる気がしますが「通貨」「会計」「パーセンテージ」までもが日付扱いになります）。
入力時の表示形式は文字列以外の時は数値と解釈できる文字は数値として扱われますので「標準」は日付と解釈できる文字列の時は1900/1/1を1とした経過日数に置き換えてセルへ格納し、自動的に表示形式を日付に切り替えます。

>(1)なぜこのようになるのでしょうか？
これはExcelを作った人たちが決めたことなので使う人はそのような動作になることを理解しなければなりません。

>(2)添付した画像は表示形式と入力した文字種との対応結果を示した表ですがこのように規則性がないためそれぞれの文字種に対しどの形式が適しているのかわかりません。
「通貨」「会計」「パーセンテージ」等では 1/2 を 0.5 と解釈して数値化しますが少数を四捨五入して 1 と表示されることがあります。
数字の 0123 は数値として 123 と解釈されますので先頭の 0 は数値として無意味なので捨てられます。数値でも 0123 と表示したいときは表示形式を "0000" と指定しますが 012 と入力すると 0012 と表示されます。
実際にセルへ格納された値は表示形式を「標準」に戻せば分かりますので入力した文字と異なる場合は入力文字列をExcelが勝手に変換したと思ってください。
勝手に変換させないようにするのは経験則で覚えるしかないでしょう。

>(3)「指数」「その他」はどのような文字種や目的に使えばよいのでしょうか？
「指数」は桁数の多い数値を10の何乗になるかで少ない桁数で概略値を示すものなので数値を扱う人が知らないはずはないでしょう。
「その他」は少ない種類の一覧から選ぶだけなのでどのような値をどのように表示するか即座に分かると思います。
必要のない表示形式なら覚えなくても良いでしょう。

回答No.2

Nobu-W

https://support.microsoft.com/ja-jp/help/883199

https://support.office.com/ja-jp/article/%e3%82%bb%e3%83%ab%e3%81%ae%e6%9b%b8%e5%bc%8f%e8%a8%ad%e5%ae%9a%e3%82%92%e4%bd%bf%e3%81%84%e3%81%93%e3%81%aa%e3%81%9d%e3%81%86-3-99457d47-7e9b-41fb-9b43-ca2af937baa6

https://allabout.co.jp/gm/gc/297804/

https://www.hello-pc.net/howto-excel/syoshiki/
その他いっぱいありますが・・・
エクセルを使うための決まり事みたいな感じで、
ゆ～っくり理解していけばいいと思いますよっ

回答No.1

noname#231016

>たとえば分数を文字列で設定して「1/2」と入力すると分数になるのに、「標準」だと「1月2日」とでます。
>同様、「標準」に対し「1/2」や「1-2」と入力すると双方とも「1月2日」となりますがこれはなぜでしょうか？
セルの表示形式が「標準」のとき数字だけを入力すると自動的に数値と認識します。従って 0123 と入力すると 123 という数値になります。
1/2 と入力すると「月/日」が入力されたものと判断され今年の1月2日と言う日付の値に変換されます。
1-2 も 1/2 と同様に日付の表示形式に自動変換して今年の1月2日になります。

>冒頭に0を置いた数字を示す場合を例外的に、原則は標準にしたほうがよいそうですが、上で述べたように「標準」でも「1/2」と分数を入力したいのに「1月2日」というように日付がでてしまいます。
Excelで扱う値を幾つかの種類に分けて考える必要があります。
1.数値（日付や分数、指数も含めて計算できる数値）
2.文字列（文章や住所、氏名等数字や記号を含む文字列）
4.論理値（等号や不等号などの比較演算の結果を表す値でTLUE、FALSE）
16.エラー値（数式の記載ミスなどで起こるエラー値）
64.配列（計算途中で扱われる複数セルの値等）
Excelの表で見ているセルの値は数値と文字列になり、エラー値は数式で発生しないように組み立てます。
また、1つのセルへ複数の値は保存できませんので配列の扱いは計算式の中のみになり、計算結果は1つの値となります。
質問の中で分数の 1/2 は数値の 0.5 としてセルに保存されれば正しい扱いになっています。そのためには入力前に表示形式を「分数」に指定すべきです。
表示形式が「標準」では 1/2 を日付の1月2日と解釈するようにプログラムされているようです。
強制的に分数として入力したいときは表示形式を「分数」に変更してから 1/2 と入力します。その結果は 0.5 という数値で表示が 1/2 になるということです。

>(1)なぜこのようになるのでしょうか？
前述のようにExcelを作った人が決めたことです。

>(2)添付した画像は表示形式と入力した文字種との・・・・・
画像が見当たりません。

>(3)「指数」「その他」はどのような文字種や目的に使えばよいのでしょうか？
「指数」は浮動小数点の自然数を簡便化して表示する形式です。
実際の値は数値なので文字列とは異なる値となります。
「日付」は1900/1/1を1としてそこからの経過日数が実際の数値です。
従って2018/1/2は1月2日と表示されても実際の数値は 43102 という数値です。
「分数」は表示形式を先に指定してから 1/2 や 2/3 のように入力しますが割り切れる分数（1/2、1/4、2/5）のような場合は小数（0.5、0.25、0.4）で入力しても分数で表示できます。
基本的には数値と文字列を意識して入力すれば良いことになります。
特例としてはい付けと解釈できる文字を入力すると自動的に日付の値に変換してセルへ格納されるので注意が必要になります。
また、先頭に0を含み数字だけの文字列を入力するときは表示形式を「文字列」にしてから入力します。
尚、計算結果を代入するための数式をセルに設定するときは表示形式を「文字列」にしてはいけません。