【Excel】TOCOLとTOROW関数で2次元を1列に変換する!Excelの配列フラット化テクニック

Excelで複数のセル範囲を1つの列にまとめたい場面があります。特に、2次元の配列データを1次元のリストに変換する作業は、データ分析の前処理で頻繁に発生します。しかし、手作業でコピー&ペーストを繰り返すと、手間がかかりミスも起こりやすくなります。この記事では、ExcelのTOCOL関数とTOROW関数を使って、2次元配列を簡単に1列に変換するテクニックを解説します。これらの関数を使いこなせば、データの整形作業を劇的に効率化できます。

TOCOL関数とTOROW関数は、動く配列を返す新しい関数です。これらの関数を理解することで、Excelでのデータ操作の幅が大きく広がります。本記事を読めば、複雑なデータ整形作業をスムーズに行えるようになります。

TOCOL関数とTOROW関数の概要とできること

TOCOL関数とTOROW関数は、Excelの動く配列関数群の一つです。これらの関数は、指定した範囲の配列を、指定した次元(行または列)に変換(フラット化)します。これにより、複数のセル範囲や配列を、簡単に1つの列または1つの行にまとめることが可能です。これらの関数はExcel for Microsoft 365で利用できます。

TOCOL関数は、配列を列方向にフラット化します。つまり、2次元の配列を1列の配列に変換する際に使用します。TOROW関数は、配列を行方向にフラット化します。2次元の配列を1行の配列に変換する際に使用します。これらの関数は、データ集計やグラフ作成の前処理で非常に役立ちます。

TOCOL関数で2次元配列を1列に変換する手順

TOCOL関数は、配列を縦方向、つまり1列の配列に変換する関数です。基本的な構文は以下の通りです。

TOCOL(配列, [無視], [空を挿入])

• 配列:変換したい配列またはセル範囲を指定します。
• 無視(省略可能):0(すべて含める)、1(空白を無視)、2(エラーを無視)、3(空白とエラーを無視)のいずれかを指定します。デフォルトは0です。
• 空を挿入(省略可能):TRUE(空の行を挿入)、FALSE(空の行を挿入しない)を指定します。デフォルトはFALSEです。

具体的な使用例を見てみましょう。例えば、A1:B3の範囲とD1:E3の範囲のデータを1列にまとめたいとします。

1. 変換したいデータを準備する
   Excelシートに、以下のようなデータを入力します。

   A1:B3の範囲:

   A1: 1, A2: 2, A3: 3

   B1: 4, B2: 5, B3: 6

   D1: 7, D1: 8, D1: 9

   E1: 10, E2: 11, E3: 12

2. TOCOL関数を入力するセルを選択する
   結果を表示したいセル(例:G1セル)を選択します。
3. TOCOL関数を入力する
   G1セルに以下の数式を入力します。

   =TOCOL(A1:B3, 1, TRUE)

   この数式は、A1:B3の範囲を変換し、空白を無視し、変換後の配列に空の行を挿入する設定です。しかし、A1:B3は2次元配列なので、このままでは意図した結果にならない場合があります。複数の範囲を連結するには、CHOOSE関数やHSTACK関数などを組み合わせる必要があります。

4. 複数の範囲を連結してTOCOL関数で変換する
   複数の範囲を1つの配列としてTOCOL関数に渡すには、CHOOSE関数やHSTACK関数と組み合わせます。例えば、A1:B3とD1:E3のデータを1列に変換するには、G1セルに以下の数式を入力します。

   =TOCOL(HSTACK(A1:B3, D1:E3), 1)

   HSTACK関数は、指定した配列を横方向に連結します。ここでは、A1:B3とD1:E3を横に連結した結果を、TOCOL関数で1列に変換しています。空白は無視(引数1)するように指定しています。

TOROW関数で2次元配列を行に変換する手順

TOROW関数は、配列を横方向、つまり1行の配列に変換する関数です。基本的な構文は以下の通りです。

TOROW(配列, [無視], [空を挿入])

• 配列:変換したい配列またはセル範囲を指定します。
• 無視(省略可能):0(すべて含める)、1(空白を無視)、2(エラーを無視)、3(空白とエラーを無視)のいずれかを指定します。デフォルトは0です。
• 空を挿入(省略可能):TRUE(空の列を挿入)、FALSE(空の列を挿入しない)を指定します。デフォルトはFALSEです。

具体的な使用例を見てみましょう。例えば、A1:C2の範囲のデータを1行にまとめたいとします。

1. 変換したいデータを準備する
   Excelシートに、以下のようなデータを入力します。

   A1: 1, B1: 2, C1: 3

   A2: 4, B2: 5, C2: 6

2. TOROW関数を入力するセルを選択する
   結果を表示したいセル(例:E1セル)を選択します。
3. TOROW関数を入力する
   E1セルに以下の数式を入力します。

   =TOROW(A1:C2, 1)

   この数式は、A1:C2の範囲を変換し、空白を無視するように指定しています。結果として、1,2,3,4,5,6が1行に表示されます。

