わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法


Excelで、非常に大きい値や非常に小さい値、ゼロに限りなく近い値を入力すると「E」「E+」「E-」という文字を含む表記になることがあります。これを、指数表記といいます。もちろん指数表記はサイエンスの世界で頻繁に使われるものであり、非常に便利なものであることは言うまでもありませんが、科学計算をしているわけでもないのに勝手に指数表記に変換されたら戸惑ってしまいます。

 

せっかく画面に「E」が出てきたのであれば、単に「E」を消す方法だけを学ぶのではなく、どういった場合に「E」が表示されるのかをしっかりと理解したほうが良いです。一度、本格的に学んでおけば戸惑うこともないでしょう。

そこで、今回は、指数の表示形式の「E」について出題します。

 

「セルの書式設定」完全マスターシリーズ(この記事は第8回です)
  1. Excel「セルの書式設定」最低限練習すべき設定と解除の特訓問題50問
  2. 【Excel】均等割り付けの設定と解除の方法と、種類を見分ける練習
  3. 表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本)
  4. セルの表示形式「ユーザー定義」書式記号完全総まとめ
  5. ユーザー定義表示形式のサンプルの意味を全部解説します
  6. 表示形式で単位を付ける、または単位を変えるときの注意点
  7. Excelで先頭にプラス+をつける、マイナスを三角△で表示する方法
  8. なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法
  9. Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?

 

目次

1.あえてEを表示してみよう

問題

(1)次の図のようにセルA1に5桁の整数「12345」を入力し、10倍する計算を繰り返した。この計算をさらに繰り返すとどのような表示になるか。

f:id:waenavi:20190717124746j:plain

 

(2)次の図のようにセルB1に5桁の整数「12345」を入力し、10で割る計算を繰り返した。この計算をさらに繰り返すとどのような表示になるか。

f:id:waenavi:20190717124735j:plain

 

解説

(1)大きい数の場合

セルA1に「12345」を入力します。

f:id:waenavi:20190717124925j:plain

 

セルA2に「=A1*10」と入力して、下向きにオートフィル(コピー)すると、10倍を繰り返す計算になります(参考:【Excel】オートフィルは累積的に同じ計算を繰り返すときにも使う)。

f:id:waenavi:20190717124848j:plain

 

9桁以上の数は「1.23E+XX」という形式の表示になります。これはA列の幅が「8.38」のため、9桁以上の数値が表示できないのが原因です。

f:id:waenavi:20190717125039j:plain

 

そこで、A列の幅を広げます。十分な列幅があるにもかかわらず、12桁以上の数が「1.23E+XX」という形式の表示になります。

f:id:waenavi:20190717125240j:plain

 

幅を狭くすると「####」になります。

f:id:waenavi:20190717125242j:plain

 

(2)ゼロに限りなく近い値

セルB1に「12345」を入力します。

f:id:waenavi:20190717125418j:plain

 

セルB2に「=A1/10」と入力して、下向きにオートフィル(コピー)すると、10分の1を繰り返す計算になります。さらに繰り返すと「1.23E-XX」という形式の表示になります。これはB列の列幅が狭いのが原因です。

f:id:waenavi:20190717125552j:plain

 

そこで、B列の幅を広げます。十分な列幅があるにもかかわらず、途中から「1.2345E-XX」といった形式の表示になります。

f:id:waenavi:20190717125555j:plain

 

2.Eとは何か(非常に大きな数)

問題

「1.2345E+14」とはいくつか。また、Eの表示をなくして通常の数値の表記にしなさい。

f:id:waenavi:20190717125914j:plain

 

解説

(1)指数表記とは何か

さきほどの問題で、1.2345E+14の表示形式を「数値」にすると123450000000000となります(参考:【Excel】表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本))。123兆4500億です。

f:id:waenavi:20190717130110j:plain

 

「1.2345E+14」のような表記の仕方を「指数表記」(Exponential Notation)といいます。なぜ、このような訳の分からない表記の仕方をするのかと言うと、大きな数はたくさんの数字が並び、大小関係が分かりにくいからです。例えば「123450000000000円」と言うよりも「123兆4500億円」のほうが分かりやすいです。

