Excelのシート名をセルに表示させる方法・しくみ・注意点

Excelのシート名をセルに表示するには関数を使う方法があります。
ちょっと複雑ですが、そのしくみを解説します。
(もちろん、そのまま貼り付ければすぐ使えます。)
セルにシート名を表示

シート名をセルに表示する数式・関数

シート名をセルに表示する場合、こういう数式を使います。

=MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,31)

次の点に注意してください。
・A1は、どこでもいいです。シートのいずれかを指定していればかまいません。
・いったんファイルを保存しないとエラーが出ます。
・VLOOKUP関数で数値を使うときは、

=Value(MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,31))

としてください。
(理由は後述します)

数式のしくみ

この数式は、複数の関数を組み合わせています。

①CELL関数でファイル、シートの情報を表示

まず、開いているファイルとシートの情報を表示させます。
CELL関数は、対象セルの情報を表示する関数です。

=CELL(“FILENAME”,A1)

といれると、
Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都
と表示されます。

スクリーンショット 2013 08 29 10 42 17

②MID関数で、シート名のみ取り出す

今、”YドライブーDropboxフォルダーINBOXフォルダのBook2.xlsxというファイルの[東京都]というシート”で操作しているからです。
セルの指定は、A1でも、B1でも、A100でもどこでもかまいません。

シート名を表示するには、
Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都
の「東京都」のみを表示させれば大丈夫です。

文字列から特定の文字列を取り出すには、MID関数を使います。

=MID(開始位置、文字列、文字数)
で指定する関数です。

[文字列]で何文字目から[開始位置]、何文字[文字数]を取り出すかを指定します。

この場合、「東京都」は

[文字列]Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都

の30文字目から3文字取り出しますので、

=MID(30、”Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都”、3)

と書きます。

③FIND関数でシート名が始まる位置を探す

ただ、毎回、「何文字目から何文字」というのを指定するのはめんどくさいです。
そこで、シート名が何文字目からはじまるかを関数でExcelに判断してもらいます。

Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都
をよく見ると、シート名は必ず、]の次から始まっています。
[ ]でファイル名を示しているからです。

そこで、]を探して、何文字目かを表示するFIND関数を使うのです。

=FIND(検索する文字列、検索する対象)
ですので、

=FIND(“]”,”Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都”)

とすると、29と表示されます。

「東京都」は、30文字目から始まるので、1を加算して
=FIND(“]”,”Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都”)+1
とすればいいのです。

④複数の数式を組み合わせる

これまで書いた3つの式を組み合わせます。
=CELL(“FILENAME”,A1)
=MID(30、”Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都”、3)
=FIND(“]”,”Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都”)+1

スクリーンショット 2013 08 29 11 03 30

文字数の3は31にします。
これは、シート名の最大文字数が31だからです。
(100にしても1,000にしても正しく表示されます。)

結果、
=MID(CELL(“FILENAME”,A1),FIND(“]”,CELL(“FILENAME”,A1))+1,31)
になるのです。

複雑な数式も一度理解しておくと、応用がききます。

VLOOKUP関数で使うときは、注意

シート名をセルに表示される事例として、VLOOKUP関数を使うものがあります。
書籍にも載せた事例です。

次のような送付状データ一覧を使い、
スクリーンショット 2013 08 29 11 08 23

送付状を作ります。
スクリーンショット 2013 08 29 11 08 37

たとえば、会社名の欄には、次のような数式が入っています。
黄色いセルにある数字(送付状データの送付状番号)をVLOOKUP関数で連動させているのです。
スクリーンショット 2013 08 29 11 09 35

シート名を「1」に変更すると、黄色いセルが「1」に変わるように、

=MID(CELL(“FILENAME”,A1),FIND(“]”,CELL(“FILENAME”,A1))+1,31)

を黄色いセルに入力しておくと便利です。
シート名を変えるだけでデータを呼び出せます。

ただし、
=MID(CELL(“FILENAME”,A1),FIND(“]”,CELL(“FILENAME”,A1))+1,31)
では、正しく表示されません。

スクリーンショット 2013 08 29 11 14 09

なぜなら、この「1」は文字列で、送付状データの「1」は、数値だからです。
人間には同じように見えても、Excelでは別物とみなします。

こういう場合は、
=VALUE(MID(CELL(“FILENAME”,A1),FIND(“]”,CELL(“FILENAME”,A1))+1,31))

と入れるようにしてください。
VALUE関数は文字列を数値に変換する関数です。

こちらの記事で挙げた事例と同じ考え方をします。
VLOOKUP関数でどうしてもエラーが出る場合の解決方法 | EX-IT

マクロを使える方は、上記の事例もマクロを使ったほうが楽です。
数枚なら、こちらの方法でもいいんですけどね。
VLOOKUPを使う方法は、この本に書いています。
マクロ関係は、今書いている新作に書く予定です。





【編集後記】
使っていたイヤホンが完全にこわれつつあったので、昨日、新しく買いました。
お気に入りのShureです。
近々レビューします。


■スポンサードリンク

■著書
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』