ピボットテーブルを使用しないクロス集計

はじめに

ここで説明するデータテーブル機能と、先のExcelデータベース関数と条件統計関数で説明したデータベース関数やCOUNTIFS関数などの条件統計関数と配列数式機能などを組み合わせることによって、ピボットテーブル機能を利用することなくクロス集計を行うことができます。

このページでは以下の内容で進めていきます。

  1. データテーブル機能
  2. データベース関数とデータテーブル機能を使ったクロス集計
  3. 条件統計関数(COUNTIFS関数)と配列数式機能を使ったクロス集計

まずは、データテーブル機能から見ていきます。

[本テキスト用データダウンロード]
※先のExcelデータベース関数と条件統計関数で提示したファイルと同じです。
ex_db_cross1.xlsx


データテーブル機能

データテーブル機能は、二次元配列(テーブル)において、行と列の項目の値を使って、テーブル内の計算を一度に行う機能です。

Q1の例は、左の列項目「1,000円、5,000円、10,000円」が、それぞれ上の行項目「500円玉、100円玉、50円玉」にてそれぞれ何枚必要かを計算する表を示したものです。単純に「1,000円÷500円玉=2枚」、「1,000円÷100円玉=10枚」というように行っていけば良いのですが、データテーブル機能を利用することで、セルB5に、「=H6/G5」の式を設定するだけで表の作成が可能です。計算対象となっているセルH6とG5にはそれぞれ「1」が入っています(実際は値を入れておかなくても構わない)。

データテーブル機能の利用方法(Q1の例)

  1. 既に項目にデータが入っている表が作成済みであること
  2. 表の左上のセル(B5)に数式「=H6/G5」を入力
  3. データテーブルのセル範囲(B5~E8)を選択状態にする
  4. メニュー「データ」タブの「予測」グループの「What-If分析」から「データテーブル」を選択
  5. 行の代入セル」にセルH6、「列の代入セル」にセルG6を指定し、[OK]をクリック

ex-cross01

データテーブルの中身の式がすべて「{=TABLE(G6,H6)}」の配列数式になっていることに注意してください。

Q2の例でも、「行の代入セル」「列の代入セル」それぞれがQ1の時と同じものを利用していますが、そのセルの中にどんな値が入っていても関係ないので問題ありません。


ピボットテーブルを使わないクロス集計

データベース関数とデータテーブル機能、及び配列数式と条件付き統計関数を使ってクロス集計を行う例を見ていきます。

下図のように、学部ごとの「優」、「良」、「可」のそれぞれの評価の学生数を集計する表、いわゆるクロス集計を行ってみます。

ex-cross02

データベース関数とデータテーブル機能を使ったクロス集計

Q1の例は、データベース関数とデータテーブル機能を使用しています。

表の左上セル(G5)に、数式「=DCOUNTA(B5:E18,1,L6:M7)」を入れており、条件範囲をL6:M7に設定し、「学部」の条件(L7)を列の代入セル(”法文学部”、”教育学部”、”理工学部”それぞれが入る)、「評価」の条件(M7)を行の代入セル(”優”、”良”、”可”それぞれが入る)とすることで、条件に合致した個数をカウントします。

条件統計関数(COUNTIFS関数)と配列数式機能を使ったクロス集計

Q2の例は、条件統計関数(COUNTIFS関数)と配列数式機能を使用しています。

条件範囲1(第1引数)を「学生成績表」の「学部」の列に、検索条件1(第2引数)を集計表の左列項目G15:G17(”法文学部”、”教育学部”、”理工学部”)に、そして、条件範囲2(第3引数)を「学生成績表」の「評価」の列に、検索条件2(第4引数)のを集計表の上行項目H14:J14(”優”、”良”、”可”)にした数式「=COUNTIFS(C6:C18,G15:G17,E6:E18,H14:J14)」を、セル範囲H15:J17において配列数式として入力します。

Q1の方法もQ2の方法も結果は同じですが、データベース関数とデータテーブル機能を利用するQ1は、条件範囲を別途設ける必要があり、また面倒なメニュー操作も必要ですが、条件統計関数と配列数式を利用したQ2は、余計なメニュー操作もなく簡潔にできますが、Excel 2007以前のバージョンでは対応できません。


練習課題

あるTシャツの問屋さんが、様々なサイズと色のTシャツを得意先2店舗に卸している。下表がその出荷先の台帳である。

[Tシャツ出荷台帳]

得意先 サイズ カラー 出荷数
オニクロ S 10
石松屋 M 20
オニクロ L 15
オニクロ 2L 11
石松屋 M 8
オニクロ S 18
石松屋 M 15
オニクロ L 7
石松屋 M 28
オニクロ M 8
オニクロ L 6
石松屋 S 14
オニクロ M 26
オニクロ L 21
石松屋 2L 10
オニクロ L 12
オニクロ S 7
オニクロ M 12
石松屋 M 8
オニクロ L 10
オニクロ 2L 16
石松屋 L 5
オニクロ S 8
石松屋 M 16
オニクロ M 12
オニクロ S 6
オニクロ L 11
石松屋 2L 8
オニクロ M 6

この「Tシャツ出荷台帳」から、出荷の傾向を分析するため、以下のような集計表を完成させなさい。

ex-cross-sub01

ex-cross-sub02

ex-cross-sub03

ex-cross-sub04

なお、ワークシートは下図のように作成するものとする。

ex-cross03


以下は、なるべく見ないで練習課題に挑戦してください。

練習課題の解答例

各問いの解答例は以下の通り。

  • Q1
    • 配列数式の入力範囲: I5:M8
    • 配列数式(I5): =SUMIFS(E5:E33,C5:C33,H5:H8,D5:D33,I4:M4)
  • Q2
    • 配列数式の入力範囲: I13:M14
    • 配列数式(I5): =SUMIFS(E5:E33,B5:B33,H13:H14,D5:D33,I12:M12)
  • Q3
    • 配列数式の入力範囲: I19:M22
    • 配列数式(I5): =SUMIFS(E5:E33,C5:C33,H19:H22,D5:D33,I18:M18,B5:B33,P19)
  • Q4
    • 配列数式の入力範囲: I27:M30
    • 配列数式(I5): =SUMIFS(E5:E33,C5:C33,H27:H30,D5:D33,I26:M26)/SUM(E5:E33)

ピボットテーブルを使用しないクロス集計」への2件のフィードバック

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中