【Excel新関数】コピペじゃないよTOCOLだよ！ 表を一発でリスト化するTOCOL関数の基本と実践：Tech TIPS

[小林章彦，デジタルアドバンテージ]

連載目次

対象：Excel 2024／365

Excel新関数：複数行・列を1列にまとめるTOCOL関数の基本と実践
複数行・複数列で構成された表データを集計のために縦1列や横1行へ並べ直す作業は、Excelの実務で頻繁に発生する。従来は手作業のコピー＆ペーストや複雑な数式が必要だったが、新関数「TOCOL」「TOROW」を使えば、たった1つの数式で自動展開できる。本Tech TIPSでは基本構文から実践例、注意点までを詳しく解説する。

　複数行×複数列の表（クロス集計形式）に入力されたデータを、集計や検索のために「縦1列」あるいは「横1行」に並べ直したいという場面は意外と多い。従来は手作業でのコピー＆ペーストや、INDEX関数とINT/MOD関数を組み合わせた複雑な数式で対応するしかなかった。

　しかし、Microsoft 365版やExcel 2024に追加された「TOCOL関数」や「TOROW関数」を使えば、1つの数式で変換が完了する。本Tech TIPSでは、両関数の基本的な使い方から実務で役立つ具体例、従来手法との比較までを詳しく解説する。

TOCOL／TOROW関数とは

　TOCOL関数は指定したセル範囲（配列）を「縦1列」に変換する関数であり、TOROW関数はセル範囲を「横1行」に変換する関数だ。いずれも結果はスピル（動的配列）として出力されるため、数式を入力したセルを起点として必要な数だけ自動的に隣接セルへ展開される。

　基本となる構文は以下の通りである。

=TOCOL(配列, [無視する値], [列単位でスキャン])
=TOROW(配列, [無視する値], [列単位でスキャン])

TOCOL／TOROW関数の基本的な構文

　各引数の意味は以下の通りだ。

引数	必須／省略可	内容
配列	必須	変換したいセル範囲または配列
無視する値	省略可	0または省略：全ての値を保持 1：空白を無視 2：エラーを無視 3：空白とエラーの両方を無視
列単位でスキャン	省略可	FALSEまたは省略：行方向（左→右、上→下）に読み取る TRUE：列方向（上→下、左→右）に読み取る
TOCOL／TOROW関数の引数

対応バージョンに注意

　TOCOL／TOROW関数が利用できるのは、Excel for Microsoft 365（Windows／Mac）、Excel for the web、Excel 2024（Windows／Mac）である。執筆時点でサポート対象であるExcel 2021はスピルに対応しているものの、これらの関数は未搭載のため、数式を入力すると「#NAME?」エラーが発生する。作成したファイルを取り交わす際は、相手の利用環境を事前に確認しておくと安心だ。

使用例：当番表を1列（1行）に変換して集計する

　典型的な利用例として、曜日×週のクロス形式で作られた当番表を集計するケースを考えてみよう。「B2:D4」のセル範囲に次のような当番表があるとする。

表のデータを1列に変換する
画面のような曜日×週のクロス形式で作られた当番表を集計するケースを考えてみよう。そのためには、一度、1列にしたリストを作るとよい。

　この形式のままでは、UNIQUE関数で担当者一覧を作ったり、並べ替えたりといった「リスト」を前提とする処理はしづらい。そこで、空いているセル（例えば「F2」セル）に次の数式を入力する。

=TOCOL(B2:D4)

TOCOL関数で表をリストに変換する

　これだけで、「F2」セルを起点にして「佐藤、鈴木、高橋、田中、佐藤、鈴木、高橋、田中、佐藤」という9件の縦1列のリストがスピルで展開される。既定では行方向（左→右）に読み取るため、第1週の月→火→水、第2週の月→火→……という順になる。

TOCOL関数で表をリストに変換
TOCOL関数で1列にしたいセル範囲を指定する。セル範囲内のデータが1列に並ぶ。

　重複を削除したリストを作成したいのであれば、UNIQUE関数と組み合わせた数式を入力すればよい（UNIQUE関数についてはTech TIPS「【Excel新関数】UNIQUE関数で重複データを除いた自動更新リストを一発作成するテクニック」参照のこと）。

=UNIQUE(TOCOL(B2:D4))

重複を除いたリストを作成する

UNIQUE関数とTOCOL関数を組み合わせて重複を除いたリストを作成
UNIQUE関数を組み合わせることで、重複を除いたリストが作成できる。

　縦1列のリストにしてしまえば、通常のリストと同様に扱える。例えば「佐藤」さんの当番回数を集計したい場合は、次のようにCOUNTIF関数とスピル範囲演算子（#）を組み合わせる。