国家予算規模の金額を表すのに日本語では億や兆を使いますが、コンピュータでは、指数(Exponent)の頭文字である「E」を使って表すのがきまりとなっています。このことはJIS日本工業規格にも定められています。

例えば、123兆4500億は12345を100億倍した数ですが、100億は10を10回かけた数なので「12345E+10」と表記します。

f:id:waenavi:20190717130855j:plain


これは12345の後に0が10個付くと考えることもできます。

f:id:waenavi:20190717130917j:plain


いっぱんに10倍する計算のことを「E+」と表記し、10倍する計算をn回繰り返すことを「E+n」と表します。10回繰り返せば「E+10」です。

f:id:waenavi:20190717131714j:plain

 

しかし、元の数を12345ではなく、1.2345にすると100兆倍、つまり、10倍する計算を14回繰り返すことになるので、「1.2345E+14」と書くこともできます。

f:id:waenavi:20190717131822j:plain

 

このように、元の数を変えることによって10倍する回数が変わります。同じ数を表すのに表し方がたくさんあります。

f:id:waenavi:20190717132547j:plain

 

(2)Excelの仕様

Excelでは列幅が狭い場合は、その列に合わせて指数表記になります。

f:id:waenavi:20190717125039j:plain

 

さらに狭くなって指数表記で表すこともできない場合は「####」となります。

f:id:waenavi:20190717125242j:plain

 

また、Excelでは12桁以上(1000億以上)の数を大きな数として扱います。列幅を広げたとしても、12桁以上の数値は原則として指数表記となります。

f:id:waenavi:20190717133136j:plain

 

Excelの指数表記は、元の数mの整数部分(小数点の左側)が1~9になるように表示します。したがって、123兆4500億は標準の表示形式では1.2345E+14となります。ただし、これは標準的な表示形式であって、同じ数でも表記の仕方がいろいろあるので、ユーザー定義によって表示形式を変えることは可能です(後述)。

f:id:waenavi:20190717133524j:plain

 

(3)Eを消す方法

A列の書式(表示形式)を「数値」にします。指数表記になっている数値は、表示形式を数値にすると通常の数値の表示になります。通常、表示形式を標準に戻すと入力したとおりの表示になりますが、大きな数は指数表示が標準なので、数値にしなければなりません

f:id:waenavi:20190717133825j:plain

 

3.JANコード

問題

JANコードを入力したら指数表記になった。指数表記を解除しなさい。

f:id:waenavi:20190717134603j:plain

 

解説

JANコードはEANコードともいいますが、商品のバーコードにも使用される識別番号であり、13桁の数値です(参考:Excelでバーコード(JANコード)を使うための基礎知識の総まとめ【勉強会資料】)。

Excelでは12桁以上の数値を指数表記とするため、JANコードも指数表記となります。これを元に戻すには表示形式を「数値」にします。

f:id:waenavi:20190717134652j:plain

 

また、入力する前にあらかじめ文字列の表示形式にしておくという方法もあります。

f:id:waenavi:20190717135119j:plain

 

テキストファイルウィザードによりCSVや他のファイルを読み込む場合も、文字列の表示形式にしなければなりません(参考:【ExcelとCSV】初心者のためのCSV取り込み事例演習教材(勉強会資料))。

f:id:waenavi:20190717135454j:plain

 

4.Eとは何か(ゼロに限りなく近い値)

問題

「1.2345E-7」とはいくつか。また、Eの表示をなくして通常の数値の表記にしなさい。

f:id:waenavi:20190717135748j:plain

 

解説

非常に小さな数も小数点以下にたくさんの数字が並び、大小関係が分かりにくくなります。例えば「0.00005キロメートル」と言うよりも「5センチ」のほうが分かりやすいです。

指数表記では10倍することを「E+」と表しましたが、逆に10で割ることを「E-」と表します。例えば、0.00000012345は1.2345の1000万で割った数ですが、1000万は10を7回かけた数なので「1.2345E-7」と表記します。

