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完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド

本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。

【本書の特徴】

情報量：Word-A4サイズ換算で1,400ページ相当
基本操作から高度なテクニックまで、段階的に学習可能
140種類の関数を詳細に解説
109種類の実用的なテクニックと具体例を紹介
最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説

【対象読者】

Excel初心者からプロフェッショナルまで
体系的にExcelスキルを向上させたい方
業務効率化を目指すビジネスパーソン
データ分析や可視化のスキルを磨きたい方

【本書の強み】

実務に即した例題と解説
視覚的な図表やスクリーンショットで理解を促進
最新のExcelバージョンに対応した内容
著者の長年の経験に基づく、実践的なTipsを多数収録

【更新とサポート】

常に最新の情報を反映するため、定期的に内容を更新
最新版の入手方法：Amazonカスタマーサポートへお問い合わせください

【入手方法】

買い切り：950円
Kindle Unlimited：読み放題プランで利用可能

Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。

購入はこちら

Excel 便利技LET関数