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

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

問題1の解答例　　　　topへ

• 解答：シート「第1四半期」が下図のようになれば正解です。
  
• 「4月」〜「6月」と「第1四半期」の行と列の各項目が同じであるのに気づきます。
  各シートの同じ位置のセル値を合計して求めることができます。つまり、3-D集計(串刺し集計)が可能な状態です。
  
• 3-D集計(串刺し集計)の操作手順
  1. シート「第1四半期」の数量の合計を求める C3:C8セルを選択します。
     
  2. [ホーム]タブの[オートSUM]ボタンをクリックします。
     
     C3セルには　=SUM()　と入力されます。
     
  3. シート「4月」のC3セルを選択します。
     数式バーには =SUM('4月'!C3) と表示されます。
     
  4. 3-D集計する(串刺し集計する)最後のシートを　{Shift]キーを押した状態で、シート見出しの「6月」をクリックします。
     数式バーには　=SUM('4月:6月'!C3) と表示されます。
     
  5. ここで、[ホーム]タブの[オートSUM]ボタンをクリックします。または、[Ctrl]+[Enter]キーを押します。
     
     シート「第1四半期」の　C3:C8セルの集計ができました。
     
     • [Enter]キーで確定すると、1つのセルしか数式が入力されません。
       
       C3セルを選択して、フィルハンドルをC8セルまでドラッグしてコピーします。
       
  6. シート「第1四半期」のE3:E8セルの合計金額も同様に串刺し集計で求めます。
     

問題2の解答例　　　　topへ

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

SUMIF関数を使って求める

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

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

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

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

スポンサードリンク

PageViewCounter

Since2006/2/27