シート名をタイトルとしてセルに入力したいときなどに、いちいちコピペするのはめんどくさいので、数式を使ってシート名を取得する方法を説明します。
シート名の取得する数式
いきなり答えを書いてしまうと、下記の数式をコピペしてもらえばシート名を取得できます。
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,260)
もし、今いるシートではなく別シートのシート名を取得したい場合は、A1としている2か所を"別シート名"!A1と書き換えます。
数式の中で何をやっているのか気になる方だけ、続きを読んでみてください。
数式の解説
シート名を取得する数式の中で使っている関数について、順を追って説明します。
CELL 関数
=CELL("{検索タイプ}", {検索対象のセル番号})
CELL 関数は、第2引数で指定したセルについて、第1引数で指定した情報を返す関数です。
第1引数に"filename"を指定した場合、第2引数に指定したセルが存在するシート名を含めたエクセルファイルのフルパスが得られます。
たとえば、
=CELL("filename",A1)
と入力すると、下記例のような結果が表示されます。
C:\workspace\[Sample.xlsx]Sheet1
第2引数は、名前を取得したいシート内のセルであればどれを参照しても問題ありませんので、ここでは適当にA1を参照しています。
FIND 関数
=FIND("{検索する文字}", "{検索対象の文字列}")
FIND 関数は、第1引数で指定した文字が、第2引数で指定した文字列の何文字目にあったかを返す関数です。
たとえば、CELL 関数の説明で求めた文字列を使って、
=FIND("]", "C:\workspace\[Sample.xlsx]Sheet1")
と入力すると、下記のように]がある文字数（26文字目）が表示されます。
26
MID 関数
=MID("{元の文字列}", {抜き出し開始文字数}, {抜き出し終了文字数})
MID 関数は、第1引数で指定した文字列のうち、第2引数で指定した開始文字数～第3引数で指定した終了文字数までの文字列を抜き出す関数です。
たとえば、CELL 関数の説明で求めた文字列と、FIND 関数の説明で求めた文字数を使って、
=MID("C:\workspace\[Sample.xlsx]Sheet1", (26+1), 260)
と入力すると、下記のように]の1文字うしろ、つまり27文字目からうしろの文字列が抜き出されます。
Sheet1
このとき、第3引数の文字数が元の文字列の文字数より多い場合は、元の文字列の最後まで抜き出されます。
ここでは元の文字列の最後まで抜き出したいので、とりあえず大きめの数字としています。 （第3引数の数字は十分に大きければ何でもよいですが、Windows でいくつかある絶対パス最大文字数のひとつである「260」としています）
【補足】LET 関数を使った別の書き方
シート名を取得する数式は、Excel 2021 以降のバージョンであれば LET 関数を使用して下記のようにも書き換えられます。
=LET(name, CELL("filename", A1), MID(name, FIND("]", name)+1, 260))
LET 関数とは、指定した数式に名前をつけることで、その名前を使って別の数式を記述できるようにする関数です。
CELL("filename", A1)という部分がくり返し2回登場してますので、nameという名前に置き換えています。
今回の例ではくり返し回数が少ないのであまりメリットを感じませんが、長い関数が何度もくり返し出てくる数式を書くときに便利な関数です。
