【Access】VBAを使ってデータに順番をつけ並べ替えをする

2022年2月1日2024年1月24日

AceessVBAを使い順位付け、昇順・降順に並べ替える

競馬データを扱っていると
スクレイピングで取った数値データをある項目で並べ替えたりすることが
データ分析を行う上で、必須になってくる。

初めから用意しておけば良いとは思うが
その項目を実際に使うまでは、順位付けが必要かどうか？は判断できない。

AccessVBAで数値項目を昇順（降順）に並べて順位付けするサンプル作成

adono

この記事を読むと、VBAで順位をつけるモジュールが作成できる ❣

この記事を読むと、VBAでテーブルのある数値項目の順位をつけるモジュール（VBA）が作成できる。
これを更新クエリーだけでやると、少なくとも５つほど作成しなければいけない。

ので、Key項目で並べ替えて、SEQ.を付けるといった場合に重宝する雛形モジュールになる。

競馬データを例に取り複勝倍率順（馬連順、馬単順）に並べ替えて順番をデータ化

複勝倍率順に並べ替えて、複勝人気順を作成する
馬連倍率順（馬単倍率順など）
前走上り順　　　〃　　、前走上り順　　〃

今回のサンプルは前走上り順データを作成するモジュール

これは今日データ分析するのに作成したばかりの出来立てホヤホヤなので、ここで晒しておく！内容に不備がある場合は、ご自身で修正してお使い下さいませ。

なぜExcelでなく、Accessなのか？というと・・・

Excelよりも大量データを扱えデータ更新が簡単
並べ替えなどのデータ操作が楽
Accessの許容量を超えた場合もSQL-server(Express)無料へのデータ移行がAccessを使うと比較的簡単なため

参考　AccessVBA：前走上り順データを作成するモジュール

Function RAgariRenban()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim BL_DATE As Date, BL_ZYOMEI As String, BL_R As Integer
    Dim BL_AGARI As Double, BL_SEQ As Integer
    Dim i As Long, n As Long

    strSQL = "SELECT 開催日, 場名, Ｒ, 推定上り, 推定上り順" & " "
    strSQL = strSQL & "FROM WK_DT" & " "
    strSQL = strSQL & "WHERE 推定上り > 0" & " "
    strSQL = strSQL & "ORDER BY 開催日, 場名, Ｒ, 推定上り"
    strSQL = strSQL & ";"

    'カレントデータベースを変数に代入する
    Set db = CurrentDb
    
    'クエリを実行してレコードセットを開く
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
        
    RAgariRenban = False
    If rst.RecordCount <= 0 Then Exit Function

    i = 1

        Do
            rst.Edit
            If i = 1 Then
                BL_DATE = rst!開催日
                BL_ZYOMEI = rst!場名
                BL_R = rst!R
                BL_AGARI = rst!推定上り
                BL_SEQ = 1
                n = 0
            End If
'''ブレイクkey
            If BL_DATE = rst!開催日 And BL_ZYOMEI = rst!場名 And BL_R = rst!R Then
                If BL_AGARI = rst!推定上り Then
'''ここまで一緒だと「推定上り順」は重複する
                    rst!推定上り順 = BL_SEQ
                    If i > 1 Then
                        n = n + 1
                    End If
                Else
                    BL_SEQ = BL_SEQ + 1 + n
                    rst!推定上り順 = BL_SEQ
                    BL_AGARI = rst!推定上り
                    n = 0
                End If
            Else
                BL_DATE = rst!開催日
                BL_ZYOMEI = rst!場名
                BL_R = rst!R
                BL_AGARI = rst!推定上り
                BL_SEQ = 1
                n = 0
                rst!推定上り順 = BL_SEQ
            End If
    
            rst.Update
            
            i = i + 1
            rst.MoveNext
        Loop Until rst.EOF = True
    
    rst.Close
    RAgariRenban = True
End Function

※前回使用した複勝順作成を再利用してモジュールを作成。

前走上り順データを作成するモジュールの補足説明を【ChatGPT】にお願いすると…

