（Gスプレッドシート）クロス表を縦持ちに変換する

Googleスプレッドシート

　Googleスプレッドシートでクロス表を縦持ちのデータに置き換える、いわゆるアンピボットです。ここでは関数で実行してみます。
　なお横持ち→縦持ち変換と異なり、横方向の見出しも抽出するので出力は3列（以上）となります。

  • FLATTEN関数を使ってクロス表を縦持ちに変換することができます。

手順

　画像ではB2:F5の範囲がクロス表になっています。
　B列に縦方向の見出し（店名）が、第2行に横方向の見出し（月）が並んでおり、C3:F5の範囲には数値が記録されています。

　これに対しC8セルに式を入力することで、店名と月の組み合わせを縦に並べるとともに、対応する数値をその隣に表示しています。

　C8セル

={ARRAYFORMULA(SPLIT(FLATTEN(B3:B5&"_"&C2:F2),"_")),FLATTEN(C3:F5)}

　まず「ARRAYFORMULA(SPLIT(FLATTEN(B3:B5&"_"&C2:F2),"_"))」で1つ目の行列を生成しています。これは縦横の見出しのすべての組み合わせ（直積）です。この例の場合は12行（店名3つ×月4つ=12通り）2列の行列となります。
　そして「FLATTEN(C3:F5)」で2つ目の行列を生成しています。これは数値を縦1列に並べたものです。
　最後に「={ , }」の記法を使って2つの行列を左右に並べています。並べているだけですので2つの式をC8セルとE8セルに分けて記しても結果は同じです。
　ところでこの式には「見出しから数値を検索・抽出する」という操作が含まれていませんが、結果的に「店名」「月」とそれに対応する「数値」は左右に並んで表示されます。


　なぜなのかわかりやすいように、次の画像では「=ARRAYFORMULA(B3:B5&"_"&C2:F2)」の部分だけを実行しています。

　この式により、縦横の見出しを連結した文字列からなる3行4列の行列が生成されます。
　数値の並びも同じく3行4列であり、2つの行列の「店名・月」と「数値」の位置が対応しているので、FLATTEN関数でそれぞれ縦1列に並べ替えたときも対応付けを保ったまま並ぶというわけです。

accs2014