【Excel】TOCOL・TOROW関数で2次元配列を1列に変換!Excelのデータ整形と集計の前処理術

Excelで大量のデータを扱う際、2次元配列のデータを1列にまとめる必要が生じることがあります。集計や分析の前処理として、このデータ整形は非常に重要です。しかし、手作業でコピー&ペーストを繰り返すのは非効率的です。Microsoft 365で利用できるTOCOL関数とTOROW関数を使えば、この作業を劇的に効率化できます。この記事では、これらの関数を使った2次元配列の1列変換方法を解説します。データ整形と集計の前処理がスムーズに行えるようになります。

TOCOL関数は、指定した範囲の配列を縦方向(1列)に展開します。TOROW関数は、指定した範囲の配列を横方向(1行)に展開します。これらの関数を理解することで、Excelでのデータ整形作業が格段に楽になります。特に、複数の表を一つのリストにまとめたい場合などに威力を発揮します。

TOCOL・TOROW関数の概要と基本機能

TOCOL関数とTOROW関数は、Microsoft 365の最新バージョンで利用できる動的配列関数です。これらの関数は、指定した2次元配列(表形式のデータ)を、1次元配列(1列または1行のデータ)に変換する機能を持っています。これにより、データの集計や分析、グラフ作成などの前処理が容易になります。

TOCOL関数は、配列を縦方向に展開するため、複数の行にわたるデータを一つの列にまとめたい場合に適しています。一方、TOROW関数は配列を横方向に展開するため、複数の列にわたるデータを一つの行にまとめたい場合に利用できます。これらの関数は、データ整形作業の効率を大幅に向上させます。

TOCOL関数による2次元配列の1列変換

TOCOL関数は、指定した配列を縦方向に展開し、1列の配列として返します。基本的な構文は以下の通りです。

TOCOL(array, [ignore], [pad_with])

ここで、`array`は変換したい2次元配列を指定します。`[ignore]`は省略可能な引数で、空白セルをどのように扱うかを指定します。0(デフォルト)は空白をそのまま表示、1は空白を無視します。`[pad_with]`も省略可能で、空白を無視した場合に、その部分を何で埋めるかを指定できます。

TOCOL関数の基本的な使い方

例えば、A1からB3までの範囲に以下のようなデータがあるとします。

A1: りんご, 100

A2: みかん, 150

A3: ぶどう, 200

このデータを1列に変換するには、任意のセルに以下の数式を入力します。

=TOCOL(A1:B3)

この数式により、以下のような結果が返されます。

りんご

100

みかん

150

ぶどう

200

空白セルを無視する

もし、A1:B3の範囲に空白セルが含まれている場合、`[ignore]`引数で空白を無視できます。例えば、C1:D3の範囲に以下のデータがあり、C2とD3が空白だとします。

C1: 商品A, 1000

C2: , 2000

C3: 商品B,

この場合、空のセルを無視して1列にしたいときは、以下の数式を使用します。

=TOCOL(C1:D3, 1)

これにより、以下のような結果が得られます。

商品A

1000

2000

商品B

(空白セルは表示されない)

空白セルを特定の値で埋める

空白セルを無視するのではなく、特定の値で埋めたい場合は、`[pad_with]`引数を使用します。例えば、上記のC1:D3の空白を「N/A」で埋めたい場合は、以下の数式を入力します。

=TOCOL(C1:D3, 1, “N/A”)

結果は以下のようになります。

商品A

1000

N/A

2000

商品B

N/A

TOROW関数による2次元配列の1行変換

TOROW関数は、TOCOL関数と対になる関数で、指定した配列を横方向に展開し、1行の配列として返します。構文は以下の通りです。

TOROW(array, [ignore], [pad_with])

`array`は変換したい2次元配列、`[ignore]`と`[pad_with]`はTOCOL関数と同様に、空白セルの扱いを指定します。

TOROW関数の基本的な使い方

