【Excel】TRANSPOSE関数で行列を入れ替える!Excelの縦横変換を数式で動的に行う方法

Excelで表の縦横を入れ替えたい場面は多いです。例えば、部署ごとの月別売上データを、月ごとの部署別売上データに変換したい場合などです。手作業でコピー&ペーストを繰り返すと、ミスが発生しやすくなります。また、元データの更新に合わせて手動で再作業が必要になり、非効率です。この記事では、ExcelのTRANSPOSE関数を使って、表の縦横を数式で動的に入れ替える方法を解説します。これにより、データ変換作業の効率が大幅に向上します。

TRANSPOSE関数は、配列の行と列を入れ替えるためのExcel関数です。この関数を使うことで、元のデータを参照しながら、縦横が変換された新しい表を動的に作成できます。これにより、元のデータが更新された場合でも、変換後の表にその変更が自動的に反映されるため、手作業での再更新が不要になります。Excel 2021以降では、動的配列に対応したことで、TRANSPOSE関数の使い方がさらに簡単になりました。それ以前のバージョンでは、配列数式として入力する必要がありましたが、基本的な機能は同じです。

TRANSPOSE関数とは何か

TRANSPOSE関数は、指定した配列(範囲)の行と列を入れ替えて、新しい配列を返す関数です。例えば、3行4列の範囲を指定した場合、TRANSPOSE関数は4行3列の配列を返します。この関数は、データ分析やレポート作成において、データの表示形式を柔軟に変更したい場合に非常に役立ちます。元のデータ構造を維持したまま、分析しやすい形にデータを整形することが可能です。

この関数は、結果を配列として返すため、Excelのバージョンによって入力方法が異なります。Excel 2021以降のMicrosoft 365では、動的配列機能により、1つのセルに関数を入力するだけで、結果が自動的に必要な範囲に展開されます。一方、Excel 2019以前のバージョンでは、結果が表示される範囲を事前に選択し、配列数式として確定させる必要があります。この違いを理解しておくことが、TRANSPOSE関数を正しく使う上で重要です。

TRANSPOSE関数で行列を入れ替える手順

TRANSPOSE関数を使って行列を入れ替える手順は、Excelのバージョンによって異なります。ここでは、Excel 2021以降の動的配列に対応したバージョンと、それ以前のバージョンでの手順をそれぞれ解説します。

Excel 2021以降 (動的配列)

Excel 2021以降では、動的配列機能によりTRANSPOSE関数の入力が非常に簡単になりました。関数を入力するだけで、結果が自動的に必要なセル範囲に展開されます。

1. 変換したい範囲を選択する
   まず、縦横を入れ替えたい元のデータ範囲を選択します。例えば、A1からC4までの範囲にデータがある場合、この範囲を選択します。
2. TRANSPOSE関数を入力するセルを選択する
   結果を表示させたい新しい表の左上のセルを選択します。このセルにTRANSPOSE関数を入力します。
3. TRANSPOSE関数を入力する
   選択したセルに、以下の構文でTRANSPOSE関数を入力します。
   =TRANSPOSE(配列)
   ここで、「配列」には、縦横を入れ替えたい元のデータ範囲を指定します。例えば、元のデータがA1:C4にある場合は、「=TRANSPOSE(A1:C4)」と入力します。
4. Enterキーで確定する
   関数を入力したら、Enterキーを押します。すると、元のデータの行と列が入れ替わった結果が、自動的に必要なセル範囲に展開されます。

Excel 2019以前 (配列数式)

Excel 2019以前のバージョンでは、TRANSPOSE関数は配列数式として入力する必要があります。結果が表示される範囲を事前に選択してから、数式を確定させる手順が必要です。

1. 結果を表示する範囲を選択する
   まず、変換後の表が表示されるセル範囲を事前に選択します。この範囲の行数と列数は、元のデータの列数と行数に対応している必要があります。例えば、元のデータが3行4列の場合、結果を表示する範囲は4行3列になるように選択します。
2. 選択した範囲の左上のセルにTRANSPOSE関数を入力する
   選択した範囲の左上のセルがアクティブな状態で、数式バーに以下の構文でTRANSPOSE関数を入力します。
   =TRANSPOSE(配列)
   「配列」には、縦横を入れ替えたい元のデータ範囲を指定します。例えば、元のデータがA1:C4にある場合は、「=TRANSPOSE(A1:C4)」と入力します。
3. Ctrl + Shift + Enterキーで確定する
   関数を入力したら、Enterキーではなく、Ctrl + Shift + Enterキーを同時に押して配列数式として確定します。数式バーに数式が波括弧 {} で囲まれて表示されれば、正しく入力されています。
4. 結果を確認する
   正しく入力されていれば、選択した範囲全体に、行と列が入れ替わったデータが表示されます。

TRANSPOSE関数でよくある失敗例と対処法

TRANSPOSE関数を使用する際に、意図した結果が得られない場合があります。ここでは、よくある失敗例とその対処法を解説します。

#SPILL! エラーが表示される場合

