今回はシートの名前をセルに引用する方法を紹介します。まず結論から言うと、以下の数式を入力することで、シート名を引用することができます。(下記をそのまま使ってエラーが出る場合は、”(ダブルクォーテーション)を英数字半角モードで打ち直してみてください。)
- =RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1)))
シート名の引用はこれでできますが、この数式は様々な関数の組み合わせで成り立っていて、これだけでは何をしているのかがわからないと思います。以下では、数式の中身を理解したい人向けに解説します。
CELL関数
まず使われているのが、CELL関数です。上の数式で3回使用しています。
CELL関数の使い方は以下の通りで、今回は検査の対象を”filename”としているので、このファイルの名前を取得することになります。
- =CELL(検査の種類, 対象範囲)
ここでいうファイルの名前は、そのファイルが保管されている場所からファイル上のシート名まで含みます。私の場合はファイルをクラウド上に保存しているので、以下のようにファイルの場所が”http:”で始まりますが、ローカルドライブに保存している場合は、”C:\Users\user_name\”のようなパスが表示されるでしょう。
そして、この出力の最後に“[参照.xlsx]東京”という形で、ファイル名とシート名が表示されます。今回の処理は、この最後に現れるシート名を利用します。
LEN関数
続いて使用するのがLEN関数です。LEN関数は()内の文字数を数える関数です。上の数式では、以下のように使われています。
- =LEN(CELL(“filename”,A1))
この数式の意味は、()内、つまりCELL関数で出力した”http:”から始まるファイル名の文字数をカウントすることになり、結果としては以下の通り”73″文字であることがわかります。
FIND関数
FIND関数の使い方
次に使うのはFIND関数です。その名の通り、文字列を検索することができます。使い方は以下の通りです。
- =FIND(検索する文字列,検索対象,開始位置(省略可))
開始位置を省略した場合は、文字列の先頭から数えます。上の数式では以下のように使われています。
- =FIND(“]”,CELL(“filename”,A1))
上述の通り、CELL(“filename”,A1)は73文字からなるファイル名でした。今回はその73文字を検索対象として設定し、その中から”]”という鍵カッコの右端を検索しています。これは何をしているのでしょうか。
ほしい情報がどこにあるのかを知る
今回引用したいのはシート名です。最終的には73文字の長いファイル名からシート名の部分だけを抽出すればシート名となります。そこで、そのシート名の直前、つまり“[参照.xlsx]東京”の”]”の部分の場所を知りたいのです。実際、上のFINDの数式によって、以下の通り”71″という結果が得られます。全体73文字のファイル名で、71文字目までがシート名と関係のない情報=消したい情報と分かります。
これを表現したのが以下の式で、上述の通り、計算結果は73-71=2となります。この情報を使うと、”右端から2番目までの文字列がシート名”となります。
- LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1))
RIGHT関数
最後に使うのが、RIGHT関数です。この関数はその名の通り、ある文字列の右端から指定の文字数を取り出す関数です。
- RIGHT(検索する文字列,取り出す文字数)
ここまで言えば、おそらく多くの人は使い方を理解されるでしょう。今回でいえば、以下のように数式を組むことで、シート名を取り出します。
- RIGHT(73文字のファイル名, 2文字(=73-71))
上で説明したテーブル上では、以下の通りとなります。
そして、これらすべてを一つの数式にまとめ上げると、冒頭で説明した通り、以下の数式となり、シート名を引用することができるのです。
- =RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1)))
コメント