Excel関数で行列を入れ替える方法をお探しですね。

広告

Excelで表の縦横を入れ替える！TRANSPOSE関数の使い方

Excelで表の向きを変えたいとき、「コピーして形式を選択して貼り付け」で行と列を入れ替える方法をよく使いますよね。

でも、この方法だと元のデータを修正しても貼り付けた先には反映されないんです。

データを更新するたびに同じ作業を繰り返すのは、正直めんどうですよね。

そんなときに便利なのが、ExcelのTRANSPOSE関数です。

この記事では、コピペを使わずに関数で表の縦横を入れ替える方法を、初心者の方にも分かりやすく説明していきます。

TRANSPOSE関数って何？表の縦横を自動で入れ替えられる便利な機能

TRANSPOSE関数は、指定したセルの範囲の行と列を入れ替えて表示してくれるExcelの関数です。

例えば、A1からA3のセルに縦方向で「りんご」「みかん」「バナナ」と入力されているとします。

別のセルでTRANSPOSE関数を使うと、横方向に「りんご、みかん、バナナ」と並べて表示できるんです。

ただ見た目を変えるだけじゃなくて、元のデータと転置した後の表が連動するのが大きなポイント。

元のセルを変更すれば、TRANSPOSE関数で表示している側も自動で更新されます。

だから、定期的に更新する売上表やアンケートの集計表、報告資料を作るときにとても便利です。

TRANSPOSE関数の基本的な書き方は「=TRANSPOSE(配列)」です。

ここでいう配列というのは、行列を入れ替えたいセルの範囲のことです。

例えば「=TRANSPOSE(A1:C4)」と入力すると、A1からC4の3列×4行の範囲が、4列×3行の形で表示されます。

コピー貼り付けで転置すると一度きりの固定されたデータになってしまいますが、TRANSPOSE関数は元の範囲を参照し続けるので、元データを変更すれば自動的に反映されます。

この「動的に変わる」という性質を理解しておくと、コピペ作業を減らせるだけじゃなくて、更新し忘れや転記ミスも防ぎやすくなりますよ。

TRANSPOSE関数の使い方と入力の手順

まず、行と列を入れ替えたい元のデータの範囲を確認しましょう。

例えば、A1からD3に3行×4列の表がある場合、TRANSPOSE関数で入れ替えた後は4行×3列の表示になります。

つまり、出力先には元のデータと逆の形になるだけの空きスペースが必要なんですね。

Microsoft 365やExcel 2021以降のバージョンでは、左上のセルに数式を1つ入力するだけで、結果が必要な範囲に自動で広がります。

これを「スピル」といいます。

例えばF1のセルに「=TRANSPOSE(A1:D3)」と入力してEnterキーを押すと、F1からH4の範囲に転置した結果が表示されます。

一方、Excel 2019以前のバージョンでスピルに対応していない場合は、少し操作が違います。

あらかじめ転置後の表示範囲を選択してから数式を入力して、Ctrl＋Shift＋Enterで配列数式として確定する必要があるんです。

元のデータが3行×4列なら、転置後の4行×3列の範囲を先に選択します。

その状態で「=TRANSPOSE(A1:D3)」と入力して、普通のEnterじゃなくてCtrl＋Shift＋Enterで確定すると、選択した範囲全体に結果が表示されます。

古いExcelでこの操作を忘れると、先頭の値しか表示されなかったり、思った通りに展開されなかったりするので、使っているExcelのバージョンを確認しておくことが大切です。

縦1列のリストを横1行に変換する使い方も、実際の仕事でよく使います。

例えば、A2からA6に担当者の名前が縦に並んでいる場合、C2のセルに「=TRANSPOSE(A2:A6)」と入力すれば、C2からG2に横並びで表示できます。

逆に、横方向に並んだ月別のデータを縦方向の一覧にしたい場合も同じ考え方です。

「=TRANSPOSE(B1:M1)」のように横の範囲を指定すれば、縦方向に展開されます。

会議の資料やグラフ用のデータの向きを整えるとき、手入力で並べ替えるよりも正確で、後からデータが変わっても修正の手間が少なくて済みます。

コピペの転置との違いと、TRANSPOSE関数を使うメリット

Excelには「形式を選択して貼り付け」から「行/列の入れ替え」を選ぶ方法もあります。

この方法は操作が分かりやすくて、完成した表をそのまま別の場所に固定したいときには便利です。

