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

スポンサードリンク

更新:2024/11/15;作成: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. シート「第1四半期」の数量の合計を求める C3:C8セルを選択します。
2. [ホーム]タブの[オートSUM]ボタンをクリックします。
  ショートカットキーは　[Alt]+[Shift]+[=] です。
  
  C3セルには　=SUM()　と入力されます。
3. シート「4月」のC3セルを選択します。
  数式バーには =SUM('4月'!C3) と表示されます。
4. 3-D集計する(串刺し集計する)最後のシートを　[Shift]キーを押した状態で、シート見出しの「6月」をクリックします。
  数式バーには　=SUM('4月:6月'!C3) と表示されます。
5. ここで、[ホーム]タブの[オートSUM]ボタンをクリックします。
  または、[Ctrl]+[Enter]キーを押します。
6. シート「第1四半期」の　C3:C8セルの集計ができました。
  - [Enter]キーで確定すると、1つのセルしか数式が入力されません。
    
    C3セルを選択して、フィルハンドルをC8セルまでドラッグしてコピーします。
7. シート「第1四半期」のE3:E8セルの合計金額も同様に串刺し集計で求めます。

問題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月'!$B$3:$B$17,集計!$B3,'1月'!$E$3:$E$17)　として、下方向へ数式をコピーします。
D3セルの数式は　=SUMIF('2月'!$B$3:$B$17,集計!$B3,'2月'!$E$3:$E$17)
E3セルの数式は　=SUMIF('3月'!$B$3:$B$17,集計!$B3,'3月'!$E$3:$E$17)
関数の挿入を使った場合は　下図のようになります。
範囲に　'1月'!$B$3:$B$17、検索条件に　集計!$B3、合計範囲に　'1月'!$E$3:$E$17　と入力します。

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月'!$B$3:$B$17,集計!$B3,'1月'!$E$3:$E$17
をセル参照して
=SUMIF(C2&"!$B$3:$B$17",集計!$B3,C2&"!$E$3:$E$17")
とできそうに思います。
ところが、このような参照の仕方はできないとの警告が表示されます。
よって、INDIRECT関数を使って参照先を指定します。
なお、C2の部分は複合参照の C$2　として、コピー時にも対応できるようにします。
=SUMIF(INDIRECT(C$2&"!$B$3:$B$17"),集計!$B3,INDIRECT(C$2&"!$E$3:$E$17"))
この数式を右方向、下方向へコピーします。
- また、セル範囲は　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