f:id:waenavi:20190717191838j:plain

 

これは1.2345の小数点を左に7個動かすと考えることもできます。

f:id:waenavi:20190717140303j:plain

 

いっぱんに10で割る計算のことを「E-」と表記し、10で割る計算をn回繰り返すことを「E-n」と表します。

f:id:waenavi:20190717140403j:plain

 

しかし、大きな数と同じように、元の数を変えることによって10で割る回数が変わります。

f:id:waenavi:20190717140646j:plain

 

Excelでは、元の数の整数部分が1~9になるように表示します。したがって、0.00000012345は標準の表示形式では1.2345E-7となります。ただし、これは標準的な表示形式であって、ユーザー定義によって表示形式を変えることは可能です(後述)。

f:id:waenavi:20190717141343j:plain

 

書式(表示形式)を「数値」にします。整数表示なので0になります。

f:id:waenavi:20190717140730j:plain

 

小数点以下の表示桁数を増やすことによって、通常の小数の表示になります。

f:id:waenavi:20190717140904j:plain

 

5.Eを含むコードを入力するときに注意する

問題

「6e23」と入力しなさい。また、6.02E23、6.02E-23と入力しなさい。

f:id:waenavi:20190717141531j:plain

 

解説

Excelで「6e23」と入力すると「6.00E+23」となります。このように、数字+E+数字を入力すると指数表記の数値が入力されたものとみなされます。

f:id:waenavi:20190717141636j:plain

 

本当に「6e23」と入力するには、文字列の表示形式にしなければなりません。

f:id:waenavi:20190717141726j:plain

 

商品番号「6E23」の商品があったら注意が必要です。また、24cm・3Eの靴を「3E24.0」「24e3」などと入力したら大変なことになります。このようなトラブルを避けるため、通常は、数字+E+数字」のコードをつけないようにします(理系であればすぐに気がつくと思いますが、一般の人はなかなか気がつかないかもしれません)。

f:id:waenavi:20190717143954j:plain

 

6.02e23」「6.02e-23」と入力すると、プラスは自動的につきます。また、Eは大文字になります。

f:id:waenavi:20190717144207j:plain

 

6.仮数部と指数部

問題

6.02E23、6.02E-23の仮数部と指数部をそれぞれ答えなさい。

解説

指数表記のEの左側の数のことを仮数部significand)、右側の数を指数部exponent)といいます。ちなみに、指数表記のEは、高校数学に出てくるネイピア数のeとはまったく関係ありません。

f:id:waenavi:20190717152306j:plain

 

「6.02E+23」の仮数部は6.02、指数部は+23です。6.02に、10の23乗をかけます。指数部のプラスを省略して「6.02E23」とすることもできます。6.02の小数点を右に23個動かすと考えることもできますから、602のあとに0が21個付きます。

「6.02E-23」の仮数部は6.02、指数部は-23です。指数部のマイナスを省略することはできません。高校数学ではマイナスの累乗として習います。6.02に、10のマイナス23乗(10の23乗分の1)をかけます。6.02の小数点を左に23個動かすと考えることもできます。

f:id:waenavi:20190717192741j:plain

 

小数点を右に動かすのが「E+」、左に動かすのが「E-」です。「E+」はものすごく大きな数、「E-」は0に極めて近い小さな数です。

f:id:waenavi:20190717161432j:plain

 

7.発展問題(MOD関数を使用した問題)

問題

次のようにA列に文字列として、1、12、123、1234、12345、・・・と入力しなさい。また、これらの文字列を数値に変換してB列に表示しなさい。

f:id:waenavi:20190717161549j:plain

 

解説

(1)有効桁数15けた

セルA1に「1」を入力します。

f:id:waenavi:20190717161635j:plain

 

セルA2に「=A1&MOD(ROW(),10)」と入力して、下向きにオートフィル(コピー)すると、行番号の1の位を連結する計算になります。文字列の場合は桁数に関係なくすべて表示されます。

