Excel：複数行に入力されているデータを1つの数式で横1行や縦1列の入力に変換する

複数行に入力されているデータは利用しづらいため横1行や縦1列の入力に変換したい場面があります。

Microsoft 365のエクセルで追加された動的配列を返す数式とスピル機能を使うことで1つの数式で変換することができます。

はじめに

例えば以下の画像のようにデータがB2:K6の範囲に入力されている場合、データを利用するために横1行や縦1列に入力し直す必要があります。

複数行に入力されているデータ

データが少ない場合は手作業で切り貼りをしたりして入力し直しても問題ないと思いますがデータが多い場合は大変です。

TOCOL関数, TOROW関数

TOCOL関数, TOROW関数が追加されましたので今は１つの関数で簡単に変換することができます。以下の記事を参照ください。

関連する記事

複数行に行方向の並びでデータが入力されている場合

入力データは以下のように行方向の並びでデータが入力されているものとします。

複数行に行方向で入力されているデータの並び

以下の数式でB2:K6の範囲に入力されているデータを縦1列に変換することができます。

=LET(
    対象範囲, B2:K6,
    データIDX, SEQUENCE(COUNT(対象範囲))-1,
    行IDX, INT(データIDX/COLUMNS(対象範囲)),
    列IDX, MOD(データIDX,COLUMNS(対象範囲)),
    変換結果, INDEX(対象範囲,行IDX+1,列IDX+1),
    変換結果)

数式の対象範囲に指定している範囲（B2:K6）を変換したい範囲に書き換えることで再利用することができます。

上記の数式をB8のセルに入力した結果は以下です。

行方向の並びでデータが入力されている場合に数式で縦1列に変換する

D8からの列は数式の結果と比較するために手作業で切り貼りを行っています。

縦1列ではなく横1行の入力データに変換したい場合は以下のようにTRANSPOSE関数で変換結果の行列を入れ替えることで対応できます。

=LET(
    対象範囲, B2:K6,
    データIDX, SEQUENCE(COUNT(対象範囲))-1,
    行IDX, INT(データIDX/COLUMNS(対象範囲)),
    列IDX, MOD(データIDX,COLUMNS(対象範囲)),
    変換結果, INDEX(対象範囲,行IDX+1,列IDX+1),
    TRANSPOSE(変換結果))

上記の数式をB8のセルに入力した結果は以下です。

行方向の並びでデータが入力されている場合に数式で横1行に変換する

複数行に列方向の並びでデータが入力されている場合

今度は入力データが以下のように列方向の並びでデータが入力されている場合です。

複数行に列方向で入力されているデータの並び

先ほどの行方向に入力されているデータを変換する数式を少し変更します。

以下のようにTRANSPOSE関数で対象範囲の行列を入れ替えることで列方向の入力の変換に利用できます。

=LET(
    対象範囲, TRANSPOSE(B2:K6),
    データIDX, SEQUENCE(COUNT(対象範囲))-1,
    行IDX, INT(データIDX/COLUMNS(対象範囲)),
    列IDX, MOD(データIDX,COLUMNS(対象範囲)),
    変換結果, INDEX(対象範囲,行IDX+1,列IDX+1),
    変換結果)

上記の数式をB8のセルに入力した結果は以下です。

列方向の並びでデータが入力されている場合に数式で縦1列に変換する

横1行の入力データに変換したい場合は先ほどの行方向に入力されているデータを変換する数式のときと同様に変換結果の行列をTRANSPOSE関数で入れ替えることで対応できます。

=LET(
    対象範囲, TRANSPOSE(B2:K6),
    データIDX, SEQUENCE(COUNT(対象範囲))-1,
    行IDX, INT(データIDX/COLUMNS(対象範囲)),
    列IDX, MOD(データIDX,COLUMNS(対象範囲)),
    変換結果, INDEX(対象範囲,行IDX+1,列IDX+1),
    TRANSPOSE(変換結果))

上記の数式をB8のセルに入力した結果は以下です。

列方向の並びでデータが入力されている場合に数式で横1行に変換する

最後に数式で出力された結果の範囲をコピーして、必要な場所に値の貼り付けでペーストして作業完了です。