Excelでアンケートデータを分析する際など、ピボットテーブル機能を用いて分析するために、複数の列にあるデータを行方向のデータに変換したい時があります。マトリクス表をリストに変換する操作、つまり列のピボット解除です。
前提として、最近のExcelに標準で含まれているPower Queryには、「列のピボット解除」という機能があります。繰り返しの業務や大量のデータなどはPower Queryがお勧めです。
ただし、一時的な処理であったり、慣れた関数等でしたい場面もあります。
もし、Microsoft365のExcelなどでVSTACK関数、HSTACK関数が使える場合、数式だけで「列のピボット解除」のようにマトリクス表をリストに変換できます。
以下はアウトプットイメージと数式の例です。
アウトプットイメージ
次のようにA1:E10の表を、G:I列に変換する。
具体的には、C:E列に複数回答の選択肢が列ごとにあるアンケートデータで、「A:B列 + C列」、「A:B列 + D列」、「A:B列 + E列」でできる表を縦方向に変換する。
実施方法
数式は、上記の事例の場合、次のとおりです。
=VSTACK(HSTACK(A1:C10),HSTACK(A1:B10,D1:D10),HSTACK(A1:B10,E1:E10))
なお、上記数式で、HSTACK(A1:C10)の部分は、A1:C10と同じことです。
HSTACK関数が他と同様にあった方がわかりやすいかと思いましたが、以下でも同様の結果です。
=VSTACK(A1:C10,HSTACK(A1:B10,D1:D10),HSTACK(A1:B10,E1:E10))
要は、HSTACK関数を利用して横方向に連結した表を作成し、VSTACK関数で縦方向に連結しています。
以上、関数を使用してマトリクス表をリストに変換する処理の備忘録でした。
複数回答の選択肢が列ごとにあるアンケートデータの集計などで活用できる方法です。
参考情報
VSTACK関数の基本的な活用例です。
