クロス集計表・二次元集計表をリスト化するExcel計算式(列見出しでグループ化)

リスト化を実現する計算式(列見出しでグループ化)
実例
1. 集計表　(A1:H9)
コピペ計算式使用の流れ
参考：行見出しでグループ化したリストにするならば

リスト化を実現する計算式(列見出しでグループ化)

Excelでクロス集計表・二次元集計表をリスト化する方法について解説します。以下の計算式を使うことで、クロス集計表・二次元集計表を簡単にリスト形式に変換することができます。

以下が基本式です。この計算式では列見出し(上の見出し)のリストが左の列に表示されます。行見出しは中央の列、値は右の列に表示されます。

=LET(_Data,集計表,_ArrayX,DROP(TAKE(_Data,1),,1),_ArrayY,DROP(TAKE(_Data,,1),1),_ArrayXX,TOCOL(IFERROR(EXPAND(_ArrayX,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayX),,TRUE),_ArrayYY,IFERROR(EXPAND(_ArrayY,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayY),HSTACK(TOCOL(_ArrayXX,,TRUE),TOCOL(_ArrayYY,,TRUE),INDEX(_Data,MATCH(TOCOL(_ArrayYY,,TRUE),_ArrayY,0)+1,MATCH(TOCOL(_ArrayXX,,TRUE),_ArrayX,0)+1)))

「集計表」を「A1:H9」とした場合の式は以下となります。

=LET(_Data,A1:H9,_ArrayX,DROP(TAKE(_Data,1),,1),_ArrayY,DROP(TAKE(_Data,,1),1),_ArrayXX,TOCOL(IFERROR(EXPAND(_ArrayX,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayX),,TRUE),_ArrayYY,IFERROR(EXPAND(_ArrayY,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayY),HSTACK(TOCOL(_ArrayXX,,TRUE),TOCOL(_ArrayYY,,TRUE),INDEX(_Data,MATCH(TOCOL(_ArrayYY,,TRUE),_ArrayY,0)+1,MATCH(TOCOL(_ArrayXX,,TRUE),_ArrayX,0)+1)))

結果は3列です。左から「列見出し,行見出し,値」です。「列見出し」(左の見出し)がグループ、「行見出し」(上の見出し)が一般項目となっています。3列目は値です。

実例

具体的な例を使って、式の動作を確認してみましょう。例えば、次のようなクロス集計表があるとします。表のサイズはA1:H9です。

集計表　(A1:H9)

	1999年	2000年	2001年	2002年	2003年	2004年	2005年
甲	258	147	63	58	491	449	89
乙	177	456	58	419	202	472	250
丙	489	326	399	403	466	169	252
丁	484	495	107	345	370	120	211
戊	148	239	469	248	149	26	289
己	33	87	366	487	413	15	40
庚	279	475	378	90	369	300	311
辛	21	136	94	424	227	382	457

このデータに対して、以下の計算式を適用します。

=LET(_Data,A1:H9,_ArrayX,DROP(TAKE(_Data,1),,1),_ArrayY,DROP(TAKE(_Data,,1),1),_ArrayXX,TOCOL(IFERROR(EXPAND(_ArrayX,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayX),,TRUE),_ArrayYY,IFERROR(EXPAND(_ArrayY,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayY),HSTACK(TOCOL(_ArrayXX,,TRUE),TOCOL(_ArrayYY,,TRUE),INDEX(_Data,MATCH(TOCOL(_ArrayYY,,TRUE),_ArrayY,0)+1,MATCH(TOCOL(_ArrayXX,,TRUE),_ArrayX,0)+1)))

結果は以下のようになります。1列目が列見出し(上の見出し)で、大グループ扱いとなります。2列目は行見出し(左の見出し)です。3列目は値です。

1999年 甲 258
1999年 乙 177
1999年 丙 489
1999年 丁 484
1999年 戊 148
1999年 己 33
1999年 庚 279
1999年 辛 21
2000年 甲 147
2000年 乙 456
2000年 丙 326
2000年 丁 495
2000年 戊 239
2000年 己 87
2000年 庚 475
2000年 辛 136
2001年 甲 63
2001年 乙 58
2001年 丙 399
2001年 丁 107
2001年 戊 469
2001年 己 366
2001年 庚 378
2001年 辛 94
2002年 甲 58
2002年 乙 419
2002年 丙 403
2002年 丁 345
2002年 戊 248
2002年 己 487
2002年 庚 90
2002年 辛 424
2003年 甲 491
2003年 乙 202
2003年 丙 466
2003年 丁 370
2003年 戊 149
2003年 己 413
2003年 庚 369
2003年 辛 227
2004年 甲 449
2004年 乙 472
2004年 丙 169
2004年 丁 120
2004年 戊 26
2004年 己 15
2004年 庚 300
2004年 辛 382
2005年 甲 89
2005年 乙 250
2005年 丙 252
2005年 丁 211
2005年 戊 289
2005年 己 40
2005年 庚 311
2005年 辛 457

コピペ計算式使用の流れ

以下の計算式をコピペして、実際の操作の流れを確認しましょう。「名前値1」の「集計表」を範囲気に置き換えるだけなので難しくはありません。

=LET(_Data,集計表,_ArrayX,DROP(TAKE(_Data,1),,1),_ArrayY,DROP(TAKE(_Data,,1),1),_ArrayXX,TOCOL(IFERROR(EXPAND(_ArrayX,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayX),,TRUE),_ArrayYY,IFERROR(EXPAND(_ArrayY,COUNTA(_ArrayY),COUNTA(_ArrayX)),_ArrayY),HSTACK(TOCOL(_ArrayXX,,TRUE),TOCOL(_ArrayYY,,TRUE),INDEX(_Data,MATCH(TOCOL(_ArrayYY,,TRUE),_ArrayY,0)+1,MATCH(TOCOL(_ArrayXX,,TRUE),_ArrayX,0)+1)))

続けて「関数の挿入」で範囲を指定します。