クロス集計表・二次元集計表をリスト化する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年
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)))

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

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

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

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

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

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

 

コメント

PAGE TOP
タイトルとURLをコピーしました