- 01.はじめに
- 02.エクセルにおける目次の役割と必要性
- 03.手動で目次を作成する方法（初心者向け）
- 04.VBAで目次を自動作成する方法【基本編】
- 05.VBAコードをカスタマイズする方法【応用編】
- 06.自動目次をより便利に使うテクニック
- 07.VBAが使えない環境で目次を活用する方法
- 08.よくあるトラブルと対処法
- 09.まとめ｜Excel目次作成で業務の見通しが変わる
【2025年4月28日】
VBAコードについて目次作成を辞書順から自然順に修正
01.はじめに
Excelを使った業務――それは、思った以上に迷子になりがちです。
気がつけばシートが10枚、20枚と増えていき、「あれ、このデータどこにあったっけ？」って経験、ありませんか？
特に月次レポートやプロジェクトの進捗管理、部署ごとの集計ファイルなどをひとつのブックで管理している場合、どのシートに何があるのか一目でわかる「目次」は、もはや“必須装備”。
もちろん、手動で目次シートを作ることもできます。でもそれって、ちょっと面倒だし、なにより 「効率化」になってません。
そんな時に登場するのが、 VBAを使った目次の自動作成 というスマートな解決策。
このブログでは、
– 手動での目次作成の基本
– VBAで一瞬で目次を作る方法
– コードのカスタマイズや応用テクニック
– トラブル時の対処法
まで、完全網羅でご紹介します。
初心者にもわかる丁寧な解説から、実務ですぐに役立つ応用まで、今日からあなたのExcelが“プロ仕様”になること間違いなしです。
02.エクセルにおける目次の役割と必要性
なぜエクセルに「目次」が必要なのか？
Excelは「見ればわかる」ツールと思われがちですが、シート数が増えれば増えるほど、迷子になりがち。
たとえば、以下のような状況になったことはないでしょうか？
- ファイルを開いたとき、どのシートから確認すべきか迷う
- 顧客別／月別／部署別など、構造が複雑になりシート間の移動に時間がかかる
- 他の人にファイルを渡したとき、「どこに何があるのか分かりづらい」と言われる
これらの問題は、目次シートが1枚あるだけで解消されることがほとんどです。
目次があることで得られる5つのメリット
1. 必要なシートにすぐアクセスできる
リンク付きの目次があれば、数十枚あるシートでもクリック一つでジャンプ可能。移動のストレスがなくなります。
2. ファイルの構造がひと目でわかる
全体像を一覧できるので、ファイルの設計や運用ルールが明確になります。
3. 他人に渡しても親切
目次があれば、初見の人でも「どこに何があるか」が直感的に理解でき、引き継ぎや共有にも役立ちます。
4. 更新・修正漏れを防止できる
目次で全シートが一覧できるので、見落としや記入ミスを事前に防ぎやすくなります。
5. “デキる人感”が出る（地味に大事）
自動目次があると「この人、仕事できそう」と思わせる効果があります。見た目は大事！
実務での具体的な利用シーン
- 営業部門：顧客ごとの進捗管理ファイルで、各顧客のシートに飛べる目次を作成
- 経理部門：月次レポートの各月シートにアクセスするためのインデックス作成
- 製造業の品質管理：検査記録やロットごとの履歴シートを一覧化
- プロジェクトマネジメント：WBS、進捗、課題管理などの各タブを整理し、目次から誘導
どんな業種でも、「複数の情報を1つのファイルで管理する」場面では、目次があるかないかで作業効率が大きく変わってきます。
目次は、単なる“おまけ”ではなく、Excelの見通しを良くする「ナビゲーション機能」です。
次章では、まず手動で目次を作る基本的な方法を確認してみましょう。
03.手動で目次を作成する方法（初心者向け）
「VBAはちょっとハードルが高い…」という方もご安心を。
まずは、誰でもできる手動での目次作成から始めてみましょう。
数シート程度のファイルであれば、これだけでも十分に便利です。
ステップ①：目次用のシートを新規作成
まずはワークブックの一番左、もしくは右端に新しいシートを追加します。
- シート見出しの「＋」ボタンをクリック
- 追加されたシート名を「目次」や「Index」などに変更
- 今後、他のユーザーが見てもわかりやすいように名前を工夫しましょう
ステップ②：シート名をリストアップ
目次シートのA列に、他のすべてのシート名を手入力またはコピーして入力します。
例：
A1：営業レポート
A2：集計シート
A3：月次報告書
A4：顧客一覧
※正確なシート名を入れないと、次のステップでリンクがうまくいかないので注意！
ステップ③：ハイパーリンクを設定する
各シート名にリンクを設定して、クリックで移動できるようにします。
- リンクを設定したいセルを右クリック →「ハイパーリンク」を選択
- 「このドキュメント内の場所」を選び、リンク先のシート名を指定
- 必要に応じてセル番地（例：A1など）も設定可能
- 「OK」をクリック
これを繰り返すと、シート一覧が“インタラクティブな目次”に早変わりします。
ステップ④（任意）：見た目を整える
リンク文字列に色を付けたり、枠線やアイコンを入れたりして、より分かりやすく、使いやすい目次にカスタマイズしてみましょう。
- フォントサイズを大きめに
- 色分けでカテゴリ化（例：青＝報告書系、緑＝データ系）
- 太字・枠線で装飾
手動で作成するメリットとデメリット
| 項目 | メリット | デメリット |
|---|---|---|
| 難易度 | 誰でも簡単に作成できる | シートが多いと作業が大変 |
| メンテナンス性 | 一部だけ修正しやすい | シート名変更時にリンク切れのリスク |
| 時間効率 | 少数シートなら問題なし | 毎回手作業は非効率 |
少数のシート構成ならこの方法で充分ですが、10枚以上のシートがある場合には効率が一気に悪化します。
そこで次章では、VBAを使った“全自動目次作成術”をご紹介します。
クリックひとつで目次と並び替えまでやってくれる、まさに業務効率化には必須の機能です。
04.VBAで目次を自動作成する方法【基本編】
手動での目次作成は確かに便利ですが、シートが10枚を超えると管理が大変になります。
そんな時に活躍するのが VBA（マクロ）を使った目次の自動作成です。
以下で紹介するコードを使えば、ワンクリックで目次を作成し、さらにシートの順序まで整えてくれます。
コピペで使えるVBAコード
まずは以下のコードをコピーして、Excelの「開発」タブ →「Visual Basic」からモジュールを挿入して貼り付けてください。
Sub 目次作成と並べ替え_自然順()
Dim a() As String ' ← String型に変更
Dim i As Long, j As Long
Dim idx As Long
Dim sh As Worksheet
Dim temp As String ' ← String型に変更
Dim cnt As Long
' すべてのシートを表示
For Each sh In Worksheets
sh.Visible = True
Next
' 目次シート削除処理
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("目次").Delete
On Error GoTo 0
Application.DisplayAlerts = True
' シート数カウント
cnt = 0
For Each sh In Worksheets
If sh.Name <> "目次" Then cnt = cnt + 1
Next
' シート名取得
ReDim a(1 To cnt)
idx = 1
For Each sh In Worksheets
If sh.Name <> "目次" Then
a(idx) = sh.Name
idx = idx + 1
End If
Next
' 自然順ソート（修正箇所）
For i = 1 To cnt - 1
For j = i + 1 To cnt
If NaturalCompare(CStr(a(i)), CStr(a(j))) > 0 Then ' ← CStr追加
temp = a(i)
a(i) = a(j)
a(j) = temp
End If
Next j
Next i
' 目次シート作成
Sheets.Add Before:=Sheets(1)
ActiveSheet.Name = "目次"
' ハイパーリンク設定
For i = 1 To cnt
Sheets("目次").Hyperlinks.Add _
Anchor:=Sheets("目次").Cells(i, 1), _
Address:="", _
SubAddress:="'" & a(i) & "'!A1", _
TextToDisplay:=a(i)
Next
' シート並べ替え
For i = 1 To cnt
Worksheets(a(i)).Move After:=Worksheets(Worksheets.Count)
Next i
' 目次を先頭に
Worksheets("目次").Move Before:=Worksheets(1)
MsgBox "目次作成とシート並べ替え（自然順）が完了しました！", vbInformation, "処理完了"
End Sub
' 自然順比較関数（変更不要）
Function NaturalCompare(strA As String, strB As String) As Integer
Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")
Dim matchesA As Object, matchesB As Object
Dim i As Integer, nA As Long, nB As Long ' ← Long型に変更
Dim sA As String, sB As String
Dim minLen As Integer
reg.Global = True
reg.Pattern = "(\d+|\D+)"
Set matchesA = reg.Execute(strA)
Set matchesB = reg.Execute(strB)
minLen = Application.Min(matchesA.Count, matchesB.Count)
For i = 0 To minLen - 1
sA = matchesA(i).Value
sB = matchesB(i).Value
If IsNumeric(sA) And IsNumeric(sB) Then
nA = CLng(sA) ' ← Long型対応
nB = CLng(sB) ' ← Long型対応
If nA <> nB Then
NaturalCompare = Sgn(nA - nB)
Exit Function
End If
Else
If sA <> sB Then
NaturalCompare = Sgn(StrComp(sA, sB, vbTextCompare))
Exit Function
End If
End If
Next i
NaturalCompare = Sgn(matchesA.Count - matchesB.Count)
End Function
コードの処理内容と流れ
このVBAは、次のような流れで動作します。
-
全シートの表示を強制的にONにする
非表示のシートも含めて目次にリストアップされます。 -
すべてのシート名を配列に格納し、シート名を「数字と文字列を分割」し、
数字部分は数値として比較、文字部分は文字列として比較、「目次」シートを除外して自然順にソートする仕様になっています。
これは、Windowsのエクスプローラーでファイル名を並べ替えるときの順番と同じです。
数字を含むシート名がある場合、直感的で分かりやすい順番になります。 -
既存の「目次」シートがあれば削除し、新しい目次を一番前に作成
-
全シートへのハイパーリンクを設定
クリックすると該当シートのA1セルへジャンプできる仕組み。 -
ソートされた順番でシートの並び替えを実施
-
目次シートを先頭に固定
-
処理完了のメッセージボックスを表示
このVBAを使うメリット
- ✔ 一瞬で目次が完成（10秒もかかりません）
- ✔ シート順も自動で整理されてスッキリ
- ✔ ハイパーリンク付きでナビゲーションも簡単
- ✔ ファイル共有時にも親切
このコードさえあれば、「もう手動で目次を作る必要はありません」。
次章では、さらにこのVBAを“カスタマイズ”してもっと便利に使う方法をご紹介します！
05.VBAコードをカスタマイズする方法【応用編】
基本の自動目次VBAを使うだけでも十分便利ですが、ちょっとしたカスタマイズでさらに業務にフィットさせることができます。
この章では、目的別にVBAコードを改良する方法を4つご紹介します。
カスタマイズ①：特定のシートを目次から除外する
「設定」や「テンプレート」など、目次には載せたくないシートがある場合は、次のようにします。
If a(i) <> "目次" And a(i) <> "設定" And a(i) <> "テンプレート" Then
この1行を、ハイパーリンクを設定している部分と、並び替えている部分のIf文に追加してください。
複数の除外対象を「And」でつなぐのがポイントです。
カスタマイズ②：目次に説明コメントを追加する
シート名だけだと中身が分かりにくい場合、横の列に簡単な説明文を追加して、目次をガイドブックのようにすることができます。
Sheets("目次").Cells(i, 2).Value = "○○に関するデータ"
これは例ですが、条件に応じて説明文を自動で入れるようにすることも可能です（IF文やSelect Case文と組み合わせて応用可）。
カスタマイズ③：目次に色や装飾を追加して見やすくする
目次の可読性をアップするには、以下のような見た目の工夫も効果的です。
With Sheets("目次").Cells(i, 1)
.Font.Bold = True
.Font.Size = 11
.Interior.Color = RGB(240, 240, 240)
End With
.Font.Bold→ 太字.Font.Size→ フォントサイズ変更.Interior.Color→ 背景色の変更
視認性が上がるので、プレゼンや共有資料にも好印象を与えます。
カスタマイズ④：ハイパーリンクではなくボタンでジャンプするには？
VBAボタン（フォームコントロール）を使って、クリックで対象シートにジャンプする方法もあります。
ただし、大量のシートに対しては実装がやや面倒なので、小規模ファイル向けです。
基本の流れは以下の通り
目次シートにボタンを配置（ActiveSheet.Buttons.Addなど）- 各ボタンにジャンプ用のマクロを割り当てる
- ジャンプ先の処理を書く（例：
Sheets("営業レポート").Activate）
複雑になりますが、デザイン性を重視したファイルを作る場合にはアリです。
カスタマイズのコツまとめ
| カスタマイズ内容 | 対象場面 | 難易度 |
|---|---|---|
| 除外シートの設定 | テンプレや非表示シート除外 | ★☆☆ |
| コメント追加 | 複数人で使うファイル | ★★☆ |
| デザイン調整 | 社内共有や印象UP | ★★☆ |
| ボタン化 | 見た目重視・小規模ファイル | ★★★ |
「業務に合ったカスタマイズを加える」ことで、VBAはより強力なツールになります。
次章では、この目次VBAをさらに便利に活用するテクニック集をご紹介します。
06.自動目次をより便利に使うテクニック
VBAで目次を自動作成できるようになったら、次は「どう活用するか」がポイントになります。
この章では、業務で目次をよりスマートに使いこなすためのテクニックをいくつかご紹介します。
テクニック①：ブックを開いたら自動で目次にジャンプする
ファイルを開いた瞬間に「目次」シートが表示されたら便利ですよね。
以下のコードを ThisWorkbook モジュールに記述することで、ブックオープン時に自動で目次へ移動できます。
Private Sub Workbook_Open()
Sheets("目次").Activate
End Sub
補足：
ThisWorkbookは「Microsoft Excel Objects」内にあります- セキュリティ設定でマクロが無効になっていると動作しないので注意
テクニック②：保存時に目次を再生成する
ファイルの更新中にシート名や構成が変わることもあります。
そのたびに手動で目次を更新するのは手間なので、保存時に目次を自動生成するようにしておくと効率的です。
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call 目次作成と並べ替え End Sub
これも ThisWorkbook に記述します。
こうすることで、保存するたびに最新の状態で目次が作成されるようになります。
テクニック③：階層型の目次を作る（カテゴリ別）
大量のシートがある場合、目次もごちゃつきがちです。
「カテゴリ別」にグループ化することで、より階層的な目次に仕上げることができます。
例
営業関連 - 顧客別レポート - 月次進捗 経理関連 - 売上集計 - 経費精算
実装方法
- シート名にカテゴリ接頭語をつけておく（例：
【営業】顧客別レポート） - 目次作成時にカテゴリごとに改行やインデントを追加する処理を加える
MidやInStr関数を使えば分類は可能
テクニック④：目次をPDFに出力して紙資料にも対応
目次シートは見栄えを整えれば、そのまま会議用のPDF資料にも使えます。
以下のコードで目次だけをPDF出力することもできます。
Sheets("目次").ExportAsFixedFormat Type:=xlTypePDF, Filename:="目次.pdf"
- 出力先フォルダを指定する場合はパスをフルで書いてください
- ネットワークドライブでもOKです
テクニック⑤：目次を他ブックとリンクさせる
ファイルが複数に分かれている場合でも、ハイパーリンクに「別ブックのパス」を記載すればジャンプできます。
ActiveSheet.Hyperlinks.Add _ Anchor:=Cells(i, 1), _ Address:="C:\Users\username\Documents\別ファイル.xlsx", _ TextToDisplay:="経理月報"
社内で共有されている共通フォルダ構成がある場合などに便利です。
おすすめの活用シーンまとめ
| シーン | 便利なテクニック |
|---|---|
| 自分専用の管理ファイル | 起動時に目次ジャンプ、階層型目次 |
| チーム内共有ファイル | 保存時に自動更新、カテゴリ分け |
| 会議や印刷用途 | PDF出力、見た目の調整 |
| 複数ファイル管理 | 外部ファイルへのリンク化 |
一度作ったVBAでも、こうした工夫を組み合わせることでさらに業務にマッチした便利機能へと進化させることができます。
次章では、VBAを使えない環境でも目次を“それっぽく”作成する裏技をご紹介します。
07.VBAが使えない環境で目次を活用する方法
「会社のセキュリティでマクロが禁止されている」
「共有ファイルでマクロ付きはNGと言われる」
そんな場面でも、あきらめるのは早い！
VBAを使わなくても“それっぽい目次”を作る方法はいくつか存在します。
方法①：関数＋ハイパーリンクで簡易目次を作成
Excelの関数と手動リンクだけでも、十分実用的な目次を作成できます。
ステップ
- A列にシート名一覧を記載（
=CELL("filename",A1)などを駆使して自動化も可能） - 各セルを選択して「挿入」→「リンク」→「このドキュメント内の場所」→該当シートを指定
メリット
- マクロ不要で安心
- フィルタや並べ替えにも対応可能
デメリット
- シート数が多いと手作業の手間が大きくなる
方法②：目次テンプレートを作って使い回す
業務でよく使う構成のファイルがあるなら、あらかじめ目次付きのテンプレートを作っておくのがおすすめです。
- 各シート名に応じたハイパーリンク付き目次を事前に用意
- 新規作成時はそのテンプレートをコピーして使う
利用例
- 月次レポート用テンプレート
- プロジェクト進行テンプレート
- チェックリスト付きファイル
作業の初速が一気に上がります。
方法③：Power Queryを使って動的シート一覧を表示する
Power Queryはデータの取得と整形に使われる強力なツールですが、少し工夫すればシート一覧も取得可能です。
手順
- 「データ」→「データの取得」→「ブックから」
- 自分自身のExcelファイルを指定
- シート一覧を読み込み、整形する
- ハイパーリンク付きの表に整える（手動作業が一部必要）
メリット
- かなり自動化に近づける
- マクロ非対応環境でも利用可能
デメリット
- 初期設定にやや知識が必要
- 更新のたびに「クエリの更新」が必要
方法④：シート間のリンクボタンを使う
簡易的なナビゲーションであれば、図形やボタンに「リンク」を設定することで実現できます。
例
- トップページに「売上管理へ」「顧客一覧へ」などのボタンを配置
- 挿入タブ → 図形 → リンク → シート指定
メリット
- 見た目がリッチで直感的
- マクロ不要
デメリット
- 一括で更新できないため、変更時は手間がかかる
方法⑤：目次をPDFとして社内配布用に活用する
マクロは使えなくても、紙資料やPDF資料として使える目次シートを作っておけば業務でも大活躍。
- シート名＋説明＋ページ数（手動で記入）で構成
- 印刷設定を整えてPDF出力
使用シーン
- 社内会議資料
- 顧客向け提出書類
- 社内マニュアルブック形式
マクロNG環境での選択肢まとめ
| 方法 | 主な特徴 | 難易度 | 自動化度 |
|---|---|---|---|
| 関数＋手動リンク | 一番手軽で導入しやすい | ★☆☆ | △ |
| テンプレ活用 | 業務効率アップに◎ | ★☆☆ | △ |
| Power Query | データ管理と連携しやすい | ★★★ | ○ |
| 図形リンク | ビジュアル重視 | ★★☆ | △ |
| PDF出力 | 印刷＆共有向け | ★☆☆ | × |
「マクロが使えない」＝「目次が作れない」ではありません。
Excelの機能をうまく使いこなせば、ノーVBAでも十分な目次が作れます。
次章では、VBA目次作成でよくあるトラブルとその対処法をチェックしていきましょう。
08.よくあるトラブルと対処法
便利な自動目次VBAですが、運用していくとどうしてもトラブルに遭遇することがあります。
この章では、「あれ？動かないぞ？」を解決するためのチェックリストをまとめました。
トラブル①：目次が正しく作成されない
主な原因
- シート名に使用できない記号が含まれている
- シートが非表示状態（
VeryHidden）になっている - 既存の「目次」シートが保護されている or 編集できない
対処法
- シート名に
[ ] : * ? / \などが使われていないか確認 VeryHiddenのシートはVBAで明示的に表示しないと見えません
→sh.Visible = xlSheetVisibleを加える目次シートが保護されていないか確認（保護解除して再試行）
トラブル②：ハイパーリンクが動作しない
主な原因
- シート名の変更によりリンクが無効になった
- ハイパーリンクの作成時にシート名にスペースが含まれているのに
'（シングルクォート）が無い
対処法
- VBA内のリンク作成部分で、常に
'シート名'!A1の形式にするのが安全
例：SubAddress:="'" & シート名 & "'!A1"
トラブル③：目次シートが勝手に削除された（ように見える）
主な原因
- コード実行時に「既存の目次シートを削除する処理」が走っているため
- シートの名前が「目次」ではなかった（例：「INDEX」など）場合に、新しい「目次」シートに上書きされた
対処法
-
削除処理部分を確認し、必要であればコメントアウトまたは名称を変更
' ActiveWorkbook.Worksheets("目次").Delete
-
シート名を「Index」など他の名前にしたい場合は、コード中の
"目次"をすべて変更する必要あり
トラブル④：シートの順番が変わらない
主な原因
- 並び替え対象から除外されたシートがある
- 並べ替え対象のリストが不完全
対処法
- ソート対象配列に全シートが入っているかチェック
- 並べ替えコード内の条件式にミスがないか確認
トラブル⑤：保存時にエラーが出る or フリーズする
主な原因
Workbook_BeforeSaveイベントに書いた処理が重い- 大量のシートがある or ネットワークドライブで保存している
対処法
Application.ScreenUpdating = FalseとTrueで処理の表示を抑制Workbook_BeforeSave内では最低限の処理にとどめる- 必要なら処理時間を計測して改善
トラブル対処 早見表
| 症状 | 原因 | 解決方法 |
|---|---|---|
| 目次が作成されない | シート名不正・非表示・保護 | シート名チェック・保護解除・Visible設定 |
| リンクが無効 | シート名変更 or スペースあり | 'シート名'!A1の形式に修正 |
| シートが消えた | 自動削除されている | 削除コードの確認と修正 |
| 並べ替えが効かない | 除外シート or コードミス | 条件式の見直し |
| 保存時に固まる | 処理が重い or 通信遅延 | ScreenUpdatingで負荷軽減 |
「動かない！壊れた！」と焦る前に、まずはこの章を参考に一つずつチェックしてみてください。
09.まとめ｜Excel目次作成で業務の見通しが変わる
ここまでお読みいただきありがとうございました！
本記事では、「エクセルで目次を自動作成する方法」を軸に、実務で役立つ以下のポイントを解説してきました。
記事のおさらい
- 目次の役割とメリット
→ 大量のシートを視覚的に整理し、アクセス性を向上 - 手動で作る目次の方法
→ 初心者でも簡単にリンク付きインデックスを作成可能 - VBAを使った自動目次の作成方法
→ ワンクリックで目次＋シート並べ替えまで対応 - VBAのカスタマイズ例
→ 除外・コメント・デザイン調整など業務に最適化 - 活用テクニック
→ 自動ジャンプ、保存時更新、階層型目次などの応用 - マクロが使えない場合の代替手段
→ 関数リンク、テンプレ活用、Power Queryなども紹介 - よくあるトラブルと解決策
→ 動かない・リンク切れ・削除ミスなどを防ぐポイント
目次機能は“Excelを見える化”する最強ツール
目次は、ただのシート名一覧ではなく、Excelブック全体をナビゲートするためのマップです。
特に大量のシートを扱う実務では、目次の有無が作業スピードやチーム内共有の質に直結します。
ぜひあなたの業務にも取り入れてみてください
- シート数が増えて混乱してきた…
- 他人に共有したときに「分かりにくい」と言われた…
- 手作業で目次を作るのに疲れた…
こんな悩みを持つあなたにこそ、今回のVBAテクニックは役立ちます。
小さな自動化が、確かな効率化につながる。
ぜひ一度、試してみてください！
コメント