関数でシート名を取得する
これは超古典的なやり方なので、もしかすると別のコンテンツ内でも使っているかもしれません。どのコンテンツに書いたかを調べるのも面倒くさいので、あらためてここで解説します。
今回は、任意のセル内にシート名だけを表示する方法を解説しますが、もちろん、この方法で取得したシート名を別の関数の引数に指定することも可能です。使うのはCELL関数です。試しに入力してみましょう。
ちょっと何言ってんのか分かりません。引数はこんな感じです。
引数「検査の種類」には、次の文字列を指定します。
どんな「検査の種類」を指定すると何を返すのかは、ヘルプで確認して、実際に試してください。おそらく、期待外れです。何やら、すごく便利そうな気もしますが、便利に活用できそうな検査は、ほとんどありません。そもそも、このCELL関数をご存じだった方は少ないのでは。それもそのはずです。このCELL関数は、MS-DOS時代に一世を風靡した表計算ソフト「Lotus 1-2-3」にあった関数で、互換性を維持するために用意されていた関数です。使えそうな「検査の種類」は、ほとんどないのですが、そんな中で"filename"だけは別格です。これは使えます。てゆーか、今回のように、アクティブシートの名前を取得するには、これを使うしか手がありません。実際にやってみましょう。ですが、その前にひとつ注意が必要です。このCELL関数＋"filename"というのは、その名のとおり「ブックのフルパス」を返す仕組みなのですが、そのブックが"名前をつけて保存"されていないと何も返ってきません。未保存のブックは、どこかのフォルダに何かの名前で保存しておいてください。ちなみに、解説で使用するブックは、C:\Workフォルダに、Sample.xlsxという名前で保存しています。
引数「検査の種類」には"filename"を指定しました。さて、引数「参照」にはセルA1を指定しているのですが、これはどのセルを指定してもかまいません。今回のCELL関数で調べるのは「ブックのフルパス」です。セルの情報ではありません。どこか適当なセルを指定してください。何なら「=CELL("filename",B2)」のように、CELL関数が入力されているセル自身を指定してもOKです。この引数「参照」は、今回の"filename"でしたら省略可能です。可能ですけど、指定した方がいいです。この引数「参照」を省略すると「アクティブセルが指定された」ものとみなされます。もし、複数のブックを同時に開いていたり、別のシートで作業をしたりすると、そちらの情報が表示されてしまいます。では、CELL関数の結果をご覧ください。左から、ブックが保存されているパス、角括弧[]で囲われたファイル名、そして最後にシート名が表示されています。この中から、シート名だけを取り出せばいいです。
とはいえ、パスの長さやファイル名の長さ、もちろんシート名の文字数などは毎回異なります。しかし、どんなケースであっても必ず「シート名はファイル名の後ろ」に表示されています。さらにファイル名は必ず「角括弧[]で囲われている」わけです。ということは、シート名は必ず「閉じ角括弧]の後ろ全部」と考えられます。もし、今回のケースに限定して考えるなら、
- パス＋ファイル名部分の文字数は21文字
- ファイル名の閉じ角括弧]の位置は21文字め
- 抜き出すシート名の先頭文字(S)は22文字め
- この22は、閉じ角括弧]の位置21に1を足した数
ということです。
文字列の、指定した位置から、指定した文字数分を抜き出すにはMID関数を使います。MID関数をご存じない方はヘルプなどで調べてください。さて、MID関数で抜き出す"元の文字列"は、CELL関数の結果です。今回のケースでしたら、抜き出す先頭位置は22です。問題は"抜き出す文字数"ですね。要するにシート名の文字数です。これは毎回異なります。これ何らかの方法で、厳密に計算してもいいのですが、ここは便利な手があります。MID関数は、最後の引数「抜き出す文字数」に、抜き出せる文字数よりも大きい数値を指定すると、後ろ全部を抜き出すことができます。例えば今回でしたら、シート名「Sheet2」は6文字ですから、6以上の数値を指定すればいいです。Excelでは、シート名に指定できる文字数は「31文字まで」と決まっています。31文字より多い文字はシート名に指定できません。したがって、MID関数で抜き出す文字数が、31を超えることはありません。だったら、悩まずに31を指定すればOKです。
さて、シート名の先頭位置(今回は22)は、閉じ角括弧]の位置(今回は21)に1を加えた数値でした。
この位置を調べるには、FIND関数を使います。FIND関数は、文字列の中で、指定した文字が、左から何文字めにあるかを調べる関数です。VBAのInStr関数みたいなやつですね。書式は「FIND(探す文字,元の文字列,開始位置)」です。今回「探す文字」は、閉じ角括弧ですから"]"を指定します。元の文字列は、CELL関数の結果ですね。最後の引数「開始位置」は省略可能です。省略すると"0文字め"つまり文字列の先頭から探します。今回は先頭から探したいので、この「開始位置」は省略します。
合体させると、こうなります。
CELL関数は「CELL("filename",A1)」でしたね。これを入れて完成です。
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
CELL関数の存在と、その結果からシート名だけを抜き出す考え方をご理解いただけたでしょうか。これで完成ですから、ここで読むのを止めていただいてもかまいません。ただ、最近Insider版に追加されて、今後のExcelに追加されるであろうLET関数を使うと、もう少しだけ、この数式が短くなるので、それも書いておきます。LET関数の詳しい解説は、下記ページをご覧ください。
LET関数は、任意の数値や文字列、あるいは関数の計算結果などを、変数(のようなもの)に格納して、数式内で使える関数です。今回の数式
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
では、まったく同じ「CELL("filename",A1)」を2回使っています。だったら、この結果を変数に入れて使えば、数式全体がコンパクトになります。こんな感じです。
まぁ、それほど大きな効果ではありませんので、無理をしないでいいです。セル内で作成する数式は、マクロよりもはるかに難しいのですから、何よりも"読みやすさ"が最優先です。