ChatGPT(Bing)

上のAccessVBAが何をしているのか補足説明をして下さい

このEXCELVBAは、WK_DTというテーブルから開催日、場名、Ｒ、推定上り、推定上り順のデータを取得し、推定上り順を連番に更新する関数です。以下に補足説明をします。

まず、strSQLという変数にSQL文を代入します。このSQL文は、推定上りが0より大きいレコードを開催日、場名、Ｒ、推定上りの順に並べて取得するものです。

次に、dbという変数にカレントデータベースを代入し、rstという変数にレコードセットを開きます。レコードセットとは、データベースから取得したデータの集合です。

RAgariRenbanという関数の戻り値をFalseに設定します。これは、関数が正常に終了したかどうかを示すものです。レコードセットが空だった場合は、関数を終了します。

iという変数に1を代入します。これは、レコードセットのインデックスを表します。

レコードセットの最後まで繰り返し処理を行います。その中で以下の処理を行います。 – レコードセットを編集モードにします。

iが1の場合は、BL_DATE, BL_ZYOMEI, BL_R, BL_AGARI, BL_SEQという変数にレコードセットの各フィールドの値を代入します。これらの変数は、前回のレコードの値を保持するものです。BL_SEQは推定上り順の値で、1から始まります。nという変数に0を代入します。これは、同じ推定上りのレコードが何件あったかをカウントするものです。

iが1でない場合は、ブレイクキーとして開催日、場名、Ｒの値が前回のレコードと同じかどうか判定します。同じ場合は以下の処理を行います。 – 推定上りの値が前回のレコードと同じかどうか判定します。同じ場合は推定上り順にBL_SEQの値を代入し、nを1増やします。これは、同じ推定上り順であることを示すためです。

推定上りの値が前回のレコードと異なる場合はBL_SEQにnを足して1増やし、推定上り順に代入します。これは、次の推定上り順であることを示すためです。BL_AGARIに現在のレコードの推定上りの値を代入し、nを0にリセットします。

開催日、場名、Ｒの値が前回のレコードと異なる場合は以下の処理を行います。 – BL_DATE, BL_ZYOMEI, BL_R, BL_AGARIという変数に現在のレコードの各フィールドの値を代入します。BL_SEQに1を代入し、推定上り順に代入します。これは、新しいブレイクキーであることを示すためです。nを0にリセットします。

レコードセットに変更を保存します。

iを1増やし、次のレコードへ移動します。

レコードセットを閉じます。

RAgariRenbanという関数の戻り値をTrueに設定します。これは、関数が正常に終了したことを示すものです。

以上がこのAccessVBAが何をしているか補足説明したものです。ご参考になれば幸いです。😊
ChatGPT(Bing)から2023.8.3(thu)に追記

参考　Accessマクロ：前走上り順データを更新するマクロサンプル抜粋

「前走上り順」項目をテーブルに追加して、その順番を更新するAccessマクロ抜粋！

※サブマクロを書き換えれば、データ更新の雛形としてこれを流用すれば手順のミス（人的エラー）は激減する。

Access マクロ入門 ~仕事の現場で即使える

Amazon

楽　天

Access マクロ入門 ~仕事の現場で即使える

マクロの基本と本書のサンプルについて
マクロツールを使ってみよう
フォームを作成しよう
高機能なフォームからクエリやレポートを操作
アプリケーションを仕上げて使いやすくしよう
Excelとデータをやりとりしよう
マクロをもっと使いこなそう
アクションカタログリファレンス
索引

AccessVBAではなく「Accessマクロの専門書」としてはコレしか見たことがない！
個人でデータ加工するなら、Excelの弱点である大量データの更新や一括更新は、Accessで操作した方が正確で素早く加工できるが、皆様あまり使わない。GUIでSQL発行をするので、Accessクエリを勉強するとSQL文も同時に習得できるといった特典付きでも誰もやらないのは、私からすると未だに謎だよｗ
Amazonレビューは★4.2

私のKindle-Unlimited評価は★4.5

