Excel 関数でシート名を取得する（コピペで使える）【マクロ不要】

「CELL」関数を使います。
細かい中身は分からなくても大丈夫です。

コピペすればそのまま使えます。

シート名を表示する式

以下です。（そのまま使えます。）

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

関数フィールドにコピペしてください。
後述する注意点のとおり、ブックを保存していないと値が返りません。

やっていること

CELL関数によって取得したパス、ファイル名、シート名の文字列から、シート名部分を切り出しています。

CELL関数

CELL関数で「filename」を指定して、保存されているフォルダー名、ファイル名（ブック名）、シート名を含むフルパスを文字列として返してくれます。
「参照」として指定する第2引数は任意指定なので、何も書かなくても大丈夫ですし、どこのセル番号を書いても大丈夫です。

CELL("filename")

CELL("filename",A1)

返される具体的な値は以下のようなかたちです。

\\C:\fileパス名\[ファイル名.xlsx]シート名

関数の説明は以下です。

セルの書式、位置、または内容に関する情報

シート名部分を切り出し（RIGHT関数）

RIGHT関数で、フルパス（前項のとおり）の右側にある「シート名」を切り出します。
フルパスの全体文字数から「]」の位置までの文字数を引いて、第2引数として切り出し文字数にします。

どうしても冗長な指定になってしまいますが、一度指定すればコピペで使いまわせます。
よくやるやつです。

注意点

ブックを保存してから、この式を使ってください。
保存していないと、空白文字列になってしまいます。

また、以下のExcelでは利用できません。

• Web版
• Excel Mobile
• Excel Starter

加えて、ファイル名やシート名に「]」が含まれていると意図通りに動かないです。
名称変更するか、いったん切り出した文字列に対して、あらためて関数処理するか、等の対策が考えられます。