TOCOL関数の使い方｜配列を1列に変換・空白除外・実務活用5例

スポンサーリンク

複数列にまたがるデータを1列にまとめたいとき、ありませんか。

手作業でコピペを繰り返すのは面倒ですし、VBAを書くのは大げさですよね。

TOCOL関数を使えば、数式ひとつで2次元データを縦1列に変換できます。空白やエラーの除外もオプションで指定できるので、データの前処理にぴったりです。

この記事では、TOCOL関数の基本から実務での活用例まで解説します。

  1. TOCOL関数とは
  2. TOCOL関数の構文と引数
    1. 引数「無視」の値
    2. 引数「scan_by_column」の動作
  3. TOCOL関数の基本的な使い方
    1. 2次元データをそのまま1列にする
    2. 空白セルを除外して1列にする
    3. エラー値を除外して1列にする
    4. 列方向にスキャンして1列にする
  4. TOCOL関数とTOROW関数の違いと使い分け
  5. 他の配列関数とTOCOL関数の組み合わせ活用
    1. HSTACK / VSTACKとの組み合わせ
    2. UNIQUEとの組み合わせ
    3. FILTERとの組み合わせ
    4. SORTとの組み合わせ
  6. WRAPCOLS / WRAPROWSとの関係
  7. TOCOL関数の実務活用例5選
    1. 名簿管理：部署別の名前を1列にまとめる
    2. 商品管理：カテゴリ別コードの重複チェック
    3. アンケート集計：複数列の回答をCOUNTIF集計
    4. データ前処理：横結合→1列→重複除去
    5. レポート生成：条件抽出→1列→並び替え
  8. TOCOL関数のよくあるエラーと対処法
  9. TAKE・DROP と組み合わせて先頭・末尾N件を取り出す
  10. BYROW + TOCOL で行ごとに列を縦結合する
  11. INDIRECT と組み合わせて複数シートのデータを1列に集約する
  12. TRIMRANGE と組み合わせて端の空白を除去してから1列に変換する
  13. TOCOL関数が重い・遅いときの対処法
    1. 参照範囲を必要最小限に絞る
    2. テーブル機能で動的範囲化する
    3. 揮発性関数との併用を避ける
    4. 結果を値貼り付けに置き換える
  14. GoogleスプレッドシートでTOCOLを使う方法
  15. Excel 2021以前のバージョンで同じ結果を得る代替手段
    1. Power Queryの「列のピボット解除」を使う
    2. INDEX関数とROW・COLUMN式で再現する
  16. まとめ

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列
123
456
  • 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とは出力の方向だけが異なります。

項目TOCOLTOROW
出力方向縦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は行数で折り返す関数です。

関数変換方向用途
TOCOL2次元 → 縦1列複数列を縦1列にフラット化
TOROW2次元 → 横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で囲むか、条件式を見直してください。

TAKE・DROP と組み合わせて先頭・末尾N件を取り出す

TOCOLで1列に変換したあと、TAKE関数やDROP関数で先頭や末尾のN件だけを取り出せます。「複数列にまたがるデータを1列に並べ、そのうち上位10件だけを表示したい」といった場面でよく使います。

' 複数列データを1列にしてから上位5件を取り出す
=TAKE(SORT(TOCOL(A2:D20, 1), 1, -1), 5)

SORTで降順に並び替えた1列データから、TAKEで先頭5件（=上位5件）を取得しています。上位N件を動的に変えたいときはTAKEの第2引数をセル参照にしておくと便利です。

逆にDROPを使えばヘッダー行や不要な先頭行を除いてから処理できます。

' 先頭1行を除いてから1列に変換する
=TOCOL(DROP(A1:D10, 1), 1)

TAKE・DROPはMicrosoft 365とExcel 2024で使用できます。TOCOLと同じ対応バージョンなので、セットで使っても問題ありません。

BYROW + TOCOL で行ごとに列を縦結合する

BYROW関数とTOCOLを組み合わせると、各行の複数列を「行ごとに結合した文字列」に変換できます。たとえば「姓・名・部署が3列に分かれているデータを1列のフルラベルにしたい」場面で役立ちます。

