EXCEL VBA 複数のワークシート集計・異なるワークシートの串刺し集計（統合：Consolidate・異なる縦軸・横軸集計）

2021年11月13日

さ

EXCEL VBA 複数のワークシート集計・異なるワークシートの串刺し集計（統合：Consolidate・異なる縦軸・横軸集計）

●はじめに

今回説明するのは、複数のワークシートを集計するConsolidateメゾットの利用方法を説明いたします。通常EXCELで複数シートを串刺し集計を行う場合は、同じ形の表を串刺しする必要がありますが、Consolidateメゾットのパラメーターを利用する事で、縦軸・横軸に異なる項目や並び順が違っていても串刺し集計する事ができます。複数のワークシートデータを一つのシートへデータを纏める・集計する場合は、とても便利な機能だとおもいます。それでは、サンプルプログラムを交えて順番に説明致します。

●【EXCE VBAセル範囲の串刺し集計・計算・カウント・平均】については、下記を参照して下さい】

EXCEL VBA セル範囲の串刺し集計・計算・カウント・平均（テクニック）

●【Consolidateメソッド (Excel)（Microsoft様）、下記を参照して下さい】

https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.consolidate

●書式の説明

●　Consolidateメゾットを利用するには、下記の通りに設定を行います。

●Function：種類一覧

Function（名前）	説明
xlsum	合計値
xlAverage	平均値
xlcount	カウント
xlCountNums	数値のみカウント
xlmax	最大値
xlmin	最小値
xlproduct	積

●Consolidateメソッド・パラメーター一覧

パラメーター	データ型	説明
Sources	Variant	統合（集計）元範囲を参照する、R1C1 形式の文字列を指定します。指定するセル参照には、必ず統合するシートのパスを含めます。また、複数シートを選択する事も可能。
Function	Variant	XlConsolidationFunction の定数の1つで、統合（集計）の種類を指定します。
TopRow	Variant	統合（集計）を行うとき、統合する範囲の上端行に入力されている列見出しに基づく場合は、 True を指定します。データの位置に基づく場合は、 False を指定します。既定値は False です。
LeftColumn	Variant	統合（集計）を行うとき、統合する範囲の左端列に入力されている行見出しに基づく場合は、 True を指定します。データの位置に基づく場合は、 False を指定します。既定値は False です。
CreateLinks	Variant	統合を行うとき、ワークシートリンクを使う場合は、 True を指定します。データをコピーする場合は、 False を指定します。既定値は False です。

【使用例】
●下記の記述例では、【Sheet２】と【Sheet３】のデータをSUM関数でSheet1に統合（集計）します。

’
’
Worksheets("Sheet1").Range("A1").Consolidate Sources:=Array("Sheet2!R1C1:R3C3", "Sheet3!R1C1:R3C3"), Function:=xlSum , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=True

’

’

Worksheets("Sheet1").Range("A1").Consolidate Sources:=Array("Sheet2!R1C1:R3C3", "Sheet3!R1C1:R3C3"), Function:=xlSum , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=True

’

●上記パラメーター設定説明
・Sources：Sheet２のセルA1：C３・Sheet３のセルA1:C3の範囲
・Function:= xlsum　（合計値）
・TopRow:= Ture (列見出しを利用します)　　※Trueは、利用する・Falseは、利用しない。
・LeftColumn:＝Ture（行見出しを利用します）　※Trueは、利用する・Falseは、利用しない。
・CreateLinks:=True （ワークシートリンクを利用します）　※Trueは、利用する・Falseは、利用しない。

●　パラメーター【Sources】に参照元のセル範囲を指定する場合は、R1C1形式で設定します。
例：【R1C1】形式　⇒　R=行　C＝列を意味します。　R1C1＝”A1″　　R２C2＝”B2″　 R10C10＝”J10″

EXCEL VBA 複数のワークシート集計・異なるワークシートの串刺し集計①　見出し設定なし

●プログラム説明　(Consolidateメソッド：サンプル①)

下記のサンプルプログラムは、複数のワークシートを集計するConsolidateメソッドの利用方法を説明します。

●下記パラメーター設定説明
・Sources：Sheet２のセルA1：J10・Sheet３のセルA1:J10の範囲
・Function:= xlsum　（合計値）
・TopRow:= False (列見出しを利用しない)　　
・LeftColumn:＝False（行見出しを利用しない）
・CreateLinks:=False （ワークシートリンクを利用しない）

