当サイトはアフィリエイト広告を利用しています。
今回は、Excelでシート名を取得する方法をご紹介します。
- シート名を一覧化したい
- セルの値をシート名に反映させたい
- シート名をセルに反映させたい
こんな方はぜひ参考にしてみてくださいね。
なお、シート名を一覧化する方法については、各シートへのリンクを付ける方法もご紹介します。
Excelでシート名を取得して一覧にする方法
Excelでシート名を取得して一覧にするには、マクロを使います。
［Alt］＋［F11］でVBEを起動し、標準モジュールに以下のコードをコピペします。
Sub シート名を一覧化()
Dim ws1 As Worksheet
Dim c As Long
Dim i As Long
' シート名一覧を表示するシート
Set ws1 = Worksheets(1)
' シート名を表示する列番号
c = 1
' シート名一覧をリセット
ws1.Columns(c).Clear
For i = 2 To Worksheets.Count
ws1.Cells(i - 1, c).Value = Worksheets(i).Name
Next
End Subこのマクロを実行すると、左から1番目のシートに2番目以降のシート名がA1から下方向に順に入力されます。
対象のシートが左から3番目以降ならFor i = 2 To Worksheets.CountをFor i = 3 To Worksheets.Countに、シート名を表示するセルの起点がA2ならws1.Cells(i - 1, c).Valueをws1.Cells(i, c).Valueに変更しましょう。
コードをコピペしたらExcelの画面に戻り、「開発」タブ⇒「マクロ」をクリックします。
「マクロ」が表示されたら、「シート名を一覧化」を選択して「実行」をクリックします。
これで左から2番目以降のシート名が一覧で表示されます。
なお、各シートへのリンクを付けて一覧化したい場合は、以下のコードをコピペしましょう。
Sub シート名をリンク付きで一覧化()
Dim ws1 As Worksheet
Dim c As Long
Dim i As Long
' シート名一覧を表示するシート
Set ws1 = Worksheets(1)
' シート名を表示する列番号
c = 1
' シート名一覧をリセット
ws1.Columns(c).Clear
For i = 2 To Worksheets.Count
ws1.Hyperlinks.Add _
Anchor:=Cells(i - 1, c), _
Address:="", _
SubAddress:=Worksheets(i).Name & "!A1", _
TextToDisplay:=Worksheets(i).Name
Next
End Subこちらのマクロを実行すると、以下のように各シートへのリンクが付きます。
mono
シートがたくさんある場合はリンクを付けておくと一覧からワンクリックで移動できるので便利です♪
Excelでセルの値をシート名にする方法
Excelでセルの値をシート名にする場合もマクロを使います。さきほどと同様、標準モジュールに以下のコードをコピペします。
Sub セルの値をシート名にする()
Worksheets(1).Name = Worksheets(1).Range("A1")
End Subこのマクロを実行すると、左から1番目のシートの名前がA1の値になります。
対象のシートが左から2番目ならWorksheets(1)をWorksheets(2)に、対象のセルがB1ならRange("A1")をRange("B1")に変更しましょう。
コードをコピペしたらExcelの画面に戻り、「開発」タブ⇒「マクロ」から「セルの値をシート名にする」を実行します。これでセルの値がシート名になります。
なお、複数のシートで同じことをしたい場合は、シートの数だけWorksheets(左から数えたシートの位置).Name = Worksheets(左から数えたシートの位置).Range("セル番地")を追加すればOKです。
Excelでシート名をセルに入力する方法
Excelでシート名をセルに入力するには、以下の3つの関数を使います。
- REPLACE関数
- CELL関数
- FIND関数
REPLACE関数（リプレイス関数）は、文字列中の指定された文字数の文字を別の文字に置き換える関数です。
REPLACE（文字列, 開始位置, 文字数, 置換文字列）
- 文字列（必須）
- 置き換えたい文字列を含む文字列を指定する。
- 開始位置（必須）
- 置き換えたい文字列が【文字列】の先頭から数えて何番目にあるか指定する。
- 文字数（必須）
- 置き換えたい文字列が【開始位置】から数えて何文字分か指定する。
- 置換文字列（必須）
- どのような文字列に置き換えるか指定する。
CELL関数（セル関数）は、対象の書式・位置・内容などの情報を返す関数です。
CELL（検査の種類, ［対象］）
- 検査の種類（必須）
- 対象についてどんな情報を取得したいか「””」で囲んで指定する。
- aliress：対象の左上隅にあるセル番地
- col：対象の左上隅にあるセルの列番号
- colo：セルに負の数を色で表す表示形式が設定されている場合は「1」を、設定されていない場合は「0」を返す。
- contents：対象の左上隅にあるセルの値
- filename：ファイルの名前。保存されていない場合は「””（空白）」を返す。
- forma：セルの表示形式
- parentheses：数値を()で囲む表示形式が設定されている場合は「1」を、設定されていない場合は「0」を返す。
- prefix：セル内のデータの配置。左詰めの場合は「’」を、右詰めの場合は「”」を、中央揃えの場合は「^」を、両揃えの場合は「\」を、それ以外の場合は「””」を返す。
- protect：セルがロックされている場合は「1」を、ロックされていない場合は「0」を返す。
- row：対象の左上隅にあるセルの行番号
- type：セルに含まれるデータのタイプ。空白の場合は「b」、文字列定数の場合は「l」、それ以外の場合は「v」を返す。
- wilih：セルの幅（標準のフォントサイズで何文字分か）
- 対象（省略可）
- どのセルから情報を取得したいか指定する。省略するとCELL関数を入力したセルとして処理される。
FIND関数（ファインド関数）は、対象の中から指定された文字列を検索し、それが左端から数えて何番目にあるか返す関数です。
FIND（検索文字列, 対象, ［開始位置］）
- 検索文字列（必須）
- 検索したい文字列を指定する。
- 対象（必須）
- どの文字列から検索したいか指定する。
- 開始位置（省略可）
- 検索を開始する位置を指定する。省略すると1文字目として処理される。
（例）Sheet1の名前をA1に入力したい
Sheet1のA1に以下のような数式を入力します。
=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")CELL("filename")でファイルのシート名を含むフルパス名が取得され、FIND("]",CELL("filename"))でフルパス名とシート名の堺の文字列となる「]」が検索され、それが先頭から何文字目にあるか調べます。
これらをREPLACE関数の引数とすることで、フルパス名からシート名以外の文字列を削除することができ、結果としてシート名が表示されます。
「#VALUE!」エラーが表示される場合は、一旦ファイルを保存してみてください。保存したファイルを開くとエラーが解消されているはずです。
まとめ
今回は、Excelでシート名を取得する方法をご紹介しました。
シート名を一覧化したい場合や、セルの値をシート名にしたい場合は、マクロを使いましょう。いずれも複雑なコードではないので、マクロ初心者の方でも簡単にカスタマイズできるはずです。
シート名をセルに入力したいときは、REPLACE関数、CELL関数、FIND関数の3つの関数を組み合わせると良いでしょう。
ご紹介したマクロで希望の動作にならない場合、マクロの作成を依頼できるサービスを利用するのがおすすめです。以下の記事でマクロの作成を依頼できるサービスをご紹介していますので、興味のある方はぜひあわせてご覧ください。
