Excelでハイパーリンクが変化する表を作りたい

お尋ねします。
添付したような表で、たとえばB2セルに、https://www.aaa.com/1/ と入力します。
（ハイパーリンクアドレスも https://www.aaa.com/1/ となります。）
これをB3:B5セルにドラッグコピーすると、黄色のようになります。

ただ、セルのデータは黄色のとおりですが、
ハイパーリンクは、C3:C5セルに記載したように https://www.aaa.com/1/ のままです。
B3:B5のハイパーリンク先を、セルの表示のとおりにすることはできないでしょうか？

HYPERLINK関数を使って、B2セルをたとえば、
=HYPERLINK("https://www.aaa.com/(B4-1)/",A4) としましたが、
ドラッグコピーしても、B5:B7のセルの内容のB4の部分はB5,B6,B7と変化しません。
どなたかお知恵をお貸しください。 よろしくお願いいたします。

質問者からの補足コメント

• 

  3名の方に回答をいただき解決したと思っていました。
  実際、enunokokoro さんにご教示いただいた方法で、
  HYPERLINKの第1引数の相対セルは可変となりました。
  ところが、実際にハイパーリンクアドレスは変わっていませんでした。
  まだ、どこか理解不足のところがあるのでしょうか。
  どなたか追加でご教示をお願いいたします。

    補足日時：2023/05/03 22:13

• 

  ちなみに、質問を簡潔にするために添付のような表を
  題材としましたが、実際に解決したいのは以下のような記述です。
  （改めて見ると、固定部分と可変部分をちゃんと分けていました。）
  
  これは、グループの計画表から関連するカレンダーを
  個人のGoogleカレンダーに設定するためのリンクになります。

    補足日時：2023/05/03 22:14

