ワークブックからすべてのワークシート名のリストを作成するにはどうすればよいですか？

複数のワークシートを持つワークブックがあり、すべてのシート名を自動的にリスト化し、それぞれのシートにナビゲートできるクリック可能なハイパーリンクを追加したいとします。このチュートリアルでは、Excelでワークシート名のリストを生成するための迅速かつ効果的な方法を紹介します。

数式を使用して、ワークブックからすべてのワークシート名を動的に取得する

Kutools for Excelを使用して、ワークブックからすべてのワークシート名を取得する

VBAコードを使用して、ワークブックからすべてのワークシート名を取得する

数式を使用して、ワークブックからすべてのワークシート名を動的に取得する

Excelでは、範囲名を定義し、その後、現在のワークブック内のすべてのシート名をリスト化するために数式を適用できます。次の手順に従ってください：

1. 「数式」＞「名前」をクリックしてください。スクリーンショットをご覧ください：

2. 「名前」ダイアログボックスで、「新規」ボタンをクリックしてください。スクリーンショットをご覧ください：

3. 表示された「新しい名前」ダイアログで、「名前」テキストボックスに名前を指定し、以下の数式を「参照先」テキストボックスにコピーしてください。スクリーンショットをご覧ください：

=GET.WORKBOOK(1)&T(NOW())

4. その後、「OK」＞「閉じる」をクリックしてダイアログボックスを閉じます。次に、すべてのシート名をリスト化したいシートに移動し、空のセルに次の数式を入力してください：

=IFERROR(INDEX(MID(Sheetnames,FIND("]",Sheetnames)+1,255),ROWS($A$2:A2)),"")

注: 上記の数式において、「Sheetnames」はステップ3で作成した範囲名です。

5. 次に、空白セルが表示されるまでフィルハンドルを下にドラッグすると、現在のワークブック内のすべてのシート名が以下のようにリストされます。スクリーンショットをご覧ください：

6. 各シートにハイパーリンクを作成したい場合は、次の数式を使用してください：

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

注: 上記の数式において、「A2」はシート名が含まれているセルであり、「A1」はアクティブなセルを配置したい場所です。たとえば、ハイパーリンクのテキストをクリックすると、そのシートのセルA1に移動します。

7. これで、ハイパーリンクのテキストをクリックすると、そのシートに移動します。以下のデモをご覧ください：

Kutools for Excelを使用して、ワークブックからすべてのワークシート名を取得する

サードパーティ製アドイン「Kutools for Excel」の「リンクテーブルの作成」機能を使用すると、ワンクリックでワークシート名のリストを作成でき、各ワークシートにハイパーリンクを設定できます。

1. 「Kutools Plus」＞「ワークシート」＞「リンクテーブルの作成」をクリックしてください。スクリーンショットをご覧ください：

2. 「リンクテーブルの作成」ダイアログボックスで、次の設定を行ってください：

(1.) 好みの「スタイル」を選択してください。ワークシート名をハイパーリンク付きまたはマクロボタン付きで作成できます。 (2.) シートインデックス用のワークシート名を入力します。 (3.) ワークシートインデックスの位置を指定します。 (4.) 新しいワークシートでワークシート名を表示するために使用する列数を指定します。

3. 設定が完了したら、「OK」をクリックします。現在のワークブックの新しいワークシートに、すべてのワークシート名がリンク付きでリストされます。スクリーンショットをご覧ください：

ハイパーリンク付きのワークシート名	マクロボタン付きのワークシート名

VBAコードを使用して、ワークブックからすべてのワークシート名を取得する

1. 「ALT」＋「F11」キーを押すと、「Microsoft Visual Basic for Applications」ウィンドウが開きます。

2. 「挿入」＞「モジュール」をクリックし、以下のマクロを「モジュール」ウィンドウに貼り付けます。

VBA: 新しいワークシートにすべてのワークシート名をハイパーリンク付きでリスト化する：

Sub CreateIndex()
'updateby Extendoffice
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. 「F5」キーを押してこのマクロを実行します。アクティブなワークブックのすべてのワークシート名が、「Index」という新しいワークシートにハイパーリンク付きでリストされます。それぞれのシートにナビゲートできます。以下のスクリーンショットをご覧ください：

関連記事