【Excel】エクセルで目次を自動作成する方法（マクロ・シート一覧）
Excelで複数のシートを使っていると、目的の表へ移動するだけでも時間がかかります。
そこで便利なのが、ワークシート名を一覧化し、クリックすると対象シートへ移動できる目次です。
数式を使ったシート一覧、ハイパーリンクによる移動、VBAマクロによる自動更新を組み合わせると、ブックの管理がぐっと楽になります。
この記事で分かるポイント
・手作業で作るシート一覧とリンク付き目次
・VBAでシート名を自動取得して目次を作る方法
・目次シートを更新するときの注意点
シート数が増えるほど、目次シートは検索時間の短縮と入力ミスの防止に役立ちます。
ここでは、1行目に見出しがある集計表を含むブックを例に、実務で使いやすいExcelの目次作成方法を解説します。
エクセルで目次を自動作成する方法【VBAでシート一覧を生成】
それではまず、VBAを使ってワークシートの一覧を自動作成する方法について解説していきます。
| シート名 | 用途 | 目次から移動 |
|---|---|---|
| 売上集計 | 月別売上の一覧 | リンクをクリック |
| 商品別分析 | 商品単位の集計 | リンクをクリック |
| 担当者別分析 | 担当別の実績確認 | リンクをクリック |
VBAを実行すると、目次という名前のシートを作成し、ブック内のシート名を上から順に並べられます。
目次シート自身は一覧から除外する設定にしておくと、何度実行しても目次という項目が重複しません。
VBAを使う前のブック保存形式
マクロを使う場合は、先にブックをExcelマクロ有効ブック形式で保存しましょう。
通常のxlsx形式ではVBAコードを保存できないため、作成したマクロが失われる可能性があります。
ファイルタブから名前を付けて保存を選び、ファイルの種類でExcelマクロ有効ブックを指定します。
拡張子はxlsmになります。
社内共有する場合は、受け取る人がマクロを有効化できる環境かどうかも確認しておくと安心です。
なお、インターネットから入手したファイルでは、Windowsの保護機能によりマクロがブロックされる場合があります。
信頼できる配布元か確認したうえで、必要に応じてファイルのプロパティからブロックを解除してください。
保存前の確認事項
・元ファイルを残したい場合は、別名でxlsm形式を作成します。
・マクロを含むファイルは、メール添付や共有フォルダでの取り扱いに注意します。
・作業前にバックアップを取ると、誤操作時にも元に戻しやすくなります。
標準モジュールへの目次作成コード
続いて、VBAコードを入力する場所と、目次を生成するコードを確認していきます。
Altキーを押しながらF11キーを押すと、Visual Basic Editorが開きます。
挿入メニューから標準モジュールを選び、表示されたコード画面へ次の内容を貼り付けます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Sub CreateSheetIndex() Dim ws As Worksheet Dim indexWs As Worksheet Dim rowNum As Long Application.DisplayAlerts = False On Error Resume Next Worksheets("目次").Delete On Error GoTo 0 Application.DisplayAlerts = True Set indexWs = Worksheets.Add(Before:=Worksheets(1)) indexWs.Name = "目次" indexWs.Range("A1").Value = "シート一覧" indexWs.Range("A1").Font.Bold = True indexWs.Range("A1").Font.Size = 16 indexWs.Range("A3").Value = "No." indexWs.Range("B3").Value = "シート名" rowNum = 4 For Each ws In Worksheets If ws.Name <> "目次" Then indexWs.Cells(rowNum, 1).Value = rowNum - 3 indexWs.Hyperlinks.Add _ Anchor:=indexWs.Cells(rowNum, 2), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name rowNum = rowNum + 1 End If Next ws indexWs.Columns("A:B").AutoFit End Sub |
このコードは、既存の目次シートを削除してから新しい目次を先頭に作成する仕組みです。
Worksheetsの繰り返し処理で各シートを取得し、B列には対象シートのA1セルへ移動するハイパーリンクを設定しています。
SubAddressにシート名とセル番地を指定することで、外部ファイルを開かずに同一ブック内を移動できます。
マクロの実行と目次シートの確認
続いては、入力したマクロを実行し、リンク付きの目次を確認していきます。
Visual Basic Editor内でカーソルをCreateSheetIndexの中へ置き、F5キーを押すと実行できます。
Excel画面へ戻ると、ブックの左端に目次シートが追加されているはずです。
B列に表示されたシート名をクリックすると、それぞれのシートのA1セルへ移動します。
新しいシートを追加した後は、もう一度マクロを実行するだけで一覧を作り直せます。
ただし、このコードでは既存の目次を作り直すため、目次シートに手入力したメモは消えます。
メモを残したい場合は、目次専用の入力列を別シートへ用意するか、削除せずに更新するコードへ調整しましょう。
【操作のポイント】マクロ実行前に目次シートへ独自の注記を書いている場合は、内容を別の場所へ退避してから実行します。
ハイパーリンクによるシート一覧の作成
続いては、マクロを使わずにリンク付きのシート一覧を作る方法を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| No. | シート名 | 説明 |
| 1 | 売上集計 | 月別売上を確認 |
| 2 | 商品別分析 | 商品別の比較 |
少数のシートだけを管理するなら、ハイパーリンク機能で手作業の目次を作る方法も実用的です。
シート名に加えて用途や更新担当者を記載できるため、共有用ブックでは手作りの目次が分かりやすい場合もあります。
リンク先を指定する基本操作
リンクを設定したいセルを選択し、挿入タブのリンクをクリックします。
キーボード操作なら、Ctrlキーを押しながらKキーを押してもリンクの挿入画面を開けます。
画面の左側でこのドキュメント内を選択すると、現在のブックに含まれるシート名が表示されます。
移動先にしたいシートを選び、セル参照欄にはA1と入力します。
表示文字列には、読者に見せたいシート名や説明を設定してください。
OKを押すと、セルが青字と下線のあるリンク表示に変わります。
リンク先をA1以外にすることも可能です。
たとえば売上集計シートの月別集計表がD5セルから始まるなら、D5を指定すると目的の表を直接開けます。
HYPERLINK関数で作るリンク
続いては、数式でリンクを作成するHYPERLINK関数を確認していきます。
B2セルに売上集計というシート名が入力されている場合、C2セルには次の数式を入力できます。
=HYPERLINK(“#'”&B2&”‘!A1”,B2)
この数式では、B2セルの文字列をシート名として利用し、同時にB2の内容をリンクの表示文字列にも使っています。
シート名に空白や日本語が含まれていても、シート名をシングルクォーテーションで囲む形にすると安定します。
先頭のシャープ記号は、同じブックの内部位置へ移動するリンクであることを示す記号です。
1行目をヘッダーにした一覧表なら、C2へ数式を入れた後、セル右下のフィルハンドルを下へドラッグしてオートフィルします。
各行のB列にシート名を入力するだけで、C列には対応したリンクが完成します。
数式の構成
HYPERLINK関数の第1引数はリンク先です。
第2引数はセル上に表示する文字です。
B2の値を変更すると、表示名とリンク先の両方が変わるため、一覧の修正を一度で済ませられます。
目次から戻るリンクの配置
続いては、各データシートから目次シートへ戻るリンクを付ける方法を確認していきます。
各シートのA1付近に、目次へ戻るという文字列を入力します。
そのセルを選択し、CtrlキーとKキーでリンクの挿入画面を開きます。
このドキュメント内から目次シートを選び、セル参照をA1に指定すれば設定は完了です。
戻るリンクがあると、利用者はシート見出しを右クリックして移動先を探す必要がありません。
複数の人が使う報告書や、月ごとに追加される管理表では特に便利です。
一方で、シート名を変更すると既存リンクが意図した場所を指さなくなる場合があります。
変更後はリンクを数件クリックして、正しく移動できるか確認しましょう。
【操作のポイント】目次へ戻るリンクは、どのシートでも同じ場所に置くと、初めて使う人にも位置が伝わりやすくなります。
シート名を取得するVBAと自動更新
続いては、シート名の取得方法を応用し、更新しやすい目次へ整える方法を確認していきます。
| No. | シート名 | 更新状況 |
|---|---|---|
| 1 | 目次 | 自動作成 |
| 2 | 売上集計 | リンク作成 |
| 3 | 商品別分析 | リンク作成 |
目次を何度も使うブックでは、並び順、書式、更新日時も自動化すると管理しやすくなります。
ここでは、VBAで目次を作成し、実行した日時を記録する例を紹介します。
一覧の見出しと更新日時の記録
まずは、目次の上部にタイトルと更新日時を表示する考え方を確認していきます。
目次を作るたびに更新日時が入ると、一覧がいつ作成されたものか判断しやすくなります。
コード内でRange(“B2”).ValueにNowを代入すると、マクロ実行時の日時を記録できます。
indexWs.Range(“A2”).Value = “更新日時”
indexWs.Range(“B2”).Value = Now
indexWs.Range(“B2”).NumberFormatLocal = “yyyy/mm/dd hh:mm”
Nowは日付と時刻を返すVBAの関数です。
今日の日付だけでよい場合はDateを使えます。
更新日時を表示すると、利用者が古い目次を見ていることに気付きやすくなります。
また、A3行目を見出しにして、4行目から一覧を始めるようにすると、タイトルや更新情報とデータを区別できます。
非表示シートを一覧から除外する条件
続いては、非表示シートを目次へ表示しない条件設定を確認していきます。
補助計算用や設定用のシートまで目次に並ぶと、一般利用者には見づらい場合があります。
その場合は、繰り返し処理の条件にws.Visibleを追加します。
|
1 2 3 4 5 6 7 8 9 |
If ws.Name <> "目次" And ws.Visible = xlSheetVisible Then indexWs.Cells(rowNum, 1).Value = rowNum - 3 indexWs.Hyperlinks.Add _ Anchor:=indexWs.Cells(rowNum, 2), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name rowNum = rowNum + 1 End If |
xlSheetVisibleは、シートが表示状態であるかを調べる定数です。
この条件を入れると、非表示とVeryHiddenのシートは一覧作成の対象外になります。
設定用シートを見せずに、必要な入力シートと集計シートだけを案内したいときに有効です。
ただし、管理者向けの目次では、あえて非表示シートの存在を確認したいケースもあります。
利用目的に合わせて条件を外すかどうか判断しましょう。
シート順と目次順の考え方
続いては、シートタブの順番と目次の並び順について確認していきます。
For Each ws In Worksheetsという書き方では、基本的にシートタブの左から右への順番で処理されます。
そのため、目次を読みやすくしたいなら、先にシートタブを用途順へ並べ替えるのが簡単です。
たとえば入力、集計、分析、印刷用という流れにすると、作業の順序も伝わりやすくなります。
五十音順や月順に並べたい場合は、いったんシート名を配列へ格納し、並べ替える処理が必要になります。
無理に複雑なVBAを書くより、シートタブの並びを整えるほうが保守しやすい場面も少なくありません。
【操作のポイント】目次の並びを変更したいときは、まずシートタブの順番が利用者の作業順になっているか確認します。
数式とテーブルを使った管理用目次
続いては、Excelテーブルと数式を組み合わせた管理用目次を確認していきます。
| No. | シート名 | 分類 | リンク |
|---|---|---|---|
| 1 | 売上集計 | 集計 | 開く |
| 2 | 商品別分析 | 分析 | 開く |
手入力の一覧でも、テーブル機能を使えばフィルターや書式が自動で広がり、目次として見やすくなります。
テーブル化による入力範囲の拡張
まずは、シート名一覧をテーブルに変換する操作について解説していきます。
1行目にNo.、シート名、分類、リンクというヘッダーを入力し、2行目以降にデータを入力します。
一覧内のセルを選択した状態でCtrlキーとTキーを押すと、テーブルの作成画面が開きます。
先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認して、OKを押しましょう。
テーブル化すると、最終行の下へ新しいデータを入力した時点で範囲が自動拡張されます。
月次のシートやプロジェクト用のシートを追加する運用では、テーブル化が特に便利です。
分類列に入力規則を設定しておけば、入力、集計、分析、設定といった区分を統一できます。
フィルターで集計だけを表示することもできるため、シート数が多いブックでも目的の表を探しやすくなります。
連番を作るROW関数
続いては、一覧のNo.列を自動採番するROW関数を確認していきます。
ヘッダーが1行目にあり、データが2行目から始まる場合、A2セルには次の数式を入力できます。
=ROW()-1
ROW関数は、数式が入っているセルの行番号を返します。
A2では行番号が2なので、2から1を引いて連番の1になります。
A3では3から1を引くため2になり、下へコピーすると連番が続きます。
テーブル内であれば、数式を最初のデータ行へ入力するだけで、列全体へ自動適用されることがあります。
途中で行を追加しても番号が更新されやすい点が利点です。
ただし、フィルターで行を隠した場合もROW関数は元の行番号を基準にします。
表示行だけに連番を振る必要があるときは、SUBTOTAL関数など別の方法を検討しましょう。
分類ごとの表示とリンクの活用
続いては、分類列を使って目次の情報量を増やす方法を確認していきます。
シート名だけでは内容を思い出しにくい場合、分類と説明の列を追加すると一覧性が高まります。
たとえば、入力シートにはデータを入力する場所、集計シートには数式を確認する場所、分析シートにはグラフを見る場所という説明を付けられます。
リンク列には、シート名を参照するHYPERLINK関数を設定します。
=HYPERLINK(“#'”&[@シート名]&”‘!A1″,”開く”)
Excelテーブル内では、角かっこを使った構造化参照で同じ行のシート名を取得できます。
リンク文字を開くに固定すると、シート名が長くても表の幅を整えやすくなります。
目次は単なるシート一覧ではなく、ブックの使い方を案内するナビゲーションとして考えると設計しやすくなります。
【操作のポイント】分類列は増やしすぎず、利用者が迷わない三つから五つ程度の区分に絞ると見やすくなります。
目次シートのデザインと運用
続いては、日常的に使いやすい目次シートに整えるデザインと運用を確認していきます。
| 区分 | 表示例 | 目的 |
|---|---|---|
| タイトル | 2026年度 売上管理ブック | ブックの用途を示す |
| 案内 | 青字のリンクをクリック | 操作を伝える |
| 更新情報 | 最終更新日 | 鮮度を確認する |
目次の使いやすさは、リンクの有無だけでなく、初めて開いた人が内容を理解できるかどうかで決まります。
タイトルと操作案内の配置
まずは、目次シート上部に配置したい情報について解説していきます。
A1セルには、売上管理ブックや案件進捗管理表など、ブック全体の目的が分かるタイトルを置きます。
その下には、青い文字をクリックすると各シートへ移動できますという短い操作案内を入れると親切です。
リンクを使い慣れていない人でも、セルをクリックすればよいと理解しやすくなります。
さらに、最終更新日、作成者、問い合わせ先を控えめに記載しておくと、共有ブックの管理にも役立ちます。
目次は最初に開かれるページとして、操作説明を最小限にまとめる場所にもなります。
色分けと条件付き書式
続いては、分類を見やすくする色分けについて確認していきます。
入力用シート、集計用シート、分析用シートを区別したい場合は、分類列に条件付き書式を設定できます。
ホームタブの条件付き書式から新しいルールを選び、特定の文字列を含むセルだけを書式設定するルールを作成します。
入力には淡い黄色、集計には淡い緑、分析には淡い青など、背景色を控えめにすると視認性が上がります。
色だけで意味を伝えず、分類名も必ず文字で表示することが重要です。
印刷した場合や色覚の違いがある場合でも、文字情報があれば内容を判別できます。
強い原色を多用すると目が疲れやすいため、背景色は淡く、文字色は黒を基本にするとよいでしょう。
保護と共有時の注意点
続いては、目次を共有する場合の保護設定と注意点を確認していきます。
目次のリンクや数式を誤って消されないようにしたい場合、シートの保護を利用できます。
ただし、保護をかける前に、利用者が編集する必要があるセルのロックを解除しておく必要があります。
セルの書式設定画面で保護タブを開き、編集を許可するセルだけロックを外します。
その後、校閲タブからシートの保護を設定します。
VBAで目次を作り直す運用では、シート保護がマクロ実行を妨げることがあります。
保護を使うなら、コード側で一時的に保護を解除して再設定する方法も検討しましょう。
【操作のポイント】共有前にはリンク切れ、保護後の編集可否、マクロ実行可否を実際の利用者目線で一度確認します。
まとめ エクセルで目次を自動作成する方法（シート一覧・マクロ）
| 方法 | 向いているケース |
|---|---|
| 手動リンク | シート数が少なく、説明も加えたい場合 |
| HYPERLINK関数 | 一覧表を数式で管理したい場合 |
| VBAマクロ | シート追加や更新が多い場合 |
Excelで目次を自動作成するには、シート数や更新頻度に合わせて、手動リンク、HYPERLINK関数、VBAマクロを使い分けることが大切です。
シート数が少ないブックでは、説明列を付けた手作業の一覧でも十分に役立ちます。
一方、月次資料や案件管理のようにシートが増減するブックでは、VBAでシート一覧を作成すると更新作業を短縮できます。
マクロによる目次作成では、目次シートを除外し、シート名へハイパーリンクを付ける設計が基本になります。
さらに更新日時、分類、目次へ戻るリンクを追加すれば、ブック全体を案内する実用的なトップページになります。
まずは現在使っているExcelファイルで、手動のシート一覧かVBAによる自動目次のどちらが適しているか試してみましょう。
