業務日報を作成するときなど、データ内にシート名を表示させたいことはありませんか？実は関数やVBAを使うことで、簡単にシート名を取得することが可能です。また、シート名の取得ができればセルに表示できるほか、他のシートやファイルに参照したり、日付や条件に応じて自動更新したりと、便利な使い方が多くあります。
この記事では、Excelでシート名を取得する方法と、その活用テクニックを紹介します。
Excelのシート名をSUBSTITUTE関数で取得する方法
まずは、SUBSTITUTE関数を使って、Excelのシート名を取得する方法を紹介します。比較的簡単な手順なので、コピペをするなどして活用してください。
【1】まずエクセルシートの数式タブを開き、「名前の定義」をクリックします。
【2】続いて、名前(N)に「book」を、参照範囲(R)には「=GET.WORKBOOK(1)」と入力して、「OK」をクリックします。
【3】再度「名前の定義」をクリックし、今回は名前(N)には「doc」を、参照範囲(R)には「=GET.DOCUMENT(88)」と入力して「OK」をクリックします。
【4】セルに「=SUBSTITUTE(INDEX(book,1),”[“&doc&”]”,””)」と入力すると一番左のシート名が表示されます。うまく反映されない場合は手動で一度更新すると、反映されることがあります。
「名前の定義」を使用したため、保存時に上記画像のようなメッセージが出てきます。メッセージに従って、マクロ有効ブックで保存してください。
【コピペ用】シート名を自動取得する関数
シート名を自動取得するための関数は以下の通りです。
=SUBSTITUTE(INDEX(book,1),”[“&doc&”]”,””)
Excelのシート名を関数を使用して自動取得する方法
上記の関数は便利ですが、一つ問題があります。それは、シート名がうまく反映されない際に、手動で更新する必要があるケースが存在すること。少々、面倒だと感じる方もいるのでは。
そこで、別の関数を使ってシート名を自動取得する方法もセットで紹介します。後半の数式については少し長めなため、少し混乱することもあるでしょう。コピペをするなどして活用してください。
【1】今回はB2セルにシート名である「１月」を表示させるようにします
【2】B2セルに「=CELL(“filename”,B2)」と入力してEnterキーを押すと、シート名以外にも、ファイルパスなどがすべて表示されます
【3】先ほどの数式に少し手を加えて、シート名の部分だけを表示指せるようにすれば、「１月」とセルに表示されます
【3】のようにシート名のみを表示させるようにするには、以下のような数式に変更する必要があります。
=RIGHT(CELL(“filename”,B2),LEN(CELL(“filename”,B2))-FIND(“]”,CELL(“filename”,B2)))
カッコの位置に注意して入力しましょう。場所を間違った場合エラーになります。なお、数式の構成としては、以下のようになっています。関数に興味がある場合は参考までに。
1. シート名とファイルパスをすべて取得（CELL関数）
2. シート名とファイルパスの文字数を取得（LEN関数）
3. ]の記号が何文字目にあるかを確認する（FIND関数）
4. 2.と3.の差を計算する（今回は112-110＝2という結果に）
5. シート名とファイルパスの文字列の右から2文字だけ表示させる（RIGHT関数）
【コピペ用】シート名取得の関数
シート名取得のための関数は以下の通りです。
=RIGHT(CELL(“filename”,B2),LEN(CELL(“filename”,B2))-FIND(“]”,CELL(“filename”,B2)))
この関数では、「CELL関数」でシート名とファイルパスを取得しています。また、「LEN関数」で文字数を取得、「FIND関数」で”]”が何番目にあるかを探しています。「RIGHT関数」では、ここまでの式を用いることで、シート名だけを抽出しています
Excelの複数シート名を「VBA」で一括取得する方法
VBA（マクロ）では複数のシート名を一括で取得することが可能です。
今回のVBAは比較的簡単に実装可能なので、VBAをまだ一度も使ったことがない人でもぜひチャレンジしてみてください。
【1】VBAを起動するため、Alt + F11キーを押すか、開発タブからVisual Basicをクリックします。表示のタブの中にイミディエイトウィンドウがあるので、クリックします。
【2】上の画像のように、画面下にウィンドウが表示されます。ここに「For Each i In ThisWorkbook.Sheets: debug.print i.name : next i」をコピー＆ペーストし、Enterキーを押すと、下にシート名の一覧が表示されます。
【3】表示されたシート名一覧をコピーし、セルに貼り付けすれば完了です。
【コピペ用】シート名取得のVBA
シート名を取得するためのVBAは以下の通りです。
For Each i In ThisWorkbook.Sheets: debug.print i.name : next i
【応用編】Excelのシート名取得を活用したテクニック
Excelのシート名取得は、単にセルに表示するだけでなく、さまざまなテクニックに応用できます。ここでは、シート名取得を活用した二つのテクニックを紹介します。
シート名を使って日付更新
Excelのシート名を日付にすると、そのシートの内容がその日付に関するものだと一目でわかるようになります。たとえば、売上や在庫などのデータを日別に管理する場合などに便利です。
【1】まずは、シート名を日付に変更します。ここではシート名を月日の数字の羅列（例：0101）とします。
【2】続いて、日付を表示したいセルを選択し、「=TEXT(2023&RIGHT(CELL(“filename”,B2),LEN(CELL(“filename”,B2))-FIND(“]”,CELL(“filename”,B2))),”0000!/00!/00″)」と入力すると、2023/1/1と表示されました。
Indirect関数と「データの入力規則」でシート名をセル参照で指定
Excelのシート名取得は、Indirect関数と「データの入力規則」を使って、セル参照で指定できます。Indirect関数は、文字列で指定したセルや範囲の値を返す関数でのこと。「データの入力規則」は、セルに入力できる値や形式を制限する機能のことです。
これらを組み合わせると、あらかじめリスト化されたシート名をドロップダウンリストで選択して、それを基にINDIRECT関数を使って特定のシートの特定のセルの値を取得するという動きが可能です。
この方法は、以下のような手順で行います。
【1】セルに「シート名」を入力します。つまり、注意点としてこの方法では事前に「シート名」をまとめておく必要があります。
【2】ドロップダウンリストを作成したいセルを選択し、「データ」タブを開き、「データの入力規則」をクリックします。
【3】「設定」タブを開き、「入力値の種類(A)」から「リスト」を選択、「元の値(S)」に「=(シート名を入れた範囲)」を入力します。設定が完了したら「OK」をクリックします。
【4】結果を表示させたいセルを選択し、「=INDIRECT(C2&”!C10″)」と入力します。(指定したシートから取得したい値が『セルC10』にある場合）
【5】セルC2のリストからシート名を選択すると、参照先のシートの指定したセルの値が表示されます。
まとめ
Excelでシート名を取得する方法と、その活用テクニックを紹介しました。シート名取得は、関数やVBAを使って簡単にできます。シート名取得を活用すると、セルに表示したり、他のシートやファイルに参照したり、日付や条件に応じて自動更新したりと、便利な使い方がたくさんあります。ぜひ、試してみてください。
