【Excel】行と列を入れ替えてデータを転置する方法

Excelで表を作成していると、行と列が逆になってしまったデータを扱いたい場面に遭遇します。

例えば、アンケート結果で回答者を行、質問を列にしたものを、質問を行、回答者を列にした形式に変換したい場合などです。

このような時、手作業で一つずつ入力し直すのは大変な手間がかかります。本記事では、Excelの「行列を入れ替える」機能を使って、この作業を効率的に行う方法を解説します。

コピー&形式を選択して貼り付け(行列を入れ替え)の概要

Excelで最も手軽に行と列を入れ替える方法は、「コピー&形式を選択して貼り付け」機能の「行列を入れ替え」オプションを使うことです。

この機能は、選択した範囲のデータをコピーし、貼り付け時に行と列を入れ替えて配置します。数式も値として貼り付けられるため、静的なデータ変換に適しています。

ただし、元データが更新されても、貼り付けたデータは自動的に更新されない点に注意が必要です。

コピー&形式を選択して貼り付け(行列を入れ替え)の手順

1. 転置したい範囲を選択する
   入れ替えたいデータが含まれるセル範囲をマウスでドラッグして選択します。
2. 選択範囲をコピーする
   選択した状態で右クリックし、「コピー」を選択するか、Ctrl+C(Windows)またはCommand+C(Mac)を押します。
3. 貼り付け先のセルを選択する
   転置したデータを貼り付けたい場所のセルを1つ選択します。元データとは異なるシートや、十分な空きスペースがある場所を選びましょう。
4. 形式を選択して貼り付けを実行する
   選択したセルを右クリックし、「形式を選択して貼り付け」を選択します。
5. 「行列を入れ替え」にチェックを入れる
   表示されたダイアログボックスの中から、「行列を入れ替え」というチェックボックスを探してチェックを入れます。
6. 貼り付けを実行する
   「OK」ボタンをクリックすると、選択範囲の行と列が入れ替わった状態でデータが貼り付けられます。

TRANSPOSE関数を使った転置

TRANSPOSE関数は、配列(範囲)の行と列を入れ替えることができる関数です。この関数を使うと、元データが変更されたときに、転置したデータも自動的に更新されるようになります。

ただし、TRANSPOSE関数は配列数式として入力する必要があり、Excelのバージョンによっては操作方法が異なります。Microsoft 365やExcel 2021以降では、動的配列に対応しているため、より簡単に利用できます。

Excel 2019以前のバージョンでは、配列数式として確定するために特別な操作(Ctrl+Shift+Enter)が必要になる場合があります。

TRANSPOSE関数(Microsoft 365 / Excel 2021以降)の手順

1. 転置したい範囲の行数と列数を確認する
   例えば、元データが3行5列の場合、転置後のデータは5行3列になります。
2. 結果を表示したい範囲を選択する
   転置後のデータが表示されるのに十分な大きさのセル範囲を選択します。上記の例では5行3列の範囲を選択します。
