いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

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

 Googleスプレッドシートで、横持ちのデータを縦持ちにする変換を関数で行う例です。

  • FLATTEN関数を利用して横持ちの表を縦持ちに変換することができます。

手順

FLATTEN関数を用いる例

 変換の対象となっているのはB3:E6の範囲のデータで、これに対しG3セルに式を入力することで変換を実現しています。

f:id:accs2014:20210202001207p:plain:w780

 G3セル

=QUERY(ARRAYFORMULA(SPLIT(FLATTEN(B3:B6&"_"&C3:E6),"_")),"WHERE Col2<>''")

 「B3:B6&"_"&C3:E6」の部分によりB列の値とC~E列の値を「_」でつないた文字列を取得し、FLATTEN関数により値を縦1列に並べ、SPLIT関数で2列に分割し、最後にQUERY関数により2列目が空白であるものを除いています。
 最初の「B3:B6&"_"&C3:E6」の部分が何をやっているかわかりにくい感じですが、ここだけ実行すると次のようになります。これで以降の部分も理解しやすいと思います。

f:id:accs2014:20210202003031p:plain:w780

CONCATENATE関数等を用いる例

 こちらは文字列結合関数を駆使してなんとかしてみた例です。
 上記のFLATTEN関数が利用できるようになったため、もはや実験的な意味しかないものの一応残しておきます。

f:id:accs2014:20191211231411p:plain:w780

 G3セルに入力されている式は次の通りです。

=QUERY(TRANSPOSE({SPLIT(CONCATENATE(ARRAYFORMULA(REPT(B3:B6&",",COLUMNS(C3:E6)))),",");SPLIT(TEXTJOIN(",",,C3:E6),",",,)}),"WHERE Col2<>''")

 ちょっと長いですが補足しますと、波カッコ({)からセミコロン(;)の前までは同じ氏名を繰り返し横に並べて出力している部分です(ここでは結果的に4名×3回。全体で横12セル)。セミコロンの後から波カッコ(})までは資格の名称を並べて出力している部分です(横12セル)。これらをセミコロンで上下につないでからTRANSPOSEで転置して縦長にし、QUERYで資格名が空の行を除いています。
 値の走査を簡略化するため全体的に文字列結合関数(CONCATENATEやTEXTJOIN)に頼るアプローチになっています。しかし、これらの関数により結合される文字列の長さが合計5万字ほどに達するとエラーになります。膨大なデータの処理には向きません。
 また、文字列操作において区切り文字としてカンマを使っていますので、対象文字列にカンマが含まれる場合は「","」(4か所)を別の記号に変える必要があります。
 「COLUMNS(C3:E6)」の部分は繰り返し部分の列数を測っているだけですので、即値にすれば(この場合は3)少し短くできます。


 なお、逆の変換(縦持ち→横持ち)については次の記事をご覧ください。

www.officeisyours.com