- SPLIT関数やFLATTEN関数を使って、セル内改行により並んでいるデータを縦1列のセルに配置し直すことができます。
手順
単純な例
画像では、B3セル内に改行で区切られた3つの文字列が記録されています。
そこでB5セルに式を入力し、3つの文字列を縦方向のセルに並べています。
B5セル
=FLATTEN(SPLIT(B3,CHAR(10)))
SPLIT関数を使い「CHAR(10)」つまり改行を区切り文字として文字列を分割します。
それだけだと値が横方向に並ぶので、FLATTEN関数で縦に並べ替えています。
この例ではFLATTEN関数をTRANSPOSE関数に代えても結果は同じです。
こちらの画像では、B3:B5の3つのセルにそれぞれ改行された値が記録されています。
B7セルに式を入力し、すべての文字列を縦方向のセルに並べています。
B7セル
=FLATTEN(ARRAYFORMULA(SPLIT(B3:B5,CHAR(10))))
ARRAYFORMULAを使ってSPLIT関数の参照範囲を広げただけですが基本的にこれでうまくいきます。
ただ、各セルに含まれている文字列の数が異なることが原因で空白セルが出力されている（B12セルとB15セル）のが気になるところです。
そこで、次の画像ではさらにQUERY関数を使って空白セルを除いています。
B7セル
=QUERY(FLATTEN(ARRAYFORMULA(SPLIT(B3:B5,CHAR(10)))),"WHERE Col1<>''")
上記の式をさらにQUERY関数で囲み、WHERE条件をつけて空白セルを除いています。
ちなみに、ARRAFORMULA/SPLIT関数のみを実行した結果はこうなります。
さらにFLATTENを加えると、このB7:D9の範囲（3行3列）を縦1列に並べ替える（D8,D9セルの部分も含めて並べ替えられる）ので、2つ目の画像のように2つの空白セルが発生します。
また、2つ目の画像（以降）ではTRANSPOSE関数を使うと縦1列には並ばないこともこの画像からわかります。
B7セル
=ARRAYFORMULA(SPLIT(B3:B5,CHAR(10)))
グループとなる列が存在する例
実務的にありそうなのが、グループとなる列についても一緒に縦に並べたいという例です。
画像はその具体例ですが、C列の文字列（上記の例と同じ）を縦1列に並べるとともに、対応するB列の値についても対応を保つように縦一列に並べています。
B7セル
=QUERY(ARRAYFORMULA(SPLIT(FLATTEN(B3:B5&"_"&SPLIT(C3:C5,CHAR(10))),"_")),"WHERE Col2<>''")
なかなか難解ですが、内側のSPLIT関数を使って全体を一旦横持ちデータにしてから縦持ちへの変換を行っています。
詳しい説明は省略しますが、横持ち→縦持ちの変換例については次の記事でも紹介しています。この記事に式の途中経過も記していますので、これがわかれば上記の式も理解できるものと思います。
