いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

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

 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列に並べ替えたときも対応付けを保ったまま並ぶというわけです。