FLATTENは、複数の行・列にまたがるデータを、1列にまとめる関数です。
横に広がった表を縦一列にしたい、複数の列をまとめて1つのリストにしたい——こうした「形を変える」処理に使います。
Excelにはありません。 スプレッドシート固有の関数です。
書式
=FLATTEN(範囲1, [範囲2], ...)範囲はいくつでも並べられます。
基本の動き
| A | B | C | |
|---|---|---|---|
| 1 | 東京 | 大阪 | 名古屋 |
| 2 | 福岡 | 札幌 | 仙台 |
=FLATTEN(A1:C2)結果（縦一列）： 東京 / 大阪 / 名古屋 / 福岡 / 札幌 / 仙台
行ごとに、左から右へ読んで縦に並べます。 読む順番はこの通りで固定です。
離れた範囲をまとめる
=FLATTEN(A1:A10, C1:C10, E1:E10)3つの列が1列に連結されます。
縦に結合するだけなら {} でも書けます。
={A1:A10; C1:C10; E1:E10}; が縦方向の結合です。範囲の列数が揃っているならこちらでも構いません。列数が揃っていない場合や、複数列を1列にしたい場合にFLATTENが必要になります。
空欄を除く
範囲を大きく取ると、空欄もそのまま結果に入ります。FILTER で除きます。
=FILTER(FLATTEN(A1:C100), FLATTEN(A1:C100)<>"")FLATTENを2回書く必要があるのが冗長ですが、これが標準的な書き方です。
重複も除くなら UNIQUE を重ねます。
=UNIQUE(FILTER(FLATTEN(A1:C100), FLATTEN(A1:C100)<>""))複数列に散らばった値から、重複なしの一覧を作る——これがFLATTENの最も実用的な使い方です。
横持ちを縦持ちに変換する
月ごとに列が伸びていく「横持ち」の表は、集計に向きません。FLATTENで縦持ちに直せます。
元の表（横持ち）
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 支店 | 4月 | 5月 | 6月 |
| 2 | 東京 | 1200 | 1400 | 1300 |
| 3 | 大阪 | 800 | 950 | 900 |
縦持ちに変換する
支店の列（各支店を月数ぶん繰り返す）：
=FLATTEN(A2:A3 & {"","",""})& で3列に複製してからFLATTENしています。
月の列：
=FLATTEN(IF(A2:A3<>"", {B1:D1; B1:D1}, ""))金額の列：
=FLATTEN(B2:D3)3つを横に並べると、縦持ちの表ができます。
縦持ちにしておくと、SUMIFS や QUERY で自由に切り出せます。 横持ちのままだと、月が増えるたびに数式を直す必要があります。
複数シートをまとめる
同じ形のシートが並んでいる場合、{} で縦に結合するのが基本です。
={'4月'!A2:C100; '5月'!A2:C100; '6月'!A2:C100}空行が入るので FILTER で除きます。
=FILTER({'4月'!A2:C100; '5月'!A2:C100}, {'4月'!A2:A100; '5月'!A2:A100}<>"")この用途ではFLATTENは使いません。FLATTENは「複数列を1列にする」ための関数で、「複数の表を縦に積む」のは {} の役割です。ここは混同しやすいので区別してください。
エラーの対処
#REF! が出る
結果の展開先にデータが入っています。 FLATTENは下方向に結果を広げるため、その領域が空いている必要があります。
空欄が大量に出る
範囲を大きく取りすぎています。FILTER で <>"" を指定してください。
順番が想定と違う
FLATTENは行ごとに左から右の順で読みます。列ごとに読みたい場合は、TRANSPOSE で行列を入れ替えてから渡してください。
=FLATTEN(TRANSPOSE(A1:C2))複数の範囲を一列にまとめる
複数の範囲を縦一列にまとめる処理は、集計の前段として意外に需要があります。
たとえば、月ごとに列が分かれている表があり、全月の値をまとめて一列にしたい場合。あるいは、複数の列に分散している担当者名を、一つの一覧にまとめたい場合。こうした要求に応えるのがこの関数です。
範囲を渡すと、その中の値が縦一列に並びます。複数の範囲を渡せば、順に連結されます。行と列の両方に広がった範囲でも、一列に伸ばされます。
この関数の価値は、他の関数と組み合わせたときに出ます。一列にまとめてから重複を除けば、分散していた項目の一覧が作れます。一列にまとめてから並べ替えれば、全体の順位が出ます。一列にまとめてから件数を数えれば、全体の件数が分かります。
複数の範囲をそのまま他の関数に渡せないことが多いため、この関数が橋渡しの役割を果たします。
空白セルの扱い
範囲に空白セルが含まれていると、結果にも空白として含まれます。件数を数えるときや、重複を除くときに、この空白が邪魔になります。
対処としては、まとめた結果を抽出の関数で絞り込みます。空白でないという条件で絞れば、値の入っているものだけが残ります。
順序としては、まとめてから絞る形になります。逆に、絞ってからまとめようとすると、範囲ごとに絞る必要があり、数式が長くなります。
もうひとつ、数式が返した空文字にも注意が必要です。見た目は空欄ですが、空白セルとは扱いが違います。条件で除外する場合、両方に対応できる書き方にしてください。
範囲の指定を、データのある部分に限定するという方法もあります。列全体を指定すると、大量の空白が含まれることになります。動作も重くなるので、範囲は絞ってください。
縦持ちへの変換に使う
横に広がったデータを縦持ちに変換する用途にも使えます。
月ごとに列が分かれている表は、集計のたびに横断が必要になります。これを縦持ちに変換できれば、条件付きの集計一つで済むようになります。
単純に値を一列にするだけなら、この関数で足ります。しかし、実際には「どの月の値か」という情報も必要です。値だけを縦に並べても、月が分からなければ集計できません。
このため、月の情報も同じ形で一列に並べる必要があります。月の見出しを、各列の行数分だけ繰り返した範囲を作り、それも一列にまとめます。二つの列を並べれば、縦持ちの表になります。
やや手間はかかりますが、一度作ってしまえば、元の横持ちの表が更新されるたびに自動で変換されます。手作業で縦に並べ替えるより確実です。
そもそも横持ちのデータを受け取らない構成にできるなら、そちらのほうが根本的な解決になります。データの提供元と調整できるなら、縦持ちで受け取れないか相談する価値があります。
使いどころの判断
この関数が必要になるのは、複数の範囲を一つの処理に渡したい場合に限られます。
単一の範囲であれば、そのまま他の関数に渡せます。わざわざ一列にまとめる必要はありません。
複数の範囲を扱う場面でも、範囲が隣接しているなら、まとめて一つの範囲として指定できます。離れている場合や、行と列に広がっている場合にだけ、この関数の出番になります。
また、結果は縦一列になるため、元の位置関係は失われます。どの行のどの列だったかという情報は残りません。位置関係が必要な処理では使えません。
スプレッドシート特有の関数であるため、他の環境では使えません。共有する予定があるなら、範囲を縦に並べ直したデータを持つほうが安全です。
横持ちの表を縦に組み替える手順
月ごとに列が分かれた表を、集計しやすい縦持ちに変える手順を具体的に整理します。この作業は一度作れば以降は自動化されるので、手間をかける価値があります。
第一段階として、値の部分を一列にまとめます。月ごとの列を範囲として渡せば、縦一列に並びます。ただし、この時点では「どの月の値か」が失われています。
第二段階として、月の情報も同じ形で並べる必要があります。各月の見出しを、データの行数分だけ繰り返した範囲を作ります。行数が十行なら、一月を十個、二月を十個、という形です。この繰り返しは、行番号から計算して作ります。
第三段階として、項目名も同じ形で並べます。こちらは月の数だけ繰り返す形になります。値の並び順に合わせる必要があるので、繰り返しの単位が月とは逆になります。
第四段階として、三つの列を横に並べます。項目、月、値の三列が揃えば、縦持ちの表として完成です。
第五段階として、空白の行を除きます。抽出の関数で、値が入っているものだけを残します。
手順は多いのですが、一度組めば元の表が更新されるたびに自動で変換されます。手作業で並べ替えるより確実で、作業漏れも起きません。
そもそも縦持ちで受け取れないか
変換の仕組みを作る前に、データの受け取り方を変えられないかを検討する価値があります。
システムからの出力であれば、出力形式を選べる場合があります。縦持ちの形式で出力できるなら、変換の工程そのものが不要になります。
人が入力する表であれば、入力用のシートを縦持ちに設計し直せます。入力する側にとっては横持ちのほうが楽に見えますが、慣れれば縦持ちでも問題ありません。むしろ、項目が増えたときに列を追加する必要がなくなります。
他部署から受け取る場合は、形式の変更を依頼できるか相談してみてください。相手にとっても、縦持ちのほうが管理しやすいことが多いものです。
どうしても横持ちで受け取らざるを得ない場合にだけ、変換の仕組みを作る。この順序で検討すれば、無駄な作業を減らせます。
変換の仕組みは、作った本人以外には理解しにくいという弱点もあります。引き継ぎを考えると、そもそも変換が不要な構成のほうが望ましいのです。
変換の前に確認すること
複数の範囲を一列にまとめる処理を組む前に、確認しておくべきことがあります。
範囲の大きさを把握してください。まとめた結果の行数は、各範囲の行数と列数を掛けた合計になります。想定より大きくなることが多いので、展開先を十分に空けてください。
空白セルの割合を確認してください。範囲に空白が多いと、結果にも空白が多く含まれます。抽出の関数で除く工程が必要になります。
値の型が揃っているか確認してください。数値と文字列が混在していると、まとめた後の集計で問題が起きます。
順序が意味を持つかを確認してください。まとめると、元の位置関係は失われます。どの範囲のどの位置だったかが必要なら、その情報も並行して並べる必要があります。
そして、そもそもこの処理が必要かを確認してください。データの持ち方を変えられるなら、変換は不要になります。受け取る形式を変えられないか、提供元と相談する価値があります。
これらを確認してから組み始めれば、途中で行き詰まることが減ります。変換の処理は組み上がるまで結果が見えないので、事前の見通しが重要になります。
よくある質問
Q. エラーになって展開されません
展開される先に何か入っています。下方向を空にしてください。
Q. 空白も含まれます
抽出の関数で、空白でないものだけを絞り込んでください。
Q. 複数の範囲を渡せますか
渡せます。カンマで区切って並べてください。順に連結されます。
Q. 横一列にできますか
この関数は縦一列にします。横にしたい場合は、行と列を入れ替える関数で包んでください。
Q. 元の位置が分からなくなります
位置の情報は失われます。必要なら、位置を表す列も同じ形で作って並べてください。
Q. 重複を除けますか
まとめた結果を、重複を除く関数に渡してください。
Q. Excelでも使えますか
スプレッドシート特有の関数です。他の環境では、範囲を縦に並べ直す必要があります。
Q. 動作が重いのですが
範囲を列全体で指定していないか確認してください。データのある範囲に絞ると改善します。
まとめ
- 書式は
=FLATTEN(範囲)。行ごとに左から右の順で1列にまとめる - 空欄を除くには FILTER で
<>"" - 複数列から重複なしの一覧を作るのが主な用途
- 横持ちを縦持ちに変換できる。集計しやすい形になる
- 複数の表を縦に積むのは
{}の役割。FLATTENとは別物 - Excelには無い