【Excel】シート名をセルに、セルの値をシート名に連動するには

こんばんは！今回の記事は、セルの値とシート名を連動するにはどうすれば良いか？という記事になります。

Excelのシートにタイトル付けする場合、そのタイトルとシート名と合わせたい場面がありました。

例えば、シート名が変更になった時、セルの値も変更する（またその逆も）のは、いちいち面倒で。あらかじめ値を連動させておいて、どちらかの値が変更になったら、もう一方の値も変更になるようにしたいようなシーンです。

連動の考え方としては以下があるかと思います。

シート名をセルの値に利用する
セルの値をシート名に利用する

上記2つのやり方をまとめてみました。それでは、いってみましょう！

①シート名をセルの値として使う

シート名をセルの値として利用することは、関数で実現できました。

例として、「テストシート名」というシート名をとあるセルの値として使う場合、以下の関数で実現できました。

=RIGHT(CELL("filename",テストシート名!A1),LEN(CELL("filename",テストシート名!A1))-FIND("]",CELL("filename",テストシート名!A1)))

CELL関数で、検査の種類をfilenameにして、ファイルの名を取り出し、そのファイル名の中にあるシート名をRIGHT関数を使って取り出しています。RIGHT関数の中では、全体の文字数より"]"の文字位置を引くことで、右から何文字分切り取ればよいか、という計算をしています。

セルはA1を指定しているが、取得したいシートの中であればどのセルでもOKです。

関数の結果が「#VALUE」になってしまう時は…

関数の結果が「#VALUE」となってしまう事がありました。これは、そのExcelファイルが一度も保存されていない可能性があります。名前を付けて任意の場所に保存した後、もう一度関数を確認するとシート名が取得できていました。

一度も保存されていないファイルについては、CELL関数で取得した値が空になるため、#VALUEとなったようでした。

気になる人

逆パターン、セルの値をシート名にする方法もあるの？

②セルの値をシート名として使う

セルの値をシート名に使用する場合は関数では実現できませんでした。シート名に関数を埋め込むことができないからです。

ということで、無理やりやるならば、VBAでマクロ実行時にセルの値でシート名を変更するという処理で実現になります。各シートのセルA1の値を、そのシートの名前に採用するというサンプルマクロを作成してみましたので紹介します。

Sub sheetName()

 ' 変数定義
 Dim i As Integer

 ' 全てのシートを一つずつ処理
 For i = 1 To Sheets.Count
   ' シートを選択
   Sheets(i).Select

   ' シート名にそのシートのA1セルの値を設定
   Sheets(i).Name = Range("A1").Value

 Next i

End Sub

※セルA1が空白だと動きません。（実行時エラー'1004'）

ただ、VBAマクロが含まれているとExcelファイルの拡張子が.xlsmとなってしまいますし、マクロを明示的に実行しないとシート名も変更できません。ということで、②パターンの場合は微妙だなと個人的には思います。

①の方法のシート名をセルの値に持ってくるか、手作業で変更した方が良さそうに思います。

さいごに

いかがだったでしょうか。今回の記事ではセルの値とシート名をどうリンクさせるか、2パターンの方法で考えてみました。同じような疑問を持たれた方の参考になれば幸いです。それでは、また次の記事で！