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

  • URLをコピーしました!

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

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

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

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

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

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

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

 

 

数式のしくみ

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

 

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

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

=CELL(“FILENAME”)

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

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

 

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

今、”YドライブーDropboxフォルダーINBOXフォルダのBook2.xlsxというファイルの[東京都]というシート”で操作しているからです。

シート名を表示するには、
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”)
=MID(30、”Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都”、3)
=FIND(“]”,”Y:¥Dropbox¥INBOX¥[Book2.xlsx]東京都”)+1

 

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

結果、
=MID(CELL(“FILENAME”),FIND(“]”,CELL(“FILENAME”))+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”),FIND(“]”,CELL(“FILENAME”))+1,31)

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

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

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

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

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

と入れましょう。
VALUE関数は文字列を数値に変換する関数です。

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

 

 

 

 

 

 

 

 

 

 





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

  • URLをコピーしました!