データ分析してみた結果は・・・？

特別に何かプラスが出るようなことは発見出来なかった。
まだコーナー順を調べた方が何かありそうな雰囲気はあるな～と感じた昨日から今日の２日間であった。

前走上り①番手の分析データのサンプル

編集後記

AccessマクロとAccessVBAは全く違うもの（Excelは一応マクロ＝ExcelVBA…）

先にExcelから言うと、厳密にはExcelにも昔からマクロ機能は備わっているが、あまりに誰も使わないので埋もれてしまい世間的には、「ExcelVBA＝Excelマクロ」ということになっている。

今でも「Ctrl + F11」を押すと、シートに「Macro1」が追加されるが、私も実際に使ったのは30年以上前テスト用に2,3回で、今は全て「ALT + F11」で表示されるVBEでの記述での運用しかない。
したがってExcelの場合はExcelVBAもExcelマクロも同じExcelVBAを指す。

但し、AccessVBAとAccessマクロは全く違うものを指すので、注意が必要…
ただこちらもほぼAccessVBAしか使われていないのが現状。。。
更に最近ではAccessそのものがあまり使われていないと思われる。
便利なんだけどなぁ～Accessマクロ 🤗
と個人的には思うが

今後はデータベースそのものが見えない形に進化していくのかも知れないね💗

但し、2024.1.24時点では、まだまだ使った方が大幅な時短になるツールであることは確か！
ショートカットキーを覚えても単発の作業は多少速くなるとしても、単純で大量作業をプログラミングする方が圧倒的に現場では多いので、これを個人業務に落として処理する方が時短にもなるし、勉強にもなる。

私の一番のおすすめツールは、Microsoft Accessで！その中でも「Accessマクロ」

Access マクロ入門 ~仕事の現場で即使える

Amazon

楽　天

Access マクロ入門 ~仕事の現場で即使える

マクロの基本と本書のサンプルについて
マクロツールを使ってみよう
フォームを作成しよう
高機能なフォームからクエリやレポートを操作
アプリケーションを仕上げて使いやすくしよう
Excelとデータをやりとりしよう
マクロをもっと使いこなそう
アクションカタログリファレンス
索引

私のKindle-Unlimited評価は★4.5

競馬データを Access + Excel で分析していて一言。。。

なんだかんだと思いついては、競馬データを分析してみるけど
コレ！といった使えるモノはなかなか見つからないね～(￣▽￣)。。。
まぁ簡単に見つかるなら、みんな億万長者になってしまうもんねぇｗ

それでも、気になることは自分で即座に分析したい衝動に襲われるので、仕方ないかな～と思っている。
結局アナログ予想が一番当たるから「競馬は面白い！」のかも知れない♡

また気になるデータ分析の結果が出たら一部をアップするかも知れないので、応援してね～💗～

それでは、またね～😎

月	火	水	木	金	土	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

【Access】VBAを使ってデータに順番をつけ並べ替えをする

AceessVBAを使い順位付け、昇順・降順に並べ替える

AccessVBAで数値項目を昇順（降順）に並べて順位付けするサンプル作成

競馬データを例に取り複勝倍率順（馬連順、馬単順）に並べ替えて順番をデータ化

今回のサンプルは前走上り順データを作成するモジュール

なぜExcelでなく、Accessなのか？というと・・・

参考 AccessVBA：前走上り順データを作成するモジュール

前走上り順データを作成するモジュールの補足説明を【ChatGPT】にお願いすると…

ChatGPT(Bing)

上のAccessVBAが何をしているのか補足説明をして下さい

参考 Accessマクロ：前走上り順データを更新するマクロサンプル抜粋

データ分析してみた結果は・・・？

前走上り①番手の分析データのサンプル

編集後記

AccessマクロとAccessVBAは全く違うもの（Excelは一応マクロ＝ExcelVBA…）

競馬データを Access + Excel で分析していて一言。。。

参考　AccessVBA：前走上り順データを作成するモジュール

参考　Accessマクロ：前走上り順データを更新するマクロサンプル抜粋