Googleスプレッドシート-縦一列から表に並べ替える方法|文字列の置き換えを利用

Googleスプレッドシートで縦一列に並んだ値を、表に並べ替えたい。

表に並んだ値を縦一列に並べる場合は、FLATTEN関数 を使いますが、ではその反対の縦一列から表に並べ替える関数は？

答え：その様な関数はありません

GoogleスプレッドシートだけでなくEXCELにもそんな関数はありません。おそらく他の表計算ソフトにも無いと思われます。

簡単に処理できる関数を追加してほしいところですが無いものは仕方がない。

ただ既存の関数を組合わせれば、「縦一列から表に並べ替え」の処理は可能なので、諦める必要はありません。

EXCELで最も簡単なのは、INDEX関数とSEQUENCE関数を組み合わせた方法ですが、残念ながらGoogleスプレッドシートでは、SEQUENCE関数のバグなのか仕様なのか分かりませんが上手くいきませんでした。（2021/07/01現在）

=INDEX(縦一列のセル範囲,SEQUENCE(行数,列数))

では、Googleスプレッドシートで「縦一列から表に並べ替え」の処理はどうしたら良いのか。

方法としては少し手数が多いモノから、少々複雑な数式を要するものまで様々ありますが、今回は手間は少しかかるけど数式の知識が殆ど必要ない「文字列の置き換え」を利用した方法を紹介します。

縦列のセルを参照する

あらためて解説する必要も無いとは思いますが、数式でセル番号を指定すれば他のセルを参照する事ができます。

今回しようとしている、縦一列のセル範囲を表に並べ替える場合であれば、表の各セルに縦一列に並ぶセル番号を参照する数式を入力すれば、一応「縦一列から表に並べ替える」は実現できます。

でも各セルに参照の数式を入力するこの方法は、どう見ても非効率。縦一列に並ぶ要素の行数が増えれば増えるだけ手間がかかってしまう。

失敗：数式のセル番号をオートフィル

それならばと、一番上の一行だけに数式を入力して、オートフィルを使って連番にすると。案の定メチャクチャな並びに。

数式の並びを確認すると、参照する縦列の並びを無視して、各列の数値が+1づつ増えるので、一行ずつズレて参照する縦一列の並び順になっただけ。

置き換え文字列を付けてオートフィル

数式のセル番号をオートフィルで増やす方法は上手くいきませんでした。

でも数値なら、増分量（何個飛ばしか）を決めてオートフィルが可能。

セル番号の先頭に置き換え文字を付けた文字列で、表の範囲をオートフィルで埋める。
その後に置き換え文字を数式の「=」に変えれば、文字列から数式になり、適切なセル番号を参照できるはず。

少し面倒ではあるけど表の上2行のセルに、先頭に置き換え文字「xx」を付けたセル番号を入力。
（当然この時点では、数式としては機能しません）

次に、入力した2行のセルを選択し下方向にオートフィル。
すると、1行目と2行目の値の差分から判断した増分量でオートフィルの処理が行われます。

Googleスプレッドシートのメニューから　編集 > 検索と置換　をクリック

[検索と置換]で

検索文字に「xx」
置換後の文字列に「=」

を指定し、[すべて置換]をクリック

文字列「xx」が「=」に置き換わると同時に、セルを参照する数式になり、縦一列に並んだ値を表に並べる事ができました 。
上2行は手動で入力する必要があるので、そこだけは面倒ですが、縦一列の数と表の列数が少ない場合は、手軽に使える方法ではないかと思います。

まとめ

「文字列の置き換え」を利用した縦一列から表に並べ替える方法を紹介しました。

EXCELで使える、INDEX関数とSEQUENCE関数を組み合わせた方法が、Googleスプレッドシートで使えないので手間は増えてしまいますが、「縦一列から表に並べ替え」の対処は可能です。

今回は「文字列の置き換え」を利用する方法を使いましたが、他にも方法はあるので別記事で紹介できたらと思います。

---

𝕏 ( 旧Twitter ) Facebook Pocket LINE はてブ