f:id:waenavi:20190717161749j:plain

 

1を掛けると数値に変換できます(参考:【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ)。セルB1に「=A1*1」と入力して、下向きにオートフィル(コピー)すると、12桁以上の数は指数表記になります。

f:id:waenavi:20201103082306j:plain

 

指数表記は小数点を入れて11文字以内になるように表示されるので、それより多い場合は小数の部分が四捨五入になります。

f:id:waenavi:20190717162842j:plain

 

B列の表示形式を数値にします。Excelの有効桁数は15桁なので、16桁以上の数値は0になります。クレジットカードの番号は16桁なので、必ず文字列で入力しなければなりません。これを数値に変換してはいけません。指数表示になるだけでなく1の位が0になってしまいます。

f:id:waenavi:20190717162641j:plain

 

まとめると次のようになります。12桁~15桁の数は数値または文字列の表示形式にすることによって指数表記でない表示にすることができます。16桁以上の場合は有効桁数の関係で16桁以上の数値が0になります。

f:id:waenavi:20190717163038j:plain

 

(2)さらに大きい数は計算できるのか

ちなみに、関数の引数は255文字を超えることができないため、上記の計算の場合、256行目以降エラー「#VALUE!」になります。

f:id:waenavi:20190717163226j:plain

 

また、Excelの仕様として310桁以上の数値を扱うことができないので、この範囲を超えるとエラー「#NUM!」となります。

f:id:waenavi:20190717163344j:plain

Excelの仕様と制限
https://support.office.com/ja-jp/article/excel-%E3%81%AE%E4%BB%95%E6%A7%98%E3%81%A8%E5%88%B6%E9%99%90-1672b34d-7043-467e-8e27-269d656771c3

 

8.Excelで指数表記になる条件

問題

Excelでセルの表示形式が標準に設定されている場合、指数表記になる条件をすべて述べなさい。

解説

指数表記は小数点やE、プラスマイナスを含めて11字以内になるように表示します。したがって、指数表記になるのは12文字以上の数値です。正確にはMicrosoftの公式サイトに掲載されています。

[XL2002]セルの数値が指数表示になる条件
https://support.microsoft.com/ja-jp/help/410233

まとめると次の3つです。

  • 列幅が狭い
    列幅を11以下にして、列幅を超える数値を入力した場合
  • 12桁以上
    小数第1位を四捨五入して整数にしたときに12桁以上になる場合
  • 0に近い
    -0.0001超、+0.0001未満で、小数点以下が10桁以上の数値を入力した場合

 

9.指数の表示形式の設定と解除

問題

次の図のようにセルA1に15桁の整数「123456789012345」を入力し、10で割る計算を繰り返した。B列に隣のセルを参照して、小数点以下桁数3桁の指数表記にしなさい。また、標準に戻しなさい。

f:id:waenavi:20190717163751j:plain

 

解説

セルA1に「123456789012345」を入力します。15桁なので指数表記になります。

f:id:waenavi:20190717163829j:plain

 

セルA2に「=A1/10」と入力して、下向きにオートフィル(コピー)すると、10で割る計算になります。これを繰り返すと、21行目以降、0.0001未満の数になりますから上記条件により指数表記になります。

f:id:waenavi:20190717164404j:plain

 

左隣のセルを参照します。

f:id:waenavi:20190717164327j:plain

 

B列で、その他の表示形式にします。

f:id:waenavi:20190717164520j:plain

 

ここで、小数点以下の桁数を3にします。

f:id:waenavi:20190717164610j:plain

 

この小数点以下の桁数とは仮数部のことです。これで、すべて指数表記になります。指数部が1桁の場合は0がついて2桁になります。

f:id:waenavi:20190717165133j:plain

 

標準にすると元に戻ります。

f:id:waenavi:20190717165135j:plain

 

10.ユーザー定義表示形式(指数部の桁数)

問題

さきほどの問題で、次の表示形式にしなさい。

(1)0.000E+00
(2)0.000E-00
(3)0.000E+0
(4)0.000E-0
(5)0.############################

