【Excel】サンプルデータを手動でコピペしExcelVBAマクロでデータ加工する

Excel
スポンサーリンク

コピペ後、ExcelVBAマクロを実行しデータベース投入用の表に変換

あれこれ説明するより実際使っている最終を表示した方が説明が早いので下に表示するヘッダーは特に個人の名称とかないので、今回はそのまま表示している。

※このブログではサンプル表示に競走馬データを使用している。 馬名、騎手、調教師、馬主の名称は変換後表示することにしている。

出走馬RH(ヘッダー)の作成

  1. 出走馬レース選択から「ctrl+a」→「ctrl+c」
  2. Excelにシート名:head「ALT→HVM」でコピペ

※ ここから原文

2019年12月15日(日曜) 5回中山6日
天候晴
芝良
ダート良
開催選択へ戻る
ご覧になりたいレースの出馬表ボタンをクリックしてください。
全てのレースを表示
出馬表 発走時刻 競走名 変更 距離 馬場 頭数 馬体重 WIN5
1レース 9時50分 2歳未勝利牝[指定] 1,200メートル ダート 16頭 発表済
2レース 10時15分 2歳未勝利[指定] 1,800メートル ダート 16頭 発表済
3レース 10時45分 2歳未勝利(混合)[指定] 1,200メートル 芝 16頭 発表済
4レース 11時15分 2歳未勝利[指定] 1,600メートル 芝 16頭 発表済
5レース 12時05分 メイクデビュー中山 1,200メートル ダート 16頭 発表済
2歳新馬[指定]
6レース 12時35分 メイクデビュー中山 1,800メートル 芝 16頭 発表済
2歳新馬(混合)[指定]
7レース 13時05分 3歳以上1勝クラス(500万下)[指定] 2,500メートル ダート 14頭 発表済
8レース 13時35分 3歳以上2勝クラス(1000万下)牝[指定] 1,800メートル ダート 16頭 発表済
9レース 14時05分 舞浜特別 1,200メートル ダート 16頭 発表済
3歳以上2勝クラス(1000万下)(混合)[指定]
10レース 14時40分 北総ステークス 1,800メートル ダート 13頭 発表済
3歳以上3勝クラス(1600万下)(混合)[指定]
11レース 15時20分 ディセンバーSリステッド 1,800メートル 芝 16頭 発表済 ウインファイブ3レース目
3歳以上オープン(国際)(特指)
12レース 16時01分 3歳以上1勝クラス(500万下)[指定] 有 2,200メートル 芝 18頭 発表済
ページトップへ戻る
馬券は20歳になってから
ギャンブル障害への対応
サイトマップ
リンク
ご利用に際して

※ ここまでが原文

この作業を「ヘッダー、1~12R」3場開催だったので36R(中山・阪神・中京)行う。

手作業でのコピペを自動化する「ExcelVBA+UWSC」をプログラミングするのが今回の目的である。

開催日場名競走名競走条件距離ダ芝出走頭数
2019-12-15中山12歳未勝利牝120016
2019-12-15中山22歳未勝利180016
2019-12-15中山32歳未勝利120016
2019-12-15中山42歳未勝利160016
2019-12-15中山5メイクデビュー中山2歳新馬120016
2019-12-15中山6メイクデビュー中山2歳新馬180016
2019-12-15中山73歳以上1勝(500)250014
2019-12-15中山83歳以上2勝(1000)牝180016
2019-12-15中山9舞浜特別3歳以上2勝(1000)120016
2019-12-15中山10北総ステークス3歳以上3勝(1600)180013
2019-12-15中山11ディセンバーS3歳以上オープン180016
2019-12-15中山123歳以上1勝(500)220018

※上記サンプルは見やすくする為に、実際の内容より簡素化して表示している。

出走馬D(サンプル版)の作成

シート名:1R~12Rの出走馬データも同様にコピペする

※ExcelVBAの説明としては、ヘッダーだけで十分だが明細の項目が気になる人もいるかと思うので下記に項目名だけ記しておく。また下記でExcelの1行目見出しを縦に並べ変えるVBAサンプルを記載しているのでCellsの使い方を参考にしてほしい。

No.項目
1開催日
2場名
3
4K馬番
5枠番
6ブリンカー
7馬名MK
8馬名
9性齢
10毛色
11負担重量
12減量
13騎手
14調教師
15所属場
16馬主
17
18
19母父
20①出走日
21①場
22①R
23①馬番
24②出走日
25②場
26②R
27②馬番
28③出走日
29③場
30③R
31③馬番
32④出走日
33④場
34④R
35④馬番

