【実況中】

実況!ロトロクチャレンジ【②桁範囲と発生率の分析】~エクセルで予想ツールを作る~

ロトロクチャレンジ;ツール編アイキャッチ
記事内に商品プロモーションを含む場合があります

※ここでは、ロト6(ロトシックス)を

『ロトロク』と呼んでます。

ミニロト17年目。一週たりとも予想を休んでいない shirono-j🤠です。

このページでは第二段階の『分析ファイル』を作成。第一段階【①データベース】の続きとなる回だ。

主に「桁」ごとの発生形態と、「No」ごとの発生率分布を分析。ファイル名は『10.桁・発生率』とした。

作成するエクセルの基本書式は以下。

  • フォント=メイリオ
  • フォントサイズ=10p(見出しは 11p)

作成や編集を行う際は、そこまでに作成したEXCELファイルを全て同時に開いておく必要がある。

DT10シートを作る

まずは、このファイル内用のデータテーブルシートを作成する。この後作成する分析用シート達のためのデータベースとなる。

表題(セルA1)には【桁別データテーブル】と見出しを付け、シート名は「DT10」とした。

テーブルの準備

まずは『00.DB6』ファイルから「DBシート」をコピーする。これを改造するのが早くて楽だ。

コピーしたら、シート名と表題(セルA1)を書き換え、「I~V列」をいったん削除してから、テーブルを「O列」まで広げる。

これが『10.桁・発生率』ファイルの土台となる。

■ データの移植

※文字まで読めるきれいな画像はYouTubeへ移動する必要あり

ここでは主に、テータテーブルのデータを『00.DB6』へ同期させる作業となる。

① 現在の回別を示すセルを同期

[A2]へ数式を入力。

=[00.DB6.xlsx]DB!$A$2
② 回別の行を同期

[A4]へ数式を入力。

=[00.DB6.xlsx]DB!A4
③ 抽選日の行を同期

[B4]へ数式を入力。

=IF([00.DB6.xlsx]DB!B4<>"",[00.DB6.xlsx]DB!B4,"")
④ 1桁目の行を同期

[C4]へ数式を入力。

=IF([00.DB6.xlsx]DB!C4<>"",[00.DB6.xlsx]DB!C4,"")
② 2桁目の行を同期

[D4]へ数式を入力。

=IF([00.DB6.xlsx]DB!D4<>"",[00.DB6.xlsx]DB!D4,"")
② 3桁目の行を同期

[E4]へ数式を入力。

=IF([00.DB6.xlsx]DB!E4<>"",[00.DB6.xlsx]DB!E4,"")
② 4桁目の行を同期

[F4]へ数式を入力。

=IF([00.DB6.xlsx]DB!F4<>"",[00.DB6.xlsx]DB!F4,"")
② 5桁目の行を同期

[G4]へ数式を入力。

=IF([00.DB6.xlsx]DB!G4<>"",[00.DB6.xlsx]DB!G4,"")
② 6桁目の行を同期

[H4]へ数式を入力。

=IF([00.DB6.xlsx]DB!H4<>"",[00.DB6.xlsx]DB!H4,"")

■ 表頭(ヘッダー)を整える

表頭(ヘッダー)のセル [I3] から [O3] へ以下のヘッダー名を入力する。ここの書式は自分の好みで「折り返して全体を表示する」へ統一した。

  • [I3]=範囲内
  • [J3]=1桁目(%)
  • [K3]=2桁目(%)
  • [L3]=3桁目(%)
  • [M3]=4桁目(%)
  • [N3]=5桁目(%)
  • [O3]=6桁目(%)

分析作業

ここから分析作業に入る。

「各」ごとで異なる発生Noの偏りを範囲として割り出し、また、「各No」ごとの発生率を回別毎に算出、分布を調べて統計を取る。

「各」と、「各No」のデータを使って、クロスチェック表的なイメージで傾向をつかむ作業だ。

■「過去発生率」をプロットする

※文字まで読めるきれいな画像はYouTubeへ移動する必要あり

ここで「過去発生率」と呼ぶのは、各回別時点から見れば「リアルタイム」の発生率履歴である。

言葉で言うより、これから行う計算の数式を見れば一目瞭然だろう。十分に母集団としてのデータ量を確保したいので、「回別=1000以降」で統計を取る。

① 1桁目にでた数字の発生率を算出

[J1003]へ数式を入力。

=IF($B1003="","",COUNTIF($C$4:$H1002,[@1桁目])*100/($A1002))
② 2桁目にでた数字の発生率を算出

[K1003]へ数式を入力。

=IF($B1003="","",COUNTIF($C$4:$H1002,[@2桁目])*100/($A1002))
③ 3桁目にでた数字の発生率を算出

[L1003]へ数式を入力。

=IF($B1003="","",COUNTIF($C$4:$H1002,[@3桁目])*100/($A1002))
④ 4桁目にでた数字の発生率を算出

[M1003]へ数式を入力。