★注意：今回の設定では、列行共に見出し設定をしていないので、項目位置を無視して串刺し集計を行っています。

【プログラムの流れ】
①転記先のSheet１データを全てクリアします。
②Sheet２とSheet３のデータをSheet１へ統合（集計：Sum）します。

'
'
Sub Consolidate01() '統合（集計）に上端行の列見出し：False・左端列の行見出し：Falseの設定
    With Worksheets("Sheet1")
        .Cells.Clear
        .Range("A1").Consolidate _
            Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _
            Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
    End With
    
 End Sub
 '

Sub Consolidate01() '統合（集計）に上端行の列見出し：False・左端列の行見出し：Falseの設定

With Worksheets("Sheet1")

.Cells.Clear

.Range("A1").Consolidate _

Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _

Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False

End With

End Sub

●実行前～実行後　※プログラム実行後、Sheet２＋Sheet３のデータを元にSheet１に統合（集計）されました。上記のプログラムでは、見出しの設定を行っていませんので、同じセル位置にで串刺し集計を行っています。
（画面クリックして拡大）

EXCEL VBA 複数のワークシート集計・異なるワークシートの串刺し集計②　列見出し設定

●プログラム説明　(Consolidateメソッド：サンプル②)

下記のサンプルプログラムは、複数のワークシートを集計するConsolidateメソッドの利用方法を説明します。

●下記パラメーター設定説明
・Sources：Sheet２のセルA1：J10・Sheet３のセルA1:J10の範囲
・Function:= xlsum　（合計値）
・TopRow:= Ture　(列見出しを利用する)　　
・LeftColumn:＝False（行見出しを利用しない）
・CreateLinks:=False （ワークシートリンクを利用しない）

★注意：今回の設定では、列見出し設定のみを行っていますので、列項目の位置を元に串刺し集計を行っています。

【プログラムの流れ】
①転記先のSheet１データを全てクリアします。
②Sheet２とSheet３のデータをSheet１へ統合（集計：Sum）します。

'
'
Sub Consolidate02()   '統合（集計）に上端行の列見出し：True・左端列の行見出し：Falseの設定
    With Worksheets("Sheet1")
        .Cells.Clear
        .Range("A1").Consolidate _
            Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _
            Function:=xlSum, TopRow:=True, LeftColumn:=False, CreateLinks:=False
    End With
    
 End Sub
 '

Sub Consolidate02() '統合（集計）に上端行の列見出し：True・左端列の行見出し：Falseの設定

With Worksheets("Sheet1")

.Cells.Clear

.Range("A1").Consolidate _

Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _

Function:=xlSum, TopRow:=True, LeftColumn:=False, CreateLinks:=False

End With

End Sub

●実行前～実行後　※プログラム実行後、Sheet２＋Sheet３のデータを元にSheet１に統合（集計）されました。上記のプログラムでは、列見出しの設定を行っていますので、列位置にて串刺し集計を行っています。
（画面クリックして拡大）

EXCEL VBA 複数のワークシート集計・異なるワークシートの串刺し集計③　行見出し設定

●プログラム説明　(Consolidateメソッド：サンプル③)

下記のサンプルプログラムは、複数のワークシートを集計するConsolidateメソッドの利用方法を説明します。

●下記パラメーター設定説明
・Sources：Sheet２のセルA1：J10・Sheet３のセルA1:J10の範囲
・Function:= xlsum　（合計値）
・TopRow:= False 　(列見出しを利用しない)　　
・LeftColumn:＝ Ture（行見出しを利用する）
・CreateLinks:=False （ワークシートリンクを利用しない）

★注意：今回の設定では、行見出し設定のみを行っていますので、行項目の位置を元に串刺し集計を行っています。

【プログラムの流れ】
①転記先のSheet１データを全てクリアします。
②Sheet２とSheet３のデータをSheet１へ統合（集計：Sum）します。

'
'
Sub Consolidate03() '統合（集計）に上端行の列見出し：False・左端列の行見出し：Trueの設定

    With Worksheets("Sheet1")
        .Cells.Clear
        .Range("A1").Consolidate _
            Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _
            Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
    End With

 End Sub
 '

Sub Consolidate03() '統合（集計）に上端行の列見出し：False・左端列の行見出し：Trueの設定

With Worksheets("Sheet1")

.Cells.Clear

.Range("A1").Consolidate _

Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _

Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

End With

End Sub