' 各行の3列（A:C列）を「姓 名（部署）」形式で結合して1列に返す
=BYROW(A2:C10, LAMBDA(row, TOCOL(row, 1)(1) & " " & TOCOL(row, 1)(2) & "（" & TOCOL(row, 1)(3) & "）"))

ただし、TEXTJOIN関数の方がシンプルなケースも多いです。BYROW + TOCOLは「行ごとに可変個の値を動的に結合する」など複雑な処理を共通化するときに有効です。

より実用的なパターンとして、各行の非空白値だけを抽出してカンマ区切りにする例を示します。

' 各行の空白以外の値をカンマ区切りで1セルに収める
=BYROW(A2:E10, LAMBDA(row, TEXTJOIN(", ", TRUE, TOCOL(row, 1))))

TOCOL(row, 1)で空白を除いた値だけ取り出し、TEXTJOINでつなげています。アンケートの複数選択肢や、月によって入力列が変わるような不規則なデータの集約に使えます。

INDIRECT と組み合わせて複数シートのデータを1列に集約する

月別・支店別などシートが分かれているデータを1列にまとめたいときは、INDIRECTと組み合わせる方法があります。ただしINDIRECTは揮発性関数（ファイルを開くたびに全セル再計算）のため、大量データでは速度に注意が必要です。

' シート名リスト（E列）を参照して各シートのA2:B10を縦積みしてから1列に変換する
=TOCOL(VSTACK(
    INDIRECT(E2 & "!A2:B10"),
    INDIRECT(E3 & "!A2:B10"),
    INDIRECT(E4 & "!A2:B10")
), 1)

シート名をE列に書いておけば、数式を変えずにシート名だけを更新できます。シート数が多い場合は、REDUCE + VSTACKを使うとリストから動的に縦積みできます。

' シート名リスト（E2:E13）を全件ループしてVSTACKで縦積みしてからTOCOLで1列にする
=TOCOL(REDUCE("", E2:E13, LAMBDA(acc, name,
    VSTACK(IF(acc="", INDIRECT(name & "!A2:B10"), acc), INDIRECT(name & "!A2:B10"))
)), 1)

REDUCE + VSTACKパターンはシート名を増やすだけで自動的に拡張されるため、月次レポートの自動集計などに活用できます。VSTACK関数の使い方もあわせて確認しておくと理解が深まりますよ。

TRIMRANGE と組み合わせて端の空白を除去してから1列に変換する

Microsoft 365 の TRIMRANGE関数 は、配列の先頭・末尾にある空白行または空白列を取り除いて返す関数です。TOCOLと組み合わせると、「入力範囲の端に余分な空白行があっても、それを除外してから1列に変換する」処理が1本の数式で書けます。

=TOCOL(TRIMRANGE(A2:D20), 1)

TRIMRANGE(A2:D20) が末尾の空白行を除いた部分だけを返し、TOCOLがその結果を縦1列に展開します。第2引数の 1 で残った空白セルも除外するので、結果は値だけが詰まったきれいな1列になります。

TRIMRANGE の第2引数でトリム方向を細かく指定できます。

値動作
0（省略時）上下左右すべての端を削除
1上端（先頭行）のみ削除
2下端（末尾行）のみ削除
3上下両端を削除
4左端（先頭列）のみ削除
8右端（末尾列）のみ削除
12左右両端を削除

末尾の空白行だけ除きたい場合は TRIMRANGE(A2:D20, 2) とすれば十分です。TRIMRANGE は Microsoft 365 の月次チャンネルで提供されており、TOCOL と対応バージョンが揃っているため、データ前処理のパイプラインとして自然に組み合わせられます。

TOCOL関数が重い・遅いときの対処法

TOCOL関数は大量データに対して使うと動作が重くなることがあります。原因は主に2つです。

  • 出力行数が数万行を超えると、スピル領域全体の再計算に時間がかかる
  • INDIRECTやOFFSETといった揮発性関数と併用すると、編集のたびに全セルを再計算する

対処法をいくつか紹介します。

参照範囲を必要最小限に絞る