• 

  =HYPERLINK("http://www.google.com/calendar/event?action=TEMP …

    補足日時：2023/05/03 22:14

• www.google.com/calendar/event?action=TEMPLATE&text="&E12&"&location="&F12&"&details="&G12&"&dates="&TEXT(B12,"yyyy")&TEXT(B12,"mm")&TEXT(B12,"dd")&"T"&LEFT(B13,2)&MID(B13,4,2)&"00/"&TEXT(B12,"yyyy")&TEXT(B12,"mm")&TEXT(B12,"dd")&"T"&MID(B13,7,2)&MID(B13,10,2)&"00",E12)

    補足日時：2023/05/03 22:15

• 

  補足画像です。
  セルのデータは、変化していますが、
  ハイパーリンクアドレスは、元セルのアドレスから変化していません。

  
    補足日時：2023/05/04 10:05

No.5 ベストアンサー 回答者： goomania 回答日時： 2023/05/04 13:48

ご質問者は「セルのリンク設定」と「HYPERLINK関数によるリンク」とを混同されているようです。

「HYPERLINK関数によるリンク」はセルの右クリックによる［ハイパーリンクの編集］で確認することはできません。
「HYPERLINK関数によるリンク」を記述すると、セルの右クリックでは［ハイパーリンクの解除］だけが表示されるはずです。

ところが、ご質問者は、
>右クリックの［ハイパーリンクの編集］で確認すると、
>ハイパーリンクアドレスは、https://www.aaa.com/1/のままなのです。
と仰っています。

というとは、予めセルのリンク設定で「https://www.aaa.com/1/」へのリンクが設定されているセルに、「HYPERLINK関数」で"https://www.aaa.com/"&ROW(A2)&"/"へのリンクを書き込んでいるということになります。

さて、この異なるリンクが設定されたセルをクリックすると、どちらのアドレスへリンクするのでしょうか?
実際に試したところ、「HYPERLINK関数のリンク先」ではなく「セルのリンク設定」へリンクしてしまうようです。

従って、ご質問者の希望を実現するためには、予め設定されているセルのリンク設定を全て解除する必要があります。

対象セルを纏めて選択してハイパーリンクの解除を行ってもよいですし、セルの右クリック［ハイパーリンクの編集］でhttps://www.aaa.com/1/が表示されるのなら、[リンクの解除]でこれを解除してもよいと思います。

ただ、この状態だとHYPERLINK関数を記述したセルは、一般にEXCELでリンクがあることを示す「青色文字」「アンダーバー」が表示されません。表示されなくてもリンクは有効に機能しますので、操作上の問題はないのですが、標準的な表示にするなら、F2キー、ENTERキーと押せば「青色文字」「アンダーバー」表示になります。

リンクが正しく修正されたかどうかを確認するには、セルのマスウオンでリンク操作の説明が表示されますが、実際のリンク先が表示されるはずです。

この回答へのお礼

goomania さん、
再びごていねいなアドバイスありがとうございます。

> ご質問者は
> 「セルのリンク設定」と「HYPERLINK関数によるリンク」
> とを混同されているようです。
そういうことなんですか。

セルのリンク設定をした訳ではなかったのですが、
デフォルトで設定されており、
それが問題だとは気づきませんでした。

ご教示のとおり、[リンクの解除]をたうえで、
ドラッグコピーしたら今度は本当に問題が解決しました。
確かにリンクが張られていることが明示されなくなりますが、
セルの書式変更で青文字・下線とすることで、
見かけ上、リンクありとすることができました。

3番目のお礼で書きましたベストアンサーの件は撤回し、
goomania さんへ差し上げます。ありがとうございました。

お礼日時：2023/05/04 22:18

No.6 回答者： enunokokoro 回答日時： 2023/05/04 14:51

HYPERLINK関数を使ったのに、実際のリンクが変わらない原因が

分かりました。右クリックでの[ハイパーリンクの編集]で確認する
ことができるのが原因です。

ハイパーリンクをしていない空白セルに
　=HYPERLINK("https://www.aaa.com/"&ROW(A1)&"/")
を、ここからコピーして貼り付けてリンクを確定します。
これを下へとドラッグして連続した表示になった場合に、上記の
右クリックでの[ハイパーリンクの編集]は出ないはずです。

ハイパーリンクの項目が有効になっていることがおかしいので、
元々設定がされていたハイパーリンクを上書きしたことで、元の
ハイパーリンクをそのまま維持して下のセルへドラッグしたため
[ハイパーリンクの編集]が有効になり、同じリンク先になって、
質問と同じ状態となるのです。

一度、右クリックで[ハイパーリンクの削除]でリンク情報を解除
しておいてから、新たにHYPERLINK関数を使った数式を設定し
新しいハイパーリンクとして用意してください。
これなら、[ハイパーリンクの編集]などのハイパーリンク関係が
有効にならずに、HYPERLINK関数としてのリンク先が正常へと
切り替わってくれるはずです。

この回答へのお礼

enunokokoro さん、
何度もアドバイスありがとうございます。

ご指摘のとおり、
当初HYPERLINK関数を使わずにあれこれ試し、その後、
HYPERLINK関数を使ったらどうかと修正し始めた結果、
HYPERLINKとセルのリンク設定がダブった状態で、
後者が優先されてしまっていたということですね。
大変勉強になりました。改めて感謝いたします。

お礼日時：2023/05/04 22:24

No.4 回答者： enunokokoro 回答日時： 2023/05/03 23:42

助長な部分がありますね。

TEXT(B12,"yyyy")&TEXT(B12,"mm")&TEXT(B12,"dd")
これは、
TEXT(B12,"yyyymmdd")としても同じことです。

補足の部分を分かりやすく、区切ってみました。
"www.google.com/calendar/event?"&
"action=TEMPLATE"&
"&text="&E12&
"&location="&F12&
"&details="&G12&
"&dates="
&TEXT(B12,"yyyymmdd")&"T"&LEFT(B13,2)&MID(B13,4,2)&"00/"
&TEXT(B12,"yyyymmdd")&"T"&MID(B13,7,2)&MID(B13,10,2)&"00"
,E12)

B12は、今日の日付として2023/5/3
B13は、文字列として○○-△△-□□-＿◇◇
これを前提にすると
dates=20230503T○○△△00/20230503T□□◇◇00
となるのですよね？

これだとB12セルとB13セルが続いているセルなので、ドラッグでの
変わる部分がおかしくなるのでは？
個々の参照部分をリンクとは別に、どこかのセルへと書き出せば、
正しいデータなのか判断付くのでは。

この回答へのお礼

