DIYしよう -マクロでワークシート分析-実践編2


EXCELでDIY	Excelで競馬分析をしよう
		Home > ＤＩＹしよう！> ExcelでDIY

DIYしよう

■アルゴリズムとは？

　今回は前回に引き続き、2001年新潟競馬場タイム分析に用いたワークシートの分析の続きをおこないます。前回は、各馬の通過順位から脚質を求めるプログラムを作りました。今回は求めた脚質をコースごとに分類するプログラムを作ります。
　ここで、重要なことはどの様にコードを書けばそのようなプログラムが作れるかと言うことです。プログラムの動作原理(動作手順)のことをアルゴリズムといいます。プログラムは通常上から下から流れるため、複雑な処理をする場合には、制御構文を用いる必要があります。どの様な処理手順を踏み、どの様に制御するかを決めるアルゴリズムは、実際のコードを書く作業よりも重要です。

　プログラムの流れを分かりやすく表現する方法として、はるか昔からフローチャート(流れ図)が用いられてきました。はっきりいってWindows用のプログラムを作る手法としては時代遅れですが、要所要所のアルゴリズムを考える場合にはまだまだ使えます。

　フローチャートは、前回でもちょこっと出てきました。簡単に言ってしまえば、プログラムの流れを図であらわしたものです。処理を表す四角形やひし形などの中に、処理内容を書きこみ、それを矢印を使ってむすんで行きます。プログラムは上から下へ流れるため、矢印は通常上から下に結ばれていきます。もちろん、前回出てきた制御構文を使えば、繰り返しや条件分岐などの流れも出来ます。

【通常の流れ】
	プログラムは上から下に順次処理される下から上には行けない

【繰り返し処理：Do While()～Loop】
	プログラムは上から下へ順次処理されるひし形の所が条件分岐となり、条件が満たされていれば、Yesとかかれた矢印をだどる条件が満たされていなければNoとかかれた矢印をたどる Do While()の場合、条件が満たされている間は、くり返し処理を行う

【条件分岐処理：IF～Then～End If】
	ひし形の所が条件分岐。条件を満たしていればYesと書かれた矢印の方向の処理へ満たしていなければNoと書かれた矢印の方向の処理へ

【繰り返し処理：For To～Next】
	決まった回数ループ処理を行う For To～Nextで囲まれた部分の処理を繰り返す無限ループにはならないループを数えるためのカウンタ変数を用意する必要がある(ループカウンタ)

ほとんどのプログラムはこれら処理を組み合わせて作ります。

■ループ処理 For～To～Next

　 For～Next文は繰り返し処理を行う為の制御構文の一種です。Do While～Loop文との違いは、Do Loopは条件が満たされるまで繰り返し処理をおこないますが、For～Nextは決まった回数繰り返したら処理を抜けます。プログラムには決まった回数繰り返すものが多いので、Do Whileよりは利用頻度が高いかもしれません。

書式：For A To B ～ Next
機能：ループカウンタがA→Bになるまで、～を繰り返す
例)
For i=1 To 10
　　(処理1)
Next
処理1を10回繰り返す

フローチャートで使われる、代表的な記号についてはこちらをご覧下さい。

■コース別の連対脚質分布を集計してみよう

　では、コース別の連対脚質を集計するプログラムのアルゴリズムを考えてみましょう。といっても、いきなりフローチャートを書くわけにはいかないので、処理手順について考えてみます。

【プログラムの動作】

データソースは前回脚質を求めたデータを使用
データ処理を簡単にする為に、あらかじめトラック、距離別にソートしておく
ループ処理を2つ用意し、１つはデータソースがなくなるまで繰り返す
もう１つは同じトラックのデータだけの集計を行うループ
データの集計は、脚質別の番号(1:逃げ 2:先行 3:差し 4:追込)をひろって来て、その数字ごとに配列変数でカウントする。
UP1トラック終了時に、ワークシート上に結果を表示する

結構やるべき処理がありますが、1つ1つ整理すれば問題ありません。

画像をクリックすると拡大されます

List1.コース別の連対脚質分布の集計

Public Sub CountKyaku()
Dim y As Long, Kya(4) As Long, i As Long, Tot As Long, Ky As Long
Dim szCor As String, szTxt As String, ln As Long
    
    Do While (Sheet1.Cells(y + 2, 1) <> "")  'Sheet1のデータソースがなくなるまで繰り返し
        
    '■■■ [1]統計用変数準備
    For i = 0 To 3
      Kya(i) = 0                            '集計用の変数をクリア
    Next
    Tot = 0                                 '連対総数集計用の変数クリア

    szCor = Sheet1.Cells(y + 2, 7) + CStr(Sheet1.Cells(y + 2, 8))
    szTxt = szCor                           'トラック名比較用
    Sheet2.Cells(ln + 2, 1) = szCor         '集計用シートにトラック名書きこみ
        
    '■■■ [2]同一トラックの脚質別連対数カウント
    Do While (szTxt = szCor)                'トラック名比較
      Ky = Sheet1.Cells(y + 2, 24) - 1      '脚質データ取得
      Kya(Ky) = Kya(Ky) + 1                 '脚質別出現数カウント
      Tot = Tot + 1                         '総数カウント
      y = y + 1                             'データソースの読みこみ位置+1
      szTxt = Sheet1.Cells(y + 2, 7) + CStr(Sheet1.Cells(y + 2, 8))
    Loop
        
    szCor = szTxt                           '次のトラック名
        
    '■■■ [3]カウントしたものを出力
    For i = 0 To 3
      Sheet2.Cells(ln + 2, i + 2) = Kya(i)  '集計カウント数
      Sheet2.Cells(ln + 2, i + 6) = Format(Kya(i) / Tot, "0.0%")  '連対占有率
    Next
    
    ln = ln + 1                             '出力用ワークシート+1行
  Loop

【概要の解説】

フローチャートを見ても明らかな通り、3つのセクションに分かれています。1つは[1]統計用の変数を初期化するセクション。２つめは[2]同一トラックの脚質別のカウント処理を行うセクション。最後は[3]カウントしたものをワークシートに出力する処理。これらをデータソースであるSheet1のデータがなくなるまで繰り返します。

【1.統計用変数準備】

脚質別の集計用変数Kya()をクリア
Sheet1のデータからトラック(芝/ダート)と距離を抜き出し、トラック判別用に変数に保存

【2.同一トラックの脚質別連対数カウント】

Sheet1のトラックと現在のトラック(szCor)が一致している限りループ
脚質データを取得し、配列変数にカウント(同じ脚質番号があれば+1)

【3.カウントしたものを出力】

逃げ、先行、差し、追込の順に保存した集計データをワークシートに出力
連対占有率も出力

なお、ここから実際のワークシートをダウンロード出来ます。

【配列変数とは？】

　配列変数とは、変数の一種です。通常の変数は1個の変数に、1つの値しかしまう事はできませんが、配列変数は同じ名前の変数を複数用意し、複数の値を格納することが出来ます。その区別は変数名の後ろにあるカッコ内の数字でおこないます。aという名前の配列変数を10個定義した場合、a(0)～a(9)までの変数を宣言したのと同じ事になります。配列変数の宣言は、通常の変数と同じように定義しますが、宣言時に使用する数をカッコ内に記述する必要があります。

宣言：Dim A(10) as Long
解説：Aという名前の配列変数をLong型で11個定義(0も含めるため)

EXCELでDIY