やろまい

楽しいことと役に立つことを紹介します。プログラム書いたり、簿記を学んだり、資産運用も少し

はじめましての人へ(サイト紹介)
ツイッター→ 招き猫ファイナンス

【エクセル関数】シート名を自動で取得する方法

この記事はこんな人に向けた内容です。

  • シート名が毎月変わるので困っている
  • まとめシートで集計したい
  • シート名を自動取得する方法が知りたい

 

エクセルでシート名を自動で取得する方法を紹介します。

さらに、シート名を利用して各シートのデータを取得する方法も併せて紹介します。

 

シート名を自動取得する方法

 シート名はエクセルの「名前の定義」と数式をエクセル関数を組み合わせることで、自動取得することができます。やり方を順番に説明していきます。

 

①数式タブの名前の定義ボタンを押す 

f:id:hamahiro881477:20200923001131j:plain

 

②名前の定義を行う(book)

f:id:hamahiro881477:20200923001216j:plain

上の画像のように名前の部分には「book」を参照範囲には「=GET.WORKBOOK(1)」と入力してOKボタンを押します。

 

③名前の定義を行う(doc)

f:id:hamahiro881477:20200923001419j:plain

もう一度「名前の定義」ボタンを押し、同じように名前の定義を行います。

上の画像のように名前の部分には「doc」を参照範囲には「=GET.DOCUMENT(88)」と入力してOKボタンを押します。

 

これで下準備は完了です。

 

④エクセル関数を入力する

好きなセルに「=SUBSTITUTE(INDEX(book,1),"["&doc&"]","")」と入力してみてください。すると、一番左のシートのシート名が表示されます。

 

関数の中の1の部分がシートのインデックスとなっていて、ここを2とすると左から2番目のシートのシート名が表示されます。

 

f:id:hamahiro881477:20200923001758j:plain

f:id:hamahiro881477:20200923001816j:plain

1の部分を変更して、A1としておけば、上の画像のように一気に全シートのシート名を取得することができます。

 

【注意】この関数は再計算実行では更新されません。

関数を更新したい場合は、入力した関数に対してオートフィルを行いましょう。

 

各シートのデータを自動取得する方法

ここまでシート名を自動で取得する方法について説明してきました。

さらにこのシート名を利用すれば、各シートのデータを自動取得することもできます。

 

f:id:hamahiro881477:20200923002315j:plain

先ほど作成した表の隣に、「=INDIRECT(B2 & "!A1")」と打ち込みます。

すると、4月シートのA1セルの情報を取得することができます。

 

INDIRECT関数は文字列を組み合わせる関数で、「='4月'!A1」と同じことをしています。

※これは4月シートのA1セルを参照せよという指示になります。

 

例えば、各シートのA1セルに合計値が来るようにしておけば、各シートの合計値を簡単にまとめシートに転記することができます。

 

保存はマクロ有効ブックで

 「名前の定義」を使用したため、通常のエクセルブックとして保存しようとすると、下画像のようなメッセージが出てきます。

f:id:hamahiro881477:20200923002932j:plain

メッセージに従って、マクロ有効ブックで保存をするようにしてください。

 

エクセルの使い方を勉強したい人は、株式会社すごい改善のこの本を読んでみてください。

見える世界が一気に変わりますよ。

 

エクセル関数について勉強したい人はこの本がオススメです。

エクセルの全関数について、網羅的に学ぶことができます。

パソコンの隣に辞書代わりに置くのがオススメです。