最近はExcelでテスト仕様書などのドキュメントを書いています。
Excelでドキュメントを管理する際に、シート名の修正や一覧取得が必要になることがあります。こういった作業は手動での処理が必要にみえますが、実はマクロを使って時短できるので、簡単に紹介します。
シート名の連番処理
前提
資料の作成中は、シート配置を変更する可能性を考慮し、「XX」など仮の値を設定して作業をされている方もいらっしゃるかと思います。
シート名のXX部分に連番を付与する
配置が完了し、「XX」の部分に連番を付ける場合は次のようにします。
- Excelファイルを開いた状態で、Alt + F11キーを押しVBAエディタを開きます。
- メニューから「挿入」-「標準モジュール」を選択し、新しいモジュールを挿入します。
- 以下のコードを記載します。
Sub ChangeSheetNameWithSequentialNumbers()
Dim ws As Worksheet
Dim i As Integer
i = 1
For Each ws In ThisWorkbook.Sheets
If Left(ws.Name, 2) = "XX" Then
ws.Name = Format(i, "00") & Mid(ws.Name, 3)
i = i + 1
End If
Next ws
End Sub - Alt + Qキーを押してVBAエディタを閉じます。
- Excelの画面に戻り、Alt + F8 キーを押します。先ほど作成したマクロを選択し「実行」をクリックします。
-
以下のように連番が付与されました。
連番を振りなおす方法
連番を付与した後、配置を変更するパターンもあるかと思います。
今回のサンプルでは、連番の振りなおし対象は4シート目以降となります。
マクロは以下のように記載して実行してください。
手順は前の項目に記載しているため割愛します。
Sub ReorderAndRenameSheets()
Dim ws As Worksheet
Dim i As Integer
i = 1
For Each ws In ThisWorkbook.Sheets
If i > 3 Then
ws.Name = Format(i - 3, "00") & Mid(ws.Name, 3)
End If
i = i + 1
Next ws
End Sub
マクロ実行の結果、4シート目から連番の振りなおしができたと思います。
シート名の一覧を出力
基本：シート名から値を抽出
Excel上のシート名を一覧表示するマクロは以下になります。
新しいシート上(Sheet1)で実行することでA1セルからリスト表示されます。
Sub ListSheetNames()
Dim i As Integer
For i = 1 To ThisWorkbook.Sheets.Count
Cells(i, 1) = ThisWorkbook.Sheets(i).Name
Next i
End Sub
以下のような出力が得られます。
私は目次の作成をするときに利用しています。
応用：シートの特定セルの値を抽出
シート名の特定の値を一覧表示させたい場合について記載します。
先ほどのマクロを流用する場合、修正対象は以下の部分です。
Cells(i, 1) = ThisWorkbook.Sheets(i).Name
各シートのA1セルの値を抽出する場合は、以下のようにします
Cells(i, 1) = ThisWorkbook.Sheets(i).Range("A1").Value
シート名セルの活用
シート名が記載されたセルを利用して関数を使う
以下のような関数があったとします。
=MAX('01.設定値確認(単体試験)'!B:B)
今回のサンプルでは、セルの連番を使ってテスト数を計算しています。
MAX関数は最大値を返す関数です。D3セルには、「01.設定値確認(単体試験)」シートのB列の最大値29が返っています。
D3セルの内容をD4セル以降にコピー&ペーストしても同じ関数が使われるため、同じ値しか返りません。
使い勝手が悪いので、C列のシート名を引用する構成にするのがおすすめです。
以下のようにINDIRECT関数を使います。
=MAX(INDIRECT("'"&C3&"'!B:B"))
この関数であれば、コピー＆ペーストで簡単に展開できます。
おわりに
小ネタでしたが、作業時間の短縮に少しでも貢献できたなら幸いです。
最近はChatGPTを使って作業時間短縮の方法を調べる事が多いのですが、入力情報から要約や添削などをしてくれるので、大変便利な世の中になったなあと痛感しております。