=IF($B1003="","",COUNTIF($C$4:$H1002,[@4桁目])*100/($A1002))
⑤ 5桁目にでた数字の発生率を算出

[N1003]へ数式を入力。

=IF($B1003="","",COUNTIF($C$4:$H1002,[@5桁目])*100/($A1002))
⑥ 6桁目にでた数字の発生率を算出

[O1003]へ数式を入力。

=IF($B1003="","",COUNTIF($C$4:$H1002,[@6桁目])*100/($A1002))
⑦ セル範囲 [J4]~[O1002] の数式を消去する

上記①~⑥の過程で、EXCELのスピル機能により「1003行より上」にも数式が自動入力されたはずだ。

自分は これを消去して「1003行以降」のデータ、つまり「回別=1000以降」のデータだけにしておく考えだ。

あと、

書式設定の表示形式で、小数点以下のケタを揃えておく。見やすくするのが目的だ。自分の場合は小数第三位までにした。ここまで済ませた時点で、各列を好みの列幅へ整えるのが良いだろう。

⑧ いったん、次工程へ移る

ここまでで、いったん次の工程「R桁シートを作る」へ進む。

R桁シート完成後の作業

ここから先は、R桁シート完成後の作業となる。

いったん「R桁シートを作る」へ進み、あとでまた戻ってくる。

■ 各桁数字の範囲判定

スポンサーリンク

R桁シートを作る

桁別に発生する数字は、ある程度限定されてくる。

