過去に「Excel 目次シート」記事で「目次ページ」の作り方をご紹介しました。
多くのシートを持つブックで、目次用のシートを設け、そこに各シートへのハイパーリンクを張った“目次”項目を列記するというものでした。
Excelには、Wordのように目次作成機能はありませんので、手作りで設けるわけです。
今回の話題は、その目次として使える各シートの名前一覧を（手書きではなく）作成する方法を考えてみます。
基本的な作業としては、先の「Excel 複数シートのデータをまとめる」記事でご紹介した「CELL」関数を使ったシート名の参照を使います。
また、「CELL」関数については「Excel シート名」記事でご紹介しています。
「CELL」関数の書式は
CELL(検査の種類, [対象範囲])
というふうに使います。
詳しくは、Microsoftサポートぺージ「CELL関数」をご参照いただきますが、第1引数である「検査の種類(info_type)」に「"filename"」を指定すると、そのブックのフルパス名とシート名が返されます。
（第2引数「対象範囲」はセルまたはセル範囲を指定するものです。）
例えば、「テーブル名」というシートの空いている場所に「=CELL("filename")」と入力すると、「C:\Users\(ユーザ名)\Desktop\[講師の引き出し(12).xlsx]テーブル名」と返ってきました。（下図）
最後の「]」より後ろがシート名で、それよりも前が「[ ]」で囲まれたファイル名を含むフルパス名です。
（Excelブックが名前を付けて保存されていないと空白文字列 ("")が返されます。）
ということは、最後の「]」よりも後ろを取り出せばシート名が得られるということです。
これを応用して目次を作ってみます。
「目次」シートを用意します。（下図）
上図の B4セルに「=CELL("filename",」と入力し、シート「テーブル名」を開いて A1セルを選択して [Enter]すると「=CELL("filename",テーブル名!A1)」と入力され、「C:\Users\(ユーザ名)\Desktop\[講師の引き出し(12).xlsx]テーブル名」と表示されます。
この表記からシート名だけにします。
改めて B4セルの数式を編集して「=RIGHT(CELL("filename",テーブル名!A1),LEN(CELL("filename",テーブル名!A1))-FIND("]",CELL("filename",テーブル名!A1)))」とします。
これで B4セルには「テーブル名」と表示されます。
次に、B4セル「テーブル名」をクリックすると、シート「テーブル名」の A1セルにジャンプするようにハイパーリンクを張ります。
これには 2つの方法があります。
1つは、「ハイパーリンクの挿入」ダイアログを使う方法です。
B4セルには「=RIGHT(CELL("filename",テーブル名!A1),LEN(CELL("filename",テーブル名!A1))-FIND("]",CELL("filename",テーブル名!A1)))」と入力され「テーブル名」と表示されているとします。
B4セルを選択し、[挿入]-[リンク]-[リンクの挿入]をクリックします。
（B4セルをマウス右クリックして [リンク]-[リンクの挿入]をクリック、または B4セルを選択し [Ctrl]+[K]キーを押してもよろしいです。）
「ハイパーリンクの挿入」ダイアログが開いたら、左側の「リンク先」で「このドキュメント内」を選択、シートリストから「テーブル名」を選択し [OK]を押します。（下図）
これで、B4セルには青字で下線付きの「テーブル名」と表示され、これをクリックするとシート「テーブル名」の A1セルにジャンプします。
なお、B4セルの下線が邪魔なら [Ctrl]＋[U]キーを押して取り去ることもできます。
もう 1つの方法、「HYPERLINK」関数を使う方法もご紹介しておきます。
「HYPERLINK」関数の書式は
HYPERLINK(リンク先, [別名])
というふうに使います。
こちらも、詳しくは、Microsoftサポートぺージ「HYPERLINK関数」をご参照いただきますが、第1引数にジャンプ先のシートのセルを指定し、第2引数は表示したい文字列を指定すればリンクを張ることができます。
B4セルに「=HYPERLINK("[講師の引き出し(12).xlsx]テーブル名!A1","テーブル名")」と入力すると、B4セルは青字で下線付きの「テーブル名」と表示され、これをクリックするとシート「テーブル名」の A1セルにジャンプします。
（ここでも、下線が邪魔なら [Ctrl]＋[U]キーで削除できます。）
「HYPERLINK」関数で別シートのセルにジャンプするには、同じブック内のシートであっても、このようにファイル名を指定する必要があります。
そして、この第2引数部分を、先の「CELL」関数でシート名を取り出す数式を適用します。
結果的に B4セルは「=HYPERLINK("[講師の引き出し(12).xlsx]テーブル名!A1",RIGHT(CELL("filename",テーブル名!A1),LEN(CELL("filename",テーブル名!A1))-FIND("]",CELL("filename",テーブル名!A1))))」となります。
この B4セルの数式中「[講師の引き出し(12).xlsx]」部分はこのブックのファイル名なので、上記 CELL関数を使えば参照できそうですね。
ちょっと長くなりますが、「=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename")),FIND("]",CELL("filename"))-FIND("[",CELL("filename"))+1)&"テーブル名!A1",RIGHT(CELL("filename",テーブル名!A1),LEN(CELL("filename",テーブル名!A1))-FIND("]",CELL("filename",テーブル名!A1))))」という感じになります。
今回の主題は、各シートの名前一覧を作成するということでしたが、結果的に数式の中に「テーブル名」という固有の名称が出てきています。
これの改良として、次回以降に続けます。