エクセルの限界に挑む

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
　　　　　◆◆◆◆　エクセルの限界に挑む　◆◆◆◆
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

先月、ある会員さんからこんな質問を受けました。

「エクセルのセルの中に16桁以上の数字を入れるのはどうしたらいいのか」

という質問です。

-----------------------------------
　エクセルの有効桁は15桁まで
-----------------------------------

エクセルの１つのセルに数字を以下のように入力してみます。

「12345678901234567890」

そうすると、

「123457E+19」と表示されます。

そこで、このセルを右クリックして、「セルの書式設定」を選択し、

表示形式を「数値」に変更して、OKボタンを押します。

そうすると、セルの中の数値は、

「12345678901234500000」

と表示されます。

ここで、最後の５つの数字がすべてゼロになっていることに気づきます。

つまり、「エクセルの有効数字は15桁まで」という限界にぶつかるわけです。

これは、エクセル2003でもエクセル2007でも同様です。

このことは、エクセルは日常生活レベルの計算は問題ないが、宇宙の計算

や複雑な数学計算には向かないということなんです。

残念ながら、VBA上での計算や表示もこの15桁の制約の中で行われており、

VBAでプログラムを書けば、有効数字15桁以上の計算が可能になるというわけ

ではありません。

----------------------------
　アクセサリの電卓で試す
----------------------------

それでは、パソコンに付属している電卓ソフトではどうでしょう。

電卓ソフトを起動して、数字を入力していくと、最大桁数は以下のように

なりました。

「12345678901234567890123456789012」

つまり、電卓では32桁までの有効数字を扱えるようです。

エクセルにしても、電卓にしても有効桁数以上の計算ができないというわけ

ではないのですが、有効数字より大きい桁数で計算を何度も行うと、本当の

計算値とのずれが生じてくることが考えられます。

----------------------------------------------
　有効数字を自分で決められる電卓フリーソフト
----------------------------------------------

どうしても、長い桁の計算で、正確な値が知りたいという方は、

フリーソフト「多倍長電卓LM」を使ってください。

http://www.vector.co.jp/soft/win95/personal/se242555.html

上記のアドレスからダウンロード可能です。

圧縮ファイルを解凍すると、その中に「lm.exe」というプログラムがあるの

でそれをダブルクリックして起動します。

画面が出てきたら、一番上の入力欄に

「123456789012345*123456789012345」などと入力してEnterキーを押すと

下の表示部分に計算結果が表示されます。

「111^9」は111の9乗の計算ができます。

「SQRT(2)」は2のルートを計算できます。

「pi」でパイの値がでます。

有効数字については、メニューの「オプション」の「設定」から自分の

希望する有効桁数を設定可能です。（メモリの許す限りの桁数が可能）

------------------------------------------
　エクセルで有効数字以上の計算をするには
------------------------------------------

それでは、エクセルで有効数字以上の計算をするにはどうすればいいので

しょうか？

答えは、長い数字を短い数字のあつまりとして計算すればいいのです。

単純にするために、以下のような計算を考えてみましょう。

（その１）　１１×１１　の計算を考える。（２桁）

　　　　　　１１
　　　　　×１１
　　　　----------
　　　　　　１１
　　　　　　１１
　　　　----------
　　　　　１２１

（その２）　１１１１×１１の計算を考える。（３桁）

　　　　　　１１１１
　　　　　×　　１１
　　　　------------
　　　　　　１１１１
　　　　　１１１１
　　　　------------
　　　　　１２２２１

（その３）　１１１１×１１１１の計算を考える。（４桁）

　　　　　　１１１１
　　　　　×１１１１
　　　　------------
　　　　　　１１１１
　　　　　１１１１
　　　　１１１１
　　　１１１１
　　----------------
　　　１２３４３２１

（その４）　１１１１１１１１×１１１１１１１１の計算を考える。（８桁）

　　　　　　　　　　　　　　１１１１１１１１
　　　　　　　　　　　　　×１１１１１１１１
　　　　　　　　　　　　--------------------
　　　　　　　　　　　　　　１１１１１１１１
　　　　　　　　　　　　　１１１１１１１１
　　　　　　　　　　　　１１１１１１１１
　　　　　　　　　　　１１１１１１１１
　　　　　　　　　　１１１１１１１１
　　　　　　　　　１１１１１１１１
　　　　　　　　１１１１１１１１
　　　　　　　１１１１１１１１
　　　　　　--------------------------------
　　　　　　　１２３４５６７８７６５４３２１

（その５）　１１１１１１１１１１×１１１１１１１１１１（１０桁）を考える。

　　　　　　　　　　　　　　１１１１１１１１１１
　　　　　　　　　　　　　×１１１１１１１１１１
　　　　　　　　　　　　------------------------
　　　　　　　　　　　　　　１１１１１１１１１１
　　　　　　　　　　　　　１１１１１１１１１１
　　　　　　　　　　　　１１１１１１１１１１
　　　　　　　　　　　１１１１１１１１１１
　　　　　　　　　　１１１１１１１１１１
　　　　　　　　　１１１１１１１１１１
　　　　　　　　１１１１１１１１１１
　　　　　　　１１１１１１１１１１
　　　　　　１１１１１１１１１１
　　　　　１１１１１１１１１１
　　　　----------------------------------------
　　　　　１２３４５６７９００９８７６５４３２１