4. 複数の範囲を連結してTOROW関数で変換する
   複数の範囲を1つの配列としてTOROW関数に渡すには、VSTACK関数やCHOOSE関数と組み合わせます。例えば、A1:C2とA4:C5のデータを1行に変換するには、E1セルに以下の数式を入力します。

   =TOROW(VSTACK(A1:C2, A4:C5), 1)

   VSTACK関数は、指定した配列を縦に連結します。ここでは、A1:C2とA4:C5を縦に連結した結果を、TOROW関数で1行に変換しています。空白は無視(引数1)するように指定しています。

TOCOL関数とTOROW関数の応用テクニック

TOCOL関数とTOROW関数は、単独で使用するだけでなく、他の動く配列関数と組み合わせることで、さらに強力なデータ整形ツールとなります。ここでは、いくつかの応用テクニックを紹介します。

重複データの削除

TOCOL関数やTOROW関数で結合したデータに重複が含まれる場合、UNIQUE関数と組み合わせることで、重複を削除したリストを作成できます。

例えば、A1:B3とD1:E3のデータを結合して重複を削除したい場合は、以下の数式を使用します。

=UNIQUE(TOCOL(HSTACK(A1:B3, D1:E3), 1))

この数式は、まずHSTACK関数で2つの範囲を横に連結し、次にTOCOL関数で1列に変換します。最後にUNIQUE関数で重複する値を取り除いた結果を返します。

条件に合うデータのみを抽出

FILTER関数と組み合わせることで、特定の条件に合うデータだけを抽出して1列にまとめることができます。

例えば、A1:B5の範囲で、A列の値が10より大きいデータのみを1列に抽出したい場合は、以下の数式を使用します。

=TOCOL(FILTER(A1:B5, A1:A5>10), 1)

FILTER関数で条件に合う行を抽出し、その結果をTOCOL関数で1列に変換しています。空白は無視するように設定しています。

並べ替えと条件抽出の組み合わせ

SORT関数、FILTER関数、TOCOL関数を組み合わせることで、条件に合うデータを抽出して並べ替えたリストを作成できます。

例えば、A1:B5の範囲で、A列の値が10より大きいデータを抽出し、B列の値で昇順に並べ替えて1列に表示したい場合は、以下の数式を使用します。

=TOCOL(SORT(FILTER(A1:B5, A1:A5>10), 2, 1), 1)

この数式では、FILTER関数で条件に合うデータを抽出し、SORT関数でB列(2番目の列)を昇順(1)で並べ替えています。最後にTOCOL関数で1列に変換しています。

TOCOL関数とTOROW関数でよくある質問と注意点

TOCOL関数とTOROW関数は非常に便利ですが、いくつか注意すべき点や、よくある疑問点があります。

Excelのバージョンについて

TOCOL関数とTOROW関数は、Excel for Microsoft 365で利用できる比較的新しい関数です。Excel 2019以前のバージョンでは利用できません。これらのバージョンで同様の処理を行いたい場合は、VBAや他の関数を組み合わせた複雑な数式が必要になります。

配列の結合順序

複数の範囲を結合してTOCOL関数やTOROW関数を使用する場合、配列を結合する関数(HSTACK, VSTACK, CHOOSEなど)の引数順序が結果に影響します。意図した順序でデータが結合されるように、配列の指定順序を正確に確認してください。

エラー値の扱い

TOCOL関数やTOROW関数の無視引数でエラーを無視する設定(2または3)を指定しない場合、配列内にエラー値が含まれていると、関数全体がエラーを返すことがあります。データにエラーが含まれる可能性がある場合は、無視引数を適切に設定するか、事前にエラー処理を行ってください。

無限ループの可能性

動く配列関数は、数式が参照するセル範囲が数式自身に影響を及ぼすような設定(循環参照)になると、無限ループエラー(#SPILL!エラー)が発生することがあります。特に、TOCOL関数やTOROW関数で変換した結果の範囲を、数式が再度参照しないように注意が必要です。

TOCOL関数とTOROW関数の比較

TOCOL関数とTOROW関数は、配列をフラット化するという点で共通していますが、変換の方向が異なります。以下にその違いをまとめます。

項目	TOCOL関数	TOROW関数
主な用途	2次元配列を1列に変換	2次元配列を1行に変換
変換方向	縦方向(列)	横方向(行)
引数「空を挿入」	TRUEで空の行を挿入	TRUEで空の列を挿入
Excelバージョン	Microsoft 365	Microsoft 365

どちらの関数を使用するかは、最終的にデータをどのような形式(1列または1行)にしたいかによって決定します。データ分析の前処理で、リスト形式のデータが必要な場合はTOCOL関数が、レコード形式のデータが必要な場合はTOROW関数が適しています。

まとめ

この記事では、ExcelのTOCOL関数とTOROW関数を使用して、2次元配列を1列または1行に変換するテクニックを解説しました。これらの関数を使えば、複数のセル範囲や配列データを簡単にフラット化でき、データ整形作業を劇的に効率化できます。UNIQUE関数やFILTER関数、SORT関数などと組み合わせることで、さらに高度なデータ処理も可能になります。これらの動く配列関数を習得し、Excelでのデータ分析やレポート作成の効率を高めましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】