Excel表で行と列を入れ替える方法！最終行取得も！

Excel関数/VBAで行と列を入れ替えたいときはないでしょうか。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

Excel関数/VBAで行と列を入れ替えるイメージ

Excel関数/VBAで行と列を入れ替えるイメージについて説明をします。

まず、以下のような表があり、隣に行と列を入れ替えた表に変換していきます。

ワークシート関数を一個のセルに入力すると、一瞬で行と列が入れ替わります。

最終行を自動取得するようにすれば、元表のデータの増減があったとしても漏れなく変換することが可能です。

続いてワークシート関数と同様の処理をVBAでも実現できるようにしていきます。

数式ではなく直接値を代入することができます。

また最終行を自動取得していますので、同様に漏れがなく変換ができます。

さらに別のシートに対しても行と列の変換処理が可能です。

早速実装して試してみましょう。

行と列を入れ替える表を用意する

Excel関数とVBAで行と列を入れ替える対象となる表を用意しましょう。

サンプルではA、B、C列にそれぞれ値を入力し、関数とVBAを用いて、
E1セルを基点に行と列を入れ替えた表を挿入するようにしていきます。

Excel関数で行と列を入れ替える

Excel関数で行と列を入れ替える方法について説明をします。
行と列を入れ替える処理を行う関数はTRANSPOSE関数となります。
書式は以下の通りです。

TRANSPOSE関数の書式/機能/引数

TRANSPOSE関数に行列を入れ替えたい範囲を引数に指定するだけで、その範囲の行と列を入れ替えることができます。

なお、数式は1行のみで個々に数式を入力する必要はありません。これをExcelではスピルと呼んでいます。
具体的には引数の配列に指定したセルの範囲を数式配列として取り扱い、関数側で自動展開される形となります。

タカヒロ

Excelのスピル機能はMicrosoft365からの対応となり、それ以前のバージョンでは使用できないことご了承ください。

早速TRANSPOSE関数を使って行と列を入れ替えてみましょう。

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

先ほど用意したサンプル表でTRANSPOSE関数を使い行と列を入れ替えてみましょう。

E1セルを基点に行と列を入れ替えた表を挿入するようにしていきますので、E1セルに以下の数式を入力します。

はい、行と列が入れ替えられましたね。

TRANSPOSE関数で最終行を取得し行と列を入れ替える

次にTRANSPOSE関数で最終行を自動的に取得し行と列を入れ替える方法について説明をします。

元の表の情報が増えた場合、現在範囲指定している値は固定値であるため、当然ながら行列入れ替えの対象から外れる形となります。

この場合は、再度指定範囲を手動で変更する必要がありますが、都度変更することは少々面倒ですよね。

そこで、元の表の最終行を自動取得するようにして、範囲を動的に変更させ、手入力する手間を省いていきたいと思います。

数式は以下の通りとなります。

数式をE1セルに入力してみましょう。

はい、最終行の自動取得がされ、漏れがなく変換されていますね。

まず、表のA列の値がある件数をカウントします。

次に”A1:C”と最終行となるカウント値を結合し、INDIRECT関数で文字列を数式として認識させます。

VBAで行と列を入れ替える

VBAで行と列を入れ替える方法について説明をします。

VBAコード

VBAは以下の通りです。

Sub 行と列を入れ替える()
    Dim strRefCol
    Dim strRange
    Dim strTargetCell
    
    '取得対象範囲を指定します。
    strRange = "A1:C6"
    
    '貼り付け先のセルを指定します。
    strTargetCell = "E1"
    
    '指定範囲のセルをコピーします。
    Range(strRange).Copy
    
    'コピーしたデータを行列入れ替えモードで指定セルへペーストします。
    Range(strTargetCell).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=True
    
    'カットコピーモードを解除します。
    Application.CutCopyMode = False
    
End Sub

VBAの設定

行と列を入れ替えるVBAの設定内容について説明をします。

取得対象範囲を指定します。

貼り付け先のセルを指定します。

VBAの実装

行と列を入れ替えるVBAの実装方法については
VBAの実装手順
をご参照ください。

VBAを実行

VBAを実行しましょう。

はい、行と列が入れ替わり、値として挿入されていますね。

VBAの説明

行と列を入れ替えるVBAについて説明をします。

指定範囲のセルをコピーします。

コピーしたデータを行列入れ替えモードで指定セルへペーストします。

カットコピーモードを解除します。

VBAで最終行を自動取得し行と列を入れ替える

続いてVBAで最終行を自動取得し行と列を入れ替えるよう変更を加えていきたいと思います。

VBAコード

VBAは以下の通りです。

Sub 行と列を入れ替える_最終行自動取得()
    Dim strRefCol
    Dim strRange
    Dim strTargetCell
    
    '最終行数取得の基準となる列を指定します。
    strRefCol = "A"
    
    '取得対象範囲を指定します。最終行は自動取得した値を入れますので除いてください。
    strRange = "A1:C"
    
    '貼り付け先のセルを指定します。
    strTargetCell = "E1"
    
    '指定範囲のセルをコピーします。
    Range(strRange & Range(strRefCol & Rows.Count).End(xlUp).Row).Copy
    
    'コピーしたデータを行列入れ替えモードで指定セルへペーストします。
    Range(strTargetCell).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=True
    
    'カットコピーモードを解除します。
    Application.CutCopyMode = False
    
End Sub

VBAの設定

最終行を自動取得し行と列を入れ替えるVBAの設定内容について説明をします。

最終行数取得の基準となる列を指定します。

タカヒロ

最終行判定はキーとなっている項目が望ましいでしょう。

取得対象範囲を指定します。最終行は自動取得した値を入れますので除いてください。

貼り付け先のセルを指定します。

VBAの実装

行と列を入れ替えるVBAの実装方法については
VBAの実装手順
をご参照ください。

VBAを実行

VBAを実行しましょう。

はい、最終行が自動判定され漏れがなく行と列が入れ替わり、値として挿入されていますね。

VBAの説明

行と列を入れ替えるVBAについて説明をします。

End(xlUp).Rowで最終行数を取得し、文字結合して範囲設定し、その指定範囲のセルをコピーします。

コピーしたデータを行列入れ替えモードで指定セルへペーストします。

別のシートの表の行と列を入れ替える

最後に別のシートの表の行と列を入れ替えをしてみましょう。

設定は

を以下のようにシート指定するだけです。サンプルのシート名は”Sheet2”としています。

VBAを実行してみましょう。

はい、別シートも行と列の変換ができましたね！

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。