その発生範囲(RANGE)を予測するためのツールを作成する。ミニロト予想で培った機能だ。(「桁」ごとの1等出現パターン

統計で勉強した「Zスコア」を使って、最新データに合わせた「最適な範囲設定」を自動算出するように調整するのだ。

表題(セルA1)には【各桁数字の範囲分析】と見出しを付け、シート名は「R桁」とした。

テーブル枠を作る

※文字まで読めるきれいな画像はYouTubeへ移動する必要あり

先にテーブルを作成する。

数式を含めた構想は先に検討しておいたので、解りやすく「箱」から準備した。

■ 見出しを準備する

① 範囲設定の算出枠
  • [B2]へ「範囲設定」と入力
  • [B3]へ「下」と入力
  • [B4]へ「上」と入力
  • [B5]へ「範囲幅」と入力
  • [B6]へ「該当数」と入力
  • [B7]へ「該当率」と入力
② 個別分析の算出枠
  • [J2]へ「個別分析」と入力
  • [J3]へ「平均値」と入力
  • [J4]へ「中央値」と入力
  • [J5]へ「標準偏差」と入力
  • [J6]へ「Zスコア上限」と入力
  • [J7]へ「Zスコア下限」と入力
③ 全体分析の算出枠
  • [R2]へ「全体分析」と入力
  •  [S2]へ「発生回数」と入力
  •  [T2]へ「発生率」と入力
  • [R3]へ「平均」と入力
  • [R4]へ「最大」と入力
  • [R5]へ「最小」と入力
  • [R6]へ「すべての「桁」が 範囲設定内=◎」と入力
  • [R7]へ「どこかの「桁」が 範囲設定外」と入力
④ データ算出枠
  • [B8]へ「No」と入力
  • [C8]へ「1桁目」と入力
  • [D8]へ「2桁目」と入力
  • [E8]へ「3桁目」と入力
  • [F8]へ「4桁目」と入力
  • [G8]へ「5桁目」と入力
  • [H8]へ「6桁目」と入力
  • [I8]へ「計」と入力
  • [J8]へ「発生率(%)」と入力
  • [K8]へ「1桁目」と入力
  • [L8]へ「2桁目」と入力
  • [M8]へ「3桁目」と入力
  • [N8]へ「4桁目」と入力
  • [O8]へ「5桁目」と入力
  • [P8]へ「6桁目」と入力
⑤ No枠

[B9]から[B51]へ、Noの「1」から「43」を入力する。

⑥ 書式を整える

見出しの色や、罫線を整える。動画はあくまで参考。自分の好みに染め上げるのが楽しい。次で数式を入力したあとでは、条件付き書式も投入する。

ここで、

【すべての「桁」が 範囲設定内=◎】というのが、「各桁数字の大きさ」が相応なパターンの発生率のことで、

【どこかの「桁」が 範囲設定外】というのが、「各桁数字の大きさ」が片寄るパターンの発生率のことである。

数式を入れる

分析データを配置するための数式を入力していく。

『00.DB6』から生データを拾う仕組みなので、DBシートさえきちんと更新していれば 分析値はいつも新鮮だ。

数式ペーストの時、テーブルのデータ列全体を指示して「Ctrl+Enter」すると楽

■「No ⇆ 桁」の発生回数

※文字まで読めるきれいな画像はYouTubeへ移動する必要あり

基本的には、各桁ごとにNo1から43までの枠を押さえた上で数式を入力し、「Ctrl」と一緒に「Enter」を押す。

① 1桁目をカウント

[C9]へ数式を入力。

=COUNTIF(テーブル1[1桁目],$B9)
② 2桁目をカウント

[D9]へ数式を入力。

=COUNTIF(テーブル1[2桁目],$B9)
③ 3桁目をカウント

[E9]へ数式を入力。

=COUNTIF(テーブル1[3桁目],$B9)
④ 4桁目をカウント

[F9]へ数式を入力。

=COUNTIF(テーブル1[4桁目],$B9)
⑤ 5桁目をカウント

[G9]へ数式を入力。

=COUNTIF(テーブル1[5桁目],$B9)
⑥ 6桁目をカウント

[H9]へ数式を入力。

=COUNTIF(テーブル1[6桁目],$B9)
⑦ ゼロを見えないようにする

これは作る人の自由であるが、セルの書式設定で「表示形式」を調整。ゼロを見えなくして見た目をスッキリさせる。

「ユーザー定義」を使って、以下の種類に設定した。

##0;-#,##0;""
⑧「計」を算出

[I9]へ数式を入力。

=SUM(C9:H9)
⑨「発生率(%)」を算出

[J9]へ数式を入力。書式は小数点第三位までにしておくのが良いと思う。

=I9*100/'DT10'!$A$2

■「No ⇆ 桁」の個別分析

Zスコアを計算するためのパラメータだ。このおかげで自動の範囲設定ができる。

Zスコアに関してはコチラをチェック→Zスコア(Z値)

① 1桁目の平均、中央、標準偏差を算出

[K3]へ数式を入力。

=AVERAGE(テーブル1[1桁目])

[K4]へ数式を入力。

=MEDIAN(テーブル1[1桁目])

[K5]へ数式を入力。

=STDEV.P(テーブル1[1桁目])
② 2桁目の平均、中央、標準偏差を算出

[L3]へ数式を入力。

=AVERAGE(テーブル1[2桁目])

[L4]へ数式を入力。

=MEDIAN(テーブル1[2桁目])

[L5]へ数式を入力。

=STDEV.P(テーブル1[2桁目])
③ 3桁目の平均、中央、標準偏差を算出

[M3]へ数式を入力。

=AVERAGE(テーブル1[3桁目])

[M4]へ数式を入力。

=MEDIAN(テーブル1[3桁目])

[M5]へ数式を入力。

=STDEV.P(テーブル1[3桁目])
④ 4桁目の平均、中央、標準偏差を算出

[N3]へ数式を入力。

=AVERAGE(テーブル1[4桁目])

[N4]へ数式を入力。

=MEDIAN(テーブル1[4桁目])

[N5]へ数式を入力。

=STDEV.P(テーブル1[4桁目])
⑤ 5桁目の平均、中央、標準偏差を算出

[O3]へ数式を入力。

=AVERAGE(テーブル1[5桁目])

[O4]へ数式を入力。

=MEDIAN(テーブル1[5桁目])

[O5]へ数式を入力。

=STDEV.P(テーブル1[5桁目])
⑥ 6桁目の平均、中央、標準偏差を算出

[P3]へ数式を入力。

=AVERAGE(テーブル1[6桁目])

[P4]へ数式を入力。

=MEDIAN(テーブル1[6桁目])

[P5]へ数式を入力。

=STDEV.P(テーブル1[6桁目])

■「No ⇆ 桁」のZスコア

ここもでも、必要な枠を押さえての「Ctrl+Enter」で対応する。フィルタリング後の状態で使ったときに、隠れているセルには影響を及ぼさないところが大好きだ。(テーブル機能上でだけかもしれんが)

① 1桁目のZスコアを算出

[K9]へ数式を入力。

=(($B9-K$3)/K$5)
② 2桁目のZスコアを算出

[L9]へ数式を入力。

=(($B9-L$3)/L$5)
③ 3桁目のZスコアを算出

[M9]へ数式を入力。

=(($B9-M$3)/M$5)
④ 4桁目のZスコアを算出

[N9]へ数式を入力。

=(($B9-N$3)/N$5)
⑤ 5桁目のZスコアを算出

[O9]へ数式を入力。

=(($B9-O$3)/O$5)
⑥ 6桁目のZスコアを算出

[P9]へ数式を入力。

=(($B9-P$3)/P$5)
⑦ Zスコアの上限値と下限値を設定する

[K7]へ数式を入力。

=K9

[P6]へ数式を入力。

=P51

■ 各桁数字の範囲設定

■ いったん「DT10シート」へ戻る

上記で設定した「■ 各桁数字の設定範囲」を使用して、

DT10シートへ「■ 各桁数字の範囲判定」の結果を出力する。

■ 全体分析

pdシートを作る

当せんNoの中で、「どの発生率のNoが」「どのように出現するか」を予想するための分析ツールを準備した。

回別ごとにプロットされた「発生率」を分析し、支配的な構成を割り出して予想を組むための一要素として利用する。

表題(セルA1)には【発生率の分布】と見出しを付け、シート名は「pd」とした。