●実行前～実行後　※プログラム実行後、Sheet２＋Sheet３のデータを元にSheet１に統合（集計）されました。上記のプログラムでは、行見出しの設定を行っていますので、行位置にて串刺し集計を行っています。
（画面クリックして拡大）

EXCEL VBA 複数のワークシート集計・異なるワークシートの串刺し集計④　列見出し設定・行見出し設定　※おすすめ！

●プログラム説明　(Consolidateメソッド：サンプル④)

下記のサンプルプログラムは、複数のワークシートを集計するConsolidateメソッドの利用方法を説明します。
※上記に３パターン（サンプル）の説明をいたしましたが、Consolidateメゾットの機能を発揮するのは、このサンプル④の説明になります。列見出し・行見出しを利用する事で、異なる表データも項目に応じにて正確に統合（集計）する事が出来ます。

●下記パラメーター設定説明
・Sources：Sheet２のセルA1：J10・Sheet３のセルA1:J10の範囲
・Function:= xlsum　（合計値）
・TopRow:= Ture 　(列見出しを利用する)　　
・LeftColumn:＝ Ture（行見出しを利用する）
・CreateLinks:=False （ワークシートリンクを利用しない）

★注意：今回の設定では、列見出し・行見出し共に設定を行っていますので、列見出し・行見出し位置を共に串刺し集計を行っています。

【プログラムの流れ】
①転記先のSheet１データを全てクリアします。
②Sheet２とSheet３のデータをSheet１へ統合（集計：Sum）します。

'
'
Sub Consolidate04() '統合（集計）に上端行の列見出し：True・左端列の行見出し：Trueの設定


    With Worksheets("Sheet1")
        .Cells.Clear
        .Range("A1").Consolidate _
            Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _
            Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
    End With

 End Sub
 '

Sub Consolidate04() '統合（集計）に上端行の列見出し：True・左端列の行見出し：Trueの設定

With Worksheets("Sheet1")

.Cells.Clear

.Range("A1").Consolidate _

Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _

Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

End With

End Sub

●実行前～実行後　※プログラム実行後、Sheet２＋Sheet３のデータを元にSheet１に統合（集計）されました。上記のプログラムでは、列見出し・行見出しの設定を行っていますので、列位置・行位置を共に串刺し集計を行っています。
（画面クリックして拡大）

EXCEL VBA 複数のワークシート集計・異なるワークシートの串刺し集計⑤　列見出し設定・行見出し設定・ワークシートリンク設定

●プログラム説明　(Consolidateメソッド：サンプル⑤)

下記のサンプルプログラムは、複数のワークシートを集計するConsolidateメソッドの利用方法を説明します。

●下記パラメーター設定説明
・Sources：Sheet２のセルA1：J10・Sheet３のセルA1:J10の範囲
・Function:= xlsum　（合計値）
・TopRow:= Ture 　(列見出しを利用する)　　
・LeftColumn:＝ Ture（行見出しを利用する）
・CreateLinks:=Ture（ワークシートリンクを利用する）

★注意：今回の設定では、列見出し・行見出し共に設定を行っていますので、列見出し・行見出し位置を共に串刺し集計を行っています。なお、ワークシートリンクの設置も行っています。

【プログラムの流れ】
①転記先のSheet１データを全てクリアします。
②Sheet２とSheet３のデータをSheet１へ統合（集計：Sum）します。

'
'
Sub Consolidate05() '統合（集計）に上端行の列見出し：True・左端列の行見出し：Trueの設定・ワークシートリンク：True

    With Worksheets("Sheet1")
        .Cells.Clear
        .Range("A1").Consolidate _
            Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _
            Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=True
    End With
    
 End Sub
'

Sub Consolidate05() '統合（集計）に上端行の列見出し：True・左端列の行見出し：Trueの設定・ワークシートリンク：True

With Worksheets("Sheet1")

.Cells.Clear

.Range("A1").Consolidate _

Sources:=Array("Sheet2!R1C1:R10C10", "Sheet3!R1C1:R10C10"), _

Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=True

End With

End Sub

●実行前～実行後　※プログラム実行後、Sheet２＋Sheet３のデータを元にSheet１に統合（集計）されました。上記のプログラムでは、列見出し・行見出しの設定を行っていますので、列位置・行位置を共に串刺し集計を行っています。なお、ワークシーリンクの設定を行っていますので、Sheet1に統合された詳細を表示する事ができます。
（画面クリックして拡大）