【Excel】シート名とセルが連動するしくみづくり★

こんにちは、HARUです！

エリア別の販売実績や店舗別の会員リスト、部門別の予算管理表を作成する際に、情報のカテゴリごとにシートに分けて管理することがよくあります。

こんなとき、各ワークシートの見やすいところにそれぞれのシート名と同じ拠点名や部門名が載っていると、どのシートを閲覧しているかが一目瞭然です。

対象のシート数が数枚であれば直接入力していっても良いですが、管理対象のワークシートが20枚,30枚とあったり、シート名が都度更新されたりするブックの場合は相当時間がかかってしまいます。

そこでこの記事では、シート名が特定のセルに自動で反映される仕組みの作り方をご紹介します。

以前解説した文字列操作関数を活用した便利なテクニックですので、ぜひ最後までご一読ください！

↓投稿者のYouTubeチャンネルはこちらをチェック！↓

シート名とセルを連動させる

---

パスの取得

Excelには、参照したセルの情報を取得する関数があります。
その名の通り、CELL関数です。

①CELL関数を挿入する。
②第１引数「検査の種類」から"filename"を選択する。

③第２引数「参照」に、現在CELL関数を入力しているワークシート上のいずれかのセルを参照する。
※下図はA1セルを参照した状態。

これにより、ファイル名（シート名）を含む【パス】が取得できます。

CELL関数で取得したパスは、必ず[ブック名]のあとにシート名が続く構成となっています。

そのため、全体の文字数から"]"が位置するｎ文字目を差し引き、その文字数分を右から取り出せば、おのずとシート名だけが表示できるのです。

パスからシート名を取り出す

ファイルパスを取得したセルの近傍で必要な関数をテスト入力してみましょう。

①LEN関数でパス全体の文字数をカウントする。

パスの文字総数として"71"が返る。

②FIND関数で"]"の位置を調べる。

パスにおける"]"の位置として"65"が返る。

③RIGHT関数でパスの文字総数（71）から"]"の位置（65）を差し引いた（6）文字分を取り出す。

これにより、作業中のシート名「Sheet1」が返されます。

試しにシート名を「テスト用」に変更してみます。

RIGHT関数を入力したセルに返される文字列も「テスト用」に更新されます。

これら一連の手順を１つの数式に組み込むことで、シート名が特定のセルに自動反映される仕組みが構築できます。

シート名をセルに表示する

①RIGHT関数を挿入する。
②第１引数「文字列」に、CELL関数で取得したA1セルのパスを参照する。

③RIGHT関数の第２引数「文字数」に、LEN関数でカウントしたパスの総文字数からFIND関数で求めた"]"の位置を差し引く数式を入れ込む。

これにより、数式を入力したセルにシート名「首都圏支社」が返ります。

試しにシート名を「東京本店」に変更してみると、セルの表示も自動更新されます。

すべてのシートの該当セルにこの数式をコピーするだけで、それぞれのシート名が返されます。

なお、すべてのワークシートでシート名を表示するセル番地が同じであれば、対象のシートをあらかじめ選択（作業グループ化）してから数式を入力することで、数式をコピーするステップも省略できます。

セルの値をシート名に表示する

ここまでとは逆に、特定のセルに入力した値や文字列をシート名に表示される手順も参考までに解説しておきます。