スプレッドシートで複数行・複数列にまたがるデータを、縦1列にまとめたいことってありますよね。手作業でコピー＆ペーストすると時間がかかりますし、データが変わるたびにやり直しです。
FLATTEN関数を使えば、数式ひとつで多次元の配列を縦1列に変換できます。引数に範囲を指定するだけのシンプルな関数なので、覚えるのも簡単です。
この記事では、FLATTEN関数の基本から、TOCOL関数との違いまでわかりやすく解説します。
FLATTEN関数とは？
FLATTEN関数（読み方: フラッテン関数）は、2次元以上のセル範囲や配列を縦1列の配列に変換する関数です。「flatten（平らにする）」が名前の由来で、多次元のデータを平坦化するイメージですね。
たとえば、3行x4列の表をFLATTEN関数に渡すと、縦12行x1列のデータに変換されます。元データと数式でつながっているため、元データを変更すると結果も自動で更新されますよ。
FLATTEN関数にできることをまとめると、次のとおりです。
- 2次元のセル範囲を縦1列に変換する
- 複数の離れた範囲を引数に指定して、まとめて1列にできる
- 他の関数（SORT、UNIQUE、FILTERなど）と組み合わせて活用できる
NOTE
FLATTEN関数はGoogleスプレッドシート独自の関数です。Excelには同じ名前の関数がありません。Excelで同様の処理をしたい場合は、TOCOL関数（Microsoft 365で利用可能）を使ってください。
FLATTEN関数の基本構文
=FLATTEN(範囲1, [範囲2, ...])カッコの中に、1列にまとめたい範囲を指定します。
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| 範囲1 | 必須 | 1列に変換したいセル範囲、配列、または値 |
| 範囲2, … | 任意 | 追加で1列にまとめたい範囲（複数指定可能） |
FLATTEN関数にはオプション引数がありません。空白の除外やスキャン方向の切り替えといった機能はなく、とてもシンプルです。指定した範囲を行方向（左から右、上から下）に読み取って縦1列に変換します。
シンプルな分、迷わずに使えるのがFLATTEN関数のよいところです。
FLATTEN関数の基本的な使い方
ここでは、月別の売上データをFLATTEN関数で1列に変換する例を紹介します。
サンプルデータ
A1:D4に以下のデータが入っているとします。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 4月 | 5月 | 6月 | |
| 2 | 東京 | 100 | 120 | 110 |
| 3 | 大阪 | 80 | 90 | 85 |
| 4 | 名古屋 | 60 | 70 | 65 |
数値データだけを1列にする
B2:D4の数値部分だけを1列に変換します。出力先のセル（たとえばF2）に以下の数式を入力して、Enterを押します。
=FLATTEN(B2:D4)行方向（左から右、上から下）に読み取られ、9個の値が縦1列に並びます。
| F | |
|---|---|
| 2 | 100 |
| 3 | 120 |
| 4 | 110 |
| 5 | 80 |
| 6 | 90 |
| 7 | 85 |
| 8 | 60 |
| 9 | 70 |
| 10 | 65 |
東京の4月→5月→6月、大阪の4月→5月→6月…という順番で並んでいますね。
ヘッダー行を含めて変換する
範囲にヘッダー行を含めると、ヘッダーも一緒に1列に変換されます。
=FLATTEN(A1:D4)| F | |
|---|---|
| 1 | |
| 2 | 4月 |
| 3 | 5月 |
| 4 | 6月 |
| 5 | 東京 |
| 6 | 100 |
| 7 | … |
A1が空白セルなので、先頭に空白が入ります。FLATTEN関数には空白を除外する機能がないため、数値だけを取り出したい場合は範囲を絞って指定してくださいね。
複数の離れた範囲を1列にまとめる
FLATTEN関数の大きな特徴は、複数の範囲を引数として直接指定できることです。
たとえば、上期（B2:D4）と下期（F2:H4）のデータを1つのリストにまとめたい場合はこう書きます。
=FLATTEN(B2:D4, F2:H4)2つの範囲のデータが順番につながって、縦1列に並びます。カンマで区切るだけなので、3つ以上の範囲も同じように指定できますよ。
=FLATTEN(B2:D4, F2:H4, J2:L4)FLATTEN関数の実務活用パターン
パターン1: 重複を除外したユニークリストを作る
複数列にまたがる担当者名を1列にまとめて、重複を除外したリストを作れます。
=UNIQUE(FLATTEN(B2:D10))FLATTEN関数で1列にまとめた結果を、UNIQUE関数に渡して重複を除外しています。ドロップダウンリストの選択肢を自動生成したい場面で活躍するパターンです。
パターン2: 1列にまとめてからソートする
散らばったデータを1列にまとめて、昇順や降順に並べ替えることもできます。
=SORT(FLATTEN(B2:D4))SORT関数と組み合わせれば、2次元の数値データを小さい順に並べた一覧が作れます。全体のランキングを見たいときに便利ですよ。
パターン3: 別シートの範囲をまとめて結合する
FLATTEN関数は別シートの範囲も引数に指定できます。
=FLATTEN(Sheet1!B2:D4, Sheet2!B2:D4)月別や拠点別にシートを分けている場合でも、1つの数式で横断的にデータをまとめられます。
パターン4: QUERY関数の入力データとして使う
FLATTEN関数で1列にまとめたデータを、QUERY関数で集計するパターンです。
=QUERY(FLATTEN(B2:D10), "select Col1, count(Col1) group by Col1 label count(Col1) '件数'")複数列に分散していたデータを1列に変換してからQUERY関数に渡すことで、全体の集計ができます。
パターン5: ARRAYFORMULA関数と組み合わせて一括計算する
1列に変換した値に対して一括で計算を適用できます。
=ARRAYFORMULA(FLATTEN(B2:D4) * 1.1)売上データを1列に変換しつつ、すべての値に1.1（税込み換算）を掛けています。ARRAYFORMULA関数と組み合わせれば、変換と計算を1つの数式で完結できますよ。
TOCOL関数との違い・使い分け
FLATTEN関数と似た機能を持つ関数に、TOCOL関数があります。どちらも2次元データを縦1列に変換しますが、機能に違いがあります。
| 比較項目 | FLATTEN関数 | TOCOL関数 |
|---|---|---|
| 出力方向 | 縦1列 | 縦1列 |
| 空白セルの除外 | できない | できる（第2引数で指定） |
| エラー値の除外 | できない | できる（第2引数で指定） |
| スキャン方向の指定 | できない（行方向に固定） | できる（第3引数で指定） |
| 複数範囲の結合 | 引数を複数指定できる | 配列記法（{}）で対応 |
| Excel互換性 | なし（Sheets独自） | あり（Microsoft 365） |
使い分けの目安はこうです。
- 複数の離れた範囲をシンプルに結合したい → FLATTEN関数
- 空白除外やスキャン方向の制御が必要 → TOCOL関数
FLATTEN関数が向いている場面
FLATTEN関数は引数にカンマ区切りで複数の範囲を指定できるのが最大の利点です。
=FLATTEN(A1:C3, E1:G3, I1:K3)TOCOL関数で同じことをするには、中カッコ（{}）で配列を組み立てる必要があります。
=TOCOL({A1:C3; E1:G3; I1:K3})範囲をまとめるだけの用途なら、FLATTEN関数のほうが数式がシンプルで読みやすいですね。
TOCOL関数が向いている場面
TOCOL関数は空白やエラーを除外する機能を持っています。
=TOCOL(B2:D4, 1)第2引数に1を指定すると空白セルをスキップし、3を指定すると空白とエラーの両方を除外できます。FLATTEN関数にはこの機能がないため、データクレンジングが必要な場面ではTOCOL関数のほうが便利です。
また、TOCOL関数は第3引数でスキャン方向を切り替えられます。列方向（上から下→次の列へ）に読み取りたい場合は、TOCOL関数を使ってください。
TIP
TOCOL関数はExcelのMicrosoft 365でも使えます。Excelとの互換性を意識するなら、TOCOL関数を選ぶのがおすすめです。FLATTEN関数はGoogleスプレッドシート独自の関数のため、Excelでは動作しません。
よくあるエラーと対処法
FLATTEN関数は引数が少なくシンプルですが、いくつかのエラーが発生することがあります。
| エラー | 原因 | 対処法 |
|---|---|---|
#REF! | 出力先のセルに既にデータがある | 出力先の範囲を空にしてください |
#REF! | 参照先の行や列が削除された | 参照先セルが存在するか確認してください |
#NAME? | 関数名のスペルミス | 「FLATTEN」のスペルを確認してください |
#NAME? | Excelで使用しようとした | FLATTEN関数はGoogleスプレッドシート専用です。ExcelではTOCOL関数を使ってください |
| 空白が混ざる | 元データに空白セルがある | FLATTEN関数では空白を除外できません。TOCOL関数の第2引数に1を指定するか、QUERY関数で空白行を除外してください |
| エラー値が混ざる | 元データにエラー値がある | FLATTEN関数ではエラーを除外できません。TOCOL関数の第2引数に2を指定してください |
特に注意したいのが「出力先のセルに既にデータがある」ケースです。スプレッドシートのスピル展開は、出力先にデータがあると#REF!エラーになります。出力先に十分な空きセルを確保してから数式を入力してくださいね。
TIP
FLATTEN関数で「Excelに渡したら動かなくなった」というケースが多いです。チームでExcelユーザーとファイルを共有する場合は、TOCOL関数を使うほうが安全ですよ。
まとめ
FLATTEN関数は、多次元のセル範囲を縦1列に変換するためのシンプルな関数です。オプション引数がなく覚えやすいので、配列操作の入門にもぴったりです。
この記事のポイントをおさらいしておきましょう。
- FLATTEN関数は
=FLATTEN(範囲)で、多次元データを縦1列に変換する - 複数の範囲をカンマ区切りで指定でき、離れたデータをまとめるのが得意
- 空白除外やスキャン方向の制御はできない（必要ならTOCOL関数を使う）
- Googleスプレッドシート独自の関数で、Excelでは使えない
- UNIQUE関数やSORT関数と組み合わせると、データ整理がグッと楽になる
まずは=FLATTEN(A1:C3)のシンプルな使い方から試してみてください。複数範囲の結合が必要になったら、カンマで範囲を追加するだけですよ。
