Excelでシート名を自動で取得できるようにすると、集計シートや一覧表の管理が一気に楽になります。
手入力によるコピペをやめて数式やVBAでシート名を取得できれば、シート名を変更しても自動で反映される信頼性の高いファイルを作れます。
この記事ではExcelでシート名を取得する関数やVBAの基本から、Power Queryでシート名一覧を扱う方法までを通して実務での使い分け方を整理します。
Excelでシート名を取得する基本と実用テクニック
まずはExcelでシート名を取得したい典型的な場面と、関数やVBA、Power Queryといった代表的な方法の選び方を全体像として押さえます。
シート名を取得したい典型的なシーン
月次シートを量産しているときに一覧シートに月名を自動で並べたい場面では、シート名を取得する仕組みがあると更新作業を短時間で終えられます。
部署ごとのシートをまとめた集計表の見出しにシート名をそのまま表示したい場合も、シート名取得を自動化しておけば表のずれやタイプミスを防げます。
別ブックのシートを大量に統合するときにシート名をキー情報として扱えれば、どのデータがどのシート由来かが一目で分かるようになります。
セル関数でシート名を取得する全体像
関数でシート名を取得する基本はCELL関数でファイルのフルパスとシート名をまとめて取得し、その文字列からシート名の部分だけを取り出す考え方です。
従来はMID関数とFIND関数を組み合わせて右側の閉じカッコやブラケットの位置を調べてシート名部分を抽出する数式がよく使われてきました。
Microsoft 365ではTEXTAFTER関数を併用することで閉じブラケットの直後以降をまとめて取り出し、より短い数式でシート名を取得できるようになっています。
ブックの保存状態や環境による制限
CELL関数でシート名を含むファイル情報を取得する場合はブックが一度保存されていることが前提となり未保存の新規ブックでは空白が返されます。
SharePointや一部のクラウド環境上ではCELL関数のfilename指定がサポートされない場合があり、その環境では別の方法を選ぶ必要があります。
オンライン版Excelやブラウザ環境ではブックのパス情報が取得できないケースもあるため、ローカル版Excelと同じ挙動を期待しないように注意が必要です。
VBAでシート名を取得する場面
シート数が多いブックで全シート名を一括取得したいときやシート名に応じて処理内容を変えたいときは、VBAでシート名を扱う方法が向いています。
VBAではActiveSheetやWorksheetsコレクションのNameプロパティを使うことでアクティブシート名や特定のシート名を簡単に文字列として取得できます。
取得したシート名をセルに書き出したり配列に格納したりすれば、ループ処理と組み合わせて柔軟な自動化ロジックを組み立てられます。
Power Queryでシート名一覧を扱う場面
複数のExcelファイルをまとめて取り込んで分析するときにはPower Queryでファイルとシートの一覧をテーブルとして扱うことで管理がしやすくなります。
Power QueryではExcelブックを読み込むとシート名やテーブル名を含む一覧テーブルが得られ、その一覧から必要なシートだけをフィルターして利用できます。
大量のファイルやシートを統合する場合は関数やVBAだけで管理するよりもPower Queryに任せた方が再利用性や保守性の高い仕組みを作りやすくなります。
方法ごとのメリットとデメリット
数式によるシート名取得はブックを開いた利用者にも分かりやすく関数が分かる人なら誰でも修正しやすい一方で環境依存の制約を受ける点があります。
VBAによる取得は自由度が高く複雑なルールにも対応できますがマクロ有効ブックにする必要がありセキュリティポリシー上使えない環境もあります。
Power Queryは大量データや複数ブックの統合に強いものの学習コストがあり簡単な用途では関数だけで済ませた方が手軽なことも多くなります。
関数でExcelのシート名を取得する手順
ここではCELL関数を起点にMIDやFINDやTEXTAFTERを組み合わせてシート名を取得する具体的な数式と、その設定手順や注意点を詳しく確認します。
CELL関数でブック情報を取得する
最初のステップはCELL関数でパスとファイル名とシート名をまとめて取得しその文字列の中にシート名が含まれていることを確認することです。
- 関数名CELL
- 検査の種類filename
- 参照セルA1など任意のセル
- 返される情報フルパスとファイル名とシート名
- 前提条件ブックが保存済み
例えばセルA1に等号CELL括弧引用filename引用カンマA1括弧閉じと入力するとパスとファイル名とシート名が一続きの文字列として表示されます。
この文字列は保存場所のパス角括弧ファイル名角括弧閉じシート名という並びになっているため右側のシート名だけを切り出せば目的の情報を得られます。
MID関数とFIND関数でシート名部分を切り出す
従来のExcel環境ではMID関数とFIND関数を組み合わせて閉じブラケットの位置を探しその位置の次の文字から末尾までを切り出す数式がよく使われてきました。
代表的な数式ではCELL関数を入れ子にした文字列からFINDで閉じブラケット位置を求めてMIDでその次の文字から末尾までを抽出します。
| 項目 | 内容 |
|---|---|
| 使用する関数 | CELLとMIDとFIND |
| 代表的な数式例 | =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255) |
| 取得できる情報 | シート名のみ |
| 対応バージョン | 多くのExcelバージョンで利用可能 |
MID関数の第ニ引数には文字列内での開始位置を指定するためFIND関数で閉じブラケットの位置を調べてプラス1した値を渡します。
第三引数で指定する文字数はシート名の最大長より十分大きい数を指定しておけば末尾までの文字列をすべて取得できます。
TEXTAFTER関数を使ったMicrosoft 365での簡単な式
Microsoft 365環境ではTEXTAFTER関数が使えるためCELL関数で取得した文字列に対して閉じブラケットを区切り文字として指定するだけでシート名を取り出せます。
具体的にはセルに等号TEXTAFTER括弧CELL括弧引用filename引用カンマA1括弧閉じカンマ引用括弧閉じ角括弧引用括弧閉じと入力するだけでシート名を得られます。
MIDとFINDを組み合わせた長い数式に比べて意味が直感的で説明がしやすく数式の保守性も高くなる点が大きな利点です。
関数でシート名を取得するときのよくあるつまずき
関数でシート名を取得する設定をしても期待どおりの結果が出ない場合はブックの保存状態や参照セルの指定など基本条件を一つずつ確認していきます。
- 新規ブックのまま保存していない
- 参照セルが別シートや別ブックを指している
- クラウドストレージ上で動作仕様が異なる
- 共有ブック設定による制約がある
- ブック名やパスに想定外の文字が含まれている
特にCELL関数が空白を返すケースではブックが未保存であることが多いため一度保存してから再計算し直すだけで解決することがよくあります。
クラウド環境で意図した値が得られないときはローカル版Excelで試して挙動を比較し自分の環境で利用可能な方法をあらためて選び直すと安心です。
VBAでExcelのシート名を取得して活用する方法
続いてマクロ有効な環境で使えるVBAを使ってシート名を取得する方法と全シート名の一覧作成など実務ですぐに役立つ活用アイデアを整理します。
ActiveSheetから現在のシート名を取得する
もっともシンプルな方法はActiveSheetのNameプロパティを参照して現在アクティブになっているシートの名前を文字列として取得するやり方です。
サンプルマクロでは変数を用意してActiveSheetのNameを代入しメッセージボックスで表示する流れを確認すると基本挙動を理解しやすくなります。
- オブジェクトActiveSheet
- プロパティName
- 戻り値アクティブシート名
- 利用例MsgBox ActiveSheet.Name
- 用途現在のシート名の確認など
Worksheetsコレクションで任意のシート名を取得する
特定のシートの名前やインデックス番号で指定したシート名を取得したい場合はWorksheetsコレクションのNameプロパティを利用します。
数値インデックスで指定すればブック内の並び順に応じて一番目や最後のシート名を取得でき名前で指定すれば特定シートの存在確認にも使えます。
| 取得方法 | 指定内容 |
|---|---|
| Worksheets(1).Name | 先頭シート名を取得 |
| Worksheets(Worksheets.Count).Name | 最後のシート名を取得 |
| Worksheets(“集計”).Name | 集計という名前のシート名を取得 |
| 変数での格納 | Dim nm As String など |
インデックス番号を使うとシート挿入や並び替えの影響を受けるため重要な処理では名前指定と組み合わせて意図したシートを扱えているかを確認すると安全です。
全シート名を一覧に出力するサンプル
ブック内の全シート名を一つのシートに一覧で出力して管理したい場合はFor EachループでWorksheetsコレクションを回しながらセルに書き込むマクロが便利です。
一覧を作っておけばシート数が多いブックでもどのようなシート構成になっているかを視覚的に把握でき名前の付け方の見直しにも役立ちます。
- ループ構文For Each ws In Worksheets
- 取得プロパティws.Name
- 出力先シート名一覧用シート
- 出力列例としてA列に縦並び
- 用途管理や簡易ドキュメント化
VBAでシート名を扱うときの注意点
VBAでシート名を操作する際は存在しないシート名を参照してエラーになるケースやユーザーが手動で名前を変更して想定外の動作になるケースに注意が必要です。
On Error構文でエラー処理を挟むかシートの存在を事前に確認する関数を用意してからNameプロパティにアクセスする設計にすると実務で壊れにくくなります。
| 注意ポイント | 概要 |
|---|---|
| 存在しないシート名 | 参照時にエラーが発生する |
| ユーザーの名称変更 | ハードコードした名前が無効になる |
| シートの削除 | ループや参照の前提が崩れる |
| 保護されたシート | 書き込みに制限がある場合がある |
シート名をロジックの中心に据える場合は変数や設定シートで管理し名称変更があっても簡単に修正できるよう設計しておくとメンテナンス性が高まります。
Power Queryや他機能でシート名一覧を扱うコツ
複数ブックや大量のシートを扱う場面ではPower Queryでシート名一覧をテーブルとして取得し関数やVBAと組み合わせて活用する考え方が有効です。
Power Queryで別ブックのシート一覧を取得する流れ
Power QueryではデータタブからExcelブックを指定して取り込むとそのブックに含まれるシートやテーブルや名前付き範囲の一覧をテーブルで取得できます。
まずはデータの取得からファイルを指定しその後に現れる一覧テーブルで必要な列だけを残しシート名や種類の列を基準にフィルターをかけます。
- データタブからデータの取得を選択
- Excelブックからを選択
- 対象ブックを指定してデータの変換を実行
- Power Queryエディターで一覧テーブルを確認
- シート名列だけを残してフィルター
M言語でシート情報テーブルを扱うイメージ
Power QueryのM言語ではExcel.Workbook関数を使ってブック内容を読み込み各行にシート名や種類やデータ本体を持つテーブルを返します。
そのテーブルに対してフィルターや列の削除を行うことでシート名一覧や必要なシートだけを対象としたテーブルを作ることができます。
| 列名の例 | 内容の例 |
|---|---|
| Name | シート名 |
| Kind | SheetやTableなどの種類 |
| Data | シートのデータ本体 |
| Item | ブック内の名前など |
シート名一覧を作るだけならName列とKind列だけを残してシート種別でフィルターすればシンプルなシート一覧テーブルを作成できます。
Power Queryと関数やVBAの使い分け
Power Queryは大量のファイルやシートを統合する処理に強い一方で個々のセルの見出しとしてシート名を表示したいだけなら関数やVBAの方が素直です。
定期的に同じフォルダ内のExcelファイルを集計するような処理ではPower Queryに取り込み設定を保存しておき再読み込みで最新状態に更新すると効率的です。
- 少数のシートで見出しに使うだけなら関数
- ループや条件分岐が多い場合はVBA
- 多ファイルや大量シートの統合はPower Query
- 他ツール連携が多い場合はPower Query優位
- 組織ポリシーに合わせて組み合わせる
シート名をキーにしたデータ結合アイデア
シート名に年月や部署名など意味のある情報を含めておきPower QueryやVBA側でそのシート名を分解すればデータの属性情報として活用できます。
たとえば売上一月というシート名から年月部分だけを切り出して日付列として追加したり部署コードを抽出して集計軸にしたりする応用が考えられます。
シート名取得のテクニックは単なる表示用途だけでなくデータの構造化や分類にも役立つためファイル設計段階からシート名ルールを決めておくと効果的です。
Excelでシート名を取得するテクニックの使い分けの考え方
Excelでシート名を取得する方法はCELL関数と文字列関数を組み合わせる数式による方法とVBAのNameプロパティを使う方法とPower Queryで一覧テーブルを扱う方法に大きく分けられます。
少数のシートで見出しや注釈としてシート名を自動表示したい程度なら数式だけで十分なことが多く複雑なロジックや大量シートの処理が必要になったらVBAやPower Queryを組み合わせると柔軟です。
自分の環境や組織のルールを踏まえてどこまでマクロやPower Queryを使えるのかを確認しExcelでシート名を取得する基本テクニックを組み合わせて実務フローに最も合う形に設計していきましょう。