Excelでセルの値をシート名にする関数とVBAの使い分け

Excelを活用する際、他のシートやブックのデータを自動的に参照できると作業効率が大幅に向上します。今回は「excel セルの値を シート名にする 関数」というキーワードを軸に、関数を使った方法とVBAを利用する方法を比較しながら紹介します。

関数を使う方法：INDIRECT関数の活用

まずは、Excelの関数を使って、セルの値に基づいたシート名を参照する方法です。INDIRECT関数は、指定した文字列をセル参照として解釈してくれるので、動的なシート参照に非常に便利です。

• 基本構文 =INDIRECT("'[Book2.xlsx]Sheet1'!A1", TRUE)
  ※ 上記の例では、Book2.xlsxのSheet1のA1セルの値を取得しています。
• 動的にシート名やセル位置を変更する方法
  • シート名やセル位置を別のセルに入力しておけば、次のように関数内で文字列を連結できます。 =INDIRECT("'[" & A1 & "]" & B1 & "'!" & C1, TRUE) この場合、A1にブック名、B1にシート名、C1にセル位置を入力することで、参照先を動的に変更可能です。

注意点

• INDIRECT関数は、参照先のブックが開いている状態でのみ正しく機能します。閉じたブックを参照しようとすると、エラー（#REF!）が発生してしまいます。

VBAを使う方法：関数では実現できない処理を可能にする

一方で、VBA（Visual Basic for Applications）を使うと、関数では難しい「閉じたブックからシート名を取得する」や「指定フォルダ内の複数ブックのシート名を一覧で取得する」といった処理が可能です。以下は、その一例として指定フォルダ内のブック名とシート名を取得するVBAコードです。

Sub ListWorkbookAndSheetNames()
    Dim folderPath As String, fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim outputRow As Long

    ' 指定フォルダのパスを設定（末尾に「\」を付ける）
    folderPath = "C:\YourFolder\"
    
    ' 出力先（例としてSheet1に出力）
    outputRow = 2
    Sheets("Sheet1").Cells(1, 1).Value = "Workbook Name"
    Sheets("Sheet1").Cells(1, 2).Value = "Sheet Name"
    
    ' フォルダ内のxlsxファイルを順次取得
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        ' 読み取り専用でブックを開く
        Set wb = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
        For Each ws In wb.Sheets
            Sheets("Sheet1").Cells(outputRow, 1).Value = fileName
            Sheets("Sheet1").Cells(outputRow, 2).Value = ws.Name
            outputRow = outputRow + 1
        Next ws
        wb.Close False
        fileName = Dir
    Loop
End Sub

このコードは、以下の流れで動作します。

• フォルダ内のExcelファイルを列挙
  Dir 関数を使用して指定したフォルダ内の.xlsxファイルを取得します。
• 各ブックを読み取り専用で開く
  ファイルを誤って編集しないように、読み取り専用で開きます。
• 各シート名の取得と出力
  開いたブック内のすべてのシート名をループ処理で取得し、出力シートに一覧表示します。

ポイント

• VBAを使用することで、INDIRECT関数ではできない「閉じたブックからのデータ取得」や「複数ブックの一括処理」が可能となります。
• ただし、VBAはセキュリティ設定でマクロの実行が許可されている必要があるため、導入時には注意が必要です。

関数とVBAの比較

• 関数（INDIRECT）のメリット
  • シンプルにセルの値を利用して動的に参照先を指定できる。
  • 他ブックが開いている場合は即座に値を取得可能。
• 関数のデメリット
  • 他ブックが閉じているとエラーになる。
  • 複雑な処理や一括操作には向かない。
• VBAのメリット
  • 閉じたブックからのシート名取得や複数ブックの一括処理が可能。
  • 自動化の幅が広がり、柔軟な処理が実現できる。
• VBAのデメリット
  • マクロのセキュリティ設定や実行環境の整備が必要。
  • 初心者にはややハードルが高いかもしれない。

まとめ

INDIRECT関数を使えば、「シート名」取得の目的は十分に達成可能ですが、閉じたブックや複数ファイルを操作する場合は、VBAの導入が効果的です。どちらの方法にも一長一短があるため、目的や環境に応じて最適な方法を選んでください。

Tamaglo

最後までお読みいただきありがとうございました。