エクセルマクロ・VBA達人養成塾 小川です。
今朝、Facebookで「1を998001で割るとおもしろい」という趣旨のリンクシェアを見かけた。
そこで、エクセルで同様の計算をできないか、とやってみた。
Fun with math: Dividing one by 998001 yields a surprising result
Facebookでのリンク元:
1を998001で割るとスゴいことが起きる
どんな結果を得られるのかというと。
000, 001, 002, 003, 004, …, 999, 000, 001, 002, … という循環した数値らしい。
998001で割った結果。
今日は、思いつきで、これと同じ結果をエクセルで出力するマクロを作ってみたのでご紹介。
前回まで続けて書いていた、給与計算のマクロのお話は、またの機会にします ヾ(´ー｀)ﾉ
今日のお話で登場するマクロは、以下からダウンロードできます。
1 / 98001 の実験サンプルデータ
http://www.exvba.com/download/blog/division98001.zip
ということで。
まず、単純な失敗例から。
以下の2つは、どちらも求める結果を出力できない。
エクセルのセルに 1 / 99801 の結果を出力。
イミディエイトウィンドウに出力。
何故かっちゅーと、平たく言えば、エクセルの仕様として、そもそも、こんな長い文字列になるような結果の出力を想定していないから。
テキトーに丸められて、話が終わってしまう。
ということで、テキストファイルに出力してみたいと思う。
「ライブラリの参照設定」とかの面倒な説明をいちいちしたくないから、珍しく、FreeFile関数、Openメソッド、Closeメソッドを使って書いてみた。
以下を試してみる。
さあ、どうだ！これで、このファイルのあるのと同じフォルダに、result0.txtというファイルが作られ、そこには、目指していたものが出力されるはずだ！
しかし、これもダメ。結果はこんな感じ↓。
テキストファイルに、割り算の結果を単純に出力。
…ということで。
自分で、割り算のアルゴリズムを考えなくてはならない。
僕は別に情報工学科の出身とかじゃないので以下が最適なアルゴリズムかどうかは分からないが、まずは書いてみた。
以下、あらかじめ、A列のセルの書式を文字列型にしてから試して欲しい。
（処理をさらに軽くしたいなら、エクセルの設定で、計算方法を、一時的に「自動」に直してからやってみるとよい。何を言っているのか分からない人は、以下に紹介する、Microsoftのサイトを参照。エクセル 2007 やエクセル 2010 でシートの計算結果が更新されない!。Excel 2003までなら、メニューの[ツール] → [オプション] → [計算方法] で「手動」を選択）
tgt = 1
div = 998001
result = “”
residue = tgt
result = result & residue \ div & “.”
residue = residue Mod div & “0”
gyo = 1
Do Until residue = 0 Or gyo > 100 ‘[*]
result = result & residue \ div
residue = residue Mod div & “0”
Range(“A” & gyo).Value = result
gyo = gyo + 1
Loop
End Sub
アルゴリズムとしては、ざっくり言うとこういう感じ↓。
[1] とりあえず、割り算をして、整数部分から求まる商を調べる。ついでに、あとあとのために、その結果の後ろに “.” を追加。
[2] 同様に、余りを調べる
[3] 余りの値を文字列とみなし、その後ろに “0” を追加する
[4] [3] で得られた文字列を割り算して得られた商を、[1]の解の後ろにくっつける
[5] [3] で得られた文字列を割り算して得られた余りを調べる
以下、なんとなく同様。
事務職の人が普段馴染みのない演算子 「\」, 「Mod」は、それぞれ、割り算の商と余りを求められる。
これらを利用する。
もしも割り切れたなら、ループを抜ける。そのほかにも、所定の回数処理を実行したら、ループを抜けることにした。
[*] のところで 回数を100にしたのは、エクセルが固まって悲鳴を上げる人がいるとかわいそうだから。
32767までの値は、いちおう入れる意味があります。（それ以上の値にしても、手元のエクセルでは、結果的にセルには長さ32767桁の文字列までしか入らないです）
うまく実行できたなら、以下のようになる。
以下のようなら、セルの書式が文字列型になっていないので、セルの書式を文字列型にしてからやりなおしてみてください。
「セルの書式を文字列型にする」ってのが何を言わんとしているか分からない人は…。まあ、ここは読み飛ばしてもらっても大丈夫です。
ということで、テキストファイルに出力してみる。
さきほどのマクロを改変。
tgt = 1
div = 998001
result = “”
residue = tgt
result = result & residue \ div & “.”
residue = residue Mod div & “0”
Print #fl, result
cnt = 1
Do Until residue = 0 Or cnt = 100
result = result & residue \ div
residue = residue Mod div & “0”
Print #fl, result
cnt = cnt + 1
Loop
Close #fl
End Sub
得られたテキストファイル「result1.txt」を開いてみると、以下のとおり。
計算の都度結果を出力すると、桁数が多くなると洒落にならないくらい処理が重たくなる。
ということで、今回は、100000回（十万回）計算をした結果だけを出力することにした。
それが以下。
tgt = 1
div = 998001
result = “”
residue = tgt
result = result & residue \ div & “.”
residue = residue Mod div & “0”
‘ Print #fl, result
cnt = 1
Do Until residue = 0 Or cnt = 100000
result = result & residue \ div
residue = residue Mod div & “0”
‘ Print #fl, result
cnt = cnt + 1
Loop
Print #fl, result
Close #fl
End Sub
あと、最後にいろいろおまけ。
まずは、小数点以下の部分だけ、3桁ごとに結果を取ってきて出力してみた。
tgt = 1
div = 998001
result = “”
residue = tgt
‘ result = result & residue \ div & “.”
residue = residue Mod div & “0”
‘ Print #fl, result
cnt = 1
Do Until residue = 0 Or cnt = 10000
result = result & residue \ div
residue = residue Mod div & “0”
If Len(result) = 3 Then
Print #fl, result
result = “”
End If
cnt = cnt + 1
Loop
‘ Print #fl, result
Close #fl
End Sub
以下ではさらに、直前の値 + 1 の関係になっているかどうかもついでにチェックしてみた。
tgt = 1
div = 998001
result = “”
residue = tgt
chokuzen = -1
‘ result = result & residue \ div & “.”
residue = residue Mod div & “0”
‘ Print #fl, result
cnt = 1
Do Until residue = 0 Or cnt = 10000
result = result & residue \ div
residue = residue Mod div & “0”
If Len(result) = 3 Then
Print #fl, result
If chokuzen >= 0 Then
If result = “000” Then
result = CLng(result) + 1000
End If
If Not CLng(result) = chokuzen + 1 Then
Debug.Print “failure: ” & cnt
End If
End If
chokuzen = CLng(result) Mod 1000
result = “”
End If
cnt = cnt + 1
Loop
‘ Print #fl, result
Close #fl
End Sub
それで、僕もやってみて分かったのだが、例えば、上記のマクロだと、以下の出力結果を得る。
このそれぞれを見て気づいたのだが、997のあと、999が出力されている。998がすっとばされている。
本家にも、同様の指摘をするコメントを見つけた。
以下の記事の、「Goddamn」と、「John-Michael Glenn」の指摘。
http://www.exvba.com/vb/index.php
まー、数学のことはよく分からないが、とりあえず、自分なりにいろいろ調べて、空いている時間の範囲で納得できるところまで突っ込めたので満足 ヾ(´ー｀)ﾉ
あと、最後に。
あまりエクセル得意でない人へ。
上記のマクロをいろいろ試していて、エクセルの動きが重くなって困ってきたなら、以下のマクロを実行してみてください。
たぶん、一気に動作が軽くなるでしょう。
今日のお話で登場するマクロは、以下からダウンロードできます。
1 / 98001 の実験サンプルデータ
http://www.exvba.com/download/blog/division98001.zip
お知らせ:
人気のセミナー2つを、久しぶりに開催します。受講受付開始しました。ふるってご参加ください☆
「親指シフト達人養成塾」 （あと3名）
「エクセルデータ分析7つの上級技」 （あと1名）
「エクセルデータ分析」は、ほとんどリピータの方だけで埋まってしまいまして、早くも、残席1つだけとなりました。
親指シフトも、あと3名くらいです。
●無料PDFレポート「誰もが知っているWindowsの、誰も知らない12の技」
http://www.exvba.com/freereport/index.php
●法人研修のお問い合わせはこちら
https://sv86.wadax.ne.jp/~exvba-com/closed/toiawase_houjin.php
●ジーザス小川の個人サイト「こねこねのさいと」へはこちらから
http://www.exvba.com/
