[VBA]PCR検査実施人数を一覧表示するマクロ

[0]はじめに

Zitanです。
Enum(列挙型)の使い方、データの最終行を取得する方法など記事にしてきました。
これらを応用した例を適当に作りました。
厚労省サイトにCSVデータがいっぱいあるので、一般利用できるテストデータとして使わせていただきました。今回の内容を理解して使えるようになると、いわゆるネ申エクセル、Excel方眼紙といった扱いづらいデータをデータベース(テーブル形式)に整形することも可能です。

[1]事前準備・マクロ実行

・テストデータはこちら
厚労省>新型コロナウイルス感染症について>オープンデータ
※PCR検査実施人数というリンクをクリックするとCSVファイルのダウンロードが始まります。
・今回のプログラムコード
Git Hub_ExcelVBA/PCR検査数一覧表示
※貼り付ける場所(開発タブ>Visual Basic>挿入タブ>標準モジュール)
※標準モジュールでなくてもいいですが。
・Excelシートの準備
「Main」シートと「pcr_tested_daily」シートを用意します。
「pcr_tested_daily」シートは厚労省サイトからダウンロードしたCSVシートをそのまま
シートごとマクロファイルに持ってきています。元からそういうシート名でした。

・「Main」シートのD2セルにデータの入力規則（0以上の整数入力）を設定します。
・マクロを実行します。(開発タブ>マクロ>PCR検査数一覧表示>実行)
※マクロを実行するには、事前に開発タブが表示される設定が必要です。

[2]コードの解説

コードはここからコピペして下さい。以降は解説用です。同じですが。。
Git Hub_ExcelVBA/PCR検査数一覧表示

変数宣言の強制オプション

Option Explicit

Enum(列挙型)の宣言

Private Enum Col
    日付 = 1
    PCR単日検査実施件数
End Enum

※1列目「日付」、2列目「PCR単日検査実施件数」をコード内で表現するときに都合が良いです。列番号をハードコーディングすると後で修正するときに都合が悪い場合があります。
　
例えば、1列目「日付」が2列目、2列目「PCR単日検査実施件数」が1列目に変わったときは、Enumの中身を書き換えるだけで済みます。

Enumの中身を書き換える※こんな書き方をするかは別として。。。

Private Enum Col
    日付 = 2
    PCR単日検査実施件数= 1
End Enum

ちなみに、Enumに記載している項目名はExcelシートと統一する必要はありません。
予約語含めて、使用できない文字もあるので対応関係の認識が合えばOKです。

画面描写をOFFにします。

Application.ScreenUpdating = False

※画面描写については処理されているなーという実感が欲しい人は書かなくてもいいですが、
　処理時間に影響が出る場合があるので私は書きます。

変数宣言

Dim MAIN_SHEET As Worksheet: Set MAIN_SHEET = ThisWorkbook.Worksheets("Main")
Dim PCRTEST_SHEET As Worksheet: Set PCRTEST_SHEET = ThisWorkbook.Worksheets("pcr_tested_daily")
Dim i As Long
Dim j As Long: j = 2
Dim pcrTestNum As Long: pcrTestNum = 0
Dim PcrLastRow As Long: PcrLastRow = PCRTEST_SHEET.Cells(Rows.Count, Col.PCR単日検査実施件数).End(xlUp).Row
Dim MainLastRow As Long: MainLastRow = MAIN_SHEET.Cells(Rows.Count, Col.PCR単日検査実施件数).End(xlUp).Row

※MAIN_SHEET：「Main」シートを簡潔に表現するためのオブジェクト変数
※PCRTEST_SHEET：「pcr_tested_daily」シートを簡潔に表現するためのオブジェクト変数
※i:FORループを回すための変数
※j:データの転記先の行番号をインクリメントするための変数
　　ちょうどデータが2行目から入るので初期値は2に設定しています。
※pcrTestNum：「Main」シートのD2セルに入っている数値を格納します。
　　　　　　　　データの入力規則を設定しているセルなので整数値のみ入力できます。
※PcrLastRow：PCRTEST_SHEETの2列目のデータが入っている最終行の行番号
※MainLastRow：MainLastRowの2列目のデータが入っている最終行の行番号
↑2列目にこだわりはありません。1列目でもOK。

