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

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

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

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

出走馬ＲＨ（ヘッダー）の作成

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

※　ここから原文

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分 ディセンバーＳリステッド 1,800メートル 芝 16頭 発表済 ウインファイブ3レース目
3歳以上オープン（国際）（特指）
12レース 16時01分 3歳以上1勝クラス(500万下)[指定] 有 2,200メートル 芝 18頭 発表済
ページトップへ戻る
馬券は20歳になってから
ギャンブル障害への対応
サイトマップ
リンク
ご利用に際して

※　ここまでが原文

この作業を「ヘッダー、１～１２Ｒ」３場開催だったので３６Ｒ（中山・阪神・中京）行う。

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

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

出走馬Ｄ（サンプル版）の作成

シート名：１R～１２Rの出走馬データも同様にコピペする

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

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

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

これはもう避けようがなく、ある程度予測して作るか分からない項目はとりあえず文字列にしておく。私の場合も当初は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)) = ""
        'Ａ列に順
        Cells(j + 1, 1) = j
        '見出しをＢ列に表示させる
        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)) = ""
        'Ａ列に順
        Sheets(sh2).Cells(j + 1, 1) = j
        '見出しをＢ列に表示させる
        Sheets(sh2).Cells(j + 1, 2) = Trim(Sheets(sh).Cells(1, j))
        j = j + 1
    Loop
End Sub

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

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

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

編集後記

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

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

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

それでは、またね～😎