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

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

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

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

以下が基本式です。

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

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

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

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

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

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

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

コメント

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