1. Home » 
2. Excel講座の総目次 » 
3. Excel VBA入門：目次 » 
4. 統合(Consolidate)でのデータ集計

統合(Consolidate)でのデータ集計:Excel VBA入門

• Excelの一般機能である[データ]→[統合]をマクロにしたものです。
• 串刺し計算(3-D集計)のマクロとしても使えます。

統合(Consolidateメソッド)でのデータ集計　　　Topへ

• 複数のワークシートにある複数のセル範囲を、1 枚のワークシートの 1 つのセル範囲に統合します。
• expression.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)
  • Sources
    • 省略可能
    • バリアント型(Variant)の値を使用します。
      統合元範囲を参照する、R1C1 形式の文字列を指定します。
      指定するセル参照には、必ず統合するシートのパスを含めます。
  • Function
    • 省略可能
    • XlConsolidationFunction クラスの定数を使用します。

      • メンバ		値
        xlAverage (既定値)	平均	-4106
        xlCount	データの個数	-4112
        xlCountNums	数値の個数	-4113
        xlMax	最大値	-4136
        xlMin	最小値	-4139
        xlProduct	積	-4149
        xlStDev	標本標準偏差	-4155
        xlStDevP	標準偏差	-4156
        xlSum	合計	-4157
        xlVar	標本分散	-4164
        xlVarP	分散	-4165

  • TopRow
    • 省略可能
    • 統合の基準に上端行の列見出しを利用する場合は、True を指定します。
      データの位置に基づく場合は、False を指定します。既定値は False です。
  • LeftColumn
    • 省略可能
    • 統合の基準に左端列の行見出しを利用する場合は、True を指定します。
      データの位置に基づく場合は、False を指定します。既定値は False です。
  • CreateLinks
    • 省略可能
    • 統合を行うとき、ワークシート リンクを使う場合は、True を指定します。
      データをコピーする場合は、False を指定します。既定値は False です。

統合の例1：上端行・左端列の各見出しを基準に統合する　　　Topへ

• 上端行・左端列の各見出しを基準に統合を行います。
• データシート「1学期」〜「3学期」のデータの合計を「集計」シートに統合します。
  なお、各データシートは順位順に並んでいます(氏名の順序はバラバラです)。
  • シート「1学期」
    
  • シート「2学期」
    
  • シート「3学期」
    
• マクロのコード例
  統合元のセルはB1:G100、統合先は集計シートのB1としています。
  

  Sub REI23_01() 　　With Worksheets("集計") 　　　　.Range("A:I").ClearContents 　　　　.Range("B1").Consolidate _ 　　　　　　Sources:=Array("'[Book2]1学期'!R1C2:R100C7", _ 　　　　　　　　　　"'[Book2]2学期'!R1C2:R100C7", _ 　　　　　　　　　　"'[Book2]3学期'!R1C2:R100C7"), _ 　　　　　　Function:=xlSum, _ 　　　　　　TopRow:=True, LeftColumn:=True, _ 　　　　　　CreateLinks:=False 　　End With End Sub

• 実行後
  
• コードの追加
  A1,B1セルの見出しを入力し、出席番号を入力します。
  データを合計の大きい順に並べ替えます。
  

  Sub REI23_02() 　　Dim myVal 　　Dim i As Long, c As Range 　　　　With Worksheets("集計") 　　　　　　.Range("A:I").ClearContents 　　　　　　.Range("B1").Consolidate _ 　　　　　　　　Sources:=Array("'[Book2]1学期'!R1C2:R100C7", _ 　　　　　　　　　　　　"'[Book2]2学期'!R1C2:R100C7", _ 　　　　　　　　　　　　"'[Book2]3学期'!R1C2:R100C7"), _ 　　　　　　　　Function:=xlSum, _ 　　　　　　　　TopRow:=True, LeftColumn:=True, _ 　　　　　　　　CreateLinks:=False 　　　　　　'---データ補充 　　　　　　.Range("A1").Value = "出席番号" 　　　　　　.Range("B1").Value = "氏名" 　　　　End With 　　　　'---出席番号の入力 　　　　With Worksheets("1学期") 　　　　　　myVal = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value 　　　　End With 　　　　With Worksheets("集計") 　　　　　　For Each c In .Range("B2", .Range("B" & Rows.Count).End(xlUp)) 　　　　　　　　For i = LBound(myVal) To UBound(myVal) 　　　　　　　　　　If c.Value = myVal(i, 2) Then 　　　　　　　　　　　　c.Offset(0, -1).Value = myVal(i, 1) 　　　　　　　　　　End If 　　　　　　　　Next i 　　　　　　Next c 　　　　'---並べ替え 　　　　　　.Range("A1", .Range("G" & Rows.Count).End(xlUp)).Sort _ 　　　　　　　　Key1:=Range("G2"), _ 　　　　　　　　Order1:=xlDescending, _ 　　　　　　　　Header:=xlGuess, _ 　　　　　　　　OrderCustom:=1 　　　　End With End Sub

• 実行結果
  

統合の例2：セル位置を基準に統合する　　　Topへ

• 各シートのセル位置が決まっている場合、列見出し、行見出しが同じである場合の例です。
• 「集計」シートにはあらかじめ列見出し、行見出しを入力しておきます。
  
• マクロのコード例
  統合元のセルはC2:G100、統合先は集計シートのC2としています。
  

  Sub REI23_03() 　　With Worksheets("集計") 　　　　.Range("C2:I100").ClearContents 　　　　.Range("C2").Consolidate _ 　　　　Sources:=Array("'[Book2]1学期'!R2C3:R100C7", _ 　　　　　　"'[Book2]2学期'!R2C3:R100C7", _ 　　　　　　"'[Book2]3学期'!R2C3:R100C7"), _ 　　　　Function:=xlSum, _ 　　　　TopRow:=False, LeftColumn:=False, _ 　　　　CreateLinks:=False 　　End With End Sub

• 実行結果
  なお、結果の値は、元データが上の例1と異なっています。
  

スポンサードリンク

PageViewCounter
　
　Since2006/2/27