Excelで、非常に大きい値や非常に小さい値、ゼロに限りなく近い値を入力すると「E」「E+」「E-」という文字を含む表記になることがあります。これを、指数表記といいます。もちろん指数表記はサイエンスの世界で頻繁に使われるものであり、非常に便利なものであることは言うまでもありませんが、科学計算をしているわけでもないのに勝手に指数表記に変換されたら戸惑ってしまいます。
せっかく画面に「E」が出てきたのであれば、単に「E」を消す方法だけを学ぶのではなく、どういった場合に「E」が表示されるのかをしっかりと理解したほうが良いです。一度、本格的に学んでおけば戸惑うこともないでしょう。
そこで、今回は、指数の表示形式の「E」について出題します。
目次
- 1.あえてEを表示してみよう
- 2.Eとは何か(非常に大きな数)
- 3.JANコード
- 4.Eとは何か(ゼロに限りなく近い値)
- 5.Eを含むコードを入力するときに注意する
- 6.仮数部と指数部
- 7.発展問題(MOD関数を使用した問題)
- 8.Excelで指数表記になる条件
- 9.指数の表示形式の設定と解除
- 10.ユーザー定義表示形式(指数部の桁数)
- 11.ユーザー定義表示形式(仮数部の整数部分の桁数)
1.あえてEを表示してみよう
問題
(1)次の図のようにセルA1に5桁の整数「12345」を入力し、10倍する計算を繰り返した。この計算をさらに繰り返すとどのような表示になるか。
(2)次の図のようにセルB1に5桁の整数「12345」を入力し、10で割る計算を繰り返した。この計算をさらに繰り返すとどのような表示になるか。
解説
(1)大きい数の場合
セルA1に「12345」を入力します。
セルA2に「=A1*10」と入力して、下向きにオートフィル(コピー)すると、10倍を繰り返す計算になります(参考:【Excel】オートフィルは累積的に同じ計算を繰り返すときにも使う)。
9桁以上の数は「1.23E+XX」という形式の表示になります。これはA列の幅が「8.38」のため、9桁以上の数値が表示できないのが原因です。
そこで、A列の幅を広げます。十分な列幅があるにもかかわらず、12桁以上の数が「1.23E+XX」という形式の表示になります。
幅を狭くすると「####」になります。
(2)ゼロに限りなく近い値
セルB1に「12345」を入力します。
セルB2に「=A1/10」と入力して、下向きにオートフィル(コピー)すると、10分の1を繰り返す計算になります。さらに繰り返すと「1.23E-XX」という形式の表示になります。これはB列の列幅が狭いのが原因です。
そこで、B列の幅を広げます。十分な列幅があるにもかかわらず、途中から「1.2345E-XX」といった形式の表示になります。
2.Eとは何か(非常に大きな数)
問題
「1.2345E+14」とはいくつか。また、Eの表示をなくして通常の数値の表記にしなさい。
解説
(1)指数表記とは何か
さきほどの問題で、1.2345E+14の表示形式を「数値」にすると123450000000000となります(参考:【Excel】表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本))。123兆4500億です。
「1.2345E+14」のような表記の仕方を「指数表記」(Exponential Notation)といいます。なぜ、このような訳の分からない表記の仕方をするのかと言うと、大きな数はたくさんの数字が並び、大小関係が分かりにくいからです。例えば「123450000000000円」と言うよりも「123兆4500億円」のほうが分かりやすいです。
国家予算規模の金額を表すのに日本語では億や兆を使いますが、コンピュータでは、指数(Exponent)の頭文字である「E」を使って表すのがきまりとなっています。このことはJIS日本工業規格にも定められています。
例えば、123兆4500億は12345を100億倍した数ですが、100億は10を10回かけた数なので「12345E+10」と表記します。
これは12345の後に0が10個付くと考えることもできます。
いっぱんに10倍する計算のことを「E+」と表記し、10倍する計算をn回繰り返すことを「E+n」と表します。10回繰り返せば「E+10」です。
しかし、元の数を12345ではなく、1.2345にすると100兆倍、つまり、10倍する計算を14回繰り返すことになるので、「1.2345E+14」と書くこともできます。
このように、元の数を変えることによって10倍する回数が変わります。同じ数を表すのに表し方がたくさんあります。
(2)Excelの仕様
Excelでは列幅が狭い場合は、その列に合わせて指数表記になります。
さらに狭くなって指数表記で表すこともできない場合は「####」となります。
また、Excelでは12桁以上(1000億以上)の数を大きな数として扱います。列幅を広げたとしても、12桁以上の数値は原則として指数表記となります。
Excelの指数表記は、元の数mの整数部分(小数点の左側)が1~9になるように表示します。したがって、123兆4500億は標準の表示形式では1.2345E+14となります。ただし、これは標準的な表示形式であって、同じ数でも表記の仕方がいろいろあるので、ユーザー定義によって表示形式を変えることは可能です(後述)。
(3)Eを消す方法
A列の書式(表示形式)を「数値」にします。指数表記になっている数値は、表示形式を数値にすると通常の数値の表示になります。通常、表示形式を標準に戻すと入力したとおりの表示になりますが、大きな数は指数表示が標準なので、数値にしなければなりません。
3.JANコード
問題
JANコードを入力したら指数表記になった。指数表記を解除しなさい。
解説
JANコードはEANコードともいいますが、商品のバーコードにも使用される識別番号であり、13桁の数値です(参考:Excelでバーコード(JANコード)を使うための基礎知識の総まとめ【勉強会資料】)。
Excelでは12桁以上の数値を指数表記とするため、JANコードも指数表記となります。これを元に戻すには表示形式を「数値」にします。
また、入力する前にあらかじめ文字列の表示形式にしておくという方法もあります。
テキストファイルウィザードによりCSVや他のファイルを読み込む場合も、文字列の表示形式にしなければなりません(参考:【ExcelとCSV】初心者のためのCSV取り込み事例演習教材(勉強会資料))。
4.Eとは何か(ゼロに限りなく近い値)
問題
「1.2345E-7」とはいくつか。また、Eの表示をなくして通常の数値の表記にしなさい。
解説
非常に小さな数も小数点以下にたくさんの数字が並び、大小関係が分かりにくくなります。例えば「0.00005キロメートル」と言うよりも「5センチ」のほうが分かりやすいです。
指数表記では10倍することを「E+」と表しましたが、逆に10で割ることを「E-」と表します。例えば、0.00000012345は1.2345の1000万で割った数ですが、1000万は10を7回かけた数なので「1.2345E-7」と表記します。
これは1.2345の小数点を左に7個動かすと考えることもできます。
いっぱんに10で割る計算のことを「E-」と表記し、10で割る計算をn回繰り返すことを「E-n」と表します。
しかし、大きな数と同じように、元の数を変えることによって10で割る回数が変わります。
Excelでは、元の数の整数部分が1~9になるように表示します。したがって、0.00000012345は標準の表示形式では1.2345E-7となります。ただし、これは標準的な表示形式であって、ユーザー定義によって表示形式を変えることは可能です(後述)。
書式(表示形式)を「数値」にします。整数表示なので0になります。
小数点以下の表示桁数を増やすことによって、通常の小数の表示になります。
5.Eを含むコードを入力するときに注意する
問題
「6e23」と入力しなさい。また、6.02E23、6.02E-23と入力しなさい。
解説
Excelで「6e23」と入力すると「6.00E+23」となります。このように、数字+E+数字を入力すると指数表記の数値が入力されたものとみなされます。
本当に「6e23」と入力するには、文字列の表示形式にしなければなりません。
商品番号「6E23」の商品があったら注意が必要です。また、24cm・3Eの靴を「3E24.0」「24e3」などと入力したら大変なことになります。このようなトラブルを避けるため、通常は、「数字+E+数字」のコードをつけないようにします(理系であればすぐに気がつくと思いますが、一般の人はなかなか気がつかないかもしれません)。
「6.02e23」「6.02e-23」と入力すると、プラスは自動的につきます。また、Eは大文字になります。
6.仮数部と指数部
問題
6.02E23、6.02E-23の仮数部と指数部をそれぞれ答えなさい。
解説
指数表記のEの左側の数のことを仮数部(significand)、右側の数を指数部(exponent)といいます。ちなみに、指数表記のEは、高校数学に出てくるネイピア数のeとはまったく関係ありません。
「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個動かすと考えることもできます。
小数点を右に動かすのが「E+」、左に動かすのが「E-」です。「E+」はものすごく大きな数、「E-」は0に極めて近い小さな数です。
7.発展問題(MOD関数を使用した問題)
問題
次のようにA列に文字列として、1、12、123、1234、12345、・・・と入力しなさい。また、これらの文字列を数値に変換してB列に表示しなさい。
解説
(1)有効桁数15けた
セルA1に「1」を入力します。
セルA2に「=A1&MOD(ROW(),10)」と入力して、下向きにオートフィル(コピー)すると、行番号の1の位を連結する計算になります。文字列の場合は桁数に関係なくすべて表示されます。
1を掛けると数値に変換できます(参考:【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ)。セルB1に「=A1*1」と入力して、下向きにオートフィル(コピー)すると、12桁以上の数は指数表記になります。
指数表記は小数点を入れて11文字以内になるように表示されるので、それより多い場合は小数の部分が四捨五入になります。
B列の表示形式を数値にします。Excelの有効桁数は15桁なので、16桁以上の数値は0になります。クレジットカードの番号は16桁なので、必ず文字列で入力しなければなりません。これを数値に変換してはいけません。指数表示になるだけでなく1の位が0になってしまいます。
まとめると次のようになります。12桁~15桁の数は数値または文字列の表示形式にすることによって指数表記でない表示にすることができます。16桁以上の場合は有効桁数の関係で16桁以上の数値が0になります。
(2)さらに大きい数は計算できるのか
ちなみに、関数の引数は255文字を超えることができないため、上記の計算の場合、256行目以降エラー「#VALUE!」になります。
また、Excelの仕様として310桁以上の数値を扱うことができないので、この範囲を超えるとエラー「#NUM!」となります。
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桁の指数表記にしなさい。また、標準に戻しなさい。
解説
セルA1に「123456789012345」を入力します。15桁なので指数表記になります。
セルA2に「=A1/10」と入力して、下向きにオートフィル(コピー)すると、10で割る計算になります。これを繰り返すと、21行目以降、0.0001未満の数になりますから上記条件により指数表記になります。
左隣のセルを参照します。
B列で、その他の表示形式にします。
ここで、小数点以下の桁数を3にします。
この小数点以下の桁数とは仮数部のことです。これで、すべて指数表記になります。指数部が1桁の場合は0がついて2桁になります。
標準にすると元に戻ります。
10.ユーザー定義表示形式(指数部の桁数)
問題
さきほどの問題で、次の表示形式にしなさい。
(1)0.000E+00
(2)0.000E-00
(3)0.000E+0
(4)0.000E-0
(5)0.############################
解説
セルの書式設定にはユーザー定義の表示形式があります。これによって自由に表示形式を決めることができます(参考:【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ)。
「0」や「#」の記号で数値の桁数を表します。例えば、「0.000E+00」とすると、仮数部の整数部分が1桁、小数第3位までで、指数部が2桁になります。指数部がマイナスの場合は自動的に「E-」になります。
「0.000E-00」とすると、指数部がプラスの時にプラスが省略となります。
「0.000E+0」「0.000E-0」とすると、指数部が1桁の時に0がつかなくなります。
小数の指数表記をすべて表示するためには、小数点の後に#を大量に打てばよいです。
11.ユーザー定義表示形式(仮数部の整数部分の桁数)
問題
さきほどの問題で、次の表示形式にしなさい。
(1)##0.00E+00
(2)#0.00E+0
解説
例えば、1234mをキロメートルに換算すると1.234kmです。
大きな数や小さな数を表すときに、キロk、メガM、ギガG、テラTのように3桁ごとの補助単位(正確には接頭辞といいます)を使うことがあります。
指数表記にすると、指数部は3の倍数になります。
このとき整数部分は1桁~3桁になります。このように仮数部の整数部分を3桁以内にして、指数部を3の倍数にする表記のことを工学表記(ENG表記)と言うことがあります。
工学表記は表示形式を「##0.00E+00」にします。「##0」は3桁以内であることを表します。
指数部は必ず3の倍数になります。指数部が「E+03」の場合はキロ、「E+06」の場合はメガ、「E+09」の場合はギガとなります。
これを利用して、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")
「#0.00E+0」は仮数部の整数部分「#0」が2桁です。整数部分が1桁~2桁になるように指定しているのですから、指数部は2の倍数になります。このように、指数部は、仮数部の整数部分として指定した桁数の倍数になります。
解説は以上です。
*補足説明その1*指数表記は有効数字を表せるというメリットがありますが、残念ながらExcelでは有効数字が保存されないのでその説明を割愛しています。
*補足説明その2*接頭辞に変換する方法としてLOG関数を使う方法もあります。なお、マイナスの場合はLOG(ABS(A2))とすればよいです。
=ROUND(A2/(10^(INT(LOG(A2)/3)*3) ),2)&CHOOSE(6-LOG(A2)/3,"T","G","M","k","","m","μ","n","p")
— W.D. (@WD4096) 2019年7月17日
logとchooseで出力しました。あとround。 pic.twitter.com/613D3aq5X6