Excel VBAで学ぶ「データベース的な処理」

目次

1. Findメソッドでデータを検索する
2. FindNext、FindPreviousで連続検索
3. Dictionaryオブジェクトの活用（重複除去、カウント）
4. ユニークなリストの作成方法
5. クロス集計とピボットテーブルの自動化
6. 注意（ここだけ）
7. 要約

Findメソッドでデータを検索する

Range.Findを使うと、指定範囲で最初の一致を効率的に取得できます。

主要引数はWhat、LookIn、LookAt（完全一致・部分一致）、SearchOrder（xlByRows・xlByColumns）、Afterです。

Afterは見落としを招きやすいため、一般的には検索対象範囲の最後のセルを指定しておくか、取得後のAddressを使ってループ制御します。

実務では列全体指定やCurrentRegionを使い、必要に応じてValueまたはValue2を選びます。

Sub FindSample()
  Dim f As Range, key As String
  key = "検索語": If key = "" Then Exit Sub
  Set f = Worksheets("Sheet1").Range("A:A").Find(What:=key, LookIn:=xlValues, LookAt:=xlWhole)
  If Not f Is Nothing Then f.Interior.Color = vbYellow
End Sub

FindNext、FindPreviousで連続検索

Findで最初のセルを取得したら、FindNextまたはFindPreviousで次を辿り、最初のAddressに戻った時点で終了するパターンが基本です。

ループでは必ずNothingチェックとfirstAddrの比較を行い、無限ループを避けられるようにします。

途中で処理（色付けや集計）を行えば、表中の全一致をまとめて扱えます。

' 概要コード
Set f = ws.Columns("B").Find("対象", LookIn:=xlValues, LookAt:=xlPart)
If Not f Is Nothing Then
  firstAddr = f.Address
  Do
    f.Interior.Color = RGB(255,230,150)
    Set f = ws.Columns("B").FindNext(f)
    If f Is Nothing Then Exit Do
  Loop While f.Address <> firstAddr
End If

Dictionaryオブジェクトの活用（重複除去、カウント）

Scripting.Dictionaryはキーの存在チェックがO(1)に近く、重複除去や出現回数カウントに適します。

参照設定は不要で、CreateObject("Scripting.Dictionary")で使えます。

ループで値をTrimして空白を除き、存在すれば値をインクリメントし、なければ追加するだけで頻度テーブルが作れます。

大規模データや繰り返し検索より高速です。

Set dict = CreateObject("Scripting.Dictionary")
For Each r In ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))
  v = Trim(r.Value)
  If v <> "" Then If dict.Exists(v) Then dict(v) = dict(v) + 1 Else dict.Add v, 1
Next

ユニークなリストの作成方法

ユニーク抽出は主に二通りあります。1) Dictionaryでキーを集める方法は、プログラム内で加工・並べ替え・カウントを続けやすいです。2) Range.AdvancedFilter(Action:=xlFilterCopy, Unique:=True) でシート上に簡単に出力する方法は、ユーザーに見せる用途に便利です。用途に応じて使い分けます。

' AdvancedFilter例
ws.Range("D1:D" & ws.Cells(Rows.Count, "D").End(xlUp).Row).AdvancedFilter _
  Action:=xlFilterCopy, CopyToRange:=out.Range("A1"), Unique:=True

クロス集計とピボットテーブルの自動化

ピボットはPivotCache→PivotTableの順で作成します。データをテーブル（ListObject）にしておくと範囲管理が楽で、SourceDataにListObject.Rangeを渡すだけで済みます。

コードから行フィールドや列フィールド、データフィールドを設定し、RefreshTableで更新します。定期レポートの自動化やフィルタ、集計関数の制御に有効です。

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, wsData.Range("A1").CurrentRegion)
Set pt = pc.CreatePivotTable(wsOut.Range("A3"), "MyPivot")
With pt
  .PivotFields("カテゴリ").Orientation = xlRowField
  .PivotFields("月").Orientation = xlColumnField
  .AddDataField .PivotFields("金額"), "合計金額", xlSum
End With

注意（ここだけ）

• マクロを保存する場合はブックを「Excelマクロ有効ブック（.xlsm）」で保存してください。参照の追加や実行権限にも要チェックです。

要約

• Findは引数を適切に指定し、Afterの扱いを要チェックして最初の一致を取得します。
• FindNext/FindPreviousは最初のAddressを保存し、Nothingチェックで全件処理を終えます。
• Dictionaryは重複除去とカウントが高速で大規模集計に向き、CreateObjectで利用可能です。
• ユニーク抽出はAdvancedFilterが手軽で、Dictionaryは後続処理に強みがあります。
• ピボットはPivotCache→PivotTableの流れで自動生成し、ListObjectと組合せると運用が楽です。