CELL関数~エクセルの関数技2

シート名をセルに表示したい~CELL関数

シートがたくさんあるブックでそれぞれが非常に良く似ている場合、
セルにワークシート名を表示できたら便利だと思うのですが、
どうすればできるのでしょう?

と、こんな質問をいただきました。
(サンプルファイルは、こちらから 関数技54回サンプルデータ

CELL関数とRIGHT関数

シート名をセルに表示するイメージはこんな感じです。
使う関数は、CELL関数とRIGHT関数などの文字列操作関数です。
 シート名をセルに表示する

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

(1)セルの情報を取得するCELL関数と、
文字列を切り出すためにRIGHT関数LEN関数FIND関数
組み合わせれば、シート名をセルに表示できます。
 シート名をセルに表示する方法2

CELL関数とは

CELL 関数は、セルの書式、位置、または内容に関する情報を返す関数です。
CELL関数
引数となる検査の種類には、次の一覧があります。
CELL関数の引数
検査の種類に"fillename"と入力すれば、そのファイルの名前が表示されます。
A2セルに=CELL("filename",A2)と入力すれば、
フルパスでシート名まで表示されます。 A2セルに=CELL("filename",A2) と、この場合の範囲は省略可能です。
どのように表示されるかというと、絶対パス名+[ファイル名]+シート名です。
フルパスでは、いくらなんでも困るので、シート名だけを取り出しましょう。
セル(A2)に下記の式を入力します。
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename",A2)))
CELL 関数
スポンサーリンク
スポンサーリンク

すると、A2セルにシート名を取得します。
シート名を表示
式を入力すれば、すぐにシート名が表示されます。
なんて便利!
式がめんどうなら、 このまま式をコピーして使えばいいんですよ。

シート名をセルに表示する方法の解説

式は、以下です。(実際は1行です。)
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename",A2)))
始めに出てくる、RIGHT関数
1行目の、RIGHT関数は、第1引数で指定された文字列の右側から、 第2引数で指定した文字数分の文字列を返してくれます。
あ
RIGHT関数にとって、
CELL("filename",A2)  が第1引数です。文字列を指定します。

第2引数は、
LEN(CELL("filename",A2))-FIND("]",CELL("filename",A2))

何文字返すのか、文字数を指定しています。

関数LENで文字列の長さを知る

今度は、第2引数に出てくるLEN関数です。
文字列に含まれる文字数を取り出します。
書式 =LEN(文字列)€€
■関数LENで絶対パス名の文字列の長さを知る
 LEN(CELL("filename",A2))
 の部分は、上記のCELL関数で取り出した、
 「絶対パス名+[ファイル名]+シート名」が何文字あるかを調べています。

関数FINDでキー文字の ] 出現位置を知る

■関数FINDで ] が絶対パス名の中で何番目かを調べています。
 FIND("]",CELL("filename",A1))
 の部分は、
 「絶対パス名+[ファイル名]+シート名」の中でファイル名の後に表示される  記号「 €] 」が何文字目にあるのかを調べています。
■ 文字列の長さ-何番目=残りの数
最終的に、関数RIGHTで右端から「残りの数」分だけ取り出しました。

残りの数だけ取り出した文字列がシート名となったわけです。

スポンサーリンク
スポンサーリンク