「1から10億まで、全部足してください」
人間に頼んだら、まず関係が悪くなりそうなお願いです。
でも、Excelなら文句を言わずに計算してくれるはず。
今回は、ガウスの公式を使わず、Excel VBAで数字を1つずつ足してみました。
天才のひらめきに、パソコンの体力で挑む第2弾です。
……ところが、体力勝負の前に「数字を入れる器」の問題が出てきました。
今回の動画はこちら
計算するのは、こちらです。
1＋2＋3＋4＋……＋1,000,000,000
ガウスの公式を使えば、
1から始まる場合は なので、よく見る
に簡略化できる
1,000,000,000 × 1,000,000,001 ÷ 2
で求められます。
でも、今回はあえて封印。
近道を知っているのに、徒歩で全国を回るような実験です。歩くのはExcelですが。
VBAは、本当に1つずつ足しています
計算の中心は、たったこれだけです。
total = 0
For i = startNum To endNum
total = total + i
Next i開始値から終了値まで、順番に足し続けます。
1から10億を指定すれば、足し算を10億回繰り返します。10億個のセルに数字を書き込むのではなく、VBAの変数の中で計算しています。
今回のマクロで使うセルは、次の4つです。
| セル | 内容 |
|---|---|
| C4 | 開始値 |
| D4 | 終了値 |
| E4 | 合計の出力先 |
| F4 | 処理時間の出力先（秒） |
数値が入力されているか、開始値が終了値を超えていないかも確認します。小数が入力された場合は、Fixで小数部分を切り捨てます。
足すだけのマクロでも、意外と準備があります。
Excelも「何を足せばいいのか」までは、空気を読んでくれません。
途中で登場した「オーバーフロー」
今回、対応することになったのが、オーバーフローというエラーです。
普段の作業では、あまりお目にかからないかもしれません。
簡単に言うと、変数の型で扱える範囲を、計算結果などが超えてしまうことです。Microsoftの説明でも、代入・計算・型変換の結果が、その型に収まらない場合に発生するとされています。Microsoft公式解説
コップに入る量を超えて水を注げば、あふれます。
「パソコンなんだから、もう少し頑張ってよ」と言っても、コップは大きくなりません。
例えばVBAのLong型が扱える整数の上限は、2,147,483,647です。
10億という数字そのものは収まりますが、1から10億まで足した合計は、はるかに大きくなります。
最後に足す数字と、全部足した答えは、桁がまったく違う。
ここが落とし穴です。
対策版ではDouble型を使用
今回の対策版では、開始値・終了値・ループ用の変数・合計を、Double型にしています。
Dim startNum As Double
Dim endNum As Double
Dim i As Double
Dim total As Doubleセルから読み込む際も、CDblでDouble型に変換しています。
startNum = CDbl(ws.Range("C4").Value)
endNum = CDbl(ws.Range("D4").Value)Double型は、非常に大きな数まで扱えます。
これで今回の合計を扱うための「器」は確保できました。
ただし、ここで話が終わらないのが、今回の実験です。
大きな数を扱えることと、その数のすべての桁を正確に保持できることは、別の話でした。
約13.2秒で完走。でも、答えが違う？
動画の画面では、処理時間は約13.2秒。
10億回も足して、この時間です。人間なら、途中で「今いくつだったっけ？」となった時点で退職届を書きたくなります。
ところが、表示された答えを比べると、こうなっています。
| 比較 | 数値 |
|---|---|
| VBAで順番に足した結果の表示 | 500,000,000,067,109,000 |
| 数学的な正解 | 500,000,000,500,000,000 |
似ています。
遠目には、かなり似ています。
でも、計算結果に「だいたい同じ顔ですね」は通用しません。
※処理時間は動画の実行環境での測定値です。PCや実行時の状態によって変わります。
次の相手は「丸め誤差」
Double型は、数値を限られた桁数で保持する浮動小数点型です。
大きな数になってくると、すべての整数を1刻みで正確に表せるわけではありません。今回のコードのように大きな合計へ加算を繰り返すと、計算途中の丸めが積み重なり、数学的な正解との差につながります。
Excelのセルでも、数値の精度は15桁に制限されます。Microsoftも、浮動小数点計算では丸めなどによって結果に誤差が生じることを説明しています。数値精度についての公式解説
今回のポイントは、次の違いです。
- オーバーフロー：扱える数値の範囲を超えてしまう。
- 丸め誤差：範囲内でも、細かい桁を正確に保持しきれない。
器を大きくしても、目盛りまで無限に細かくなるわけではない、ということです。
なお、今回のコードでは、結果をカンマ付きの整数表示にしています。
ws.Range("E4").NumberFormat = "#,##0"これは見た目を整える設定です。計算途中で生じた誤差を直す機能ではありません。
ネクタイを締めても、計算ミスは消えません。私にも耳の痛い話です。
処理時間は、足し算の前後で測定
時間測定には、Timerを使っています。
計算前の時刻を記録し、ループ終了後との差を求める仕組みです。
そのため、主に測っているのは足し算を繰り返す処理の時間です。結果をセルへ書き出す時間や、完了メッセージを表示する時間は含みません。
コードには、午前0時をまたいで差がマイナスになった場合の補正も入れています。
10億回の足し算に加えて、日付変更にも備える。妙なところで用意周到です。
また、時間を小数点以下6桁まで表示していても、それだけで100万分の1秒単位の測定精度があるわけではありません。
ここでも、表示の細かさと、測定の細かさは別です。
やはり、ガウスは強かった
今回の実験では、Excel VBAが10億回の足し算を短時間で実行できることが分かりました。
同時に、数値の型や精度を考えずに計算を大きくすると、エラーや答えのずれに出会うことも分かりました。
ガウスの公式なら、10億回の繰り返しそのものを省けます。もちろん、プログラムで公式を使う場合も、数値の型や計算途中の精度には配慮が必要です。
パソコンの速さも立派ですが、計算する回数を減らす工夫は、やはり強い。
今回のExcelは、言われたとおりに10億回頑張りました。
一方のガウスは、そもそも10億回も頑張らない方法を知っていました。
Excel、お疲れさま。
ガウス先生、次回はもう少し手加減してください。
コメント