【エクセル関数】シート名を自動で取得する方法
この記事はこんな人に向けた内容です。
- シート名が毎月変わるので困っている
- まとめシートで集計したい
- シート名を自動取得する方法が知りたい
エクセルでシート名を自動で取得する方法を紹介します。
さらに、シート名を利用して各シートのデータを取得する方法も併せて紹介します。
シート名を自動取得する方法
シート名はエクセルの「名前の定義」と数式をエクセル関数を組み合わせることで、自動取得することができます。やり方を順番に説明していきます。
①数式タブの名前の定義ボタンを押す
![f:id:hamahiro881477:20200923001131j:plain f:id:hamahiro881477:20200923001131j:plain](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hamahiro881477/20200923/20200923001131.jpg)
②名前の定義を行う(book)
![f:id:hamahiro881477:20200923001216j:plain](https://yaromai.jp/wprs/wp-content/uploads/2021/02/d145ddaef08c0084c76c38915790f378.jpg)
上の画像のように名前の部分には「book」を参照範囲には「=GET.WORKBOOK(1)」と入力してOKボタンを押します。
③名前の定義を行う(doc)
![f:id:hamahiro881477:20200923001419j:plain f:id:hamahiro881477:20200923001419j:plain](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hamahiro881477/20200923/20200923001419.jpg)
もう一度「名前の定義」ボタンを押し、同じように名前の定義を行います。
上の画像のように名前の部分には「doc」を参照範囲には「=GET.DOCUMENT(88)」と入力してOKボタンを押します。
これで下準備は完了です。
④エクセル関数を入力する
好きなセルに「=SUBSTITUTE(INDEX(book,1),"[“&doc&"]","")」と入力してみてください。すると、一番左のシートのシート名が表示されます。
関数の中の1の部分がシートのインデックスとなっていて、ここを2とすると左から2番目のシートのシート名が表示されます。
![f:id:hamahiro881477:20200923001758j:plain f:id:hamahiro881477:20200923001758j:plain](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hamahiro881477/20200923/20200923001758.jpg)
![f:id:hamahiro881477:20200923001816j:plain f:id:hamahiro881477:20200923001816j:plain](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hamahiro881477/20200923/20200923001816.jpg)
1の部分を変更して、A1としておけば、上の画像のように一気に全シートのシート名を取得することができます。
【注意】この関数は再計算実行では更新されません。
関数を更新したい場合は、入力した関数に対してオートフィルを行いましょう。
各シートのデータを自動取得する方法
ここまでシート名を自動で取得する方法について説明してきました。
さらにこのシート名を利用すれば、各シートのデータを自動取得することもできます。
![f:id:hamahiro881477:20200923002315j:plain f:id:hamahiro881477:20200923002315j:plain](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hamahiro881477/20200923/20200923002315.jpg)
先ほど作成した表の隣に、「=INDIRECT(B2 & “!A1")」と打ち込みます。
すると、4月シートのA1セルの情報を取得することができます。
INDIRECT関数は文字列を組み合わせる関数で、「=’4月’!A1」と同じことをしています。
※これは4月シートのA1セルを参照せよという指示になります。
例えば、各シートのA1セルに合計値が来るようにしておけば、各シートの合計値を簡単にまとめシートに転記することができます。
保存はマクロ有効ブックで
「名前の定義」を使用したため、通常のエクセルブックとして保存しようとすると、下画像のようなメッセージが出てきます。
![f:id:hamahiro881477:20200923002932j:plain f:id:hamahiro881477:20200923002932j:plain](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hamahiro881477/20200923/20200923002932.jpg)
メッセージに従って、マクロ有効ブックで保存をするようにしてください。