1000本の中に毒入りワインが1本 エクセルで2進数
中学生の息子が、珍しく、一生懸命、勉強でもしているのかと、覗いてみると、どこで拾ってきたのか、以下のような問題をやっていました。
一滴でも飲むと20時間後に死ぬ毒が、1000本のワインのうち1本にだけ入っている。奴隷に飲ませて、24時間以内にどれが毒入りか調べるとき、最低、奴隷は何人必要か?
奴隷に飲ませた場合、「生」か「死」かなんで、2進数に置き換える問題なんじゃないかと直感的に思いながら、息子はどこで拾って来た問題なんだろうと、調べてみると、どうやら、元はマイクロソフトの入社面接の際に、出された問題らしいことがネット上に出ていました。直感的に2進数だとは思っても、実際に、論理だてた答えを出そうと思えば、やはり、僕には暫く考える時間が必要です。それを面接で聞かれて、パッと答えられるって、やはり、マイクロソフトに入社する人って、頭がいいんでしょうね~。
この問題の詳しい解説は、検索してもらえれば、あちこちに出ていますけど、簡単に言ってしまえば、ワインを飲む奴隷の生死のパターンを1000通り作れる人数を考えれば良いことになります。生を0、死を1として2進数に当てはめると、2の10乗が1024になるので、10桁の2進数、つまり10人の奴隷を用意すればいいことになります。
そして、1本目:1、2本目:10、3本目:11、4本目:100、5本目:101・・・・というように、ワインに2進数で1から1000までナンバリングしていきます。実際には以下のような感じです。
1本目:0000000001
2本目:0000000010
3本目:0000000011
4本目:0000000100
5本目:0000000101
・・・・・・・・・・・・・
996本目:1111100100
997本目:1111100101
998本目:1111100110
999本目:1111100111
1000本目:1111101000
そして奴隷をA、B、C、D、E、F、G、H、I、Jと10人用意し、Aはナンバリングした2進数の10桁目、Bは9桁目、Cは8桁目、・・・・Jは1桁目と、対応させ、それぞれの桁に1が入っているワインを全て飲んでもらいます。
もし、20時間経って、HとJだけが死んだなら、
A B C D E F G H I J
0 0 0 0 0 0 0 1 0 1
と、それぞれに生の0と死の1を対応させ、2進数の0000000101を10進数に変換してやれば、何本目かが分かります。この場合は、見たまんまですけど、5本目が毒入りですね。仮にA、B、C、D、E、Gが死んだなら1111101000で1000本目が毒入りということになります。
と、まあ、ここまでの解説は、検索すれば、色々なページに様々な表現で書かれています。そして、問題の解説とすれば、これで終わりです。
ところが、僕はアホなもんですから、ここからさらに実際に実験するところを想像してしまいます。そうすると、例えば、一桁目を担当する奴隷Jは、500本ものワインを飲む必要があり、大変です。そうなると、一滴でも死ぬ毒ということですから、予め、それぞれから一滴ずつワインを採り、1つのグラスに混ぜたものを作っておく必要があるな!とか、そもそも、奴隷を使って実験をするなどという酷い事をしなくても、20時間後に死ぬまで分かってる毒だから、毒物が特定出来ている可能性があります。それなら、それを検出する試薬があるだろうとか、最悪でもネズミとかを使った動物実験にすればいいとか、アホな事を、とりとめもなく考えてしまいます。(笑)
さらに、1~1000本目のワインへ2進数でナンバリングするラベルを予め作っておかないといけないわけで、1000本ともなると、間違えやすいから、エクセルで2進数の1~1000までのデータを作って、それをワードの差し込み印刷で、ラベル印刷やな!とか、それぞれのワインを混ぜる際のチェック表も必要やろ!とか、バカバカしいことを想像してしまいます。(^^;
そこで、とにかく必要になる1~1000までの2進数のエクセルの元データを作ることを考えてみました。(^^;
何はともあれ、エクセルに10進数を2進数に変換する関数がないかと調べてみると、これが、ちゃんと、あるんですよね!!
DEC2BIN(数値,桁数)
上の図だと、B2セルには「=DEC2BIN(A1,10)」と入力してあります。A1セルの10進数の数字を10桁表記の2進数にして返すという意味です。
ということで、A列に数字を1~1000まで入力し、B列の数式をフィルハンドルを引っぱって、下へとコピーしていきます。ところが・・・
上の図のように512からエラーが出てしまいます。どうやら、DEC2BINは10進数の512つまり2の9乗からは、対応できない関数のようです。
仕方がないので、1~1000までの数字を512以上と512未満に分けて考えます。2進数で言うと、10桁目と9桁目までに分けるって感じです。
512以上の部分は「DEC2BIN(INT(A1/2^9),1)」とします。
INT関数は整数部分を返す関数です。つまりINT(A1/2^9)は、A1を2の9乗つまり512で割った整数を返しますから、512以上では1、512未満では0が返ることになります。この部分は2進数では10桁目だけでいいので、DEC2BIN(INT(A1/2^9),1)と、DEC2BIN関数で桁数1の出力にしました。
512未満の部分は「DEC2BIN(MOD(A1,2^9),9)」とします。
MOD関数は割った余りを返す関数です。つまりMOD(A1,2^9)は、A1をを2の9乗つまり512で割った余りを返しますから、512未満では割り切れないので、A1の値そのものが返ります。512以上ではA1の値から512を引いた値が返ることになります。この部分は2進数では9桁の表示になるので、DEC2BIN(MOD(A1,2^9),9)と、DEC2BIN関数で桁数9の出力にしました。
ここで、上記の2つを連結すると
=DEC2BIN(INT(A1/2^9),1)&DEC2BIN(MOD(A1,2^9),9)
となります。
これをB1セルに入力して、B1000セルまでフィルハンドルでコピーすれば、1~1000の10進数に対応する2進数の出来上がりです。実際にやってみると、下図のように、512以上、512未満ともに、正常に出力されていることが分かります。
これで、とりあえず2進数の元データができるので、これを元にしてラベルやチェック表を作ればいいってことになりますね。
あとは、実験で求められた2進数を10進数に変換すれば、何本目に毒が入っていたか分かるわけですが、2進数から10進数の変換は、検索すれば、それを計算してくれるサイトがありますので、それを利用するって感じですかね。(^^;
でも、敢えて、あくまでも、手元のエクセルで計算するとすれば、まず2進数を10進数に変換するロジックを考えます。例えば、1000000111を10進数に変換する式は以下のようになります。
1000000111=1(10桁目)×29+0(9桁目)×28+0(8桁目)×27+0(7桁目)×26+0(6桁目)×25+0(5桁目)×24+0(4桁目)×23+1(3桁目)×22+1(2桁目)×21+1(1桁目)×20
この場合、変換した値は519ですね。
次にエクセルに2進数を10進数に変換する関数がないか探してみると、BIN2DECという関数がありました。
しかし、これも、使ってみると、DEC2BINと同じで、2の9乗つまり512を境に動作しないものであることが分かりました。つまり2進数でいうと9桁目までしか使えないということです。
ということで、2進数の9桁目までと10桁目を分けて考えます。
仮に、E1セルに、求められた2進数を入力し、F1セルに変換した10進数を出力するとします。
2進数の9桁目までは
BIN2DEC(RIGHT(E1,9))
RIGHT(E1,9)で、E1セルの右から9番目の数字までを取り出してBIN2DECで囲みます。
2進数の10桁目は
LEFT(E4,1)*2^9
左から1番目の数字を取り出して、2の9乗を掛けます。上の1000000111を10進数に変換する式のところで書いた10桁目だけの計算ってことですね。
この2つを足したものが、F1セルに入力する数式で、今回の10桁の2進数の10進数への変換式になります。
=LEFT(E1,1)*2^9+BIN2DEC(RIGHT(E1,9))
と、まあ、こんなふうに、しょうもない事を想像して、遊んでしまいましたが、息子のお蔭で、少し、勉強になったのかも・・・。(^^;