スプレッドシートで複数列にまたがるデータを、縦1列にまとめたいことってありますよね。手作業でコピー＆ペーストするのは面倒ですし、データが更新されるたびにやり直しになります。
TOCOL関数を使えば、2次元の範囲を数式ひとつで1列に変換できます。空白やエラーを自動で除外する機能もついているので、クリーンなデータ整理に最適です。
この記事では、TOCOL関数の基本から、TOROW関数・FLATTEN関数との違いまでわかりやすく解説します。
スプレッドシートのTOCOL関数とは？
TOCOL関数（読み方: トゥコラム関数）は、2次元のセル範囲や配列を縦1列の配列に変換する関数です。「to column（列にする）」が名前の由来です。
たとえば、3行x4列の表を縦12行x1列に変換できます。元データと数式でつながっているため、元データを変更すると変換結果も自動で更新されますよ。
TOCOL関数にできることをまとめると、次のとおりです。
- 2次元のセル範囲を縦1列に変換する
- 空白セルやエラー値を除外して変換できる
- 読み取り順序を行方向・列方向で切り替えられる
- 他の関数（SORT、UNIQUE、FILTERなど）と組み合わせて活用できる
NOTE
TOCOL関数はGoogleスプレッドシート独自の関数ではなく、ExcelのMicrosoft 365でも使用できます。ただし、Excel 2019以前のバージョンでは使えません。
TOCOL関数の基本構文
=TOCOL(配列, [無視], [列方向スキャン])カッコの中に、1列に変換したい範囲と、オプションの引数を指定します。
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| 配列 | 必須 | 1列に変換したいセル範囲または配列 |
| 無視 | 任意 | 除外する値の種類を指定する（0: すべて保持、1: 空白を除外、2: エラーを除外、3: 空白とエラーを除外） |
| 列方向スキャン | 任意 | 読み取り方向を指定する（FALSE: 行方向、TRUE: 列方向） |
第1引数だけで使えるシンプルな関数です。第2・第3引数はどちらも省略可能で、必要なときだけ指定すれば大丈夫ですよ。
第2引数「無視」の設定値
第2引数では、変換時に除外したい値の種類を数値で指定します。
| 値 | 動作 |
|---|---|
| 0（省略時） | すべての値を保持する |
| 1 | 空白セルを除外する |
| 2 | エラー値を除外する |
| 3 | 空白セルとエラー値の両方を除外する |
データに歯抜けやエラーが混ざっている場合は、1や3を指定するとクリーンな1列データを取得できます。
第3引数「列方向スキャン」の読み取り順序
第3引数では、2次元データをどの順番で読み取るかを指定します。
| 値 | 読み取り順序 | 説明 |
|---|---|---|
| FALSE（省略時） | 行方向 | 1行目を左から右へ → 2行目を左から右へ → … |
| TRUE | 列方向 | 1列目を上から下へ → 2列目を上から下へ → … |
たとえば以下の2行x3列のデータを変換する場合です。
| A | B | C | |
|---|---|---|---|
| 1 | あ | い | う |
| 2 | え | お | か |
- FALSE（行方向）: あ → い → う → え → お → か
- TRUE（列方向）: あ → え → い → お → う → か
省略時は行方向（FALSE）で左上から右へ読み取ります。データの並び順が結果に影響するので、用途に合わせて切り替えてくださいね。
TOCOL関数の基本的な使い方
ここでは、月別の売上データをTOCOL関数で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を押します。
=TOCOL(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月…という順番で並んでいますね。
空白セルを除外して変換する
データに空白セルが混ざっている場合は、第2引数に1を指定します。
=TOCOL(B2:D4, 1)空白セルをスキップして、値のあるセルだけが1列に並びます。データクレンジングの第一歩として便利ですよ。
列方向に読み取って変換する
第3引数にTRUEを指定すると、列方向に読み取ります。
=TOCOL(B2:D4, 0, TRUE)| F | |
|---|---|
| 2 | 100 |
| 3 | 80 |
| 4 | 60 |
| 5 | 120 |
| 6 | 90 |
| 7 | 70 |
| 8 | 110 |
| 9 | 85 |
| 10 | 65 |
今度は4月の東京→大阪→名古屋、5月の東京→大阪→名古屋…という月別の順番になりました。「拠点別にまとめたいか」「月別にまとめたいか」で使い分けてください。
TOCOL関数の実務活用パターン
パターン1: 重複を除外したユニークリストを作る
複数列にまたがる担当者名を1列にまとめて、重複を除外したリストを作れます。
=UNIQUE(TOCOL(B2:D10, 1))TOCOL関数で1列にまとめた結果を、UNIQUE関数に渡して重複を除外しています。ドロップダウンリストの選択肢を自動生成したい場面で活躍するパターンです。
パターン2: 1列にまとめてからソートする
散らばったデータを1列にまとめて、昇順や降順に並べ替えることもできます。
=SORT(TOCOL(B2:D4, 1))SORT関数と組み合わせれば、2次元の数値データを小さい順に並べた一覧が作れます。全体のランキングを見たいときに便利ですよ。
パターン3: FILTER関数で条件抽出してから1列にする
条件で絞り込んだ結果を1列に変換するパターンです。
=TOCOL(FILTER(B2:D10, A2:A10="営業"), 1)FILTER関数で営業部のデータだけを抽出し、その結果をTOCOL関数で1列にまとめています。部署別の集計データを縦に並べたいときに使えます。
パターン4: ARRAYFORMULA関数と組み合わせて一括計算する
1列に変換した値に対して一括で計算を適用することもできます。
=ARRAYFORMULA(TOCOL(B2:D4) * 1.1)売上データを1列に変換しつつ、すべての値に1.1（税込み換算）を掛けています。ARRAYFORMULA関数と組み合わせれば、変換と計算を1つの数式でまとめられますよ。
パターン5: 複数の範囲を1列に結合する
中カッコ（{}）で複数の範囲を配列として渡すと、まとめて1列に変換できます。
=TOCOL({B2:D4; F2:H4}, 1)セミコロン（;）で縦方向に結合した配列を、TOCOL関数で1列に変換しています。別々のシートや離れた範囲のデータを1つのリストにまとめたいときに便利です。
TOROW関数との違い・使い分け
TOCOL関数には、ペアとなるTOROW関数があります。どちらも2次元データを1次元に変換する関数ですが、出力の方向が異なります。
| 比較項目 | TOCOL関数 | TOROW関数 |
|---|---|---|
| 出力方向 | 縦1列に変換 | 横1行に変換 |
| 構文 | =TOCOL(配列, [無視], [列方向スキャン]) | =TOROW(配列, [無視], [列方向スキャン]) |
| 引数 | まったく同じ（3引数） | まったく同じ（3引数） |
| 第2引数（無視） | 同じ（0/1/2/3） | 同じ（0/1/2/3） |
| 第3引数（スキャン方向） | 同じ（FALSE/TRUE） | 同じ（FALSE/TRUE） |
| 結果の展開方向 | 下方向にスピル | 右方向にスピル |
使い分けの目安はこうです。
- 縦方向にデータを並べたい → TOCOL関数
- 横方向にデータを並べたい → TOROW関数
引数の構造は完全に同じなので、出力方向を変えたいときは関数名を入れ替えるだけで切り替えられます。
実務では、TOCOL関数を使うケースのほうが多いです。スプレッドシートではデータを縦方向に並べるのが基本で、SORT関数やFILTER関数、UNIQUE関数など縦方向のデータを前提とした関数が多いためです。
FLATTEN関数との違い・使い分け
スプレッドシートには、似た機能を持つFLATTEN関数もあります。どちらも2次元データを1次元に変換しますが、機能に違いがあります。
| 比較項目 | TOCOL関数 | FLATTEN関数 |
|---|---|---|
| 出力方向 | 縦1列 | 縦1列 |
| 空白・エラーの除外 | できる（第2引数で指定） | できない |
| スキャン方向の指定 | できる（第3引数で指定） | できない（行方向固定） |
| 複数範囲の結合 | 配列記法（{}）で対応 | 引数を複数指定可能 |
| Excel互換性 | あり（Microsoft 365） | なし（Sheets独自） |
使い分けの目安はこうです。
- 空白除外やスキャン方向の制御が必要 → TOCOL関数
- 複数の離れた範囲をシンプルに結合したい → FLATTEN関数
FLATTEN関数の最大の利点は、複数の範囲を引数として直接指定できることです。
=FLATTEN(A1:C3, E1:G3, I1:K3)TOCOL関数で同じことをするには、中カッコで配列を作る必要があります。
=TOCOL({A1:C3; E1:G3; I1:K3}, 1)ただし、TOCOL関数には空白除外やスキャン方向の制御ができるという大きなメリットがあります。データクレンジングが必要な場面では、TOCOL関数のほうが便利ですよ。
TIP
FLATTEN関数はExcelにはない、Googleスプレッドシート独自の関数です。Excelとの互換性を意識するなら、TOCOL関数を使うのがおすすめです。
よくあるエラーと対処法
TOCOL関数はシンプルですが、使い方によってはエラーが発生します。
| エラー | 原因 | 対処法 |
|---|---|---|
#REF! | 出力先のセルに既にデータがある | 出力先の範囲を空にしてください |
#REF! | 参照先の行や列が削除された | 参照先セルが存在するか確認してください |
#VALUE! | 第2引数に0〜3以外の値を指定した | 0、1、2、3のいずれかを指定してください |
#VALUE! | 第3引数にTRUE/FALSE以外の値を指定した | TRUEまたはFALSEを指定してください |
#NAME? | 関数名のスペルミス | 「TOCOL」のスペルを確認してください |
| 空白が混ざる | 元データに空白セルがある | 第2引数に1または3を指定して空白を除外してください |
特に注意したいのが「出力先のセルに既にデータがある」ケースです。スプレッドシートのスピル展開は、出力先にデータがあると #REF! エラーになります。
TIP
エラー値が混在するデータを扱うときは、第2引数に2（エラー除外）または3（空白+エラー除外）を指定しましょう。IFERRORで個別に対処するよりスマートです。
まとめ
TOCOL関数は、2次元のセル範囲を縦1列に変換するための関数です。空白やエラーの除外、読み取り方向の制御まで備わっており、データ整理の強い味方になります。
この記事のポイントをおさらいしておきましょう。
- TOCOL関数は
=TOCOL(範囲)で、2次元データを縦1列に変換する - 第2引数で空白（1）やエラー（2）を除外できる。両方除外は3を指定する
- 第3引数でスキャン方向を切り替えられる（FALSE: 行方向、TRUE: 列方向）
- TOROW関数はペア関数で、横1行に変換する。引数の構造は同じ
- FLATTEN関数と比べて、空白除外・スキャン方向制御・Excel互換性の点で優れている
まずは =TOCOL(A1:C3) のシンプルな使い方から試してみてください。UNIQUE関数やSORT関数と組み合わせれば、散らばったデータの整理がグッと楽になりますよ。