3. TRANSPOSE関数を入力する
   数式バーに「=TRANSPOSE(」と入力します。
4. 転置したい元データ範囲を指定する
   続けて、転置したい元のデータ範囲をマウスで選択するか、セル参照で指定します。例:=TRANSPOSE(A1:E3)
5. 数式を確定する
   Excel 2021以降では、Enterキーを押すだけで確定できます。結果が選択した範囲に自動的に展開されます。

TRANSPOSE関数(Excel 2019以前)の手順

1. 転置したい範囲の行数と列数を確認する
   元データが3行5列なら、転置後は5行3列です。
2. 結果を表示したい範囲を選択する
   転置後のデータが表示されるのに十分な大きさのセル範囲を選択します。例:5行3列の範囲。
3. TRANSPOSE関数を入力する
   選択した範囲の左上のセル(アクティブセル)が選択された状態で、数式バーに「=TRANSPOSE(」と入力します。
4. 転置したい元データ範囲を指定する
   続けて、転置したい元のデータ範囲をマウスで選択するか、セル参照で指定します。例:=TRANSPOSE(A1:E3)
5. 配列数式として確定する
   数式バーに数式が入力された状態で、Ctrl+Shift+Enter キーを同時に押して確定します。数式が{}で囲まれ、配列数式として認識されます。

この方法で入力すると、元データが変更された場合に、転置されたデータも自動的に更新されます。

Power Queryを使った転置

Power Queryは、Excel 2016以降やMicrosoft 365に標準搭載されているデータ加工ツールです。大量のデータや複雑なデータ変換、定期的なデータ更新が必要な場合に非常に強力な機能を発揮します。

Power Queryでは、「転置」という操作が用意されており、GUI操作で簡単に行うことができます。一度設定したクエリは、データの更新時に自動的に処理が実行されるため、手作業によるミスを防ぎ、作業効率を大幅に向上させます。

Power Queryでデータを転置する手順

1. データをテーブルに変換する
   転置したいデータ範囲を選択し、「挿入」タブの「テーブル」をクリックします。ダイアログボックスで範囲を確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」をクリックします。
2. Power Queryエディターを開く
   テーブルのいずれかのセルを選択した状態で、「データ」タブの「テーブルまたは範囲から」をクリックします。Power Queryエディターが起動します。
3. 「転置」操作を実行する
   Power Queryエディターのリボンメニューにある「変換」タブをクリックします。「テーブル」グループにある「転置」ボタンをクリックします。
4. 必要に応じて列名を変更する
   転置後、元の行ヘッダーが列名になります。必要であれば、「ホーム」タブの「列名の昇格」や、各列ヘッダーをダブルクリックして名前を変更します。
5. データをExcelに読み込む
   「ホーム」タブの「閉じて読み込む」ボタンをクリックします。
6. 読み込み先を選択する
   「データのインポート」ダイアログボックスが表示されるので、データの読み込み先(新規ワークシートまたは既存のワークシート)を選択し、「OK」をクリックします。

これで、Power Queryによって転置されたデータがExcelシートに読み込まれます。元のテーブルデータが更新された場合は、「データ」タブの「すべて更新」をクリックすれば、Power Queryでの処理が再度実行され、最新の状態に更新されます。

よくある失敗パターンと対処法

「行列を入れ替え」でデータがずれる・一部しか貼り付けられない

原因

貼り付け先のセル範囲が、転置後のデータサイズに対して小さすぎる場合や、貼り付けたい場所に既にデータが存在し、上書きできない場合に発生します。

対処法

1. 十分な空きスペースを確保する
   貼り付けたい場所の行数と列数が、転置後のデータサイズ(元の列数×元の行数)に合っているか確認します。元データが3行5列なら、転置後は5行3列になるため、貼り付け先は最低でも5行3列の範囲が必要です。
2. 貼り付け先のセルをクリアする
   既にデータがある場合は、貼り付けたい範囲の既存データを削除してから再度貼り付けを実行します。
3. 別の場所に貼り付ける
   一時的に別のシートや、十分な空きスペースがある場所に貼り付けてから、必要に応じて移動させることも有効です。

TRANSPOSE関数で#VALUE!エラーが表示される

原因

Excel 2019以前のバージョンで、TRANSPOSE関数を配列数式として確定せずに、通常のEnterキーで確定してしまった場合に発生します。

対処法

1. 数式を編集モードにする
   エラーが表示されているセルを選択し、数式バーをクリックして数式を編集できる状態にします。
2. Ctrl+Shift+Enterで再確定する
   数式バーに数式が表示されている状態で、Ctrl+Shift+Enter キーを同時に押して、配列数式として再確定します。数式が{}で囲まれれば成功です。
3. Excel 2021以降を使用する
   もし可能であれば、Microsoft 365やExcel 2021以降のバージョンを使用すると、配列数式の入力がEnterキーだけで済むため、このエラーは発生しにくくなります。

Power Queryで「転置」ボタンが押せない

原因

Power Queryエディターで、転置操作を実行する前に、対象のデータが正しく選択されていない、またはデータに予期しない形式(例:結合されたセル)が含まれている場合に発生します。

対処法

1. データ全体を選択しているか確認する
   Power Queryエディターで、転置したいテーブル全体が選択されていることを確認します。通常は、リボンの「ホーム」タブにある「すべて選択」ボタンをクリックするか、データ範囲をドラッグして選択します。
2. 結合されたセルを解除する
   もしデータ内に結合されたセルがある場合は、Excelシートに戻って結合を解除してから、再度Power Queryで読み込み直してください。
3. 列ヘッダーを昇格させる
   Power Queryで最初にデータを読み込んだ際に、Excelの1行目が列ヘッダーとして自動的に認識されていない場合があります。この場合、「ホーム」タブの「最初の行をヘッダーとして使用」ボタンをクリックしてヘッダーを昇格させてから、「転置」操作を試みてください。

Excelでの行と列の転置方法の比較

項目	コピー&形式を選択して貼り付け	TRANSPOSE関数	Power Query
機能	選択範囲の行と列を入れ替えて貼り付ける	配列の行と列を入れ替える	データ変換プロセスの一部として行と列を入れ替える
動的更新	なし(静的)	あり(元データ変更時に自動更新)	あり(クエリ更新時に自動更新)
操作の容易さ	非常に容易	やや複雑(特に旧バージョン)	やや複雑(初回設定)
大量データへの対応	限定的	限定的(パフォーマンスに影響する可能性)	得意
数式・値の貼り付け	値として貼り付け(数式は展開されない)	数式として展開される	結果として値が貼り付けられる
Excelバージョン	全バージョン	全バージョン(配列数式確定方法が異なる)	Excel 2016以降、Microsoft 365

どの方法を選択するかは、データの量、元データの更新頻度、Excelのバージョン、そして求める結果(動的か静的か)によって異なります。

まとめ

Excelで行と列を入れ替えるには、「コピー&形式を選択して貼り付け(行列を入れ替え)」、「TRANSPOSE関数」、「Power Query」の3つの主要な方法があります。

手軽に一度だけ変換したい場合は「コピー&形式を選択して貼り付け」、元データとの連動が必要な場合は「TRANSPOSE関数」、大量データや定期的な更新が必要な場合は「Power Query」が適しています。

これらの方法を使い分けることで、データの整形作業を大幅に効率化できます。

📊

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

🏆

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