Home »
エクセル練習問題:目次 »
複数のシートのデータを集計する

複数のシートのデータを集計する:エクセル練習問題

スポンサードリンク

(追記) (追記ここまで)

更新:2025年9月14日;作成:2014年11月28日

問題 topへ

問題1:下表のような「4月」「5月」「6月」のデータをシート「第1四半期」に集計しなさい。問題1の解答
- シート「4月」
  
  B C D E
  
  2 型番数量単価金額
  
  3 A-001B 835 20 16,700
  
  4 A-001W 3,521 30 105,630
  
  5 B-022B 9,874 40 394,960
  
  6 B-033W 6,869 50 343,450
  
  7 C-105B 2,046 60 122,760
  
  8 C-105W 3,904 70 273,280
- シート「5月」
  
  B C D E
  
  2 型番数量単価金額
  
  3 A-001B 2,393 20 47,860
  
  4 A-001W 4,302 30 129,060
  
  5 B-022B 4,174 40 166,960
  
  6 B-033W 2,615 50 130,750
  
  7 C-105B 3,583 60 214,980
  
  8 C-105W 6,677 70 467,390
- シート「6月」
  
  B C D E
  
  2 型番数量単価金額
  
  3 A-001B 8,997 20 179,940
  
  4 A-001W 6,830 30 204,900
  
  5 B-022B 4,216 40 168,640
  
  6 B-033W 3,706 50 185,300
  
  7 C-105B 3,926 60 235,560
  
  8 C-105W 8,849 70 619,430
- シート「第1四半期」
  
  B C D E
  
  2 型番数量単価金額
  
  3 A-001B 20
  
  4 A-001W 30
  
  5 B-022B 40
  
  6 B-033W 50
  
  7 C-105B 60
  
  8 C-105W 70

問題2:下図のような複数のシート「1月」「2月」「3月」のデータをシート「集計」のような形式で集計しなさい。問題2の解答
- 求める集計シートは下図のようなものです。
  C3:E8セルの値を求めなさい。
- シート「1月」のデータ
  
  A B C D E
  
  月日品名数量単価金額
  
  2024年1月10日 A-001B 44 120 5,280
  
  2024年1月11日 A-001W 58 130 7,540
  
  2024年1月12日 B-022B 38 210 7,980
  
  2024年1月13日 B-033W 54 305 16,470
  
  2024年1月14日 A-002B 7 150 1,050
  
  2024年1月15日 A-002W 84 160 13,440
  
  2024年1月16日 A-001B 22 120 2,640
  
  2024年1月17日 A-001W 91 130 11,830
  
  2024年1月18日 B-022B 61 210 12,810
  
  2024年1月19日 B-033W 26 305 7,930
  
  2024年1月20日 A-002B 90 150 13,500
  
  2024年1月21日 A-002W 59 160 9,440
  
  2024年1月22日 A-001B 18 120 2,160
  
  2024年1月23日 A-001W 11 130 1,430
  
  2024年1月24日 B-022B 82 210 17,220
- シート「2月」のデータ
  
  A B C D E
  
  月日品名数量単価金額
  
  2024年2月1日 B-022B 69 210 14,490
  
  2024年2月2日 B-033W 85 305 25,925
  
  2024年2月3日 A-001W 73 130 9,490
  
  2024年2月4日 A-002W 62 160 9,920
  
  2024年2月5日 A-001B 68 120 8,160
  
  2024年2月6日 A-002B 44 150 6,600
  
  2024年2月7日 B-022B 31 210 6,510
  
  2024年2月8日 B-033W 36 305 10,980
  
  2024年2月9日 A-001W 79 130 10,270
  
  2024年2月10日 A-002W 33 160 5,280
  
  2024年2月11日 A-001B 52 120 6,240
  
  2024年2月12日 A-002B 71 150 10,650
  
  2024年2月13日 B-022B 34 210 7,140
- シート「3月」のデータ
  
  A B C D E
  
  月日品名数量単価金額
  
  2024年3月1日 B-033W 53 305 16,165
  
  2024年3月2日 A-002W 79 160 12,640
  
  2024年3月3日 A-002B 95 150 14,250
  
  2024年3月4日 A-001B 93 120 11,160
  
  2024年3月5日 A-001W 93 130 12,090
  
  2024年3月6日 B-022B 65 210 13,650
  
  2024年3月7日 B-033W 39 305 11,895
  
  2024年3月8日 A-002W 76 160 12,160
  
  2024年3月9日 A-002B 90 150 13,500
  
  2024年3月10日 A-001B 43 120 5,160
  
  2024年3月11日 A-001W 14 130 1,820
  
  2024年3月12日 B-022B 36 210 7,560
  
  2024年3月13日 B-033W 81 305 24,705
  
  2024年3月14日 A-002W 82 160 13,120