=COUNTIF(F2#, "佐藤")

作成したリスト内の「佐藤」をカウントする

　「F2#」はF2セルから展開されているスピル範囲全体を参照する記述だ。元の当番表を更新すれば、TOCOL関数の出力結果およびCOUNTIF関数の集計値も自動的に再計算される。

作成したリスト内で特定のデータ数をカウントする
作成したリストをCOUNTIF関数を使ってカウントすれば、特定データの数が把握できる。

　なお、当番表内に未定の空白セルが含まれる場合は、第2引数に「1」を指定して「=TOCOL(B2:D4, 1)」と入力すればよい。これにより空白セルが除外され、入力済みのデータのみが詰めて並べられる。第2引数を省略すると空白セルは「0」として出力されてしまうため、空白を含む表を扱う際は指定を忘れないようにしたい。

　同じ当番表を「横1行」に並べたい場合は、TOROW関数に置き換えるだけである。

=TOROW(B2:D4)

TOROW関数で表をリストに変換する

　結果は「佐藤、鈴木、高橋、田中、……」と横1行に展開される。引数の構成はTOCOLと共通で、縦か横かという出力方向だけが異なる。

TOROW関数で表をリストに変換
TOROW関数を使うとセル範囲を1行に変換できる。

TOCOL関数を使わない場合との比較

　同じ「3行×3列の表を縦1列にする」処理を、TOCOL関数を使わずに実現する方法と比べてみよう。

方法1：手作業のコピー＆ペースト

　各列を順番にコピーし、縦に貼り付けていく方法である。表が小さければ最も手軽だが、元データが変わるたびに同じ作業を繰り返す必要があり、貼り忘れや順序の誤りといったミスも起きやすい。定期的に更新されるデータには向かない。

方法2：INDEX関数＋行番号計算

　数式だけで実現する従来の定番は、INDEX関数とINT／MOD関数を組み合わせる方法だ。3列の表（B2:D4）を1列にする場合、先頭セルに次の数式を入力し、9行分下へコピーする。

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

TOCOL関数を使わずに1列に変換する数式

TOCOL関数を使わずに1列に変換する
INDEX関数などを組み合わせてセル範囲内のデータを取り出す。オートフィルで数式をコピーする。セル範囲のセル数に合わせて数式をコピーする必要があり、セル数を超えてコピーすると「#REF!」エラーとなる。

　この数式は「通し番号を列数（3）で割った商と余りから、参照すべき行と列を割り出す」という仕組みだが、一見して意図が読み取れず、列数が変われば「3」を書き換える必要があり、コピーする行数も自分で計算しなければならない。空白セルは「0」と表示されるため、空白除外にはさらにIF関数やFILTER関数を重ねることになる。TOCOL関数の「=TOCOL(B2:D4, 1)」と比べると、記述量・可読性・保守性のいずれでも差は歴然である。

　ただ、前述のようにTOCOL／TOROW関数はMicrosoft 365およびExcel 2024以降でしかサポートされていない。Excel 2021が使われている環境へExcelシートを送付するなどの可能性がある場合は、INDEX関数とINT／MOD関数の組み合わせを検討する必要がある。

TOCOL関数を利用する際の注意点

　実務でつまずきやすいポイントを挙げておこう。

　1つ目は、「#SPILL!」エラーへの対処だ。TOCOL／TOROW関数の出力サイズは元データのセル数に応じて動的に変化するため、展開予定のセル範囲にデータや文字が入力されているとこのエラーが発生する。出力先の下方向（TOROWの場合は右方向）にはあらかじめ十分な空白領域を確保しておく必要がある。

「#SPILL!」エラーに注意
スピル先に値が入ったセルがあると「#SPILL!」エラーが発生する。スピル先となる列や行には十分な空きを確保しておく必要がある。

　2つ目は「#NAME?」エラーである。前述の通り、これは関数自体が存在しない旧バージョンで開いた場合に表示される。数式の誤りではなくバージョンの問題であることが多い。場合によっては、「=_xlfn.TOCOL(B2:D4)」という数式に変換されることもある。これは、古いバージョンのExcelでサポートされていない新しい関数が使われていることを示すもので、関数としては機能しないため、表の値を変更してもリストの値は変更されない（再計算されない）ので注意が必要だ。

古いバージョンのExcelで開く際に注意（1）
Excel 2021以前のバージョンでTOCOL関数を使おうとすると、「#NAME?」エラーとなる。

古いバージョンのExcelで開く際に注意（2）
TOCOL関数を使ったシートを開くと、データは表示されるものの、「=_xlfn.TOCOL(B2:D4)」という数式に変換されてしまう。これは、古いバージョンのExcelでサポートされていない新しい関数が使われていることを示すもので、関数としては機能しないため、表の値を変更してもリストの値は変更されない（再計算されない）。

　そして3つ目は、スピルで出力された結果が参照用であり、出力先のセルを直接編集することはできない点だ。変換後のデータを個別に編集したい場合は、スピル範囲全体をコピーして「値として貼り付け」を実行するか、他の関数で参照して二次加工を行う必要がある。

　TOCOL／TOROW関数は、「表形式のデータを1列／1行に並べ直す」という地味だが頻出の作業を、1つの短い数式に置き換えてくれる。UNIQUEやSORT、FILTER、TAKEといったスピル系関数と組み合わせれば、従来は作業列やピボットテーブルを要した処理もシート上の数式だけで完結する。Microsoft 365またはExcel 2024の環境であれば、覚えておいて損のない関数である。