Excel関数でセルにシート名を自動で取得する方法を知りたいときはないでしょうか。
けど、そんな中で悩むことは、
・Excel関数で全シート名の他にシート位置番号からシート名を自動で取得したいが方法がわからない
ですよね。
今回はそんなお悩みを解決する
・Excel関数でセルにシート位置番号からシート名を自動で取得する方法
についてまとめます！
もくじ
Excel関数でセルにシート名を自動で取得するイメージ
Excel関数でセルにシート名を自動で取得するイメージについて説明をします。
まずはシートが複数あるExcelブックを用意します。
関数をセルに入力すると、
スピル方式で全シート名が縦方向に表示されます！
シートの数が増えても、
自動で増えた分のシート名が追加されます！
次に関数の引数にシート位置番号（1,2,3など）の整数を指定すると、
その位置にあるシート名が表示されます！
その位置にあるシート名を変更すると、
自動でシート名が変更されます！
シート名をリアルタイムに一覧化したいときに便利ですね！
早速実装してみましょう！
Excel関数でセルにシート名を自動で取得する方法
複数シートがあるExcelブックを用意
まずは複数シートがあるExcelブックを用意しましょう。
Excel関数でセルにシート名を自動で取得するオリジナル関数
次にExcel関数でセルにシート名を自動で取得するオリジナル関数を実装していきます。
サンプルコード
オリジナル関数をつくるサンプルコードは以下の通りです。
Function ExtGetSheetNames(Optional valSheetIndex As Variant)
Dim objWS As Worksheet
Dim valSheetNames As Variant
Dim i As Long
Dim outputRange As Range
' シート名を取得します。
' 引数があるかないか判定します。
If IsMissing(valSheetIndex) Then
' シートの数に合わせて配列をリサイズします。
ReDim valSheetNames(1 To Worksheets.Count)
i = 1
For Each objWS In Worksheets
valSheetNames(i) = objWS.Name
i = i + 1
Next objWS
' 引数が整数の場合の処理です。
ElseIf IsNumeric(valSheetIndex) Then
If valSheetIndex > 0 And valSheetIndex <= Worksheets.Count Then
ReDim valSheetNames(1 To 1)
valSheetNames(1) = Worksheets(valSheetIndex).Name
Else
ExtGetSheetNames = valSheetIndex & "番目のシート番号は存在しません。"
Exit Function
End If
Else
ExtGetSheetNames = "引数は整数で指定してください。"
Exit Function
End If
' シート名を出力し関数へ返します。
ExtGetSheetNames = Application.Transpose(valSheetNames)
End Functionオリジナル関数を実装する
オリジナル関数を実装する方法は以下の通りです。
Excel側にVBAを実装していきます。
①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。
②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。
③右ペインのウインドウに上記のVBAを入力します。
こちらで完了です。
オリジナル関数をセルに入力し全シート名を表示させる
オリジナル関数をセルに入力しましょう。
以下の数式をA1セルなど適当なセルに入力します。
=ExtGetSheetNames()
はい、全シート名がセルに入力されましたね！
オリジナル関数で追加シート分のシート名が出力されるか確認
オリジナル関数で追加シート分のシート名が出力されるか確認してみましょう。
シート名を追加します。
オリジナル関数を選択、Enterキーを押し再計算させます。
はい、追加分のシート含め全シート名がセルに入力されましたね！
オリジナル関数にシート位置番号を引数に指定し、該当シート名が出力されるか確認
オリジナル関数にシート位置番号を引数に指定し、該当シート名が出力されるか確認してみましょう。
2番目のシート名を指定します。
=ExtGetSheetNames(2)オリジナル関数を選択、Enterキーを押し再計算させます。
はい、シート位置番号にあるシート名がセルに入力されましたね！
オリジナル関数にシート位置番号を引数に指定し、変更したシート名が出力されるか確認
オリジナル関数にシート位置番号を引数に指定し、変更した該当シートのシート名が変更後のシート名で出力されるか確認してみましょう。
同じく2番目のシート名を指定します。
=ExtGetSheetNames(2)2番目のシート名を変更します。
オリジナル関数を選択、Enterキーを押し再計算させます。
はい、シート位置番号にあるシートの変更後のシート名がセルに入力されましたね！
オリジナル関数に存在しないシート位置番号を引数に指定した場合メッセージ表示されるか確認
次にオリジナル関数に存在しないシート位置番号を引数に指定した場合メッセージ表示されるか確認してみましょう。
存在しない7番目のシート名を指定します。
=ExtGetSheetNames(7)オリジナル関数を選択、Enterキーを押し再計算させます。
はい、存在しない旨のメッセージがセルに表示されましたね。
これで入力ミスがあったとしてもわかりますね。
オリジナル関数のコードの説明
オリジナル関数のコードの説明します。
シート名を取得します。
引数があるかないか判定し引数が省略された場合の処理を行います。
If IsMissing(valSheetIndex) Thenシートの数に合わせて配列をリサイズします。
ReDim valSheetNames(1 To Worksheets.Count)コレクション内の各ワークシートに対してループ処理を行い
現在のワークシートの名前を配列に代入します。
For Each objWS In Worksheets
valSheetNames(i) = objWS.Name
i = i + 1
Next objWSIsNumeric関数を使用して、数値であるかどうかをチェックします。
引数が整数の場合の処理です。
ElseIf IsNumeric(valSheetIndex) Then引数が有効な範囲内のシート番号であることを確認します。
範囲内であれば引数で指定されたシート番号に対応するシート名を配列に代入します。
範囲外であればメッセージを表示させます。
If valSheetIndex > 0 And valSheetIndex <= Worksheets.Count Then
ReDim valSheetNames(1 To 1)
valSheetNames(1) = Worksheets(valSheetIndex).Name
Else
ExtGetSheetNames = valSheetIndex & "番目のシート番号は存在しません。"
Exit Function
End If引数が整数でない場合のメッセージ処理となります。
Else
ExtGetSheetNames = "引数は整数で指定してください。"
Exit Function
End Ifシート名を出力し関数へ返します。
ExtGetSheetNames = Application.Transpose(valSheetNames)さいごに
いかがでしょうか。
今回は、
・Excel関数でセルにシート位置番号からシート名を自動で取得する方法
についてまとめました。
また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。
この記事の関連キーワード
「シート」の関連記事
こちらの記事の内容「シート」に関連する記事一覧です。
コメントを残す