f:id:waenavi:20190717165800j:plain

 

解説

セルの書式設定にはユーザー定義の表示形式があります。これによって自由に表示形式を決めることができます(参考:【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ)。

f:id:waenavi:20190717170049j:plain

 

「0」や「#」の記号で数値の桁数を表します。例えば、「0.000E+00」とすると、仮数部の整数部分が1桁、小数第3位までで、指数部が2桁になります。指数部がマイナスの場合は自動的に「E-」になります。

f:id:waenavi:20190717171259j:plain

 

「0.000E-00」とすると、指数部がプラスの時にプラスが省略となります。

f:id:waenavi:20190717171649j:plain

 

「0.000E+0」「0.000E-0」とすると、指数部が1桁の時に0がつかなくなります。

f:id:waenavi:20190717172000j:plain

 

小数の指数表記をすべて表示するためには、小数点の後に#を大量に打てばよいです。

f:id:waenavi:20190717172320j:plain

 

11.ユーザー定義表示形式(仮数部の整数部分の桁数)

問題

さきほどの問題で、次の表示形式にしなさい。

(1)##0.00E+00
(2)#0.00E+0

f:id:waenavi:20190717183039j:plain

 

解説

例えば、1234mをキロメートルに換算すると1.234kmです。

f:id:waenavi:20190717181423j:plain

 

大きな数や小さな数を表すときに、キロk、メガM、ギガG、テラTのように3桁ごとの補助単位(正確には接頭辞といいます)を使うことがあります。

f:id:waenavi:20190717173924j:plain

 

指数表記にすると、指数部は3の倍数になります。

f:id:waenavi:20190717181617j:plain

 

このとき整数部分は1桁~3桁になります。このように仮数部の整数部分を3桁以内にして、指数部を3の倍数にする表記のことを工学表記(ENG表記)と言うことがあります。

f:id:waenavi:20190717182442j:plain

 

工学表記は表示形式を「##0.00E+00」にします。「##0」は3桁以内であることを表します。

f:id:waenavi:20190717183156j:plain

 

指数部は必ず3の倍数になります。指数部が「E+03」の場合はキロ、「E+06」の場合はメガ、「E+09」の場合はギガとなります。

f:id:waenavi:20190717183544j:plain

 

これを利用して、TEXT関数などを用いて接頭辞に変換することができます(参考:【Excel】TEXT関数で数値を文字列に変換するときの注意点と練習問題)。

  • =LEFT(TEXT(A2,"##0.00E+00"),LEN(TEXT(A2,"##0.00E+00"))-4)&CHOOSE(VALUE(RIGHT(TEXT(A2,"##0E+00"),3))/3+5,"p","n","μ","m","","k","M","G","T")

f:id:waenavi:20190717183723j:plain

 

「#0.00E+0」は仮数部の整数部分「#0」が2桁です。整数部分が1桁~2桁になるように指定しているのですから、指数部は2の倍数になります。このように、指数部は、仮数部の整数部分として指定した桁数の倍数になります。

f:id:waenavi:20190717184131j:plain

 


解説は以上です。

*補足説明その1*指数表記は有効数字を表せるというメリットがありますが、残念ながらExcelでは有効数字が保存されないのでその説明を割愛しています。

*補足説明その2*接頭辞に変換する方法としてLOG関数を使う方法もあります。なお、マイナスの場合はLOG(ABS(A2))とすればよいです。


 

「セルの書式設定」完全マスターシリーズ(この記事は第8回です)
  1. Excel「セルの書式設定」最低限練習すべき設定と解除の特訓問題50問
  2. 【Excel】均等割り付けの設定と解除の方法と、種類を見分ける練習
  3. 表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本)
  4. セルの表示形式「ユーザー定義」書式記号完全総まとめ
  5. ユーザー定義表示形式のサンプルの意味を全部解説します
  6. 表示形式で単位を付ける、または単位を変えるときの注意点
  7. Excelで先頭にプラス+をつける、マイナスを三角△で表示する方法
  8. なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法
  9. Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