いきなり答える備忘録

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

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

 Googleスプレッドシートで、横持ちのデータを縦持ちにする変換を関数で行う例です。
 やや実験的な内容になります。

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

手順

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

f:id:accs2014:20191211231411p:plain:w750

 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