問題1の解答例 topへ

解答:シート「第1四半期」が下図のようになれば正解です。
「4月」～「6月」と「第1四半期」の行と列の各項目が同じであるのに気づきます。
各シートの同じ位置のセル値を合計して求めることができます。つまり、3-D集計(串刺し集計)が可能な状態です。

3-D集計(串刺し集計)で計算する

シート「第1四半期」の数量の合計を求める C3:C8セルを選択します。
[ホーム]タブの編集グループの[Σ オートSUM]ボタンをクリックします。
ショートカットキーは [Alt]+[Shift]+[=] です。

C3セルには =SUM() と入力されます。
シート「4月」のC3セルを選択します。
数式バーには =SUM('4月'!C3) と表示されます。
3-D集計する(串刺し集計する)最後のシートを [Shift]キーを押した状態で、シート見出しの「6月」をクリックします。
数式バーには =SUM('4月:6月'!C3) と表示されます。
ここで、[ホーム]タブの[オートSUM]ボタンをクリックします。
または、[Ctrl]+[Enter]キーを押して、選択範囲に一気に入力します。
シート「第1四半期」の C3:C8セルの集計ができました。
- [Enter]キーで確定すると、1つのセルしか数式が入力されません。
  
  ↓
  C3セルを選択して、フィルハンドルをC8セルまでドラッグしてコピーします。
シート「第1四半期」のE3:E8セルの合計金額も同様に串刺し集計で求めます。

GROUPBY関数を使う方法

G3セルに
=GROUPBY(CHOOSECOLS(VSTACK('4月'!B3:E8,'5月'!B3:E8,'6月'!B3:E8),{1,3}),
CHOOSECOLS(VSTACK('4月'!B3:E8,'5月'!B3:E8,'6月'!B3:E8),{2,4}),
SUM)
と入力しています。
数量と単価の並び順が逆になっていますが、気にしないことにします。
- Vstack関数で計算するデータを盾に結合して計算しています。
  GROUPBY関数の引数の行データにCHOOSECOLS関数で 1,3列を指定して、値にCHOOSECOLS関数で 2,4列を指定しています。
並び順を同じにするには、数式を値に直して入れ替える方法が考えられます。
Ctrl+Cでコピーして、Ctrl+Shift+Vで値の貼り付けを行います。

↓
H2:H9を選択して、[Shift]キーを押しながらドラッグして移動します。

↓
列の順が同じ並び順になりました。

問題2の解答例 topへ

シート名と求めたいデータリストの列見出し部分が同じ文字になっているのに気付くかがポイントでもあります。

PIVOTBY関数を使って求める

SUMIF関数を使って求める SUMIFS関数を使って求める

INDIRECT関数を使って求める

統合機能を使って複数シートを集計するピボットテーブル機能を使って複数シートを集計する

PIVOTBY関数を使って求める

Excel for Microsoft365で利用できる PIVOTBY関数で計算することができます。
以下の数式をB2セルに入力しています。
=PIVOTBY(CHOOSECOLS(VSTACK('1月'!B2:F17,'2月'!B3:F15,'3月'!B3:F16),2),
MONTH(CHOOSECOLS(VSTACK('1月'!B2:F17,'2月'!B3:F15,'3月'!B3:F16),1))&"月",
CHOOSECOLS(VSTACK('1月'!B2:F17,'2月'!B3:F15,'3月'!B3:F16),5),
SUM)
詳細な使い方は PIVOTBY関数でクロス集計表を作るをご覧ください。
- 1月～3月のデータを1つにまとめます。
  VSTACK('1月'!B2:F17,'2月'!B3:F15,'3月'!B3:F16)
- PIVOTBY関数で計算しています。
  行のデータは CHOOSECOLS(VSTACK('1月'!B2:F17,'2月'!B3:F15,'3月'!B3:F16),2)
  列のデータに MONTH(CHOOSECOLS(VSTACK('1月'!B2:F17,'2月'!B3:F15,'3月'!B3:F16),1))&"月"
  値に CHOOSECOLS(VSTACK('1月'!B2:F17,'2月'!B3:F15,'3月'!B3:F16),5)
  計算方法は SUM
  としています。

SUMIF関数を使って求める