例えば、A1からC2までの範囲に以下のようなデータがあるとします。

A1: 部署A, 担当者1, 役職1

A2: 部署B, 担当者2, 役職2

このデータを1行に変換するには、任意のセルに以下の数式を入力します。

=TOROW(A1:C2)

この数式により、以下のような結果が返されます。

部署A, 担当者1, 役職1, 部署B, 担当者2, 役職2

空白セルを無視して1行に変換

TOCOL関数と同様に、空白セルを無視することも可能です。例えば、A1:C3の範囲にデータがあり、A2とC3が空白だとします。

A1: 地域X, 売上1, 利益1

A2: , 売上2,

A3: 地域Y, 売上3, 利益3

空白を無視して1行に変換するには、以下の数式を使用します。

=TOROW(A1:C3, 1)

結果は以下のようになります。

地域X, 売上1, 利益1, 売上2, 地域Y, 売上3, 利益3

空白セルを特定の値で埋めて1行に変換

空白セルを特定の値で埋める場合も、`[pad_with]`引数を使用します。上記の例で、空白を「-」で埋めるには、以下の数式を入力します。

=TOROW(A1:C3, 1, “-“)

結果は以下のようになります。

地域X, 売上1, 利益1, -, 売上2, 地域Y, 売上3, 利益3

TOCOL・TOROW関数と他の配列関数との組み合わせ

TOCOL関数やTOROW関数は、他の動的配列関数と組み合わせることで、さらに強力なデータ整形ツールとなります。例えば、FILTER関数やSORT関数と組み合わせることで、条件に合うデータだけを抽出・整形したり、並べ替えたりすることが可能です。

FILTER関数との組み合わせ

FILTER関数を使って特定の条件に合致するデータのみを抽出し、それをTOCOL関数で1列にまとめることができます。例えば、A1:B5の範囲に商品名と価格があり、価格が150円以上の商品のみを1列に表示したい場合を考えます。

A1: 商品A, 100

A2: 商品B, 200

A3: 商品C, 120

A4: 商品D, 300

A5: 商品E, 180

以下の数式で実現できます。

=TOCOL(FILTER(A1:B5, B1:B5>=150))

結果は以下のようになります。

商品B

200

商品D

300

商品E

180

SORT関数との組み合わせ

SORT関数でデータを並べ替えてから、TOCOL関数で1列にまとめることも可能です。上記の例で、価格の高い順に並べ替えて1列に表示するには、以下の数式を使用します。

=TOCOL(SORT(A1:B5, 2, -1))

ここで、`2`は2列目(価格)を基準に、`-1`は降順(高い順)を指定しています。結果は以下のようになります。

商品D

300

商品E

180

商品B

200

商品A

100

商品C

120

LET関数との組み合わせ

LET関数を使うと、数式内の配列や条件を名前付きで定義できるため、複雑な数式が読みやすくなります。例えば、複数の範囲を結合して1列にしたい場合などに有効です。

=LET(data1, A1:B3, data2, D1:E3, TOCOL(VSTACK(data1, data2)))

この例では、A1:B3とD1:E3の2つの範囲をVSTACK関数で縦に結合し、それをTOCOL関数で1列に展開しています。LET関数を使うことで、`data1`や`data2`といった名前で範囲を参照できるため、数式が長くなっても管理しやすくなります。

TOCOL・TOROW関数利用時の注意点と制限事項

TOCOL関数とTOROW関数は非常に便利ですが、利用にあたってはいくつかの注意点と制限事項があります。これらの点を理解しておくことで、予期せぬエラーや意図しない結果を防ぐことができます。

Microsoft 365限定の関数であること

