ExcelのENCODEURL関数と「URLに使ってはいけない文字」について

#ENCODEURL関数について

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

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

>**ENCODEURL(文字列)**
> URL 形式でエンコードされた文字列を返します。
> [ENCODEURL 関数 - Excel](https://support.office.com/ja-jp/article/ENCODEURL-%E9%96%A2%E6%95%B0-07c7fb90-7c60-4bff-8687-fac50fe33d0e)
>注: ENCODEURL 関数は Excel Online と Excel 2016 for Mac では利用できません。

ENCODEURL関数は、URL内にそのまま入力すると問題を起こす文字（＝URLとしての予約語や非推奨文字）を[パーセントエンコーディング](http://e-words.jp/w/URL%E3%82%A8%E3%83%B3%E3%82%B3%E3%83%BC%E3%83%89.html)して、無害化してくれる便利な関数である。

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

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

![image](https://qiita-image-store.s3.amazonaws.com/0/103957/7d766302-716e-958c-0a1f-e1ebbb037328.png)

>**HYPERLINK(リンク先)**
>ネットワーク サーバー、イントラネット、またはインターネット上に保存されているドキュメントを開くために、ショートカットまたはジャンプを作成します。HYPERLINK 関数が設定されているセルをクリックすると、リンク先に指定した場所に保存されているファイルが開かれます。
>[HYPERLINK 関数 - Office のサポート](https://support.office.com/ja-jp/article/HYPERLINK-%E9%96%A2%E6%95%B0-333c7ce6-c5ae-4164-9c47-7de9b76f577f)

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

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

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

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

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

個々のパラメータの値にENCODEURL関数を使用し、
![image](https://qiita-image-store.s3.amazonaws.com/0/103957/9311a1f1-c0a4-7a90-5845-f54454ae3075.png)

すべての文字列を結合してURLを完成させる
![image](https://qiita-image-store.s3.amazonaws.com/0/103957/88e0da34-58d5-79c8-e866-86200bcae1a0.png)


**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関数を使用する

**失敗例**
![image](https://qiita-image-store.s3.amazonaws.com/0/103957/9ccecf56-7af4-b63b-965a-fb4afbb81b04.png)

今回出力されたURLは
[GoogleアナリティクスのURL 生成ツール](https://support.google.com/analytics/answer/1033867?hl=ja)によって生成されるものと合致する。

![image](https://qiita-image-store.s3.amazonaws.com/0/103957/fd99cf78-f1d5-4181-dbe8-2b89f6f3f600.png)

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

>[encodeURIComponent() - JavaScript | MDN](https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/encodeURIComponent)
> encodeURIComponent は次を除く全ての文字をエスケープします : アルファベット、10進数字、- _ . ! ~ * ' ( )

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

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

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

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

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


![image](https://qiita-image-store.s3.amazonaws.com/0/103957/12bfcd4a-1ed3-41a7-8464-3f0fae7baec0.png)

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

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

詳しい方はこの表を見て「半角空白文字が`+`になっていないじゃないか」と仰るかも知れない。
もし、Googleアナリティクスのトラッキングパラメータに使用するという目的なのであれば、**GoogleアナリティクスはURL内の`%20`も`+`もどちらも半角空白にデコードする**ので問題ない。
また、半角空白文字は[Googleアナリティクス公式のURL生成ツール](https://support.google.com/analytics/answer/1033867?hl=ja)でも`%20`にエンコードされる。
![image](https://qiita-image-store.s3.amazonaws.com/0/103957/835bda2a-53f0-8770-b448-b90c57a2b553.png)

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

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

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

>**SUBSTITUTE(文字列, 検索文字列, 置換文字列)**
>文字列中の指定された文字列を他の文字列に置き換えます
>[SUBSTITUTE 関数 - Office のサポート](https://support.office.com/ja-jp/article/SUBSTITUTE-%E9%96%A2%E6%95%B0-6434944e-a904-4336-a9b0-1e58df3bc332)


**完**

##参考になるリンク
[URIに使ってよい文字の話 - RFC2396 と RFC3986 - 本当は怖い情報科学](http://freak-da.hatenablog.com/entry/20080321/p1)

[RFC3986 日本語訳 Uniform Resource Identifier (URI): 一般的構文](https://triple-underscore.github.io/RFC3986-ja.html)

[技術/HTTP/URLエンコードで 0x20(スペース) を "+" にすべきか "%20" にすべきか - Glamenv-Septzen.net](https://www.glamenv-septzen.net/view/1170)

[URLが空白を含むときは「スペース」と「%20」、「+」のどれを使うべきか？ | 海外SEO情報ブログ](https://www.suzukikenichi.com/blog/which-shold-you-use-space-20-or-plus-in-urls-containing-space/)

[encodeURIComponentが世界基準だと誤解してた話 - Qiita](http://qiita.com/shibukawa/items/c0730092371c0e243f62)

[日本語で入力されたurlをリンクに貼るとき - by shigemk2](http://www.shigemk2.com/entry/20111206/1323185627)

[エンコードマニアックス](http://www.encodemaniax.com/)