HYPERLINK関数とSUBSTITUTE関数でURL一覧をつくる【Excel/関数】

Excel


今回、HTMLファイル一覧を元に、Excelの関数を使ってURL一覧を作成しました。
複数の関数を組み合わせての実装が難しかったので、忘れないようメモします。

完成したURL一覧を確認する

完成したExcelファイルがこちらです。
M列が関数で作成したURL、隣のL列が元になるHTMLファイル名、K1が共通のURL(ドメイン&ディレクトリ)です。

作成するURLの法則

作成したいURLの法則は次の通りです。ポイントはHTMLファイルの名前がディレクトリになる点です。

HYPERLINK関数でハイパーリンクを作成する

HYPERLINK関数を使えば、クリックしたリンク先が表示できるようになります。
関数の書式は次の通りです。別名を省略するとURLがそのまま表示されます。
今回、別名は必要ないので省略します。

=HYPERLINK([リンク先],[別名])

SUBSTITUTE関数でHTMLファイルの拡張子を置き換える

HTMLファイル名の拡張子「.html」を「/」に置き換えます。
関数SUBSTITUTEで文字列の置換ができます。
書式は次の通りです。最後の[置換対象]は、必要ないので省略します。

=SUBSTITUTE([文字列],[検索文字列],[置換文字列],[置換対象])

最終的に完成した関数はこちら

HYPERLINK関数とSUBSTITUTE関数を組み合わせたら、このような形になりました。

=HYPERLINK($K$1&SUBSTITUTE(L3,".html","/"))

「K1」は共通のURLが入力されたセルです。行と列を固定したいので、絶対参照($K$1)にします。
SUBSTITUTE関数で「L3」に入力されたHTMLファイル名の拡張子を「/」に置換します。

最後に共通URLと置換したHTMLファイル名を「&」で連結します。

まとめ

関数を組み合わせて使うと、いろいろなことができそうです。

コメント