(Excel)表のデータを1列に並べ替える

 Excelで2列以上の表に並べられているデータを、関数を使って1列に並べなおす方法についてです。

  • INDEX関数とSEQUENCE関数などを用いて、表に並んでいるデータを縦1列に並べ替えることができます。

手順

f:id:accs2014:20200101153424p:plain:right:w650

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

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

=INDEX(B2:E4,CEILING(SEQUENCE(12)/4,1),MOD(SEQUENCE(12)-1,4)+1)

 INDEX関数の第1引数として表の範囲(B2:E4)を、そして第2引数と第3引数としてそれぞれSEQUENCE関数で生成される数列を与えることにより、値をピックして縦1列に並べています。
 INDEX関数の第2引数(表中の行番号の指定)は1から12までの数列を表の列数(4)で割って切り上げたもので、結果的に1,1,1,1,2,2,2,2,3,3,3,3という数列になります。
 INDEX関数の第3引数(行番号の指定)は0から11までの数列を表の列数(4)で割った余りに1を足したもので、結果的に1,2,3,4,1,2,3,4,1,2,3,4という数列になります。
 SEQUENCE関数の引数12は表に含まれるセル数です。
 

f:id:accs2014:20200101153429p:plain:right:w400

 結果はこうなります。

 なお、表の列数(4)やセル数(12)を即値で指定するのではなく表参照から求める場合はそれぞれ「COLUMN(B2:E4)」と「ROWS(B2:E4)*COLUMNS(B2:E4)」に置き換えることとなりますが、次のようにかなり長くなってしまいます。

=INDEX(B2:E4,CEILING(SEQUENCE(ROWS(B2:E4)*COLUMNS(B2:E4))/COLUMNS(B2:E4),1),MOD(SEQUENCE(ROWS(B2:E4)*COLUMNS(B2:E4))-1,COLUMNS(B2:E4))+1)

 また、値をピックする順番は1行目→2行目→3行目となっていますが、これを1列目→2列目→3列目→4列目とする場合は次のようにします。

=INDEX(B2:E4,MOD(SEQUENCE(12)-1,3)+1,CEILING(SEQUENCE(12)/3,1))


 なお、逆の操作(縦1列から表に)については次の記事をご覧ください。

www.officeisyours.com