エクセルのシート名を全てのシートのセルに一括表示させる方法

エクセルのシート名を全てのシートのセルに一括表示させる方法 シート

皆が読んでいる! よく読まれている人気記事一覧

これは知らなかった!? 意外と知らないミニTips集

エクセルのシート名は何も変更していないと、Sheet1、Sheet2、Sheet3という名前が勝手につけられます。

でも、シート名には意味のある名前をつけるのが普通ですよね。

そして、名前をつけたシート名をそのシートのセルに表示させたい場合があります。

そういう時には、CELL関数とRIGHT関数などの文字列操作関数を組み合わせると、シートの任意のセルにシート名を表示させることができます。

今回は、セルにシート名を一括で表示させる方法を紹介したいと思います。

記事の内容は、以下の通りです。

この記事の内容

  • CELL関数、RIGHT関数などでシート名だけを切り出してセルに表示させる。
  • 切り出したシート名を全てのシートのセルに一括で表示させる。

エクセルのシート名を切り出して任意のセルに表示させる

これから紹介する方法では、シート名を任意のセルに表示させることができます。

まずは、1つのシートのセルにシート名を表示させてみせます。

 

シート名を表示させたいセルに、次の関数式を入力。

=RIGHT(CELL("filename", A1), LEN(CELL("filename", A1)) - FIND("]", CELL("filename", A1)))

 

エクセルのシート名を全てのシートのセルに一括表示させる方法

 

すると、セルにシート名が表示されます。

エクセルのシート名を全てのシートのセルに一括表示させる方法

入力した関数式については、後ほど詳しく解説します。

シート名を全てのシートのセルに一括で表示させる

シート名を表示させることができたので、シート名を表示させたいシート全てに一括表示させる方法です。

シート名を表示させる全てのシートを選択します。

適当なシート見出しをクリックし(①)、右クリックメニューから『すべてのシートを選択』をクリック。(②)

エクセルのシート名を全てのシートのセルに一括表示させる方法

 

これで全シートが選択できました。

エクセルのシート名を全てのシートのセルに一括表示させる方法

 

そして、シート名を表示させるセルをクリックし、さきほどと同じ関数式を入力します。

=RIGHT(CELL("filename", A1), LEN(CELL("filename", A1)) - FIND("]", CELL("filename", A1)))

エクセルのシート名を全てのシートのセルに一括表示させる方法

 

これで全シートに関数式が一括で入力できたので、セルにそれぞれのシート名が表示されます。

エクセルのシート名を全てのシートのセルに一括表示させる方法

エクセルのシート名を全てのシートのセルに一括表示させる方法

エクセルのシート名を全てのシートのセルに一括表示させる方法

シート名を切り出す関数式の解説

シート名を切り出す関数式についての詳しい解説です。

今まで入力してきた関数式の中で最も重要なのが、パス付きのシート名を取得するCELL関数です。

=CELL("filename", A1)

これを適当なセルに入力すると、例えば次のようなパス付きのシート名が表示されます。

C:\temp\[セルにシート名を表示させる.xlsx]シートA
この例では、エクセルファイルは『セルにシート名を表示させる.xlsx』という名前で、Cドライブのtempフォルダに保存しています。

 

これからわかる通り、CELL関数はエクセルファイルの保存場所+エクセルファイル名+シート名を返します。

そして、シート名を切り出すのに必要な関数が、RIGHT関数、FIND関数、LEN関数。

 

セルに入力してきた関数式は長いので、このように分解してみました。

エクセルのシート名を切り出す関数式

RIGHT関数は、指定の文字列の右側から指定する文字数分だけ切り出してくれます。

=RIGHT(文字列文字数

つまり、『C:\temp\[セルにシート名を表示させる.xlsx]シートA』の文字列から『シートA』だけを切り出そうとしているわけです。

 

そうした時に、切り出す文字数となるシート名の長さを求めないといけません。

そして、これがシート名の長さを求める数式です。

=LEN(CELL(“filename”, A1))FIND(“]”, CELL(“filename”, A1))

LEN(CELL(“filename”, A1))は『パス付きのシート名』の長さを求める関数。

FIND(“]”, CELL(“filename”, A1)は『パス付きのシート名』の中にある『]』を探してその位置を返す関数。

つまり、『C:\temp\[セルにシート名を表示させる.xlsx]シートA』の長さからFIND関数が返す『]』の位置を引くことで、『シートA』の長さが求まることになります。

ちょっとややこしかったですか^^

まとめると、『パス付きのシート名』から赤い下線部分のシート名(シートA)だけを切り出そうとしているわけです。

C:\temp\[セルにシート名を表示させる.xlsx]シートA

まとめ

まとめ

  • CELL関数、RIGHT関数、LEN関数、FIND関数を組み合わせることで、シート名を切り出すことができる。
  • シートを複数選択した後に関数式を入力すると、全シートに一括でセルを表示させることができる。

今回紹介した関数式を使うと、簡単にシート名をセルに表示させることができます。

既に大量のシートを作っていて、全てのシートにシート名を表示させたい場合は、対象のシートを選択してから関数式を入力すると、一括でセルに入力することができてかなり手間が省けます。

膨大なシートがあるエクセルブックでは、効率よくシート名を表示させる方法なので、ぜひ利用してください。

タイトルとURLをコピーしました