こんにちは、サンダー杉山です(嘘です)。
EXCELでひとつのブックに幾つもシートをわけるときに現在のシート名をセル内で表示させたいことがたまにある。シート名など短いものなので手入力すればよいかもしれないが、他のシートからセルを参照したりする場合にシート名が変わると参照先が見つからなくて怒られたりする場合もあるのだ。なので、シート名はなるべく数式で入力することをお薦めする。
で、この方法なのだが、どこから仕入れてきたネタだったか検索してもわからなかったので、備忘録としてここに書いておくことにする。
やってみればわかるのだが、任意のセルに下記のような数式を埋め込めばよい。
=RIGHT(CELL("filename",A1) , LEN(CELL("filename",A1)) - FIND("]",CELL("filename",A1)))
これが何をやっているかを上記の数式を分解してみよう。
Microsoft EXCEL のHELP をみると最も外側の関数 RIGHT は下記のような命令であることがわかる。
- RIGHT(文字列,文字数)
- RIGHT 関数は、文字列の末尾 (右端) から指定された文字数の文字を返します。
つまり、文字列 CELL("filename",A1) の末尾から LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)) 個分の文字を返すということのようである。
次に CELL("filename",A1) は何かを見てみる。
CELL 関数は下記のような命令である。
- CELL(検査の種類,対象範囲)
- 対象範囲の左上隅にあるセルの書式、位置、内容についての情報を返します。
ここで、検査の種類は address , col , color , contents , filename , format , parentheses , prefix , protect , row , type , width などといろいろ種類があるようだが、今回は「filename」という検査の種類を利用する。
CELL("filename",対象範囲)
はどういう挙動を示すかといえば、下記のようになっている。
- CELL("filename",対象範囲)
- 対象範囲を含むファイルの名前 (絶対パス名) を表す文字列。対象範囲を含むファイルがまだ保存されていない場合、結果は空白文字列 ("") になります。
この場合対象範囲の部分は便宜上A1というセルを利用しているが、セルの場所は現在のセルであれば何でもよい。
今、このファイルが「hoge.xls」という名前で下記の場所にあるとする。
C:\Documents and Settings\ryouchi\デスクトップ\
その中のシート名が「シートfuga」という名前の場合、
=CELL("filename",A1) は
C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]シートfuga
となる。
ここで、CELL("filename",A1) を X とすると、はじめの数式はこんな感じで表せる。
=RIGHT(X,LEN(X)-FIND("]",X))
随分簡単に見えるようになった。
次にLEN(X)の意味を調べる。
LEN関数の説明をHELPで見てみる。
LEN(文字列)
LEN 関数は、文字列の文字数を返します。
つまり、LEN("ABCDEF")であればその値は6になる。
ここでは、LEN(X)。つまり、LEN(CELL("filename",A1)) の値は
C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]シートfuga
の文字列の長さなので、計算すると58になる。
次に、FIND("]",X) を見てみる。
FIND関数の説明をHELPで見てみる。
- FIND(検索文字列,対象(,開始位置))
- FIND 関数では、指定された文字列 (検索文字列) を他の文字列 (対象) の中で検索し、その文字列が他の文字列内で最初に現れる位置を左端から数え、その番号を返します。SEARCH 関数と同じような働きをしますが、FIND 関数では英字の大文字と小文字を区別できる代わりに、ワイルドカード文字を使用することができません。
なにやら難しいことが書いてあるが、要するに、
文字列の中に自分が検索したい文字が左から数えて何番目にあるかということだ。
FIND("o","hogehoge")
の場合、hogehogeという文字列の左から2番目に「o」が存在するので、2が返ってくる。
で、FIND("]",X))ではどうなるか?
X は説明の過程で CELL("filename",A1) のこととした。
CELL("filename",A1)の値は、
C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]シートfuga
なので、FIND("]",X))は
FIND("]","C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]シートfuga")
のことである。
つまり、
C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]シートfuga
の]は左から何番目にあるかを探している。
これはやってみると、51番目であることがわかる。
=RIGHT(X,LEN(X)-FIND("]",X))
という数式の LEN(X)-FIND("]",X) の部分はまとめると、
絶対パスの長さ(LEN(X)) から 絶対パスの中で"]"がある部分までの長さ(FIND("]",X))を引きましょうということになる。
つまり、これで何がわかるかというと、シート名の文字列の長さがわかるのだ。
C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]シートfuga
C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]
なので、LEN(X)-FIND("]",X) はシート名の文字列の長さをあらわすことになる。
この長さをYとすると、
=RIGHT(X,LEN(X)-FIND("]",X))
は、
=RIGHT(X,Y)
となる。
ここで、RIGHT関数をもう一度思い出す。
RIGHT(文字列,文字数)
RIGHT 関数は、文字列の末尾 (右端) から指定された文字数の文字を返します。
なので、
=RIGHT(X,LEN(X)-FIND("]",X))
は、絶対パスの文字列から、シート名の長さ分だけ末尾(右端)分の文字数の文字列を返すということになる。
つまり、
C:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]
シートfugaC:\Documents and Settings\ryouchi\デスクトップ\[hoge.xls]
の赤文字の部分。つまりシート名が取得できることになる。
うーむ、これ考えた人、すごいな・・・
ちなみに、フォルダ名やファイル名やシート名に"]"という文字が利用できない点も見逃せない。("]"が利用できちゃうとこのTIPSは破綻するよね?)
で、今日のまとめ。
EXCELで現在のシート名をセル内で表示させるには、セル内に下記のように入力する。
=RIGHT(CELL("filename",A1) , LEN(CELL("filename",A1)) - FIND("]",CELL("filename",A1)))
うーむ、便利っす。