1. Home » 
2. エクセル練習問題：目次 » 
3. クロス集計表の作成

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

更新:2024/4/12;作成2012/3/1

問題1の解答例　　　　topへ

• 	B	C	D	E
  2		バナナ	みかん	りんご
  3	井上商事	49,000	78,000	97,000
  4	上田青果	57,000	43,000	145,000

SUMIFS関数で求める

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

   • 構文：SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
   • SUMIFS関数の説明は　SUMIFS関数　もご覧ください。
2. C3セルに　=SUMIFS(Sheet1!$D$3:$D$10,Sheet1!$B$3:$B$10,$B3,Sheet1!$C$3:$C$10,C$2)　と入力します。
   条件1の　$B3　と条件2の　C$2　は列または行の複合参照になります。(列または行の片方だけを固定します)
   他のセルにこの数式をコピーして完成です。
   
3. C3セルに関数の引数を使って入力する場合は、合計対象範囲に　Sheet1!$D$3:$D$10　、条件範囲1に　Sheet1!$B$3:$B$10　、条件1に　$B3　、条件範囲2に　Sheet1!$C$3:$C$10　、条件2に　C$2　と入力します。
   

SUMIFS関数を使う(その2：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で使用できるもっとも新しい関数の　TOROW関数を使うこともできます。
  • 配列を1行・1列にするTOROW関数・TOCOL関数の使い方
  

FILTER関数を使って求める

• Microsoft365ではFILTER関数を使うことができます。
• C3セルに　=SUM(FILTER(Sheet1!$D$3:$D$10,(Sheet1!$B$3:$B$10=Sheet2!$B3)*(Sheet1!$C$3:$C$10=Sheet2!C$2)))　と入力します。
  数式をE3セルまでコピーし、さらにE4セルまでコピーします。
  

SUMPRODUCT関数で求める

• 数式の例：Excel2003以前ではSUMIFS関数を使うことができないので、SUMPRODUCT関数を利用します。
  ただし、データ量が多くなるとファイルが重くなります。

  • 構文：=SUMPRODUCT(配列1,[配列2],[配列3],・・・)
    SUMPRODUCT関数の使い方は　SUMPRODUCT関数の使い方　をご覧ください。
• C3セルに　=SUMPRODUCT((Sheet1!$B$3:$B$10=$B3)*(Sheet1!$C$3:$C$10=C$2),Sheet1!$D$3:$D$10)　と入力します。
  

• C3セルに関数の引数を使って入力する場合は、配列1に(Sheet1!$B$3:$B$10=$B3)*(Sheet1!$C$3:$C$10=C$2)　、配列2に　Sheet1!$D$3:$D$10　と入力します。
  配列1の　(Sheet1!$B$3:$B$10=$B3) と (Sheet1!$C$3:$C$10=C$2) はともに論理式なのでTRUE/FALSE が返されます。SUMPRODUCT関数ではこの論理値は 0 として扱われるので、(Sheet1!$B$3:$B$10=$B3) * (Sheet1!$C$3:$C$10=C$2)　として 1/0　となるようにします。(四則演算では　TRUE/FALSE　は　1/0 として計算されるます。)
  

問題2の解答例　　　　topへ

• Excel2010は　ピボットテーブルの作成方法 に説明があります。
  Excel2007は　ピボットテーブル　、Excel2003以前は　ピボットテーブル　をご覧ください。
• ピボットテーブルの作成例（Excel2010で説明しています）
  1. Sheet1のB2:D10セルを選択します。
  2. ［挿入］タブの［ピボットテーブル］を実行します。
  3. テーブル/範囲には線テクしていた範囲が入力されているのを確認します。
     
  4. ピボットテーブルのフィールドリストで下図のように各フィールドを配置します。
     
  5. クロス集計表ができました。
     
  6. 数値のセルに「桁区切りスタイル」を設定します。
     

スポンサードリンク

PageViewCounter

Since2006/2/27