Excelの表の行と列を入れ替える方法3選（転置）

Excelにおいて、ある表の行と列を入れ替えて（転置して）、別の表を作る方法をまとめてみます。実現する方法は複数ありますが、場合に応じて以下の①～③の3つから選択するのがベストかと思います。

※本記事の動作確認環境は脚注を参照¹。
※本記事の例で用いているデータは、気象庁の「過去の気象データ検索」より抜粋²。

① 「形式を選択して貼り付け」

元の表と連動^※させる必要がない場合は、「形式を選択して貼り付け」が最もシンプルです。

※本記事で「連動」とは、「元の表の値を変更したときに、転置させた表の値も自動的に更新される」ことを指しています。

1. Excelシート上で、元の表の転置させたい範囲を選択し、コピーする。
2. 転置後の表を配置したい範囲の一番左上のセルを選択する。
3. メニューの「ホーム」タブの「クリップボード」ブロックにおいて、「貼り付け」の「 」ボタンをクリックする。
4. プルダウンメニューの一番下の「形式を選択して貼り付け(S)…」をクリックする。
   
5. ポップアップしたメニューで、「行/列の入れ替え(E)」を選択する。（必要に応じて「貼り付け」メニューから形式を選択する。）
   
6. 「OK」をクリックすると、転置させた表が貼り付けられる。

② TRANSPOSE関数

元の表と連動する転置の表を作成したい場合は、TRANSPOSE関数を使うことで、最も簡単に実現できます。

「transpose」とは、数学用語で「転(trans-)置(-pose)」を意味します。

1. Excelシート上で、転置後の表を配置したい範囲の一番左上のセルを選択する。
2. 選択したセルに、以下の数式（途中まで）を手入力する。
   =TRANSPOSE(
3. 入力状態のまま、元の表の転置させたい範囲をシート上で選択することで、TRANSPOSE関数の引数に以下のようにセル範囲を指定する。
   =TRANSPOSE(C5:D16
   
4. Enterキーを押す（Excelの補完機能により、TRANSPOSE関数の閉じ括弧が自動入力される）ことで、転置させた表全体が「スピル」して表示される。

「スピル」とは、セル内の数式の計算結果が「配列」になる場合に、そのセルからあふれ出して（＝spillして）、周辺のセルも使って結果を表示する機能です。たとえば、「MUNIT」という関数は、引数に指定した次元の単位行列を結果として返すので、セルA1に=MUNIT(3)と入力すると、セル範囲A1:C3を使って3×3の単位行列がスピルして表示されます。（スピルはExcel2019以降に実装された機能で、それより前のバージョンではCSE配列という同様の機能があります。）

スピルは便利ですが、利用シーンによっては使いたくない場合があるかもしれません。

たとえば下図のように、元の表を修正して、「平均気温 ℃」「平均湿度 %」の間に「平均気温 ℉」の列を追加したとします。すると、転置した表は元々想定していた範囲から図のようにあふれてしまいます。列の追加まで動的に反映してくれるので便利ですが、転置後の表の範囲が勝手に変わってしまうと都合が悪いという場合もあり得ます。

こういった挙動などを避けたい場合は、スピルを活用するのではなく、転置後の表のセル1つ1つに数式を入れる必要があります。とはいえ、手作業で1つ1つのセルを入力するのは時間の無駄ですし、参照のミスが発生する可能性があります。

Excelの強みである「オートフィル機能」を上手く活用したいですが、セル参照のオートフィルは、行方向には行、列方向には列がインクリメントして（1ずつ増えて）いくので、単純には使えません。

③ 数式文字列を作成して「数式を表示」に貼り付け

そこで、以下の手順を踏むことで、オートフィル機能を上手く利用し、転置した表を作成することができます。

③-1. 数式文字列の表を作成

まず、転置した表に適用する数式を、文字列として別の表に作成します。

1. Excelシート上で、元の表の転置させたい範囲を選択する。
2. 選択したセル範囲の、行の範囲と列の範囲を確認する。（たとえば下図の場合は、セル範囲がC5:D16なので、行の範囲は5～16、列の範囲はC～D。）
   
3. Excelシート上の、右側や下側の使用していない領域に、一時的な表を作成していく。まず下図のように、行のインデックスとして「列の範囲」を、列のインデックスとして「行の範囲」を1セルずつ入力する。
   
   • このとき、「行の範囲」は数値のためオートフィルが使えるが、「列の範囲」はアルファベットのためオートフィルが使えないので、ミスがないように気をつけて入力する。あまりに範囲が多い場合は、以下の数式によりオートフィルで入力できる。【X】は「行のインデックスの一番上のセルの行ID（図の例: セルT5→5）」、【Y】は「列の範囲の一番若い列ID（図の例: 列C→3）」。
     =SUBSTITUTE(CHAR(QUOTIENT((ROW()-1)-【X】+【Y】,26)+64),"@","")&CHAR(MOD((ROW()-1)-【X】+【Y】,26)+65)
     （この数式はA列～ZZ列まで対応しているが、AAA列以降は非対応。）
4. 次に、表の中身を埋めていく。一番左上のセルに以下の数式を入力して、3つの文字列を結合する。なお、列固定は列IDの文字列の直前に「$」をつけ、行固定は行IDの数字の直前に「$」をつける。
   ="="&【左隣のセル（列固定）】&【上隣のセル（行固定）】
   
   
5. 一番左上のセルから、表全体にオートフィルすることで、数式文字列の表が完成する。
   

③-2. 「数式を表示」に貼り付け

①で作成した数式文字列の表を、転置した表の数式として適用します。

1. ③-1で作成した数式文字列の表の中身を範囲選択し、コピーする。
2. PC上で「メモ帳」などのテキストエディタを開き、貼り付ける。
   
3. 貼り付けた内容全体を選択し、コピーし直す。（テキストエディタはもう使わないので閉じてよい。）
4. Excelシートに戻り、メニューの「数式」タブの「ワークシート分析」ブロックにおいて、「数式の表示」ボタンをクリックする。
   
5. 転置後の表を配置したい範囲の一番左上のセルを選択し、コピーした内容を貼り付ける。
   
6. 再度「数式の表示」ボタンをクリックすることで、表示が元に戻り、転置後の表が表示される。
7. ③-1で作成した数式文字列の表は不要なので削除する。

テキストエディタを介することで、コピー内容から数式の情報を削除して、表示されている文字列情報のみをコピーできます。この手順を踏まないと、表示文字列ではなく数式をペーストすることになり上手くいきません。

まとめ

本記事では、Excelの表の行と列を入れ替える「転置」の方法を3つ紹介しました。

基本的には、①か②の方法を使うとよいかと思います。サクッと転置した表が欲しい場合は①、今後も元の表とともにメンテナンスしていく、しっかりとした表が欲しい場合は②、という使い分けです。そして、何らかの理由でどうしてもスピルを使いたくないという場合は、②の代わりに③を使ってください。

筆者

ここまで読んでいただき、ありがとうございました！

脚注

1. 本記事の動作確認環境
   ・Windows 11 Home 23H2
   ・Excel 2021 バージョン 2406 ↩︎
2. “気象庁｜過去の気象データ検索”,https://www.data.jma.go.jp/obd/stats/etrn/view/monthly_s3.php?prec_no=44&block_no=47662&year=&month=&day=&view=a1. ↩︎