この記事でわかること
- VBAでデータの行と列を入れ替え（転置）できる
- PasteSpecial・Transpose関数・配列ループの3方式を使い分けられる
- 月別の横持ちデータを縦持ちに変換できる（ピボットテーブル用のデータ整形）
対象: Excel 2016以降 / Microsoft 365、Windows 10/11
—
どんな場面で使う？
行列の入れ替え（転置）が必要になるのは、たとえばこんな場面だ。
- 月別売上の横持ちデータをピボットテーブルに入れたい — 各拠点が行、月が列に並んだ横持ちデータは見やすいが、ピボットテーブルや集計関数で扱うには「拠点・月・金額」の3列構成（縦持ち）にする必要がある
- アンケート結果の行列を入れ替えてグラフを作りたい — 質問が列、回答者が行に並んだ集計表を、質問ごとの棒グラフにしたい場合、行と列を入れ替えると項目軸が整う
- 他システムから出力されたCSVの向きが逆 — 基幹システムやBIツールから出力されたデータが横持ちになっていて、自社フォーマット（縦持ち）に合わせる必要がある
- 複数シートのデータを統合する前に向きを揃えたい — シートAは行方向、シートBは列方向にデータが並んでいて、統合前にどちらかを転置して形を揃える場面
- 予算テンプレートの行列を入れ替えて報告書に貼りたい — 部門が行・四半期が列のテンプレートを、報告書用に四半期が行・部門が列のレイアウトに変換するケース
どれも手作業でやると行列の数に比例して時間がかかるし、コピペ先を間違えるリスクが高い作業ばかりだ。VBAで自動化すればデータ量に関係なく一瞬で終わる。
—
完成イメージ（Before / After）
Before（横持ちデータ）:
| 拠点 | 4月 | 5月 | 6月 | … | 3月 |
|---|---|---|---|---|---|
| 東京 | 100 | 120 | 110 | … | 130 |
| 大阪 | 80 | 90 | 85 | … | 95 |
After（縦持ちデータに変換）:
| 拠点 | 月 | 売上 |
|---|---|---|
| 東京 | 4月 | 100 |
| 東京 | 5月 | 120 |
| 東京 | 6月 | 110 |
| 大阪 | 4月 | 80 |
| 大阪 | 5月 | 90 |
| … | … | … |
—
自分も毎月、月別に横に並んだ売上データを集計用に縦持ちへ手作業で並べ替えていた。12か月×30拠点で360セル分を1つずつコピペしていて、途中でどこまでやったか分からなくなることがあった。VBAで転置を自動化してからは、360セルの並べ替えが3秒で終わるようになった。コピペミスもゼロ。同じように横持ちデータの変換に苦労している人に、この記事で自動化を体験してほしい。
行と列の入れ替えは手作業だと量が増えるほどミスが出る。VBAに任せれば速くて正確。
なお、行列の入れ替えではなく単純にセルの値を別シートにコピーしたい場合は セルの転記を自動化する方法 を参照。
—
実行前の準備
バックアップを取る
転置先に既存データがある場合、上書きされる可能性がある。 必ずファイルのコピーを別フォルダに保存してから実行する。
Excelをマクロ有効ブック（.xlsm）で保存する
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
- 保存
シート構成を確認する
このコードは以下のシート構成を前提としている：
- データシート: シート名「データ」— 転置元のデータがある
- 結果シート: シート名「結果」— 転置先
自分のシート名が異なる場合は、コード内のシート名を書き換える。
—
手順（コピペ → 実行まで約5分）
VBE（コードを書く画面）を開く
- Excelで
Alt + F11を押す
標準モジュールを挿入する
- VBEのメニュー →「挿入」→「標準モジュール」
コードを貼り付けて実行する
- コードウィンドウに、下のコードをそのままコピペする
Alt + F8→ マクロ名を選んで「実行」
ボタンに割り当てれば毎回Alt+F8を押さなくて済む。方法は マクロをボタン1つで実行する方法 を参照。
—
コード（基本版）– PasteSpecialで転置貼り付け
書式も含めてそのまま行列を入れ替えたい場合はこの方法が最もシンプル。
'============================================================
' ■ PasteSpecialで行列を入れ替え（基本版）
' → データシートの指定範囲を転置して結果シートに貼り付け
'============================================================
Sub TransposeByPasteSpecial()
'--- ★書き換えポイント ---
Dim srcSheet As String
srcSheet = "データ" '← 転置元のシート名
Dim destSheet As String
destSheet = "結果" '← 転置先のシート名
Dim srcRange As String
srcRange = "A1:D3" '← 転置する範囲
Dim destCell As String
destCell = "A1" '← 転置先の開始セル
'--- ★ここまで ---
'--- 転置元をコピー
ThisWorkbook.Worksheets(srcSheet).Range(srcRange).Copy
'--- 転置貼り付け（Transpose:=True がポイント）
ThisWorkbook.Worksheets(destSheet).Range(destCell).PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
'--- コピー状態を解除（点線枠を消す）
Application.CutCopyMode = False
MsgBox "転置が完了しました。", vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
srcSheet |
転置元のシート名 | "データ" |
destSheet |
転置先のシート名 | "結果" |
srcRange |
転置する範囲 | "A1:D3" |
destCell |
転置先の開始セル | "A1" |
ポイント: Paste:=xlPasteAll は書式も含めて全てを貼り付ける。値だけにしたい場合は Paste:=xlPasteValues に変更する。
コードの処理の流れ:
このコードは3つのステップで動いている。まず Range.Copy で転置元のセル範囲をクリップボードにコピーする。次に PasteSpecial メソッドで転置先に貼り付けるが、ここで Transpose:=True を指定することで行と列が入れ替わった状態で貼り付けられる。PasteSpecialはExcelの「形式を選択して貼り付け」と同じ機能をVBAから呼び出しているだけなので、手作業でやっている人にはイメージしやすいはずだ。最後に Application.CutCopyMode = False でコピー状態を解除する。これを入れないと、セルの周囲に点線枠（コピー中を示すマーキー）が残り続けて見た目が悪い。
—
コード（応用版）– WorksheetFunction.Transposeで転置
配列として値だけを転置したい場合はこちら。PasteSpecialと違い、コピー状態（点線枠）が発生しない。
'============================================================
' ■ WorksheetFunction.Transposeで行列を入れ替え（応用版）
' → 値のみ転置（書式はコピーしない）
'============================================================
Sub TransposeByFunction()
'--- ★書き換えポイント ---
Dim srcSheet As String
srcSheet = "データ" '← 転置元のシート名
Dim destSheet As String
destSheet = "結果" '← 転置先のシート名
Dim srcRange As String
srcRange = "A1:D3" '← 転置する範囲
Dim destCell As String
destCell = "A1" '← 転置先の開始セル
'--- ★ここまで ---
Dim wsSrc As Worksheet
Set wsSrc = ThisWorkbook.Worksheets(srcSheet)
Dim wsDest As Worksheet
Set wsDest = ThisWorkbook.Worksheets(destSheet)
'--- 転置元のデータを配列に読み込み
Dim srcData As Variant
srcData = wsSrc.Range(srcRange).Value
'--- WorksheetFunction.Transpose で転置
Dim result As Variant
result = WorksheetFunction.Transpose(srcData)
'--- 転置結果を書き込み（行数と列数が入れ替わる）
Dim srcRows As Long
srcRows = wsSrc.Range(srcRange).Rows.Count
Dim srcCols As Long
srcCols = wsSrc.Range(srcRange).Columns.Count
wsDest.Range(destCell).Resize(srcCols, srcRows).Value = result
MsgBox "転置が完了しました（値のみ）。", vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
srcSheet |
転置元のシート名 | "データ" |
destSheet |
転置先のシート名 | "結果" |
srcRange |
転置する範囲 | "A1:D3" |
destCell |
転置先の開始セル | "A1" |
コードの処理の流れ:
このコードではまず Range.Value でセルの値を一括で配列に読み込んでいる。セルを1つずつ読むのと比べて、配列に一括で読み込む方がはるかに速い。次に WorksheetFunction.Transpose で行と列を入れ替えた新しい配列を生成する。この関数はExcelワークシート上の TRANSPOSE 関数と同じ機能をVBAから呼び出している。最後に Resize で転置後のサイズ（行数と列数が入れ替わるので Resize(srcCols, srcRows) になる）を指定して、配列の値を一括で書き込む。PasteSpecial方式と違い、クリップボードを経由しないため CutCopyMode の解除が不要で、コードがすっきりする。
注意: WorksheetFunction.Transpose には 65,536行/列 の上限がある。これを超えるデータではエラーになる。大量データの場合は配列ループ（FAQ Q3を参照）を使う。
大量データの高速処理については 配列を使ってVBAの処理速度を10倍にする方法 も参考になる。
—
コード（実務版）– 月別横持ちデータを縦持ちに変換
実務で最も多いのが「月別に横に並んだデータを縦持ちに変換する」パターン。ピボットテーブルでの集計や、データベースへの取り込みには縦持ちデータが必要になる。
横持ち→縦持ち変換を覚えてからは、ピボットテーブルに入れやすい形にデータを整形できるようになった。集計作業全体が楽になった。
'============================================================
' ■ 横持ちデータを縦持ちに変換（実務版）
' → 元データ: 行=拠点、列=月 の横持ち
' → 変換後: 拠点・月・値 の縦持ち（3列）
'============================================================
Sub ConvertWideToLong()
'--- ★書き換えポイント ---
Dim srcSheet As String
srcSheet = "データ" '← 元データのシート名
Dim destSheet As String
destSheet = "結果" '← 変換結果のシート名
Dim headerRow As Long
headerRow = 1 '← ヘッダー行（月名が並ぶ行）
Dim dataStartRow As Long
dataStartRow = 2 '← データの開始行
Dim labelCol As Long
labelCol = 1 '← ラベル列（A列=拠点名）
Dim dataStartCol As Long
dataStartCol = 2 '← データ開始列（B列=4月〜）
Dim destHeaders As Variant
destHeaders = Array("拠点", "月", "売上") '← 変換後のヘッダー
'--- ★ここまで ---
Dim wsSrc As Worksheet
Set wsSrc = ThisWorkbook.Worksheets(srcSheet)
Dim wsDest As Worksheet
Set wsDest = ThisWorkbook.Worksheets(destSheet)
'--- 元データの範囲を取得
Dim lastRow As Long
lastRow = wsSrc.Cells(wsSrc.Rows.Count, labelCol).End(xlUp).Row
Dim lastCol As Long
lastCol = wsSrc.Cells(headerRow, wsSrc.Columns.Count).End(xlToLeft).Column
'--- データがあるか確認
If lastRow < dataStartRow Then
MsgBox "変換するデータがありません。", vbExclamation
Exit Sub
End If
'--- 転置先をクリア
wsDest.Cells.ClearContents
'--- ヘッダーを書き込み
Dim h As Long
For h = 0 To UBound(destHeaders)
wsDest.Cells(1, h + 1).Value = destHeaders(h)
Next h
'--- 横持ち → 縦持ち変換
Dim outRow As Long
outRow = 2 '← 書き込み開始行
Dim r As Long, c As Long
For r = dataStartRow To lastRow
For c = dataStartCol To lastCol
'--- 拠点名
wsDest.Cells(outRow, 1).Value = wsSrc.Cells(r, labelCol).Value
'--- 月名（ヘッダー行から取得）
wsDest.Cells(outRow, 2).Value = wsSrc.Cells(headerRow, c).Value
'--- 値
wsDest.Cells(outRow, 3).Value = wsSrc.Cells(r, c).Value
outRow = outRow + 1
Next c
Next r
MsgBox (outRow - 2) & " 行の縦持ちデータを作成しました。", vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
srcSheet |
元データのシート名 | "データ" |
destSheet |
変換結果のシート名 | "結果" |
headerRow |
月名が並ぶヘッダー行 | 1 |
dataStartRow |
データの開始行 | 2 |
labelCol |
ラベル列（拠点名など） | 1（A列） |
dataStartCol |
データの開始列 | 2（B列） |
destHeaders |
変換後のヘッダー名 | "拠点", "月", "売上" |
コードの処理の流れ:
この実務版コードは、横持ちデータを縦持ちに「展開」する処理を行っている。単純な行列入れ替えとは違い、1行のデータが複数行に展開される点が特徴だ。外側の For r ループでデータ行（拠点）を、内側の For c ループでデータ列（月）を回している。ループの中では、拠点名はヘッダー行ではなくラベル列（A列）から取得し、月名はヘッダー行から取得している。こうすることで、元データの構造に依存せず柔軟に変換できる。outRow という変数で書き込み先の行番号を管理しており、1セル書くごとに1ずつ増やしている。最終行の取得には End(xlUp) を使っているので、データ件数が変わっても自動で対応する。最終行取得の詳しい仕組みは データの最終行・最終列を正確に取得する方法 を参照。
行列の入れ替えではなく、単純にセルの値を別シートにコピーしたい場合は セルの転記を自動化する方法 を参照。
—
よくある落とし穴5選
1. CutCopyMode の解除忘れで点線枠が残る
原因: PasteSpecial方式でコピー後に Application.CutCopyMode = False を書き忘れた。
対策: PasteSpecialの後には必ず Application.CutCopyMode = False を入れる。基本版コードには最初から入っている。
2. WorksheetFunction.Transpose で「関数の引数が正しくありません」エラー
自分もこれで引っかかった。WorksheetFunction.Transposeで65,536行を超えるデータを転置しようとしてエラーになった。上限があることを知らなかった。
対策: 65,536行/列を超える場合は配列ループ方式を使う（FAQ Q3を参照）。
3. 転置先の範囲指定で行数と列数を逆にし忘れた
原因: 元データが3行×4列なら、転置後は4行×3列になる。Resize の引数を元データと同じにするとサイズが合わない。
対策: Resize(srcCols, srcRows) のように行と列を入れ替えて指定する。
4. 同じシートで転置したら元データが壊れた
原因: 転置元と転置先の範囲が重なっていると、コピー中にデータが書き換わる。
対策: 転置先は別シートにするか、元データと重ならない位置を指定する。
5. 1行 or 1列のデータで WorksheetFunction.Transpose の結果がおかしい
原因: 1行だけのデータを WorksheetFunction.Transpose にかけると、結果が2次元配列ではなく1次元配列になる場合がある。
対策: 複数行×複数列のデータでは問題ない。1行/1列の場合はPasteSpecial方式を使う。
VBAのTranspose関数でエラーが出るときの対処法
「WorksheetFunction.Transposeを実行すると『関数の引数が正しくありません』というエラーが出る」という場合、原因はデータの行数または列数が65,536を超えていることだ。Transpose関数にはExcelの配列サイズの上限があり、大量データでは使えない。自分も10万行のデータを転置しようとしてこのエラーに遭遇した。対処法は、FAQ Q3で紹介している配列ループ方式に切り替えること。ReDim result(1 To cols, 1 To rows) で結果配列を用意し、ネストしたForループで result(c, r) = src(r, c) と値を入れ替えれば、データ量の制限なく転置できる。
VBAで行列入れ替え時に大量データで処理が失敗するときの対処法
「数千行のデータを転置しようとしたら、処理が途中で止まる・Excelが固まる」という場合、原因は画面更新と自動計算がONのまま大量のセル書き込みを行っていることだ。転置処理はセルの読み書き回数が多いため、画面更新のオーバーヘッドが大きい。対処法は、処理の前に Application.ScreenUpdating = False と Application.Calculation = xlCalculationManual を入れ、処理後に元に戻すこと。さらに、セルを1つずつ読み書きするのではなく配列に一括で読み込んでから処理すると、体感で数十倍速くなる。配列を使ってVBAの処理速度を10倍にする方法も参考になる。
—
FAQ
Q1: 書式も含めて転置したい
基本版（PasteSpecial方式）を使う。Paste:=xlPasteAll で書式ごと転置される。
Q2: 値だけ転置したい（書式はいらない）
応用版（WorksheetFunction.Transpose方式）を使う。または基本版で Paste:=xlPasteValues に変更する。
Q3: 65,536行を超えるデータを転置したい
配列ループで転置する：
Dim src As Variant
src = Range("A1:D100000").Value
Dim rows As Long, cols As Long
rows = UBound(src, 1)
cols = UBound(src, 2)
Dim result() As Variant
ReDim result(1 To cols, 1 To rows)
Dim r As Long, c As Long
For r = 1 To rows
For c = 1 To cols
result(c, r) = src(r, c)
Next c
Next r
Range("F1").Resize(cols, rows).Value = result
Q4: 横持ちデータを縦持ちに変換したい
実務版コードを参照。月別データの横→縦変換に対応している。変換後のデータはピボットテーブルのソースデータとしてそのまま使える。
Q5: 転置後に元データを消したい
転置処理の後に元データをクリアする。誤消去を防ぐため確認ダイアログを挟む：
Dim ans As VbMsgBoxResult
ans = MsgBox("元データを消去しますか？", vbYesNo + vbQuestion)
If ans = vbYes Then
wsSrc.Range(srcRange).ClearContents
End If
—
まとめ
- PasteSpecial方式: 書式も含めて転置。
Transpose:=Trueを指定するだけ - WorksheetFunction.Transpose: 値だけ転置。ただし65,536行/列の上限あり
- 配列ループ: 上限なし。大量データやカスタム処理向き
- 横持ち→縦持ち変換: 実務版コードでピボットテーブル用のデータ整形ができる
関連記事
- セルの転記を自動化する方法 — 行列入れ替えではなく単純なセル転記はこちら
- 配列を使ってVBAの処理速度を10倍にする方法 — 大量データの高速処理テクニック
- データの最終行・最終列を正確に取得する方法 — 転置範囲を動的に取得
- ピボットテーブルをVBAで自動生成する方法 — 縦持ちデータをピボットで集計
- マクロをボタン1つで実行する方法 — 転置マクロをボタンに割り当てて素早く実行
—
次にやりたくなること
転置の自動化ができたら、次はこのあたりに挑戦してみてほしい。
- セルの転記を自動化する方法: 転置ではなく別シートへの単純コピーを自動化したい場合はこちら
- Excelファイルを自動で開いて処理して閉じる方法: 複数ファイルのデータを開いて転置して閉じる、という一連の流れを自動化できる
- オートフィルタでデータを絞り込む方法: 転置後のデータから特定条件のレコードだけ抽出したい場合に便利
- Dictionaryで集計を自動化する方法: 縦持ちに変換したデータを拠点別・月別に集計する処理と組み合わせると強力
- 処理時間を計測してボトルネックを見つける方法: 大量データの転置で処理が遅いと感じたら、どこに時間がかかっているか計測してみよう
—
もっとカスタマイズしたい場合
「横持ち→縦持ちの変換ルールが複雑（複数のラベル列がある）」「複数シートのデータを一括で転置したい」「転置後に自動で集計・グラフ作成もしたい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できる。
相談時に伝えると話が早い情報:
- Excel のバージョン / OS
- 元データの構成（行がどの項目で、列がどの項目か）
- データの行数・列数の目安
- 変換後にどう使うか（ピボットテーブル・データベース取り込みなど）
コメント