👾

【Googleスプレッドシート】SUMPRODUCT関数と正規表現を使って特定のURLをカウントする方法

2025/03/11に公開

1. はじめに

「データの中から特定のパターンを含むものだけを集計したいけれど、どうすればいいのか?」
「SUMPRODUCTを使えばいいらしいけれど、正規表現を条件に含める方法が分からない...」

こんな悩みを抱えている方に向けて、Googleスプレッドシートの SUMPRODUCT 関数と REGEXMATCH 関数を組み合わせて、特定のパターンを含むデータをカウント・合計する方法を解説します。

関数の使い方だけでなく、なぜ --(二重マイナス演算子)が必要なのか、全列参照の問題点など、スプレッドシートのパフォーマンスを最適化するポイントも説明します。

この記事では、データ分析を行うマーケターやWeb担当者向けに、具体的な関数の書き方や応用テクニックを紹介します。


2. 対象読者

  • Googleスプレッドシートを使ってデータ集計を行いたい方
  • SUMPRODUCT 関数や REGEXMATCH 関数の使い方を学びたい方
  • スプレッドシートで条件付き集計を行いたい方
  • 特定のパターンを含むデータを抽出する方法を知りたい方

3. 記事を読むメリット

この記事を読むことで、次のようなことが分かります。

  • SUMPRODUCT を使って条件付きでデータを合計・カウントする方法
  • REGEXMATCH を使って、特定のパターンを抽出する方法
  • --(二重マイナス)を使う理由とブール値の変換方法
  • SUMPRODUCT で全列参照を避けるべき理由と、最適な範囲指定の方法

4. 結論

  • SUMPRODUCT 関数と REGEXMATCH 関数を組み合わせることで、スプレッドシート上で特定のパターンを含むデータを集計できる。
  • -- を使うことで、TRUE/FALSE1/0 に変換し、条件付きの計算が可能になる。
  • 全列参照 (A:A) は処理速度が遅くなるため、範囲を限定 (A2:A1000) するのがベストプラクティス。

5. 本文

5.1 SUMPRODUCT 関数とは?

SUMPRODUCT 関数は、指定した範囲の要素同士を掛け合わせ、その合計を求める関数です。

基本構文

=SUMPRODUCT(配列1, 配列2, ...)

例:商品と価格の合計

A列 (商品) B列 (数量) C列 (単価)
りんご 3 100
みかん 5 80
りんご 2 100
みかん 4 80
=SUMPRODUCT(B2:B5, C2:C5)

これは、数量 × 単価 を計算し、すべての合計を求めます。


5.2 REGEXMATCH を使って特定のパターンを検索

REGEXMATCH は、指定したテキストが 正規表現に一致するかどうか を判定する関数です。

構文

=REGEXMATCH(検索対象セル, "正規表現")

例:特定のパターンを含むかチェック

=REGEXMATCH(A2, "example|sample|test")

TRUE または FALSE が返されます。


5.3 SUMPRODUCT と REGEXMATCH を組み合わせる

特定の条件を満たすデータの合計を求める式

=SUMPRODUCT(--(データ範囲!D2:D1000="click"), --REGEXMATCH(データ範囲!F2:F1000, "example|sample|test"), データ範囲!F2:F1000)

解説

  1. データ範囲!D2:D1000="click"D列click なら TRUE1)、それ以外は FALSE0)。
  2. REGEXMATCH(データ範囲!F2:F1000, "example|sample|test")
    • F列特定のパターン が含まれる場合 TRUE1)、それ以外 FALSE0)。
  3. SUMPRODUCT により、条件を満たす F列 の値を合計。

5.4 --(二重マイナス)を使う理由

REGEXMATCH条件式TRUE/FALSE を返しますが、SUMPRODUCT では 1/0 にする必要があります。

変換方法

--(条件式)

=SUMPRODUCT(--(A2:A10="りんご"))

この -- によって TRUE → 1FALSE → 0 に変換されます。


5.5 全列参照を避けるべき理由

SUMPRODUCTA:A のように 全列指定すると、104万行すべてが対象 になり、処理が遅くなります。

❌ 避けるべき書き方

=SUMPRODUCT(--(A:A="りんご"))

✅ 最適な書き方

=SUMPRODUCT(--(A2:A1000="りんご"))

6. まとめ

SUMPRODUCTREGEXMATCH で特定のパターンを含むデータを集計できる。
-- を使って TRUE/FALSE1/0 に変換する。
SUMPRODUCT では 全列参照を避け、範囲を限定する のが重要。

Googleスプレッドシートでデータを効率よく分析するために、ぜひ活用

Discussion