クロス集計表・二次元集計表をリスト化するExcel計算式(行見出しでグループ化)
リスト化を実現する計算式(行見出しでグループ化)
Excel365でクロス集計表・二次元集計表をリスト化する方法について解説します。以下の計算式を使うことで、クロス集計表・二次元集計表を簡単にリスト形式に変換することができます。
以下が基本式です。
「集計表」を「A1:H9」とした場合の式は以下となります。
結果は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)
このデータに対して、以下の計算式を適用します。
結果は以下のようになります。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」の「集計表」を範囲気に置き換えるだけなので難しくはありません。
続けて「関数の挿入」で範囲を指定します。
「名前値1」の「集計表」をクロス集計表に置き換えてください。例えば上のサンプル図では「A1:H9」です。
あとはOKすれば変換表が生成されます。
参考:列見出しでグループ化したリストにするならば
列見出し・上の見出しでグループ化するならば下記のページを参照してください。

コメント