今回は、ExcelのTOCOL関数とTOROW関数を使って、複数行・複数列に分かれたデータを1列に並べる、または1行に並べる方法を紹介します。
TOCOL関数とTOROW関数は、Microsoftのサポート情報によると、Excel for Microsoft 365（Windows版・Mac版）、Excel 2024、Excel 2024 for Macで利用できます。Excel 2021以前のバージョンでは使えないため、ファイルを共有する相手の環境も確認しておくと安心です。
TOCOL関数・TOROW関数でできること
表の形で入力されたデータを、縦一列や横一行にまとめ直したい場面は意外と多くあります。
- 週ごと・担当者ごとに横並びで入力された名前を、1列の名簿にしたい
- 複数列に散らばった商品コードを、1列に集めて重複を確認したい
- 入力欄が表形式になっているアンケート結果を、集計しやすい形に直したい
これまでは、コピーと貼り付けを繰り返したり、INDEX関数などを組み合わせた複雑な数式を作ったりする必要がありました。TOCOL関数を使えば、範囲を指定するだけで1列の配列として結果を返せます。TOROW関数は同じ考え方で、結果を1行として返します。
TOCOL関数の書式と引数
TOCOL関数の書式は次のとおりです。
=TOCOL(配列, [無視], [列でスキャン])
- 配列：1列にしたい範囲や配列を指定します。
- 無視（省略可）：特定の種類の値を除くかどうかを指定します。0はすべて保持（既定）、1は空白を無視、2はエラーを無視、3は空白とエラーの両方を無視します。
- 列でスキャン（省略可）：省略またはFALSEの場合は行単位で読み取り、TRUEの場合は列単位で読み取ります。
TOROW関数も引数の構成は同じで、結果が横方向の1行になる点だけが異なります。
基本の使い方
たとえば、A2:D4に3行4列の名前が入っているとします。空いているセルに次のように入力します。
=TOCOL(A2:D4)
Enterキーを押すと、結果が下方向へ自動的に広がり（スピル）、A2、B2、C2、D2、A3…の順に1列で表示されます。既定では行ごとに左から右へ読み取ってから次の行へ進む順番です。
列ごとの順番で並べたいとき
A列を上から下へ読み、次にB列へ進む順番にしたい場合は、3番目の引数にTRUEを指定します。
=TOCOL(A2:D4,,TRUE)
担当者ごとに列が分かれている表など、列単位でまとまりのあるデータでは、こちらのほうが元の意味を保った並び順になります。
空白やエラーを除いて並べるコツ
TOCOL関数で注意したいのが空白セルの扱いです。既定の設定では、空白セルは0として返されます。入力欄に空きがある表をそのまま変換すると、結果の中に0が混ざってしまいます。
空白を除きたい場合は、2番目の引数に1を指定します。
=TOCOL(A2:D4,1)
さらに、元データに#N/Aなどのエラーが含まれている場合は、3を指定すると空白とエラーの両方を除外できます。
=TOCOL(A2:D4,3)
列でスキャンと組み合わせる
空白を除きつつ列ごとの順番で並べたい場合は、引数を組み合わせて指定します。
=TOCOL(A2:D4,1,TRUE)
他の関数と組み合わせる活用例
TOCOL関数は結果を配列として返すため、ほかの動的配列関数と組み合わせやすい点が特長です。
重複を除いた一覧を作る
複数列に入力された名前から、重複のない一覧を作りたい場合は、UNIQUE関数で囲みます。
=UNIQUE(TOCOL(A2:D4,1))
並べ替えた一覧を作る
さらにSORT関数を組み合わせると、重複を除いたうえで並べ替えた一覧になります。
=SORT(UNIQUE(TOCOL(A2:D4,1)))
この形は、入力規則（ドロップダウンリスト）の元データを作るときなどにも使いやすい組み合わせです。
件数を数える
空白を除いて1列にした結果をROWS関数で囲むと、入力済みの項目数を確認できます。
=ROWS(TOCOL(A2:D4,1))
うまく表示されないときの確認ポイント
- #SPILL!エラーが出る：結果を表示する範囲に別の値が入っていると、結果を展開できません。数式の下（TOROW関数なら右側）のセルを空けておきます。
- #NUM!エラーが出る：Microsoftのサポート情報では、配列が大きすぎる場合に返されるエラーとされています。範囲を必要な部分に絞ります。
- #VALUE!エラーが出る：配列定数に整数以外の数値が含まれている場合に返されることがあります。
- 0が表示される：空白セルが0として返されています。2番目の引数に1または3を指定します。
- #NAME?エラーが出る：TOCOL関数・TOROW関数に対応していないバージョンで開いている可能性があります。
TOROW関数の使いどころ
TOROW関数は、データを横一行に並べたいときに使います。たとえば、縦に並んだ複数列の担当者名を、報告書の見出し行のように横方向へ並べたい場合です。
=TOROW(A2:D4,1)
と入力すると、空白を除いた値が右方向へ展開されます。TOCOL関数と同じく、3番目の引数にTRUEを指定すれば列ごとの順番で並べられます。
TRANSPOSE関数との違い
行と列を入れ替える関数にはTRANSPOSE関数もあります。TRANSPOSE関数は表の形を保ったまま縦横を入れ替えるのに対し、TOROW関数やTOCOL関数は表の形をなくして1行・1列にまとめる点が異なります。2次元の表を縦横入れ替えたいのか、1本の並びにしたいのかで使い分けると、目的に合った結果になります。
範囲は必要な部分だけを指定する
列全体（A:Dなど）を指定すると、空白のセルまで読み取りの対象になり、処理が重くなったり不要な0が並んだりする原因になります。データの入っている範囲に絞るか、テーブル機能を使って範囲を管理すると、データの追加にも対応しやすくなります。
結果を値として残したい場合
TOCOL関数の結果は、元データを修正すると自動的に更新されます。変換後のデータを固定して別の用途に使いたい場合は、結果の範囲をコピーし、［形式を選択して貼り付け］から値として貼り付けると、数式ではなく値として残せます。元データとの連動が不要になったタイミングで行うとよいでしょう。
まとめ
TOCOL関数とTOROW関数を使うと、表の形で散らばったデータを1列・1行に並べる作業を数式1つで行えます。
- TOCOL関数は1列、TOROW関数は1行に並べて返す
- 既定は行ごとに読み取り、3番目の引数にTRUEを指定すると列ごとに読み取る
- 空白は既定で0になるため、2番目の引数に1（空白とエラーを除く場合は3）を指定する
- UNIQUE関数やSORT関数と組み合わせると、重複のない一覧や並べ替えた一覧を作れる
- 利用できるのはMicrosoft 365とExcel 2024以降のため、共有先の環境に注意する
コピーと貼り付けで並べ直していた作業がある場合は、TOCOL関数への置き換えを試してみると、元データの変更にも追従できる一覧を作れます。