enunokokoro さん、再びありがとうございます。
私がやりたかったことに対して、
ていねいなアドバイスありがとうございます。
TEXT(B12,"yyyymmdd")としなかったのは、
当初このようにしたのですが、
どうしてもTEXT関数がうまく機能せず、
理由は分からないのですが、
分割したらうまくいったので、このようになっています。

ベストアンサーを差し上げようと思ったのですが、
元のB2セルが
=HYPERLINK("https://www.aaa.com/"&ROW(A1)&"/")
をドラッグコピーすると、セルの内容は
=HYPERLINK("https://www.aaa.com/"&ROW(A2)&"/")
と変化するのですが、
右クリックの［ハイパーリンクの編集］で確認すると、
ハイパーリンクアドレスは、
https://www.aaa.com/1/
のままなのです。（補足画像参照）
どういうことなのでしょうか？

お礼日時：2023/05/04 10:04

No.3 回答者： goomania 回答日時： 2023/05/03 13:22

ご質問者が記述している数式

=HYPERLINK("https://www.aaa.com/(B4-1)/",A4)

は、enunokokoroさんのご指摘のとおり、HYPERLINK関数の引数の扱い方に問題があります。

HYPERLINK関数の第一引数、第二引数は文字列である必要があります。ダブルコーテーションで囲まれた範囲は「文字列」を意味するため、"https://www.aaa.com/(B4-1)/"という記述にすると「(B4-1)」も固定した文字列になってしまいます。このため、オートフィル等で変化しません。
従って(B4-1)部分を変化させたいなら、そこは変数として扱う必要があります。

リンク先の別前がA列にあるとして、B2に「https//:～/1」からリンク先を指定するなら、B2に記述する数式を

=HYPERLINK("https://www.aaa.com/" & ROW(A1) & "/",A2)･･･①

または

=HYPERLINK("https://www.aaa.com/" & ROW()-1 & "/",A2)･･･②

のような数式にすれば、ドラッグコピーすると、実際のリンク先のURLの「/1/」の部分が「/2/」「/3/」･･･と変化するようになります。

「/1/」の部分を1からではなく、任意の数値にしたいときは、①の数式なら
ROW(A1)のA1の部分を任意の行数、例えばA100とすれば「/100/」からスタートさせることができますし、②の数式で同様に「/100/」からスタートするには、ROW()-1の部分をROW()+98とすればよいということになります。

この回答へのお礼

goomania さん、回答をお寄せいただき感謝します。
” ”ではさめば、確かに文字列の扱いですから、
固定部分になるのですね。
今回は、先に解決策をご教示してくれた
enunokokoro さんにベストアンサーを差し上げます。
ご了承ください。

お礼日時：2023/05/03 21:33

No.2 回答者： iw_steel 回答日時： 2023/05/03 10:35

Ctrlドラッグです。

この回答へのお礼

iw_steel さん、回答ありがとうございました。
残念ながら解決には至りませんでした。

お礼日時：2023/05/03 21:29

No.1 回答者： enunokokoro 回答日時： 2023/05/03 08:38

HYPERLINK関数の引数の使い方が間違っています。

HYPERLINK(リンク先, [別名])で、二重引用符で囲ったリンク先の
部分は固定部分です。リンク先に固定の部分と変化する部分で分け、
＆などでつなげることで、変化するリンクとなります。
B2セルに、以下の数式を入れて下にドラッグします。
=HYPERLINK("https://www.aaa.com/"&ROW(A1)&"/")

別案：
ハイパーリンクしていない文字列としてのURLがある列を用意し、
その列をHYPERLINK関数のリンク先として参照する方法もでも、
問題なく動作します。（その列ではオートフィルで連番）
https://kokodane.com/2013_kan_067.htm

どちらでも動作しますので、使いやすいほうを選んでください。

この回答へのお礼

enunokokoro さん、早速の回答ありがとうございます。
なるほど、double-quotation markではさまれた部分は
固定になるのですね。
&ではさんで可変部分を作ればOKと。
気がつきませんでした。
ご紹介いただいた別案にも、
同じ課題に対する解があるようですね。
こちらも後ほど勉強してみます。
ありがとうございました。

なお、質問向けに書いた(B4-1)は誤記でした。
ご教示いただいたROW(A1)の意味でした。

お礼日時：2023/05/03 21:28