エクセルで、シート名・ファイル名をセルに表示してみよう！セルの値をシート名にする方法も紹介！

Excel
2025.02.26

Excelで作業中、「このシート名、また打つの？」と思ったことありませんか？
報告書や資料を作っていると、タイトルや見出しにシート名を転記する場面って意外と多いですよね。
でも、実はそれ！数式ひとつで自動化できちゃうんです！
今回は数式で自動的にシート名を取得できる便利な数式を紹介します！

また同じ内容のシートを複数コピーするとき、一つずつシート名を変更するのは面倒ですよね。。こちらもVBAで一発解決する方法を紹介します！

数式やVBAを使い、作業効率をアップさせちゃいましょう！

エクセルでセルにファイル名やシート名を表示させるには？

　エクセルで作業をしていると、ファイル名やシート名を表示させたいことがありますよね。特に、複数のシートやファイルを扱っているときに、いちいちシート名を入力するのは大変ですよね。。そんなお悩みに、この記事では、エクセルのセルに「ファイル名」や「シート名」を表示させる便利な方法を紹介します！

セルにシート名を表示させる方法

　シート名をセルに表示させるには、CELL関数を使う方法が簡単です。以下数式を使用することで、セルにシート名を表示させることができます！

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

　エクセルに上記数式をコピペすればシート名が表示されますが、どうやってシート名を表示しているかを解説します！

■シート名を表示する流れ

　・CELL関数を使い、ファイルパスを取得する
　　↓
　・LEN関数でファイルパスの文字数を取得する
　　↓　
　・FIND関数で「]」の開始位置を取得し、ファイルパスの総文字数から引く（＝シート名の文字数）
　　※ファイル名は「[]」記号で囲われるため、「]」以降の文字列がシート名になる
　　↓
　・RIGHT関数で、右側からシート名の文字数分の文字列を取得する

　文字だと分かりづらいので、実際にエクセルで表現してみました！今回はCドライブ直下「テスト」フォルダに「シート名ファイル名を表示」というエクセルファイルを作成し、そのシート名を取得しました。

参考までに、各関数がどういうものかを記載してみました！

★CELL関数
　→セルに関する情報を返す関数。
　=CELL(検査の種類,[対象範囲] '対象範囲は省略可

★LEN関数
　→文字列の文字数を返す関数。
　=LEN(文字列)

★FIND関数
　→ 対象の文字列から、検索する文字列の開始位置を返す関数
　=FIND(検索文字列,対象,[開始位置]) "開始位置は省略可

★RIGHT関数
　→文字列の右端から指定した文字数の文字を返す関数
　=RIGHT(文字列,[文字数])

セルにファイル名を表示させる方法

数式が少し長くなってしまいますが、MID関数を組み合わせることにより、ファイル名をセルに表示させることもできます！

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("[",CELL("filename",A1))-(LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))+1))

こちらもどうやってファイル名を表示しているかを解説します！

■ファイル名を表示する流れ

　・CELL関数を使い、ファイルパスを取得する
　　↓
　・FIND関数で「[」から+1の開始位置を取得する
　　↓　
　・ファイルパスの総文字数から「[」までの文字数と、「]」から+1の文字数を引く（＝ファイル名の文字数）
　　↓
　・MID関数で「[」から+1の開始位置より、ファイル名の文字数分の文字列を取得する

　文字だとやっぱり分かりづらいですね。。こちらも同様に実際にエクセルで表現してみました！先ほどと同じファイルを使用し、ファイル名を取得しました。


★MID関数
　→指定した文字列の位置から、指定した文字数の文字を返す関数。
　=MID(文字列,開始位置,文字数)

活用例

・1ファイルで同じ書式のシートを複数拠点分作成しているケース（店舗や都道府県など）
　→シートを追加したタイミングでシート名を変更すれば、セルに表示される文字列も変わるため、入力ミスを防げたり、手間を減らしたりすることができます！

ぜひ、これらの関数を活用して作業効率をアップさせてください！

＜追記＞1シートに全シート名を表示させる方法

上記ではシート名を取得する関数を紹介しましたが、以下ではエクセルVBAで全シート名を取得できるVBAコードを紹介します！シートの抜け漏れがないか確認できたり、ファイル整理に役立たせることが期待できます。

今回は、下のファイルにあるシート名を「シート名一覧」シートのA列に記載します。

Sub シート名を取得()

    Dim ws As Worksheet
    Dim i As Integer
    Dim targetSheet As Worksheet
    
    ' シート名を表示するシートを設定（今回はシート名一覧シートに各シート名を取得します）
    Set targetSheet = ThisWorkbook.Sheets("シート名一覧")
    
    ' 出力開始位置（セルA1より出力）
    i = 1
    
    ' シート名をリスト表示
    For Each ws In ThisWorkbook.Sheets
        targetSheet.Cells(i, 1).Value = ws.Name
        i = i + 1
    Next ws

End Sub

こちらのVBAコードを実行すると、全シート名を取得することができました！

＜追記＞セルに表示した値をシート名に反映するには？

例えば同じ内容のシートを使うんだけど、複数シートをコピーして、一つずつシート名を変更するは面倒ですよね。。
実はVBAを使うと、セルに表示したいシート名を入力すれば、シートコピーとシート名変更を一括で行うことができます！

作業効率アップすること間違いなし！今回はその方法も紹介します！

コピーしたいシートを準備する

まずはコピーしたいシートを準備します。
こちらではわかりやすく、「テンプレート」シートを作成し、こちらをコピーしていきます。
（セルC4には、シート名を反映する数式を入力しています）

シート名としたい値をセルに入力する

コピーしたシートの名前にする値をセルに入力します。
今回は「参照」シートのセルA1～A13に、都道府県の一部を入力しましたので、こちらの名前でシートを作成していきます！

シートをコピー&シート名変更のVBAを作成する

最後にシートをコピー＆シート名を変更するVBAを作成します。
今回はシート名・参照先を合わせたVBAを記載します。実際に使用するときは、使用するシート名や参照範囲に編集してご使用ください！

Sub テンプレートからシートを作成()

    Dim rng As Range
    Dim cell As Range
    Dim sheetName As String
    Dim templateSheet As Worksheet
    Dim ws As Worksheet
    Dim sheetExists As Boolean
    
    ' テンプレートのシート名
    Const TEMPLATE_SHEET_NAME As String = "テンプレート"
    
    ' コピー元シートの存在チェック
    On Error Resume Next
    Set templateSheet = ThisWorkbook.Sheets(TEMPLATE_SHEET_NAME)
    On Error GoTo 0
    
    '「テンプレート」シートがなければ、注意メッセージを表示
    If templateSheet Is Nothing Then
        MsgBox "テンプレートシート '" & TEMPLATE_SHEET_NAME & "' が見つかりません。", vbCritical
        Exit Sub
    End If
    
    '名前一覧が入力されているセル範囲を指定（シート名、参照範囲は必要に応じて変更）
    Set rng = ThisWorkbook.Sheets("参照").Range("A1:A13")
    
    For Each cell In rng
        sheetName = Trim(cell.Value)
        
        If sheetName <> "" Then
            
            '同名シートが既に存在するか確認、あればそのシート名はスキップ
            sheetExists = False
            For Each ws In ThisWorkbook.Sheets
                If ws.Name = sheetName Then
                    sheetExists = True
                    Exit For
                End If
            Next ws
            
            'シートが存在しなければ、テンプレートをコピーして名前を変更
            '同じシート名があれば、注意メッセージを表示
            If Not sheetExists Then
                templateSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                ActiveSheet.Name = sheetName
            Else
                MsgBox "すでに存在するシートはスキップされました：" & sheetName, vbInformation
            End If
            
        End If

    Next cell
    
    MsgBox "シート作成が完了しました"
    
End Sub

実行すると、テンプレートシートをコピーし、指定したセルのシート名に変更されました！

VBAを使用すると、今まで手作業でやっていたことが自動化でき、とても便利ですね！

今回の内容が少しでも参考になれば幸いです。
ご覧いただき、ありがとうございました！

コメント

タイトルとURLをコピーしました