HOME > 即効テクニック > Excel VBA > その他関連のテクニック > コレクションの活用−複数シートの明細を集計する

即効テクニック

その他関連のテクニック

コレクションの活用−複数シートの明細を集計する

(Excel 97/2000)
Collectionオブジェクトを用いて作成した独自のオブジェクトは、Addメソッドによる要素追加時に指定したKeyを用いて各要素にアクセスすることができました。
インデックスだけでなく、指定した任意の文字列で、データにアクセスできることを応用し、以下のサンプルでは科目マスターをもとにして複数シート上のデータを集計します。

例
Dim MyCollect as New Collection
MyCollect.Add 10, "KAMOKU1001"' ⇒ MyCollect("KAMOKU1001")
で10を取得できる

具体的には、経理データなどで言えば、勘定科目マスターに登録された各勘定科目をKeyとし、対応する要素として格納されたインデックス番号を割り出し、この番号をインデックスとして持つ配列に金額をたしこんでいきます。

なお、集計結果はさまざまな損益計算書に対応できるよう、集計先シートで勘定科目コードが入力されたセルを検索し、その3列目に金額を書き込むようにしています。
必要に応じてマスターシートと集計先シートを分けてご利用ください。

(前提)・マスター兼集計先ワークシート(シート名:科目マスター)
※1列目:勘定科目コード、2列目:勘定科目名(使用しない)
  3列目:集計値格納用の空欄、1行目は項目行
 ワークシート(Sheet1〜Sheet3)に1列目を勘定科目、2列目を金額データ
 ※各シート1行目は項目行とする
'================================================================
'================================================================
Type Shukei
    CD As String '勘定科目コード
    Amount As Currency '金額
End Type

Sub MyProc()

 Dim LastRow As Long
 Dim MyCollect As New Collection
 Dim MyVar() As Shukei
 Dim Temp As Integer
 Dim Rng As Range
 Dim i  As Integer, j As Integer, k As Long, l As Long

 'マスターシート最終行取得
 LastRow = Sheets("科目マスター").Range("A65536").End(xlUp).Row
 '配列の最宣言
 ReDim MyVar(2 To LastRow)

'================================================================
'マスターデータ(コード)をユーザ定義型配列に格納
'================================================================

    With MyCollect
        For i = 2 To LastRow
            MyVar(i).CD = Sheets("科目マスター").Cells(i, 1)
            'マスターデータ件数分コレクションに要素を追加(
            'コード番号をKeyの一部としている)
            .Add i, "Kamoku" & Sheets("科目マスター").Cells(i, 1).Value
        Next i

'================================================================
'集計
'================================================================

        For j = 1 To 3 'シート数分ループ
            With Sheets("Sheet" & j)
            '項目行をのぞいて明細データ件数分ループ
                For k = 2 To .Range("A65536").End(xlUp).Row
            '対象明細に含まれるコードをキーとしてコレクションの
            '要素に格納された配列検索用インデックスを取得
                    Temp = CInt(MyCollect("Kamoku" & .Cells(k, 1)))
            'インデックスを指定してユーザ定義型配列に金額を足しこむ
                    MyVar(Temp).Amount = _
                       MyVar(Temp).Amount + .Cells(k, 2).Value
                Next k
            End With
        Next j

'================================================================
集計先シートへの金額書き込み
'================================================================

    '配列の下限から上限までループ
        For l = LBound(MyVar) To UBound(MyVar)
            If MyVar(l).Amount <> 0 Then
            '対応するコードを持つセルを検索し、3列目に金額を書き込む
                Set Rng = Sheets("科目マスター").UsedRange. _
				Find(MyVar(l).CD)
            End If
            If Not Rng Is Nothing Then _
                Rng.Offset(, 2).Value = MyVar(l).Amount
            End If
        Next l


    End With
End Sub
'================================================================
'================================================================
ここではコレクションの実用的なサンプルとして複数シートのデータの集計を行いました。
実際には複数シートの集計の場合でも、ピボットテーブルを使うなど、他の方法もあります。