別シートのデータから同じ品名の値だけを合計するので、SUMIF関数で求めることができます。
SUMIF関数の構文は =SUMIF(検索範囲,検索条件,合計範囲) となります。
SUMIF関数の詳しい使い方は条件付きで合計する関数(SUMIF関数)の使い方) をご覧ください。
C3セルの数式は =SUMIF('1月'!$B3ドル:$B17,ドル集計!$B3,'1月'!$E3ドル:$E17ドル) として、下方向へ数式をコピーします。
D3セルの数式は =SUMIF('2月'!$B3ドル:$B17,ドル集計!$B3,'2月'!$E3ドル:$E17ドル)
E3セルの数式は =SUMIF('3月'!$B3ドル:$B17,ドル集計!$B3,'3月'!$E3ドル:$E17ドル)
関数の挿入を使った場合は下図のようになります。
範囲に '1月'!$B3ドル:$B17ドル、検索条件に集計!$B3、合計範囲に '1月'!$E3ドル:$E17ドルと入力します。

SUMIFS関数を使って求める

SUMIFS関数で求めることができます。Excel2007で追加された関数です。
SUMIFS関数の構文は SUMIFS(合計対象範囲,検索条件範囲1,検索条件1,[検索条件範囲2],[検索条件2]...) となります。
SUMIFS関数の詳しい使い方は複数の条件で合計する関数(SUMIFS関数)の使い方をご覧ください。
ここではSpill(スピル)の機能を利用しています。
Spill(スピル)の機能はExcel for Microsoft365、Excel2021で使用できます。
C3セル =SUMIFS('1月'!E3:E17,'1月'!B3:B17,集計!B3:B8)
D3セル =SUMIFS('2月'!E3:E15,'2月'!B3:B15,集計!B3:B8)
E3セル =SUMIFS('3月'!E3:E16,'3月'!B3:B16,集計!B3:B8)

INDIRECT関数を使って求める

これらの数式で 1月、2月、3月といったシート名が集計するセルの3行目と同じことに気付くと・・・
=SUMIF('1月'!$B3ドル:$B17,ドル集計!$B3,'1月'!$E3ドル:$E17ドル
をセル参照して
=SUMIF(C2&"!$B3ドル:$B17ドル",集計!$B3,C2&"!$E3ドル:$E17ドル")
とできそうに思います。
ところが、このような参照の仕方はできないとの警告が表示されます。
よって、INDIRECT関数を使って参照先を指定します。
なお、C2の部分は複合参照の C2ドルとして、コピー時にも対応できるようにします。
=SUMIF(INDIRECT(C2ドル&"!$B3ドル:$B17ドル"),集計!$B3,INDIRECT(C2ドル&"!$E3ドル:$E17ドル"))
この数式を右方向、下方向へコピーします。
- また、セル範囲は B3:B17 と E3:E17としています。これは「1月」～「3月」のデータの最も多いところに合わせています。
  つまり、「1月」シートが最も多くのデータが入力されているので、このセル範囲に合わせています。

統合機能を使って複数シートを集計する

統合機能を使うために1月～3月のシートの列見出し「金額」をそれぞれ「1月」「2月」「3月」と変更します。
集計シートの集計先のシート範囲 B2:E8 を選択します。
[データ]タブの[統合]をクリックします。
統合の設定ダイアログボックスが表示されます。
集計の方法で「合計」を選択します。
統合元範囲のボックス内をクリックしてカーソルを表示して、シート「1月」のB2:E17セルを選択して、[追加]ボタンをクリックし、統合元へセル範囲を登録します。
「2月」「3月」のシートでも同様に、統合元範囲のボックス内をクリックしてカーソルを表示して、データ範囲を選択後、[追加]ボタンをクリックし、統合元へセル範囲を登録します。
ここのポイントは選択するセル範囲の左端が B列の品名の列になっていることです。
統合の基準の上端行と左端列のチェックを入れます。
[OK]ボタンをクリックします。
月別の複数シートの集計ができました。
ちなみに、数式で計算していないのでC3セルには数値が表示されています。

ピボットテーブル機能を使って複数シートを集計する

データを1つのシートにまとめます。
新しいシートに各シートのデータをコピーして貼り付けます。
[挿入]タブの[ピボットテーブル]をクリックします。
テーブル/範囲にコピーしたデータ範囲を指定します。
「既存のワークシート」にチェックを入れて、I3セルを指定しました。
[OK]ボタンをクリックします。
列に「月日」をドラッグします。Excel2016以降では自動で日時のグループ化が行われます。
行に「品名」をドラッグします。
Σ値に「金額」をドラッグします。
- Excel 2016ではピボットテーブルで日時の自動グループ化が行われるようになりました
- Excel2013以前のバージョンの場合はピボットテーブルの日付のグループ化をご覧ください。
ピボットテーブルで集計ができました。
デザインタブでデザインを変更しました。

スポンサードリンク
(追記) (追記ここまで)

(追記) (追記ここまで)

Home|エクセル練習問題:目次|複数のシートのデータを集計する

PageViewCounter
Counter
Since2006/2/27