-
Home »
-
エクセル関数一覧表 »
-
スピルを利用して自動でクロス集計する
2022/11/21
- スピルを使った配列を操作するような数式のことを動的配列数式と呼ぶようです。
一方、従来の配列数式は、[Ctr]+[Shift]+[Enter]キーで数式を確定することから、通称「CSE」と呼ばれているようです。
- Excel for Microsoft365 (サブスクリプション版) でスピルが利用できるようになりました
ちなみに、このスピル機能はExcel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
なお、2021/10/5頃から市販されている Excel2021(永続ライセンス版)でも使用できるようです。
スピルを利用したクロス集計の例 topへ
- 下表をテーブルに変換して使用します。
テーブル名は Tbl_売上 としています。
C3セル以降の数式は =MONTH([@日付]) として月の数値を取り出しています。
|
B |
C |
D |
E |
| 2 |
日付 |
月 |
商品 |
金額 |
| 3 |
2022/4/1 |
4 |
りんご |
760 |
| 4 |
2022/4/2 |
4 |
みかん |
840 |
| 5 |
2022/4/3 |
4 |
バナナ |
610 |
| 6 |
2022/4/4 |
4 |
いちご |
540 |
| 7 |
2022/5/1 |
5 |
りんご |
660 |
| 8 |
2022/5/2 |
5 |
みかん |
720 |
| 9 |
2022/5/3 |
5 |
バナナ |
660 |
| 10 |
2022/5/4 |
5 |
いちご |
730 |
| 11 |
2022/6/1 |
6 |
りんご |
560 |
| 12 |
2022/6/2 |
6 |
みかん |
590 |
| 13 |
2022/6/3 |
6 |
バナナ |
710 |
| 14 |
2022/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#)と同じ
となっています。
- 15行目と16行目にデータを追加します。
すると、クロス集計表にデータが追記されました。
- 5月のデータを削除しました。
クロス集計表の5月もなくなりました。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数一覧表 » スピルを利用して自動でクロス集計する
PageViewCounter
Since2006/2/27