スピル機能を使ったワークシート名の一覧を表示させるユーザー定義関数

スピル機能を使ったワークシート名の一覧を表示させるユーザー定義関数

Excelにはワークシート名の一覧を取得する組み込み関数が存在しないため、VBA（Visual Basic for Applications）を利用してユーザー定義関数を作成することで、この問題を解決できます。本記事では、スピル機能を利用してワークシート名の一覧を表示するユーザー定義関数の作成方法と使用例について解説します。なお、スピル機能はExcel 2021、Microsoft 365以降のバージョンで導入されています。それ以前のバージョンでは使用できません。

ユーザー定義関数の作成方法

以下の手順で、ワークシート名の一覧を表示するユーザー定義関数「ListSheets」を作成します。

  1. Excelを開き、Alt + F11キーを押してVBAエディタを起動します。
  2. 左上の「挿入」メニューから「標準モジュール」を選択し、新しいモジュールを追加します。
  3. 新しいモジュールのコードウィンドウに、以下のVBAコードを貼り付けます。
    Function ListSheets() As Variant
    Dim ws As Worksheet
    Dim i As Integer
    Dim wsArray() As String

    Application.Volatile
    ReDim wsArray(1 To ThisWorkbook.Worksheets.Count)

    i = 1
    For Each ws In ThisWorkbook.Worksheets
        wsArray(i) = ws.Name
        i = i + 1
    Next ws

    ListSheets = Application.WorksheetFunction.Transpose(wsArray)
End Function
  4. Ctrl + Sキーを押してVBAプロジェクトを保存し、VBAエディタを閉じます。

関数の使い方とサンプル

作成したユーザー定義関数「ListSheets」は、Excelのシート内で=ListSheets()と入力することで、現在のブック内のすべてのワークシート名が一覧で表示されます。スピル機能を活用して、一覧は自動的に下方向に展開されます。関数を入力するセルの下に十分な空きスペースがあることを確認してください。

例えば、以下のようなワークシート名が存在するブックがあるとします。

  • Sheet1
  • Sheet2
  • Sheet3
  • Data
  • Summary

A1セルに=ListSheets()関数を入力すると、ワークシート名が以下のように一覧表示されます。

A1: Sheet1
A2: Sheet2
A3: Sheet3
A4: Data
A5: Summary

この関数は、現在開いているブック内のワークシート名を取得します。他のブックのワークシート名を取得する場合は、VBAコード内のThisWorkbookを適切なブックオブジェクトに変更してください。

まとめ

本記事では、スピル機能を使ってワークシート名の一覧を表示するユーザー定義関数「ListSheets」の作成方法と使用例を解説しました。この関数は、ワークシート名を一覧で表示するための簡単な方法を提供し、ブック内のワークシートを素早く参照したい場合に非常に便利です。是非ともお試しください。

コメント