（その６）４桁ずつに分けて１０桁の掛け算を計算する。

　　　　　※　「・」は４桁どうしの区切りを意味しています。
　　　　　※　その１～３を参照してください。
　　　　　

　　　　　　　　　　　　　　　　　　　　　１１・１１１１・１１１１
　　　　　　　　　　　　　　　　　　　　×　　　　　　　　１１１１
　　　　　　　　　　　　　　　　　　　----------------------------
　　　　　　　　　　　　　　　　　　　　　　　　　１２３・４３２１
　　　　　　　　　　　　　　　　　　　　１２３・４３２１
　　　　　　　　　　　　　　　　　１・２２２１
　　　　　　　　　　　　　----------------------------------------
１つ目の合計　　　　　　　　　　　１・２３４４・４４４４・４３２１
---------------------------------------------------------------------

　　　　　　　　　　　　　　　　　　　　　１１・１１１１・１１１１
　　　　　　　　　　　　　　　　　　　　×　　　１１１１
　　　　　　　　　　　　　　　　　　　----------------------------
　　　　　　　　　　　　　　　　　　　　１２３・４３２１
　　　　　　　　　　　　　　　１２３・４３２１
　　　　　　　　　　　　１・２２２１
　　　　　　　　　--------------------------------------
２つ目の合計　　　　　　１・２３４４・４４４４・４３２１
---------------------------------------------------------------------

　　　　　　　　　　　　　　　　　　　　　１１・１１１１・１１１１
　　　　　　　　　　　　　　　　　　　　×１１
　　　　　　　　　　　　　　　　　　　----------------------------
　　　　　　　　　　　　　　　　　１・２２２１
　　　　　　　　　　　　１・２２２１・
　　　　　　　　　　１２１・
　　　　　　　　　--------------------------------------
３つ目の合計　　　　１２２・２２２２・２２２１
---------------------------------------------------------------------

１つ目の合計　　　　　　　　　　　１・２３４４・４４４４・４３２１
２つ目の合計　　　　　　１・２３４４・４４４４・４３２１
３つ目の合計　　　　１２２・２２２２・２２２１
　　　　　　　　--------------------------------------------------
　　　　　　　　　　１２３・４５６７・９００９・８７６５・４３２１

それでは説明しましょう。

長い数字を計算する場合、４桁ずつに数字を区切ります。

それぞれの４桁どうしが、総当たり戦で掛け算されると考えればいいわけです。

4a4b4c×4d4e4fという計算をする場合、以下の表のようにセルを組みます

┌────┬────┬────┐
　　　 │ 　(4a) │ (4b) │ (4c) │
　 ─┬────┬────┼────┼────┼────┼────┐
│ │ │ 4a*4f │ 4b*4f │ 4c*4f │ (4f)　│
　 ─┼────┼────┼────┼────┼────┼────┤
│ │ 4a*4e │ 4b*4e │ 4c*4e │ │ (4e)　│
　 ─┼────┼────┼────┼────┼────┼────┤
│ 4a*4d │ 4b*4d │ 4c*4d │ │ │ (4d)　│
─┼────┼────┼────┼────┼────┼────┘

　　　　　↓　　　　↓　　　　↓　　　　↓　　　　↓

　　　　足し算　　足し算　　足し算　　足し算　　足し算

　　　　　↓　　　　↓　　　　↓　　　　↓　　　　↓

　　　┌───────────────────────┐　　
　　　│　　４桁を超えたものは、上の桁に繰り上げる　　│
　　　└───────────────────────┘
　　　　　　　　　　　　　　　↓

　　　　　　　　結果は４桁ずつセルの中に表示させる

　　　　　↓　　　　↓　　　　↓　　　　↓　　　　↓
　　 ┌────┬────┬────┬────┬────┐　　
　　 │　　　　│　　　　│　　　　│　　　　│　　　　│
　　└────┴────┴────┴────┴────┘　　

以上が、４桁づつに分けてエクセルで計算する方法です。

------------------------------------------
　４桁を超えたものを上の桁に渡すためには
------------------------------------------

上記の計算で足し算をしたものが４桁より大きくなっていたときに、

５桁目以上を４桁目までと分けて、５桁目以上は上の区画に足し算をする

という操作が必要になります。

このような時に有効な関数について説明します。

例えば、足し算した合計が、「34567」になったとします。

このような場合、以下の計算をします。

　　　　　上の桁に持ってゆく数＝INT(34567/10000)＝3

　　　　　現在の桁に残す数＝MOD(34567/10000)＝4567

※INTは小数点以下を切り捨てる関数です。

※MODは剰余を出す関数なので、割り算のあまりとなります。

-------------------------------------
　最後に別々のセルの値をつなげるには
-------------------------------------

異なるセルどうしの数字をまとめて１つのセルに表示する方法があります。

もちろん、数字としてつなげてしまうと、再び有効数字15桁の呪縛がありま

すので、この場合は文字列としてつなげるということが必要です。

もし、A3,B3,C3,D3,E3のセルに４桁の数字がある場合には、以下のような

関数で文字列を連結します。

=CONCATENATE(A3,B3,C3,D3,E3)

このコンカテナテ関数は、すべて文字列として連結しますので、数字が長く

なっても大丈夫です。