365・LibreOfficeなどに関するメモ2022-04-25(Gスプレッドシート)セル内で改行されているデータを縦のセルに並べるGoogleスプレッドシートGoogleスプレッドシートで、セル内で改行されている値を縦1列のデータにする方法についてです。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関数を使って空白セルを除いています。Col1<>''")上記の式をさらにQUERY関数で囲み、WHERE条件をつけて空白セルを除いています。ちなみに、ARRAFORMULA/SPLIT関数のみを実行した結果はこうなります。FLATTENを加えると、このB7:D9の範囲(3行3列)を縦1列に並べ替える(D8,D9セルの部分も含めて並べ替えられる)ので、2つ目の画像のように2つの空白セルが発生します。2つ目の画像(以降)ではTRANSPOSE関数を使うと縦1列には並ばないこともこの画像からわかります。実務的にありそうなのが、グループとなる列についても一緒に縦に並べたいという例です。画像はその具体例ですが、C列の文字列(上記の例と同じ)を縦1列に並べるとともに、対応するB列の値についても対応を保つように縦一列に並べています。Col2<>''")なかなか難解ですが、内側のSPLIT関数を使って全体を一旦横持ちデータにしてから縦持ちへの変換を行っています。詳しい説明は省略しますが、横持ち→縦持ちの変換例については次の記事でも紹介しています。この記事に式の途中経過も記していますので、これがわかれば上記の式も理解できるものと思います。365はデスクトップ版、LibreOfficeは記事作成時の安定バージョンを用いた結果を示しています。サイトポリシー等についてはこちらをご覧ください。(1)検索スポンサーリンク引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。