「MAIN_SHEET」のD2セルにPCR検査数の下限値が入っているか判別する

If MAIN_SHEET.Range("D2").Value = "" Then
    MsgBox "MainシートのD2セルに検査数の下限値を入力して下さい。"
    Exit Sub
Else
     pcrTestNum = MAIN_SHEET.Range("D2").Value
End If

※D2セルがブランクの場合、メッセージボックスが出現した後に終了(Exit Sub)します。
※D2セルに何らかの整数が入っていればpcrTestNumにその値を格納します。

「MAIN_SHEET」内の前回の一覧データをクリア(初期化)する

If MAIN_SHEET.Range("A2") <> "" Or MAIN_SHEET.Range("B2") <> "" Then
    MAIN_SHEET.Range("A2:B" & MainLastRow).Clear
End If

※「MAIN_SHEET」の初期化をします。
　A2セルまたはB2セル(2行目)にデータが入っていれば2行目より下の行のデータを消します。
　前回処理したデータが入っていると都合が悪い時があるので一応書いています。

さて今回のメイン処理ですが、、、
「MAIN_SHEET」のD2セルに入力した検査数よりも大きい検査数の行データだけを
「PCRTEST_SHEET」から「MAIN_SHEET」に書きこんでいます。

「PCRTEST_SHEET」のデータを「MAIN_SHEET」に転記する

For i = 2 To PcrLastRow
    If PCRTEST_SHEET.Cells(i, Col.PCR単日検査実施件数).Value >= pcrTestNum Then
        MAIN_SHEET.Cells(j, Col.日付) = WorksheetFunction.Text(PCRTEST_SHEET.Cells(i, Col.日付), "yyyy/m/d(aaa)")
        MAIN_SHEET.Cells(j, Col.PCR単日検査実施件数) = WorksheetFunction.Text(PCRTEST_SHEET.Cells(i, Col.PCR単日検査実施件数), "###,###")
        j = j + 1
    End If
Next i

慣れない人は難しく感じるかもしれませんが、ここでやっていることを一行ずつ説明していきます。

１．「PCRTEST_SHEET」の2行目から2列目最終行まで以下の処理を繰り返します。

２．もし、「PCRTEST_SHEET」のi行目2列目の値がpcrTestNumよりも大きいならば、
　　「MAIN_SHEET」のj行1列目のセルに「PCRTEST_SHEET」のi行目1列目の値を代入（書き込み）します。
　　「MAIN_SHEET」のj行2列目のセルに「PCRTEST_SHEET」のi行目2列目の値を代入（書き込み）します。
　　 jの値ををインクリメント(+１)します。※j:初期値は2で宣言しています。

　　もし、「PCRTEST_SHEET」のi行目2列目の値がpcrTestNumよりも大きくないならば何もしません。

３．次のi行に進む。

※「WorksheetFunction.Text」：そのまま書きこむとデータの表示形式が日付ではなくシリアル値(438876とか)になってしまうので使っています。割と使います。関数をマクロで使うときは、「WorksheetFunction.ほにゃほにゃ」と表現します。

変数「j」がないと（代わりに「i」を入れると）こんな結果になります。
下の図の場合、D2セルが100以上の場合、「PCRTEST_SHEET」と同じ行番号のセルに書きこまれます。

2行目から順に書きこまれてほしいので、「j」(初期値は2)という別の変数を用意しました。
書きこまれた後は、「j=j+1」(インクリメント)することで次の行に書きこまれるようにしています。

[3]まとめ

いかがでしょうか。
最終行取得、FORループ、IF分岐を理解できれば、事務処理業務の色んな場面に応用できます。
いわゆるネ申エクセルと呼ばれるビッグデータ（？）もこれらの知識を応用することで
簡単なデータベースにクレンジングすることができます。