スプレッドシートでURLの階層を抽出する方法

2024.10.28

Webサイトのデータ分析やSEO施策において、URLの構造を理解し、各階層を正確に抽出することは非常に重要です。本記事では、Google スプレッドシートを使用してURLの各階層を効率的に抽出する方法を詳しく解説します。

なぜURLの階層抽出が重要か

URLの階層構造を分析することで、以下のような利点があります：

  • サイト構造の把握と最適化
  • SEO分析における重要な指標の取得
  • コンテンツカテゴリーの整理
  • リダイレクト設定の管理
  • アクセス解析の精度向上

使用する関数の説明

REGEXMATCH関数

=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/?$")

この関数は、URLが特定のパターンに一致するかどうかを確認します。TRUEまたはFALSEを返します。

REGEXEXTRACT関数

=REGEXEXTRACT(A1, "^https?://[^/]+/([^/]+)")

URLから特定のパターンに一致する部分を抽出します。正規表現のグループ化を使用して必要な部分のみを取得できます。

FIND関数

=FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1)

文字列内の特定の文字位置を検索します。階層の区切り文字「/」の位置を特定するのに使用します。

各階層の抽出方法

1階層目の抽出

=IFERROR(
REGEXEXTRACT(
A1,
"^https?://[^/]+/([^/]+)"
),
"階層が存在しません"
)

2階層目の抽出

=IFERROR(
REGEXEXTRACT(
A1,
"^https?://[^/]+/[^/]+/([^/]+)"
),
"階層が存在しません"
)

3階層目の抽出

=IFERROR(
REGEXEXTRACT(
A1,
"^https?://[^/]+/[^/]+/[^/]+/([^/]+)"
),
"階層が存在しません"
)

4階層目の抽出

=IFERROR(
REGEXEXTRACT(
A1,
"^https?://[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"
),
"階層が存在しません"
)

5階層目の抽出

=IFERROR(
REGEXEXTRACT(
A1,
"^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"
),
"階層が存在しません"
)

6階層目の抽出

=IFERROR(
REGEXEXTRACT(
A1,
"^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"
),
"階層が存在しません"
)

FIND関数を使用した代替方法（3階層目の例）

=IFERROR(
MID(
A1,
FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1) + 1,
FIND("/", A1 & "/", FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1) + 1) -
FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1) - 1
),
"階層が存在しません"
)

実践的な使用例

スプレッドシートの構成

  • A列：元のURL
  • B列：1階層目
  • C列：2階層目
  • D列：3階層目
  • E列：4階層目
  • F列：5階層目
  • G列：6階層目

検証用URLサンプル

https://example.com/category/subcategory/product/detail
https://example.com/blog/2023/12/article-title
スプレッドシートでURLの階層を抽出する方法
Example Domain
スプレッドシートでURLの階層を抽出する方法
example.com

よくある問題とその解決方法

1. クエリパラメータの処理

クエリパラメータを含むURLを処理する場合は、以下の関数を追加します：

=REGEXEXTRACT(A1, "^([^?]+)")

2. 末尾スラッシュの統一

末尾スラッシュの有無を統一する場合：

=REGEXREPLACE(A1, "/?$", "/")

3. 大文字小文字の統一

=LOWER(REGEXEXTRACT(A1, "^https?://[^/]+/([^/]+)"))

応用テクニック

カテゴリー分析用の集計

=QUERY(
{B2:B1000},
"SELECT Col1, COUNT(Col1) GROUP BY Col1 ORDER BY COUNT(Col1) DESC"
)

パンくずリストの生成

=B2 & " > " & C2 & " > " & D2

まとめ

URLの階層構造を効率的に分析することで、以下のような利点があります：

  • サイト構造の最適化
  • ユーザビリティの向上
  • SEO効果の向上
  • データ分析の効率化

これらの関数を組み合わせることで、大量のURLデータを効率的に処理し、有用な情報を抽出することができます。

補足：関数の使い分け

  • REGEXMATCH：パターンの一致確認
  • REGEXEXTRACT：特定部分の抽出
  • FIND：シンプルな文字位置の特定

より高度な分析や特殊なケースへの対応が必要な場合は、これらの基本関数を組み合わせることで柔軟に対応することができます。

コメント