いきなり答える備忘録

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

(Gスプレッドシート)表に並んでいるデータを縦1列に並べ替える

 Googleスプレッドシートで2列以上の表に並んでいる値を、関数を使って縦1列に並べなおす方法についてです。
 Excelの場合はINDEX関数にSEQUENCE関数を噛ませる方法がありますが、スプレッドシートだとこれらが併用できないため別のアプローチをとります。

  • TEXTJOIN関数やSPLIT関数を使って表に並んでいる値を縦1列に並べることができます。
  • ただしTEXTJOIN関数による結合結果が3万字余りに達するとエラーになるので巨大な表には対応できません。

手順

f:id:accs2014:20200101171307p:plain:right:w500

 B2:E4の範囲に12個のデータが並んでいます。これを縦1列(12行)に並べ替えるものとします。

 B6セルに次のように入力します。

=TRANSPOSE(SPLIT(TEXTJOIN(",",,B2:E4),","))

 TEXTJOIN関数で表内のすべての値をカンマ区切りにし、SPLIT関数で横1列に分割し、TRANSPOSE関数で縦に並べ替えています。
 式は比較的容易ですがTEXTJOIN関数の結果が32000字に達するとエラーになりますので巨大な表を対象とする場合は注意が必要です。
 

f:id:accs2014:20200101171311p:plain:right:w500

 結果はこうなります。

 

f:id:accs2014:20200102010921p:plain:right:w500

 なお、上記の式ですと表中の空白セルがあっても縦に並べ替えた際に詰められますが、次のような式にすると空白セルのままにすることができます(画像参照)。

=TRANSPOSE(SPLIT(TEXTJOIN(",",,ARRAYFORMULA(IF(B2:E4="","'",B2:E4))),","))

 TEXTJOINによる結合の前に空白セルをアポストロフィ(')に変換することでSPLIT後も空白セルとして残る、というわけです。正確には空白セルではなく空文字列とみるべきでしょうが、変換後の空白セルを選択しても数式バーには何も表示されません。ただしISBLANK関数で評価するとFALSEが返されます。


 以下は備考です。

 横1列に並べ替える場合は上記の式からTRANSPOSE関数を除きます。
 上記の例では表から値をピックアップする順番が1行目→2行目……となっていますが、これを1列目→2列目……とする場合は次のようにします。

=TRANSPOSE(SPLIT(TEXTJOIN(",",,TRANSPOSE(B2:E4)),","))