データベース投入用シートの書式はDBの書式に合わせる

コピペなのでイレギュラーなデータも出てくる

これはもう避けようがなく、ある程度予測して作るか分からない項目はとりあえず文字列にしておく。私の場合も当初はExcelシートは「全て選択」→「文字列」で設定しておきAceessでエラーがでる都度対応するようにした。

Excelは自動で変換してくれるのがありがたい機能だが文字列の配列で記号が入ってくると半角「/」「-」「:」は日付や時刻に変換してしまうので、文字列指定にしておかないと表示通りに拾えないということが起きる場合がある。

コピペデータを整列させて綺麗な表形式に並べる

簡単なサンプルをたくさん作ることで利用できるコードが必ずある。

  • ExcelVBAやUWSCには自動記録機能がある。
  • ExcelVBAはググればサンプル数が多いのでヒントはたくさんある。
  • ExcelのことはExcelでやるのがシンプルで一番早く安くできる。

見出しを縦に表示するExcelVBAを作ってみる

前回までの仕様で準備してみよう

VBEを開くので「A1RC1Display」は直ぐ書いて動かしてみる。

Sub A1RC1Display()
    If Application.ReferenceStyle = xlA1 Then
        Application.ReferenceStyle = xlR1C1
    Else
        Application.ReferenceStyle = xlA1
    End If
End Sub

IF文を綺麗に書く工夫。「A1RC1Display」同様部品として標準装備させる。

Function CHRCVT(r) As String
     If IsNull(r) Then
         CHRCVT = ""
     Else
         CHRCVT = Trim(r)
     End If
End Function

sheet1:「出走D見出し」からsheet2:「出走D見出し一覧縦」を作成

実際に見出しが入力されたSheet1からSheet2の「例:出走D見出し一覧縦」にデータを入れるサンプルVBAを下に明記。シートは自分で作成しSub DTL_MIDASItate()の内容を変更し起動する。

Sub DTL_MIDASItate()
Dim j As Integer
    Sheets("出走D見出し一覧縦").Select
    Cells(1, 1) = "No."
    Cells(1, 2) = "項目"
    j = 1
    Do Until CHRCVT(Sheets("出走D").Cells(1, j)) = ""
        'A列に順
        Cells(j + 1, 1) = j
        '見出しをB列に表示させる
        Cells(j + 1, 2) = Trim(Sheets("出走D").Cells(1, j))
        j = j + 1
    Loop
End Sub

MIDASItateを関数化しやすいようにシート名を変数にしておく

Sub DTL_MIDASIt2()
Dim j As Integer
Dim sh As String, sh2 As String
    sh = "出走D"
    sh2 = "出走D見出し一覧縦"
    Sheets(sh2).Cells(1, 1) = "No."
    Sheets(sh2).Cells(1, 2) = "項目"
    j = 1
    Do Until CHRCVT(Sheets(sh).Cells(1, j)) = ""
        'A列に順
        Sheets(sh2).Cells(j + 1, 1) = j
        '見出しをB列に表示させる
        Sheets(sh2).Cells(j + 1, 2) = Trim(Sheets(sh).Cells(1, j))
        j = j + 1
    Loop
End Sub

シート間のデータ操作はCellsプロパティを使うと上手く整理できる

ExcelVBAはA1表記とR1C1表記と2種類のセルの指定方法がある。

両方の指定の仕方を理解しておくと、Excel関数をコピーするときに案外役に立ったりする。

編集後記

私はあまりRange型について理解していないのと、大量データを扱う場合でも今のパソコンの処理能力なら、Cellsを指定しても待てる程度の時間なので、特に意識したり、深く勉強したりしてはいないが、高速処理を狙うならRange型(Dim r as range)を指定して処理する方が格段に速くなる。。。らしい。

ただこれについて詳しく例文付きで解説している本やブログはまだ見たことがない。多分Pythonなどの言語のRange型とはちょっと違うMicrosoftならではの特殊な概念のような気がするので、ちゃんと使える人がいないんじゃないか?という疑問符?がついてしまう感じ。

多分このまま使わないで、棺桶まで行くのだろう~函~

それでは、またね~😎

Excel
スポンサーリンク
スポンサーリンク
あどのまつりをフォローする
マクロで半自動化!go!go!!programming
タイトルとURLをコピーしました