1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. スピルを利用して自動でクロス集計する

スピルを利用して自動でクロス集計する:Excel関数

更新:2025/2/12;作成:2025/11/21

はじめに　　topへ

• スピルを使った配列を操作するような数式のことを動的配列数式と呼ぶようです。
  一方、従来の配列数式は、[Ctr]+[Shift]+[Enter]キーで数式を確定することから、通称「CSE」と呼ばれているようです。
• Excel for Microsoft365 (サブスクリプション版) でスピルが利用できるようになりました
  ちなみに、このスピル機能はExcel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
  

  	Spill(スピル)
  Excel2016,Excel2019	×
  Excel2021,Excel2024	○
  Excel for Microsoft365	○

ここで使用するデータ　　　topへ

• 下表をテーブルに変換して使用します。
  テーブル名は　Tbl_売上　としています。
  C3セル以降の数式は　=MONTH([@日付])　として月の数値を取り出しています。
  

  	B	C	D	E
  2	日付	月	商品	金額
  3	2025/4/1	4	りんご	760
  4	2025/4/2	4	みかん	840
  5	2025/4/3	4	バナナ	610
  6	2025/4/4	4	いちご	540
  7	2025/5/1	5	りんご	660
  8	2025/5/2	5	みかん	720
  9	2025/5/3	5	バナナ	660
  10	2025/5/4	5	いちご	730
  11	2025/6/1	6	りんご	560
  12	2025/6/2	6	みかん	590
  13	2025/6/3	6	バナナ	710
  14	2025/6/4	6	いちご	860

スピルを利用したクロス集計の例

• テーブルからクロス集計表の列見出しを作成します。
  H2セルには　=TRANSPOSE( SORT(UNIQUE(Tbl_売上[月])))　と入力しています。
  Tbl_売上[月] のデータのUNIQUE関数でユニークな値を取り出して、SORT関数で昇順に並べ替えています。
  このデータは行方向へ展開されたデータなので、TRANSPOSE関数を使って列方向へ展開します。
  なお、この値は月数で数値になっています。セルの表示形式を　＃”月”　としています。
  
• 行見出しはG3セルに　=SORT(UNIQUE(Tbl_売上[商品]))　と入力します。
  Tbl_売上[商品] のデータをUNIQUE関数でユニークな値を取り出して、SORT関数で昇順に並べ替えています。
  この場合の昇順は文字コードの小さい順です。
• 集計値の計算はSUMIFS関数で行いました。
  H3セルには　=SUMIFS(Tbl_売上[金額],Tbl_売上[月],H2#,Tbl_売上[商品],G3#)　と入力しています。
• SUMIFS関数の構文は　=SUMIFS(合計対象範囲,条件範囲,条件,...)　です。
  条件1は　Tbl_売上[月]が列見出しの値(H2#)と同じ　
  条件2は　Tbl_売上[商品]が行見出しの値(G3#)と同じ
  となっています。
• 
  

PIVOTBY関数でクロス集計する

• 上のように、Spillが利用できるようになり、関数のみでクロス集計表を作成できるようになりました。
  これは素早しい・・・ということでページを作成しました。
• ところが、2024年10月ごろにPIVOTBY関数が使用できるようになりました。
  この関数一つでクロス集計表を作成できるようになりました。
  2025/2/12現在、PIVOTBY関数はExcel for Microsoft365で使用できる最新の関数です。
• 今後は、こちらが主流となることでしょう。
  ということで、ご紹介です。
  

スポンサードリンク

PageViewCounter

Since2006/2/27