#8 データベースを変形させる②

前回の#7でVLOOKUP関数を使って、本数字①～⑦で入力されている当せん番号データベースのデータを1～37の数字に振り分ける作業を行いましたが、VLOOKUP関数だけではまだ完成していません。

なぜなら、VLOOKUP関数で第○○回をキーにして、本数字①の番号を持ってくる作業を1～37までのセルにすべてコピーします。

表①

すると、1～37のセルにはすべて3が入力されます。次に本数字②～⑦はどうするのか・・・と言う問題も出てきますね。
前回の冒頭で当せん番号になった数字には1を入力。を実行するには、もう一つ条件で分岐できる関数を組合わせる必要が出てきます。

察しの言い方はピンと来るでしょうか。IF関数です。私の回りにはIF関数で躓いている人が多いですが、シンプルに構造を見ることができれば難しくありません。

IF関数の基本の型は　=IF(①,②,③)

IF関数は①(条件式),②(正),③(誤)をカンマで区切って並べた型になりますが、日本語の説明で簡単に理解できます。①の条件を満たす場合は②、そうでない場合は③と聞けば簡単に感じませんか？$マークは絶対参照と言ってセルを固定する記号です。これはまた別の回で説明します。

表②

表②を見ていただくと、1～37までの数字別に作るデータベースのC562列に第560回の数字が入っていて、そこから2列隣のE列からAO列まで1～37までの数字が続きますが、本数字①が出た番号に1を入力するには以下の数式で実現できます。

=IF(VLOOKUP($C562,Sheet1!$B:$J,3,0)=E$2,1,0)  ←本数字①
①の条件式のところにVLOOKUP関数を使っていますが、本数字①の数字がB列からスタートしてE列にあるので3です。本数字②の場合はF列なので4。同様に本数字⑦のJ列の9まで変えてあげれば問題ありません。

1～37の数字は本数字①～⑦まで可能性があるので、関数をつなげていきます。一つのセルの中で関数をつなげるには関数でつなげる場合と記号でつなげる場合がありますが、記号だけ覚えればいいと思います。この記号のことを演算子と言います。
よく使うのが＆で関数だけでなく、セルに入った文字をつなげることもできます。

=IF(VLOOKUP($C562,Sheet1!$B:$J,3,0)=E$2,1,0)&IF(VLOOKUP($C562,Sheet1!$B:$J,4,0)=E$2,1,0)&IF(VLOOKUP($C562,Sheet1!$B:$J,5,0)=E$2,1,0)&IF(VLOOKUP($C562,Sheet1!$B:$J,6,0)=E$2,1,0)&IF(VLOOKUP($C562,Sheet1!$B:$J,7,0)=E$2,1,0)&IF(VLOOKUP($C562,Sheet1!$B:$J,8,0)=E$2,1,0)&IF(VLOOKUP($C562,Sheet1!$B:$J,9,0)=E$2,1,0)

と&で繋げると0000000と0が並んでしまいます。これを解決するために、＆でなく+にすると、0をいくつ足しても0になり当せん番号になった数字は1
が返されるので1になります。

ここまで、VLOOKUP関数とIF関数を使ってデータベースのレイアウト変更を行いましたが、Excel関数で行う方法一つではありません。他にもありますが、私がいつも考えているのはなるべくシンプルで簡潔に済ませること。
先ほどの本数字①～⑦までを+でつなげたものは正しい結果を導くことができましたが、簡潔で美しいかと言われるとそうではありません。

今回の場合は、COUNTIFS関数を使えばシンプルに短い数式にできます。
=COUNTIFS(Sheet1!$G562:$M562,Sheet3!E$2)

以前紹介したCOUNT関数とIF関数が組み合わさったもので、(複数の)条件を満たしているものを数えてくれる関数です。
COUNTIFS関数を使うと当せん番号は7種類の数字が選ばれるので、該当した場合は自然に1となります。

なぜ、簡潔な関数がいいのかは見た目が美しいだけではありません。もっと大事なことは後から見た人、自分かも知れませんがその時に分かりやすい方がいいです。自身が作ったファイルの全ての関数を覚えていられるのかと言ったら否です。後から修正する必要があった場合に調べるのが大変になってしまいます。自分のことなら自業自得ですが、他の人に見せる場合はそうはいかないですね。

以上で、データベースの形を変えて変形させるのは終了です。
次回から、新しいデータベースを使ってロト7当せん番号の傾向を調べていきます。