エクセルの関数でクロス集計表を作成したい:エクセル入門編

クロス集計を行いたいときはピボットテーブルを使うと簡単にできますが、ここではエクセルの関数を使ってクロス集計表を作成してみます。

ピボットテーブルで作成したクロス集計表　　　topへ

下図のように、日付、担当者、型番、金額といった売上リストがあります。
ピボットテーブルを使って、2つのクロス集計表を作成しました。これをエクセルの関数を使って求めようと思います。
- 行ラベルに「担当者」、列ラベルに「型番」、値に「金額」の合計を配置してピボットテーブルを作成しています。
- 行ラベルに「日付」、列ラベルに「担当者」、値に「金額」の合計を配置してピボットテーブルを作成しています。
  日付の列は「月」でグループ化しています。

エクセルの関数でクロス集計表を作成する　　　topへ

最初に担当者と型番のクロス集計表を作成してみます。
1. 担当者と型番のリストを入力します。
  手入力でもよいのですが、ここは重複データの削除機能を使います。
  データリストの担当者列をコピーして集計表を作成するシートに貼り付けます。
  下図ではデータが長いので一部分を表示しています。
2. 「データ」タブの[重複の削除]を実行します。
3. 重複しない担当者のリストが作成できました。
4. 「型番」の列を元のデータリストからコピーして、集計表のシートに貼り付けます。
  上記と同様に重複の削除を実行しました。型番の重複しないリストが作成できました。
5. データタブの「昇順に並べ替え」で型番を並べ替えました。
6. 型番をコピーして、右クリックで「行列を入れ替えて貼り付け」を実行しました。
7. クロス集計表の基になる表が作成できました。
  不要な個所は[Delete]キーでクリアしています。
8. C4セルにSUMIFS関数を使った計算式を入力します。
  　=SUMIFS(Sheet1!$G$2:$G$47,Sheet1!$C$2:$C$47,Sheet3!$B4,Sheet1!$D$2:$D$47,Sheet3!C$3)
  計算式を縦横にコピーして完成です。
  - データリストはSheet1、集計表はSheet3に作成しています。
    数式は複合参照になっていますので、$の位置にご注意ください。
9. 総計はSUM関数で求めることができます。
10. ピボットテーブルの結果と同じ結果が得られました。
  担当者を並べ替えるのを忘れていましたので、ちょっと見づらいですがご了承ください。
2つ目の日付と担当者の集計表をエクセルの関数を使って作成してみます。
1. まず、集計表の土台を作成します。
  B列の表示形式は　m月　としてあります。B13セルの値は「2014/4/1」で表示が「4月」となるようにしてあります。
  (数式を作成するうえでちょっと小細工が必要なためです)
2. C4セルにSUMIFS関数を使った計算式を入力します。
  =SUMIFS(Sheet1!$G$2:$G$47,Sheet1!$C$2:$C$47,Sheet3!C$12,Sheet1!$B$2:$B$47,">="&Sheet3!$B13,Sheet1!$B$2:$B$47,"<="&EOMONTH(Sheet3!$B13,0))
  計算式を縦横にコピーして完成です。
  - 4月も値を合計するために2014/4/1以上を　Sheet1!$B$2:$B$47,">="&Sheet3!$B13　としています。
    月末を求めるのに　EOMONTH(Sheet3!$B13,0)　を使っています。
    Sheet1!$B$2:$B$47,"<="&EOMONTH(Sheet3!$B13,0)　で　2014/4/30以下になります。
  総計はSUM関数で求めています。
3. ピボットテーブルの結果とを並べてみます。同じ結果が得られたことが確認できます。