以前、パワークエリのピボット解除を使えばマトリクス表を簡単にテーブル形式に変換できることを紹介しました。
今回は、パワークエリほど簡単ではないのですが、関数を使ってマトリクス表をテーブル形式に変換してみたいと思います。
今回やりたいこと
改めて、この記事でやりたいことを確認しておきましょう。
このように左のマトリクス表を右のテーブル形式の表に、Excelの関数を使って変換します。
方法に入る前に確認しておきたいこと
まずはどのように考えればいいか、確認しておきましょう。
この表の値をマトリクス表から縦一列に配置するときどんな順番でしょうか。
通常このように転記すると思います。
では、これが可能な数式を使えばいいわけです。
そう、OFFSET関数です。
この、起点から移動距離を指定し、移動先の内容を返す関数を使います。
そして起点を設定してそこからの移動距離を指定することで、マトリクス表からテーブル形式の表に転記するわけです。
起点と移動距離を示すとこのようになります。
このような関係ですね。
これを各セルに応じた移動数をOFFSET関数にセットすればOKです。
では早速テーブル形式に変換してみましょう。
テーブル形式に変換する関数の解説
事前準備
まずOFFSET関数にセットするタテ移動数、ヨコ移動数を指定する補助列を作ります。
テーブル形式の表の作成
まず枠を用意しましょう。
こんなのでOKです。
言わずもがなですが、行数はマトリクス表のタテ×ヨコです。
次に関数を入力しましょう。
こんな感じです。
OFFSET関数の中身が、（スタート地点、タテ移動、ヨコ移動）になっていることが分かると思います。
移動数を示した表と関連を確認してください。
これと、上の補助列が対応し、OFFSET関数で補助列を参照しています。
ちなみに項目については、スタート地点との関係ではヨコ移動あるいはタテ移動がありませんので、そこはゼロ（０）としています。
これでマトリクス表からテーブル形式の表に変換できるわけです。
補助列の作成方法
ところで移動距離を指定する補助列。
これをいちいち入力するのは手間ですよね。
これも関数で作ってみましょう。
これ、ヨコは{1,2…ヨコ項目数｝を繰り返し、
タテは{1,1,1,2,2,2…タテ項目数}と繰り返しますよね。
なので、数式はこうしました。
まずヨコから。
前段の値に1を足し、それがヨコの項目数を超えれば１とし、越えなければ前段＋１の値を返すようにしました。
ちなみにこの項目数ですが、ヨコ項目のタイトル行を範囲指定してCOUNTA関数で個数を数えるようにしてもいいかもしれませんね。
ここでは値のセルを数えるCOUNT関数ではだめですよ、空白でないセルの個数を数えるCOUNTA関数でないとうまくいきません。
次にタテ。
同行のヨコの値が１の時前段の値に１を足す、そうでないときは前段と同値を返す、そのように組みました。
そしてそれをタテ×ヨコの行数までコピー。
これで補助列は完成します。
このように、IF関数を使って補助列を作成し、OFFSET関数を使ってマトリクス表からテーブル形式の表へと転記すればいいわけです。
最後に
いかがだったでしょうか。
できたことはできたのですが、やはりパワークエリのピボット解除を使った方が簡単ですね。
とはいえ、パワークエリが使えないとき使いたくないときにはこの方法が使えますので、参考にしていただければと思います。
今回も最後までお読みいただきありがとうございました。