A:D のような列全体ではなく、A2:D5000 のように行数を限定してください。Excelは列全体指定だと約100万行を内部的に処理しようとするため、TOCOLとの相性がよくありません。

テーブル機能で動的範囲化する

データに「テーブル機能」（Ctrl+T）を適用すると、範囲が自動で拡張・縮小されます。テーブル名で参照すれば、空白行まで読みに行かないので無駄な再計算を防げます。

=TOCOL(テーブル1[#データ], 1)

揮発性関数との併用を避ける

INDIRECTやOFFSETを使うと、シート全体の再計算が走ります。可能ならINDEX関数など非揮発性の参照に置き換えてください。複数シート集約の場合は、Sheet1!A2:B10 のように直接参照を使う方が軽くなります。

結果を値貼り付けに置き換える

最終出力が確定したら、TOCOLのスピル範囲をコピーして「値の貼り付け」に置き換えるのも有効です。リアルタイム更新が不要なレポートでは、計算負荷をゼロにできます。

GoogleスプレッドシートでTOCOLを使う方法

Googleスプレッドシートには、TOCOL関数がありません（2026年5月時点）。代わりにFLATTEN関数が同じ役割を果たします。

=FLATTEN(A2:C10)

FLATTENは2次元配列を縦1列に展開する関数です。基本動作はTOCOLと同じですが、いくつか違いがあります。

項目Excel: TOCOLSheets: FLATTEN
空白除外オプション第2引数で指定可なし
エラー除外オプション第2引数で指定可なし
スキャン方向切替第3引数で指定可なし（行方向固定）

空白を除外したい場合は、FILTERで囲んでください。

=FILTER(FLATTEN(A2:C10), FLATTEN(A2:C10)<>"")

列方向にスキャンしたい場合は、TRANSPOSEで転置してからFLATTENにかけます。

=FLATTEN(TRANSPOSE(A2:C10))

ExcelとGoogleスプレッドシートで同じデータを扱う場合、関数名を切り替える必要があります。共有ファイルの互換性に注意してください。

Excel 2021以前のバージョンで同じ結果を得る代替手段

TOCOL関数はMicrosoft 365とExcel 2024でしか使えません。Excel 2021・2019・2016を使っている場合は、別の方法で代替できます。

Power Queryの「列のピボット解除」を使う

Power QueryはExcel 2016以降に標準搭載されている機能です。マトリクス表を1列のリスト形式に変換できます。

  1. データ範囲を選択して「データ」タブから「テーブルまたは範囲から」を選ぶ
  2. Power Queryエディタで対象列を選択する
  3. 「変換」タブの「列のピボット解除」をクリックする
  4. 「閉じて読み込む」で結果をシートに返す

属性列と値列の2列構成で出力されるので、不要な属性列を削除すれば1列データになります。マウス操作だけで完了するので、関数が使えなくても対応できます。

INDEX関数とROW・COLUMN式で再現する

数式で代替する場合は、INDEXと行番号計算を組み合わせます。3行4列のデータ（A2:D4）を1列にする例です。

=INDEX($A$2:$D$4, INT((ROW(A1)-1)/4)+1, MOD(ROW(A1)-1, 4)+1)

下方向にコピーすると、行優先で1列に並びます。/4 と MOD(..., 4) の数字は列数（この例では4）に合わせて変更してください。スピルがないため、必要な行数だけ手動でコピーする必要があります。

業務でTOCOLを継続的に使うなら、Microsoft 365へのアップデートを検討する方が結果的に楽です。

まとめ

TOCOL関数は、2次元データを縦1列に変換する関数です。

  • 第2引数で空白やエラーを除外できる
  • 第3引数でスキャン方向を切り替えられる
  • UNIQUE・FILTER・SORTと組み合わせて柔軟にデータ加工できる
  • 対応バージョンはMicrosoft 365とExcel 2024

手作業でやっていたデータ整形の多くをTOCOL関数で自動化できます。まずは基本の1列変換から試してみてください。

配列操作の関連関数もチェックしておくと便利です。

スポンサーリンク
タイトルとURLをコピーしました