TOCOL関数とTOROW関数は、Excel for Microsoft 365の最新バージョンでのみ利用可能です。Excel 2019、Excel 2021、またはそれ以前のバージョンではこれらの関数はサポートされていません。これらのバージョンでファイルを開いた場合、数式はエラー値(例: #NAME?)として表示されるか、正しく計算されません。

動的配列の挙動

これらの関数は動的配列関数であるため、数式を入力したセルから結果が自動的にスピル(展開)します。スピル範囲に他のデータが存在する場合、#SPILL!エラーが発生します。数式を入力する前に、結果が表示される範囲に十分な空きがあることを確認してください。

エラー値の扱い

変換元の配列にエラー値(例: #DIV/0!, #N/A)が含まれている場合、TOCOL関数やTOROW関数はそれらのエラー値もそのまま展開します。これらのエラー値を処理したい場合は、IFERROR関数などを組み合わせて事前にエラー処理を行う必要があります。

例えば、A1:B3の範囲にエラー値が含まれている場合、エラーを0で置き換えてから1列に展開するには、以下の数式を使用します。

=TOCOL(IFERROR(A1:B3, 0))

パフォーマンスへの影響

非常に大きな配列(数万行・数千列など)に対してこれらの関数を使用すると、計算に時間がかかったり、Excelの動作が重くなったりする可能性があります。大量のデータを処理する場合は、PCのスペックやExcelのバージョン、可能であればPower Queryなどの別ツールとの併用も検討すると良いでしょう。

TOCOL・TOROW関数を使ったデータ整形・集計の前処理例

これらの関数は、実際の業務でどのように役立つのでしょうか。具体的な前処理の例を見てみましょう。

例1: 複数シートのリストを1つにまとめる

部署ごとに分かれた売上リストが別々のシートにある場合、それらを一つのシートに集約して全体の集計を行うことがあります。各シートのリスト範囲をTOCOL関数で1列に変換し、それをさらにVSTACK関数などで結合することで、容易に集約できます。

例えば、「営業部」「開発部」「総務部」という3つのシートに、それぞれA1セルから始まる商品リストがあるとします。

=TOCOL(VSTACK(‘営業部’!A1:A100, ‘開発部’!A1:A100, ‘総務部’!A1:A100))

この数式は、各シートの商品リストを縦に結合し、TOCOL関数で1列に展開します。

例2: データベース形式のデータを集計しやすい形式に変換

あるデータが、例えば「商品」「地域」「販売数」のような列を持つテーブル形式になっているとします。このテーブルを、特定の集計(例: 商品ごとの合計販売数)のために、各要素を1列に展開したい場合があります。TOCOL関数は、このようなテーブルの全要素を1列に展開し、その後の集計処理(SUMIF関数など)で利用しやすくします。

例えば、A1:C10の範囲に商品、地域、販売数のデータがある場合、全データを1列に展開するには以下のようにします。

=TOCOL(A1:C10)

展開されたデータに対して、後続の関数で集計処理を行うことができます。

例3: アンケート結果の集計

アンケートで、自由記述欄や複数選択式の質問項目が複数ある場合、それらの回答を一覧化して集計したいことがあります。TOCOL関数とTOROW関数を組み合わせることで、これらの複雑なデータを整形し、集計しやすい形式に変換できます。

例えば、回答者ごとに複数の項目(A列〜D列)に回答が入力されている場合、各回答者の回答を1列にまとめるにはTOCOL関数を、全回答者の回答を横一列にまとめるにはTOROW関数が利用できます。

まとめ

TOCOL関数とTOROW関数は、Excelでのデータ整形作業を劇的に効率化する強力なツールです。これらの関数を使うことで、2次元配列のデータを簡単に1列または1行に変換できます。これにより、データの集計、分析、可視化の前処理が格段にスムーズになります。Microsoft 365ユーザーであれば、ぜひこれらの関数を活用して、日々の業務効率を高めてください。今後は、これらの関数と他の動的配列関数(FILTER, SORT, LETなど)を組み合わせて、さらに高度なデータ処理に挑戦してみましょう。

📊

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

🏆

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