「Excel Tips」ではExcelを使用していて気付いたことを取り上げています。
今回はExcelでワークシートをコピーしてもリンク設定を再設定しなくても済むための方法をご紹介いたします。
ただし特別なリンク設定の方法がある訳ではなく、HYPERLINK関数などを使用して面倒くさい数式を作る話になるのであらかじめお含み置きいただければ幸いです。
32bit版Excel 2016の バージョン2205(ビルド 15225.20204)を使用して検証しています。
リンク設定をセル範囲で指定する
本題から一旦離れますが、設定したリンク設定が「うまく動かない(遷移しない)」という経験をした事は無いでしょうか?
例えば左図のようにF5セルのリンク設定で、薄青色の背景色を付けたL15セルへのセル参照を指定した場合
F5セルのリンクをクリックしても…
画面はそのままでカーソル位置だけがL15セルに移動する事になります。
この場合に期待する動き方としては、「L15セルが画面左上の位置にくるように画面遷移する」と言う動きではないでしょうか?
そこで、リンク設定でセル参照をL15セルからL15:X30のセル範囲に変更します。
F5セルのリンクをクリックすると…
L15セルが画面左上の位置にくるように画面が遷移しました。
ただお気づきのように画面全体がセル選択された状態になってしまうのが、このやり方の欠点です。
「これは嫌だ!」という場合はセル範囲の始点と反対の終点セルを指定する事で対応される場合もあるかもしれません。
ただしこの場合は、画面拡大やズーム操作などによって画面の表示範囲が変化すると、終点セルが左上の位置に来なくなってしまいます。
例えばF6セルに終点セルW29をリンク設定のセル参照に指定します。
画面左上の位置にL15セルが来るようにできました。
画面サイズを大きくした場合の動きを確認します。
残念ながら設定したW29が画面右下にくるように遷移してしまいます。
セル範囲の場合でも画面サイズがセル範囲よりも大きくなるように変化した時は、上記と同じ現象が起きます。
ただしセル範囲の場合は、リンク設定のセル参照で指定するセル範囲を前もって十分に大きく設定しておく事ができるので、この現象を回避する事ができます。
指定したセル範囲が画面サイズよりも大きい場合は、セル範囲の始点が画面左上の位置に来るように遷移します。
セル範囲をL15:AH38と大きく設定しています。
画面サイズよりも十分大きければL15セルは画面左上に遷移します。
画面サイズが固定ではく、いろいろな使い方(例えばプロジェクターに投影するなど)が想定されるケースでは有効な方法になるかもしれません。
ワークシートコピーした時のリンク設定の動き方
前章で使用したSheet1シートをデフォルトの名前「Sheet1 (2)」でワークシートコピーします。
この「Sheet1 (2)」のリンクをクリックするとSheet1のセル参照先に遷移します。
ハイパーリンクの編集で見てみると確かに赤枠でくくった「Sheet1 (2)」ではなくSheet1が設定されている事が分かります。
なお赤枠のシート名がシングルコーテーション(‘)でくくられている事は注意が必要です。
実は後段でご説明する時にこの話がでてきますのでご記憶に留めていただければ幸いです。
話を戻しますが、ワークシートコピーをした時にリンク先をコピーしたワークシートの中のセル参照先にするためには、個々のリンク設定のセル参照先のワークシートを設定し直す必要があります。
リンク設定の数が少なかったりワークシートコピーをする頻度が少ないのであれば手で設定し直す事になるかと思いますが、そうでない時は何か他の方法が無いか?と思われる事と存じます。
これを「数式を使って解決する」というのが今回のテーマになります。
HYPERLINK関数で井桁記号(#)を使用する
実は3年ほど前に「Excelで動的にシート内ハイパーリンクを設定する」という記事の中でも取り上げているのですが、HYPERLINK関数のリンク先に井桁記号(#)をつけたセル参照を指定してリンクさせる事ができます。
Sheet1のF8セルにL15:X30をセル範囲とする次の数式を入力します。
=HYPERLINK("#L15:X30")
Sheet1をワークシートコピーして「Sheet1 (2)」を作ります。
「Sheet1 (2)」のF8セルのリンクをクリックすると、「Sheet1 (2)」ワークシートのセル範囲L15:X30に遷移します。
このやり方で問題なく動く事が確認できたので、別に「面倒くさい数式という程ではない」と思われる事と存じます。
ただ今回のテーマの最終ゴールは実はこれではありません。ある意味ここまでが前段で、ここから本題に入るイメージです。
HYPERLINK関数で井桁記号(#)を使用しない場合の数式
記事テーマの課題は解決されたので、「面倒くさい数式は興味がない」という方はこの章を読み飛ばしていただければ幸いです。
MicrosoftドキュメントにおけるHYPERLINK関数の説明と井桁記号の意味
以下にMicrosoft OfficeサポートのHYPERLINK関数のページをご紹介します。
ご覧いただくとお分かりになるように、実は現在のHYPERLINK関数の使用法に井桁記号(#)の説明はありません。
そのためGoogle検索したところ下記2005年2月の「インストラクターのネタ帳」のページにたどり着きました。
このページの説明では井桁記号(#)はHTML言語の同一ページ内アンカータグの機能と同じように書かれています。
という事は井桁記号(#)は「OfficeがXML化する前のバージョンでサポートされていたもので、今もまだ機能停止されずに残っている」状況であると推測致します。
「十数年経ってもそのまま残っているのであれば今後も大丈夫では?」と言えるかもしれませんが、今回は井桁記号(#)を使用しない場合の数式について考えて見る事にします。
HYPERLINK関数で同一シート内のセル参照を指定するには?
Microsoft OfficeサポートのHYPERLINK関数の説明を見ると次のような例が示されています。
現在のワークシートの別の場所にジャンプするには(Sheet1 が現在のワークシートの場合)、このようにブックの名前とワークシートの名前の両方を含めます。
=HYPERLINK(“[Book1.xlsx]Sheet1!A10″,”Go to Sheet1 > A10”)
同一シート内であるにも関わらず「ブックの名前」が必要というのは違和感がありますが、書式としては下記のようになる認識です。
=HYPERLINK("[ブック名]'シート名'!セル参照")
シート名をシングルコーテーション(‘)でくくっているのは前段でご紹介した注意点に対応するためです。
なお実際に試して見ていただけば分かるのですがシングルコーテーション(‘)を付けないシート名「Sheet1 (2)」の場合は、リンクをクリックすると「参照が正しくありません」というメッセージが表示されて遷移しません。
書式が分かれば後は「[ブック名]’シート名’」の形にデータを加工するだけなのですが、数式としては面倒くさい話になります。
CELL(“filename”)関数からブック名とアクティブなワークシート名を取得する
Microsoft Officeサポートの下記のページに必要な数式が掲載されています。
ただ、この数式の品質に問題があるために世の中に正しい数式を掲載しているページが存在しています。事程左様にGoogleで「excel シート名 取得」を検索すると複数のページが見つかります。
CELL(“filename”)関数はブックのドライブ・ルートフォルダーからの絶対パスを含めて取得するので、そこから「[ブック名]’シート名’」を抜き出せば良いのですが、残念なことにCELL(“filename”)関数はシート名にシングルコーテーション(‘)を付けて出力してくれません。
そのために、まず「[ブック名]シート名」を取り出して、つぎにシート名にシングルコーテーション(‘)を付与する必要があります。
セルに数式を入力する場合は次の2つの数式を2つのセルで使用することになります。
①CELL(“filename”)関数の返り値から「[ブック名]シート名」を取り出す数式
「ドライブ・ルートフォルダーからの絶対パス」を”[“を目印にしてレングスを計算してRIGHT関数で取り出します。
CELL(“filename”)関数の第2パラメータを指定しないとアクティブなシート名ではないシート名が返されるようです。このセル参照は相対参照でも良いのですがセルに依存する訳ではないので、絶対参照で$A$1をセットするようにしてありますのでお含み置きください。
=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))+1)
②取り出された値のシート名にシングルコーテーション(‘)を付与する数式
次に①で取り出された値から”]”を目印にしてシート名を取り出して、シート名の前後にシングルコーテーション(‘)を付与します。
なお下記の数式は①がB10セルに入力してある場合になります。
=LEFT(B10,FIND("]",B10))&"'"&RIGHT(B10,LEN(B10)-FIND("]",B10))&"'"
以下に実際の画面キャプチャでご説明します。
B10セルに①の数式を入力します。
B11セルに②の数式を入力します。
F10セルに次のHYPERLINK関数を入力します。
=HYPERLINK(B11&"!L15:X30")
Sheet1をコピーして「Sheet1 (2)」を作り、F10セルのリンクをクリックします。
コピーした時点でB10セル、B11セルの内容は「Sheet1 (2)」に書き換わっているので、「Sheet1 (2)」に正しく遷移します。
名前の定義に「[ブック名]’シート名’」を取得する数式を登録する
特定のセルに数式を入力する形で①と②の説明をしてきましたが、特定のセルを設定したくない場合もある事と存じます。
この場合「名前の定義に数式を登録する」事ができます。
名前の定義を2つ使う事もできますが、どうせ数式が隠蔽されて見えなくなってしまうので、ここでは①と②を1つにまとめた思いっ切り面倒くさい数式で名前に定義する事にします。
②では①の結果を5ヶ所で使用していますので、この部分を①で置き換えた数式を作ることになります。
=LEFT(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))+1),FIND("]",RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))+1)))&"'"&RIGHT(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))+1),LEN(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))+1))-FIND("]",RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))+1)))&"'"
「数式」リボンタブから名前の定義をクリックすると「新しい名前」ウインドウが開きます。
名前は「_LINKSHEET」にして、範囲は「ブック」、参照範囲に上記の長い数式を貼り付けてOKボタンをクリックします。
これで名前の定義は完了です。次にこの名前を使った数式を入力します。
F10セルに次の数式を入力します。
=HYPERLINK(_LINKSHEET&"!L15:X30")
※登録した名前「_LINKSHEET」は入力時に提案されます。
Sheet1をコピーして「Sheet1 (2)」を作り、F10セルのリンクをクリックします。
コピーした時点でF10セルの内容は「Sheet1 (2)」に書き換わっているので、「Sheet1 (2)」に正しく遷移します。
これで動きとしては正しい事が分かりました。
ただしタブメニュー「数式」から「名前の管理」を確認すると次のようになっています。
ワークシートコピーをすると、思いっ切り面倒くさい数式の名前の定義が「範囲」がコピーしたワークシートになって自動的に追加されています。
残念ながらちょっとこれでは実用には耐えられない認識です。
最後に
Excelでワークシートをコピーしてもリンク設定を再設定しなくても済むための方法としての数式を見てきました。
現時点では「ダントツで井桁記号を使った数式」になるのでは無いでしょうか?
その他のやり方では面倒くさい事が多すぎます。
ただし「井桁記号は過去の機能として現状のドキュメントには何も書かれていない」、つまりは将来的には保証しないという事であれば、「[ブック名]’シート名’」を取得するための関数をMicrosoft Officeとしてご提供いただくべきではないでしょうか?
「[ブック名]’シート名’」を取得するための関数があれば、井桁記号記号は使わなくて済む認識です。
以上最後までご一読いただき誠にありがとうございました。
