いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)クロス表を縦持ちに変換する

 Excelで、シンプルなクロス表を縦持ちのデータに置き換える例です。メニュー操作により進める方法も考えられますが、ここでは新しい関数を利用した方法を試してみます。
 なお横持ち→縦持ち変換と異なり、横方向の見出しも抽出するので出力は3列となります。

  • TOCOL/TEXTBEFORE/TEXTAFTER関数を使用してクロス表を縦持ちのデータに変換することができます。

手順

 画像ではB2:F5の範囲がクロス表になっています。
 B3:B5の範囲には縦方向の見出し(店名)が、C2:F2の範囲には横方向の見出し(月)が並んでおり、C3:F5の範囲にはそれらに対応する数値が記録されています。

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

 C8セル

=LET(x,TOCOL(B3:B5&"_"&C2:F2),
HSTACK(TEXTBEFORE(x,"_"),TEXTAFTER(x,"_"),TOCOL(C3:F5))
)

 まず「TOCOL(B3:B5&"_"&C2:F2)」の部分により、「池袋店_11月」というように店名と月名を組み合わせた文字列(下記参照)を縦1列(店名3つ×月4つ=12行)に並べた配列を生成しています。そしてLET関数により、これにxという名前を付けています。
 次に「TEXTBEFORE(x,"_")」によりxのうち店名だけの列を、「TEXTAFTER(x,"_")」によりxのうち月名だけの列を取得し、「TOCOL(C3:F5)」により数値だけを縦1列に並べた列(12行)を取得しています。最後にHSTACK関数によりこれらを横並びに表示させています。
 TEXTSPLIT関数が使えれば店名と月名の分割がさらに簡単になりそうですが、TEXTSPLITの第1引数を配列にすると、つまり「TEXTSPLIT(x,"_")」とすると店名しか返ってこないのでTEXTBEFORE/TEXTAFTERで代用しています。


 参考ですが、「B3:B5&"_"&C2:F2」だけを実行するとこのようになります(C8セル)。
 縦横の見出しを連結した文字列からなる3行4列の行列が生成されているのがわかります。
 数値の並びも同じく3行4列で、2つの行列の「店名・月」と「数値」の位置が対応しているため、それぞれ縦1列に並べ替えたときも対応付けを保ちます。
 上記の式ではTOCOL関数によりそれぞれ縦1列に並べ替え、それらをHSTACK関数で横方向に結合することで、縦持ちデータとして表示させています。