ただし、貼り付けた後のデータは元のデータと連動しません。

元の表を更新した場合、貼り付け先の表も手作業で更新し直す必要があります。

だから、毎月更新する売上表とか、元データが頻繁に変わる管理表では、更新し忘れたり古い数値のまま提出してしまったりするリスクがあるんです。

TRANSPOSE関数のメリットは、元データを参照し続けることで、転置後の表を自動更新できる点です。

例えば、部署別の売上表を入力用のシートに作成して、別シートの報告用レイアウトではTRANSPOSE関数で見やすい向きに表示する、といった使い方ができます。

入力用のデータと提出用のデータを分けられるので、元の表を管理しながら、見せ方だけを柔軟に変えられるんです。

また、数値だけじゃなくて文字や数式の結果も転置できるので、集計表、名簿、スケジュール表など色々な表で活用できます。

ただし、TRANSPOSE関数は書式をそのまま引き継ぐ関数ではありません。

文字の色、罫線、列の幅、セルの背景色などの見た目は、基本的に出力先で別に整える必要があります。

なので、元データと完全に同じデザインで固定したい場合は貼り付けによる転置、データの更新を優先したい場合はTRANSPOSE関数、と使い分けるのが実用的です。

判断に迷ったときは、「今後も元データが変わるかどうか」を基準にすると選びやすくなります。

更新される表なら関数、完成済みの資料として固定したい表ならコピー貼り付けが向いています。

TRANSPOSE関数でよくあるエラーと実際に使うときの注意点

TRANSPOSE関数を使うときによくあるトラブルは、出力先に十分な空きセルがないケースです。

Microsoft 365やExcel 2021以降では、結果が複数のセルに自動で広がる「スピル」という仕組みが使われます。

出力先の範囲に文字や数式が入っていると、結果を展開できなくて「#SPILL!」というエラーが表示されます。

この場合は、転置結果が広がる予定の範囲を空にするか、数式を入力する位置を変更しましょう。

また、スピルの範囲内の一部のセルだけを直接編集することはできません。

修正したい場合は、元データを直すか、数式を入力している左上のセルを編集します。

別のシートのデータを参照して行列を入れ替えることもできます。

例えば、Sheet1のA1からD3をSheet2に転置したい場合は、Sheet2の好きなセルに「=TRANSPOSE(Sheet1!A1:D3)」と入力します。

シート名にスペースが含まれる場合は「=TRANSPOSE(‘売上 データ’!A1:D3)」のように、シート名をシングルクォーテーションで囲みます。

この方法を使えば、入力用のシートと閲覧用のシートを分けられるので、実際の仕事ではかなり便利です。

ただし、元データの行数や列数が後から増える場合は、参照する範囲も広めに取っておくか、Excelテーブルや動的配列関数との組み合わせを検討すると管理しやすくなります。

空白のセルが0として表示される場合もあります。

これは、参照先の空白が数式の結果として0扱いになることがあるためです。

空白を空白のまま見せたい場合は、「=TRANSPOSE(IF(A1:D3=””,””,A1:D3))」のようにIF関数を組み合わせる方法があります。

また、結合したセルを含む表は、転置後のレイアウトが崩れたり、意図通りに処理できなかったりする原因になります。

TRANSPOSE関数を安定して使うには、元データ側をできるだけシンプルな表にしておくことが重要です。

見出し行、データの範囲、集計行を分けて、結合セルを避けるだけでも、エラーや修正作業を大きく減らせます。

まとめ

TRANSPOSE関数は、行と列を入れ替えるだけのシンプルな関数ですが、コピペによる転置とは違って、元データと連動できるのが大きな強みです。

スピルに対応しているExcelなら、左上のセルに数式を入力するだけで複数のセルに結果が展開されるので、以前よりもずっと使いやすくなっています。

一方で、出力先の空き範囲、書式の扱い、空白セルの表示、古いExcelでの配列数式の確定方法など、実際に使うときにつまずきやすいポイントもあります。

まずは小さな表で「=TRANSPOSE(範囲)」を試してみて、元データを変更したときに転置先も更新される動きを確認してみると理解しやすいですよ。

行列の入れ替えを関数化できれば、表の作り直しや転記ミスを減らせて、Excel作業がもっと効率的に進められます。

ぜひ試してみてくださいね。

広告