複数列にまたがるデータを1列にまとめたいとき、ありませんか。
手作業でコピペを繰り返すのは面倒ですし、VBAを書くのは大げさですよね。
TOCOL関数を使えば、数式ひとつで2次元データを縦1列に変換できます。空白やエラーの除外もオプションで指定できるので、データの前処理にぴったりです。
この記事では、TOCOL関数の基本から実務での活用例まで解説します。
TOCOL関数とは
TOCOL関数は、配列やセル範囲を縦1列に変換する関数です。読み方は「トゥーコル」で、”TO COLumn”（列へ変換）の略です。
たとえば3行×4列の表なら、12行×1列に展開できます。結果はスピルで自動展開されるので、出力セルを1つずつ指定する必要はありません。
TOCOL関数でできることを整理すると、次のようになります。
- 複数列に分散したデータを縦1列にまとめる
- 空白セルやエラー値を除外して詰めた一覧を作る
- 行方向・列方向の2通りのスキャン順序を選べる
- UNIQUE・FILTER・SORTなど他の配列関数と組み合わせて柔軟に加工できる
対応バージョンはMicrosoft 365とExcel 2024です。Excel 2021では使用できないので注意してください。Excel 2021はスピル対応済みですが、TOCOL関数自体は含まれていません。
TOCOL関数の構文と引数
=TOCOL(配列, [無視], [scan_by_column])| 引数 | 必須/省略 | 説明 |
|---|---|---|
| 配列 | 必須 | 1列に変換したい配列またはセル範囲 |
| 無視 | 省略可 | 除外する値の種類を指定（0〜3） |
| scan_by_column | 省略可 | スキャン方向を指定（FALSEまたはTRUE） |
引数「無視」の値
| 値 | 動作 |
|---|---|
| 0（省略時） | すべての値を保持する |
| 1 | 空白セルを除外する |
| 2 | エラー値を除外する |
| 3 | 空白とエラーの両方を除外する |
第2引数を省略すると0と同じ扱いです。空白やエラーが混在するデータでは、1や3を指定すると余計な行が入りません。
引数「scan_by_column」の動作
次のような2行3列のデータで考えます。
| A列 | B列 | C列 |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
- FALSE（省略時）：1, 2, 3, 4, 5, 6の順になります。行を左から右へ読む動きです。
- TRUE：1, 4, 2, 5, 3, 6の順になります。列を上から下へ読む動きです。
各行が1レコードの表ならFALSEが自然です。月別データなど列単位のまとまりがあればTRUEを使います。
TOCOL関数の基本的な使い方
2次元データをそのまま1列にする
A2:C4に3行3列のデータがある場合です。
=TOCOL(A2:C4)行順（左→右→次の行）で9行1列に展開されます。
空白セルを除外して1列にする
データに空白が混在している場合です。第2引数に1を指定します。
=TOCOL(A2:C10, 1)空白セルを飛ばし、値だけを詰めた1列が返ります。
エラー値を除外して1列にする
#N/Aや#VALUE!が含まれるデータ向けです。
=TOCOL(A2:C10, 2)エラーセルを除外して値だけを1列にします。
列方向にスキャンして1列にする
第3引数にTRUEを指定します。
=TOCOL(A2:C4, 0, TRUE)列単位で上から下へ読み取ります。月別に並んだ列を時系列で1列にしたいときに向いています。
TOCOL関数とTOROW関数の違いと使い分け
TOROW関数は、配列を横1行に変換する関数です。TOCOLとは出力の方向だけが異なります。
| 項目 | TOCOL | TOROW |
|---|---|---|
| 出力方向 | 縦1列 | 横1行 |
| 引数構成 | 同じ | 同じ |
| 空白/エラー除外 | できる | できる |
使い分けのポイントは、後続の関数が何を求めるかです。XLOOKUPの検索範囲は縦配列なのでTOCOLが向いています。横に並べたい場面ではTOROWを選びます。
なお、従来のTRANSPOSE関数は行列の転置のみです。2次元を1次元にフラット化するのはTOCOL/TOROWの役割です。
他の配列関数とTOCOL関数の組み合わせ活用
TOCOL関数は他の配列関数と組み合わせると、活用の幅が広がります。
HSTACK / VSTACKとの組み合わせ
離れた列をHSTACK関数で横結合してから1列にできます。
=TOCOL(HSTACK(A2:A10, C2:C10, E2:E10))複数シートのデータはVSTACK関数で縦結合してから渡します。
=TOCOL(VSTACK(Sheet1!A2:D5, Sheet2!A2:D5), 1)UNIQUEとの組み合わせ
複数列のデータを1列にまとめて重複を除去できます。
=UNIQUE(TOCOL(A2:D20, 1))UNIQUE関数で一意の値だけを取り出せます。
FILTERとの組み合わせ
FILTER関数の抽出結果を1列に整形できます。
=TOCOL(FILTER(A2:D10, B2:B10="東京"), 1)SORTとの組み合わせ
1列にしたデータをSORT関数で並び替えできます。
=SORT(TOCOL(A2:C10, 1))WRAPCOLS / WRAPROWSとの関係
TOCOLで1列にしたデータを別の列数で並べ直したいときがあります。WRAPCOLS関数なら、1列のデータを指定列数で折り返して2次元に再形成できます。WRAPROWSは行数で折り返す関数です。
| 関数 | 変換方向 | 用途 |
|---|---|---|
| TOCOL | 2次元 → 縦1列 | 複数列を縦1列にフラット化 |
| TOROW | 2次元 → 横1行 | 複数列を横1行にフラット化 |
| WRAPCOLS | 縦1列 → 2次元 | 1列を指定列数で折り返して2次元に変換 |
| WRAPROWS | 横1行 → 2次元 | 1行を指定行数で折り返して2次元に変換 |
TOCOLとWRAPCOLSはちょうど逆の操作にあたります。「1列にしてから別の列数で並べ直したい」ときはこの2つをセットで使うと便利です。
TOCOL関数の実務活用例5選
名簿管理：部署別の名前を1列にまとめる
部署ごとに列が分かれた名簿をTOCOLで1列にします。空白除外を指定すれば、人数差による空白も飛ばせます。そのままXLOOKUPの検索リストとして使えます。
=TOCOL(A2:E20, 1)商品管理：カテゴリ別コードの重複チェック
カテゴリ別に分散した商品コードを1列にまとめ、UNIQUEで重複を検出します。元データより行数が少なければ重複ありです。
=UNIQUE(TOCOL(A2:D50, 1))アンケート集計：複数列の回答をCOUNTIF集計
自由回答が複数列にまたがるとき、TOCOLで1列にしてからCOUNTIFで集計します。列ごとに数式を書く手間が省けます。
=COUNTIF(TOCOL(B2:D100, 1), "満足")データ前処理：横結合→1列→重複除去
HSTACKで横結合した表をTOCOLで1列にし、UNIQUEで重複を除去する3段階の流れです。
=UNIQUE(TOCOL(HSTACK(A2:A50, C2:C50), 1))レポート生成：条件抽出→1列→並び替え
FILTERで条件に合うデータを抽出し、TOCOLで1列にしてからSORTで昇順に並べます。
=SORT(TOCOL(FILTER(A2:D20, E2:E20>=100), 1))条件に合う値だけを並び替えた一覧を作れます。
TOCOL関数のよくあるエラーと対処法
| エラー | 原因 | 対処法 |
|---|---|---|
| #SPILL! | 出力先の下に既存データがある | 出力先の下方向を空けてください |
| #NUM! | 配列が大きすぎて列に収まらない | より小さい範囲を参照してください |
| #VALUE! | 引数に不正な値を指定している | 各引数の値を確認してください |
| #NAME? | 非対応バージョンで使用している | Microsoft 365またはExcel 2024で使用してください |
| #CALC! | 結果が空（FILTER等の組み合わせ時） | 入力データや条件を見直してください |
よく出るのは#NAME?と#SPILL!の2つです。#NAME?が出たら、まずバージョンを確認しましょう。TOCOL関数はMicrosoft 365とExcel 2024で使えます。Excel 2021では対応していません。
#SPILL!は出力先の下に既存データがある場合に発生します。TOCOL関数の出力行数は可変なので、出力先セルの下は十分に空けておいてください。
#CALC!はFILTERと組み合わせたときに条件に一致する行がゼロの場合に発生します。IFERRORで囲むか、条件式を見直してください。
まとめ
TOCOL関数は、2次元データを縦1列に変換する関数です。
- 第2引数で空白やエラーを除外できる
- 第3引数でスキャン方向を切り替えられる
- UNIQUE・FILTER・SORTと組み合わせて柔軟にデータ加工できる
- 対応バージョンはMicrosoft 365とExcel 2024
手作業でやっていたデータ整形の多くをTOCOL関数で自動化できます。まずは基本の1列変換から試してみてください。
配列操作の関連関数もチェックしておくと便利です。
