【Excel】エクセルでシート名をセルに表示する（一覧：全部：簡単：一部：関数：エラーも）方法

この記事では、【Excel】エクセルでシート名をセルに表示する（一覧：全部：簡単：一部：関数：エラーも）方法、について解説していきます。

ポイントは、

 ・cell関数の活用

・ＧET.ＷORＫBOOK関数の活用

ですね。 

それでは実際のサンプルを用いて、詳しい処理方法を見ていきましょう。

 

エクセルで一部のシート名をセルに表示する方法（一覧で指定セル範囲に）

以下のエクセルシートのサンプルのＡ１セルに当該シート名を表示します。

 

下の式を数式バーから設定します。CELL関数の第２引数は式を設定したセル位置を指定しています（下のサンプルではA1）。

 

=RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-SEARCH(“]”,CELL(“filename”,A1)))

  

数式の詳細を解説していきます。

CELL関数は、セルの書式、位置、または内容に関する情報を返します。

“filename”を第１引数に、今回は表示させたいセルを第２引数に指定することで対象範囲を含むファイル名（フルパスの文字列）、対象範囲を含むワークシートが保存されていなかった場合は、空白文字列を返します。

 

SEARCH関数は、指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返します。

 

LEN関数は、文字列の文字数を返します。

 

RIGHT関数は、文字列の末尾(右端)から指定された文字数の文字を返します。

 

例としてエクセルファイルsample.xlsxのsheet1がC:\Users\Public\Documentsに保存されていた時、CELL関数はシートのある場所のパスの文字列

C:\Users\Public\Documents\[sample.xlsx]sheet1

を返します。

 

LEN関数の戻り値のパスの文字数からSEARCH関数の戻り値の“]”の位置番号を引くことで、

RIGHT関数は、パスの終わりから“]”の次の位置までの文字列であるシート名を返します。

これらの処理で、シート名がセルに表示されます。

 

 

この方法でセルに式を設定しておくことでシート名を変更すれば、セルの内容も変更されるため、変更忘れがなくなります。下のサンプルではシート名を「Sheet10000」に変更したため、Ａ１セルの内容も変更されました。

 

他のシートの他のセル位置に設定した例です。

Sheet2のＢ２セルに設定。

 

 

 

エクセルで全部のシート名をセルに表示する方法（指定セル範囲に）

 以下のエクセルシートのサンプルのＡ１セルから全部のシート名を表示します。

 

エクセルの［数式］タブにある［名前の定義］を押します。

 

 

［新しい名前］のダイアログボックスが表示されます。

 

［名前］に任意の名前（今回のサンプルでは「シート名取得」）を、［参照範囲］に下の式を設定して［ＯＫ］ボタン押します。

 

=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

 

GET.WORKBOOKは引数に1を指定することでファイル一覧の文字列配列を返します。

 

例：[sample.xlsx]sheet1 [sample.xlsx]sheet2 [sample.xlsx]sheet3

 

REPLACE関数で[]で囲われたブック名を削除してシート名にしています。

 

 

［新しい名前］のダイアログボックスが閉じます。

 

Ａ１セルに数値の1を設定します。Ｂ１セルに下の式を数式バーから設定します。

 

=INDEX(［新しい名前］で設定した［名前］,A1)

 

 

INDEX関数はテーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。第１引数に先に［新しい名前］で設定した［名前］を、第２引数で配列の行番号を指定します。

 

下では、第１引数に「シート名取得」を、第２引数にＡ１セルを設定しています。

Ｂ１セルに式の結果としてＡ１セルの値である1に対応した１つ目のシート名が表示されます。

 

 

 

Ａ１セルとＢ１セルを選択し、枠の右下を下にドラッグしセルをコピーします。

 

Ａ２セルの値である2に対応した２つ目のシート名、Ａ３セルの値である3に対応した３つ目のシート名が表示されます。表示されるシートがなくなるとＢ列のセルがエラーになります。

 

これで全部のシート名を表示することができました。

 

  

［名前］で使っているマクロ関数のGET.WORKBOOKは、エクセルファイルでは保存できないので、保存する前に別のセルに表示されたシート名を値としてコピーしておきましょう。

 

シート名の表示されているセルを選択して、キーボードからショートカットキーの［Ctrl］＋［C］を押してコピーします。

 

 

コピー先のセルを選択（今回のサンプルではＣ１セルを選択）し、右クリックのメニューから［貼り付けオプション］の［値］を選択して押します。

 

 

 

これで全部のシート名が値としてコピーされました。

Ａ列、Ｂ列の内容は削除しておきましょう。

 

 

まとめ　エクセルでシート名をセルに表示する（簡単：一部：関数：エラーも）方法 

この記事ではエクセルでシート名をセルに表示する方法について解説しました。

 

エクセルでのさまざまな処理を理解し、業務に役立てていきましょう。