クロス集計表の作成:エクセル練習問題

スポンサードリンク

更新:2025/9/22;作成2012/3/1

問題　　　　topへ

問題1：Sheet1に下表のデータがあります。Sheet2のクロス集計表を完成しなさい。　　問題1の解答

Sheet1のデータ

	B	C	D
2	販売先	商品名	販売額
3	井上商事	りんご	52,000
4	上田青果	りんご	65,000
5	井上商事	みかん	78,000
6	上田青果	みかん	43,000
7	井上商事	りんご	45,000
8	上田青果	バナナ	57,000
9	井上商事	バナナ	49,000
10	上田青果	りんご	80,000

Sheet2の状態は下表の通りです。ピンクのセルに数式を入れて完成しなさい。

	B	C	D	E
2		バナナ	みかん	りんご
3	井上商事
4	上田青果

問題2：上記のSheet1のデータをピボットテーブルを使ってクロス集計表を作成しなさい。　　問題2の解答

SUMIFS関数を使う(Spillが使える場合)

Excel for Microsoft365、Excel2021以降のバージョンではスピルが利用できます。

Sheet2のC3セルに　=SUMIFS(Sheet1!D3:D10,Sheet1!B3:B10,B3:B4,Sheet1!C3:C10,C2:E2)　と入力します。
数式はC3セルにのみ入力しますので、絶対参照にせず、相対参照でOKです。

なお、列項目や行項目もExcel for Microsoft365、Excel2021で使用できるUNIQUE関数とSORT関数で求めることができます。
B3セルには =SORT(UNIQUE(Sheet1!B3:B10))　と入力しています。

これらの関数の使い方は↓をご覧ください。
UNIQUE関数で重複しない値を取り出す:Excel関数
 SORT関数でデータを並べ替える:Excel関数

C2セルには　=TRANSPOSE( SORT(UNIQUE(Sheet1!C3:C10)))　と入力しています。

行と列を入れ替える関数(TRANSPOSE関数)の使い方:Excel関数

Excel for Microsoft365,Excel2024で使用できる新しい関数の　TOROW関数を使うこともできます。

配列を1行・1列にするTOROW関数・TOCOL関数の使い方

C2セルに　=TOROW( SORT(UNIQUE(Sheet1!C3:C10)))　と入力します。

SUMIFS関数で求める

Excel2019以前のバージョンではSpillが利用できないので、こちらのような方法になります。

数式の例：2条件での合計はSUMIFS関数を使うことができます。（SUMIFS関数はExcel2007以降で使用することができます）

構文：SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
SUMIFS関数の説明は　SUMIFS関数　もご覧ください。

C3:E4セルを選択します。
C3セルに　=SUMIFS(Sheet1!$D$3:$D$10,Sheet1!$B$3:$B$10,$B3,Sheet1!$C$3:$C$10,C$2)　と入力します。
[Ctrl]+[Enter]で入力を完了すると、選択していたセルに一気に数式が入力されます。
条件1の　$B3　と条件2の　C$2　は列または行の複合参照になります。(列または行の片方だけを固定します)