【Excel】エクセルでシート名をセルに表示する(一覧:全部:簡単:一部:関数:エラーも)方法 | モアイライフ(more E life)
EXCELのYouTube始めました!

EXCEL初心者に向けたYouTubeチャンネルを開始しました(^^)/
ぜひチャンネル登録よろしくお願いします!

効率よくエクセルを学ぶ!

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

本サイトでは記事内に広告が含まれています

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

ポイントは、

 ・cell関数の活用

・GET.WORKBOOK関数の活用

ですね。 

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

 

スポンサーリンク

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

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

 

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

 

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

  

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

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

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

 

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

 

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

 

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

 

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

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

を返します。

 

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

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

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

 

 

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

 

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

Sheet2のB2セルに設定。

 

 

 

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

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

 

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

 

 

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

 

[名前]に任意の名前(今回のサンプルでは「シート名取得」)参照範囲に下の式設定してOKボタンします。

 

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

 

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

 

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

 

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

 

 

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

 

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

 

=INDEX([新しい名前]で設定した[名前],A1)

 

 

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

 

では、第1引数に「シート名取得」を、第2引数にA1セルを設定しています。

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

 

 

 

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

 

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

 

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

 

  

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

 

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

 

 

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

 

 

 

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

A列、B列の内容は削除しておきましょう