エクセルの計算が合わない? エクセルに強い人程要注意。付録:PHPトリック

「エクセルに強いできるオンナ」から受け取ったエクセルファイルが、どうしても計算が合わないと、「エクセルに強いできる男」から相談があった。

あまり言うと嫌われるのでなおさら言うが(笑)、エクセルに強い系はどちらかというと結構迷惑なことが多い。下手に作り込まれると、全部検算(計算の確認)をやり直さなきゃいけないから。

具体的な解説は、下記の参考記事をご参照いただきたい。

計算誤差 - 仕事に役立つエクセル実践問題集
http://mt-soft.sakura.ne.jp/kyozai/excel_high/210_jissen_chu/60_error.htm


日経PC21 / 小数計算で発生する「誤差」 - 単純な計算の答えが合わない理由
http://pc.nikkeibp.co.jp/pc21/special/gosa/eg1.shtml


ここでの問題は、エクセルの数式「5.1-5.0」の計算結果(0.1)が、0.1以上(>=0.1)かと問われて、当然0.1以上でしょうとは(必ずしも)ならないという件。

例えば、
=IF(5.1-5.0>=0.1, “○”, “”)
という場合。

「5.1-5.0」の結果が「0.1」かそれより大きければ(以上なら)、「○」を返すというIF関数において、返るはずの「○」が返らない。

計算自体はちゃんと「0.1」が返るので、計算結果の数値を表示するだけの表なら問題ないが、このようにIFなどで判定させようとすると問題が生じる(小数の場合に限る)。

コンピュータ内部の2進数処理では、そもそも存在できない数値というものがあり、日常使われる10進数の数字で言えば、割り切れない数字(0.33333...)を「0.3だ」と精神的に割切っているだけなのと似たイメージだ。
かといって、エクセルのセルの設定で、小数点以下30桁(最大)表示に設定した(見た目上精度を上げた)ところで結果は変わらない。

このIF関数の例で言うと、計算結果は同じ「0.1」であっても、「5.2-5.1」なら「○」が返り、数式内の数値によって判定結果が異なる。

/*
「5.2」「5.1」「5.0」「0.1」は、内部的には下記のようになっている。
5.2000000000000001776356839400250464677810668945312500000
5.0999999999999996447286321199499070644378662109375000000
5.0000000000000000000000000000000000000000000000000000000
0.1000000000000000055511151231257827021181583404541015625
*/


エクセルに限らず他の言語(例えばPHP)でも同じだ。
これらの問題はプログラマーは知っている人が多いが、エクセラーは知らない人が多い。ソフトウェアを作る側と、作られた(完成品)ソフトウェアを信頼して使いこなすエクセラーの違いだ。

■付録:PHPトリック
ちなみにこの“挙動”を踏まえた上であえてアルゴリズムに組み込むと、コードが漏れたり覗かれたとしても、一見動くはずのない欠陥コードに見えるが実際は正しく動くという、ちょっとしたトリック(目の錯覚のようなもの)に使える。

ただでさえ目で追うのが面倒な再帰処理を行う複雑なループ構造の中で、

$variable = "手前の計算の結果0.1になる変数";
if(5.1-5.0>=
$variable){
 break;
}else{
 その他の処理。
}


というifによる判定分岐を入れる。
※簡単に書いたが「5.1-5.0」のところも変数で目隠しした方がイイ。

変数$variableには「0.1」が入っているから、コードの読み手はどう見ても「5.1-5.0>=0.1」のif判定は「真」でありbreakしてループを抜け出すと考える。が、このifの判定は「偽」であり、else文に書かれた「その他の処理」を行い、引き続きループ内に留まる。

数字が得意な人ほど、5.1-5.0が0.1にならないはずはないので、このコードは意味をなさない(途中でbreakしてしまって、重要な処理を完了できない)と絶対的に診断してしまう。
もちろん、念のため電卓を使って5.1-5.0を試す慎重派にもちゃんと0.1が返り、客観的証拠をもって、このコードはおかしい(意味が無い)と結論付けられる。

しかし、なぜか作者が意図した通りに動作し、必要なだけループをこなした後、無事に処理を終える。
よって暗号化していないインタプリタ言語のコードが平文で漏れても、一見わからないという「盲点」をついた私が昔から使っているコード秘匿化(時間稼ぎ?)の手法だ。

f0337316_11153241.jpg
光るキューブを投げて、スローシャッターで撮るとこうなる。2013年赤坂ルチアーノショーにて。

2013年の夏、プログラマー達がディスカッションを繰り広げる海外のあるコミュニティにおいて、半分冗談、半分優しさ、僅かに悪戯(それこそ小数点の誤差部分)で、とある質問(悩み)に対し、上記の判定を含めた解決コードを投稿したところ、どこにそんなに隠れていたのかという程の参加者達から、しばらくの間バカ扱いされた(笑)。その時私はマリリン・ボス・サヴァントの気持ちがちょっとだけ理解できた気持ちになった。

1ヶ月くらい経った頃だろうか、このコードの謎(?)を解いたMIT(マサチューセッツ工科大学)の女学生からメッセージをもらった。とても手の込んだ接触方法で、この上なく知的で気の利いたポエミーな文の最後に「自分を過信していたつもりはなかったが落ち込んでしまった」と書かれていた。

君を悲しませるつもりはなかったんだベイビー的な。
危うく恋に落ちるところだったぜベイビー的な。
シャンパン用意して待ってます。ベイビー御中的な。

反応しないはずがない私は、ロキシーと美人すぎるカメラマンナタリーの助けを借りて、更に意味不明なメッセージを送ったとさ。めでたし、めでたし。的な。

/*
余程印象的だったのか、今年初めMITの正規表現クロスワードを解いていたら、ロキシーから「(彼女)覚えてる!」というメッセージが届いた(笑)。
*/

そんな本日のBGMは(久しぶり)、Can You Save Me (“Covert Affairs”テーマソング)
https://www.youtube.com/watch?v=VaP0tnORkJI
※一瞬“夕陽のガンマン”かと思うが違う(笑)。

というわけで、「エクセルの計算が合わない」という悩み相談から、ふと2013年の熱い夏023.gifを思い出してしまったチャーリーであります。

追記:5.1から5.0を引いたらサブウーファーだ(笑)。

チャーリー
JAPAN MENSA会員

AEAJアロマテラピー検定1級
AEAJ認定アロマテラピーアドバイザー
AEAJ認定環境カオリスタ
AEAJ個人正会員
JAMHAメディカルハーブ検定1級
JAMHA認定メディカルハーブコーディネーター


チャーリーのタンブラー(毎日更新、日記・ブックマーク的な)

Commented by ふうぷうようよう at 2016-06-17 07:46 x
こんにちは。はじめまして。
楽しく拝見してます。
私はこういうの、「round」で処理しちゃいますね。
ちなみにメンサ入会しました。愛知ですがいつかお会いできると嬉しいです。
by charlie-ls | 2016-06-16 11:41 | 個人ブログ | Comments(1)

カメラマン☆チャーリーのブログ


by チャーリー
カレンダー
S M T W T F S
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31