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

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

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

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

以下が基本式です。

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

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

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

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

式の構造と効果

この計算式は、二次元集計表をリスト形式に変換するために設計されています。それぞれの関数の役割を理解することで、式の動作を把握することができます。

LET関数

LET関数を使うことで、中間変数を定義し、計算式の読みやすさとパフォーマンスを向上させることができます。以下の変数が定義されています：

_Data: 集計表全体を格納します。
_ArrayX: 集計表の行見出しを格納します。
_ArrayY: 集計表の列見出しを格納します。
_ArrayXX: 行見出しを繰り返し拡張したものを格納します。
_ArrayYY: 列見出しを繰り返し拡張したものを格納します。

DROP関数とTAKE関数

DROP関数とTAKE関数を組み合わせて、行見出しと列見出しを抽出します。

TRANSPOSE関数

TRANSPOSE関数を使って、列見出しを縦に並べ替えます。

EXPAND関数とIFERROR関数

EXPAND関数は、配列を指定した行数と列数に拡張します。IFERROR関数は、EXPAND関数がエラーとなった場合に元の配列を返すようにします。

TOCOL関数

TOCOL関数は、配列を1列に変換します。これは結果をリスト形式にするためです。

HSTACK関数

HSTACK関数は、複数の配列を横に結合します。ここでは、拡張された行見出しと列見出し、そして集計表のデータを結合しています。

実例

具体的な例を使って、式の動作を確認してみましょう。例えば、次のようなクロス集計表があるとします。表のサイズは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,TRANSPOSE(DROP(TAKE(_Data,1),,1)),_ArrayXX,TOCOL(IFERROR(EXPAND(_ArrayX, COUNTA(_ArrayX), COUNTA(_ArrayY)), _ArrayX)),_ArrayYY,TOCOL(IFERROR(EXPAND(_ArrayY, COUNTA(_ArrayY), COUNTA(_ArrayX)), _ArrayY), , TRUE),HSTACK(_ArrayXX,_ArrayYY,INDEX(_Data,MATCH(_ArrayXX,_ArrayX,0)+1,MATCH(_ArrayYY,_ArrayY,0)+1)))

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

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

コピペ計算式使用の流れ

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

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

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