エクセルを使ってファイルを突合チェックする

先日、成果物（ここではプログラムとします）と成果物一覧を見比べてファイル有無をチェックしてほしいと言われ、エクセル関数とマクロを使用し自動化したので備忘録として残しておきます。

したいこと

フォルダ内の成果物（.txt）と成果物一覧を突合チェックしたい。

方針

• Excelが使える人対象。Excel関数は IF, COUNTIF, LEFTB, LENB関数程度
• マクロは使えないけど興味ある
• マクロはあくまで補助

エクセル側

1. 突合させる用のシート作成

フォルダ側のリスト

成果物一覧

2. 突合結果シート作成

• B2, D2はそれぞれのシートからの値が入る(=シート名!セル)
• C2はB2から.txt（4文字）を省いた値が入る（=LEFTB(セル, LENB(セル)-4)）
• E2はCOUNTIF(範囲,検索条件)を使用したチェック結果が入る。１つでもあれば１、なければ０になる。

関数を適用するとこうなる↓

これでは分かりづらいので、IFを使用し０と#VALUE!が表示されないようにする。あと一覧整形。

エクセル側は完成。フォルダ内のファイル一覧取得はコマンドでもいいが、ここではマクロで一覧取得してみる

マクロ

1. Alt + F11　でVisual Basic Editorを起動し、標準モジュールを作成する

2. VBAを書く、分からなければコピーでOK（サンプルはネットにころがっているので）

Sub test()

    '変数を定義
    Dim buf As String, ThisPath As String, cnt As Long
    
    '変数「ThisPath」にパスを格納
    ThisPath = ActiveWorkbook.Path
    
    '「Path」に存在するすべての「*.txt」をアクティブシートに書き出す。
    buf = Dir(Path & "*.txt")
    Do While buf <> ""
        cnt = cnt + 1
        Cells(cnt, 1).Value = buf
        buf = Dir()
    Loop
    
    '完了メッセージをポップアップで表示
    MsgBox "ファイル一覧を更新しました"

End Sub

3. 開発タブからボタンを挿入し、VBAが起動できるように紐付け

完成。成果物と同じフォルダへ入れて一覧更新をすれば簡単にチェックできます。

Excel VBAに興味を持った人へのおまけ

• Excel VBA チートシート