ねらい：もらったExcelを「一発で“見れる形”に直す」レイアウト修正テンプレ
他部署や取引先からもらった Excel、こんな状態で届きませんか。
- 列幅バラバラ・行高バラバラ
- 罫線が中途半端／二重線／色がバラバラ
- フォントが混在・サイズも段々畑
- ヘッダーだけ太字・中央揃えにしたい
- 印刷すると右端が切れる・ページが変なところで分割される
そのたびに「列幅調整→罫線書き直し→フォント揃え→印刷設定…」を手作業でやっていると、地味に時間を奪われます。
レイアウト修正テンプレのゴールは、
「貼り付けた生データに対して、ボタン1つで“見れる表・印刷できる表”に整える」
ための VBA 部品をセットで用意しておくことです。
用途ごとに部品を組み合わせて使えば、どんな表でも「だいたい同じ見た目」に揃えられるようになります。
共通基盤：範囲取得・安全な CurrentRegion・フォント／罫線の一括適用
基本ユーティリティ（全テンプレ共通の土台）
' ModLayout_Base.bas
Option Explicit
Public Function GetUsedBlock(ws As Worksheet, Optional ByVal topLeft As String = "A1") As Range
' A1 からの CurrentRegion を基本範囲とする
' 貼り付け位置を変えたい場合は topLeft を変える
With ws.Range(topLeft)
If .CurrentRegion.Rows.Count = 1 And .CurrentRegion.Columns.Count = 1 And IsEmpty(.Value) Then
Set GetUsedBlock = Nothing
Else
Set GetUsedBlock = .CurrentRegion
End If
End With
End Function
Public Sub ApplyFontAndAlign(ByVal rng As Range, _
Optional ByVal fontName As String = "Meiryo UI", _
Optional ByVal fontSize As Double = 9)
With rng
.Font.Name = fontName
.Font.Size = fontSize
.VerticalAlignment = xlVAlignCenter
.WrapText = True
End With
End Sub
Public Sub ApplyBorder(ByVal rng As Range)
With rng.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(200, 200, 200)
End With
End Sub
Public Sub AutoFitAll(ByVal rng As Range)
rng.EntireColumn.AutoFit
rng.EntireRow.AutoFit
End Sub
ここがレイアウト修正の“核”です。
- GetUsedBlock：A1 基準の CurrentRegion で「表の範囲」を拾う基本。
- ApplyFontAndAlign：フォント名・サイズ・縦位置・折り返しを一括で整える。
- ApplyBorder：標準的な罫線（薄いグレー）を一気に引く。
- AutoFitAll：行と列の自動調整。
この4つを覚えておくだけで、「とにかくぐちゃぐちゃを整える」ことはほぼできます。
ヘッダ行・データ行の見た目テンプレ：1行目を“ヘッダっぽく”整える
見出し行（ヘッダ）の強調テンプレ
' ModLayout_Header.bas
Option Explicit
Public Sub FormatHeaderRow(ByVal ws As Worksheet, Optional ByVal headerRow As Long = 1, _
Optional ByVal fontName As String = "Meiryo UI")
Dim blk As Range
Set blk = GetUsedBlock(ws, "A1")
If blk Is Nothing Then Exit Sub
Dim hdr As Range
Set hdr = blk.Rows(headerRow)
With hdr
.Font.Name = fontName
.Font.Size = 9
.Font.Bold = True
.Interior.Color = RGB(230, 230, 250) ' 薄い色
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = False
End With
' 下のデータ行は上書きされないようにフォントだけ薄く揃える例
With blk.Offset(1, 0).Resize(blk.Rows.Count - 1)
.Font.Name = fontName
.Font.Size = 9
.Font.Bold = False
End With
End Sub
ここでの重要ポイントを深掘りします。
- ヘッダ行（デフォルト1行目）は見た目が「太字＋背景色＋中央寄せ」であると、人は一瞬で“これは見出しだ”と認識できます。
- データ行はあえて太字にしないことで、ヘッダとのコントラストが出ます。
- WrapText（折り返し）はヘッダでは OFF にしておき、列幅を広げて見やすくします。
この Sub は「ヘッダ行を一段“テーブルっぽく”するだけ」の役割に絞ってあります。
他のテンプレと組み合わせて使えるように、役割を分けるのがコツです。
一括レイアウト修正：列幅／罫線／フォント／数値書式／グリッド線非表示
「もらった表」を一気に綺麗にする標準テンプレ
' ModLayout_Standard.bas
Option Explicit
Public Sub FixLayout_Standard(Optional ByVal sheetName As String = "")
Dim ws As Worksheet
If sheetName = "" Then
Set ws = ActiveSheet
Else
Set ws = Worksheets(sheetName)
End If
Dim blk As Range
Set blk = GetUsedBlock(ws, "A1")
If blk Is Nothing Then
MsgBox "整形対象のデータが見つかりません。", vbInformation
Exit Sub
End If
' 1) 全体のフォント・縦位置・折り返し
Call ApplyFontAndAlign(blk)
' 2) ヘッダ行の強調（1行目前提）
Call FormatHeaderRow(ws, 1)
' 3) 罫線を引き直す
Call ApplyBorder(blk)
' 4) 列幅・行高の自動調整
Call AutoFitAll(blk)
' 5) 数値列の書式（簡易：数値っぽい列に #,##0 をかける）
Dim c As Long, r As Long, isNumericCol As Boolean
For c = 1 To blk.Columns.Count
isNumericCol = True
For r = 2 To blk.Rows.Count ' データ行だけざっとチェック
If Not IsEmpty(blk.Cells(r, c).Value) Then
If Not IsNumeric(blk.Cells(r, c).Value) Then
isNumericCol = False
Exit For
End If
End If
Next
If isNumericCol Then
blk.Columns(c).NumberFormatLocal = "#,##0"
End If
Next
' 6) グリッド線を消して「帳票っぽい」見た目に
ws.Activate
ActiveWindow.DisplayGridlines = False
MsgBox "レイアウトの標準整形が完了しました。", vbInformation
End Sub
このテンプレひとつで、ほとんどの“雑に送られてきた表”はだいぶマシになります。
重要なポイントを整理すると、
- フォントと縦位置を揃えるだけで、ガタガタ感が減る。
- 罫線を一度全消し→一括で引き直すことで、「太線／細線／色」バラバラ問題をリセットできる。
- 数値列検出は簡易ですが「全部数値っぽい列だけに書式をかける」ので、文字コード列などへの誤適用を減らせます。
- グリッド線を消すと、帳票やレポートのような“紙のドキュメント”感が出ます。
「とにかく見づらい表を、数秒で“最低限見れる表”に直す」ための基本技として、1本覚えておくと重宝します。
印刷レイアウト修正：1ページに収める・余白・ヘッダ行の繰り返し
印刷設定を一括で“まともにする”テンプレ
' ModLayout_Print.bas
Option Explicit
Public Sub FixPrintLayout(Optional ByVal sheetName As String = "")
Dim ws As Worksheet
If sheetName = "" Then
Set ws = ActiveSheet
Else
Set ws = Worksheets(sheetName)
End If
Dim blk As Range
Set blk = GetUsedBlock(ws, "A1")
If blk Is Nothing Then Exit Sub
With ws.PageSetup
.Orientation = xlPortrait ' 縦向き（横向きにしたければ xlLandscape）
.Zoom = False
.FitToPagesWide = 1 ' 横1ページに収める
.FitToPagesTall = False ' 縦は自動（複数ページ可）
.LeftMargin = Application.CentimetersToPoints(1.0)
.RightMargin = Application.CentimetersToPoints(1.0)
.TopMargin = Application.CentimetersToPoints(1.5)
.BottomMargin = Application.CentimetersToPoints(1.5)
.HeaderMargin = Application.CentimetersToPoints(0.8)
.FooterMargin = Application.CentimetersToPoints(0.8)
.PrintTitleRows = "$1:$1" ' 1行目ヘッダを各ページに繰り返し表示
.PrintArea = blk.Address ' 表だけ印刷範囲にする
.CenterHorizontally = True ' 中央寄せ（左右）
End With
MsgBox "印刷レイアウトの整形が完了しました。印刷プレビューでご確認ください。", vbInformation
End Sub
ここでの深掘りポイント。
- Zoom を OFF にして FitToPagesWide=1 にすることで、「右端が切れる」「ページが変なところで折れる」問題がかなり減ります。
- PrintTitleRows を指定すると、2ページ目以降にもヘッダ行が印刷されるので、紙で見ても迷子になりにくくなります。
- PrintArea に CurrentRegion を当てることで、「謎の余白や離れたセル」が印刷される事故を防げます。
標準整形テンプレ（FixLayout_Standard）とセットで使うと、
「見た目整える→印刷レイアウト整える→PDF出力」の流れを一気に作れます。
列の入れ替え・非表示・並び順の統一：レイアウト定義表を使う
「この順で列を並べたい」を表にして、自動で並び替える
破壊力が大きいのが「列並びのバラバラを揃えるテンプレ」です。
これは少しだけレベルを上げますが、慣れると“超再利用部品”になります。
LayoutDef シートを1枚作り、こういう表を持ちます。
A列：FromHeader（元のヘッダ名）
B列：ToHeader（新しいヘッダ名。空なら同じ）
C列：OrderNo（並べたい順番。1,2,3,…）
D列：Visible（表示するか。Y/N）
例：
| FromHeader | ToHeader | OrderNo | Visible |
|---|---|---|---|
| 顧客名 | 顧客名 | 1 | Y |
| 顧客ID | 顧客コード | 2 | Y |
| 郵便番号 | 郵便番号 | 3 | Y |
| 電話番号 | TEL | 4 | Y |
| 内部メモ | 99 | N |
この定義に基づいて、データシートの列を並べ替えるテンプレを作ります。
' ModLayout_ColumnOrder.bas
Option Explicit
Public Sub ReorderColumnsByLayout(ByVal dataSheet As String, ByVal layoutSheet As String)
Dim wsData As Worksheet, wsLayout As Worksheet
Set wsData = Worksheets(dataSheet)
Set wsLayout = Worksheets(layoutSheet)
Dim data As Variant, layout As Variant
data = wsData.Range("A1").CurrentRegion.Value
layout = wsLayout.Range("A1").CurrentRegion.Value
Dim idxFrom As Object
Set idxFrom = CreateObject("Scripting.Dictionary")
idxFrom.CompareMode = 1
Dim c As Long
For c = 1 To UBound(data, 2)
idxFrom(LCase$(Trim$(CStr(data(1, c))))) = c
Next
Dim orderList As Object
Set orderList = CreateObject("Scripting.Dictionary")
Dim l As Long
For l = 2 To UBound(layout, 1)
Dim fromName As String
fromName = LCase$(Trim$(CStr(layout(l, 1))))
If idxFrom.Exists(fromName) Then
Dim ord As Long
ord = CLng(layout(l, 3))
If layout(l, 4) = "Y" Or layout(l, 4) = "y" Then
orderList(ord) = fromName
End If
End If
Next
Dim keys() As Variant
If orderList.Count = 0 Then Exit Sub
ReDim keys(1 To orderList.Count)
Dim i As Long, k As Variant
i = 1
For Each k In orderList.Keys
keys(i) = k
i = i + 1
Next
Dim j As Long, tmp
For i = 1 To UBound(keys) - 1
For j = i + 1 To UBound(keys)
If CLng(keys(i)) > CLng(keys(j)) Then
tmp = keys(i): keys(i) = keys(j): keys(j) = tmp
End If
Next
Next
Dim newCols As Long
newCols = orderList.Count
Dim out() As Variant
ReDim out(1 To UBound(data, 1), 1 To newCols)
For i = 1 To newCols
Dim keyOrd As Long
keyOrd = CLng(keys(i))
Dim fname As String
fname = orderList(keyOrd)
Dim colSrc As Long
colSrc = idxFrom(fname)
Dim toHeader As String
toHeader = ""
For l = 2 To UBound(layout, 1)
If LCase$(Trim$(CStr(layout(l, 1)))) = fname Then
toHeader = CStr(layout(l, 2))
Exit For
End If
Next
If toHeader = "" Then toHeader = CStr(data(1, colSrc))
out(1, i) = toHeader
Dim r As Long
For r = 2 To UBound(data, 1)
out(r, i) = data(r, colSrc)
Next
Next
wsData.Cells.Clear
wsData.Range("A1").Resize(UBound(out, 1), UBound(out, 2)).Value = out
FormatBlock wsData, "A1"
End Sub
これは少し長いですが、やっていることはシンプルです。
- 元データヘッダ → 列番号 の辞書を作る
- レイアウト定義に従って「順番」と「表示／非表示」を決める
- 新しい並びの配列を作ってシートに書き戻す
これで、「毎回順番が違う Excel」を「いつも同じ順番」に変換できます。
カラム名も ToHeader で変えられるので、「顧客ID → 顧客コード」のような名前変更も一緒にできます。
実行例：もらった生データを“業務標準のレイアウト”に落とし込む
実行の流れを 1 つにまとめた例
' ModLayout_Example.bas
Option Explicit
Public Sub Run_LayoutFix_Example()
Dim ws As Worksheet
Set ws = Worksheets("RawData") ' もらった生データを貼るシートとする
' 1) 列並びとヘッダ名を揃える
ReorderColumnsByLayout "RawData", "LayoutDef"
' 2) 標準レイアウト整形（フォント・罫線・数値書式）
FixLayout_Standard "RawData"
' 3) 印刷レイアウト整形
FixPrintLayout "RawData"
MsgBox "レイアウト修正テンプレの例が完了しました。", vbInformation
End Sub
この Sub をボタンに割り当てておけば、
- RawData にコピペ
- ボタン押す
- 列順・ヘッダ名・見た目・印刷設定が全部“社内標準の形”になる
という運用にできます。
落とし穴と対策（重要ポイントの深掘り）
CurrentRegion まかせで“余計な範囲”まで巻き込む問題
A1 の CurrentRegion は便利ですが、
右や下に「たまたま何か入力されているセル」があると、そこまで巻き込んでしまいます。
対策としては、
- レイアウト整形をかける前に、「A列の最終行」「1行目の最終列」で自分で範囲を決める
- もしくは「一度余計なセルを削除してから CurrentRegion を使う」
などがあります。
実務では「データ貼付け専用シート」を用意して、そこは“余計なものを絶対置かない”ルールにするのが楽です。
数値列の自動判定が完全ではないことを理解しておく
数値列判定は「データ行が全て数値 or 空なら数値列」としていますが、
たまに文字が混ざっていると数値書式がかからないことがあります。
大事なのは、「このロジックは完璧ではない」と理解したうえで、
- よく使う標準レイアウトでは、明示的に列番号を指定して書式をかける
- それ以外は“だいたい整う”くらいに期待値を下げる
という使い分けをすることです。
列並び定義（LayoutDef）と元データヘッダのズレ
FromHeader が 1文字でも違うと、「その列は並べ替え対象外」になります。
最初はそれで構いませんが、本番運用では
- LayoutDef の FromHeader は「元データヘッダのままの表記」を使う
- どう変えたいかは ToHeader に書く
というルールにしておくと混乱が減ります。
見た目とロジックを混ぜすぎない
レイアウトテンプレは、「見た目」に特化させるのがコツです。
計算ロジックやデータ加工まで一緒に入れ始めると、
「レイアウト修正したいだけなのに計算も走る」という重いマクロになってしまいます。
データ加工（クレンジング・集計）は別テンプレに分け、
レイアウト修正は“最後の化粧”の役割に留めておくと、全体の構造がシンプルになります。
まとめ：レイアウト修正も「部品化」して、どんな表も同じ“顔つき”にする
レイアウト修正テンプレのキモは、
- フォント・罫線・数値書式・印刷設定を部品単位で用意しておく
- 列並びは LayoutDef などの“定義表”で管理する
- 「生データを貼る → ボタン1つで社内標準レイアウトにする」という流れを作る
ことです。
この型さえ作ってしまえば、
他部署からどんなぐちゃぐちゃな Excel が来ても、
「RawDataに貼る → レイアウトボタン → あとは分析や帳票生成に回す」
というシンプルな運用に変えられます。
