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

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

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

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年
258147635849144989
17745658419202472250
489326399403466169252
484495107345370120211
14823946924814926289
33873664874131540
27947537890369300311
2113694424227382457

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

=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)))

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

「名前値1」の「集計表」をクロス集計表に置き換えてください。例えば上のサンプル図では「A1:H9」です。

あとはOKすれば変換表が生成されます。

参考:行見出しでグループ化したリストにするならば

行見出し・左の見出しでグループ化するならば下記のページを参照してください。

クロス集計表・二次元集計表をリスト化するExcel計算式(行見出しでグループ化)
クロス集計表・二次元集計表をリスト化するExcel計算式(行見出しでグループ化) リスト化を実現する計算式(行見出しでグループ化) Excel365でクロス集計表・二次元集計表をリスト化する方法について解説します。以下の計算式を使うことで、...

 

コメント