Excel 2021以降でTRANSPOSE関数を入力した際に #SPILL! エラーが表示されるのは、結果を展開するための十分なスペースがないことが原因です。これは、動的配列関数が自動的に結果を展開しようとした際に、他のデータやオブジェクトがその展開先に存在する場合に発生します。

対処法:

1. 展開先のセルをクリアする
   TRANSPOSE関数を入力したセルから、結果が展開される可能性のある範囲に、他のデータや数式が入っていないか確認します。もしあれば、それらを削除または移動して、展開先のセルをクリアします。
2. 別の場所に数式を入力する
   展開先のセルがクリアできない場合は、別の空いている場所にTRANSPOSE関数を入力し直します。

一部のデータしか表示されない、またはエラーになる場合

Excel 2019以前で配列数式として確定しなかった場合や、結果を表示する範囲の選択を誤った場合に、この問題が発生することがあります。また、元のデータ範囲の指定が間違っている可能性もあります。

対処法:

1. 配列数式として正しく確定する
   Excel 2019以前の場合は、必ずCtrl + Shift + Enterキーで配列数式として確定しているか確認します。数式バーの数式が波括弧 {} で囲まれていればOKです。
2. 結果表示範囲を再確認する
   選択した結果表示範囲の行数と列数が、元のデータの列数と行数に正しく対応しているか確認します。
3. 元のデータ範囲を再確認する
   TRANSPOSE関数の引数で指定している元のデータ範囲が正しいか確認します。

元のデータが更新されても、変換後の表が更新されない場合

TRANSPOSE関数は、数式として入力されているため、元のデータが更新されれば自動的に結果も更新されます。もし更新されない場合は、数式が正しく入力されていない、または数式の計算が手動になっている可能性があります。

対処法:

1. 数式が有効か確認する
   変換後の表のセルを選択し、数式バーにTRANSPOSE関数が表示されているか確認します。もし、値だけが表示されている場合は、数式が失われているため、再度入力が必要です。
2. 計算方法を確認する
   Excelの「数式」タブにある「計算方法の設定」が「手動」になっていないか確認します。「自動」になっていることを確認してください。

TRANSPOSE関数と他の関数との組み合わせ

TRANSPOSE関数は、他の関数と組み合わせることで、さらに高度なデータ処理が可能になります。ここでは、代表的な組み合わせ例を紹介します。

SUM関数との組み合わせ

TRANSPOSE関数で縦横を入れ替えたデータに対し、SUM関数を使って合計値を計算したい場合があります。例えば、月ごとの部署別売上データを縦横変換した後、各月の合計売上を算出するケースです。

例: 元データがA1:C4にあり、D1セルに月ごとの合計を計算したい場合。

1. TRANSPOSE関数でデータを展開する
   例えば、E1セルに「=TRANSPOSE(A1:C4)」と入力し、データを縦横変換します。
2. SUM関数で合計を計算する
   D1セルに、展開されたデータ範囲(例えばE1:G3)の合計を計算するSUM関数を入力します。
   =SUM(E1:G3)

Excel 2021以降では、LET関数などを利用して、より簡潔に記述することも可能です。

INDEX関数やMATCH関数との組み合わせ

TRANSPOSE関数で縦横を入れ替えたデータから、特定の条件に合う値を取り出したい場合は、INDEX関数やMATCH関数と組み合わせます。例えば、縦横変換した表から、特定の月・特定の部署の売上を検索する場合などです。

例: 縦横変換したデータがE1:G3にあり、特定の月(例: 2月)と部署(例: B部署)の売上を検索したい場合。

1. MATCH関数で位置を特定する
   月(例: 2月)がE1:G1のどこにあるか、部署(例: B部署)がE1:E3のどこにあるかをMATCH関数で検索します。
2. INDEX関数で値を取得する
   MATCH関数で得られた行番号と列番号を使って、INDEX関数で目的の売上データを取得します。

これらの関数を組み合わせることで、複雑なデータ集計や検索も可能になります。

機能	TRANSPOSE関数	手作業でのコピー&ペースト
処理速度	高速	データ量に依存
動的更新	可能	不可
ミス発生率	低い	高い
学習コスト	低 (Excel 2021以降) / 中 (それ以前)	低

TRANSPOSE関数は、Excelでデータの縦横変換を効率的かつ動的に行うための強力なツールです。特にExcel 2021以降の動的配列機能により、その利用はさらに容易になりました。元のデータを参照しながら、分析しやすい形式にデータを整形できるため、レポート作成やデータ分析の作業効率を大幅に向上させることができます。手作業でのコピー&ペーストによるミスや、都度更新の手間を省きたい場合に、ぜひ活用してください。

今後、さらに複雑なデータ処理が必要になった場合は、TRANSPOSE関数をSUM関数やINDEX/MATCH関数などと組み合わせることを検討してみてください。これにより、Excelでのデータ分析能力をさらに高めることができるでしょう。TRANSPOSE関数をマスターし、Excel作業をより効率的に進めましょう。

📊

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

🏆

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