Excelでは、異なる複数のシートの合計を簡単に求めることができます。
3つの方法を紹介します。
また、集計するシートが増減する場合の方法も解説します。
この記事の目次
集計する表の確認
ただし、同じ形の表であることが前提となります。
たとえば下のような4つの表があります。
シート名[東京]です。
シート名[大阪]です。
シート名[名古屋]です。
シート名[福岡]です。
参考上記のような同じ形式の表を作成する場合は、作業グループの設定を行うと効率的です。
-
複数のワークシートに同時に書式設定(グループ設定の活用)
Excelでは、同じ形式のワークシートを複数作成することはよくあります。月別や県別、支店別などシートに分けて管理されることは多いですね。 そ ...
合計値を求めたい範囲を選択して複数シートの合計を求める方法
[集計]シートを追加します。
同じレイアウトのシートです。
集計シートの合計値を求めたい範囲を選択して集計する方法です。
[集計]シートで合計値を求めたい範囲を選択します。以下の表の場合、セル[B2]からセル[E4]までを選択します。
集計シートにSUM関数を入力
[ホーム]タブの[編集]グループにある[オートSUM]ボタンをクリックします。
入力対象になっているセル(ここでは、セル[B2])に数式[=SUM()]が入力されます。
ショートカットキーは、[Alt]+[Shift]+[=]です。
集計したい左端のシートを選択して合計を求めたい左上のセルをクリック
一番左端にあるシート見出し[東京]をクリックして、セル[B2]をクリックします。
数式バーは、以下のようになります。
=SUM(東京!B2)
[Shift]キーを押して集計したい最後のシート見出しを選択
[Shift]キーを押したまま、集計を求める最後の[福岡]シート見出しをクリックします。
数式バーの数式が以下のように変わります。
=SUM('東京:福岡'!B2)
[オートSUM]ボタンをクリック
再度、[ホーム]タブの[編集]グループの[オートSUM]ボタンをクリックします。
あるいは、[Ctrl]キーと[Enter]キーを押します。
これで[集計]シートに[東京]、[大阪]、[名古屋]、[福岡]シートの各セルの合計値が表示されます。
参考ここまでの操作は、Excel2003でも解説しています。この集計方法は、[串刺し計算]とも呼ばれています。
-
串刺し計算(同じレイアウトの異なるシート間の集計)
同じレイアウトの表であれば異なるシート間でも簡単に集計できます。 串刺し計算と呼ばれます。または、3D参照とも呼ばれます。 この記事の目次1 ...
1つのセルに複数シートの合計値を求めた後に数式をコピーする方法
[集計]シートを追加します。
同じレイアウトのシートです。
[集計]シートのセル[B2]を選択して、[オートSUM]ボタンをクリックします。
集計したいシートの左端のシートを選択して合計を求めたい左上のセルをクリック
一番左端にあるシート見出し[東京]をクリックして、セル[B2]をクリックします。
数式バーは、以下のようになります。
=SUM(東京!B2)
[Shift]キーを押して集計したい最後のシート見出しをクリック
[Shift]キーを押したまま、集計を求める最後の[福岡]シート見出しをクリックします。
数式バーの表示が以下のように変わります。
=SUM('東京:福岡'!B2)
[オートSUM]ボタンをクリック
シートを切り替えたりせず、このまま、再度、[ホーム]タブの[オートSUM]ボタンをクリックします。
あるいは、[Enter]キーを押します。
[集計]シートのセル[B2]に合計値が表示されます。
数式のコピー
後は、オートフィルで数式をコピーします。
セル[B2]のフィルハンドルをセル[C2]からセル[E2]までドラッグして、セル[E2]ではフィルハンドルをダブルクリックすると効率的です。
すべてのセルに数式がコピーされ、合計値が表示されます。
参考フィルハンドルのダブルクリックについては、以下の記事で解説しています。
-
フィルハンドルのダブルクリックで隣接する列の連続した行まで入力
オートフィルハンドルをダブルクリックすると、隣接する列の連続した行までまとめて入力することができます。 ただし、この操作は列に対してのみです ...
シート数に増減がある場合
シートの数に増減がある場合は、新規シートを2枚追加します。シート名は仮に[start]と[end]とします。
そして、この2枚のシートの間に集計したいシートを挟んで上記操作を行います。
そうすると、[集計]シートの各セルの数式は以下のようになります。
すでにシートの集計を行っている場合は、左端上のセルを修正してコピーすればOKです。
=SUM(start:end!B2)
次回からは、シートを[start]シートと[end]シートの間に追加するだけでOKです。シートを削除しても自動的に集計されます。
シートの非表示
[start]シートと[end]シートは、非表示にしておいてもいいと思います。
[start]シートを選択した後、[Ctrl]キーを押したまま[end]シートを選択して、右クリックします。
ショートカットメニューから[非表示]をクリックします。
参考シートの再表示は、Microsoft 365のExcel と Excel 2021では、まとめて再表示ができるようになっています。
[統合]機能を使って集計
[データ]タブにある[統合]という機能を使って複数シートの合計を求める方法です。
注意この場合の方法は、各シートのセルの値が変更されても合計値は変わりません。
数式ではなく、値のみの表示になります。
設定によって、データをリンクする方法もあります。
[データ]タブの[統合]ボタンをクリック
[集計]シートのセル[B2]をアクティブにして[データ]タブの[データツール]グループにある[統合]ボタンをクリックします。
統合の設定
[統合の設定]ダイアログボックスが表示されます。
[集計の方法]はメニューから[合計]を選択します。
[統合元範囲]の[ダイアログボックス拡大/縮小]ボタンをクリックします。
[東京]シートを選択して、セル[B2]からセル[E4]までを範囲選択します。
下のようになったら、再度[ダイアログボックス拡大/縮小]ボタンをクリックします。
[統合の設定]ダイアログボックスに戻ります。
[追加]ボタンをクリックして、[統合元]へ追加します。
同様にして、[大阪]シート、[名古屋]シート、[福岡]シートのセル範囲を追加していきます。
[統合の設定]ダイアログボックスの[統合元]に4つのシートのセル範囲が表示されていること確認して[OK]ボタンをクリックします。
これで[集計]シートに各シートの同一セルの合計値が表示されます。
ただし、この場合は、値のみです。
数式バーを見ていただくと分かると思いますが、数式バーにはセル[B2]の値のみが表示されています。
数式ではないので、データ元の値が変更されても[集計]シートの値は変更されません。
統合元データをリンクする
もし、[統合]機能を使ったときに、データをリンクしたい場合は、統合元に各シートのセル範囲を追加した後に、[統合元データとリンクする]のチェックボックスをオンにして、[OK]ボタンをクリックします。
こうすると、データがリンクされているので、各ワークシートのセルの値が変わると、集計値も変わります。
関連複数の表で、項目や位置が一致しない場合でも1つのワークシートに集計することができます。
以下は、Excel2003で解説しています。
-
データの統合(複数のブックやシートのデータを集計)
複数の同じレイアウトの表の合計は串刺し計算が便利ですが、複数のブックやシートの表で、項目の数や位置が一致しない場合でも 1つのワークシートに ...
以下の記事では、統合するワークシートの項目名の数と並び順が一致している場合と、異なる場合の方法を分けて解説しています。(Excel2016)
-
データの統合(表の項目名の数と並び順が一致している場合)
Excelには複数の表を統合してくれるデータの統合という機能があります。 各店舗や各支店の異なるブックのデータをまとめたり、複数のワークシー ...
-
データの統合(表の項目数と並び順が異なる場合)
Excelのデータ統合という機能を使うと、複数シートの項目名の数や並び順が違っていても1つのワークシートにまとめることができます。 統合には ...
ピボットテーブルウィザードを使用して複数のマトリックス表からテーブルやピボットテーブルを作成することができます。
-
複数のマトリックス表(クロス集計)をピボットテーブルウィザードで統合
複数のマトリックス表(クロス表)をピボットテーブルウィザードで統合する方法です。 [データの統合]という機能を使って、合計を求めたりすること ...
Excel2010以降であれば、Power Queryを使って複数シートを結合してピボットテーブルを作成することができます。
以下の記事では、この記事で使用しているサンプルと同じものを使って解説しています。
-
複数のマトリックス表をPower Queryでピボットテーブルに変換
Power Queryを使って、複数のマトリックス表を結合して1つのピボットテーブルに変換することができます。一度、ピボットテーブルを作成す ...