Excel URLパラメータの値を抽出する数式

2020年10月14日2023年1月23日

English version.

この記事ではGoogleアナリティクスのUTMパラメータを例に、

Excelの数式でURLパラメータの値を抽出する数式を紹介します。

1. サンプルファイル
- 1.1. 使用方法
2. 手順（数式の説明）
3. Excelを効率的に習得したい方へ

サンプルファイル

数式設定済みのブックファイルです。

excel-extraction-url-parameter ダウンロード

使用方法

A列の2行目以降に抽出したいURLを指定し、

B列以降の1行目に抽出したいパラメータ名、

B列以降の2行目以降にB2セルの数式をコピーします。

すると数式に対応するパラメータの値が表示されます。

日本語がURLエンコードされている場合、

必要に応じて変換ツールでデコードしておきましょう。

手順（数式の説明）

コピー元であるB2セルの数式は以下の通りです。セル内改行やインデントは任意です。

=SUBSTITUTE(
  MID($A2,
      FIND(B$1,$A2),
      IFERROR(FIND("&",$A2,FIND(B$1,$A2))-FIND(B$1,$A2),LEN($A2))
  ),
  B$1&"=",
  ""
)

まず2～4行目のMID関数でA列のURLから「URLパラメータ=値」の形で抽出しています。

FIND関数で1行目のパラメータ名の文字列を検索して抽出開始位置を探し、

次の「&」が来る位置から抽出文字数を割り出しています。

そして最後のパラメータには次の「&」がないため、

IFERROR関数で抽出文字数をURLの文字数（LEN関数）にしています。

（多いですが、エラーと不具合が発生しない指定）

1行目のSUBSTITUTE関数の「URLパラメータ=」の部分を削除しています。

（ここを残したければSUBSTITUTE関数は不要です）

数式の改善例(LET関数が使える場合)

2020年10月頃にMicrosoft(Office)365で追加された新機能のLET関数を利用すれば

FIND関数の重複なしに数式を組むことが可能です。

（いつ更新が反映されるかは時間差があります）

LET関数は一定の数式に任意の名前を付けて再利用可能にする関数です。

（マクロやプログラミングの変数のようなもの）

今回の数式例です。

重複している3個のFIND関数（背景色黄色）を任意の名前に置き換えます。

=SUBSTITUTE(
  MID($A2,
      FIND(B$1,$A2),
      IFERROR(FIND("&",$A2,FIND(B$1,$A2))-FIND(B$1,$A2),LEN($A2))
  ),
  B$1&"=",
  ""
)

↑元の数式。↓LET関数で置き換えた数式.

=LET(
  抽出開始位置,FIND(B$1,$A2),
  SUBSTITUTE(
    MID($A2,
    抽出開始位置,
    IFERROR(FIND("&",$A2,抽出開始位置)-抽出開始位置,LEN($A2))
  ),
  B$1&"=",
  ""
  )
)

これにより以下のようなメリットがあります。

数式の重複（冗長）がなくなり、数式の変更が簡単になる
意味のある名前を付けられるため数式の意図を把握しやすくなる（ただしLET関数を知っている必要あり）
数式の実行回数が減るのでExcelの計算が早くなる(今回の例ではFIND関数が3回→1回)

FIND関数は対象の文字数が長くなると重さを増してくる関数です。

数式を使うセルが多い場合は特に効果が大きいでしょう。

重複だけでなく意味のある数式の固まりやセル指定に名前を付ける手もあります。

数式自体は長くなりますが、何を意図した数式なのか構造は読みやすくなります。

（LET関数や変数に慣れているかという個人差の要素はあります）

どこまでLET関数で置き換えるかは適宜、考える必要がありますが、

セル指定が最小になるため数式を変更する手間や間違えるリスクが小さくなります。

=LET(
  抽出対象URL,$A2,
  パラメータ名,B$1,
  抽出開始位置,FIND(パラメータ名,抽出対象URL),
  抽出対象文字数,IFERROR(FIND("&",抽出対象URL,抽出開始位置)-抽出開始位置,LEN(抽出対象URL)),
  SUBSTITUTE(
    MID(抽出対象URL,
        抽出開始位置,
        抽出対象文字数
    ),
    パラメータ名&"=",
    ""
  )
)

パラメータ名を指定しない場合

パラメータの登場順にセル分割して列挙したい場合は、

?と&を区切り文字にしてテキストファイルウィザードを利用するか、

こちらの分割用関数（シート）を利用すると実現可能です。

URLパラメータを削除、またはURLパラメータのみにする

こちらの数式になります。

Excelを効率的に習得したい方へ

当サイトの情報を電子書籍用に読み易く整理したコンテンツを

買い切り950円またはKindle Unlimited (読み放題) で提供中です。

Word-A4サイズ：1,400ページの情報量で

（実際のページ数はデバイスで変わります）

基本的な使い方、関数の解説(140種類)、

頻出テクニックと実用例(109種類)、

XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。

体系的に学びたい方は是非ご検討ください。

アップデートなどの更新事項があれば随時反映しています。

なお購入後に最新版をダウンロードするには

Amazonへの問い合わせが必要です。

購入はこちら

Excel 便利技LET関数