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

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

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

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

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

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

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

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

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

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

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

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

コメント