メインコンテンツへスキップ

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

著者: テクニカルサポート 最終更新日:2023年11月17日

複数のワークシートを含むブックがあり、現在のブック内のすべてのシート名を一覧表示したい場合、Excelでこれらのシート名の一覧をXNUMXつずつ入力せずに作成する簡単な方法はありますか? このチュートリアルでは、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)),"")

Note:上記の式では、 シート名 手順3でクレストする範囲名です。

5。 次に、空白のセルが表示されたら、塗りつぶしハンドルをセルまでドラッグします。これで、現在のブックのすべてのシート名が次のスクリーンショットのように一覧表示されます。

6。 各シートのハイパーリンクを作成する場合は、次の式を使用してください。

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

Note:上記の式では、 A2 シート名を含むセルであり、 A1 アクティブセルを配置するセルです。 たとえば、ハイパーリンクテキストをクリックすると、シートのセルA1が検索されます。

7。 ハイパーリンクテキストをクリックすると、そのシートに移動します。以下のデモを参照してください。

ヒント:
  • 1.上記の式では、作成されたシート名が動的に一覧表示されます。ワークブックでシート名を変更すると、インデックスシート名が自動的に更新されます。
  • 2.ファイルを次のように保存する必要があります Excelマクロが有効なブック ファイルを閉じて再度開いた後に数式を適切に機能させる場合は、形式。

便利な機能を備えたワークブックからすべてのワークシート名のリストを取得します

シート名のリストを作成する サードパーティのアドインのユーティリティ Kutools for Excel、ワンクリックでワークシート名のリストを作成し、ハイパーリンクで各ワークシートにリンクすることができます。

注:これを適用する シート名のリストを作成する、まず、ダウンロードする必要があります Kutools for Excel、次に機能をすばやく簡単に適用します。

インストールした後 Kutools for Excel、次のようにしてください。

1. クリック クツールズプラス > ワークシート > シート名のリストを作成する、スクリーンショットを参照してください:

2。 の中に シート名のリストを作成する ダイアログボックスで、次の設定を指定してください。

(1.)選択 シートインデックススタイル 必要に応じて、ハイパーリンクまたはマクロボタンを使用してワークシート名を作成できます。

(2.)シートインデックスのワークシート名を入力します。

(3.)ワークシートインデックスの場所を指定します。

(4.)ワークシート名を表示するために新しいワークシートで使用する列の数を指定します。

3. 設定が終わったら、 OK。 すべてのワークシート名は、現在のワークブックの新しいワークシートにリンク付きでリストされています。 スクリーンショットを参照してください:

ハイパーリンク付きのワークシート名 マクロボタン付きのワークシート名
先端: この機能を使用するには、以下をインストールする必要があります Kutools for Excel まずはお願いします クリックしてダウンロードし、30 日間の無料トライアルをご利用ください 今。

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

1。 を押し続けます Alt + F11 キー、そしてそれは開きます アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のマクロをに貼り付けます モジュール 窓。

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 という新しいワークシートにリストされ、シート名も各シートにリンクされました。スクリーンショットを参照してください。

Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
First, thanks to the author. And adding the Czech version.

tp 3. =O.KNIZE(1)&T(NYNÍ())
tp 4. =IFERROR(INDEX(ČÁST(nazvylistu;NAJÍT("]";nazvylistu)+1;255);ŘÁDKY($A$2:A4));"")
This comment was minimized by the moderator on the site
Hello,

je viens de tester la méthode via macros (dynamique avec formules) et ça marche au poil donc je tiens vraiment à remercier l'auteur de cet article parce que ça va vraiment m'aider dans mon travail ! Juste, je me permet de corriger les formules pour la version française. Et alors je ne sais pas si c'est parce que je suis sous la version 2019 mais Excel rouspète quand il n'y a pas d'argument en 3ème position de la fonction STXT ("MID" en version anglaise) donc obligé d'en rajouter un. Donc voilà ce que ça donne :

=LIRE.CLASSEUR(1)&T(MAINTENANT())

=SIERREUR(INDEX(STXT(nomsFeuilles;TROUVE("]";nomsFeuilles)+1,255;20);LIGNES($A$2:A2));"")

Bon travail à tous ! ;-)
This comment was minimized by the moderator on the site
Hello, Gizmil
Thank you for your comment, there are some functions are only available for English in Excel.
Your formula may help others.
Thanks again!
This comment was minimized by the moderator on the site
I close and open my document and all values in my sheet names column are all gone and blank but still the formula is there. I tried entering the same formula but it doesn't show the value anymore
This comment was minimized by the moderator on the site
Hello, Anne,
Sorrry for replying late, after creating the range names and formulas, you should save the workbook as Excel Macro-Enabled Workbook format, so next time, when you open the Excel file,the formulas can work well.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
I tried this one and it works. But when I close and open the file again all the values in my sheet names are blank and gone but the formula is still there. I tried enteing the same formula again but it doesn't show the value anymore
This comment was minimized by the moderator on the site
BRILLIANT!! Thank you so much! 😊
This comment was minimized by the moderator on the site
Causes problems when document protection is enabled by email or corporate policy
This comment was minimized by the moderator on the site
Thanks so much, this worked great.
This comment was minimized by the moderator on the site
Works great!! Thank you!!!!
This comment was minimized by the moderator on the site
sooooo helpful, works as expected!!!!! Thanks
This comment was minimized by the moderator on the site
THANK YOU SO MUCH! I freaking love your website. In a matter of minutes I've had a ton of time saved with two sections of this site including this one. Love it!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations