#ENCODEURL関数について

何故か知名度が低いExcelのENCODEURL関数とURLに使ってはいけない文字について解説。

Excel 2013以降から文字列をURLエンコードするENCODEURLという関数が追加された。

ENCODEURL(文字列)
URL 形式でエンコードされた文字列を返します。
ENCODEURL 関数 - Excel
注: ENCODEURL 関数は Excel Online と Excel 2016 for Mac では利用できません。

ENCODEURL関数は、URL内にそのまま入力すると問題を起こす文字（＝URLとしての予約語や非推奨文字）をパーセントエンコーディングして、無害化してくれる便利な関数である。

例えば、以下のような数式を作成すれば、Google検索用のリンク表を作成することができる。

=HYPERLINK("https://www.google.co.jp/search?q="&ENCODEURL([[検索したい語句]]))

HYPERLINK(リンク先)
ネットワークサーバー、イントラネット、またはインターネット上に保存されているドキュメントを開くために、ショートカットまたはジャンプを作成します。HYPERLINK 関数が設定されているセルをクリックすると、リンク先に指定した場所に保存されているファイルが開かれます。
HYPERLINK 関数 - Office のサポート

また、以下のようにENCODEURL関数を二重にすれば、二重にエンコードすることができる。

=ENCODEURL(ENCODEURL([[URLエンコードしたい語句]]))

##使用例：Googleアナリティクスのトラッキングに利用する場合
Web分析ツール「Googleアナリティクス」用にリンク先URLのGETパラメータにトラッキングパラメータを付与する状況を例に説明する。

URLのGETパラメータに以下のように日本語を使用したいとき、
http://qiita.com/AQRiL_1132/items/3b979e0a5476c33f2261?utm_source=GAフォーラム&utm_campaign=1701_注目記事&utm_medium=email

このままではURL内に使えない文字が含まれているため、GETパラメータの値部分をそれぞれ抜き出し、以下のようにURLエンコードすることによってリンクに設定する。

個々のパラメータの値にENCODEURL関数を使用し、

すべての文字列を結合してURLを完成させる

C9セルに出力された完成URL

http://qiita.com/AQRiL_1132/items/3b979e0a5476c33f2261?utm_source=GA%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A9%E3%83%A0&utm_campaign=1701_%E6%B3%A8%E7%9B%AE%E8%A8%98%E4%BA%8B&utm_medium=email

注意

ENCODEURL関数の引数にURL全体をそのまま入れると、https://の部分がhttps%3A%2F%2Fになってしまう等、エンコードされたくない部分の記号もすべてエンコードされてしまうので、あくまでGETパラメータの値など、個々の要素に対してENCODEURL関数を使用する

失敗例

今回出力されたURLは
GoogleアナリティクスのURL 生成ツールによって生成されるものと合致する。

実は、GoogleアナリティクスのツールでエンコードされたURLだと、ExcelのENCODEURL 関数と違ってアスタリスク記号*や丸括弧記号(``)がエスケープされない等の細かい違いがある。
これは、Googleのツール内でエンコードに使われているJavaScript命令encodeURIComponent()が1998年に定められた古いURL規格（RFC2396）に基づいて動いているからである。（2005年にRFC3986という新しい規格に更新されているにも関わらず、GoogleのURL生成ツールはこれに対応していない。）

encodeURIComponent() - JavaScript | MDN
encodeURIComponent は次を除く全ての文字をエスケープします : アルファベット、10進数字、- _ . ! ~ * ' ( )

ECMAScriptのURI関数の問題
これらの関数は ECMAScript 3rd edition で導入された関数である。そのため、URI の仕様として RFC 3986 でなく、古い RFC 2396 を参考に設計されている。
︙
encodeURIComponent: URI の最新の仕様に追随しておらずエスケープが足りない
情報セキュリティ技術動向調査（2009 年下期）：IPA 独立行政法人情報処理推進機構

むしろExcelでURLエンコードを行う方が「安全なURL」が生成される。

##検証：ENCODEURL関数は「正しいURL」を出力するか

上記の内容を説明したら
「ExcelのENCODEURL関数って本当にちゃんと動くの？」と言われてしまった。
何故か信用してもらえないので、検証結果を以下に示す。
結論としては、「(ほぼ)正しく動作する」

ここで言う「正しい」とは、URLについて定めた最新の規格であるRFC3986と、未だに多くのシステムで基準となっているRFC2396において、
「特別な意味を持つので、そのまま使用してはいけない」とされる予約語（reserved）、
「システム上で問題を起こすかもしれない」とされる非推奨の文字がパーセントエンコーディングされるということによって確かめている。
また、参考までにJavaScriptのencodeURIComponent()でのエンコード結果も横に示す。

RFC3986に反する箇所

ExcelのENCODEURL関数は、RFC3986でもRFC2396でも予約語に指定されていないチルダ記号（~）を%7Eにエンコードする。このため、ExcelのENCODEURL関数の動作は完全にRFC3986に合致している訳ではない。
この動作は、チルダ記号が1994年に定められたかなり古いURLの規格であるRFC1738において「安全でない」とされている記号に指定されているためと思われる。
%7EはURLデコード時にチルダ記号に変換されるため、通常の使用には問題ない。

###「半角空白のURLエンコードがおかしいぞゴルア！」と感じた方へ

詳しい方はこの表を見て「半角空白文字が+になっていないじゃないか」と仰るかも知れない。
もし、Googleアナリティクスのトラッキングパラメータに使用するという目的なのであれば、GoogleアナリティクスはURL内の%20も+もどちらも半角空白にデコードするので問題ない。
また、半角空白文字はGoogleアナリティクス公式のURL生成ツールでも%20にエンコードされる。

もし半角空白が%20とエンコードされることによって、何かしらの不都合が発生する場合は、

=SUBSTITUTE([[完成したエンコード処理済みのURL]],"%20","+")

のようにすると%20にエンコードされた半角空白が+記号に置き換えられ、
application/x-www-form-urlencodedに従ったエンコード結果が得られる。

SUBSTITUTE(文字列, 検索文字列, 置換文字列)
文字列中の指定された文字列を他の文字列に置き換えます
SUBSTITUTE 関数 - Office のサポート

完

##参考になるリンク
URIに使ってよい文字の話 - RFC2396 と RFC3986 - 本当は怖い情報科学

RFC3986 日本語訳 Uniform Resource Identifier (URI): 一般的構文

技術/HTTP/URLエンコードで 0x20(スペース) を "+" にすべきか "%20" にすべきか - Glamenv-Septzen.net

URLが空白を含むときは「スペース」と「%20」、「+」のどれを使うべきか？ | 海外SEO情報ブログ

encodeURIComponentが世界基準だと誤解してた話 - Qiita

日本語で入力されたurlをリンクに貼るとき - by shigemk2

エンコードマニアックス