【コピペで使える】エクセルのシート名取得法-活用法とは何?どんなことに使えるでしょうか?

当サイトワカルニの記事はPRを含む場合があります。
excel シート名 取得 エクセル
「シート名をセルに表示させたい」
「日付のシート名を使ってカレンダーを自動で更新できたなら楽なのに…」

エクセルに徐々に慣れてくると使うようになってくる「シート名」。
取得できればカレンダーと関連付けたり数式と組み合わせて参照先を切り替えたりできるのですが、どうすればシート名を取得できるか分からなくて悩んでいませんか?

ここではシート名取得の基本手順やボタン一つでシート名を取得できるマクロコード、そして取得したシート名を使った応用テクニックを紹介します。
読めば確実にエクセルのレベルがワンランクアップしますよ!

1.シート名を取得する基本手順

まずはシート名を取得する基本手順をみてみましょう。
ここでは関数を使った方法を紹介します。

シート名を取得するには「CELL」、「LEN」、「RIGHT」、「FIND」の4種類の関数を組み合わせることが必要で、一見なにをしようとしているのか分かりづらいのがネックです。
しかし整理してみると分かりますので、ここではまず手順を確認してから詳しい説明をします。

では手順からみてみましょう。

(1)シート名を表示させたいセルを選択する
excel シート名 取得

(2)「=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))」と入力する
excel シート名 取得

(3)完了
excel シート名 取得

このような式を入力すれば、簡単にシート名を取得することができます。
なお、この数式が正しく動くには条件があり、それは「一度以上保存されているデータであること」、「ファイル名に『]』が使われていないこと」の2つです。この点に注意しましょう。

続いて関数の組み合わせの説明に入ります。まず使用した関数はそれぞれ次のような動きをします。

CELL関数:指定した範囲のセルの情報を返す
LEN関数:指定した文字列の文字数を返す
FIND関数:指定した文字列が最初に現れる位置を返す
RIGHT関数:文字列の末尾から指定した文字数の文字を返す

そして数式の流れは次の通りです。

① CELL関数で「ファイルパス」、「ファイル名」、「シート名」を取得する
(数式の結果 → C:\NB\その他\[エクセル シート名 取得.xlsx]Sheet1)
excel シート名 取得

② LEN関数で①で返される文字列の文字数をカウントする
(数式の結果 → 35)
excel シート名 取得

③ FIND関数で①で返される文字列の中から「]」の位置を調べる
(数式の結果 → 29)
excel シート名 取得

④ RIGHT関数で①で取得した文字列の末尾から②-③の文字(35-29=6)だけ取り出す(数式の結果 → Sheet1)
excel シート名 取得

特に③の所で「]」の位置をFIND関数で先頭から調べているため、ファイル名に「]」があるとシート名を上手く取得できなくなります。気を付けてください。

2.ボタン一つで簡単に!シート名を取得するマクロコード

次にボタン一つで簡単にシート名を取得できるマクロコードを紹介します。
シートの名前が選択しているセルに入るマクロになっている便利なものです。

(1)「Alt」+「F11」キーを押し、Visual Basicを開く
excel シート名 取得

(2)左のツリー上で右クリックを押し、「挿入」→「標準モジュール」を選択する
excel シート名 取得

(3)追加されたModule1に、「Sub~End Sub」までの以下のコードを張り付ける

Sub 指定したセルにシート名挿入()
Dim sheetName AS String
sheetName = ActiveSheet.Name
ActiveCell.Value = sheetName
End Sub

(4)シート名を入力したいセルを選択し、「Alt」+「F8」キーを押した後「指定したセルにシート名挿入」を選択して「実行」を押す
excel シート名 取得

(5)完了
excel シート名 取得

マクロコードを上記の手順でModuleに書きこんでしまえば次からは、(4)を行うだけでシート名を簡単に取得できるようになります。

3.シート名取得を活用した応用テクニック

最後に取得したシート名を活用してできる応用テクニックを2つ紹介します。

3-1.シート名を使って日付を更新

まずはシート名を使って日付を更新するテクニックからです。
ここではシート名を月日の羅列(例:0301)とした場合で解説します。

(1)シート名を日付にする
excel シート名 取得

(2)日付を表示したいセルを選択する
excel シート名 取得

(3)「=TEXT(2019&RIGHT(CELL("filename",A3),LEN(CELL("filename",A3))-FIND("]",CELL("filename",A3))),"0000!/00!/00")」と入力する
excel シート名 取得

(4)完了
excel シート名 取得

先ほど紹介した「関数を使ってシート名を取得する」方法に、TEXT関数を組み合わせて日付データとしています。
なお、「2019」の所は年が変わる都度変更する必要があります。

なお、「YEAR(TODAY())」とすれば常に「今年」の表示となりますが、開いた日によって変わってしまうので使える場面は限定的です。

3-2.Indirect関数を使ってシート名をセル参照で指定

次にIndirect関数を使ってシート名をセル参照で指定する方法です。
あらかじめ設定したリストから選ぶことで、参照先のシートを切り替えるものになっています。

excel シート名 取得

excel シート名 取得

では見てみましょう。

(1)枠外のセルに「シート名」を入力する
excel シート名 取得

(2)「データ」タブにある「データの入力規則」をクリックする
excel シート名 取得

(3)「設定」タブにある「入力値の種類」から「リスト」を選択し、「元の値」に「=(シート名を入れた範囲)」を入れる
excel シート名 取得

(4)結果を表示させたいセルを選択する
excel シート名 取得

(5)「=INDIRECT(A1&"!B2")」と入力する(シートAとシートBから取得したいセルの値が『セルB2』にある場合)
excel シート名 取得

(6)セルA1のリストからシート名を選択すれば完了
excel シート名 取得

このようにすればリストの切り替えで簡単に参照先のシートを切り替えることができます。
ぜひ試して見てください。

4.まとめ

以上エクセルのシート名取得について、基本手順とマクロコード、そして応用テクニックを2つ紹介しました。

シート名を取得できるようになることで、やれることの幅はぐっと広がります。
ですからここに書かれたテクニックは是非習得するようにしてください。

スポンサードリンク

コメント