Excelでワークシート名の一覧をスピル形式で生成する方法(マクロ関数を使用する)

Excelでワークシートの一覧を取得し、スピル形式で表示させる方法について解説します。この方法を使用すると、現在のワークブック内のすべてのシート名を簡単に取得して表示できます。今回は、特殊なマクロ関数と「名前の管理」を用いてこのタスクを達成する方法を紹介します。

推奨する準備

まず、Excelのマクロ有効ブック（.xlsm）を使用して作業することが推奨されます。これは、使用するGET.WORKBOOK関数がExcel 4.0マクロ関数であるためです。ブックを保存する際に、ファイル形式を「Excelマクロ有効ブック（.xlsm）」に設定してください。

通常の「Excel ブック（.xlsx）」でも一時的な利用が可能ですが、ファイルを閉じて開きなおした際は式の再登録が必要になります。

このマクロ有効ブックの保存場所は「トラストセンター・信頼できる場所」へ登録しておく必要があります。登録しておかないと、開きなおしたときにExcel 4.0マクロ関数が有効になりません。

「ファイル・オプション・トラストセンター・トラストセンターの設定・信頼できる場所」から保存場所を登録しておきましょう。

シート名の一覧を取得するための基本的な計算式は以下の通りです。この式に適当な名前を付け、「名前の管理」へ登録します。そして「=名前」という式をワークシートへセットします。

=TEXTAFTER(GET.WORKBOOK(1), "]")

この計算式は、現在のワークブック内のすべてのシート名を横方向にスピル形式で表示します。

シート名の一覧を縦に展開する場合には、TRANSPOSE関数を使用します。

=TRANSPOSE(TEXTAFTER(GET.WORKBOOK(1), "]"))

Excelを開き、新しいワークブックを作成します。
ワークブックを「Excelマクロ有効ブック（.xlsm）」として保存します。
名前の定義を作成するために、Ctrl + F3 を押して「名前の管理」を開きます。
「新規作成」をクリックし、名前に SheetNames と入力し、参照範囲に以下の数式を入力します。入力後はOKし、「名前の管理」も閉じます。
```
=TEXTAFTER(GET.WORKBOOK(1), "]")
```
シート名の一覧を取得するセルに以下の計算式を入力します。
```
=SheetNames
```
シート名の一覧を縦に展開する場合は、次の計算式を使用します。
```
=TRANSPOSE(SheetNames)
```

この手順を実行することで、現在のワークブック内のすべてのシート名がスピル形式で表示されます。

マクロ有効ブックとして保存： GET.WORKBOOK関数を使用するためには、ブックをマクロ有効ブック（.xlsm）として保存する必要があります。
マクロの有効化： 初回保存後、ブックを再度開くときにマクロの有効化を求められることがあります。マクロを有効にしないと、GET.WORKBOOK関数は正しく動作しません。
保存場所の登録：トラストセンターから保存場所を信頼できる場所として登録していないと、開きなおした際に正しく動作しません。下図のように「#BLOCKED!」エラーが表示されます。